Al utilizar Excel para el análisis de datos, a menudo nos encontramos en situaciones en las que se necesita obtener todos los valores que coinciden con una identificación específica, nombre, dirección de correo electrónico, región, producto, etc. Una solución inmediata que viene a la mente es usar la función Excel BUSCARV, pero el problema es que solo puede devolver una coincidencia.
BUSCARV para múltiples valores se puede hacer a través de una combinación de funciones. Si tu nivel de Excel no es de experto en Excel, espero que puedar aguantar y seguir leyendo. Haré lo posible para explicar la lógica subyacente para que incluso un principiante pueda entender las fórmulas y ajustarlas para resolver tareas similares.
El post tendrá 3 partes y hoy será la primera.
1. Buscar coincidencias para devolver múltiples valores en columnas
2. Buscar coincidencias para devolver múltiples coincidencias en filas
3. Buscar múltiples coincidencias basadas en múltiples criterios
Empecemos!
Cómo hacer múltiples coincidencias en Excel usando una fórmula
Como se mencionó al principio de este tutorial, no hay manera de hacer que la función de Excel BUSCARV devuelva múltiples valores. La tarea se puede realizar utilizando las siguientes funciones en una fórmula matricial:
- SI: evalúa la condición y devuelve un valor si se cumple la condición, y otro valor si la condición no se cumple.
- K.ESIMO.MENOR : obtiene el valor k-esimo más pequeño de la matriz.
- ÍNDICE: devuelve un elemento de matriz según los números de fila y columna que especifique.
- FILA - devuelve el número de fila.
- COLUMNA - devuelve el número de columna.
- SI.ERROR - atrapa errores.
Para más información consulta los siguientes enlaces:
- La función SI
- La función K.ESIMO.MENOR
- La función INDICE
- La función FILA
- La función COLUMNA
- La función SI.ERROR
Buscar múltiples valores y que devuelva resultados en una columna
Supongamos que se tienen las regiones donde una empresa opera en la columna A y los productos que vendieron en la columna B. La columna A contiene algunas apariciones de cada región.
Nuestro objetivo es obtener una lista de todos los productos vendidos por región.
Para hacerlo, por favor sigue estos pasos:
1. Creamos una lista con la regiones en alguna fila vacía, en la misma u otra hoja de trabajo. En mi ejemplo, las regiones se encuentran en las celdas E2:H2.
2. Debajo de la primera región, seleccionamos un número de celdas vacías que sea igual o mayor que el número máximo de coincidencias posibles (en mi caso 4)
{=SI.ERROR(INDICE($B$3:$B$11, K.ESIMO.MENOR(SI(E$2=$A$3:$A$11, FILA($A$3:$A$11)-2,""), FILA()-2)),"")}
o
{=SI.ERROR(INDICE($B$3:$B$11, K.ESIMO.MENOR(SI(E$2=$A$3:$A$11, FILA($A$3:$A$11)-MIN(FILA($A$3:$A$11))+1,""), FILA()-2)),"")}
Como se puede ver, la primera fórmula es un poco más compacta, pero la segunda es más universal y requiere menos modificaciones (explicaremos un poco más la sintaxis y la lógica).
3. Se copia la fórmula a otras columnas al resto de celdas.
Cómo funciona esta fórmula
Este es un ejemplo de nivel intermedio-avanzado en Excel ya que implica un conocimiento básico de fórmulas matriciales y funciones de Excel. Si necesitas un refuerzo en fórmulas matriciales, lee el siguiente enlace:
Bueno para entender la fórmula anterior iremos trabajando desde dentro hacia afuera:
1. Función SI;
En el núcleo de la fórmula, se utiliza la función SI para obtener las posiciones de todas las apariciones del valor de búsqueda en el rango de búsqueda: SI(E$2=$A$3:$A$11, FILA($A$3:$A$11)-2,"")
La función SI compara el valor de búsqueda (E2) con cada valor en el rango de búsqueda (A3:A11), y si encuentra la coincidencia devuelve la posición relativa de la fila y una cadena vacía ("") en caso contrario.
Las posiciones relativas de las filas se calculan restando 2 de FILA($A$3:$A$11) de modo que la primera fila tenga la posición 1. Si su rango de retorno comienza en la fila 2, reste 1, y así sucesivamente. El resultado de esta operación es la matriz {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}, que va al argumento valor_si-verdadero de la función SI.
En lugar del cálculo anterior, se puede usar esta expresión: FILA(columna_a_mirar) - MIN(FILA (columna_a_mirar)) + 1, que devuelve el mismo resultado pero no requiere ningún cambio independientemente de la ubicación de la columna de devolución. En este ejemplo, sería FILA($A$3:$A$11)-MIN(FILA($A$3:$A$11))+1
Entonces, en este punto tienes una matriz que consiste en números (posiciones de coincidencias) y cadenas vacías (no coincidencias). Para la celda E3 en este ejemplo, tenemos la siguiente matriz:
={1;"";"";"";5;"";"";"";""}
Si verificamos con los datos de origen, vereremos que "Norte" (valor de búsqueda en E2) aparece en las posiciones 1 y 5 en el rango de búsqueda (A3: A11).
2. Función K-ESIMO.MENOR
A continuación, la función K-ESIMO.MENOR(matriz, k) interviene para determinar cuál de las coincidencias debe devolverse en una celda específica.
Con la matriz ya establecida, resolvemos el argumento k, es decir, el k-esimo valor más pequeño que se devolverá. Para esto, crearemos una especie de "contador incremental" FILA() - n, donde "n" es el número de fila de la primera celda de fórmula menos 1.
En este ejemplo, ingresamos la fórmula en las celdas E3: E7, por lo que FILA()-2 devuelve "1" para la celda E3 (fila 3 menos 2), "2" para la celda E4 (fila 4 menos 2), etc.
Como resultado, la función K-ESIMO.MENOR extrae el primer elemento más pequeño de la matriz en la celda E3, el segundo elemento más pequeño en la celda E4, y así sucesivamente. Y esto transforma la fórmula inicial larga y compleja en una muy simple, como esta:
=SI.ERROR(INDICE($B$3:$B$11, {5},"")
EXTRA:
Para ver el valor calculado detrás de una cierta parte de una fórmula, selecciona esa parte en la barra de fórmulas y presionamos F9.
3. Función INDICE
Esta parte es fácil. Utilizamos la función ÍNDICE para devolver el valor de un elemento de la matriz en función de su número de fila.
4. Función SI.ERROR
Y por último, la fórmula se envuelve con la función SI.ERROR la cual nos facilita manejar posibles errores, que son inevitables porque no puede saber cuántas coincidencias se devolverán para este o aquel valor de búsqueda, y por lo tanto, se copia la fórmula en un número de celdas igual a o mayor que el número de coincidencias posibles. Para no asustar a los usuarios con un conjunto de errores, simplemente esta función nos ayuda a reemplazarlos con una cadena vacía (celda en blanco).
Nota.
Se ha de tener en cuenta el uso correcto de referencias de celda absolutas y relativas en la fórmula. Todas las referencias son fijas, excepto la referencia de columna relativa en el valor de búsqueda (E$ 2), que debe cambiar según la posición relativa de una (s) columna (s) donde se copia la fórmula para devolver coincidencias para otros valores de búsqueda.
Al juntar todo esto, obtenemos las siguientes fórmulas genéricas para varios valores de BUSCARV en Excel:
Formula 1:
SI.ERROR(INDICE(rango_a_devolver, K.ESIMO.MENOR(SI(Valor_busqueda = rango_a_buscar, FILA(rango_a_devolver)- m ,""), FILA() - n )),"")
Formula 2:
SI.ERROR(INDICE(rango_a_devolver, K.ESIMO.MENOR(SI(Valor_busqueda = rango_a_buscar, FILA(rango_a_buscar) - MIN(FILA(rango_a_buscar))+1,""), FILA() - n )),"")
Dónde:
- m es el número de fila de la primera celda en el rango a devlover menos 1.
- n es el número de fila de la primera celda de fórmula menos 1.
Nota. En el ejemplo anterior, tanto n como m son iguales a "2" porque nuestro rango de retorno y fórmula comienzan en la fila 3. En otras hojas de trabajo, estos pueden ser números diferentes.
Entradas relacionadas
1. Also así como BuscarV horizontalmente para múltiples registros
2. Cómo buscar (BUSCARV) registros similares (pero que no coinciden) en Excel?
3. 3 errores comunes al escribir la función BUSCARV en Excel
4. Cómo hacer que BUSCARV vaya más rápido cuantos teneos gran cantidad de datos en Excel
5. Devolver un valor en Excel si se encuentra en un intervalo especificado
6. BUSCARV para la última aparición
7. Buscando hacia la izquierda de una tabla - INDICE + COINCIDIR
2. Cómo buscar (BUSCARV) registros similares (pero que no coinciden) en Excel?
3. 3 errores comunes al escribir la función BUSCARV en Excel
4. Cómo hacer que BUSCARV vaya más rápido cuantos teneos gran cantidad de datos en Excel
5. Devolver un valor en Excel si se encuentra en un intervalo especificado
6. BUSCARV para la última aparición
7. Buscando hacia la izquierda de una tabla - INDICE + COINCIDIR
Quieres más trucos?
Te ha gustado el artículo?
No te olvides de mostrar tu gratitud hacia el autor (yo) mediante una de las 2 siguientes acciones:
1. Puedes dejar una donación pinchando en la siguiente imagen.
2. Puedes recomendar el artículo en Google haciendo clic en g+1 (justo después del artículo), también puedes compartirlo en Facebook, y/o dejar comentarios.
Quieres aprender más en Excel o tienes algún problema que necesita solución?
Puedo ofrecer servicios de consultoría así como cursos personalizados online. Mírate el siguiente enlace si estás interesado/a.
El Sabio de Excel busca colaborades del blog
Si te interesa compartir tu conocimiento sobre Excel de tal manera que puedas ayudar a otras personas, por favor mándame un email a elsabiodeexcel@gmail.com
Subscríbete y recibe todos las entradas por email
Para ello regístrate suministrando tu email en el sitio "Recibe los trucos en tu email" del menú de la derecha.
Para ello regístrate suministrando tu email en el sitio "Recibe los trucos en tu email" del menú de la derecha.
No hay comentarios:
Publicar un comentario