Ao combinar o ExcelVLOOKUP função com oCOLUNA função podemos criar uma fórmula de pesquisa que permite retornar vários valores de uma única linha de um banco de dados ou tabela de dados.
01 de 10Retornar vários valores com o Excel VLOOKUP
Siga as etapas listadas abaixo para criar a fórmula de pesquisa vista na imagem acima que retornará vários valores de um único registro de dados.
A fórmula de pesquisa requer que o COLUNA função é aninhada dentro de VLOOKUP. Aninhar uma função envolve inserir a segunda função como um dos argumentos da primeira função.
Neste tutorial, o COLUNA função será inserida como o índice de coluna número argumento para VLOOKUP. A última etapa do tutorial envolve copiar a fórmula de pesquisa para colunas adicionais para recuperar valores adicionais para a parte escolhida.
Digite os dados do tutorial
O primeiro passo do tutorial é inserir os dados em uma planilha do Excel. Para seguir as etapas do tutorial, insira os dados mostrados na imagem acima nas seguintes células:
- Insira o intervalo superior de dados em células D1 para G1.
- Digite o segundo intervalo em células D4 para G10.
Os critérios de pesquisa e a fórmula de pesquisa criada durante este tutorial serão inseridos linha 2 da planilha.
O tutorial não inclui a formatação vista na imagem, mas isso não afetará o funcionamento da fórmula de pesquisa. Informações sobre as opções de formatação semelhantes às vistas acima estão disponíveis neste Tutorial básico de formatação do Excel.
03 de 10Criando um intervalo nomeado para a tabela de dados
Um intervalo nomeado é uma maneira fácil de se referir a um intervalo de dados em uma fórmula. Em vez de digitar as referências de célula para os dados, basta digitar o nome do intervalo.
Uma segunda vantagem para usar um intervalo nomeado é que as referências de célula para esse intervalo nunca mudam mesmo quando a fórmula é copiada para outras células na planilha. Os nomes de intervalos são, portanto, uma alternativa ao uso de referências de células absolutas para evitar erros ao copiar fórmulas.
O nome do intervalo não inclui os títulos ou nomes de campo dos dados (linha 4) mas apenas os dados em si.
- Realçar células D5 para G10 na planilha para selecioná-los.
- Clique no Caixa de nome localizado acima coluna A.
- Tipo Mesa no Caixa de nome.
- aperte o Entrar tecla no teclado.
- Células D5 para G10 agora tem o nome do intervalo de Mesa. Nós vamos usar o nome para o VLOOKUP matriz de tabelas argumento mais tarde no tutorial.
Abrindo a caixa de diálogo VLOOKUP
Embora seja possível apenas digitar nossa fórmula de pesquisa diretamente em uma célula em uma planilha, muitas pessoas acham difícil manter a sintaxe correta - especialmente para uma fórmula complexa como a que estamos usando neste tutorial.
Uma alternativa, neste caso, é usar o VLOOKUP caixa de diálogo. Quase todas as funções do Excel têm uma caixa de diálogo que permite inserir cada um dos argumentos da função em uma linha separada.
Etapas do tutorial
- Clique em célula E2 da planilha - o local onde os resultados da fórmula de pesquisa bidimensional serão exibidos.
- Clique no Guia Fórmulas do fita.
- Clique no Pesquisa e Referência opção no fita para abrir o menu suspenso da função.
- Clique em VLOOKUP na lista para abrir a caixa de diálogo da função.
Inserindo o Argumento do Valor de Pesquisa
Normalmente, o olho para cima valor corresponde a um campo de dados na primeira coluna da tabela de dados. Em nosso exemplo, o olho para cima valor refere-se ao nome da parte de hardware sobre a qual queremos encontrar informações. Os tipos de dados permitidos para o olho para cima valor são dados de texto, valores lógicos, números e referências de células.
Referências De Células Absolutas
Normalmente, quando as fórmulas são copiadas no Excel, as referências de célula são alteradas para refletir seu novo local. Se isso acontecer, D2, a referência da célula para o olho para cima valor, irá mudar à medida que a fórmula é copiada, criando erros células F2 e G2.
Referências de células absolutas não são alteradas quando as fórmulas são copiadas.
Para evitar os erros, converteremos a referência da célula D2 em uma referência de célula absoluta. Referências de células absolutas são criadas pressionando o F4 tecla no teclado. Isso adiciona sinais de dólar ao redor da referência da célula, como $ D $ 2.
- Clique no lookup_value linha na caixa de diálogo.
- Clique em célula D2 para adicionar esta referência de célula ao lookup_value linha - esta é a célula onde vamos digitar o nome da peça sobre o qual estamos buscando informações
- Sem mover o ponto de inserção, pressione o botão F4 tecla no teclado para converter D2 na referência de célula absoluta $ D $ 2.
- Deixe o VLOOKUP caixa de diálogo de função aberta para a próxima etapa do tutorial.
Inserindo o Argumento da Matriz de Tabela
A tabela table é a tabela de dados que a fórmula de pesquisa pesquisa para encontrar as informações que desejamos.A matriz da tabela deve conter pelo menos duas colunas de dados.
A primeira coluna contém o argumento do valor de pesquisa (etapa anterior no tutorial), enquanto a segunda coluna será pesquisada pela fórmula de pesquisa para localizar as informações que especificamos.
O argumento da matriz da tabela deve ser inserido como um intervalo contendo as referências da célula para a tabela de dados ou como um nome de intervalo.
- Clique no table_array linha na caixa de diálogo.
- Tipo Mesa para inserir o nome do intervalo para esse argumento.
- Deixe o VLOOKUP caixa de diálogo de função aberta para a próxima etapa do tutorial.
Aninhando a função COLUMN
Normalmente VLOOKUP só retorna dados de uma coluna de uma tabela de dados e esta coluna é definida pelo número do índice da coluna argumento.
Neste exemplo, no entanto, temos três colunas das quais desejamos retornar dados, de modo que precisamos de uma maneira de alterar facilmente número do índice da coluna sem editar nossa fórmula de pesquisa - é aqui que o COLUNA função vem dentro
Ao entrar como o número do índice da coluna argumento, ele será alterado conforme a fórmula de pesquisa é copiada célula D2 para células E2 e F2 mais tarde no tutorial.
Funções de aninhamento
o COLUNA função, portanto, atua como VLOOKUP's número do índice da coluna argumento; isso é feito aninhando o COLUNA função dentro de VLOOKUP no Col_index_num linha da caixa de diálogo.
Inserindo a função COLUMN manualmente
Quando o aninhamento funciona, o Excel não nos permite abrir a caixa de diálogo da segunda função para inserir seus argumentos. o COLUNA função, portanto, deve ser inserida manualmente no Col_index_num linha. o COLUNA função tem apenas um argumento - o Referência argumento que é uma referência de célula.
Escolhendo o argumento de referência da função COLUMN
o COLUNA trabalho da função é retornar o número da coluna dada como o Referência argumento. Em outras palavras, ele converte a letra da coluna em um número com coluna A sendo a primeira coluna, coluna B o segundo e assim por diante.
Desde o primeiro campo de dados, o que queremos retornar é o preço do item, que está na coluna dois da tabela de dados. Podemos escolher a referência de célula para qualquer célula em coluna B Enquanto o Referência Argumento para obter o número 2 para o Col_index_num argumento.
- No VLOOKUP caixa de diálogo de função, clique no Col_index_num linha.
- Digite o nome da função coluna seguido por um suporte redondo aberto.
- Clique em célula B1 na planilha para inserir essa referência de célula como o Referência argumento.
- Digite um fecho do suporte redondo para completar o COLUNA função
- Deixe o VLOOKUP caixa de diálogo de função aberta para a próxima etapa do tutorial.
Inserindo o argumento de pesquisa do intervalo VLOOKUP
VLOOKUP's Pesquisa de alcance argumento é um valor lógico (VERDADE ou FALSO) que indica se você quer VLOOKUP para encontrar uma correspondência exata ou aproximada ao Lookup_value.
- E se VERDADE ou se este argumento for omitido: VLOOKUP retorna uma correspondência exata para o Lookup_valueOu, se uma correspondência exata não for encontrada, VLOOKUP retorna o próximo maior valor. Para a fórmula para fazer isso, os dados na primeira coluna de Table_array deve ser classificado em ordem crescente.
- E se FALSO: VLOOKUP usará apenas uma correspondência exata para o Lookup_value. Se houver dois ou mais valores na primeira coluna do Table_array que correspondem ao valor de pesquisa, o primeiro valor encontrado é usado. Se uma correspondência exata não for encontrada, #N / D erro é retornado.
Neste tutorial, uma vez que estamos procurando por informações específicas sobre um item de hardware em particular, vamos definir Pesquisa de alcance igual a Falso.
- Clique no Pesquisa de alcance linha na caixa de diálogo.
- Digite a palavra Falso nesta linha para indicar que queremos VLOOKUP para retornar uma correspondência exata para os dados que estamos procurando.
- Clique Está bem para concluir a fórmula de pesquisa e fechar a caixa de diálogo.
- Como ainda não inserimos os critérios de pesquisa célula D2 a # N / A error estará presente em célula E2.
- Este erro será corrigido quando adicionarmos os critérios de pesquisa na última etapa do tutorial.
Copiando a fórmula de pesquisa
A fórmula de pesquisa destina-se a recuperar dados de várias colunas da tabela de dados de uma só vez. Para fazer isso, a fórmula de pesquisa deve residir em todos os campos dos quais queremos informações.
Neste tutorial, queremos que ele recupere dados de colunas 2, 3e 4 da tabela de dados - ou seja, o preço, o número da peça e o nome do fornecedor quando inserimos um nome parcial como Lookup_value.
Como os dados são dispostos em um padrão regular na planilha, podemos copiar a fórmula de pesquisa em célula E2 para células F2 e G2.
À medida que a fórmula é copiada, o Excel atualizará a referência relativa da célula no COLUNA função (célula B1) para refletir a nova localização da fórmula.
Além disso, o Excel não altera a referência absoluta da célula $ D $ 2 e o intervalo nomeado Mesa como a fórmula é copiada.
Há mais de uma maneira de copiar dados no Excel, mas provavelmente a maneira mais fácil é usar o Preenchimento.
- Clique em célula E2, onde a fórmula de pesquisa está localizada, para torná-la a célula ativa.
- Coloque o ponteiro do mouse sobre o quadrado preto no canto inferior direito - o ponteiro mudará para um sinal de mais para indicar a alça de preenchimento.
- Clique no botão esquerdo do mouse e arraste a alça de preenchimento Através de célula G2.
- Lançamento o botão do mouse e célula F3 deve conter a fórmula de pesquisa bidimensional.
- Se feito corretamente, células F2 e G2 deve agora também conter o #N / D erro que está presente em célula E2.
Inserindo os critérios de pesquisa
Depois que a fórmula de pesquisa for copiada para as células necessárias, ela poderá ser usada para recuperar informações da tabela de dados. Para fazer isso, digite o nome do item você deseja recuperar no Lookup_value célula (D2) e pressione o botão Entrar tecla no teclado.
Uma vez feito, cada célula contendo a fórmula de pesquisa deve conter uma parte diferente dos dados sobre o item de hardware que você está procurando.
- Clique em célula D2 na planilha.
- Tipo Ferramenta para dentro célula D2 e pressione o Entrar tecla no teclado.
- As seguintes informações devem ser exibidas em células E2 para G2.
- E2: US $ 14,76 - o preço de um widget
- F2: PN-98769 - o número da peça de um widget
- G2: Widgets Inc. - o nome do fornecedor para widgets
- Teste o VLOOKUP fórmula de matriz mais, digitando o nome de outras partes em célula D2 e observando os resultados em células E2 para G2
