La mayoría de las funciones de búsqueda (BUSCAR, BUSCRAV, COINCIDIR, etc) que existen en Excel son muy prácticas a la hora de encontrar un valor buscado, pero todas ellas se paran en el primer valor que encuentran y coincide. En esta entrada aprenderemos una manera, mediante funciones, de localizar no sólo el primer valor coincidente, si no sucesivos.
Partiremos de un listado con diferentes elementos, entre los cuales se halla nuestro valor buscado: 'Excelforo' (remarcado en fondo amarillo):
El proceso de búsqueda se basa precisamente en la funcionalidad comentada, Excel devuelve el primer valor encontrado; de tal forma que el trabajo consistirá en construir un rango dinámico (donde buscar las coincidencias), adaptándolo al último valor encontrado. Veamos el resultado y la fórmula principal:
=COINCIDIR($E$2;INDIRECTO("A$"&E5+1&":$A$"&CONTARA(A:A));0)+E5
En el rango de resultados E5:E9 observamos como cada fórmula se basa en la anterior para componer el nuevo rango de búsqueda, que comienza en la fila siguiente al anterior valor encontrado. Nuestra fórmula emplea las funciones COINCIDIR e INDIRECTO para operar. Desglosamos nuestra fórmula explicándola paso a paso, centrándonos en la segunda posición (celda E6)...
Lo más profundo de la fórmula es el literal que se convertirá posteriormente en rango, lo que conseguimos con un concatenado:
"A$"&E5+1&":$A$"&CONTARA(A:A)
La clave de todo está precisamente en este paso, ya que al valor encontrado anterior le sumamos uno +1 para determinar el comienzo del nuevo rango de búsqueda.
Una vez construido este literal (tipo texto) es fácil anidarlo en una función INDIRECTO para convertirlo en un rango de celda entendible por Excel:
INDIRECTO("A$"&E5+1&":$A$"&CONTARA(A:A))
Una vez tratado como rango de celdas, podremos emplearlo en la búsqueda realizada con la función COINCIDIR, muy sencilla de entender, ya que sólo le decimos que busque el valor 'Excelforo' de la celda E2, en la matriz de búsqueda o rango 'dinámico' recién construido, para una coincidencia exacta (determinado por el tercer argumento igual a cero):
=COINCIDIR($E$2;INDIRECTO("A$"&E5+1&":$A$"&CONTARA(A:A));0)+E5
A todo eso le sumamos el número de filas anteriormente calculado, con lo que conseguimos el número de fila absoluto.
Los valores obtenidos corresponden, en definitiva, al número de fila en que se encuentra el valor buscado.
Partiremos de un listado con diferentes elementos, entre los cuales se halla nuestro valor buscado: 'Excelforo' (remarcado en fondo amarillo):
El proceso de búsqueda se basa precisamente en la funcionalidad comentada, Excel devuelve el primer valor encontrado; de tal forma que el trabajo consistirá en construir un rango dinámico (donde buscar las coincidencias), adaptándolo al último valor encontrado. Veamos el resultado y la fórmula principal:
=COINCIDIR($E$2;INDIRECTO("A$"&E5+1&":$A$"&CONTARA(A:A));0)+E5
En el rango de resultados E5:E9 observamos como cada fórmula se basa en la anterior para componer el nuevo rango de búsqueda, que comienza en la fila siguiente al anterior valor encontrado. Nuestra fórmula emplea las funciones COINCIDIR e INDIRECTO para operar. Desglosamos nuestra fórmula explicándola paso a paso, centrándonos en la segunda posición (celda E6)...
Lo más profundo de la fórmula es el literal que se convertirá posteriormente en rango, lo que conseguimos con un concatenado:
"A$"&E5+1&":$A$"&CONTARA(A:A)
La clave de todo está precisamente en este paso, ya que al valor encontrado anterior le sumamos uno +1 para determinar el comienzo del nuevo rango de búsqueda.
Una vez construido este literal (tipo texto) es fácil anidarlo en una función INDIRECTO para convertirlo en un rango de celda entendible por Excel:
INDIRECTO("A$"&E5+1&":$A$"&CONTARA(A:A))
Una vez tratado como rango de celdas, podremos emplearlo en la búsqueda realizada con la función COINCIDIR, muy sencilla de entender, ya que sólo le decimos que busque el valor 'Excelforo' de la celda E2, en la matriz de búsqueda o rango 'dinámico' recién construido, para una coincidencia exacta (determinado por el tercer argumento igual a cero):
=COINCIDIR($E$2;INDIRECTO("A$"&E5+1&":$A$"&CONTARA(A:A));0)+E5
A todo eso le sumamos el número de filas anteriormente calculado, con lo que conseguimos el número de fila absoluto.
Los valores obtenidos corresponden, en definitiva, al número de fila en que se encuentra el valor buscado.
Hola amigo: De entrada gracias por resolvernos las dudas. Tengo excel 2007 y tengo que insertar una función que devuelva un cargo del 70% a los artículos cuya referencia es menor o igual que 4000; un 75% si es mayor que 4000 y menor o igual que 10000, y un 80% si es mayor de 10000. No se exactamente que fórmula realizar. Me podrías ayudar. Tengo una tabla con estas columnas: código artículo (referencia), descripción, categoría, precio de compra y otra que es el cargo donde debo introducir los nuevos datos. Me podrías ayudar. Gracias de antemano
ResponderEliminarHola!
Eliminarbueno, pues tendrías que aplicar una función SI:
=SI(Referencia<=4000;70%;SI(Y(4000<Referencia;Referencia<=10000);75%;80%))
Espero te sirva...
Slds cordiales
Gracias y mil gracias. ya me ha salido y lo he entendido
Eliminar;-)
EliminarMe alegra te sirviera.
Slds
Hola amigo, estoy practicando con el ejercicio que propones (tal cual), pero observo que, si por ejemplo tienes la fórmula en diez campos para posible coincidencia, y de antemano sabes que, como en el ejemplo, tienes cinco coincidencias, si el último registro coincide con el valor de búsqueda, la fórmula sigue "corriendo" y seguirá numerando aun cuando ya no haya más registros. ¿Podrías indicarme por qué?
ResponderEliminarSaludos cordiales!
Hola beto,
Eliminaral haber en la última fila del rango de búsqueda un valor buscado, el rango construido se invierte según arrastras la fórmula; por ejemplo si el rango con datos es A1:A10 y en la última celda existe el valor buscado, si continuamos nuestra fórmula el rango contruido sería:
A11:A0
A12:A10
A13:A10
etc
claro, al tener el rango activo siempre A10 la función COINCICIR encuentra algo en la primera posición, es decir, devuelve 1; como además sumamos el valor anterior incrementamos en +1.
Espero haber sido claro
:-)
Slds