7 Formulas Do Excel Que Podem Salvar Sua Vida

(1)

(2)

(3)

c c e e l l d d o o s s e e u u j j e e i i t t o o . . c c o o m m . . b b r r

7 Fórmulas

do Excel que

Podem

Salvar sua

Vida

(4)

c c o o m m . . b b r r

(5)

c c e e l l d d o o s s e e u u j j e e i i t t o o . . c c o o m m . . b b r r

Sumário

Capítulo 1 Capítulo 1 ...5...5 Uso Uso da da função função SE ...SE ...6...6

Capítulo 2 Capítulo 2 ... ... 1313 Uso da função SOMASE... 14

Uso da função SOMASE... 14

Entendendo a função SOMA ... 14

Soma Condicional Soma Condicional – –SOMSOMASE ASE ... 16... 16

Capítulo 3 Capítulo 3 ... ... 2020 Uso da função SOMASES ... 21

Uso da função SOMASES ... 21

Capítulo 4 Capítulo 4 ... ... 2525 SOMA e SE (Combinadas SOMA e SE (Combinadas – – Fórmula Matricial) ... 26 Fórmula Matricial) ... 26

Capítulo 5 Capítulo 5 ... ... 3232 Uso da função PROCV ... 33

Uso da função PROCV ... 33

Capítulo 6 Capítulo 6 ... ... 4040 Uso das funções ÍNDICE e CORRESP ... 41

Uso das funções ÍNDICE e CORRESP ... 41

Entendendo a função CORRESP... 41

Entendendo a função ÍNDICE ... 43

Unindo as d Unindo as duas funçuas funções ÍNDIões ÍNDICE e CORRESP CE e CORRESP ... .. 4444 Capítulo 7 Capítulo 7 ... ... 4646 Uso da função DESLOC ... 47

Uso da função DESLOC ... 47 Anexo I

(6)

c c o o m m . . b b r r

Capítulo 1

(7)

c e l d o s e u j e i t o . c o m . b r

1ª.Parte 2ª.Parte 3ª.Parte

Muitas vezes precisamos obter resultados que atendam determinados critérios e abstraia os demais que não sejam necessários. Em diversas situações esses critérios são múltiplos, ou seja, um conjunto de condições agrupadas para retornar apenas um resultado único com base em todos os parâmetros solicitados. Veja alguns exemplos:

1) Critério condicional simples (Apenas uma comparação):

a) Retornar “REPROVADO” se a nota for MENOR do que 7, senão retorne “APROVADO”;

b) Aplicar desconto de 10% se produto custar mais que R$1.000,00;

c) Adicionar cobrança de Frete ao valor da venda caso o local de entrega não seja no RJ;

2) Vários critérios condicionais

a) Retornar “REPROVADO” se a nota for MENOR do que 7, senão retorne “APROVADO”

caso não possua o número de faltas maior que 5;

b) Aplicar desconto de 15% se produto custar mais que R$2.000,00, 10% de desconto se custar mais de R$1.000,00, 5% mais de R$500,00;

c) Adicionar cobrança de Frete ao valor da venda caso o local de entrega não seja no RJ e o valor da venda seja menor que R$1.000,00;

Como elaborar fórmulas para exibir os resultados dessas sentenças?

Primeiramente, vamos entender como uma fórmula manipula uma expressão condicional. Para nosso exemplo inicial, o uso da fórmula SE irá nos atender com precisão. A fórmula SE trabalha assim:

SE(Condição; Caso a condição seja verdadeira, execute isto aqui!; Caso contrário, execute o que está aqui!)

1ª. Parte: Onde é descrita a condição a ser analisada. Nesta parte, será retornado um valor booleano, verdadeiro ou falso, para que a fórmula o interprete e escolha o que fazer. Se a expressão condicional for verdadeira, será executada a 2ª parte, caso seja falsa, a 3ª parte será processada.

2ª. Parte: Expressão que será executada se a condição interpretada na 1ª parte for verdadeira.

(8)

c o m . b r

Se a co ndição for VERDADEIRA Fórmula e scrita na célula B2

A. Retornar “REPROVADO” se a nota for MENOR do que 7, senão retorne “APROVADO”;

Separando a expressão acima em partes:

Se nota for menor que 7, o aluno está Reprovado, caso contrário, ele está Aprovado .

A condição seria:Se nota for menor que 7, ou seja, nota < 7

Transformando tudo isto em fórmula, e supondo que a Nota esteja na célula A2, teremos:

(9)

c e l d o s e u j e i t o . c o m . b r 1ª.Parte 2ª.Parte 3ª.Parte

B. Aplicar desconto de 10% se produto custar mais que R$1.000,00 Separando a expressão acima em partes:

Se o produto custar mais que R$ 1.000,00, será aplicado um desconto de 10% sobre o valor desse produto, caso contrário, não faz nada, ou seja, retorna o próprio valor.

A condição seria: Se o valor do produto for maior do que R$ 1.000,00, ou seja, produto < 1000.

Transformando tudo isto em fórmula, e supondo que o Valor do Produto esteja na célula A2, teremos:

(10)

c o m . b r

C. Adicionar cobrança de Frete ao valor da venda caso o local de entrega não seja no RJ

Se o local de entrega for no RJ, não deverá cobrar frete, caso contrário, adicionar valor do frete ao total da venda.(No nosso exemplo, o valor do frete é fixo de R$ 30,00)

A condição seria:Se o local de entrega for no RJ, ou seja, uf da entrega= “RJ” .

Transformando tudo isto em fórmula, e supondo que a UF do Local de Entrega esteja na célula B2 e o Valor da Venda na célula A2, teremos:

(11)

D. Retornar “REPROVADO” se a nota for MENOR do que 7, senão retorne “APROVADO” caso não possua o número de faltas maior que 5

Se a nota do aluno for menor do que 7, ele estará REPROVADO, caso contrário, estará APROVADO mas somente se não possuir mais de 5 faltas.

Perceba que na 3ª parte ainda temos uma condição que precisa ser verificada: o total de faltas do aluno. Pois, somente se este número for menor ou igual a 5 ele poderá ser APROVADO, senão, ficará REPROVADO.

Então, ainda podemos subdividir a 3ª parte em mais uma expressão de avaliação condicional. Veja:

Se o número de faltas do aluno for maior que 5 , ele estará REPROVADO, caso contrário, estará APROVADO.

Transformando tudo isto em fórmula, teremos:

(12)

c o m . b r 1ª.Parte 2ª.Parte 3ª.Parte

E. Aplicar desconto de 15% se o produto custar mais que R$2.000,00; 10% de desconto se custar mais de R$1.000,00; e, 5% mais de R$500,00.

Já de partida, percebemos que há mais de um critério de avaliação nesta expressão. Podemos contar 3 avaliações.

Se o produto custar mais que R$ 2.000,00, será aplicado um desconto de 15% sobre o valor desse produto, caso contrário, se o produto custar mais que R$ 1.000,00, aplique um desconto de 10%, mas se custar apenas mais que R$ 500,00 desconte 5%. Menos que isso, inclusive, não dê desconto algum.

Neste caso, a 3ª parte ainda pode ser subdividida em mais 2 expressões de avaliação. Para descobrir se o valor do produto é maior do que R$ 1.000,00, ou se é apenas maior do R$ 500,00, e ainda, por último, se menor ou igual a R$ 500,00.

Transformando tudo isto em fórmula, e supondo que o Valor do Produto esteja na célula A2, teremos:

(13)

F. Adicionar cobrança de Frete ao valor da venda caso o local de entrega não seja no RJ e o valor da venda seja menor que R$1.000,00

Se o local de entrega for no RJ, não deverá cobrar frete, caso contrário, adicionar valor do frete ao total da venda, mas apenas se o valor da venda for inferior a R$ 1.000,00.

Vemos que na 3ª parte ainda temos uma condição que precisa ser verificada: o valor da venda que deverá ser menor do que R$ 1.000,00 para que seja cobrada a tarifa de frete.

Então, ainda podemos subdividir a 3ª parte em mais uma expressão de avaliação condicional. Veja:

Se o valor da venda for menor do que R$ 1.000,00, cobrar frete, caso contrário, não cobrar frete.

Transformando tudo isto em fórmula, teremos: =SE(B2="RJ";A2;SE(A2<1000;A2+30;A2))

(14)

c o m . b r

Capítulo 2

(15)

Antes de apresentar a função SOMASE, vou expl icar o funcionamento da função SOMA para que você entenda melhor as atribuições da SOMASE.

Entendendo a função SOMA

A SOMA é outra função extremamente utilizada em nossas planilhas no dia a dia. Pelo seu nome já podemos entender sua aplicação, muito simples inclusive. Sua tarefa é somar todos os valores passados como parâmetros à ela.

Como ela trabalha? Vejamos algumas situações em que precisamos usá-la. a) Somar os valores de um intervalo de vendas especificado;

b) Somar o total de alunos de uma escola; c) Somar faturamento anual da empresa;

Qualquer soma pode ser efetuada com esta função, desde que, sejam passadas todas as parcelas da operação. O uso dela seria similar, por exemplo, a somar entre as células A1 até a A10, que ficaria assim:

=A1+ A2+ A3+ A4+ A5+ A6+ A7+ A8+ A91+ A10

Agora, imagine fazer isso do intervalo entre A1 até A65000!!!

Na SOMA podemos definir este intervalo de maneira simples, eficaz e limpa. Veja como ficaria:

=SOMA(A1:A65000)

Bem mais funcional.

(16)

c o m . b r A. Somar os valores de um intervalo de vendas especificado;

(17)

C. Somar faturamento anual da empresa;

Soma Condicional

_–

SOMASE

A soma condicional surge da necessidade de restringir valores que deverão ser incluídos às parcelas da operação de adição que queremos realizar. No capítulo anterior falamos sobre essa necessidade de maneira mais simples, utilizando a função SE, que é capaz de interpretar expressões de decisão, e retornar valores que atendam a estes critérios. Com base neste filtro podemos programar nossas operações.

No caso do SOMASE, juntamos as funcionalidades das funções SOMA e SE. Como? A função SE será responsável por filtrar nossos critérios e a SOMA por efetuar a adição apenas dos valores retornados pela consulta. Com isso, podemos restringir em determinado intervalos de dados apenas os valores que precisamos somar, dinamicamente.

(18)

c o m . b r

Alguns casos, nos quais precisaremos usar SOMASE: a) Somar o total de vendas de cada vendedor;

b) Somar apenas as vendas maiores que R$ 1000,00.

Nos casos acima vemos que a soma a ser realizada será restringida por determinada condição. Somar apenas as vendas maiores que R$ 100,00 ou somar apenas as ve ndas de determinado vendedor. Estas restrições são interpretadas pela função SOMASE e calculadas levando-se em conta essa avaliação.

Primeiramente, vamos entender como uma fórmula manipula uma expressão condicional. Para nosso exemplo inicial, o uso da fórmula SE irá nos atender com precisão. A fórmula SE trabalha assim:

SOMASE(Intervalo a ser consultado; Condição; [Intervalo que será somado])

1ª. Parte: Onde é informado o intervalo de células a ser analisado.

2ª. Parte: Onde é descrita a condição a ser analisada. Nesta parte, será retornado um valor booleano, verdadeiro ou falso, para que a fórmula o interprete e escolha o que fazer. Se a expressão condicional for verdadeira, a referência a célula correspondente será incluída na soma.

3ª. Parte: O Intervalo adjacente que será somado. Esta parte é opcional, caso não seja informada, o intervalo a ser somado será o mesmo descrito na 1ª parte.

(19)

A. Somar o total de vendas de cada vendedor

=SOMASE($A$2:$A$18;D4;$B$2:$B$18)

Nas colunas A e B está contida a tabela com todos os dados. Nas colunas D e E foi projetada uma tabela de resumo para o somatório de vendas de cada vendedor.

(20)

c o m . b r B. Somar apenas as vendas maiores que R$ 1000,00

=SOMASE($B$2:$B$18;">"&D4)

Aqui apenas foram somadas as células que possuíam valores acima dos descritos na coluna D. Veja que não há nenhum valor maior que R$ 2.000, ou seja, o resultado da soma foi R$ 0,00.

Apesar de esta função solucionar diversas situações, você pode ter percebido que o SOMASE atende apenas a uma restrição no critério, ou seja, se for necessário efetuar mais de um tipo de restrição, teremos que recorrer a outras soluções. Por exemplo, caso queira somar as vendas acima de R$ 1.000,00 de cada vendedor. São duas comparações: valor de venda >

(21)

Capítulo 3

(22)

c o m . b r

1ª.Parte 2ª.Parte 3ª.Parte 4ª.Parte

Nos capítulos anteriores estudamos as aplicações das funções SOMA, SE e SOMASE. O emprego destas funções produz incontáveis possibilidades de resultados para nossas planilhas, mesmo assim, ainda podemos querer mais.

Para alcançarmos resultados mais detalhados e específicos, precisamos, muitas vezes, efetuar uma infinidade de comparações e restrições em nossas tabelas de dados. Por exemplo, se quisermos obter a soma das vendas maiores que R$ 1.000,00 apenas do vendedor X. Ou, somar as vendas de determinado produto realizadas por um vendedor específico.

Como vimos anteriormente, a SOMASE só aceita um critério de avaliação, portanto, não resolveria nestas novas situações mencionadas. A alternativa nestes casos seria o uso da função SOMASES, que seria como um agrupamento de funções SOMASE. Contudo, esta função só foi adicionada nas versões a partir do Excel 2007. No próximo capítulo, aprenderemos a formular uma solução que tenha compatibilidade e suporte para as versões anteriores do Excel.

Como funciona o SOMASES? Vejamos.

SOMASES(Intervalo que será s omado; Interval o a ser consultado 1; Condição1; [Intervalo a ser consulta do 1]; [Condição1])

1ª. Parte: Onde é informado o intervalo de células que será somado.

2ª. Parte: Nesta parte é informado o intervalo de células que será analisado pelo critério de restrição. Este é o intervalo 1, que será comparado pela condição 1.

3ª. Parte: Onde é descrita a condição a ser analisada. Nesta parte, será retornado um valor booleano, verdadeiro ou falso, para que a fórmula o interprete e escolha o que fazer. Se a expressão condicional for verdadeira, a referência a célula correspondente será incluída na soma.

(23)

Sugerimos a tabela de dados abaixo:

A. Somar as vendas acima de R$ 1000 para cada vendedor.

Neste exemplo, é criada uma lista de vendedores na vertical da tabela. Já nas colunas, criamos rótulos para os valores de venda que queremos comparar, maior que 1000 (> 1000),

(24)

c o m . b r B. Somar as vendas de cada produto para cada vendedor.

Neste exemplo, mostramos quanto cada vendedor obteve em vendas para determinado produto. Primeiro, a fórmula, analisa as vendas de cada vendedor. Em seguida, verifica as vendas de cada produto que atendam ao primeiro critério.

C. Somar as vendas maiores que determinado valor, para cada produto e para cada vendedor.

(25)

No capítulo seguinte, abordaremos como criar fórmulas semelhantes às que utilizamos com SOMASES obtendo maior flexibilidade e aplicáveis a todas as versões do Excel.

(26)

c o m . b r

Capítulo 4

(27)

–

Neste capítulo, falaremos sobre como criar sua própria função para calcular intervalos tendo como base fórmulas comuns que tratarão seus intervalos como matrizes de valores, ou seja, para cada item do intervalo de células informado, serão aplicadas as funções que você determinar. Isto é possível graças ao recurso do Excel para manipulação de fórmulas matriciais. Esse objetivo é alcançado quando, após digitar a fórmula desejada, concluir a digitação comCTRL+Shift+ENTER ao invés de simplesmente teclar ENTER.

Existem situações em que precisamos restringir algumas informações com base em intervalos grandes para retorná-los em uma única célula, sem utilizar-se de recursos de células ou intervalos de apoio. Bem, nos capítulos anteriores vimos diversas maneiras que podemos obter estes resultados, contudo, em alguns casos, ficamos um pouco limi tados pela estrutura interna da função e, para efeito deste capítulo, dependemos da compatibilidade das versões mais atuais do Excel que suporta algumas novas funções.

Neste capítulo, vamos aprender a construir uma fórmula que simula a SOMASE ou SOMASES já presente nas versões mais atuais do Excel. Com isso. Teremos mais flexibilidade de restringir nossos dados e teremos uma planilha que será bem interpretada pelas versões mais antigas como o Excel 2003, que ainda existe em muitas máquinas.

Veja a conversão de alguns casos descritos nos capítulos anteriores onde usamos SOMASE/SOMASES:

a) Somar o total de vendas de cada vendedor; Com SOMASE:

=SOMASE($A$2:$A$18;D4;$B$2:$B$18)

Com SOMA e SE (Matricial):

(28)

c o m . b r =SOMASE($B$2:$B$18;">"&D4)

={SOMA(SE($B$2:$B$18 > D4;$B$2:$B$18;0))}

c) Somar as vendas de cada produto para cada vendedor; Com SOMASE:

= SOMASES($C$2:$C$18;$A$2:$A$18;$E5;$B$2:$B$18;F$4)

={SOMA(SE($A$2:$A$18=$E5;SE($B$2:$B$18=F$4;$C$2:$C$18;0);0))}

Esta fórmula é baseada no conceito de matrizes, ou seja, ela faz uma correlação entre as linhas e colunas dos intervalos especificados. Observe a estrutura empregada na construção da fórmula:

= { SOMA( SE( $A$2:$A$18=D4; $B$2:$B$18; 0 ) ) }

(29)

Explicando mais um pouco: Se, por exemplo, a célula A2 atender ao critério de comparação, o SE retornará o valor de B2. Se A3 atender ao critério, retornará B3, e assim por diante.

Conforme já citei, para validar uma fórmula deste tipo, matricial, ao finalizar sua escrita NÃO se pode teclar ENTER, mas, uma combinação de 3 teclas. São elas: Ctrl + Shift + Enter. Após pressionar estas teclas, simultaneamente, você perceberá que o Excel adicionou

