Excel

Cómo buscar la primera y la última coincidencia

How Lookup First

Uno de los aspectos más confusos de las funciones de búsqueda en Excel es comprender cómo obtener la primera o la última coincidencia en un conjunto de datos con más de una coincidencia. Esto se debe a que el comportamiento de Excel cambia según dos factores: si está realizando una coincidencia exacta o aproximada y si los datos están ordenados o no.

Por ejemplo, digamos que usamos BUSCARV para buscar el precio de 'verde' en los datos a continuación. ¿Qué precio obtendremos?



BUSCARV ¿qué partido obtendremos?



Siga leyendo para obtener la respuesta y más ejemplos interesantes.

Notas:



Coincidencia exacta = primero

Al hacer una coincidencia exacta, siempre obtendrá la primera coincidencia, punto. No importa si los datos están ordenados o no. En la siguiente pantalla, el valor de búsqueda en E5 es 'rojo'. los Función BUSCARV , en el modo de coincidencia exacta, devuelve el precio de la primera coincidencia:

|_+_|

La coincidencia exacta BUSCARV encuentra la primera coincidencia

Observe que el último argumento en BUSCARV es FALSO para forzar la coincidencia exacta.



Coincidencia aproximada = último

Si está haciendo una coincidencia aproximada, y los datos se ordenan por valor de búsqueda , obtendrás el último partido. ¿Por qué? Debido a que durante una coincidencia aproximada, Excel escanea los valores hasta que se encuentra un valor mayor que el valor de búsqueda, luego 'retrocede' al valor anterior.

En la siguiente pantalla, BUSCARV está configurado para aproximarse al modo de coincidencia y los colores están ordenados. BUSCARV devuelve el precio del último 'verde':

cómo crear una distribución de frecuencia en Excel
|_+_|

VLOOKUP aproximada coincidencia encuentra la última coincidencia

Observe que el último argumento en BUSCARV es VERDADERO para una coincidencia aproximada.

Coincidencia aproximada + datos sin clasificar = peligro

Con búsquedas de coincidencias aproximadas estándar, los datos deben ordenarse por valor de búsqueda . Con datos sin clasificar, es posible que vea resultados de apariencia normal que son totalmente incorrectos. Este problema es más probable con BUSCARV porque VLOOKUP tiene como valor predeterminado una coincidencia aproximada cuando no se proporciona un cuarto argumento.

Para ilustrar este problema, consulte el siguiente ejemplo. Los datos son sin clasificar y BUSCARV, sin un cuarto argumento proporcionado, tiene como valor predeterminado una coincidencia aproximada. Tenga en cuenta que no hay 'rojo' con un precio de $ 17,00, pero VLOOKUP devuelve felizmente este resultado no válido:

|_+_|

Ejemplo de VLOOKUP aproximada coincidencia resultado incorrecto

Por esta razón, recomiendo configurar siempre el último argumento para BUSCARV explícitamente: VERDADERO = coincidencia aproximada, FALSO = coincidencia exacta. El argumento es opcional, pero proporcionar un valor te hace pensar en ello y proporciona un recordatorio visual en el futuro.

Veremos cómo superar el problema del último partido y los datos sin clasificar a continuación.

Coincidencia aproximada 'normal'

En este punto, es posible que se sienta un poco confundido y desorientado acerca de la idea de que la coincidencia aproximada puede devolver la última coincidencia en algunos casos. Si es así, no se preocupe. Usar una coincidencia aproximada para obtener la última coincidencia no es el caso 'normal'. Por lo general, verá una coincidencia aproximada utilizada para asignar valores de acuerdo con algún tipo de escala. Un ejemplo clásico es el uso de BUSCARV en modo de coincidencia aproximada para asignar calificaciones, que funciona de maravilla:

cómo hacer un hipervínculo en Excel
|_+_|

VLOOKUP coincidencia aproximada para asignar calificaciones

En casos como este, la tabla de búsqueda deliberadamente no incluye valores duplicados, por lo que toda la idea de 'último valor coincidente' es irrelevante. Más detalles sobre esta fórmula aquí .

La información anterior es para proporcionar antecedentes y contexto sobre cómo funcionan las coincidencias en Excel, de modo que los enfoques descritos a continuación tengan sentido.

Aplicaciones prácticas

¿Cómo podemos utilizar el comportamiento descrito anteriormente en una situación práctica? Bueno, un escenario común es buscar la entrada 'última' o 'última' de un artículo. Por ejemplo, a continuación, usamos BUSCARV en modo de coincidencia aproximada para encontrar el último precio de Sandals. Observe que los datos se ordenan por artículo, luego por fecha, por lo que el último precio de un artículo determinado aparece al final:

|_+_|

