Use INDEX y MATCH para consultas de bases de datos simples en Excel

Originalmente, Excel no fue diseñado para ser una base de datos real. Sus primeras funciones de base de datos estaban limitadas en cantidad y calidad. Y debido a que cada registro en una base de datos Excel es visible en la pantalla a la vez, lo que significa todo en la memoria a la vez, las bases de datos de Excel deben ser realmente pequeñas: campos múltiples con pocos registros o pocos campos con muchos registros; y cálculos mínimos.

VLOOKUP (vertical) y HLOOKUP (horizontal) fueron las únicas funciones disponibles para consultar una base de datos para obtener información específica. Por ejemplo, puede consultar para buscar y extraer todos los registros que contengan ventas mayores de $ 1000 pero menos de $ 5000, pero solo en archivos planos (solo una matriz de base de datos). Se desarrollaron

tablas dinámicas para que los usuarios pudieran crear bases de datos relacionales, que más fácil de consultar, usar menos memoria y proporcionar resultados más precisos. Sin embargo, si no tiene o necesita una base de datos relacional, pero requiere funciones de base de datos más potentes y confiables, intente esto para empezar.

[Lectura adicional: Su nueva PC necesita estos 15 programas gratuitos, excelentes]

Coincidencia de índice, coincidencia e índice

En Excel, la función ÍNDICE devuelve un ítem de una posición específica (en una lista, tabla, base de datos).

La función COINCIDIR devuelve la posición de un valor (en una lista) , tabla, base de datos). Y, las funciones INDEX-MATCH juntas, hacen que la extracción de datos de una tabla sea muy fácil.

La sintaxis de la función INDEX es: INDEX (matriz, número de fila, [columna_num]). La matriz es el rango de celdas con el que estás trabajando. Row_num es, obviamente, el número de fila en el rango que contiene los datos que estás buscando. Column_num es el número de columna en el rango que contiene los datos que está buscando. La fórmula INDEX no reconoce las letras de las columnas, por lo que debe usar números (contados desde la izquierda).

La sintaxis de la función MATCH es: MATCH (lookup_value, lookup_array, [match_type]). Lookup_value es el número o texto que está buscando, que puede ser un valor, un valor lógico o una referencia de celda. Lookup_array es el rango de celdas con el que estás trabajando. Match_type determina la función MATCH, es decir, una coincidencia exacta o la coincidencia más cercana.

A. Función INDEX

En nuestro ejemplo, el famoso comodoro James Norrington tiene una hoja de cálculo que rastrea todos los barcos piratas en el Caribe. La lista de Norrington está organizada por las formaciones de combate de los barcos, que coinciden con sus cartas náuticas de la zona. Cuando ve un barco avanzando, ingresa la fórmula del índice en su hoja de cálculo, para poder identificar el barco y sus capacidades. En esta primera consulta, Norrington quiere saber el tipo de barco que avanza.

1. Seleccione una ubicación (celda o rango de celdas) para sus consultas (es decir, funciones y resultados), luego mueva el cursor a esa celda. Por ejemplo: cualquier celda en la fila 18.

2. Ingrese la función ÍNDICE (precedida por el signo igual), más un paréntesis de apertura, luego resalte (o escriba) el rango de la base de datos / tabla como este: = ÍNDICE (A2: I16

Nota: Si desea una referencia absoluta (que , en este caso, significa codificar la fórmula para que cuando / si se copia, el rango no se modifique), presione F4 una vez después de cada referencia de celda. También puede resaltar el rango: simplemente presione F4 una vez después de seleccionar el rango completo , y se agregan los símbolos de referencia absoluta completos.

3. A continuación, ingrese una coma para separar los argumentos (es decir, las piezas de fórmula separadas); luego, ingrese el número de fila y una coma, seguido del número de columna (sí , debe ser un número y no la letra de la columna habitual) y el paréntesis derecho (o simplemente presione Entrar y deje que Excel agregue el paréntesis final). La fórmula completa tiene el siguiente aspecto: = ÍNDICE ($ A $ 2: $ I $ 16 , 15,2).

Nota: La numeración de las filas comienza con el primer número del rango, no con el primer número en la hoja de cálculo. Por ejemplo, la víspera Aunque el barco pirata Cavalleria está en la fila 16 de Excel, en realidad es la fila 15 en nuestra fórmula porque nuestro rango comienza en A2 y pasa por I16. Si A2 es la fila 1, entonces A16 es la fila 15).

4. Tenga en cuenta que el tipo de barco que Norrington estaba buscando es un War Sloop.

