Vamos a hablar de la función COINCIDIR, busca un elemento en un intervalo de celdas y devuelve la posición de dicho elemento en el rango.
Para buscar la posición de un elemento, es mejor usar COINCIDIR que la función BUSCAR.
Sintaxis
COINCIDIR(valor_buscado,matriz_buscada, [tipo_de_coincidencia])
· Valor buscado, es un argumento obligatorio. Es el valor que deseamos buscar en el argumento matriz_buscada. El argumento valor buscado puede ser un número, texto, valor lógico, o, una referencia a una celda que contenga un número, texto o valor lógico.
· Matriz buscada, es un argumento obligatorio. Es el rango de celdas donde se va a realizar la búsqueda.
· Tipo de coincidencia, es un argumento opcional. Puede ser el número -1, 0 o 1. El argumento tipo de coincidencia especifica cómo Excel hace coincidir el valor buscado con los valores del argumento matriz buscada. El valor predeterminado de este argumento es 1.
Si especificamos el valor 1, u, lo omitimos, buscara el valor mas grande que sea menor o igual al buscado, si especificamos el valor 0, buscara una coincidencia exacta, y, si ponemos el valor -1, busca el valor mas pequeño que sea mayor o igual al buscado.
COINCIDIR nos devuelve la posición no el valor.
COINCIDIR no distingue entre mayúsculas y minúsculas.
Si COINCIDIR no puede encontrar una coincidencia, devuelve el valor de error #N/A.
Veamos un ejemplo, tenemos los siguientes datos.
Queremos buscar el valor 4500, pero dicho valor no se encuentra por lo que vamos a encontrar el siguiente elemento menor, para ello, en la celda K2, ponemos COINCIDIR, donde como argumento valor buscado, seleccionamos la celda K1.
=COINCIDIR(K1
Punto y coma, como argumento matriz buscada, seleccionamos el rango H2:H18.
=COINCIDIR(K1;H2:H18
Punto y coma, como argumento tipo de coincidencia, seleccionamos menor que.
Cerramos paréntesis y aceptamos.
Nos devuelve la posición 3.
Si nos fijamos en el modelo, la posición 3 corresponde al valor 2035.
Vemos que hay cantidades más cercanas a 4500, como son los valores 3675, 3330, y, 3636.
Entonces, ¿Qué ocurre?
Ocurre que el modelo no está ordenado, cuando solicitamos que nos devuelva el siguiente elemento menor, el modelo debe de estar ordenado de manera ascendente, es decir, de menor a mayor, para ello, vamos a seleccionar los datos, vamos a la pestaña datos, dentro de ordenar y filtrar, hacemos clic en el siguiente icono:
Vemos como la posición ha cambiado, ahora, nos devuelve la posición 7.
Si nos fijamos en el modelo, vemos que si es la siguiente cantidad menor.
Ahora, en el mismo modelo, queremos buscar la cantidad 6125, la expresión es la misma que para el ejemplo anterior, lo único que debemos de cambiar es el argumento tipo de coincidencia al valor 0, es decir, coincidencia exacta.
=COINCIDIR(D1;A2:A18;0)
Nos devuelve la posición 12.
Ahora, queremos buscar la misma cantidad que para el ejemplo1 (4500), pero con una coincidencia mayor que, para ello, solo debemos de cambiar el argumento tipo de coincidencia a mayor que.
=COINCIDIR(D1;A2:A18;-1)
Pero, nos devuelve un error.
¿Por qué?
Pues, cuando solicitamos un mayor que el buscado, el modelo debe de estar ordenado de manera descendente, es decir, del numero mayor al numero menor, vamos a la pestaña datos, dentro de ordenar y filtrar, hacemos clic en el siguiente icono:
Ahora, nos devuelve la posición correcta.
Si buscamos un valor, y, en el argumento matriz buscada, seleccionamos mas de una columna, nos devolverá un error, porque solo podemos especificar como argumento matriz buscada, una sola columna.
Tenemos una función llamada FILA, que nos devuelve la fila de la celda o rango que especifiquemos, solo tiene un argumento, que es la celda o rango para devolver la fila, tenemos el siguiente modelo.
Queremos saber la fila que ocupa la provincia de Sevilla, COINCIDIR cuando encuentra la primera coincidencia, deja de buscar, si escribimos COINCIDIR, donde como argumento valor buscado, ponemos entre comillas dobles, Sevilla, como argumento matriz buscada, seleccionamos los datos, y, como tipo de coincidencia, ponemos exacta.
=COINCIDIR("Sevilla";A2:A23;0)
Nos devuelve la posición 4, que es la primera vez que aparece Sevilla.
Pero, queremos la fila de todas las veces que aparece Sevilla, volvemos a la función, cambiamos el argumento matriz buscada, por:
=COINCIDIR("Sevilla";B2:B2;0)
Es decir, que busque en la primera fila.
Arrastramos, y, tenemos la posición cada vez que aparece Sevilla, siempre nos devolverá la posición 1, porque busca en una sola fila, donde no hay coincidencia, nos devuelve un error.
Para obtener la fila, vamos a usar el condicional SI, donde vamos a preguntar que si es error el resultado de la función COINCIDIR, por lo que usaremos la función ESERROR.
=SI(ESERROR(COINCIDIR("Sevilla";B2:B2;0))
En ese caso, que ponga un texto en blanco.
=SI(ESERROR(COINCIDIR("Sevilla";B2:B2;0));""
En caso contrario, ponemos la función FILA con un argumento nulo, por lo que nos devolverá la fila donde tenemos la función.
Cerramos paréntesis.
=SI(ESERROR(COINCIDIR("Sevilla";B2:B2;0));"";FILA())
Aceptamos, y arrastramos.
Vemos que donde no hay coincidencia, aparece blanco, y, donde la hay, nos aparece el numero de fila donde se encuentra la provincia de Sevilla.
Función COINCIDIRX(XMATCH)
Excel para Office 365 Excel para Office 365 para Mac Excel para la Web
La función COINCIDIRX(XMATCH) busca un elemento dentro de una matriz o rango de celdas y, a continuación, devuelve la posición relativa del elemento.
Es una versión mejorada de COINCIDIR, puede buscar en cualquier dirección, y, de forma predeterminada, devuelve una coincidencia exacta.
Sintaxis
· Valor_buscado: Obligatorio: El valor de búsqueda.
· Matriz_buscada: Obligatorio. Matriz o rango donde buscar.
· Modo_de_coincidencia: Opcional. Tipo de coincidencia.
· 0: coincidencia exacta (predeterminado).
· -1-coincidencia exacta o siguiente elemento más pequeño.
· 1-coincidencia exacta o siguiente elemento de mayor tamaño.
· 2-una coincidencia de comodín (*,?), el asterisco sustituye cualquier carácter, y, el asterisco cualquier número de caracteres.
· Modo_de_busqueda: Opcional. Especifica el tipo de búsqueda.
1-Buscar en primer y último elemento (predeterminado).
-1-buscar el apellido del primero (búsqueda inversa).
2-realizar una búsqueda binaria que depende del valor_buscado se ordena en orden Ascendente. Si no está ordenado, se devolverán resultados no válidos.
-2-Realice una búsqueda binaria en la que se basa del valor_buscado en orden descendente. Si no está ordenado, se devolverán resultados no válidos.
Veamos un ejemplo.
Tenemos el siguiente modelo:
Vamos a usar una búsqueda con caracteres comodín, para buscar la posición del primer término que se parezca a "Uv".
En la celda F5, ponemos la función, como argumento valor buscado, seleccionamos la celda E5.
=COINCIDIRX(E5
Punto y coma, como argumento matriz buscada, seleccionamos el rango con los datos.
=COINCIDIRX(e5;C5:C9
Punto y coma, como argumento modo de coincidencia, seleccionamos coincidencia de caracteres comodín.
Cerramos paréntesis.
=COINCIDIRX(E5;C5:C9;2)
Aceptamos, y, nos devuelve la posición 2.
Cosa que es correcto.
También, podríamos haber usado, para este ejemplo, ¿el carácter comodín ?, pues si, porque solo vamos a sustituir un carácter.
Para el siguiente ejemplo, tenemos una serie de comerciales, junto con sus ventas.
Queremos buscar unas ventas que sean de 15000.
Dicho valor no se encuentra en la columna de totales, y, queremos obtener una coincidencia exacta, o, siguiente elemento menor.
En la celda F6, ponemos COINCIDIRX, como argumento valor buscado, seleccionamos la celda F4.
=COINCIDIRX(F4
Punto y coma, como argumento matriz buscada, seleccionamos los valores.
=COINCIDIRX(F4;C5:C11
Punto y coma, como argumento modo de coincidencia, seleccionamos coincidencia exacta o el siguiente elemento menor, quiere decir, que, si encuentra la cantidad buscada, nos la devolverá, en caso contrario, nos devolverá el siguiente elemento menor.
Cerramos paréntesis.
=COINCIDIRX(F4;C5:C11;-1)
Aceptamos, y, nos devuelve la posición 3.
Si vemos el modelo, vemos que la posición 3, es el siguiente elemento menor a 15000.
Y, si el modelo no estuviera ordenado, pues, vemos que nos devuelve la posición 4, que no es el siguiente elemento menor.
Igual que pasa con COINCIDIR, cuando solicitamos que nos devuelva el siguiente elemento menor, el modelo debe de estar ordenado de manera ascendente.
Si queremos que nos devuelve la posición del número mayor, si aplicamos la formula, y, cambiamos -1 por 1, nos devolverá un resultado incorrecto, por lo que igual que con COINCIDIR, cuando solicitamos un mayor que el buscado, el modelo debe de estar ordenado de manera descendente.
El ultimo argumento, opcional, es modo de búsqueda, somos nosotros los que decidimos como queremos que busque, las opciones disponibles son las siguientes.
Miguel Angel Franco Garcia
Comments