Função INDEX do Excel - Exemplos de fórmulas + vídeo grátis

Função INDEX do Excel (exemplos + vídeo)

Quando usar a função INDEX do Excel

A função INDEX do Excel pode ser usada quando você deseja obter o valor de dados tabulares e tem o número da linha e da coluna do ponto de dados. Por exemplo, no exemplo abaixo, você pode usar a função INDEX para obter as marcas de 'Tom' em Física quando você sabe o número da linha e o número da coluna no conjunto de dados.

O que retorna

Ele retorna o valor de uma tabela para o número da linha e o número da coluna especificados.

Sintaxe

= INDEX (matriz, núm_linha, [núm_col])
= INDEX (matriz, núm_linha, [núm_col], [núm_área])

A função INDEX tem 2 sintaxe. O primeiro é usado na maioria dos casos, no entanto, no caso de pesquisas de três vias, o segundo é usado (coberto no Exemplo 5).

Argumentos de entrada

• variedade - uma intervalo de células ou uma constante de matriz.
• row_num - o número da linha a partir da qual o valor deve ser obtido.
• [col_num] - o número da coluna da qual o valor deve ser obtido. Embora este seja um argumento opcional, se row_num não for fornecido, ele precisa ser fornecido.
• [area_num] - (Opcional) Se o argumento da matriz for composto de vários intervalos, esse número será usado para selecionar a referência de todos os intervalos.

Notas adicionais (coisas chatas … mas importantes para saber)

• Se o número da linha ou da coluna for 0, ele retornará os valores de toda a linha ou coluna, respectivamente.
• Se a função INDEX for usada na frente de uma referência de célula (como A1 :), ela retornará uma referência de célula em vez de um valor (consulte os exemplos abaixo).
• Mais amplamente utilizado junto com a função MATCH.
• Ao contrário de VLOOKUP, a função INDEX pode retornar um valor à esquerda do valor de pesquisa.
• A função INDEX tem duas formas - forma de matriz e forma de referência
  • ‘Forma de matriz’ é onde você busca um valor com base no número da linha e da coluna de uma determinada tabela.
  • 'Formulário de referência' é onde existem várias tabelas, e você usa o argumento area_num para selecionar a tabela e, em seguida, buscar um valor dentro dela usando o número da linha e da coluna (veja o exemplo ao vivo abaixo).

Função INDEX do Excel - Exemplos

Aqui estão seis exemplos de uso da função INDEX do Excel.

Exemplo 1 - Encontrando as marcas de Tom na física (uma pesquisa bidirecional)

Suponha que você tenha um conjunto de dados conforme mostrado abaixo:

Para encontrar as notas de Tom em Física, use a fórmula abaixo:

= ÍNDICE ($ B $ 3: $ E $ 10,3,2)

Esta fórmula INDEX especifica a matriz como $ B $ 3: $ E $ 10 que tem as marcas para todas as disciplinas. Em seguida, ele usa o número da linha (3) e o número da coluna (2) para buscar as marcas de Tom em Física.

Exemplo 2 - Tornando o valor LOOKUP dinâmico usando a função MATCH

Nem sempre é possível especificar o número da linha e o número da coluna manualmente. Você pode ter um grande conjunto de dados ou pode querer torná-lo dinâmico para que ele identifique automaticamente o nome e / ou assunto especificado nas células e forneça o resultado correto.

Algo conforme mostrado abaixo:

Isso pode ser feito usando uma combinação das funções INDEX e MATCH.

Esta é a fórmula que tornará os valores de pesquisa dinâmicos:

= ÍNDICE ($ B $ 3: $ E $ 10, CORRESPONDÊNCIA ($ G $ 5, $ A $ 3: $ A $ 10,0), CORRESPONDÊNCIA ($ H $ 4, $ B $ 2: $ E $ 2,0))

Na fórmula acima, em vez de codificar o número da linha e o número da coluna, a função MATCH é usada para torná-la dinâmica.

• O Número da Linha Dinâmica é dado pela seguinte parte da fórmula - CORRESPONDÊNCIA ($ G $ 5, $ A $ 3: $ A $ 10,0). Faz a varredura do nome dos alunos e identifica o valor de pesquisa ($ G $ 5 neste caso). Em seguida, ele retorna o número da linha do valor de pesquisa no conjunto de dados. Por exemplo, se o valor de pesquisa for Matt, ele retornará 1, se for Bob, ele retornará 2 e assim por diante.
• O Número da Coluna Dinâmica é dado pela seguinte parte da fórmula - CORRESPONDÊNCIA ($ H $ 4, $ B $ 2: $ E $ 2,0). Faz a varredura dos nomes dos assuntos e identifica o valor de pesquisa ($ H $ 4 neste caso). Em seguida, ele retorna o número da coluna do valor de pesquisa no conjunto de dados. Por exemplo, se o valor de pesquisa for Math, ele retornará 1, se for Physics, ele retornará 2 e assim por diante.

