AULA 4
EXCEL
– FUNÇÃO ÍNDICE E CORRESP –
A função ÍNDICE em conjunto com o CORRESP dispensam o uso do PROCV, pois o PROCV só
funcionaria da esquerda para a direita.
Logo, nesta aula iremos aprender um pouco sobre o ÍNDICE e sobre o CORRESP, e como
podemos utilizar os dois ao mesmo tempo.
..::ÍNDICE::...........................................................................................................................
A função ÍNDICE retorna um valor, de uma determinada matriz, através das referências dadas
de Linha e Coluna.
Exemplo:
Na planilha a seguir, se quisermos o retorno do valor do vendedor B em 2017, teremos que dar
uma coordenada para a função ÍNDICE:
No caso, a função funciona da seguinte forma:
ÍNDICE(matriz; núm_linha; núm_coluna)
Matriz: é o intervalo de células que a função irá procurar e, no caso, seria B2:D8;
Núm_Linha: é a posição da linha em relação a matriz. No caso seria 2, ou segunda linha do
intervalo;
Núm_coluna: é a posição da coluna em relação a matriz. No caso seria 3, ou seja, 3º coluna do
intervalo;
Então ficaria: =ÍNDICE(B2:D8;2;3)
Mas podemos fazer de outra forma para enxergamos melhor:
Vamos criar alguns campos a mais para ficar mais claro o uso da função ÍNDICE.
No campo F6, referente ao valor, vamos escrever a fórmula:
=ÍNDICE(B2:D8;G2;G3) onde, G2 seria referencia da linha que escolhermos e G3 a coluna que
também escolhermos.
Vamos fazer o teste: vamos colocar na referencia de linha o 2 e na referencia de coluna o 3:
Ao darmos as
referencias de linha
e coluna, a função
ÍNDICE retorna o
valor das
coordenadas.
Portanto, a Função ÍNDICE retorna valores de uma matriz, dado a referencia de linha e coluna.
..::CORRESP::...............................................................................
A função CORRESP procura um item em um intervalo de células e retorna a posição desse item
no intervalo.
Exemplo:
Caso o intervalo A1:A3 contenham os seguintes valores: 3, 10 e 18, poderíamos ter uma fórmula:
=CORRESP(10;A1:A3;0), retornará o número 2, pois o número 10 é o segundo item do intervalo.
Sendo assim, vamos entender a fórmula:
=CORRESP (valor_procurado;matriz_procurada;tipo_correspondência)
Valor procurado: Valor que procuramos;
Matriz procurada: intervalo de células onde queremos procurar o valor;
Tipo correspondência: 1 se o valor é maior que, 0 se o valor é exato, 2 se o valor é menor que.
Argumento opcional, para determinar se o Excel deve procurar o dado desejado com base
exatamente o que foi indicado em valor_procurado ou se deve procurar um valor maior ou
menor que o indicado.
Para entendermos de modo prático, vamos adicionar mais alguns campos a nossa planilha atual:
Nas células de Referência, G2 e G3, vamos usar a função CORRESP:
Nesses campos vamos
selecionar as opções para trazer
na referência.
Na fórmula temos: =CORRESP(J2;A3:A8;0), ou seja, “Retorne a posição do valor igual ao de J2”,
“no intervalo de A3 a A8”, “de forma exata (0)”;
Vamos fazer o teste. Se na célula J2 colocarmos o valor B, que se refere ao Vendedor B, ele terá
que retornar 2, ou seja, no intervalo de A3 a A8, o vendedor B é o segundo item do intervalo.
Vamos fazer o mesmo com a Referência Coluna:
- Usaremos a função CORRESP:
=CORRESP(J3;B2:D2;0)
Ou seja, procurar o valor igual a J3, no intervalo de B2 à D2, exatamente igual a J3, e retornar
sua posição no intervalo de B2 a D2;
Ao selecionarmos o Vendedor B,
a função retornou o valor “2”,
ou seja, o vendedor B se
encontra na segunda posição do
intervalo de A3 à A8.
Na célula J3, por exemplo, vamos selecionar o ano de 2016.
A função retornou o valor 2, ou seja, do intervalo de B2 à D2, o ano de 2016 (J3) é o segundo
item do intervalo.
Sendo assim, a função CORRESP retorna à posição do valor em relação a um intervalo.
..::ÍNDICE + CORRESP::................................................................
Podemos utilizar a mesma planilha para exemplificar o uso das duas funções ao mesmo tempo.
Vamos supor que, escolhendo a opção de Vendedor B e o ano de 2016, a planilha teria que
retornar o resultado de vendas dessas referências. O resultado que a planilha teria que retornar
seria o valor de R$ 3200,00.
Utilizando a função ÍNDICE e CORRESP, podemos trazer esse resultado de forma automática:
Vamos diferenciar as cores para entender quando é ÍNDICE e quando é CORRESP
FUNÇÃO ÍNDICE: cor vermelha
FUNÇÃO CORRESP: azul
A Função ÍNDICE vai trabalhar justamente com a indicação da posição de linha e coluna, ou seja,
no caso do exemplo, a função teria que trazer linha 2 e coluna 3 para trazer o R$ 3200,00 como
resultado:
=ÍNDICE(matriz; num_linha; num_coluna)
Porém, para descobrirmos o valor num_linha, a Funçao CORRESP se encarrega, pois ele irá
procurar o valor B, no intervalo entre A3 à A8, e dará a posição 2, pois está na segunda linha, ou
seja, item dois.
O valor de num_coluna seria a mesma coisa: a Função CORRESP se encarrega de buscar o valor
2016 no intervalo de A2 à D2; como resultado, trará a posição 3, pois é o segundo item entre A2
à D2;
A fórmula ficará da seguinte forma:
=ÍNDICE(
Matriz: A3:D8;
Num_linha: CORRESP(H2;A3:A8;0); (queremos trazer a posição que H2 se encontra no intervalo
de A3 à A8)
Num_coluna: CORRESP(H3;A2:D2;0)) (queremos trazer a posição que H3 se encontra no
intervalo de B2 à D2)
=ÍNDICE(A3:D8;CORRESP(H2;A3:A8;0);CORRESP(H3;A2:D2;0))
Agora, vamos aplicar na planilha de “Dados”. Vamos preencher as colunas dos meses com as
despesas lançadas na planilha que contém as macros.
Ou seja, vamos limpar os dados preenchidos da planilha, e através de fórmulas iremos preencher
a planilha com os botões de lançamento de despesa.
Na célula B3, vamos digitar a fórmula. Primeiro vamos entender o que queremos:
Vamos buscar o valor “água” dessa planilha de “Dados”, na planilha “Jan”. Achando o valor
“Água”, na planilha de “Jan”, então queremos que retorne o valor referente a ela, buscando o
Num_Linha Num_Coluna
valor no campo de lançamento de Despesa, ou seja, traga o valor correspondente a Água da
coluna “M”.
Começando:
=ÍNDICE(Jan! obs.: Quando colocamos por exemplo Jan! (nome da planilha e o sinal de
exclamação (!), queremos dizer que estamos buscando o valor na planilha “Jan”)
Então:
=ÍNDICE(
Matriz: Jan!L:M; (na planilha Jan, vamos considerar todo o intervalo de células que compreende
entre as colunas L à M.
Num_Linha: CORRESP(Dados!A3;Jan!$L:$L;0); (ao procurar o valor A3 da planilha “Dados” na
planilha “Jan”, no intervalo de L:L, ou seja, na coluna L inteira)
Num_coluna: CORRESP(Jan!$M$3;Jan!$L$3:$M$3;0)) (ao procurar M3 da planilha de “Jan” na
planilha “Jan”, no intervalo de L3 à M3, sendo exatamente o mesmo valor, retornar sua posição;)
Obs.: $L$3 à $M$3 ou $L:$L - Travamos as células ($) para que possamos arrastar a fórmula
para as demais células da planilha, sendo que não alteramos o intervalo;
Vamos supor que já tivéssemos lançado os valores de despesa na planilha “Jan”:
=ÍNDICE(Jan!$L:$M;CORRESP('Dados'!A3;Jan!$L:$L;0);CORRESP(Jan!$M$3;Jan!$L$3:$M$3))
Observe que a ordem das
despesas foi lançada de modo
aleatório, não obedecendo a
mesma ordem da planilha
“Dados”.
De forma resumida, a fórmula sugere: Ao buscarmos o valor “água” da planilha de “Dados” e
encontrarmos o mesmo valor “água” na planilha “Jan”, queremos que traga a posição da linha
dela no intervalo de L1 à L50 (ou seja, posição 14); e queremos que traga a posição da coluna,
quando procurarmos o valor Jan, entre as colunas L3 à M3 (ou seja, posição 2); Finalizando,
pedimos que a função retorne o valor encontrado, nos intervalos de L:M, o valor que está na
linha 14, coluna 2. O valor correspondente a essas referências seria R$ 35,00.
Vamos arrastar a fórmula para as demais células;
Podemos fazer o mesmo para os demais meses, e depois criar as planilhas de lançamento dos
meses de Fev à Dez.
Também poderemos utilizar o ÍNDICE E CORRESP para lançar Receitas.
1 2
Vamos supor que venderemos 3 produtos, sendo Produto A, B e C. E vamos lançar na planilha
de lançamentos de receita e com a função preencher na planilha de “Dados”, os valores de
receita.
Para tanto, na planilha de “Dados”, vamos criar três linhas para Produto A, B e C:
Na célula B16, iremos escrever a fórmula e arrastaremos para as demais:
=ÍNDICE(Jan!$I:$J;CORRESP('Dados '!A16;Jan!$I:$I;0);CORRESP(Jan!$J$3;Jan!$I$3:$J$3;0))
E vamos lançar alguns valores de receita para os produtos A, B e C na planilha de lançamentos
de Jan.
Agora voltamos a planilha de “Dados” e verificamos que já forma preenchidos
automaticamente:
Para ficar mais completo, vamos alterar a fórmula da célula que representa o Resultado, a B20.
=SOMA(B16:B18)-B13, ou seja, a soma das receitas dos produtos A,B e C, subtraindo o total de
Despesas Fixas.
Dessa forma, já temos nosso Fluxo de Caixa completo. Apenas precisamos replicar as fórmulas
para as demais células em branco.
1) Na empresa que vocês pesquisando, monte ou aproveite suas planilhas para utilizar a
função ÍNDICE E CORRESP juntas.
EXERCÍCIOS