20 anos usando o Excel e ele ainda me surpreende. E sabe o que é mais curioso? Ele te surpreende com recursos que sempre estiveram lá, há anos, muitos, mas você nunca notou!
Este é o caso das funções de Banco de Dados, ou BD*.
Eu tive contato com estas fórmulas no começo da minha vida no Excel, mas, seja pela forma como ela foi explicada ou minha falta de experiência, acabei não sabendo o que fazer com elas e acabei deixando pra lá.
Não vou abordar todas aqui, primeiro, porque uma vez que você aprenda a usar uma, as outras seguem o mesmo raciocínio de uso, tornando fácil a adaptação, segundo, a lista é longa, terceiro… já esqueci, mas vamos ao que interessa.
No fim do artigo tem uma lista de TODAS as funções de banco de dados do próprio site da Microsoft para você conferir.
É hora do show!
Vou usar aqui o BDSOMA. Ele vai resumir o espetáculo que utilizar as funções de banco de dados. Vou aplicar o conceito máximo do reaproveitamento e utilizar o próprio exemplo da Microsoft.
Considere a tabela de dados abaixo:
A | B | C | D | E | |
1 | Árvore | Altura | Idade | Rendimento | Lucro |
2 | Maçã | 18 | 20 | 14 | $105 |
3 | Pera | 12 | 12 | 10 | $96 |
4 | Cereja | 13 | 14 | 9 | $105 |
5 | Maçã | 14 | 15 | 10 | $75 |
6 | Pera | 9 | 8 | 8 | $77 |
7 | Maçã | 8 | 9 | 6 | $45 |
Uma lista de simples de pés-de-alguma fruta, coisa que todo morador de interior já teve no quintal de casa.
Para o exemplo, se quisermos extrair a SOMA dos rendimentos, fica fácil, se quisermos extrair a SOMASE a árvore for de Maçã, também fica fácil. Mas, se quisermos extrair a soma se a árvore for Maçã e os redimentos forem maiores que 10 e menores do que 16, precisamos pensar um pouco.
Se você for um pouco letrado no Excel, sabe que isso se resolve com um SOMASES com a seguinte fórmula:
=SOMASES(D2:D7;B2:B7;”>10″;A2:A7;”=Maçã”;B2:B7;”<16″)
Mas, se complicarmos um pouco mais, gerando critérios múltiplos para nossa soma, você precisa ou elaborar muito o SOMASES ou utilizar mais de um. Por exemplo, se eu quiser incluir a soma de tudo o que for Pera, a fórmula fica assim:
=SOMASES(D2:D7;B2:B7;”>10″;A2:A7;”=Maçã”;B2:B7;”<16″)+SOMASES(D2:D7;A2:A7;”=Pera”)
Resolve, mas é “feio”.
Nada contra o SOMASES, o problema é que, se você precisa repetir uma fórmula, a probabilidade de existir uma solução mais simples é inegável. Outra reclamação pessoal que tenho é que, para entender o que está acontecendo, eu basicamente tenho que decifrar a fórmula, ou seja, entrar na célula e lembrar o que ela está fazendo. Para fórmulas simples, isso não é problema, mas para coisas medonhas que requerem quebras de linha, não é tão simples.
Isso não acontece quando você utiliza o BDSOMA. Vamos aplicar o mesmo critério de soma detalhado acima usando-a. Abaixo, a planilha um pouco transformada:
A | B | C | D | E | F | |
1 | Árvore | Altura | Idade | Rendimento | Lucro | Altura |
2 | =Maçã | >10 | <16 | |||
3 | =Pera | |||||
4 | ||||||
5 | Árvore | Altura | Idade | Rendimento | Lucro | |
6 | Maçã | 18 | 20 | 14 | $105 | |
7 | Pera | 12 | 12 | 10 | $96 | |
8 | Cereja | 13 | 14 | 9 | $105 | |
9 | Maçã | 14 | 15 | 10 | $75 | |
10 | Pera | 9 | 8 | 8 | $77 | |
11 | Maçã | 8 | 9 | 6 | $45 |
Eu já gosto mais do que estou vendo. Em resumo, a tabela principal foi empurrada algumas linhas para baixo e deu espaço ao que vou chamar de área de filtros ou critérios. Ou seja:
A5:E11 – São os dados que queremos trabalhar
A1:F3 – Os filtros a serem aplicados
Usando o BDSOMA, o mesmo resultado do SOMASES pode ser conseguido com a seguinte fórmula:
=BDSOMA(A5:E11;”Rendimento”;A1:F3)
Explicando a fórmula acima:
- A5:E11 é obviamente a área de dados a ser avaliada
- “Rendimento” é a coluna que queremos somar. Sim, você não precisa mencionar índice ou coisa parecida. Basta o nome
- A1:F3 são os filtros, que podem ser repetidos, unidos e cruzados! Veja que temos duas linhas para dizer que queremos árvores de Maçã OU Pera, e no caso da Maçã, com altura >10 (célula B2) e <16 (célula F2)
Não sei para você leitor, mas para mim, isso é mágico! Os filtros estão à mostra e sei EXATAMENTE o que está sendo considerado na soma! E posso mudar à vontade a área de filtros para ver o resultado mudar, sem precisar mexer na fórmula! Experimente!
Sim, você pode chegar num resultado parecido com o SOMASES, mas dá mais trabalho, já que não é natural.
O formato utilizado é só uma convenção, sendo que os filtros podem ser colocar em qualquer lugar do seu arquivo.
“Mas se eu quiser o PRODUTO? Ou a MÉDIA?”. Oh meu caro padawan, divirta-se com a lista abaixo que utiliza a mesma estrutura do BDSOMA:
Função | Descrição |
---|---|
BDMÉDIA | Retorna a média das entradas selecionadas de um banco de dados |
BDCONTAR | Conta as células que contêm números em um banco de dados |
BDCONTARA | Conta células não vazias em um banco de dados |
BDEXTRAIR | Extrai de um banco de dados um único registro que corresponde a um critério específico |
BDMÁX | Retorna o valor máximo de entradas selecionadas de um banco de dados |
BDMÍN | Retorna o valor mínimo de entradas selecionadas de um banco de dados |
BDMULTIPL | Multiplica os valores em um campo específico de registros que correspondem ao critério em um banco de dados |
BDEST | Estima o desvio padrão com base em uma amostra de entradas selecionadas de um banco de dados |
BDDESVPA | Calcula o desvio padrão com base na população inteira de entradas selecionadas de um banco de dados |
BDSOMA | Soma os números na coluna de campos de registros do banco de dados que correspondem ao critério |
BDVAREST | Estima a variação com base em uma amostra de entradas selecionadas de um banco de dados |
BDVARP | Calcula a variação com base na população inteira de entradas selecionadas de um banco de dados |