Exemplo 3 - Usando listas suspensas como valores de pesquisa

No exemplo acima, temos que inserir os dados manualmente. Isso pode ser demorado e sujeito a erros, especialmente se você tiver uma lista enorme de valores de pesquisa.

Uma boa ideia nesses casos é criar uma lista suspensa dos valores de pesquisa (neste caso, podem ser nomes de alunos e disciplinas) e, em seguida, simplesmente escolher na lista. Com base na seleção, a fórmula atualizaria automaticamente o resultado.

Algo conforme mostrado abaixo:

Isso é um bom componente do painel, pois você pode ter um grande conjunto de dados com centenas de alunos no back end, mas o usuário final (digamos um professor) pode obter rapidamente as notas de um aluno em uma disciplina simplesmente fazendo as seleções de a lista suspensa.

Como fazer isso:

A fórmula usada neste caso é a mesma usada no Exemplo 2.

= ÍNDICE ($ B $ 3: $ E $ 10, CORRESPONDÊNCIA ($ G $ 5, $ A $ 3: $ A $ 10,0), CORRESPONDÊNCIA ($ H $ 4, $ B $ 2: $ E $ 2,0))

Os valores de pesquisa foram convertidos em listas suspensas.

Aqui estão as etapas para criar a lista suspensa do Excel:

• Selecione a célula na qual deseja a lista suspensa. Neste exemplo, em G4, queremos os nomes dos alunos.
• Vá para Dados -> Ferramentas de dados -> Validação de dados.
• Na caixa Diálogo de validação de dados, na guia de configurações, selecione Lista no menu suspenso Permitir.
• Na fonte, selecione $ A $ 3: $ A $ 10
• Clique OK.

Agora você terá a lista suspensa na célula G5. Da mesma forma, você pode criar um em H4 para os assuntos.

Exemplo 4 - Retornar valores de uma linha / coluna inteira

Nos exemplos acima, usamos a função INDEX do Excel para fazer uma pesquisa bidirecional e obter um único valor.

Agora, e se você quiser obter todas as notas de um aluno. Isso pode permitir que você encontre a pontuação máxima / mínima daquele aluno, ou as notas totais marcadas em todas as disciplinas.

Em inglês simples, você deseja primeiro obter a linha inteira de pontuações de um aluno (digamos Bob) e, em seguida, dentro desses valores, identificar a pontuação mais alta ou o total de todas as pontuações.

Aqui está o truque.

Na função INDEX do Excel, quando você insere o número da coluna como 0, ele retornará os valores de toda a linha.

Portanto, a fórmula para isso seria:

= ÍNDICE ($ B $ 3: $ E $ 10, CORRESPONDÊNCIA ($ G $ 5, $ A $ 3: $ A $ 10,0), 0)

Agora, esta fórmula. se usado como está, retornaria o erro #VALUE! erro. Enquanto exibe o erro, no backend, ele retorna uma matriz que contém todas as pontuações de Tom - {57,77,91,91}.

Se você selecionar a fórmula no modo de edição e pressionar F9, poderá ver a matriz que ela retorna (conforme mostrado abaixo):

Da mesma forma, com base no valor de pesquisa, quando o número da coluna é especificado como 0 (ou é deixado em branco), ele retorna todos os valores na linha para o valor de pesquisa

Agora, para calcular a pontuação total obtida por Tom, podemos simplesmente usar a fórmula acima dentro da função SUM.

= SOMA (ÍNDICE ($ B $ 3: $ E $ 10, CORRESPONDÊNCIA ($ G $ 5, $ A $ 3: $ A $ 10,0), 0))

Em linhas semelhantes, para calcular a pontuação mais alta, podemos usar MAX / LARGE e para calcular o mínimo, podemos usar MIN / SMALL.

Exemplo 5 - Pesquisa de três vias usando INDEX / MATCH

A função ÍNDICE do Excel foi desenvolvida para lidar com pesquisas de três vias.

O que é uma pesquisa de três vias?

Nos exemplos acima, usamos uma tabela com pontuações para alunos em diferentes disciplinas. Este é um exemplo de uma pesquisa bidirecional, pois usamos duas variáveis ​​para obter a pontuação (nome do aluno e o assunto).

