Ver video: busque el segundo, el tercero o el enésimo valor coincidente
Cuando se trata de buscar datos en Excel, hay dos funciones asombrosas que uso a menudo: BUSCARV e ÍNDICE (principalmente junto con la función COINCIDIR).
Sin embargo, estas fórmulas están diseñadas para buscar solo la primera instancia del valor de búsqueda.
Pero, ¿qué sucede si desea buscar el segundo, tercer, cuarto o enésimo valor?
Bueno, es factible con un poco de trabajo extra.
En este tutorial, le mostraré varias formas (con ejemplos) sobre cómo buscar el segundo o el enésimo valor en Excel.
Busque el segundo, tercer o enésimo valor en Excel
En este tutorial, cubriré dos formas de buscar el segundo o el enésimo valor en Excel:
- Usando una columna de ayuda.
- Usando fórmulas de matriz.
Empecemos y profundicemos.
Usar columna auxiliar
Suponga que es un coordinador de capacitación en una organización y tiene un conjunto de datos como se muestra a continuación. Desea enumerar toda la capacitación frente al nombre de un empleado.
En el conjunto de datos anterior, los empleados recibieron capacitación sobre diferentes herramientas de Microsoft Office (Excel, PowerPoint y Word).
Ahora, puede usar la función VLOOKUP o el combo INDEX / MATCH para encontrar la capacitación que ha completado un empleado. Sin embargo, solo devolverá la primera instancia coincidente.
Por ejemplo, en el caso de John, ha realizado los tres entrenamientos, pero cuando busco su nombre con VLOOKUP o INDEX / MATCH, siempre devolverá 'Excel', que es el primer entrenamiento para su nombre en la lista. .
Para hacer esto, podemos usar una columna auxiliar y crear valores de búsqueda únicos en ella.
Estos son los pasos:
- Inserte una columna antes de la columna que enumera el entrenamiento.
- En la celda B2, ingrese la siguiente fórmula:
= A2 Y CONTAR.SI ($ A $ 2: $ A2, A2)
- En la celda F2, ingrese la siguiente fórmula y copie y pegue para todas las demás celdas:
= IFNA (VLOOKUP ($ E2 & COLUMNS ($ F $ 1: F1), $ B $ 2: $ C $ 14,2,0), "")
La fórmula anterior devolvería la capacitación para cada empleado en el orden en que aparece en la lista. En caso de que no haya una formación para un empleado, devuelve un espacio en blanco.
¿Cómo funciona esta fórmula?
La fórmula CONTAR.SI en la columna auxiliar hace que el nombre de cada empleado sea único al agregarle un número. Por ejemplo, la primera instancia de John se convierte en John1, la segunda instancia se convierte en John2 y así sucesivamente.
La fórmula BUSCARV ahora usa estos nombres de empleados únicos para encontrar la capacitación correspondiente.
Tenga en cuenta que $ E2 & COLUMNS ($ F $ 1: F1) es el valor de búsqueda en la fórmula. Esto agregaría un número al nombre del empleado basado en el número de columna. Por ejemplo, cuando se usa esta fórmula en la celda F2, el valor de búsqueda se convierte en "John1". En la celda G2, se convierte en "John2" y así sucesivamente.
Usando fórmula de matriz
Si no desea modificar el conjunto de datos original agregando columnas auxiliares, también puede usar una fórmula de matriz para buscar el segundo, tercer o enésimo valor.
Suponga que tiene el mismo conjunto de datos que se muestra a continuación:
Aquí está la fórmula que devolverá el valor de búsqueda correcto:
= SI.ERROR (ÍNDICE ($ B $ 2: $ B $ 14, PEQUEÑO (SI ($ A $ 2: $ A $ 14 = $ D2, FILA ($ A $ 2: $ A $ 14) -1, ""), COLUMNAS ($ E $ 1 : E1))), "")
Copie esta fórmula y péguela en la celda E2.
Tenga en cuenta que esta es una fórmula de matriz y debe usar Control + Shift + Enter (mantenga presionadas las teclas Control y Shift y presione la tecla Enter), en lugar de presionar solo la tecla Enter.
Haga clic aquí para descargar el archivo de ejemplo.
¿Cómo funciona esta fórmula?
Dividamos esta fórmula en partes y veamos cómo funciona.
$ A $ 2: $ A $ 14 = $ D2
La parte anterior de la fórmula compara cada celda en A2: A14 con el valor en D2. En este conjunto de datos, verifica si una celda contiene el nombre "John" o no.
Devuelve una matriz de VERDADERO o FALSO. Si la celda tiene el nombre "John", sería Verdadero, de lo contrario, sería Falso.
A continuación se muestra la matriz que obtendría en este ejemplo:
{VERDADERO; FALSO; FALSO; FALSO; FALSO; FALSO; VERDADERO; FALSO; FALSO; FALSO; VERDADERO; FALSO; FALSO}
Tenga en cuenta que tiene VERDADERO en la 1ª, 7ª y 111ª posición, ya que ahí es donde aparece el nombre John en el conjunto de datos.
SI ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””)
La fórmula SI anterior usa la matriz de VERDADERO y FALSO, y reemplaza VERDADERO con la posición de su ocurrencia en la lista (dada por FILA ($ A $ 2: $ A $ 14) -1) y FALSO con “” (espacios en blanco). La siguiente es la matriz resultante que obtiene con esta fórmula SI:
{1;””;””;””;””;””;7;””;””;””;11;””;””}
Tenga en cuenta que 1, 7 y 11 son la posición de aparición de John en la lista.
PEQUEÑO (SI ($ A $ 2: $ A $ 14 = $ D2, FILA ($ A $ 2: $ A $ 14) -1, ””), COLUMNAS ($ E $ 1: E1))
La función PEQUEÑO ahora elige el primer número más pequeño, el segundo más pequeño y el tercero más pequeño de esta matriz. Tenga en cuenta que utiliza la función COLUMNAS para generar el número de columna. En la celda E2, la función COLUMNAS devuelve 1 y la función PEQUEÑA devuelve 1. En la celda F2, la función COLUMNAS devuelve 2 y la función PEQUEÑA devuelve 7.
ÍNDICE ($ B $ 2: $ B $ 14, PEQUEÑO (SI ($ A $ 2: $ A $ 14 = $ D2, FILA ($ A $ 2: $ A $ 14) -1, ””), COLUMNAS ($ E $ 1: E1) ))
La función INDICE ahora devuelve el valor de la lista en la Columna B según la posición devuelta por la función PEQUEÑA. Por lo tanto, en la celda E2, devuelve "Excel", que es el primer elemento en B2: B14. En la celda F2, devuelve PowerPoint, que es el séptimo elemento de la lista.
Dado que hay casos en los que solo hay una o dos capacitaciones para algunos empleados, la función INDICE devolvería un error. La función IFERROR se utiliza para devolver un espacio en blanco en lugar del error.
Tenga en cuenta que en estos ejemplos, he usado referencias de rango. Sin embargo, en ejemplos prácticos, resulta beneficioso convertir los datos en una tabla de Excel. Al convertir en una tabla de Excel, puede utilizar referencias estructuradas, lo que facilita la creación de fórmulas. Además, una tabla de Excel puede contabilizar automáticamente cualquier elemento de entrenamiento nuevo que se agregue a la lista (para que no tenga que ajustar las fórmulas cada vez).
¿Qué hace cuando tiene que buscar el segundo, tercero o enésimo valor? Estoy seguro de que hay más formas de hacer esto. Si usa algo más fácil que el que se enumera aquí, compártalo con todos nosotros en la sección de comentarios.
Haga clic aquí para descargar el archivo de ejemplo.
