Supongamos que tienes una hoja de cálculo muy grande con mucha información en las filas y columnas. Tienes una larga lista de estudiantes en la primera columna, y muchos de las puntuaciones y calificaciones asociadas a cada estudiante. La pregunta es, ¿cómo puede buscas, digamos, sólo la puntuación de Ali en Historia. Haremos eso con el ayuda de las funciones de Excel MATCH e INDEX.
Crea la siguiente hoja de cálculo simple:
Hemos optado por mostrar sólo cuatro estudiantes y sus notas en cinco asignaturas: Matemáticas, Inglés, Ciencia, Historia y Arte. Lo que nos gustaría hacer es buscar La puntuación de Ali en Historia. (Por supuesto, con una simple hoja de cálculo como la de arriba, es muy fácil comprobarlo por ti mismo mirando a través de las filas y por el columnas. Pero tu hoja de cálculo probablemente será más compleja que la nuestra).
Lo que haremos es usar la función de Excel MATCH para obtener un número de fila y luego un número de columna. Entonces usaremos estos números de fila y columna en la función de ÍNDICE.
La función MATCH se utiliza para devolver un número de fila o de columna de un de datos. En nuestra hoja de cálculo de arriba, tenemos a los estudiantes en la columna A, en las filas 2, 3, 4 y 5. El estudiante llamó a Ali en la fila 3. Podemos buscar en el Una columna y volver a la fila en la que está Ali.
Haz clic en la celda A7 de tu hoja de cálculo e introduce Estudiante . En la celda B7 introduce el texto Asunto . En la celda C7 introduce Fila , y en la celda D7 entrar Col . Tu hoja de cálculo tendrá entonces este aspecto:
Haga clic en la celda A8 y escriba Ali como el estudiante. Haz clic en la celda B8 y entrar en la Historia como el sujeto:
En la celda C8, introduzca la siguiente fórmula:
=MATCH(A8, A2:A5, 0)
MATCH necesita tres cosas: un valor que buscar, un rango de células que buscar, y un tipo de partido. El valor que queremos buscar es Ali, que es la celda A8 para nosotros. Si lo desea, puede escribir su término de búsqueda entre comillas:
=MATCH(«Ali», A2:A5, 0)
El A2:A5 de arriba significa buscar en las celdas A2 a A5. El tipo de coincidencia que hemos utilizado es cero. Hay tres tipos de coincidencias que puedes usar:
1 Encuentra el mayor valor que es menor que o igual a lo que sea que estés buscando. Así que si tu término de búsqueda era 3 y buscabas los números 1, 2, 4, 5, 6, entonces MATCH devolvería un valor de 2. Si utiliza 1 como tipo de coincidencia, los números que está buscando deben estar en orden ascendente.
0 Usa 0 para encontrar una coincidencia exacta (Encuentra el primer partido.)
– 1 Encuentra el valor más pequeño que es menor …que o igual a lo que sea que estés buscando. Así que si tu término de búsqueda era 3 y buscabas los números 6, 5, 4, 2, 1, entonces MATCH regresaría un valor de 3. Si usas -1 como tipo de coincidencia, los números que buscas debe ser en orden descendente.
Si dejas el tipo de partido, obtienes un valor por defecto de 1:
=MATCH(«Ali», A2:A5)
Los tipos de coincidencia pueden ser muy confusos, así que es mejor usar el 0, que es para una coincidencia exacta.
Cuando se introduce la fórmula MATCH de arriba (la que tiene el cero como tipo de coincidencia) entonces obtendrás un valor de 2 en la celda C8:
Se podría pensar que MATCH devolvería un valor de 3. Después de todo, el estudiante llamado Ali comienza en la fila 3. La razón por la que MATCH regresa 2 es que Ali es el segundo estudiante de la lista que especificamos como el segundo parámetro de MATCH, que era A2:A5. Así que John está en la posición 1, Ali en la posición 2, Priyanka en la posición 3, y Helen en la posición 4.
Intentemos conseguir el número de columna.
Haga clic dentro de la celda D8. Ahora introduce la siguiente fórmula:
=MATCH(B8, B1:F1, 0)
Debes obtener un valor de 4 en la celda D8:
Esta vez, hemos usado B8 como término de búsqueda. Aquí es donde hemos escrito la historia. Queremos buscar los valores de B1 a F1, que es donde los títulos de la materia son. El tipo de coincidencia que hemos usado es de nuevo 0, que se usa para una coincidencia exacta. Se devuelve un valor de 4. (Las matemáticas están en la posición 1, el inglés en la posición 2, la ciencia está en la posición 3, Historia en la posición 4, y Arte en la posición 5.)
Ahora que tenemos un número de fila y columna, podemos usar INDICE para buscar un valor.
La función de índice de Excel
La función de ÍNDICE necesita dos cosas, un rango de células para buscar, y una fila número. Un tercer parámetro, la columna, es opcional.
Haz clic en la celda E8 de tu hoja de cálculo. Ahora introduce la siguiente fórmula:
=INDICE(B2:F5, C8, D8)
Cuando presionas la tecla «Enter» en tu teclado, tu hoja de cálculo debe verse así (hemos añadido el texto GRADO en la celda E7 como encabezamiento):
Un valor de B está ahora en la celda E8. Las celdas B2 a F5 contienen los datos que queremos para buscar, que son todos los grados. La celda C8 contiene el número de fila que queremos y la celda D8 contiene el número de columna. Excel utiliza la fila y la columna números para devolver el valor B.
Aunque tenemos las referencias celulares C8 y D8 en nuestra fórmula de INDICE, puedes reemplácelas con dos funciones de MATCH, de su preferencia. Por ejemplo, introduzca la la siguiente fórmula en la celda E9:
= ÍNDICE(B2:F5, COINCIDENCIA(A8,A2:A5, 0), COINCIDENCIA(B8, B1:F1, 0))
En lugar de la referencia de la celda C8 en la fórmula del ÍNDICE, ahora tenemos la PAREJA fórmula que introdujimos en la celda C8 anteriormente. De la misma manera, la referencia de la celda D8 ha sido reemplazada por su fórmula MATCH. La fórmula de INDICE es ahora más difícil de leer, sin embargo.
Si quieres que la fórmula de INDICE sea aún más fácil de leer, puedes reemplazar la célula referencias con rangos nombrados. Aquí está cómo.
Selecciona las celdas B2 a F5. Haz un clic dentro del cuadro de Nombre en la parte superior de Excel. Escribe el Nombre Grados :
Presiona la tecla «Enter» en tu teclado para establecer el rango de nombre para los datos en las células B2 a F5.
Ahora haz clic en la celda C8 para seleccionarla. De nuevo, haz clic dentro del cuadro de nombre. Escribe el nombre RowNumber y pulse intro:
Haz clic dentro de la celda D8 y crea un nombre llamado ColNumber :
Ahora puede introducir una fórmula de ÍNDICE que sea más legible. En la celda E10 introduce lo siguiente:
=INDEX(Grados, Número de Fila, Número de Columna)
Presiona la tecla «Enter» en tu teclado y tu hoja de cálculo se verá así:
Los tres parámetros de la función de ÍNDICE tienen ahora rangos nombrados.
Intenta escribir el nombre de un nuevo estudiante en la celda A8. Cambia Ali por Helen. Deberías encontrar que las calificaciones cambiarán a A. La fila y la columna serán ambas de 4:
En la imagen de abajo, hemos cambiado el estudiante a John y el sujeto a Inglés :
El uso de MATCH e INDEX puede ser una gran manera de buscar valores en sus datos, especialmente si tu hoja de cálculo tiene muchas filas y columnas.
Crear una factura comercial en Excel —
Regreso a la página de contenidos de Excel
