Função ÍNDICE no Excel: Como usar?

julho 30, 2018fevereiro 16, 2023 admin 0 comentários

Quer aprender a usar a função ÍNDICE no Excel? Então você está no lugar certo!

Se você acredita que a função PROCV é capaz de fazer milagres em suas planilhas, então espere só para conhecer a função ÍNDICE.

Com essa função, você será capaz de realizar buscas mais dinâmicas, rápidas e flexíveis. Acompanhe os detalhes a seguir e confira tudo o que você precisa saber para dominar a função ÍNDICE no Excel.

Já está interessado(a) no que vem por aí? Então compartilhe com sua rede de amigos para que mais pessoas possam a usar a função ÍNDICE no Excel:

Facebook

Twitter

LinkedIn

Quando usar a função ÍNDICE no Excel?

A função ÍNDICE no Excel pode ser usada quando se deseja buscar o valor em uma tabela e se tem o número da linha e o número da coluna do ponto onde o dado se encontra. Para melhor compreensão, observe o exemplo abaixo onde podemos usar a função ÍNDICE para obter as notas da aluna Bianca na disciplina de Física quando souber o número da linha e o número da coluna no conjunto de dados.

Como funciona a função ÍNDICE?

A função ÍNDICE no Excel retorna o valor de uma tabela para o número da linha e o número da coluna especificados.

Sintaxe:

ÍNDICE(matriz;núm_linha;[núm_coluna])
ÍNDICE(ref;núm_linha;[núm_coluna];[núm_área])

A função ÍNDICE possui 2 sintaxes. A primeira é usada na maioria dos casos, no entanto, no caso de pesquisas de três vias, a segunda sintaxe é usada (veja o Exemplo 5).

Argumentos de entrada

