Busque el segundo, el tercero o el noveno valor a Excel

Vea el vídeo: busque el segundo, el tercero o el noveno valor coincidente

Cuando se trata de buscar datos a Excel, hay dos funciones sorprendentes que a menudo utilizo: VLOOKUP y INDEX (principalmente junto con la función MATCH).

Sin embargo, estas fórmulas están diseñadas para encontrar sólo la primera instancia del valor de búsqueda.

Pero, ¿qué ocurre si desea buscar el segundo, tercero, cuarto o el valor N.

Bueno, se puede hacer con un poco de trabajo adicional.

En este tutorial, te mostraré varias maneras (con ejemplos) de cómo buscar el segundo o el enésimo valor a Excel.

Busque el segundo, tercero o enésimo valor en Excel

En este tutorial, trataré de dos maneras de buscar el segundo o el valor enésimo a Excel:

  • Mediante una columna auxiliar.
  • Utilizando fórmulas de matriz.

Empezamos y buceamos directamente.

Utilización de la columna Ayudante

Supongamos que usted es un coordinador de formación de una organización y que tiene un conjunto de datos como se muestra a continuación. Desea enumerar toda la formación ante el nombre de un empleado.

Los datos de formación se han definido como búsqueda del segundo o el enésimo valor a Excel

En el conjunto de datos anterior, los empleados han recibido formación sobre diferentes herramientas de Microsoft Office (Excel, PowerPoint y Word).

Ahora puede utilizar la función VLOOKUP o el combinado INDEX / MATCH para encontrar la formación que ha completado un empleado. Sin embargo, sólo 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 volverá “Excel”, que es el primer entrenamiento de su nombre en la lista. .

Para ello, podemos utilizar una columna auxiliar y crear valores de búsqueda únicos.

Estos son los pasos:

  • Inserte una columna antes de la columna que lista la formación.Se ha insertado una columna auxiliar al conjunto de datos
  • En la celda B2, escriba la fórmula siguiente:
    =A2&COUNTIF($A$2:$A2,A2)

fórmula en la columna auxiliar

  • En la celda F2, introduzca la siguiente fórmula y copie pegue para el resto de celdas:
    =IFNA(VLOOKUP($E2&COLUMNS($F$1:F1),$B$2:$C$14,2,0),"")

Fórmula para buscar el segundo tercer y enésimo valor

La fórmula anterior devolvería la formación de cada empleado en el orden que aparece en la lista. En caso de que no se muestre ninguna formación para un empleado, se vuelve en blanco.

¿Cómo funciona esta fórmula?

La fórmula COUNTIF de la columna auxiliar hace que el nombre de cada empleado sea único añadiendo 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 VLOOKUP utiliza ahora estos nombres únicos de empleados para encontrar la formación adecuada.

Tenga en cuenta que $ E2 & COLUMNS ($ F $ 1: F1) es el valor de búsqueda de la fórmula. Esto añadiría un número en el nombre del empleado en función del número de columna. Por ejemplo, cuando se utiliza esta fórmula en la celda F2, el valor de búsqueda se convierte en “John1”. En la celda G2, se convierte en “John2”, etc.

Utilizando la fórmula Array

Si no desea modificar el conjunto de datos original añadiendo columnas auxiliares, también puede utilizar una fórmula de matriz para buscar el segundo, tercer o enésimo valor.

Supongamos que tiene el mismo conjunto de datos que se muestra a continuación:

Los datos de formación se han definido como búsqueda del segundo o el enésimo valor a Excel

Aquí tiene la fórmula que devolverá el valor de búsqueda correcto:

=IFERROR(INDEX($B$2:$B$14,SMALL(IF($A$2:$A$14=$D2,ROW($A$2:$A$14)-1,""),COLUMNS($E$1:E1))),"")

Copie esta fórmula y pegarla en la celda E2.

Tenga en cuenta que se trata de una fórmula de matriz y que debe utilizar Control + Mayús + Intro (mantenga pulsadas las teclas Control y Mayús y pulse la tecla Intro) en lugar de pulsar sólo la tecla Intro.

Haga clic aquí para descargar el archivo de ejemplo.

¿Cómo funciona esta fórmula?

Dividimos 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 de A2: A14 con el valor de D2. En este conjunto de datos, comprueba si una celda contiene o no el nombre “John”.

Devuelve una matriz de TRUE of FALSE. Si la celda tiene el nombre de “John” sería cierto, de lo contrario sería falso.

A continuación se muestra la matriz que obtendría en este ejemplo:

{VERDAD; FALSO; FALSO; FALSO; FALSO; FALSO; VERDAD; FALSO; FALSO; FALSO; VERDAD; FALSO; FALSO}

Tenga en cuenta que tiene TRUE en la 1ª, la 7ª y la 111ª posición, ya que es donde aparece el nombre John al conjunto de datos.

IF ($ A $ 2: $ A $ 14 = $ D2, FILA ($ A $ 2: $ A $ 14) -1, “”)

La fórmula IF anterior utiliza la matriz de TRUE y FALSE y sustituye TRUE por la posición de su aparición en la lista (dada por ROW ($ A $ 2: $ A $ 14) -1) y FALSE para “” (espacios en blanco). La siguiente es la matriz resultante que obtenga con esta fórmula IF:

{1; “”; “”; “”; “”; “”; 7; “”; “”; “”; 11; “”; “”}

Tenga en cuenta que 1, 7 y 11 son la posición de ocurrencia 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 SMALL selecciona ahora el primer número más pequeño, el segundo más pequeño y el tercer número más pequeño de esta matriz. Tenga en cuenta que utiliza la función COLUMNS para generar el número de columna. En la celda E2, la función COLUMNS devuelve 1 y la función PEQUEÑO devuelve 1. En la celda F2, la función COLUMNS devuelve 2 y la función PEQUEÑO 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 INDEX ahora devuelve el valor de la lista de la columna B en función de la posición devuelta por la función SMALL. Por lo tanto, en la celda E2, devuelve “Excel”, que es el primer elemento de B2: B14. En la celda F2, devuelve PowerPoint, que es el séptimo elemento de la lista.

Como hay casos en los que sólo hay una o dos capacitaciones para algunos empleados, la función INDEX devolvería un error. La función IFERROR utiliza para volver un blanco en lugar del error.

Tenga en cuenta que en estos ejemplos he utilizado referencias de rango. Sin embargo, en ejemplos prácticos, es beneficioso convertir los datos en una tabla de Excel. En convertirlos 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 tener en cuenta automáticamente cualquier elemento de formación nuevo que se añada a la lista (por lo que no debe modificar las fórmulas cada vez).

Qué haga cuando tenga que buscar el segundo, tercero o el valor N? Estoy seguro de que hay más maneras de hacerlo. Si utiliza algo más fácil que la que se muestra aquí, compartir con todos en la sección de comentarios.

Haga clic aquí para descargar el archivo de ejemplo.

También te pueden gustar los siguientes tutoriales de Excel:

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *