terça-feira, 21 de fevereiro de 2012

Excel - Montando resumos

Uma coisa que gosto muito de fazer, são os resumos feitos a partir de outra planilha de lançamento de dados, sejam lá quais forem.

A maior parte das pessoas utiliza o filtro do Excel. Mas eu não gosto e não uso quase nunca. Gosto eu mesmo de montar meus resumos e posso fazer melhor do que usando apenas a filtragem.

A única outra forma que eu conheço para montar resumos quase tão bons do que utilizando-se de fórmulas, são as tabelas dinâmicas, mas elas pecam em formatação. São terríveis de serem formatadas de uma forma que fiquem "legais".

Nessa postagem, vamos utilizar algumas funções que uso muito:

=PROC(), =PROCV() ou =PROCH() dependendo de como estão os dados.
=SOMASE()
=SOMASES()



Eu ia explicar todas as funções, mas acho que iria ficar muito extensa a postagem, fugindo da finalidade. Vamos explicar apenas as que eu vou usar de forma sucinta.

Vou começar com a  =PROCV().
A tradução da função para o português quer dizer "PROcura na Vertical"

A ideia da função é fazer uma busca por um determinado valor na primeira coluna de uma matriz e retornar o resultado na linha da coluna subsequente. Simples né? #not
Acho que é por isso que gosto de desenhar pra mostrar... vamos lá.


Agora o que quero fazer, é digitar um número em outra célula que seja um dentre estes na coluna A. E que a função retorne o correspondente da coluna B. Sim. A ideia é bem essa mesmo. Que os números da coluna A não estivessem em ordem. (a função PROC não funciona nessa situação)


=PROCV(C8;A1:B6;2;FALSO)

1) É o valor procurado. - C8
2) A matriz dos dados. - A1:B6
3) Qual a coluna está o valor a ser retornado. - 2 (segunda coluna da matriz)
4) Indica se deve achar o valor exato (FALSO) ou aproximado (VERDADEIRO)

A próxima função que vamos utilizar é a função =SOMASE(). Ela realiza a soma apenas dos números que atenderem UMA ÚNICA condição.
Retorna a soma da própria coluna testada ou de uma outra coluna com o mesmo intervalo do teste.

Vamos ao exemplo:


=SOMASE($F$1:$F$8;F11;$G$1:$G$8)

Note que o intervalo em linhas entre as duas colunas DEVE NECESSARIAMENTE ser o mesmo.
$F$1:$F$8 e $G$1:$G$8

O primeiro intervalo F1:F8, corresponde à coluna de teste e o segundo intervalo, corresponde à coluna dos dados a serem somados.

Observe que travei os dois intervalos com $ e deixei o critério F11 livre. Com isso pude simplesmente arrastar a fórmula para os outros itens (Porco e Marreco).

Uma evolução do SOMASE(), é o SOMASES() que faz o mesmo mas utilizando-se de vários critérios.

Esta função está presente apenas nas versões 2007 em diante. Não sei dizer se existe o SOMASE() em versões anteriores.

O SOMASES() trabalha da mesma forma, mas muda a posição dos argumentos dentro da função. Agora, o primeiro intervalo é o intervalo a ser somado. Os argumentos seguintes são em pares de intervalo e critério correspondente.

Vamos demonstrar incrementando a tabela anterior.



=SOMASES($H$1:$H$8;$F$1:$F$8;F16;$G$1:$G$8;G16)

O intervalo de soma é o destacado em vermelho. Os intervalos de teste e seus critérios de teste correspondentes estão em azul.
Note que é um teste do tipo E, ou seja, soma apenas se TODOS os critérios forem satisfeitos.

Agora que apresentamos as principais funções, vamos ao que interessa. Um exemplo prático.

Vou utilizar um controle de carreteiros que estou fazendo agora. Vou simplificar algumas coisas para tornar mais didático.

A ideia é criar um banco de dados com o cadastro dos caminhões e utilizar a placa como código para lançamento da planilha.

Depois dos dados lançados, vamos fazer um resumo diário de cada caminhão escolhendo o tipo de material transportado.

Primeiro passo é criar as tabelas com os dados, que no nosso caso, serão as dos caminhões e dos materiais.


Tentei embaralhar um pouco as placas para demonstrar que elas não precisam estar em ordem para a função PROCV().