matriz – Representa um intervalo de células ou uma constante de matriz.
núm_linha – Representa o número da linha a partir da qual o valor deve ser buscado.
[núm_coluna] – (Argumento Opcional). Representa o número da coluna a partir da qual o valor deve ser buscado. Embora este seja um argumento opcional, se o argumento núm_linha não for fornecido, então será necessário fornecer o argumento núm_coluna.
[núm_área] – (Argumento 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.

Exemplos de uso da Função ÍNDICE no Excel

Confira abaixo alguns exemplos de uso da função ÍNDICE no Excel que permitirá obter o domínio dessa função:

Exemplo 1: Encontrar notas de um aluno em uma matéria (pesquisa de 2 critérios)

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

Para encontrar as notas de Bianca na disciplina de Física, basta utilizar a seguinte fórmula:

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

A fórmula acima especifica a matriz como $B$3:$E$10, que possui as notas de todas as matérias. Em seguida, ela usa o número da linha (4) e o número da coluna (2) para buscar as notas de Bianca em Física.

Exemplo 2: Tornando a busca dinâmica com a função CORRESP

Nem sempre é possível especificar o número da linha e o número da coluna manualmente quando se pretende usar a função ÍNDICE no Excel. Em certas ocasiões, você pode ter um grande conjunto de dados, ou simplesmente pode querer tornar a busca dinâmica para que ela identifique automaticamente o nome do aluno e / a matéria especificada nas células e forneça o resultado correto.

Algo como mostrado no exemplo abaixo:

Isso pode ser feito combinando as funções ÍNDICE com CORRESP. Aqui está a fórmula que tornará os valores de pesquisa dinâmicos:

=ÍNDICE($B$3:$E$10;CORRESP($G$6;$A$3:$A$10;0);CORRESP($H$5;$B$2:$E$2;0))

Observe que na fórmula acima, em vez de inserirmos o número da linha e o número da coluna, a função CORRESP é usada para torná-la dinâmica.

O número da linha dinâmica é dado pela seguinte parte da fórmula – ($G$6;$A$3:$A$10;0). Ele verifica o nome dos alunos e identifica o valor da pesquisa ($G$6 nesse 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 Mateus, ele retornará 1, se for Bruno, retornará 2 e assim por diante.
O número da coluna dinâmica é dado pela seguinte parte da fórmula – CORRESP($H$5;$B$2:$E$2;0). Ele verifica os nomes das matérias e identifica o valor de pesquisa ($H$5 nesse 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 Matemática, ele retornará 1, se for Física, retornará 2 e assim por diante.

Exemplo 3 – Usando listas suspensas como valores de pesquisa

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

Uma boa dica nesses casos é criar uma lista suspensa os valores de pesquisa (nesse caso, poderia ser nomes e matérias dos alunos) e, em seguida, simplesmente escolher na lista. Com base na seleção, a fórmula atualizará automaticamente o resultado.

Algo como mostrado abaixo:

Isso pode ser muito útil para a criação de dashboards, pois você pode ter uma grande quantidade de dados em em uma outra planilha, mas o usuário final (digamos, um professor), poderá consultar as notas de um aluno em uma matéria simplesmente fazendo as seleções na lista suspensa.

Como fazer isso?

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

=ÍNDICE($B$3:$E$10;CORRESP($G$6;$A$3:$A$10;0);CORRESP($H$5;$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 você deseja a lista suspensa. Neste exemplo, no G5, queremos os nomes dos alunos.
Acesse a guia Dados > Validação de Dados.
Na caixa Diálogo de validação de dados, na guia Configurações, selecione Lista na lista suspensa Permitir.
Na fonte, selecione $A$3:$A$10
Clique em OK.

Agora você terá a lista suspensa na célula G6. Da mesma forma, você pode criar um em H5 para as matérias.

Exemplo 4 – Retornar valores de uma linha / coluna inteira

Nos exemplos anteriores, usamos a função ÍNDICE no Excel para fazer uma pesquisa bidirecional (com dois critérios) e obter um valor único.

Agora, e se você quiser obter todas as notas de um aluno? Isso possibilitaria encontrar a nota máxima / mínima desse aluno, ou o total de pontos marcados em todas as matérias.

Em uma linguagem simples, queremos primeiro obter a linha inteira de notas para um aluno (digamos, Bruno) e, em seguida, dentro desses valores, identificar a nota mais alta ou o total de todas as notas.

Aqui está o truque.

Na função ÍNDICE do Excel, quando você insere o número da coluna como 0 , ele retornará os valores da linha inteira.

Então a fórmula seria:

Agora esta fórmula, se usada como está, retornará o erro #VALOR!. Enquanto a função exibe o erro, no back-end, ela retorna uma matriz que tem todas as notas de Tomas – {57,77,91,91}.

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

Do mesmo modo, com base em qual é o 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 Tomas, podemos simplesmente usar a fórmula acima dentro da função SOMA.

=SOMA(ÍNDICE($B$3:$E$10;CORRESP($G$6;$A$3:$A$10;0);0))

Da mesma forma, para exibir a nota mais alta, podemos usar a função MÁXIMO e, para exibir a nota mais baixa do aluno, podemos usar a função MÍNIMO.

Exemplo 5: Pesquisa de 3 critérios usando ÍNDICE e CORRESP

Nos exemplos anteriores, usamos uma tabela com notas de alunos em diferentes disciplinas. Este é um exemplo de uma pesquisa com dois critérios, pois usamos duas variáveis para buscar a nota (o nome do aluno e a matéria).

Agora, suponha que em um ano, um aluno tenha três tipos diferentes de exames sendo Exame 1, Exame 2 e Exame Final.

Neste caso, uma pesquisa de três critérios seria capaz de mostrar as notas de um aluno para uma determinada matéria a partir do exame especificado. Isso tornaria uma pesquisa de três vias, pois há três variáveis (nome do aluno, nome da matéria e tipo de exame).

Abaixo temos um exemplo de uma pesquisa de três critérios usando a função ÍNDICE no Excel junto com a função CORRESP:

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

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

=ÍNDICE(($B$3:$E$7;$B$11:$E$15;$B$19:$E$23);CORRESP($G$4;$A$3:$A$7;0);CORRESP($H$3;$B$2:$E$2;0);SE($H$2=”Exame 2″;1;SE($H$2=”Exame 1″;2;3)))

Vamos dividir essa fórmula para compreender como ela funciona. Esta fórmula leva quatro argumentos.

Conforme dito anteriormente, a função ÍNDICE é uma funções no Excel que possui mais de uma sintaxe. Sendo:

ÍNDICE(matriz;núm_linha;[núm_coluna])
ÍNDICE(ref;núm_linha;[núm_coluna];[núm_área])

Até agora, em todos os exemplos anteriores, usamos a primeira sintaxe, mas para fazer uma pesquisa de três critérios, precisamos usar a segunda sintaxe.

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

ref – ($B$3:$E$7;$B$11:$E$15;$B$19:$E$23): Em vez de usar uma única matriz, neste caso, usamos três matrizes entre parênteses.
núm_linha – CORRESP($G$4;$A$3:$A$7;0): A função CORRESP é usada para encontrar a posição do nome do aluno na célula $G$4 na lista do nome do aluno.
núm_coluna – CORRESP($H$3;$B$2:$E$2;0): A função CORRESP é usada para encontrar a posição do nome da disciplina na célula $H$3 na lista do nome das disciplinas.
[núm_área] – SE($H$2=”Exame 2″;1;SE($H$2=”Exame 1″;2;3)) : O valor do número da área informa à função ÍNDICE qual matriz selecionar. Neste exemplo, temos três matrizes no primeiro argumento. Se você selecionar Exame 1 na lista suspensa, a função SE retornará 1 e as funções ÍNDICE selecionarão a 1ª matriz das três matrizes (que é $B$3:$E$7).

Notas adicionais sobre a função ÍNDICE

Ao usar a função ÍNDICE no Excel, é preciso estar atento a alguns pontos importantes. São eles:

Se o número da linha ou o número da coluna for 0, a função retornará os valores da linha ou coluna inteira, respectivamente.
Se a função ÍNDICE 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.
A função ÍNDICE é mais amplamente utilizada junto com a função CORRESP.
Ao contrário da função PROCV, a função ÍNDICE pode retornar um valor da esquerda do valor de pesquisa.
A função ÍNDICE tem dois formulários – Formulário de Matriz e o Formulário de Referência
- ‘Formulário 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 há várias tabelas, e você usa o argumento área_núm para selecionar a tabela e depois buscar um valor dentro dela usando o número da linha e da coluna (para melhor compreensão consulte o exemplo 5 deste tutorial).

Baixe aqui a planilha com os exemplos apresentados neste tutorial

E então, o que achou da explicação sobre a função ÍNDICE no Excel? Deixe o seu comentário abaixo, gostaríamos muito de conhecer a sua opinião! Para conferir mais dicas de Excel, continue acompanhando o Excel Easy!