En este artículo, aprenderemos cómo buscar varias instancias de un valor en Excel.
¿Buscar valores usando la opción desplegable?
Aquí entendemos cómo podemos buscar diferentes resultados usando la fórmula de matriz de la función INDICE. Simplemente seleccione el valor de la lista y el resultado correspondiente estará allí.
Fórmula genérica
| {= INDEX (matriz, SMALL (IF (lookup_value = lookup_value_range, ROW (lookup_value_range) -ROW (primera celda de lookup_value_range) +1), ROW (1: 1)))} |
Formación: El rango desde el que desea obtener los datos.
valor de búsqueda: Su lookup_value que desea filtrar.
rango_valor_buscado: El rango en el que desea filtrar lookup_value.
La primera celda en el rango lookup_value: si su rango de lookup_value es $ A $ 5: $ A $ 100, entonces es $ A $ 5.
Importante: Todo debería ser absoluto referenciado. valor de búsqueda Puede ser relativo según el requisito.
Introdúzcalo como una fórmula de matriz. Después de escribir la fórmula, use la tecla CTRL + MAYÚS + ENTRAR para convertirla en una fórmula de matriz.
Ejemplo de búsqueda de varios resultados
Tengo estos datos de estudiantes en el rango A2: E14. En la celda G1, tengo un menú desplegable de valores de región, p. Ej. Central, Este, Norte, Sur y Oeste. Ahora quiero, cualquiera que sea la región que tenga en G1, una lista de todos los estudiantes de esa región debería mostrarse en la columna H.
Para buscar varios valores en Excel, identifiquemos nuestras variables.
Formación: $ C $ 2: $ C $ 14
valor de búsqueda : $ G $ 1
rango_valor_buscado: $ A $ 2: $ A $ 14
La primera celda del rango lookup_value: $ A $ 2
Según los datos anteriores, nuestra fórmula para recuperar múltiples valores en Excel será:
| {= SIERROR (ÍNDICE ($ C $ 2: $ C $ 14, PEQUEÑO (SI ($ G $ 1 = $ A $ 2: $ A $ 14, FILA ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1) , FILA (1: 1))), "No hay más valor")} |
Esta es una fórmula de matriz. No use solo Enter después de escribir la fórmula. Use CTRL + MAYÚS + ENTRAR juntos.
Ahora entendamos CÓMO FUNCIONA.
Aunque la fórmula puede parecer compleja, la idea es simple. Necesitamos obtener el número de índice de cada ocurrencia de valor, luego recuperar los valores usando la función INDICE de Excel.
Por lo tanto, el principal desafío es obtener una matriz de números de índice de valor de búsqueda. Para obtener números de índice, usamos las funciones SI y FILA. La fórmula es realmente compleja en conjunto, vamos a desglosarla.
Queremos obtener valores de la columna del estudiante, por lo que nuestra matriz para la función INDICE es $ C $ 2: $ C $ 14.
Ahora necesitamos dar los números de fila de $ A $ 2: $ A $ 14 (valor de búsqueda) en el que existe el valor de G1 (por ahora, digamos G1 tiene una central en ella).
SI ($ G $ 1 = $ A $ 2: $ A $ 14, FILA ($ A $ 2: $ A $ 14): Ahora, esta parte devuelve el número de fila si el valor de G1 de un recuento de celda (central) en el rango $ A $ 2: $ A $ 14 más regresa FALSO. En este ejemplo, devolverá
{2; FALSO; FALSO; FALSO; FALSO; 7; 8; FALSO; FALSO; 11; FALSO; FALSO; FALSO}.
Ahora, dado que la matriz anterior contiene números de fila de la 1ª fila (1: 1) y necesitamos filas a partir de nuestra matriz (A2: A14). Para hacerlo, usamos -ROW ($ A $ 2) +1 en la fórmula SI. Esto devolverá un número de filas antes de comenzar nuestra matriz.
Para este ejemplo, es -1. Si comenzara desde A3, devolvería -2 y así sucesivamente. Este número se restará de cada número en la matriz devuelta por IF. Así que finalmente SI ($ G $ 1 = $ A $ 2: $ A $ 14, FILA ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1) esto se traducirá en {1; FALSO; FALSO; FALSO; FALSO;6;7; FALSO; FALSO;10; FALSO; FALSO; FALSO}.
A continuación, esta matriz está rodeada por la función PEQUEÑA. Esta función devuelve el enésimo valor más pequeño de la matriz dada. Ahora, tenemos PEQUEÑAS ({2; FALSO; FALSO; FALSO; FALSO;7;8; FALSO; FALSO;11; FALSO; FALSO; FALSO}, FILA (1: 1)). FILA (1: 1) devolverá 1. Por lo tanto, la función anterior devolverá el primer valor más pequeño de la matriz, que es 2.
Cuando copie esta fórmula en las siguientes celdas, FILA (1: 1) se convertirá en FILA (2: 2) y devolverá el segundo valor más pequeño en la matriz, que es 7 y así sucesivamente. Esto permite que la función devuelva primero el primer valor encontrado. Pero si primero desea obtener el último valor encontrado, utilice la función GRANDE en lugar de la función PEQUEÑA.
Ahora, utilizando los valores devueltos por encima de las funciones, la función INDICE devuelve fácilmente cada valor coincidente de un rango.
También puede realizar búsquedas de coincidencias exactas utilizando la función INDICE y COINCIDIR en Excel. Obtenga más información sobre cómo realizar una búsqueda sensible a mayúsculas y minúsculas mediante la función INDICE Y COINCIDIR en Excel. También puede buscar las coincidencias parciales utilizando los comodines en Excel.
Espero que este artículo sobre cómo buscar varias instancias de un valor en Excel sea explicativo. Encuentre más artículos sobre el cálculo de valores y fórmulas de Excel relacionadas aquí. Si te gustaron nuestros blogs, compártelo con tus amigos en Facebook. Y también puedes seguirnos en Twitter y Facebook. Nos encantaría saber de usted, háganos saber cómo podemos mejorar, complementar o innovar nuestro trabajo y hacerlo mejor para usted. Escríbanos al sitio de correo electrónico.
Utilice BUSCARV de dos o más tablas de búsqueda | Para buscar desde varias tablas, podemos adoptar un enfoque IFERROR. Para buscar desde varias tablas, se toma el error como un cambio para la siguiente tabla. Otro método puede ser un enfoque If.
Cómo hacer una búsqueda sensible a mayúsculas y minúsculas en Excel | La función BUSCARV de Excel no distingue entre mayúsculas y minúsculas y devolverá el primer valor coincidente de la lista. INDEX-MATCH no es una excepción, pero se puede modificar para que distinga entre mayúsculas y minúsculas. Veamos cómo …
Búsqueda de texto que aparece con frecuencia con criterios en Excel | La búsqueda aparece con mayor frecuencia en el texto en un rango que usamos la función INDEX-MATCH con MODE. Este es el método.
Articulos populares :
Cómo usar la función SI en Excel : La declaración IF en Excel verifica la condición y devuelve un valor específico si la condición es VERDADERA o devuelve otro valor específico si es FALSO.
Cómo utilizar la función BUSCARV en Excel : Esta es una de las funciones más utilizadas y populares de Excel que se utiliza para buscar valores de diferentes rangos y hojas.
Cómo utilizar la función SUMIF en Excel : Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.
Cómo usar la función CONTAR.SI en Excel : Cuente valores con condiciones usando esta asombrosa función. No necesita filtrar sus datos para contar valores específicos. La función Countif es esencial para preparar su tablero.