Neste tutorial, vou apresentar vários exemplos de fórmulas ÍNDICE do Excel que demonstram o uso mais eficientes dessa função.
Mas, o que a função ÍNDICE faz?
Definitivamente, uma fórmula ÍNDICE retorna uma referência de célula dentro de um determinada matriz ou intervalo. Em outras palavras, usamos o ÍNDICE quando já conhecemos a posição de um elemento em um intervalo e quando desejamos obter o valor real desse elemento.
Pode parecer um pouco trivial, mas uma vez que se conhece o verdadeiro potencial da função ÍNDICE, pode ser feita alterações importantes na maneira de calcular, analisar e apresentar dados nas planilhas.
Função ÍNDICE do Excel – sintaxe e uso básico
Existem duas versões da função ÍNDICE no Excel – formulário de matriz e formulário de referência. Ambos os formulários podem ser usados em todas as versões do Microsoft Excel 2013, Excel 2010, Excel 2007 e 2003.
Formulário de matriz ÍNDICE
O formulário da matriz ÍNDICE retorna o valor de um elemento em uma tabela ou matriz com base nos números de linhas e colunas especificados.
= ÍNDICE (matriz; número da linha; [número da coluna])
- matriz – é um intervalo de células, denominado intervalo ou tabela.
- núm_linha – é o número da linha na matriz da qual retorna um valor. Se núm_linha for omitido, núm_coluna será necessário.
- núm_coluna – é o número da coluna a partir da qual retorna um valor. Se núm_coluna for omitido, núm_linha será necessário.
Por exemplo, a fórmula = ÍNDICE (A1:D6; 4; 3) retorna o valor na interseção da quarta linha e da terceira coluna no intervalo A1:D6, que é o valor na célula C4.
Para se ter uma ideia de como a fórmula do ÍNDICE do Excel funciona com dados reais, acompanhe o exemplo abaixo:
Em vez de inserir o número de linhas e colunas na fórmula, podemos fornecer as referências de célula para obter uma fórmula mais universal: = ÍNDICE ($B$2:$D$6;G2;G1)
Portanto, essa fórmula ÍNDICE retorna o número de itens exatamente na interseção do número do produto especificado em G2 (número da linha) e o número da semana inserido na célula G1 (número da coluna).
Dica. O uso de referências absolutas ($B$ 2:$D$6) em vez de referências relativas (B2:D6) no argumento da matriz, facilita a cópia da fórmula para outras células. Como alternativa, você pode converter um intervalo em uma tabela (Ctrl + T) e consultá-lo pelo nome da tabela.
Para saber mais sobre sobre referências absolutas e relativas clique aqui!!!
Formulário de matriz ÍNDICE – coisas para lembrar
- Se o argumento da matriz consistir em apenas uma linha ou coluna, podemos ou não especificar o argumento núm_linha ou núm_coluna correspondente.
- Se o argumento da matriz incluir mais de uma linha e núm_linha for omitido ou definido como 0, a função ÍNDICE do Excel retornará uma matriz da coluna inteira. Da mesma forma, se a matriz incluir mais de uma coluna e o argumento núm_coluna for omitido ou definido como 0, a fórmula ÍNDICE retornará a linha inteira. Aqui está um exemplo de fórmula que demonstra esse comportamento.
- Os argumentos núm_linha e num_coluna devem se referir a uma célula na matriz; caso contrário, a fórmula ÍNDICE retornará o erro #REF!.
Formulário de referência ÍNDICE
O formulário de referência da função ÍNDICE do Excel retorna a referência de célula na interseção da linha e coluna especificadas.
= ÍNDICE (referência; número da linha; [número da coluna]; [número da área])
- referência – é um ou vários intervalos.
Se for inserindo mais de um intervalo, separe-os por ponto-e-vírgula e coloque o argumento de referência entre parênteses, por exemplo (A1:B5; D1:F5).
Se cada intervalo na referência contiver apenas uma linha ou coluna, o argumento núm_linha ou núm_coluna correspondente será opcional.
- núm_linha – o número da linha no intervalo do qual retorna uma referência de célula, é semelhante ao formulário da matriz.
- núm_coluna – o número da coluna a partir da qual retorna uma referência de célula, também funciona de maneira semelhante ao formulário da matriz.
- núm_área – um parâmetro opcional que especifica qual intervalo do argumento de referência usar. Se omitido, a fórmula ÍNDICE retornará o resultado para o primeiro intervalo listado na referência.
Por exemplo, a fórmula = ÍNDICE ((A2:D3; A5:D7); 3; 4; 2) retorna o valor da célula D7, que está na interseção da terceira linha e da quarta coluna na segunda área (A5:D7).
Formulário de referência ÍNDICE – coisas para lembrar
- Se o argumento núm_linha ou núm_coluna estiver definido como zero (0), uma fórmula ÍNDICE do Excel retornará a referência para a coluna ou linha inteira.
- Se o número da linha e número da coluna forem omitidos, a função ÍNDICE retorna a área especificada no argumento núm_área.
- Todos os argumentos (núm_linha, núm_coluna e núm_área) devem se referir a uma célula dentro da referência; caso contrário, a fórmula ÍNDICE retornará o erro #REF!.
As duas fórmulas ÍNDICE que discutimos até agora são muito simples e ilustram apenas o conceito. É provável que você use fórmulas mais complexas do que essa, então vamos explorar alguns usos mais eficientes da função ÍNDICE no Excel.
Como usar a função ÍNDICE no Excel – exemplos de fórmulas
Talvez, a função ÍNDICE não aparenta ser tão prática quanto realmente é, mas em combinação com outras funções ela se encaixa muito bem, como as funções CORRESP ou CONT.VALORES. Juntas podem criar fórmulas poderosas.
Acesse o conteúdo: Como usar a função CORRESP no Excel clicando aqui!!!
Acesse o conteúdo: Função ÍNDICE e CORRESP no Excel clicando aqui!!!
Acesse o conteúdo: Como contar linhas, colunas, células ou valores no Excel clicando aqui!!
Dados de origem:
Para todas as fórmulas da função ÍNDICE deste artigo (exceto a última), serão usados os dados abaixo:
O uso de tabelas ou intervalos nomeados pode tornar as fórmulas um pouco mais longas, mas também as torna significativamente mais flexíveis e legíveis. Para ajustar qualquer fórmula ÍNDICE para suas planilhas, você precisa modificar apenas um único nome, e isso compensa completamente um comprimento maior da fórmula.
Obviamente, nada impede de se usar intervalos usuais, se quiser. Nesse caso, podemos simplesmente substituir o nome da tabela Tabela2 pela referência de intervalo apropriada.
1. Obtendo o enésimo item da lista
Este é o uso básico e o exemplo mais fácil da função ÍNDICE do Excel. Para buscar um determinado item da lista, basta escrever = ÍNDICE (intervalo, n) onde o intervalo refere-se ao intervalo de células ou um intervalo nomeado e n é a posição do item que queremos obter.
Ao trabalhar com tabelas do Excel, podemos selecionar a coluna usando o mouse e o Excel puxará o nome da coluna junto com o nome da tabela na fórmula:
Para obter o valor da célula na interseção de uma determinada linha e coluna, use a mesma abordagem com uma única diferença: especifique o número da linha e o número da coluna.
E aqui está mais um exemplo. Com base na nossa tabela de amostra, para encontrar o segundo maior planeta do sistema Solar, classifique a tabela pela coluna Diâmetro e use a seguinte fórmula ÍNDICE:
= ÍNDICE (Tabela2; 2; 3)
- matriz é o nome da tabela ou uma referência de intervalo, Tabela2 neste exemplo.
- núm_linha é 2 porque estamos procurando o segundo item da lista.
- núm_coluna é 3 porque Diâmetro é a terceira coluna da tabela.
Se deseja retornar o nome do planeta em vez de diâmetro, altere núm_coluna para 1, e naturalmente, pode ser usado uma referência de célula nos argumentos núm_linha e / ou núm_coluna para tornar sua fórmula ÍNDICE mais versátil, conforme demonstrado abaixo:
2. Obtendo todos os valores em uma linha ou coluna
Além de recuperar uma única célula, a função ÍNDICE do Excel pode retornar uma matriz de valores de toda a linha ou coluna. Para obter todos os valores de uma determinada coluna, deve se omitir o argumento núm_linha ou configurá-lo como 0. Da mesma forma, para obter a linha inteira, o valor vazio ou 0 deve ser passado para núm_coluna.
Essas fórmulas ÍNDICE dificilmente podem ser usadas por conta própria, porque o Excel não pode ajustar a matriz de valores retornados pela fórmula em uma única célula e obteríamos o erro # VALOR! em vez disso. No entanto, devemos usar a função ÍNDICE em conjunto com outras funções do Excel, como SOMA ou MÉDIA, para obter resultados impressionantes.
Por exemplo, pode ser usada a seguinte fórmula do ÍNDICE Excel para calcular a temperatura média do planeta no sistema Solar:
= MÉDIA (ÍNDICE (Tabela2;; 4))
Na fórmula acima, o argumento núm_coluna é 4 porque Temperatura esta na quarta coluna da nossa tabela. O argumento núm_linha é omitido.
De maneira semelhante, podemos encontrar as temperaturas mínima e máxima:
= MÁXIMO (ÍNDICE (Tabela2;; 4))
= MÍNIMO (ÍNDICE (Tabela2;; 4))
Para calcular a massa total do planeta, sendo que Massa esta na 2ª coluna da tabela, use a fórmula:
= SOMA (ÍNDICE (Tabela2;; 2))
Do ponto de vista prático, a função ÍNDICE na fórmula acima é desnecessária. Podemos simplesmente escrever = MÉDIA (intervalo) ou = SOMA (intervalo) e obter os mesmos resultados.
Quando trabalhamos com dados reais, esse recurso pode ser útil como parte de fórmulas mais complexas usadas para análise de dados.
3. Usando ÍNDICE com outras funções do Excel (SOMA, MÉDIA, MÁXIMO, MÍNIMO)
Nos exemplos anteriores, você deve ter tido a impressão de que uma fórmula ÍNDICE no Excel retorna valores, mas a realidade é que retorna uma referência à célula que contém o valor. Este exemplo demonstra a verdadeira natureza da função ÍNDICE do Excel.
Como o resultado de uma fórmula ÍNDICE é uma referência, podemos usá-la em outras funções para criar um intervalo dinâmico. Parece confuso? A fórmula a seguir deixará mais claro.
Suponha que você tenha uma fórmula = MÉDIA (A1: A10) que retorna uma média dos valores nas células A1: A10. Em vez de escrever o intervalo diretamente na fórmula, podemos substituir A1 ou A10, ou ambos, por funções ÍNDICE, desta forma:
= MÉDIA (A1: ÍNDICE (A1: A20;10))
Ambas as fórmulas acima fornecerão o mesmo resultado, porque a função ÍNDICE também retorna uma referência à célula A10 (número da linha definido como 10; nome da coluna omitido). A diferença é que o intervalo da fórmula MÉDIA / ÍNDICE é dinâmica e, depois de alterar o argumento núm_linha em ÍNDICE, o intervalo processado pela função MÉDIA será alterado e a fórmula retornará um resultado diferente.
Aparentemente, a rota da fórmula ÍNDICE parece excessivamente complicada, mas possui aplicações práticas, conforme demonstrado nos exemplos a seguir.
Exemplo 1. Calcule a média do enésimo item principal da lista
Digamos que a gente queira saber o diâmetro médio dos N maiores planetas do nosso sistema. Para isso, classificamos a tabela por coluna Diâmetro do maior para o menor e usamos a seguinte fórmula MÉDIA/ ÍNDICE:
= MÉDIA (C5: ÍNDICE (Tabela2 [Diâmetro (km)]; B1))
Exemplo 2. Soma itens entre os dois itens especificados
Caso deseje definir os itens de limite superior e inferior em sua fórmula, basta empregar duas funções ÍNDICE para retornar o primeiro e o último item desejado.
Por exemplo, a fórmula a seguir retorna a soma dos valores na coluna Diâmetro entre os dois itens especificados nas células B1 e B2:
= SOMA (ÍNDICE (Tabela2 [Diâmetro (Km)]; B1): ÍNDICE (Tabela2 [Diâmetro (Km)]; B2))
4. O poderoso PROCV com ÍNDICE / CORRESP
Executar pesquisas no Excel – é aqui que a função ÍNDICE do Excel realmente brilha!
Se você já experimentou usar a função PROCV do Excel, conhece bem as limitações, como a incapacidade de extrair valores das colunas à esquerda da coluna de pesquisa ou o limite de 255 caracteres para um valor de pesquisa.
O contato ÍNDICE / CORRESP é superior ao PROCV em muitos aspectos:
- Sem problemas com visualizações à esquerda.
- Não há limite para o tamanho do valor da pesquisa.
- Nenhuma classificação é necessária (o PROCV com correspondência aproximada exige a classificação da coluna de pesquisa em ordem crescente).
- É possível inserir e remover colunas em uma tabela sem atualizar todas as fórmulas associadas.
- E por último, mas não menos importante, ÍNDICE / CORRESP não diminui a velocidade do seu Excel, como fazem vários PROCVs.
As funções ÍNDICE / CORRESP é usada da seguinte maneira:
=ÍNDICE (coluna que retorna o valor desejado; (CORRESP ( valor de pesquisa; coluna para pesquisa; 0))
Para mais dicas e exemplos de fórmulas, consulte o tutorial Excel ÍNDICE/CORRESP.
5. Fórmula Excel ÍNDICE para obter 1 intervalo de uma lista de intervalos
Outro uso inteligente e poderoso da função ÍNDICE no Excel é a capacidade de obter um intervalo de uma lista de intervalos.
Suponha que você tenha várias listas com um número diferente de itens em cada uma. Acredite ou não, você pode calcular a média ou somar os valores em qualquer intervalo selecionado com uma única fórmula.
Espero que a imagem acima explique o raciocínio por trás dos nomes dos intervalos 🙂 Aliás, a tabela Luas está longe de estar completa, existem 176 luas naturais conhecidas em nosso Sistema Solar, apenas Júpiter tem 63 atualmente. Para este exemplo, escolhi 11 aleatórios.
Supondo que, PlanetasD seja o seu intervalo 1 e LuasD seja o intervalo 2 e a célula B1 seja onde é colocado o número do intervalo, podemos usar a seguinte fórmula de ÍNDICE para calcular a média dos valores no intervalo selecionado:
= MÉDIA (ÍNDICE ((PlanetasD; LuasD);;; B1))
Preste muita atenção agora! estamos usando o formulário Referência da função ÍNDICE, e o número no último argumento (núm_área) indica a fórmula que o intervalo escolher.
Na captura de tela abaixo, núm_área (célula B1) é definido como 2; portanto, a fórmula calcula o diâmetro médio de Luas porque o intervalo LuasD fica em segundo lugar no argumento de referência.
Para quem trabalha com várias listas e não deseja se lembrar dos números associados, pode empregar uma função SE aninhada para fazer isso por você:
= MÉDIA (ÍNDICE ((PlanetasD; LuasD);;; SE (B1 = "planetas"; 1; SE (B1 = "luas"; 2))))
Na função SE, podemos usar alguns nomes de lista simples e fáceis de lembrar que deseja que os usuários digitem na célula B1 em vez de números. Lembre-se de que, para que a fórmula funcione corretamente, o texto em B1 deve ser exatamente o mesmo (sem distinção entre maiúsculas e minúsculas) dos parâmetros do SE; caso contrário, a fórmula de ÍNDICE gerará o erro #VALOR.
Para tornar a fórmula ainda mais amigável, é possível usar uma Validação de Dados do Excel para criar uma lista suspensa com nomes predefinidos para evitar erros de ortografia e erros de impressão:
Por fim, para tornar sua fórmula ÍNDICE absolutamente perfeita, devemos incluí-la na função SEERRO que solicitará ao usuário que escolha um item da lista suspensa se nenhuma seleção ainda tiver sido feita:
= SEERRO (MÉDIA (ÍNDICE ((PlanetasD; LuasD);;; IF (B1 = “planetas”; 1; IF (B1 = “luas”; 2))))); “Por favor, selecione a lista!”)
É assim que a gente trabalha com as fórmulas ÍNDICE no Excel. Espero que esses exemplos tenham mostrado uma maneira de aproveitar as vantagens da função ÍNDICE do Excel .
Obrigado pela leitura!
Conclusão
Neste artigo vimos como a função ÍNDICE é prática e pode ser trabalhada em conjunto com outras funções do Excel.
O site deste pessoal aqui foi referência para este artigo. O site é muitoooo bacana com várias dicas legais para Excel, mas infelizmente o conteúdo está em inglês, o que não deixa de ser uma boa referência para quem conseguir acompanhar. Eu recomendo.
Eu sei que não é comum ler um artigo deste, como tutorial, e repetir para aprender, mas recomendo fortemente que você tome coragem de repetir todo o passo-a-passo. Eu penso que praticar é a melhor maneira de aprender e, embora pareça simples, é preciso se acostumar a navegar entre as telas e fórmulas do Excel para que no futuro você possa se tornar um usuário avançado.
Portanto, continue lendo mais sobre Excel avançado aqui.
Parabéns pela matéria. Muito didática.
Estou tentando utilizar a função ÍNDICE em conjunto com a função CÉL(“CONTEÚDO”, ref).
É possível essa junção?
Exemplo:
ÍNDICE(CÉL(“CONTEÚDO”, C1):P6;……….
Desde já agradeço a atenção.