Al usar una fórmula matricial en Excel, podemos crear una fórmula de búsqueda que usa múltiples criterios para encontrar información en una base de datos o tabla de datos. La fórmula matricial implica anidar la función MATCH dentro de la función INDEX.
Este tutorial incluye un ejemplo paso a paso de cómo crear una fórmula de búsqueda que usa múltiples criterios para encontrar un proveedor deWidgets de titanio en una base de datos de muestra. Siga adelante para saber qué tan fácil es crear sus propias fórmulas de búsqueda personalizadas.
Prepare su libro de Excel con datos
Para seguir los pasos de este tutorial, deberá comenzar ingresando los datos de muestra en las siguientes celdas, como se muestra en la imagen de arriba. Las filas 3 y 4 se dejan en blanco para acomodar la fórmula de matriz creada durante este tutorial.
- Ingrese el rango superior de datos en las celdas D1 a F2
- Introduzca el segundo rango en las celdas D5 a F11
El tutorial no incluye el formato que se ve en la imagen, pero esto no afectará el funcionamiento de la fórmula de búsqueda. La información sobre opciones de formato similares a las que se vieron anteriormente está disponible en este Tutorial Básico de Formato de Excel.
02 de 08Crear una función de índice en Excel
los ÍNDICE La función es una de las pocas en Excel que tiene múltiples formas. La función tiene una Forma de matriz y un Formulario de referencia. El formulario de matriz devuelve los datos reales de una base de datos o tabla de datos, mientras que el formulario de referencia le proporciona la referencia de celda o la ubicación de los datos en la tabla.
En este tutorial, usaremos Array Form ya que queremos saber el nombre del proveedor para widgets de titanio en lugar de la referencia de celda de este proveedor en nuestra base de datos. Siga estos pasos para crear su función de índice:
- Haga clic en la celda F3 para convertirla en la celda activa: aquí es donde ingresaremos la función anidada.
- Haga clic en el Fórmulas pestaña del menú de la cinta.
- Escoger Búsqueda y referencia Desde la barra de cinta para abrir la función desplegable.
- Haga clic en ÍNDICE en la lista para que aparezca el Seleccionar argumentos caja de diálogo.
- Elegir la array, row_num, col_num Opción en el cuadro de diálogo.
- Hacer clic DE ACUERDO para abrir el cuadro de diálogo de función INDICE; Esto abrirá el Formula Builder en Excel.
- En el Formula Builder, haga clic en el Formación línea.
- Resaltar celdas D6 a F11, al hacer clic y arrastrar, en la hoja de trabajo para ingresar el rango en el generador.
En versiones anteriores de Excel el Formula Builder será reemplazado por el Argumentos de la función ventana. Aplique los mismos pasos mencionados en este tutorial en la ventana.
03 de 08Iniciar la función MATCH anidada
Al anidar una función dentro de otra, no es posible abrir el generador de fórmulas de la segunda función o anidada o ingresar los argumentos necesarios. La función anidada debe escribirse como una de las ordenadas en la primera función.
Al ingresar funciones manualmente, los argumentos de la función están separados entre sí por una coma.
El primer paso para ingresar la función MATCH anidada es ingresar la Valor de búsqueda argumento. El Lookup_value será la ubicación o referencia de celda para el término de búsqueda que queremos hacer coincidir en la base de datos.
Normalmente, Lookup_value acepta solo un criterio de búsqueda o término. Para buscar múltiples criterios, debemos ampliar el Lookup_value; tEsto se realiza mediante la concatenación o la unión de dos o más referencias de celdas usando el símbolo "y" comercial - &.
- En el Formula Builder, haga clic en el Row_num línea.
- Escriba el nombre de la función PARTIDO Seguido de un soporte redondo abierto.
- Haga clic en la celda D3 para introducir esa referencia de celda en el cuadro de diálogo.
- Escribe un ampersand después de la referencia de celda D3 Para agregar una segunda referencia de celda.
- Haga clic en la celda E3 para introducir esta segunda referencia de celda.
- Escriba una coma después de la referencia de celda E3 para completar la entrada de la función MATCH Valor de búsqueda argumento.
En el último paso del tutorial, nuestro Lookup_values se ingresarán en las celdas D3 y E3 de la hoja de trabajo.
04 de 08Completa la función MATCH anidada
Este paso cubre la adición de Lookup_array argumento para los anidados PARTIDO función. El Lookup_array es el rango de celdas que la función MATCH buscará para encontrar el argumento Lookup_value agregado en el paso anterior del tutorial.
Como hemos identificado dos campos de búsqueda en el argumento Lookup_array, debemos hacer lo mismo para Lookup_array. La función MATCH solo busca una matriz para cada término especificado, por lo tanto, para ingresar múltiples matrices, nuevamente utilizamos el signo y para concatenar las matrices juntas.
Estos pasos se deben ingresar después de la coma ingresada en el paso anterior en la Row_num línea en el ÍNDICE función.
- Haga clic en el Row_num Línea después de la coma para colocar el punto de inserción al final de la entrada actual.
- Resaltar celdas D6 a D11 en la hoja de trabajo para ingresar el rango - esta es la primera matriz en la que la función es buscar.
- Escribe un ampersand después de las referencias de la celda. D6: D11 Porque queremos que la función busque dos matrices.
- Resaltar celdas E6 a E11 en la hoja de trabajo para ingresar el rango - esta es la segunda matriz en la que la función es buscar.
- Escriba una coma después de la referencia de celda E3 para completar la entrada de la PARTIDO función de Lookup_array argumento.
- Dejar el ÍNDICE Cuadro de diálogo de función abierto para el siguiente paso en el tutorial.
Agregue el argumento de tipo MATCH
El tercer y último argumento de la función MATCH es el Tipo de concordancia argumento; le dice a Excel cómo emparejar el Valor de búsqueda con valores en Lookup_array: las opciones disponibles son 1, 0 o -1.
Este argumento es opcional. Si se omite, la función utiliza el valor predeterminado de 1.
- Si el Match_type = 1 o se omite: MATCH encuentra el valor más grande que es menor o igual que el valor Lookup_value. Los datos de Lookup_array deben ordenarse en orden ascendente.
- Si el Match_type = 0: MATCH encuentra el primer valor que es exactamente igual al Lookup_value. Los datos de Lookup_array se pueden ordenar en cualquier orden.
- Si el Match_type = -1: MATCH encuentra el valor más pequeño que es mayor o igual que el valor Lookup_value. Los datos de Lookup_array deben ordenarse en orden descendente.
Estos pasos se deben ingresar después de la coma ingresada en el paso anterior en la Row_num línea en el ÍNDICE función.
- Siguiendo la coma sobre el Row_num línea, escriba un cero - 0 - ya que queremos que la función anidada devuelva coincidencias exactas a los términos que ingresamos en las celdas D3 y E3.
- Escriba un soporte redondo de cierre - ) - para completar el PARTIDO función.
- Dejar el Formula Builder Abierto para el siguiente paso en el tutorial.
Terminando la función INDEX
Ahora que el PARTIDO la función está terminada, nos moveremos a la tercera línea del Generador de fórmulas e ingresaremos el último argumento para el ÍNDICE función. Este tercer y último argumento es el Column_num argumento que le dice a Excel el número de columna en el rango D6 a F11; aquí es donde encontrará la información que queremos que devuelva la función. En este caso, un proveedor de widgets de titanio.
- Haga clic en el Column_num línea.
- Ingrese el número tres - 3 - en esta línea ya que estamos buscando datos en la tercera columna del rango D6 a F11.
- De nuevo, deja el Formula Builder Abierto para el siguiente paso en el tutorial.
Creación de la fórmula de matriz
Antes de cerrar el Formula Builder, necesitamos convertir nuestra función anidada en una fórmula de matriz; esto es lo que permite que nuestra función busque múltiples términos en la tabla de datos. En este tutorial, buscamos unir dos términos: Widgets de la columna 1 y Titanio de la columna 2.
La creación de una fórmula matricial en Excel se realiza presionando la tecla CTRL, CAMBIOy ENTRAR Teclas en el teclado al mismo tiempo. Una vez que se presiona, la función estará rodeada de llaves, lo que indica que la función es ahora una matriz.
- Cierra el Formula Builder haciendo clic en el Hecho botón.
- A continuación, seleccione la celda F4 y presione el Entrar Clave para ver la fórmula.
- Para convertir la fórmula en una matriz, presione simultáneamente CTRL + MAYÚS + ENTRAR en tu teclado
- Si se hace correctamente un #N / A El error aparecerá en la celda F3, la celda donde ingresamos a la función.
- los #N / A el error aparece en la celda F3 porque las células D3 y E3 están en blanco D3 y E3 son las celdas donde le dijimos a la función que encuentre el Lookup_values. Una vez que se agregan los datos a estas dos celdas, el error será reemplazado por la información de la base de datos.
Añadiendo los criterios de búsqueda
Finalmente, agregaremos los términos de búsqueda a nuestra hoja de trabajo. Como se mencionó en el paso anterior, buscamos coincidir con los términos Widgets de la columna 1 y Titanio de la columna 2. Si, y solo si, nuestra fórmula encuentra una coincidencia para ambos términos en las columnas apropiadas en la base de datos, devolverá el valor de la tercera columna.
- Haga clic en la celda D3.
- Tipo Widgets y presione el Entrar Tecla en el teclado.
- Haga clic en la celda E3.
- Tipo Titanio y presione el Entrar Tecla en el teclado.
- El nombre del proveedor Widgets Inc. debe aparecer en la celda F3: la ubicación de la función, ya que es el único proveedor que se vende que vende widgets de titanio.
- Cuando hace clic en la celda F3, la función completa aparece en la barra de fórmulas sobre la hoja de trabajo.
{= INDICE (D6: F11, MATCH (D3 y E3, D6: D11 y E6: E11, 0), 3)}
En nuestro ejemplo, solo había un proveedor para aparatos de titanio. Si hubiera más de un proveedor, la función devuelve el proveedor que figura primero en la base de datos.