Agora, vamos montar a tabela de lançamentos dos dados.

Vou manter as colunas que serão digitadas em amarelo e as que contém fórmula em branco. Isso não só serve para nosso exemplo, mas ajuda muito durante o manuseio da planilha.


Preferi montar a tabela de lançamento junto com as tabelas dos dados para melhor didática, mas usualmente eu separo.

Acrescentei uma coluna na tabela materiais com as densidades correspondentes. Apenas para realizar o cálculo de peso.

*A unidade da densidade é ton/m³

Observe que nesta tabela de lançamentos, digitamos apenas 3 colunas. Todas as restantes são fórmulas.

Coluna H - =SEERRO(PROCV($G4;$A$4:$D$10;2;FALSO);"")
Coluna I - =SEERRO(PROCV($G4;$A$4:$D$10;4;FALSO);"")
Coluna J - =SEERRO(PROCV($G4;$A$4:$D$10;3;FALSO);0)

Note que a única diferença entre as fórmulas acima é o índice da coluna que será retornada quando encontrar o valor G4, ou seja, a placa na tabela caminhões.

Coluna L - =SEERRO(PROCV($K4;$A$15:$C$17;2;FALSO);"")
Coluna M - =SEERRO(PROCV($K4;$A$15:$C$17;3;FALSO);"")
Coluna N - =J4*M4

Agora vou promover algumas modificações nesta mesma planilha para que os resumos saiam nas próprias tabelas de dados. Uma forma de encurtar meu trabalho aqui.

Vou inserir duas colunas para cada tabela, uma com o total geral, utilizando a função SOMASE e outra com os totais por período, usando a função SOMASES.


Os valores que considerei para a soma são da coluna L (volume). Poderia escolher a coluna P (peso) ou qualquer outra que quisesse. Ou ainda fazer um teste para que eu pudesse escolher qual somar. Mas para a postagem atual, creio que está de bom tamanho.

Para a coluna E, utilizei o SOMASE onde não levo em consideração o período e sim a soma total de todas as ocorrências.

Coluna E - =SOMASE($I$4:$I$20;A4;$L$4:$L$20)

Note como é simples. A função faz um teste na coluna I (em vermelho), procurando todas as vezes que ocorrem o valor na célula A4 (em azul) e soma os valores da coluna L (em roxo) todas as vezes que o teste for verdadeiro. Mamão com açúcar né?

Para a coluna F, a função é um pouco mais longa mas igualmente simples. A função SOMASES difere da SOMASE apenas por possibilitar testar mais de UM intervalo.

Coluna F - =SOMASES($L$4:$L$20;$I$4:$I$20;A4;$H$4:$H$20;">="&$F$20;$H$4:$H$20;"<="&$F$21)

Note que agora, a coluna de soma vem primeiro (em vermelho), diferente da função SOMASE onde o intervalo de soma ficava no final da fórmula.

Cada intervalo da fórmula que grifei, correspondem ao intervalo e ao critério de teste correspondente.
A única diferença para os dois intervalos finais, é que ele testa todas as ocorrências em H que sejam maiores ou iguais que F20 e que, também em H, sejam menores ou iguais a F20.

O truque aqui é que tanto a função SOMASE como a SOMASES aceitam texto como critério, o que amplia muito a possibilidade de uso para ambas.

Uma última observação é que o número de possibilidades é imensa. Podemos bolar resumos de tantas formas quanto quisermos.

Algumas limitações (ou que demandam muita fórmula) é a de fazer resumos por grupos.
Exemplo: Gostaria de fazer um resumo único onde os dados de um caminhão ficasse no topo e todos os seus lançamentos dentro de um período escolhido. E após seu último lançamento, a planilha lançasse os dados do próximo caminhão e seus registros na sequencia e assim sucessivamente até o último caminhão.

A resposta fácil para esse problema é a utilização do Access ao invés do Excel.

A forma de trabalho do Access é bem voltada para esse tipo de resumo.

Mas não se preocupem, não vou fazer postagens sobre o Access. Se montar postagem sobre planilhas já dá trabalho, em Access eu faria uma única postagem e abandonaria o Blog... for good!

Link para a planilha do texto: Planilha e imagens

Nenhum comentário:

Postar um comentário

"Be cool" quando for escrever seu comentário. Seja construtivo!