Agora, suponha que em um ano, um aluno tenha três níveis diferentes de exames, teste de unidade, meio-termo e exame final (era o que eu tinha quando era aluno).

Uma pesquisa de três vias seria a capacidade de obter as notas de um aluno para um determinado assunto do nível de exame especificado. Isso tornaria uma pesquisa de três vias, pois há três variáveis ​​(nome do aluno, nome da disciplina e o nível do exame).

Aqui está um exemplo de uma pesquisa de três vias:

No exemplo acima, além de selecionar o nome do aluno e o nome da disciplina, você também pode selecionar o nível do exame. Com base no nível do exame, ele retorna o valor correspondente de uma das três tabelas.

Aqui está a fórmula usada na célula H4:

= ÍNDICE (($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23), CORRESPONDÊNCIA ($ G $ 4, $ A $ 3: $ A $ 7,0), CORRESPONDÊNCIA ($ H $ 3, $ B $ 2: $ E $ 2,0), SE ($ H $ 2 = "Teste de unidade", 1, SE ($ H $ 2 = "Período intermediário", 2,3)))

Vamos analisar essa fórmula para entender como ela funciona.

Esta fórmula leva quatro argumentos. INDEX é uma das funções do Excel que possui mais de uma sintaxe.

= INDEX (matriz, núm_linha, [núm_col])
= INDEX (matriz, núm_linha, [núm_col], [núm_área])

Até agora, em todo o exemplo acima, usamos a primeira sintaxe, mas para fazer uma pesquisa de três vias, precisamos usar a segunda sintaxe.

Agora vamos ver cada parte da fórmula com base na segunda sintaxe.

• array - ($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23): Em vez de usar um único array, neste caso, usamos três arrays entre parênteses.
• row_num - MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0): a função MATCH é usada para encontrar a posição do nome do aluno na célula $ G $ 4 na lista de nomes do aluno.
• col_num - MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0): a função MATCH é usada para encontrar a posição do nome do sujeito na célula $ H $ 3 na lista de nomes do sujeito.
• [area_num] - IF ($ H $ 2 = ”Teste de unidade”, 1, IF ($ H $ 2 = ”Meio do período”, 2,3)): O valor do número da área informa à função INDEX qual array selecionar. Neste exemplo, temos três matrizes no primeiro argumento. Se você selecionar Unit Test no menu suspenso, a função IF retorna 1 e as funções INDEX selecionam a 1ª matriz das três matrizes (que é $ B $ 3: $ E $ 7).

Exemplo 6 - Criação de uma referência usando a função INDEX (intervalos nomeados dinâmicos)

Este é um uso selvagem da função ÍNDICE do Excel.

Vamos dar um exemplo simples.

Eu tenho uma lista de nomes conforme mostrado abaixo:

Agora posso usar uma função INDEX simples para obter o sobrenome da lista.

Aqui está a fórmula:

= ÍNDICE ($ A $ 2: $ A $ 9, CONTAGEM ($ A $ 2: $ A $ 9))

Esta função simplesmente conta o número de células que não estão vazias e retorna o último item da lista (funciona apenas quando não há espaços em branco na lista).

Agora, o que aí vem a mágica.

Se você colocar a fórmula na frente de uma referência de célula, a fórmula retornará uma referência de célula do valor correspondente (em vez do próprio valor).

= A2: ÍNDICE ($ A $ 2: $ A $ 9, CONTAGEM ($ A $ 2: $ A $ 9))

Você esperaria que a fórmula acima retornasse = A2: ”Josh” (onde Josh é o último valor da lista). No entanto, ele retorna = A2: A9 e, portanto, você obtém uma matriz de nomes conforme mostrado abaixo:

Um exemplo prático em que essa técnica pode ser útil é na criação de intervalos nomeados dinâmicos.

É isso neste tutorial. Eu tentei cobrir os principais exemplos de uso da função INDEX do Excel. Se você gostaria de ver mais exemplos adicionados a esta lista, deixe-me saber na seção de comentários.

Nota: Eu tentei o meu melhor para revisar este tutorial, mas caso você encontre algum erro ortográfico, por favor me avise 🙂

Função Excel INDEX - Vídeo Tutorial

• Função VLOOKUP do Excel.
• Função HLOOKUP do Excel.
• Função INDIRETA do Excel.
• Função Excel MATCH.
• Função Excel OFFSET.

Você também pode gostar dos seguintes tutoriais do Excel:

• VLOOKUP vs. ÍNDICE / CORRESPONDÊNCIA
• Correspondência de índice do Excel
• Valores de pesquisa e retorno em uma linha / coluna inteira.