Se utiliza regularmente o Excel para analisar dados detalhados em listas extensas com muita informação e pretende conhecer apenas a informação essencial que pode extrair dessas listas, então pode apoiar-se nas fórmulas SUMIFS, COUNTIFS, AVERAGEIFS que calculam respetivamente a soma, a contagem e a média de acordo com múltiplas condições e simplificar significativamente o seu trabalho.
Suponha por exemplo que dispõe de uma listagem com a produção diária de sapatos do primeiro trimestre do ano assim como do número de unidades vendidas e respetivas unidades produzidas com defeito.
Teria uma lista com a seguinte estrutura:
Quanto mais extensa for a lista mais difícil será obter informação relevante, o que aliás é um problema cada vez mais frequente dado o excesso de dados disponíveis atualmente.
Então, uma primeira abordagem para sintetizar a informação poderia passar por usar a fórmula SUMIF, que executa a soma de dados de um determinado intervalo de acordo com uma única condição e resumir toda a informação por meses.
Como, na primeira coluna, temos datas e pretendemos resumir a informação em meses, uma solução possível seria criar uma coluna adjacente com apenas a indicação do mês de cada registo.
Seria algo do género:
A partir daqui bastaria dizer ao Excel para somar as unidades produzidas, vendidas e produzidas com defeito com a condição de pertencerem ao mês a que dizem respeito que, de acordo com a fórmula anterior seria:
- 1 para Janeiro
- 2 para fevereiro
- 3 para Março
Deste modo, a fórmula para somar a produção do mês de janeiro poderia ser:
=SUMIF($A$2:$A$92;1;C$2:C$92)
- No primeiro argumento da fórmula introduzimos o intervalo que pretendemos avaliar, que no nosso caso é o número do mês em questão e está nas células A2 a A92.
- No segundo argumento, o mês para o qual queremos somar (1 na fórmula acima)
- E no terceiro o intervalo de dados a somar, neste caso a produção, que está nas células C2 a C92.
Adotando a mesma fórmula para as vendas e para a produção defeituosa, obteríamos a seguinte tabela que resume a informação mensalmente:
Esta informação é mais sintética e fácil de analisar. Também não depende do ordenamento dos dados de origem, que não têm necessariamente de estar ordenados cronologicamente.
Se pretendermos descobrir mais informação relevante neste conjunto de dados, e saber por exemplo:
- Quantos foram os dias em que a produção excedeu as 50.000 unidades,
- A venda média diária e
- A taxa de defeituosos.
Podemos usar as fórmulas COUNTIFS e AVERAGEIFS que calculam respetivamente a contagem e a média aritmética que respeitam uma ou várias condições.
Assim, relativamente à primeira questão, a fórmula poderia ser:
=COUNTIFS($A$2:$A$92;1;$C$2:$C$92;">"&50000)
Em que:
- No primeiro argumento introduzimos o número do mês para cada registo,
- No segundo argumento inserimos o critério, que será igual a 1, 2 ou 3 conforme o mês para o qual pretendemos executar a contagem,
- No terceiro argumento introduzimos o intervalo de células que contêm o critério que pretendemos avaliar (no nosso caso será a produção diária que está nas células C2 a C92 e finalmente
- No quarto argumento introduzimos o critério, expresso por: “>”&50000, que se deve ler como: “maior ou igual a 50000”.
A venda média diária pode ser obtida a partir da fórmula:
=AVERAGEIFS($C$2:$C$92;$A$2:$A$92;1)
Em que:
- No primeiro argumento temos o intervalo de células para o qual pretendemos obter a média (será o intervalo A2 a A92 que contém as vendas diárias),
- No segundo argumento, o intervalo que pretendemos avaliar, que corresponde ao número do mês de cada registo e está nas células A2 a A92 e, finalmente
- No terceiro argumento, o número do mês para o qual pretendemos executar a fórmula.
Por fim, para calcular a taxa média de defeituosos, podemos simplesmente dividir o número total de defeituosos pelo número total de unidades produzidas, a partir da tabela anterior.
Então, os resultados seriam os seguintes:
Estes números fornecem uma informação bastante mais útil para a tomada de decisões. Desde logo, podemos verificar que
- Houve muitos mais dias em fevereiro em que a produção excedeu o limite de 50.000 unidades,
- As vendas médias por dia aumentaram a partir de fevereiro e mantiveram-se num patamar próximo das 66.000 unidades,
- A taxa de defeituosos subiu consideravelmente em fevereiro e março quando comparada com o valor de janeiro.
Quanto mais extensa (e dinâmica) for a fonte de informação destes dados maior terá de ser a capacidade do analista para obter dela o sumo que facilita a tomada de decisões de gestão bem fundamentadas.
NOTA:
Em anexo encontra-se o ficheiro que utilizei para este exemplo, que poderá descarregar se achar interessante.
