Al combinar Excel'sVLOOKUP funciona con elCOLUMNA función podemos crear una fórmula de búsqueda que le permita devolver múltiples valores de una sola fila de una base de datos o tabla de datos.
01 de 10Devolver múltiples valores con Excel VLOOKUP
Siguiendo los pasos que se enumeran a continuación, se crea la fórmula de búsqueda que se ve en la imagen de arriba y devolverá múltiples valores de un solo registro de datos.
La fórmula de búsqueda requiere que el COLUMNA la función está anidada dentro de VLOOKUP. Anidar una función implica ingresar la segunda función como uno de los argumentos para la primera función.
En este tutorial, el COLUMNA La función se ingresará como índice de columna número argumento para VLOOKUP. El último paso en el tutorial consiste en copiar la fórmula de búsqueda en columnas adicionales para recuperar valores adicionales para la parte elegida.
Ingrese los datos del tutorial
El primer paso en el tutorial es ingresar los datos en una hoja de cálculo de Excel. Para seguir los pasos del tutorial, ingrese los datos que se muestran en la imagen de arriba en las siguientes celdas:
- Ingrese el rango superior de datos en células D1 a G1.
- Ingrese el segundo rango en células D4 a G10.
Los criterios de búsqueda y la fórmula de búsqueda creada durante este tutorial se ingresarán en fila 2 de la hoja de trabajo.
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.
03 de 10Creación de un rango con nombre para la tabla de datos
Un rango con nombre es una manera fácil de referirse a un rango de datos en una fórmula. En lugar de escribir las referencias de celda para los datos, puede escribir el nombre del rango.
Una segunda ventaja para usar un rango con nombre es que las referencias de celda para este rango nunca cambian, incluso cuando la fórmula se copia a otras celdas en la hoja de cálculo. Los nombres de rango son, por lo tanto, una alternativa al uso de referencias de celda absolutas para evitar errores al copiar fórmulas.
El nombre del rango no incluye los encabezados o los nombres de campo para los datos (fila 4) pero sólo los datos en sí.
- Realce celdas D5 a G10 en la hoja de trabajo para seleccionarlos.
- Haga clic en el Nombre de Caja ubicado arriba columna A.
- Tipo Mesa en el Nombre de Caja.
- presione el Entrar Tecla en el teclado.
- Celulas d5 a G10 ahora tiene el nombre de rango de Mesa. Usaremos el nombre para el VLOOKUP matriz de tabla Argumento posterior en el tutorial.
Abrir el cuadro de diálogo VLOOKUP
Aunque es posible simplemente escribir nuestra fórmula de búsqueda directamente en una celda en una hoja de trabajo, muchas personas encuentran difícil mantener la sintaxis recta, especialmente para una fórmula compleja como la que estamos usando en este tutorial.
Una alternativa, en este caso, es utilizar el VLOOKUP caja de diálogo. Casi todas las funciones de Excel tienen un cuadro de diálogo que le permite ingresar cada uno de los argumentos de la función en una línea separada.
Pasos de tutorial
- Haga clic en celda e2 de la hoja de trabajo: la ubicación donde se mostrarán los resultados de la fórmula de búsqueda bidimensional.
- Haga clic en el Pestaña de fórmulas del cinta.
- Haga clic en el Búsqueda y referencia opción en el cinta Para abrir la función desplegable.
- Haga clic en VLOOKUP en la lista para abrir el cuadro de diálogo de la función.
Entrar en el argumento del valor de búsqueda
Normalmente, el buscar valor coincide con un campo de datos en la primera columna de la tabla de datos. En nuestro ejemplo, el buscar valor se refiere al nombre de la parte de hardware sobre la que queremos encontrar información. Los tipos de datos permitidos para el buscar valor Son datos de texto, valores lógicos, números y referencias de celda.
Referencias celulares absolutas
Normalmente, cuando las fórmulas se copian en Excel, las referencias de celda cambian para reflejar su nueva ubicación. Si esto pasa, D2, la referencia de celda para el buscar valor, cambiará a medida que se copie la fórmula creando errores en células F2 y G2.
Las referencias de celda absolutas no cambian cuando se copian las fórmulas.
Para evitar los errores, convertiremos la referencia de celda. D2 en una referencia de celda absoluta. Las referencias de celda absolutas se crean presionando el F4 Tecla en el teclado. Al hacerlo, se agregan signos de dólar alrededor de la referencia de la celda, como $ D $ 2.
- Haga clic en el valor de búsqueda línea en el cuadro de diálogo.
- Haga clic en célula D2 para agregar esta referencia de celda a la valor de búsqueda línea - esta es la celda donde escribiremos el nombre de la parte sobre la que estamos buscando información
- Sin mover el punto de inserción, presione el F4 Tecla en el teclado para convertir. D2 en la referencia de celda absoluta $ D $ 2.
- Dejar el VLOOKUP Cuadro de diálogo de función abierto para el siguiente paso en el tutorial.
Entrar en el argumento de matriz de tabla
La matriz de tabla es la tabla de datos que la fórmula de búsqueda busca para encontrar la información que deseamos.La matriz de tabla debe contener al menos dos columnas de datos.
La primera columna contiene el argumento del valor de búsqueda (paso anterior en el tutorial), mientras que la fórmula de búsqueda buscará la segunda columna para encontrar la información que especificamos.
El argumento de la matriz de la tabla debe ingresarse como un rango que contenga las referencias de celda para la tabla de datos o como un nombre de rango.
- Haga clic en el matriz de tabla línea en el cuadro de diálogo.
- Tipo Mesa para ingresar el nombre del rango para este argumento.
- Dejar el VLOOKUP Cuadro de diálogo de función abierto para el siguiente paso en el tutorial.
Anidando la función de COLUMNA
Normalmente VLOOKUP solo devuelve datos de una columna de una tabla de datos y esta columna la establece el número de índice de columna argumento.
En este ejemplo, sin embargo, tenemos tres columnas de las que deseamos devolver datos, por lo que necesitamos una manera de cambiar fácilmente la número de índice de columna sin editar nuestra fórmula de búsqueda - aquí es donde el COLUMNA la función entra.
Entrando como número de índice de columna argumento, cambiará a medida que la fórmula de búsqueda se copia de celda D2 a celulas e2 y F2 Más adelante en el tutorial.
Funciones de anidación
los COLUMNA función, por lo tanto, actúa como VLOOKUPes número de índice de columna argumento; Esto se logra anidando el COLUMNA función dentro de VLOOKUP en el Col_index_num Línea del cuadro de diálogo.
Ingresando la función de COLUMNA manualmente
Al anidar funciones, Excel no nos permite abrir el cuadro de diálogo de la segunda función para ingresar sus argumentos. los COLUMNA función, por lo tanto, se debe introducir manualmente en el Col_index_num línea. los COLUMNA La función tiene un solo argumento: el Referencia Argumento que es una referencia de celda.
Eligiendo el Argumento de Referencia de la Función COLUMNA
los COLUMNA El trabajo de la función es devolver el número de la columna dada como el Referencia argumento. En otras palabras, convierte la letra de la columna en un número con columna A siendo la primera columna, columna B El segundo y así sucesivamente.
Desde el primer campo de datos, lo que queremos que se devuelva es el precio del artículo, que se encuentra en la columna dos de la tabla de datos. Podemos elegir la referencia de celda para cualquier celda en columna B como el Referencia Argumento para obtener el número. 2 Para el Col_index_num argumento.
- En el VLOOKUP cuadro de diálogo de función, haga clic en el Col_index_num línea.
- Escriba el nombre de la función columna seguido de un soporte redondo abierto.
- Haga clic en célula B1 en la hoja de trabajo para ingresar esa referencia de celda como la Referencia argumento.
- Escribe un cierre redondo de cierre para completar el COLUMNA función
- Dejar el VLOOKUP Cuadro de diálogo de función abierto para el siguiente paso en el tutorial.
Entrar en el argumento de búsqueda de rango VLOOKUP
VLOOKUPes Rango de búsqueda el argumento es un valor lógico (CIERTO o FALSO) que indica si quieres VLOOKUP para encontrar una coincidencia exacta o aproximada a la Valor de búsqueda.
- Si CIERTO o si este argumento se omite: VLOOKUP devuelve una coincidencia exacta a la Valor de búsquedaO, si no se encuentra una coincidencia exacta, VLOOKUP devuelve el siguiente valor más grande. Para que la fórmula haga esto, los datos en la primera columna de Matriz de tabla debe ser ordenado en orden ascendente.
- Si FALSO: VLOOKUP solo utilizará una coincidencia exacta con la Valor de búsqueda. Si hay dos o más valores en la primera columna de Matriz de tabla que coincidan con el valor de búsqueda, se utiliza el primer valor encontrado. Si no se encuentra una coincidencia exacta, un #N / A se devuelve el error.
En este tutorial, ya que estamos buscando información específica sobre un elemento de hardware en particular, estableceremos Rango de búsqueda igual a Falso.
- Haga clic en el Rango de búsqueda línea en el cuadro de diálogo.
- Escribe la palabra Falso En esta línea para indicar que queremos. VLOOKUP para devolver una coincidencia exacta para los datos que estamos buscando.
- Hacer clic DE ACUERDO para completar la fórmula de búsqueda y cerrar el cuadro de diálogo.
- Dado que aún no hemos introducido los criterios de búsqueda en célula D2 un # N / A error estará presente en celda e2.
- Este error se corregirá cuando agreguemos los criterios de búsqueda en el último paso del tutorial.
Copiando la fórmula de búsqueda
La fórmula de búsqueda está destinada a recuperar datos de varias columnas de la tabla de datos al mismo tiempo. Para hacer esto, la fórmula de búsqueda debe residir en todos los campos de los cuales deseamos información.
En este tutorial queremos que recupere datos de columnas 2, 3y 4 de la tabla de datos, es decir, el precio, el número de pieza y el nombre del proveedor cuando ingresamos un nombre parcial como Valor de búsqueda.
Dado que los datos se presentan en un patrón regular en la hoja de trabajo, podemos copiar la fórmula de búsqueda en celda e2 a células F2 y G2.
A medida que se copia la fórmula, Excel actualizará la referencia de celda relativa en el COLUMNA funcióncélula B1) para reflejar la nueva ubicación de la fórmula.
Además, Excel no cambia la referencia de celda absoluta $ D $ 2 y el rango nombrado Mesa como la fórmula se copia.
Hay más de una forma de copiar datos en Excel, pero probablemente la forma más fácil es usar el Mango de llenado.
- Haga clic en celda e2, donde se encuentra la fórmula de búsqueda, para que sea la celda activa.
- Colocar el Puntero del ratón sobre el cuadrado negro en la esquina inferior derecha: el puntero cambiará a un signo más para indicar el controlador de relleno.
- Haga clic en el botón izquierdo del ratón y arrastre el controlador de relleno a través de celda g2.
- Lanzamiento el botón del ratón y celda F3 Debe contener la fórmula de búsqueda bidimensional.
- Si se hace correctamente, células F2 y G2 ahora también debe contener el #N / A error que está presente en celda e2.
Entrar en los criterios de búsqueda
Una vez que la fórmula de búsqueda se haya copiado en las celdas requeridas, se puede utilizar para recuperar información de la tabla de datos. Para ello, escriba el nombre del artículo desea recuperar en el Valor de búsqueda célula (D2) y presione el Entrar Tecla en el teclado.
Una vez hecho esto, cada celda que contiene la fórmula de búsqueda debe contener un dato diferente sobre el elemento de hardware que está buscando.
- Haga clic en celda D2 en la hoja de trabajo
- Tipo Widget dentro celda D2 y presione el Entrar Tecla en el teclado.
- La siguiente información debe ser mostrada en celulas e2 a G2.
- E2: $ 14.76 - el precio de un widget
- F2: PN-98769 - el número de parte de un widget
- G2: Widgets Inc. - el nombre del proveedor para widgets
- Prueba el VLOOKUP fórmula matricial adicional escribiendo el nombre de otras partes en celda D2 y observando los resultados en celulas e2 a G2
