Ao combinar a función VLOOKUP de Excel coa función COLUMN podemos crear unha fórmula de busca que lle permita devolver varios valores desde unha única fila dunha base de datos ou dunha táboa de datos.
No exemplo que se mostra na imaxe anterior, a fórmula de busca facilita a devolución de todos os valores, como o prezo, o número de parte e o fornecedor, relacionados con varias pezas de hardware.
01 de 10
Devolve varios valores con Excel VLOOKUP
Seguindo os pasos que se detallan a continuación, créase a fórmula de busca que se ven na imaxe superior que devolverá varios valores a partir dun único rexistro de datos.
A fórmula de busca require que a función COLUMN estea aniñada dentro de VLOOKUP.
Analizar unha función implica a entrada na segunda función como un dos argumentos para a primeira función.
Neste tutorial, a función COLUMN ingresarase como argumento de número de índice de columna para VLOOKUP.
O último paso do tutorial implica copiar a fórmula de busca a columnas adicionais para recuperar valores adicionais para a parte elixida.
Contidos do titorial
- Introducir os datos do titorial
- Creando un intervalo nomeado para a táboa de datos
- Comezando a función VLOOKUP
- Introducir o argumento de valor de busca usando referencias de cela absoluta
- Introducir o argumento da matriz de táboa
- Introducir a función COLUMN aninhada
- Completando a función VLOOKUP
- Copiar a fórmula de busca co controlador de recheo
- Recuperando datos coa fórmula de busca
02 de 10
Introduce os datos do titorial
O primeiro paso no tutorial é ingresar os datos nunha folla de cálculo de Excel.
Para seguir os pasos do tutorial, introduza os datos que se amosan na imaxe anterior nas seguintes celas .
- Introduce o rango superior de datos nas celas D1 a G1
- Introduce o segundo intervalo nas celas D4 a G10
Os criterios de busca e a fórmula de busca creada durante este tutorial serán inseridos na fila 2 da folla de cálculo.
O tutorial non inclúe o formato visto na imaxe, pero isto non afectará o funcionamento da fórmula de busca.
A información sobre as opcións de formato semellantes ás vistas anteriormente está dispoñible neste tutorial básico de formatado de Excel .
Pasos de titorial
- Introduza os datos que se ven na imaxe de arriba nas celas D1 a G10
03 de 10
Creando un intervalo nomeado para a táboa de datos
Un intervalo nomeado é un xeito doado de referirse a un intervalo de datos nunha fórmula. En vez de escribir nas referencias das celas para os datos, só pode escribir o nome do intervalo.
Unha segunda vantaxe para usar un intervalo nomeado é que as referencias das celas para este intervalo nunca se modifican aínda cando a fórmula se copia noutras celas na folla de cálculo.
Os nomes de intervalo son, polo tanto, unha alternativa ao uso de referencias de cela absoluta para evitar erros ao copiar fórmulas.
Nota: o nome do alcance non inclúe os títulos nin os nomes de campo dos datos (fila 4), pero só os datos en si.
Pasos de titorial
- Destaque as celas D5 a G10 na folla de traballo para seleccionalas
- Fai clic no cadro de nome situado arriba da columna A
- Escriba "Táboa" (sen comiñas) no cadro de nome
- Prema a tecla ENTER no teclado
- As celas D5 a G10 agora teñen o nome de alcance de "Táboa". Usaremos o nome para o argumento da matriz de táboas VLOOKUP máis tarde no tutorial
04 de 10
Apertura do cuadro de diálogo VLOOKUP
Aínda que só se pode escribir a nosa fórmula de busca directamente nunha cela nunha folla de traballo, moitas persoas creen que é difícil manter a sintaxe en liña recta, especialmente para unha fórmula complexa como a que estamos a usar neste tutorial.
Unha alternativa, neste caso, é usar o cuadro de diálogo VLOOKUP. Case todas as funcións de Excel teñen unha caixa de diálogo que permite introducir cada un dos argumentos da función nunha liña separada.
Pasos de titorial
- Faga clic na celda E2 da folla de traballo : a localización onde se mostrarán os resultados da fórmula de busca bidimensional
- Fai clic na pestana Fórmulas da cinta
- Fai clic na opción Busca e referencia da cinta para abrir a lista despregábel da función
- Faga clic en VLOOKUP na lista para abrir o cadro de diálogo da función
05 de 10
Introducir o argumento de valor de busca usando referencias de cela absoluta
Normalmente, o valor de busca coincide cun campo de datos na primeira columna da táboa de datos.
No noso exemplo, o valor de busca refírese ao nome da parte de hardware sobre o que queremos atopar información.
Os tipos permitidos de datos para o valor de busca son:
- datos de texto
- un valor lóxico (TRUE ou FALSE)
- un número
- unha referencia de cela para un valor na folla de traballo
Neste exemplo, ingresaremos a referencia da cela onde se localiza o nome da parte - a cela D2.
Referencias celulares absolutas
Nun paso posterior do tutorial, copiamos a fórmula de busca na cela E2 ás celas F2 e G2.
Normalmente, cando se copian fórmulas en Excel, as referencias celulares cambian para reflectir a súa nova localización.
Se isto ocorre, D2 - a referencia da cela para o valor de busca - cambiará a medida que se copie a fórmula creando erros nas celas F2 e G2.
Para evitar os erros, converteremos a referencia de célula D2 nunha referencia de célula absoluta .
As referencias absolutas das celas non cambian cando se copian as fórmulas.
As referencias celulares absolutas créanse premendo a tecla F4 no teclado. Ao facelo, engade sinais de dólar ao redor da referencia de cela, como $ D $ 2
Pasos de titorial
- Prema na liña de busca_valor no cadro de diálogo
- Faga clic na cela D2 para engadir esta referencia de cela á liña de busca_valor . Esta é a cela onde escribiremos o nome da parte sobre o que buscamos información
- Sen mover o punto de inserción, prema a tecla F4 no teclado para converter D2 na referencia de cela absoluta $ D $ 2
- Deixar a caixa de diálogo de función VLOOKUP aberta para o seguinte paso no titorial
06 de 10
Introducir o argumento da matriz de táboa
A matriz de táboas é a táboa de datos que busca a fórmula para atopar a información que desexamos.
A matriz de táboas debe conter polo menos dúas columnas de datos .
- a primeira columna contén o argumento de valor de busca (paso anterior no titorial)
- o segundo e todas as columnas adicionais serán investigadas pola fórmula de busca para atopar a información que especificamos.
O argumento da matriz de táboas debe ingresarse como un intervalo que contén as referencias de cela para a táboa de datos ou como un nome de intervalo .
Para este exemplo, imos usar o nome de alcance creado no paso 3 do tutorial.
Pasos de titorial
- Faga clic na liña de táboa da táboa no cadro de diálogo
- Escriba "Táboa" (sen comiñas) para ingresar o nome do alcance deste argumento
- Deixar a caixa de diálogo de función VLOOKUP aberta para o seguinte paso no titorial
07 de 10
Aninando a función COLUMN
Normalmente, VLOOKUP só retorna datos dunha columna dunha táboa de datos e esta columna está definida polo argumento de número do índice da columna .
Neste exemplo, con todo, temos tres columnas que desexamos devolver datos, polo que necesitamos un xeito de cambiar fácilmente o número de índice da columna sen editar a nosa fórmula de busca.
Aquí é onde entra a función COLUMN. Ao ingresalo como argumento de número de índice de columna , cambiará a medida que a fórmula de busca copiase da cela D2 ás celas E2 e F2 máis tarde no titorial.
Funcións de anidación
A función COLUMN, polo tanto, actúa como argumento de número de índice de columna de VLOOKUP.
Isto conséguese aniñando a función COLUMNA dentro de VLOOKUP na liña Col_index_num do cadro de diálogo.
Introducindo a función COLUMN manualmente
Cando as funcións de anidación, Excel non nos permite abrir a caixa de diálogo da segunda función para introducir os seus argumentos.
A función COLUMN, polo tanto, debe ingresarse manualmente na liña Col_index_num .
A función COLUMN só ten un argumento: o argumento de referencia que é unha referencia de cela.
Escolla o argumento de referencia da función COLUMN
O traballo da función COLUMN é devolver o número da columna dada como argumento de referencia .
Noutras palabras, converte a letra da columna nun número coa columna A sendo a primeira columna, a columna B o segundo e así sucesivamente.
Dado que o primeiro campo de datos que desexamos é o prezo do elemento, que está na columna dous da táboa de datos, podemos elixir a referencia da cela para calquera cela da columna B como o Argumento de referencia para obter o número 2 o argumento Col_index_num .
Pasos de titorial
- Na caixa de diálogo de función VLOOKUP, fai clic na liña Col_index_num
- Escribe a columna de nome de función seguida dun soporte aberto redondo " ( "
- Faga clic na cela B1 na folla de traballo para ingresar a referencia de cela como argumento de referencia
- Escriba un soporte de rolda de peche " ) " para completar a función COLUMN
- Deixar a caixa de diálogo de función VLOOKUP aberta para o seguinte paso no titorial
08 de 10
Introdución ao argumento de busca do rango VLOOKUP
O argumento Range_lookup de VLOOKUP é un valor lóxico (TRUE ou FALSE) que indica se quere que VLOOKUP poida atopar unha coincidencia exacta ou aproximada co valor de busca.
- Se é TRUE ou se este argumento é omitido, VLOOKUP devolve unha coincidencia exacta co valor de busca_ ou se non se atopa unha coincidencia exacta, VLOOKUP devolve o seguinte valor máis grande. Para que a fórmula faga isto, os datos da primeira columna de Table_array deben clasificarse en orde crecente .
- Se FALSE, VLOOKUP só empregará unha coincidencia exacta co valor de busca. Se hai dous ou máis valores na primeira columna de Table_array que coinciden co valor de busca, emprégase o primeiro valor atopado. Se non se atopa unha coincidencia exacta, devólvese un erro de # N / A.
Neste tutorial, xa que estamos a buscar información específica sobre un elemento de hardware en particular, estableceremos Range_lookup igual a False .
Pasos de titorial
- Prema na liña Range_lookup no cadro de diálogo
- Escriba a palabra False nesta liña para indicar que queremos que VLOOKUP devolva unha correspondencia exacta para os datos que buscamos
- Faga clic en Aceptar para completar a fórmula de busca e pechar caixa de diálogo
- Xa que aínda non entramos os criterios de busca na cela D2, un erro N / A estará presente na cela E2
- Este erro corrixirase cando engadiremos os criterios de busca no último paso do tutorial
09 de 10
Copiar a fórmula de busca co controlador de recheo
A fórmula de busca ten como obxectivo recuperar datos de varias columnas da táboa de datos ao mesmo tempo.
Para iso, a fórmula de busca debe residir en todos os campos dos que queremos información.
Neste tutorial queremos recuperar datos das columnas 2, 3 e 4 da táboa de datos: ese é o prezo, o número da parte e o nome do provedor cando ingresamos un nome de parte como o valor de busca.
Dado que os datos están distribuídos nun estándar normal na folla de traballo , podemos copiar a fórmula de busca na cela E2 ás celas F2 e G2.
Cando se copia a fórmula, Excel actualizará a referencia relativa da célula na función COLUMN (B1) para reflectir a nova localización da fórmula.
Tamén, Excel non cambia a referencia de célula absoluta $ D $ 2 ea táboa de intervalo nomeado como a fórmula é copiada.
Existe máis dunha forma de copiar datos en Excel, pero probablemente o xeito máis doado é usar o Control de recheo .
Pasos de titorial
- Faga clic na celular E2 - onde está a fórmula de busca - para que sexa a célula activa
- Coloca o punteiro do rato sobre o cadrado negro no ángulo inferior dereito. O punteiro cambiará a un signo máis " + " - este é o identificador de recheo
- Fai clic co botón esquerdo do rato e arrastra o identificador de recheo para a cela G2
- Solte o botón do rato e a cela F3 debería conter a fórmula de busca bidimensional
- Se se realiza correctamente, as celas F2 e G2 tamén deberían tamén conter o erro # N / A que está presente na cela E2
10 de 10
Introducir os criterios de busca
Unha vez que a fórmula de busca foi copiada ás celas requiridas, pode usarse para recuperar información da táboa de datos.
Para facelo, escriba o nome do elemento que desexe recuperar na cela de busca_valor (D2) e prema a tecla ENTER no teclado.
Unha vez feito isto, cada célula que contén a fórmula de busca debería conter unha información diferente sobre o elemento de hardware que está a procurar.
Pasos de titorial
- Faga clic na cela D2 na folla de cálculo
- Introduza o widget na cela D2 e prema a tecla ENTER no teclado
- A seguinte información debe mostrarse nas celas E2 a G2:
- E2 - $ 14.76 - o prezo dun widget
- F2 - PN-98769 - o número de parte dun widget
- G2 - Widgets Inc. - o nome do provedor para widgets
- Proba a fórmula da matriz VLOOKUP escribindo o nome doutras partes na cela D2 e observando os resultados nas celas E2 a G2
Se hai unha mensaxe de erro como #REF! aparece nas celas E2, F2 ou G2, esta lista de mensaxes de erro VLOOKUP pode axudar a determinar onde está o problema.