VLOOKUP coincidencia aproximada + datos ordenados = último precio

ÍNDICE y PARTIDO

También se pueden usar otras funciones de búsqueda de esta manera. A continuación, estamos usando un equivalente ÍNDICE y PARTIDO fórmula encuentra el último precio con los mismos datos. Note MATCH está configurado para aproximarse a la coincidencia de los elementos ordenados en orden ascendente estableciendo el tercer argumento en 1:

|_+_|

ÍNDICE y PARTIDO aproximados para encontrar el último precio

Función BUSCAR

los Función BUSCAR también se puede utilizar en este caso. BUSCAR siempre realiza una coincidencia aproximada, por lo que funciona bien en escenarios de 'última coincidencia'. La fórmula es bastante simple:

|_+_|

Función BUSCAR para encontrar el último precio

Última coincidencia con datos sin clasificar

¿Qué sucede si desea la última coincidencia, pero los datos no están ordenados por valor de búsqueda? En otras palabras, ¿desea aplicar criterios para encontrar una coincidencia y simplemente desea el último elemento de los datos que coincida con sus criterios? En realidad, este es un caso en el que la función LOOKUP brilla, porque LOOKUP puede manejar operaciones de matriz de forma nativa, sin control + shift + enter. Esto significa que podemos construir dinámicamente una matriz de búsqueda para ubicar los datos que queremos usando expresiones lógicas simples.

Por ejemplo, eche un vistazo a la siguiente fórmula:

|_+_|

Función BÚSQUEDA para encontrar la última coincidencia con datos no clasificados

Esta fórmula encuentra el último precio de Sandals en sin clasificar datos.

Es posible que no haya visto una fórmula como esta antes, así que vamos a dividirla en pasos. Trabajando de adentro hacia afuera, primero aplicamos los criterios con una expresión lógica simple:

|_+_|

Esto resulta en una formación de valores VERDADERO y FALSO, donde VERDADERO corresponde a elementos que son 'sandalias' y FALSO corresponde a todos los demás valores:

|_+_|

A continuación, dividimos el número 1 por esta matriz. Durante la división, VERDADERO se convierte en 1 y FALSO se convierte en cero, por lo que debe visualizar la operación así:

|_+_|

Uno dividido por uno es uno, y uno dividido por cero es # DIV / 0, por lo que el resultado es otra matriz, esta contiene solo 1s y # DIV / 0 errores:

|_+_|

No te preocupes, hay un método para esta locura :)

Excel nombre de la pestaña de la referencia de celda

Ahora, puede haber notado que el valor de búsqueda es el número 2. Esto puede parecer desconcertante. ¿Cómo encontrará LOOKUP alguna vez el número 2 en una matriz que contiene solo 1 y errores? No lo hará. Estamos usando 2 como valor de búsqueda para forzar a LOOKUP a escanear al fin de los datos .

La función BÚSQUEDA ignorará automáticamente los errores, por lo que lo único que queda por hacer coincidir son los 1. Explorará los 1 en busca de un 2 que nunca se encontrará. Cuando llega al final de la matriz, 'retrocederá' al último valor válido, el último 1, que corresponde a la última coincidencia según los criterios proporcionados.

Versión de matriz INDEX y MATCH

La belleza del Función BUSCAR es que puede manejar la operación de matriz descrita anteriormente de forma nativa, sin necesidad de que ingrese como un fórmula de matriz con control + shift + enter. Sin embargo, ciertamente puede usar una fórmula de matriz si lo desea. Aquí está la fórmula equivalente de INDICE y COINCIDIR, que debe ingresarse con control + shift + enter:

|_+_|

Última celda que no está en blanco

El enfoque anterior resulta realmente útil. Por ejemplo, ajustando un poco la lógica, podemos hacer cosas como encontrar la última celda no vacía en una columna:

|_+_|

Usando BUSCAR para encontrar la última celda que no está en blanco

Esta frmula es descrito con más detalle aquí .

¿Búsqueda enésima coincidencia? ¿Todos los partidos?

Si ha llegado hasta aquí, es posible que se pregunte cómo encontraría la segunda o tercera coincidencia, o cómo recuperaría todas las coincidencias. Aquí tienes algunos enlaces:

Notarás que fórmulas como esta se complican. Hay algunas funciones nuevas e interesantes que llegarán a Excel en 2019 que harán que estas soluciones sean mucho más simples. Manténganse al tanto. Mientras tanto, no olvides eso Tablas dinamicas son una excelente manera de explorar datos sin fórmulas .

¿Que sigue?

A continuación, se muestran guías para obtener más información sobre las fórmulas de Excel. También ofrecemos capacitación en video en línea .

Autor Dave Bruns


^