os símbolos “{“ e “}”, no início e final da fórmula, respectivamente. Isso significa que esta é

uma função de matrizes.

(30)

c o m . b r A. Exibir a quantidade de cada produto vendido por mês em cada loja da rede;

Supondo que tenhamos uma tabela de dados na planilhaBaseVendaPorDia, como a

exibida na figura abaixo, poderemos criar a seguinte fórmula para atender esta expressão.

={SOMA(SE(MÊS(BaseVendaPorDia!$C$2:$C$649)=MÊS(C$9);SE(BaseVendaPorDia!$A$2:$A$ 649=$A10;SE(BaseVendaPorDia!$B$2:$B$649=$B10;BaseVendaPorDia!$D$2:$D$649;0);0);0)

(31)

B. Exibir o total de produtos vendidos por mês em cada loja;

(32)

c o m . b r C. Exibir, para cada produto, a quantidade de itens vendidos por mês.

(33)

Capítulo 5

(34)

c o m . b r Esta é, sem dúvida, a função mais utilizada nas fórmulas da maioria das planilhas que

usamos em nosso dia a dia. A funçãoPROCV é muito eficaz quando precisamos fazer um busca de informações em alguma tabela na qual não sabemos em que linha iremos obter o valor desejado, o que temos apenas é um dado identificador para procurar nesta tabela e retornar o valor adjacente a linha onde for encontrado. Por exemplo, tenho uma lista contendo o boletim dos alunos de determinado curso. Nesta lista temos na primeira coluna, o nome de cada aluno, na segunda coluna temos as notas do 1º bimestre, na coluna seguinte, as notas do 2º bimestre, e, nas próximas colunas, as notas dos 3º e 4º bimestres, respectivamente. Se quisermos saber a nota que José da Silva obteve no 1º bimestre, sabemos que, essa informação deverá ser procurada na segunda coluna da tabela, mas não sabemos em qual linha estará escrita as notas deste aluno. Nesse momento é que recorreremos ao PROCV .

PROCV pesquisará o valor informado na 1ª coluna do intervalo fornecido e retornará o valor adjacente na coluna da posição informada dentro do mesmo intervalo. No caso do exemplo acima, a função irá procurar José da Silva na 1ª coluna (B), e retornará o valor correspondente, ou seja, na mesma linha da coluna (C). Veja pela figura a seguir.

(35)

1ª.Parte 2ª.Parte 3ª.Parte 4ª.Parte

Deixe-me explicar como funciona a função PROCV. Sua estrutura básica é composta da seguinte maneira:

PROCV(valor_procurado; matriz_tabela; núm_índice_coluna; procurar_intervalo)

Onde:

valor_procurado: Significa o termo que vamos procurar na tabela de dados. Essa informação será pesquisada sempre na primeira coluna do intervalo de dados passados no segundo parâmetro.

matriz_tabela: É o intervalo de células que corresponde a tabela de dados de origem. Ou seja, nesta tabela deverá conter o termo pesquisado e os valores que serão retornados.

núm_índice_coluna: Deve ser informado um valor numérico que represente, dentre do intervalo informado, a posição da coluna que contém os dados que serão retornados.

procurar_intervalo: Se quiser que a procura retorne valor apenas se a correspondência for exata, informe 0 (FALSO). Se quiser uma correspondência aproximada, digite 1 (VERDADEIRO).

Então, neste primeiro exemplo, para conseguir as notas bimestrais de um aluno específico, utilizei a construção das fórmulas assim:

(36)

c o m . b r Para as notas do 2º Bim:

=PROCV(I7;B7:F18;3;FALSO)

Para as notas do 3º Bim:

Para as notas do 4º Bim:

Perceba que a 3ª parte de cada fórmula (itens em vermelho) foram aumentados a cada bimestre. O que significa? Cada bimestre foi informado na tabela base em uma coluna diferente, e esta parte da informação corresponde as posições de tais colunas no intervalo. Você deve ter notado também que o nome do aluno foi informado da célula I7, portanto, quando o nome do aluno é trocado naquela célula, a fórmula é automaticamente recalculada com base no novo nome de aluno.

A função PROCV é bem simples de ser usado e muito funcional. Ela tem sua variação, a função PROCH, que faz pesquisas pela horizontal nos intervalos de dados. No arquivo de exemplos, anexo deste livro, há um exemplo de sua utilização.

Vamos mostrar mais alguns exemplos de uso do PROCV. Para isso, aproveitaremos a base de dados na planilha BaseVendaPorDia, já utilizada nos exemplos anteriores para construirmos nossas fórmulas.

(37)

A. Exibir Totais de Produtos Vendidos Por Mês.

Neste exemplo, usamos uma combinação de Soma condicional e PROCV para alcançarmos o resultado da soma de todas as vendas de cada produto em determinado mês. A soma condicional, como vimos nos capítulos anteriores, consegue nos retornar, pela tabela base que temos, os totais de itens vendidos em cada mês. Basta agora conseguirmos saber quanto custa cada produto para, então, multiplicarmos por esse total vendido. Para isso, usaremos PROCV.

Relembre a construção da fórmula para saber o total de itens vendidos no mês: SOMA(SE(MÊS(BaseVendaPorDia!$C$2:$C$649)=MÊS(C$27);SE(BaseVendaPorDia!$B $2:$B$649=$B28;BaseVendaPorDia!$D$2:$D$649;0);0))

Veja, agora, a construção da fórmula PROCV para identificarmos o preço do produto. PROCV($B28; TabelaPreços!$B$3:$C$20; 2; FALSO)

Agora é só multiplicar pelo total de itens vendidos.

SOMA(SE(MÊS(BaseVendaPorDia!$C$2:$C$649)=MÊS(C$27);SE(BaseVendaPorDia!$B$2:$B$64 9=$B28;BaseVendaPorDia!$D$2:$D$649;0);0)) * PROCV($B28; TabelaPreços!$B$3:$C$20; 2; FALSO)

(38)

c o m . b r = SOMA(SE(MÊS(BaseVendaPorDia!$C$2:$C$649)=MÊS(C$27);SE(BaseVendaPorDia!$B$2:$B$6 49=$B28;BaseVendaPorDia!$D$2:$D$649;0);0)) * PROCV($B28;TabelaPreços!$B$3:$C$20;2;FALSO)

B. Exibir Percentual de Produtos Vendidos Por Mês.

Para este exemplo, vamos utilizar como intervalo base a tabela gerada no exemplo anterior. Partindo dela, precisamos obter os totais mensais e anuais de vendas de cada produto para conseguirmos calcular os percentuais mensais. Utilizaremos o PROCV para retornar estes valores.

Veja como ficaria a fórmula para a coluna do mês de Janeiro:

(39)

A fórmula da colunaTotal por Produto ficou assim:

=PROCV ( $B54; $B$28:$O$45; 14; 0 )

Depois de preencher a fórmula de todos os meses desta primeira linha, basta arrastá-la para as demais linhas.

C. Exibir Valor e Percentual Vendidos Por Mês. (Um pouco de PROCH)

Neste exemplo veremos um pouco da utilização do PROCH, muito semelhante ao PROCV, diferencia-se apenas pelo sentido da procura que será na horizontal do intervalo, ao contrário da busca vertical que ocorre na PROCV. Vejamos.

(40)

c o m . b r O intervalo base especificado possui 20 linhas, e na última linha encontra-se o valor

que precisamos: os totais de vendas em cada mês. Portanto, a linha referência que desejamos retornar será a 20.

Desta vez a busca será feita pelas colunas (meses) e retornará um valor adjacente a coluna encontrada na linha que definimos na fórmula.

No próximo capítulo, aprenderemos como retornar valores a partir de outras tabelas quando não sabemos a posição de referência dentro dos intervalos de origem.

(41)

Capítulo 6

(42)

c o m . b r ª ª ª

Nos capítulos anteriores criamos fórmulas de procura em intervalos onde tínhamos conhecimento de onde estariam os dados que deveriam ser retornados. Em várias situações conseguimos fazer desta maneira, contudo, há casos onde não poderemos prever a posição absoluta onde estas informações estarão alocadas. Neste capítulo veremos como identificar essas referências e aplica-las em nossas fórmulas.

Entendendo a função CORRESP

A função CORRESP procura um item especificado em um intervalo de células e retorna a posição relativa desse item no intervalo. Sua estrutura é:

CORRESP(valor_procurado, matriz_procurada, [tipo_correspondência] )

1ª. Parte: Onde é informado o valor que procuramos.

2ª. Parte: Onde é informado o intervalo onde será procurado o valor especificado na 1ª parte.

3ª. Parte: Opcional. O número -1, 0 ou 1. O argumento tipo_correspondência especifica como o Excel corresponde valor_procurado com os valores em matriz_procurada. Assim:

Se for -1, CORRESP localiza o menor valor que é maior do que ou igual a valor_procurado.

Se for 0, CORRESP localiza o primeiro valor que é exatamente igual a valor_procurado.

(43)

Use CORRESP junto da função PROCV quando precisar da posição de um item em um intervalo. Por exemplo, você pode usar a função CORRESP para fornecer um valor para o argumento número_índice_coluna da função PROCV. Veja no exemplo a seguir, onde utilizaremos a base da tabela criada no capítulo anterior.

Anteriormente, para buscarmos o valor da venda de um produto no mês de Janeiro, fizemos:

=PROCV($B54;$B$28:$N$45;2;0)

Nós utilizamos a referência à coluna 2, pois, sabíamos que nesta coluna teríamos do valores de mês de Janeiro. Veja como ficaria a fórmula para se adaptar quando quisermos, dinamicamente, informar o mês, portanto, a referência para a coluna de retorno na fórmula

não poderá ficar “engessada”.

=PROCV($B54; $B$28:$N$45; CORRESP(C$53; $B$27:$N$27; 0); 0) Onde:

C$53 => É onde, na tabela destino, está o mês que desejamos procurar. Este valor está no cabeçalho da tabela. Veja que travamos apenas a linha, para que, a fórmula possa ser arrastada para as demais células e permaneça a referência a primeira linha da tabela e mova apenas as colunas, por causa dos meses.

$B$27:$N$27 => É o intervalo da tabela base onde será procurado o mês e ret ornada sua posição relativa no intervalo. Perceba que, a referência deste intervalo deve ser semelhante ao intervalo utilizado no PROCV, pois, a posição relativa será, na verdade, util izada pela fórmula PROCV. Ou seja, se na PROCV, o intervalo é entre B:N, na CORRESP também deverá ser entre B:N. O mesmo ocorrerá quando uma referência for na vertical. Se um intervalo for entre as linhas 10:50, em CORRESP seguirá o mesmo limite.

(44)

c o m . b r

Entendendo a função ÍNDICE

A função ÍNDICE retorna um valor ou a referência a um valor de dentro de uma tabela ou intervalo. Sua estrutura é:

ÍNDICE(matriz, núm_linha, [núm_coluna])

1ª. Parte: Onde é informado o intervalo, nossa tabela contendo todos os dados. 2ª. Parte: Seleciona a linha na matriz a partir da qual um valor deverá ser retornado. 3ª. Parte: Seleciona a coluna na matriz a partir da qual um valor deverá ser retornado.

Vejamos um exemplo do uso do ÍNDICE. Temos a seguinte tabela:

Aplicando a fórmula ÍNDICE, teremos algumas possibilidades.

=ÍNDICE(A1:C3;2;3) //Retornará: Verde claro

(45)

Unindo as duas funções ÍNDICE e CORRESP

Agora chegou o momento de juntar as funcionalidades destas duas poderosas funções do Excel. Uma grande vantagem do uso destas funções agrupadas é que, diferentemente, das funções PROCV e PROCH, com elas poderemos passear pelo intervalo base da maneira que desejarmos ou fazer buscas em qualquer linha ou coluna retornando valores de qualquer posição também.

Semelhante a maneira que explicamos da integração de CORRESP com PROCV, vejamos como construir uma fórmula utilizando CORRESP e ÍNDICE.

No caso do ÍNDICE, substituiremos as referências a núm_linha e núm_coluna, por resultados oriundos da CORRESP que indica a posição relativa de qualquer coluna ou linha correspondente ao termo pesquisado. Observe o caso a seguir.

A. Exibir Percentual de Produtos Vendidos.

No capítulo anterior mostramos uma tabela semelhante que mostrava o percentual de cada produto vendido em cada mês. Naquele exemplo, buscamos informações de todos os meses e informamos manualmente a posição relativa das colunas que continham os valores para cada mês. Se qualquer mês fosse alterado ou a posição destes meses fosse trocada na base de dados, já era nosso trabalho. Com o uso do CORRESP isto não aconteceria, pois, sua referência é obtida dinamicamente.

Vejamos um exemplo de como isto pode ocorrer. Imagine que precisamos mostrar os percentuais de vendas apenas para o primeiro mês de cada trimestre (janeiro, abril, julho, outubro). A tabela base está na planilha do Capítulo anterior (‘Capítulo 5’ ).

(46)

c o m . b r A fórmula seria:

=ÍNDICE(‘Capítulo 5’!$B$27:$N$45;CORRESP($B10;’Capítulo 5’!$B$27:$B$45;0);CORRESP(C$9;’Capítulo 5’!$B$27:$N$27;0)) / $G10

Sendo que:

CORRESP(‘Capítulo 5’!$B10;$B$27:$B$45;0) => Pesquisaria nas linhas da coluna B do intervalo base, de qual linha deveria retornar os dados. Esta pesquisa seria feita com base no nome do produto. Ou seja, na linha onde ele fosse encontrado no intervalo, seria a referência retornada para a fórmula ÍNDICE.

CORRESP(‘Capítulo 5’!C$9;$B$27:$N$27;0) => Pesquisaria nas colunas da primeira linha relativa no intervalo base (linha 27), de qual coluna deveria retornar os dados. Esta pesquisa seria feita com base no nome do mês. Ou seja, na coluna onde ele fosse encontrado no intervalo, seria a referência retornada para a fórmula ÍNDICE.

(47)

Capítulo 7

(48)

c o m . b r 1ª.Parte 2ª.Parte ª _ª _ª

Até o capítulo anterior vimos maneiras de definir referências a linhas e células dinamicamente em nossas fórmulas sempre com base em um intervalo informado manualmente. Agora vamos aprender a automatizar, também, esta referência a intervalos de base.

A função DESLOC retorna uma referência para um intervalo, que é um número especificado de linhas e colunas de uma célula ou intervalo de células. A referência retornada pode ser uma única célula ou um intervalo de células. Você pode especificar o número de linhas e de colunas a serem retornadas. Na verdade, DESLOC não desloca quaisquer células nem modifica a seleção, apenas fornece uma referência para elas.

DESLOC ( ref, lins, cols, [altura], [largura] )

1ª. Parte: A referência na qual você deseja basear o deslocamento. Ref deve ser uma referência a uma célula ou intervalo de células adjacentes.

2ª. Parte: O número de linhas, acima ou abaixo, a que se deseja que a célula superior esquerda se refira.

3ª. Parte: O número de colunas, à esquerda ou à direita, a que se deseja que a célula superior esquerda do resultado se refira.

4ª. Parte: A altura, em número de linhas, que se deseja para a referência fornecida. 5ª. Parte: A largura, em número de colunas, que se deseja para a referência fornecida.

(49)

Usando DESLOC, podemos:

 Obter a Receita de Loja Física em 2009 (115.255,00):

 =DESLOC(B3;0;0) //Partindo de B3, deslocamos 0 colunas e 0 linhas  Obter a Receita de Loja Física em 2010 (156.694,00):

 =DESLOC(B3;0;2) //Partindo de B3, deslocamos 2 colunas e 0 linhas  Obter a Receita de Loja Virtual em 2011 (96.179,00):

 =DESLOC(B3;1;4) //Partindo de B3, deslocamos 4 colunas e 1 linha  Obter a Despesa de Loja Física em 2010 (124.264,00):

 =DESLOC(B3;0;1) //Partindo de B3, deslocamos 1 coluna e 0 linhas

Podemos incrementar mais um pouco ainda. Imagine que eu queira somar o total de Receitas por ano. Poderia fazer assim:

 Obter a soma do Total de Receitas em 2009 (195.701,00):

 =SOMA(DESLOC(B3;0;0;2;1)) //Partindo de B3, deslocamos 0

colunas e 0 linhas com intervalo de 2 linhas e 1 coluna  Obter a soma do Total de Despesas em 2010 (258.084,00):

 = SOMA(DESLOC(B3;0;3;2;1)) //Partindo de B3, deslocamos 3

colunas e 0 linhas com intervalo de 2 linhas e 1 coluna

Desta forma, conseguimos compreender um pouco melhor o funcionamento do DESLOC. Isso é muito bom porque, assim, como pelos capítulos anteriores já havíamos aprendido como manipular a referência dinâmica das posições de linha e coluna em nossas fórmulas de procura, agora, com DESLOC, podemos informar, também, o intervalo matriz base

(50)

c o m . b r A. Exibir Vendas Por Trimestre.

Tendo uma base como esta da planilha do Capítulo 2.

Queremos somar as vendas de cada trimestre. A soma do 1º trimestre seria, então, o intervalo C27:E27, sendo que, para cada produto, a referência seria adjacente a linha do produto em questão. A ideia do uso do DESLOC neste caso é que, dado o intervalo inicial (C27:E27) este seja movido em 3 colunas a cada trimestre e 1 linha a cada produto. A altura e a largura, neste exemplo, continuarão as mesmas. Vejamos com ficaria:

(51)

c e l d o s e u j e i t o . c o m . b r No 2º Trimestre:

=SOMA(DESLOC(‘Capítulo 5’!$C$27:$E$27;CORRESP($B10;’Capítulo 5’!$B$28:$B$45;0);3))

No 3º Trimestre:

No 4º Trimestre:

Desta forma, seus intervalos, também ficarão dinâmicos, e sua planilha muito mais preparada para modificações.

(52)

c o m . b r