JD Sartain

Use la función de índice para buscar información específica en su base de datos.

B. Rangos de índices

Ahora, podemos hacer mucho más con esta tabla de base de datos. No necesita redefinir el rango cada vez que quiera saber algo. Para hacerlo más fácil, definiremos el rango una vez y luego le daremos un nombre. Entonces podemos simplemente poner el nombre del rango en nuestras fórmulas.

1. Vaya a A2 y resalte el rango de A2 a I16.

2. En la pestaña Fórmulas, seleccione Definir nombre del grupo Nombres definidos.

3. En el cuadro de diálogo emergente, ingrese un nombre para su rango en el cuadro del campo Nombre.

4. Luego, ingrese el Alcance (donde se encuentra el rango), que es el Libro de trabajo o una de las hojas de trabajo en el Libro de trabajo.

5. Ingrese un comentario, si es necesario.

6. Y, por último, verifique que el campo "Refiere a" muestre el nombre y el rango correctos, luego haga clic en OK .

7. Si desea verificar que su rango esté, de hecho, guardado en Excel, intente con esta pequeña prueba: presione Ctrl + G (el comando Ir a). Seleccione Envíos en el cuadro de diálogo Ir a, luego haga clic en Aceptar , y Excel vuelve a resaltar el rango A2: I16.

Cómo definir y guardar un rango

C. ÍNDICE con fórmulas SUMA & MEDIA

Norrington está evaluando las capacidades de batalla de la flota. Primero quiere saber cuántos cañones tienen los piratas, el número promedio de cañones por barco y el número total de tripulantes que tripulan todos esos barcos piratas. Introduce las siguientes fórmulas:

1. = SUMA (ÍNDICE (Barcos, 8)) es igual a 334, el número total de cañones, y

2. = PROMEDIO (ÍNDICE (Barcos, 8)) es igual a 22.27, o aproximadamente 22.27 cañones por barco.

3. = SUMA (ÍNDICE (Barcos, 7)) es igual a 2350, el número total de tripulantes en todos los barcos.

¿Por qué hay una coma, espacio, coma entre los barcos y el número 8, y qué significan estos números? Ships es el Rango (seguido de una coma), el argumento de Fila está en blanco (o un espacio) porque Norrington quiere todas las filas, y el 8 representa la 8va columna (que es la columna H, Cañones).

Algunos podrían preguntar , ¿por qué no simplemente ingresas las fórmulas SUMA y / o PROMEDIO en la parte inferior de esas columnas? En esta pequeña hoja de cálculo, sí, sería igual de fácil. Pero si la hoja de cálculo tiene 5000 filas y 300 columnas, querrá usar INDEX

JD Sartain

03 fórmulas INDEX usando SUM y AVERAGE.

Una vez que el rango recibe su nombre, Norrington puede abrir una hoja de cálculo en blanco este mismo libro de trabajo y escribe sus consultas (fórmulas) en la columna B (que muestran los resultados en lugar de las fórmulas) con una descripción que define esas consultas en la columna A. (Nota: la columna C muestra las fórmulas reales que están en la columna B).

No tiene que ver visualmente su enorme base de datos de 5000 registros o esperar varios segundos mientras calculan las fórmulas. Él puede obtener toda la información que necesita de su Hoja de consulta. Recuerde, cuanto más grande sea la hoja de cálculo, más lento funcionará, especialmente si hay muchas fórmulas.

JD Sartain

04 La información del barco pirata James Norrington.

D PARTIDO DE ÍNDICES con MAX

Ahora, Norrington quiere saber cuántos piratas hay en el barco más poblado y de qué barco se trata. Utiliza la fórmula ÍNDICE con MAX para obtener la mayor cantidad de piratas, pero también necesita saber qué nave los transporta. Entonces usa la fórmula INDEX / MATCH con MAX para descubrir qué nave tiene la mayor cantidad de piratas a bordo.

1. = MAX (ÍNDICE (Barcos, 7)) es igual a 300, el mayor número de piratas en uno de los barcos

2. = INDICE ($ A $ 2: $ A $ 16, PARTIDO (MAX (Barcos), $ G $ 2: G $ 16, 0)) es igual al Royal James, el barco con la mayor cantidad de piratas a bordo de

3. = INDICE ($ F $ 2: $ F $ 16, MATCH (MAX (Barcos), $ G $ 2: G $ 16, 0)) es igual a Stede Bonnet, Capitán del Royal James con tripulación pirata de 300

JD Sartain

Use INDEX-MATCH y MAX para recuperar información específica de su base de datos.