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