Usando uma fórmula de matriz no Excel, podemos criar uma fórmula de pesquisa que usa vários critérios para localizar informações em um banco de dados ou tabela de dados. A fórmula de matriz envolve o aninhamento da função MATCH dentro da função INDEX.
Este tutorial inclui um exemplo passo a passo de criação de uma fórmula de pesquisa que usa vários critérios para localizar um fornecedor deWidgets de titânio em um banco de dados de amostra. Acompanhe para saber como é fácil criar suas próprias fórmulas de pesquisa personalizadas.
Prepare sua pasta de trabalho do Excel com dados
Para seguir as etapas deste tutorial, você precisará começar inserindo os dados de amostra nas seguintes células, conforme mostrado na imagem acima. As linhas 3 e 4 são deixadas em branco para acomodar a fórmula de matriz criada durante este tutorial.
- Insira o intervalo superior de dados nas células D1 para F2
- Digite o segundo intervalo em células D5 para F11
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 opções de formatação semelhantes às vistas acima estão disponíveis neste tutorial de formatação básica do Excel.
02 de 08Criar uma função INDEX no Excel
o ÍNDICE A função é uma das poucas no Excel que possui vários formulários. A função tem um Formulário de Matriz e um Formulário de Referência. O Array Form retorna os dados reais de um banco de dados ou tabela de dados, enquanto o Formulário de Referência fornece a referência da célula ou o local dos dados na tabela.
Neste tutorial, usaremos o Formulário de Matriz, pois queremos saber o nome do fornecedor para os widgets de titânio, em vez da referência de célula a esse fornecedor em nosso banco de dados. Siga estas etapas para criar sua função INDEX:
- Clique na célula F3 para torná-lo a célula ativa - é aqui que entraremos na função aninhada.
- Clique no Fórmulas guia do menu da faixa de opções.
- Escolher Pesquisa e Referência da barra de fita para abrir a função suspensa.
- Clique em ÍNDICE na lista para trazer o Selecione Argumentos caixa de diálogo.
- Escolha o matriz, row_num, col_num opção na caixa de diálogo.
- Clique Está bem para abrir a caixa de diálogo da função ÍNDICE; isso abrirá o Formula Builder no Excel.
- No Construtor de fórmulas, Clique no Matriz linha.
- Realçar células D6 para F11, clicando e arrastando, na planilha para inserir o intervalo no construtor.
Em versões mais antigas do Excel, o Construtor de fórmulas será substituído pelo Argumentos da Função janela. Aplique as mesmas etapas mencionadas neste tutorial na janela.
03 de 08Inicie a função MATCH aninhada
Ao aninhar uma função dentro de outra, não é possível abrir o construtor da fórmula da segunda função ou o aninhado o insira os argumentos necessários. A função aninhada deve ser digitada como uma das ordenadas da primeira função.
Ao inserir funções manualmente, os argumentos da função são separados uns dos outros por uma vírgula.
O primeiro passo para entrar na função MATCH aninhada é entrar no Lookup_value argumento. O Lookup_value será o local ou referência de célula para o termo de pesquisa que queremos corresponder no banco de dados.
Normalmente, o Lookup_value aceita apenas um critério ou termo de pesquisa. Para pesquisar vários critérios, devemos estender o Lookup_value; tisso é feito concatenando ou unindo duas ou mais referências de célula usando o símbolo "e" comercial - &.
- No Construtor de fórmulas, Clique no Row_num linha.
- Digite o nome da função PARTIDA seguido por um suporte circular aberto.
- Clique na célula D3 para inserir essa referência de célula na caixa de diálogo.
- Digite um e comercial após a referência da célula D3 para adicionar uma segunda referência de célula.
- Clique na célula E3 para inserir essa segunda referência de célula.
- Digite uma vírgula após a referência da célula E3 para completar a entrada da função MATCH Lookup_value argumento.
Na última etapa do tutorial, nossa Lookup_values serão inseridos nas células D3 e E3 da planilha.
04 de 08Complete a função MATCH aninhada
Esta etapa cobre a adição do Lookup_array argumento para o aninhado PARTIDA função. O Lookup_array é o intervalo de células que a função MATCH pesquisará para localizar o argumento Lookup_value adicionado na etapa anterior do tutorial.
Como identificamos dois campos de pesquisa no argumento Lookup_array, devemos fazer o mesmo para o Lookup_array. A função CORRESP apenas procura uma matriz para cada termo especificado, portanto, para inserir várias matrizes, usamos novamente o E comercial para concatenar as matrizes juntas.
Estes passos devem ser introduzidos após a vírgula introduzida no passo anterior no Row_num linha no ÍNDICE função.
- Clique no Row_num linha após a vírgula para colocar o ponto de inserção no final da entrada atual.
- Realçar células D6 para D11 na planilha para inserir o intervalo - esta é a primeira matriz que a função é pesquisar.
- Digite um e comercial após as referências de célula D6: D11 porque queremos que a função pesquise dois arrays.
- Realçar células E6 para E11 na planilha para inserir o intervalo - esta é a segunda matriz que a função é pesquisar.
- Digite uma vírgula após a referência da célula E3 para completar a entrada do PARTIDA funções Lookup_array argumento.
- Deixe o ÍNDICE caixa de diálogo de função aberta para a próxima etapa do tutorial.
Adicione o argumento do tipo MATCH
O terceiro e último argumento da função MATCH é o Tipo de partida argumento; ele diz ao Excel como combinar Lookup_value com valores no Lookup_array - as opções disponíveis são 1, 0 ou -1.
Este argumento é opcional. Se for omitido, a função usa o valor padrão de 1.
- Se o Match_type = 1 ou é omitido: MATCH localiza o maior valor que é menor ou igual ao Lookup_value. Os dados de Lookup_array devem ser classificados em ordem crescente.
- Se o Match_type = 0: MATCH localiza o primeiro valor que é exatamente igual ao Lookup_value. Os dados Lookup_array podem ser classificados em qualquer ordem.
- Se o Match_type = -1: MATCH localiza o menor valor que é maior que ou igual ao Lookup_value. Os dados de Lookup_array devem ser classificados em ordem decrescente.
Estes passos devem ser introduzidos após a vírgula introduzida no passo anterior no Row_num linha no ÍNDICE função.
- Após a vírgula no Row_num linha, digite um zero - 0 - como queremos que a função aninhada retorne correspondências exatas aos termos que inserimos nas células D3 e E3.
- Digite um suporte redondo de fechamento - ) - para completar o PARTIDA função.
- Deixe o Construtor de fórmulas abra para o próximo passo no tutorial.
Finalizando a Função INDEX
Agora que o PARTIDA função é feita, vamos passar para a terceira linha do Formula Builder e digite o último argumento para o ÍNDICE função. Este terceiro e último argumento é o Column_num argumento que informa ao Excel o número da coluna no intervalo D6 para F11; é aqui que ele encontrará as informações que queremos que sejam retornadas pela função. Neste caso, um fornecedor para widgets de titânio.
- Clique no Column_num linha.
- Digite o número três - 3 - nesta linha, uma vez que estamos procurando por dados na terceira coluna do intervalo D6 para F11.
- Mais uma vez, deixe o Construtor de fórmulas abra para o próximo passo no tutorial.
Criando a fórmula da matriz
Antes de fechar o Construtor de fórmulas, precisamos transformar nossa função aninhada em uma fórmula de matriz; isso é o que permite que nossa função pesquise vários termos na tabela de dados. Neste tutorial, estamos procurando combinar dois termos: Widgets da coluna 1 e Titânio da coluna 2.
A criação de uma fórmula de matriz no Excel é feita pressionando o botão CTRL, MUDANÇAe ENTRAR teclas no teclado ao mesmo tempo. Uma vez pressionada, a função será cercada por chaves, indicando que a função agora é uma matriz.
- Feche o Construtor de fórmulas clicando no Feito botão.
- Em seguida, selecione celular F4 e pressione o Entrar chave para ver a fórmula.
- Para converter a fórmula em uma matriz, pressione simultaneamente CTRL + SHIFT + ENTER no seu teclado.
- Se feito corretamente um #N / D erro aparecerá na célula F3 - a célula onde entramos na função.
- o #N / D erro aparece na célula F3 porque as células D3 e E3 estão em branco. D3 e E3 são as células onde dissemos a função para encontrar o Lookup_values. Depois que os dados forem adicionados a essas duas células, o erro será substituído pelas informações do banco de dados.
Adicionando os Critérios de Pesquisa
Por fim, adicionaremos os termos de pesquisa à nossa planilha. Como mencionado na etapa anterior, estamos procurando combinar os termos Widgets da coluna 1 e Titânio da coluna 2. Se, e somente se, nossa fórmula encontrar uma correspondência para os dois termos nas colunas apropriadas no banco de dados, ela retornará o valor da terceira coluna.
- Clique na célula D3.
- Tipo Widgets e pressione o Entrar tecla no teclado.
- Clique na célula E3.
- Tipo Titânio e pressione o Entrar tecla no teclado.
- O nome do fornecedor Widgets Inc. deve aparecer na célula F3 - a localização da função, pois é o único fornecedor listado que vende Titanium Widgets.
- Quando você clica na célula F3, a função completa aparece na barra de fórmulas acima da planilha.
{= ÍNDICE (D6: F11, CORRESP (D3 & E3, D6: D11 & E6: E11, 0), 3)}
Em nosso exemplo, havia apenas um fornecedor para widgets de titânio. Se houver mais de um fornecedor, o fornecedor listado primeiro no banco de dados é retornado pela função.
