Función INDICE de Excel (ejemplos + video)
Cuándo usar la función ÍNDICE de Excel
La función ÍNDICE de Excel se puede utilizar cuando desea obtener el valor de un dato tabular y tiene el número de fila y el número de columna del punto de datos. Por ejemplo, en el siguiente ejemplo, puede usar la función INDICE para obtener las marcas de "Tom" en Física cuando conoce el número de fila y el número de columna en el conjunto de datos.
Lo que devuelve
Devuelve el valor de una tabla para el número de fila y el número de columna especificados.
Sintaxis
= INDICE (matriz, núm_fila, [núm_columna])
= INDICE (matriz, núm_fila, [núm_columna], [núm_área])
La función INDICE tiene 2 sintaxis. El primero se usa en la mayoría de los casos, sin embargo, en el caso de búsquedas de tres vías, se usa el segundo (cubierto en el Ejemplo 5).
Argumentos de entrada
- matriz - a rango de celdas o una matriz constante.
- fila_num - el número de fila desde el que se obtendrá el valor.
- [col_num] - el número de columna desde el que se obtendrá el valor. Aunque este es un argumento opcional, pero si no se proporciona row_num, es necesario proporcionarlo.
- [area_num] - (Opcional) Si el argumento de la matriz está formado por varios rangos, este número se usaría para seleccionar la referencia de todos los rangos.
Notas adicionales (cosas aburridas … pero es importante saberlo)
- Si el número de fila o el número de columna es 0, devuelve los valores de toda la fila o columna respectivamente.
- Si la función INDICE se usa delante de una referencia de celda (como A1 :), devuelve una referencia de celda en lugar de un valor (vea los ejemplos a continuación).
- Más utilizado junto con la función COINCIDIR.
- A diferencia de BUSCARV, la función INDICE puede devolver un valor desde la izquierda del valor de búsqueda.
- La función INDICE tiene dos formas: la forma de matriz y la forma de referencia
- "Forma de matriz" es donde se obtiene un valor basado en el número de fila y columna de una tabla determinada.
- "Formulario de referencia" es donde hay varias tablas, y usa el argumento area_num para seleccionar la tabla y luego obtener un valor dentro de ella usando el número de fila y columna (vea el ejemplo en vivo a continuación).
Función INDICE de Excel: ejemplos
Aquí hay seis ejemplos del uso de la función INDICE de Excel.
Ejemplo 1: encontrar las marcas de Tom en física (búsqueda bidireccional)
Suponga que tiene un conjunto de datos como se muestra a continuación:
Para encontrar las calificaciones de Tom en Física, use la siguiente fórmula:
= ÍNDICE ($ B $ 3: $ E $ 10,3,2)
Esta fórmula de ÍNDICE especifica la matriz como $ B $ 3: $ E $ 10 que tiene las marcas para todos los sujetos. Luego usa el número de fila (3) y el número de columna (2) para obtener las calificaciones de Tom en Física.
Ejemplo 2: hacer que el valor de BÚSQUEDA sea dinámico mediante la función COINCIDIR
Puede que no siempre sea posible especificar el número de fila y el número de columna manualmente. Es posible que tenga un gran conjunto de datos o que desee hacerlo dinámico para que identifique automáticamente el nombre y / o el tema especificado en las celdas y proporcione el resultado correcto.
Algo como se muestra a continuación:
Esto se puede hacer usando una combinación de las funciones INDICE y COINCIDIR.
Aquí está la fórmula que hará que los valores de búsqueda sean dinámicos:
= ÍNDICE ($ B $ 3: $ E $ 10, PARTIDO ($ G $ 5, $ A $ 3: $ A $ 10,0), PARTIDO ($ H $ 4, $ B $ 2: $ E $ 2,0))
En la fórmula anterior, en lugar de codificar el número de fila y el número de columna, se usa la función COINCIDIR para hacerla dinámica.
- El número de fila dinámico viene dado por la siguiente parte de la fórmula: PARTIDA ($ G $ 5, $ A $ 3: $ A $ 10,0). Escanea el nombre de los estudiantes e identifica el valor de búsqueda ($ G $ 5 en este caso). Luego, devuelve el número de fila del valor de búsqueda en el conjunto de datos. Por ejemplo, si el valor de búsqueda es Matt, devolverá 1, si es Bob, devolverá 2 y así sucesivamente.
- El número de columna dinámica viene dado por la siguiente parte de la fórmula: COINCIDIR ($ H $ 4, $ B $ 2: $ E $ 2,0). Escanea los nombres de los sujetos e identifica el valor de búsqueda ($ H $ 4 en este caso). Luego, devuelve el número de columna del valor de búsqueda en el conjunto de datos. Por ejemplo, si el valor de búsqueda es Math, devolverá 1, si es Physics, devolverá 2 y así sucesivamente.
Ejemplo 3: uso de listas desplegables como valores de búsqueda
En el ejemplo anterior, tenemos que ingresar manualmente los datos. Eso podría llevar mucho tiempo y ser propenso a errores, especialmente si tiene una gran lista de valores de búsqueda.
Una buena idea en tales casos es crear una lista desplegable de los valores de búsqueda (en este caso, podrían ser los nombres de los estudiantes y las materias) y luego simplemente elegir de la lista. Según la selección, la fórmula actualizaría automáticamente el resultado.
Algo como se muestra a continuación:
Esto es un buen componente de tablero, ya que puede tener un gran conjunto de datos con cientos de estudiantes en el back-end, pero el usuario final (digamos un maestro) puede obtener rápidamente las calificaciones de un estudiante en una materia simplemente haciendo las selecciones de el desplegable.
Cómo hacer esto:
La fórmula utilizada en este caso es la misma utilizada en el Ejemplo 2.
= ÍNDICE ($ B $ 3: $ E $ 10, PARTIDO ($ G $ 5, $ A $ 3: $ A $ 10,0), PARTIDO ($ H $ 4, $ B $ 2: $ E $ 2,0))
Los valores de búsqueda se han convertido en listas desplegables.
Estos son los pasos para crear la lista desplegable de Excel:
- Seleccione la celda en la que desea la lista desplegable. En este ejemplo, en G4, queremos los nombres de los estudiantes.
- Vaya a Datos -> Herramientas de datos -> Validación de datos.
- En el cuadro de diálogo Validación de datos, dentro de la pestaña de configuración, seleccione Lista en el menú desplegable Permitir.
- En la fuente, seleccione $ A $ 3: $ A $ 10
- Haga clic en Aceptar.
Ahora tendrá la lista desplegable en la celda G5. Del mismo modo, puede crear uno en H4 para los sujetos.
Ejemplo 4: valores devueltos de una fila / columna completa
En los ejemplos anteriores, usamos la función ÍNDICE de Excel para realizar una búsqueda bidireccional y obtener un valor único.
Ahora, ¿qué pasa si quieres obtener todas las notas de un estudiante? Esto puede permitirle encontrar la puntuación máxima / mínima de ese estudiante, o la puntuación total obtenida en todas las materias.
En inglés simple, primero desea obtener la fila completa de puntajes de un estudiante (digamos Bob) y luego, dentro de esos valores, identificar el puntaje más alto o el total de todos los puntajes.
Aquí está el truco.
En la función ÍNDICE de Excel, cuando ingresa el número de columna como 0, devolverá los valores de toda esa fila.
Entonces la fórmula para esto sería:
= ÍNDICE ($ B $ 3: $ E $ 10, PARTIDA ($ G $ 5, $ A $ 3: $ A $ 10,0), 0)
Ahora esta fórmula. si se usa como está, devolvería el # ¡VALOR! error. Mientras muestra el error, en el backend, devuelve una matriz que tiene todas las puntuaciones de Tom: {57,77,91,91}.
Si selecciona la fórmula en el modo de edición y presiona F9, podrá ver la matriz que devuelve (como se muestra a continuación): 
De manera similar, según el valor de búsqueda, cuando el número de columna se especifica como 0 (o se deja en blanco), devuelve todos los valores en la fila para el valor de búsqueda.
Ahora, para calcular el puntaje total obtenido por Tom, simplemente podemos usar la fórmula anterior dentro de la función SUMA.
= SUMA (ÍNDICE ($ B $ 3: $ E $ 10, PARTIDA ($ G $ 5, $ A $ 3: $ A $ 10,0), 0))
En líneas similares, para calcular la puntuación más alta, podemos usar MAX / LARGE y para calcular el mínimo, podemos usar MIN / SMALL.
Ejemplo 5: búsqueda de tres vías usando INDEX / MATCH
La función ÍNDICE de Excel está diseñada para manejar búsquedas de tres vías.
¿Qué es una búsqueda de tres vías?
En los ejemplos anteriores, usamos una tabla con puntajes para estudiantes en diferentes materias. Este es un ejemplo de una búsqueda bidireccional, ya que usamos dos variables para obtener la puntuación (el nombre del alumno y la asignatura).
Ahora, supongamos que en un año, un estudiante tiene tres niveles diferentes de exámenes, prueba de unidad, examen parcial y examen final (eso es lo que tenía cuando era estudiante).
Una búsqueda de tres vías sería la capacidad de obtener las calificaciones de un estudiante para una materia específica del nivel de examen especificado. Esto lo convertiría en una búsqueda de tres vías, ya que hay tres variables (nombre del estudiante, nombre de la asignatura y nivel de examen).
A continuación, se muestra un ejemplo de una búsqueda de tres vías:
En el ejemplo anterior, además de seleccionar el nombre del estudiante y el nombre de la asignatura, también puede seleccionar el nivel de examen. Según el nivel del examen, devuelve el valor coincidente de una de las tres tablas.
Aquí está la fórmula utilizada en la celda H4:
= ÍNDICE (($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23), PARTIDA ($ G $ 4, $ A $ 3: $ A $ 7,0), PARTIDA ($ H $ 3, $ B $ 2: $ E $ 2,0), SI ($ H $ 2 = "Prueba unitaria", 1, SI ($ H $ 2 = "Medio término", 2,3)))
Analicemos esta fórmula para comprender cómo funciona.
Esta fórmula tiene cuatro argumentos. ÍNDICE es una de esas funciones en Excel que tiene más de una sintaxis.
= INDICE (matriz, núm_fila, [núm_columna])
= INDICE (matriz, núm_fila, [núm_columna], [núm_área])
Hasta ahora, en todo el ejemplo anterior, hemos usado la primera sintaxis, pero para hacer una búsqueda de tres vías, necesitamos usar la segunda sintaxis.
Ahora veamos cada parte de la fórmula según la segunda sintaxis.
- array - ($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23): En lugar de usar un solo arreglo, en este caso, hemos usado tres arreglos entre paréntesis.
- row_num - MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0): La función MATCH se usa para encontrar la posición del nombre del estudiante en la celda $ G $ 4 en la lista de nombres del estudiante.
- col_num - COINCIDIR ($ H $ 3, $ B $ 2: $ E $ 2,0): La función COINCIDIR se usa para encontrar la posición del nombre del sujeto en la celda $ H $ 3 en la lista de nombres del sujeto.
- [area_num] - IF ($ H $ 2 = ”Unit Test”, 1, IF ($ H $ 2 = ”Midterm”, 2,3)): El valor del número de área le dice a la función INDICE qué matriz seleccionar. En este ejemplo, tenemos tres matrices en el primer argumento. Si selecciona Prueba unitaria en el menú desplegable, la función SI devuelve 1 y las funciones INDICE seleccionan la primera matriz de las tres matrices (que es $ B $ 3: $ E $ 7).
Ejemplo 6: creación de una referencia mediante la función INDEX (rangos dinámicos con nombre)
Este es un uso salvaje de la función INDICE de Excel.
Tomemos un ejemplo sencillo.
Tengo una lista de nombres como se muestra a continuación:
Ahora puedo usar una función INDICE simple para obtener el apellido en la lista.
Aquí está la fórmula:
= ÍNDICE ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Esta función simplemente cuenta el número de celdas que no están vacías y devuelve el último elemento de esta lista (funciona solo cuando no hay espacios en blanco en la lista).
Ahora, lo que aquí viene es la magia.
Si coloca la fórmula delante de una referencia de celda, la fórmula devolverá una referencia de celda del valor coincidente (en lugar del valor en sí).
= A2: ÍNDICE ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Es de esperar que la fórmula anterior devuelva = A2: ”Josh” (donde Josh es el último valor de la lista). Sin embargo, devuelve = A2: A9 y, por lo tanto, obtiene una matriz de nombres como se muestra a continuación:
Un ejemplo práctico en el que esta técnica puede resultar útil es la creación de rangos dinámicos con nombre.
Eso es todo en este tutorial. He tratado de cubrir los principales ejemplos del uso de la función ÍNDICE de Excel. Si desea ver más ejemplos agregados a esta lista, hágamelo saber en la sección de comentarios.
Nota: Hice todo lo posible para revisar este tutorial, pero en caso de que encuentres algún error o errores ortográficos, házmelo saber 🙂
Función ÍNDICE de Excel - Tutorial en vídeo
- Función BUSCARV de Excel.
- Función HLOOKUP de Excel.
- Función INDIRECTA de Excel.
- Función MATCH de Excel.
- Función OFFSET de Excel.
También le pueden gustar los siguientes tutoriales de Excel:
- VLOOKUP vs. ÍNDICE / PARTIDO
- Coincidencia de índice de Excel
- Buscar y devolver valores en toda una fila / columna.
