KHIZHNYAKOVComo controlar consumo de energia, água e gás com limites no Excel
Controle de energia, água e gás com limites em Excel: eletricidade, água, calor e gás — Ver a planilha →
Em escolas, prédios administrativos, condomínios e pequenos órgãos, os dados de consumo costumam estar espalhados: as leituras dos medidores num caderno, as faturas em outra pasta, o limite anual num documento à parte. O resultado é conhecido: o excesso só aparece no fim do ano, quando já não há o que fazer.
Com uma planilha bem estruturada, você enxerga o desvio mês a mês, calcula o custo do excesso e compara com o mesmo período do ano anterior. A seguir está o método completo, que funciona com qualquer recurso: eletricidade, água, calor ou gás.
Estrutura básica de dados
Mantenha uma tabela de consumo com uma linha por mês, prédio e recurso. Colunas mínimas: ano, mês, prédio, recurso e consumo (em kWh, m³ ou outra unidade). Evite uma coluna por mês: ela parece organizada, mas impede filtros e resumos.
Em uma segunda aba, crie as tabelas de apoio:
- Tarifas: preço por unidade de cada recurso, conforme seu contrato ou fatura.
- Limites anuais: quantidade permitida por prédio e por recurso.
- Distribuição mensal do limite: fração do limite anual que cabe a cada mês.
Por que distribuir o limite por mês
Dividir o limite anual por 12 parece natural, mas é injusto para recursos sazonais. O aquecimento pesa mais no inverno; o ar-condicionado, no verão. Uma distribuição que reflita a sazonalidade evita falsos alarmes. A única regra é que, para cada recurso, as frações somem 100%.
Exemplo: limite anual de 12.000 kWh para o prédio A.
| Mês | Fração | Limite do mês |
|---|---|---|
| Janeiro | 10% | 1.200 kWh |
| Abril | 7% | 840 kWh |
| Julho | 6% | 720 kWh |
O limite do mês é calculado com uma fórmula simples:
=limite_anual * fração_do_mês
Fórmulas do dia a dia
Valor do consumo pela tarifa, buscando o preço com PROCV:
=E2 * PROCV(D2; Tarifas!A:B; 2; FALSO)
Excesso em unidades, que nunca fica negativo:
=MÁXIMO(0; consumo - limite_mes)
Excesso em valor:
=excesso * tarifa
Variação em relação ao ano anterior, usando SOMASES para encontrar o mesmo mês, prédio e recurso:
=consumo / SOMASES(consumo; ano; A2-1; mes; B2; predio; C2; recurso; D2) - 1
Se o consumo de março é de 1.050 kWh contra 960 kWh em março do ano anterior, a variação é de +9,4%. Já o excesso, num mês com limite de 900 kWh, é de 150 kWh; com tarifa de R$ 0,85 por kWh, isso representa R$ 127,50.
O semáforo de limite
Três faixas bastam:
- Verde: consumo abaixo do limite de aviso.
- Amarelo ("perto do limite"): o consumo atingiu uma fração do limite do mês (por exemplo, 90%), mas ainda não o ultrapassou.
- Vermelho: limite excedido.
=SE(consumo>limite;"Excedido";SE(consumo>=limite*0,9;"Perto do limite";"Dentro"))
O amarelo é o mais útil, porque mostra o problema enquanto ainda dá tempo de agir, por exemplo, orientando as equipes ou revisando um equipamento.
Resumo e gráficos
Para o relatório final, use tabelas dinâmicas ou SOMASES para somar consumo, valor e excesso por recurso e por prédio. Um gráfico de colunas com o consumo mensal, e uma linha com o limite, mostra a história de relance. Outro gráfico, comparando o ano atual com o anterior, ajuda a mostrar se as medidas de economia estão funcionando.
Erros comuns
- Misturar unidades: kWh com MWh, ou m³ com litros, dá resultados absurdos.
- Digitar a tarifa em cada linha: quando ela muda, o histórico fica errado. Guarde-a em tabela.
- Esquecer prédios de consumo zero: um medidor parado também precisa de registro.
- Limite sem sazonalidade: gera alarmes falsos em estações de pico.
- Comparar meses com número de dias muito diferente sem pensar: fevereiro tem menos dias, e isso afeta o consumo.
Modelo pronto de controle de energia, água e gás
O modelo organiza esse trabalho em três abas. Em «Listas» ficam os recursos e as tarifas, os prédios (até 8), os limites anuais de cada prédio e recurso, as frações do limite por mês e o ano do relatório. Em «Consumo», com até 1000 registros, você informa ano, mês, prédio, recurso e consumo, e a planilha calcula tarifa, valor, limite do mês, excesso, semáforo e variação em relação ao ano anterior.
O «Resumo» traz os totais por recurso e por prédio, o excesso em valor, a tabela mensal e dois gráficos. A comparação com o ano anterior é automática: basta registrar os dados do ano anterior na mesma tabela. O limite de aviso do semáforo (90% por padrão) pode ser alterado em «Listas».
Funciona no Excel 2007 ou superior e no LibreOffice, sem macros, em português do Brasil. É um modelo pago. Tarifas, limites e demais valores são parâmetros: confira-os com os seus contratos e faturas. O cálculo é indicativo.
FAQ
Preciso das faturas para usar a planilha?
As leituras dos medidores bastam para controlar o consumo, mas as faturas ajudam a conferir a tarifa e a validar os números. Use as duas fontes sempre que possível.
Como definir o limite anual?
Parta do histórico: o consumo dos últimos 12 meses, menos a meta de economia. Se a sua instituição recebe limites de outro órgão, informe esses valores e confira-os com a versão em vigor.
Dá para controlar vários prédios no mesmo arquivo?
Sim. Basta incluir a coluna do prédio na tabela e calcular o limite por prédio e recurso. O resumo mostra qual unidade gasta mais.
O que fazer quando um mês passa do limite?
Veja primeiro se houve algo atípico (obra, evento, medidor com defeito). Depois compare com o mesmo mês do ano anterior. Se o aumento persiste, vale revisar equipamentos e hábitos.
