Limity zużycia mediów w Excelu: prąd, woda, ciepło i gaz według budynków

O przekroczeniu rocznego limitu mediów zwykle dowiadujemy się w grudniu, kiedy nic już nie da się zrobić. Odczyty z liczników leżą w jednym pliku, rachunki w drugim, a limity w dokumencie, do którego nikt nie zagląda. Dopiero porównanie wszystkiego razem pokazuje, który budynek zużywa za dużo.

Poniżej znajdziesz sposób na kontrolę prądu, wody, ciepła i gazu w Excelu: jak rozłożyć limit roczny na miesiące, policzyć przekroczenie w jednostkach i w pieniądzach, porównać z rokiem poprzednim i ustawić ostrzeżenie z wyprzedzeniem. Na końcu opis gotowego szablonu.

Jak kontrolować limity mediów w Excelu

1. Jeden dziennik zużycia

Zamiast osobnej zakładki dla każdego medium zrób jedną tabelę: rok, miesiąc, budynek, medium, zużycie. Jeden wiersz to jeden odczyt miesięczny. Dzięki temu dane za kilka lat leżą w jednym miejscu, a porównania stają się proste.

2. Słowniki: taryfy i limity

Potrzebujesz dwóch tabel:

• taryfa za jednostkę dla każdego medium (kWh, m³, GJ),
• roczny limit dla każdego budynku i medium.

Liczby weź z umów, rachunków i dokumentów dotyczących limitów swojej jednostki. W razie zmiany cen wpisujesz nową wartość w jednym miejscu.

3. Limit na miesiąc: nie dziel przez 12

Ogrzewanie w styczniu i w lipcu to dwa różne światy. Dlatego dodaj tabelę udziałów miesięcznych, w której suma dla medium wynosi 100%. Dla prądu możesz przyjąć niemal równe udziały, dla ciepła i gazu większe w sezonie grzewczym.

=WYSZUKAJ.PIONOWO(budynek&medium;Limity!A:C;3;FAŁSZ)*INDEKS(Udziały!B2:B13;miesiąc)

Pamiętaj, żeby klucz łączony (budynek i medium) istniał w kolumnie pomocniczej tabeli limitów.

Kontrola sumy udziałów:

=JEŻELI(ZAOKR(SUMA(B2:B13);4)=1;"OK";"Suma musi dać 100%")

4. Przekroczenie, kwota i status

Przekroczenie w jednostkach: =MAX(0;zużycie-limit_miesięczny)

Przekroczenie w pieniądzach: =przekroczenie*taryfa

Status z ostrzeżeniem wyprzedzającym, gdy próg „blisko limitu” to 90%:

=JEŻELI(zużycie>limit;"Przekroczenie";JEŻELI(zużycie>=0,9*limit;"Blisko limitu";"W normie"))

5. Porównanie z rokiem poprzednim

Gdy dane z lat leżą w jednym dzienniku, wystarczy SUMA.WARUNKÓW z rokiem minus jeden:

=SUMA.WARUNKÓW(Zużycie!E:E;Zużycie!A:A;A2-1;Zużycie!B:B;B2;Zużycie!C:C;C2;Zużycie!D:D;D2)

Zmiana procentowa to różnica podzielona przez wartość z poprzedniego roku (z zabezpieczeniem przed dzieleniem przez zero).

Przykład z liczbami

Budynek A, gaz. Roczny limit 12 000 m³, udział stycznia 18%, taryfa 3,20 zł za m³.

• Limit na styczeń: 12 000 × 18% = 2 160 m³.
• Zużycie w styczniu: 2 400 m³.
• Przekroczenie: 240 m³.
• Koszt przekroczenia: 240 × 3,20 = 768 zł.
• Rok wcześniej w styczniu: 2 000 m³, czyli wzrost o 20%.

W lutym zużycie 1 900 m³ przy limicie 1 920 m³ (16%) da status „Blisko limitu”: 99% limitu to sygnał, że warto sprawdzić instalację przed końcem miesiąca.

Częste błędy

• Dzielenie limitu przez 12. Zimą zawsze wyjdzie „przekroczenie”, latem „oszczędność”, choć nic się nie zmieniło.
• Niezgodne jednostki. Gaz w m³, ciepło w GJ i prąd w kWh nie mogą trafić do jednej sumy bez przeliczenia.
• Odczyty bez miesiąca. Jeśli zapisujesz daty odczytu, ustal, do którego miesiąca przypisujesz zużycie.
• Stare taryfy. Po zmianie cennika kwoty zaniżają lub zawyżają koszty.
• Brak kontroli budynków. Zbiorcza suma ukrywa jeden budynek, który wyraźnie odstaje.

Gotowy szablon ewidencji mediów i limitów

Szablon (płatny) zawiera wszystko opisane powyżej:

• „Słowniki”: media i taryfy, budynki (do 8), roczne limity dla każdego budynku i medium, udziały limitu według miesięcy i rok sprawozdawczy.
• „Zużycie”: do 1000 wpisów z rokiem, miesiącem, budynkiem, medium i zużyciem. Tabela sama liczy taryfę, kwotę, limit na miesiąc, przekroczenie, semafor i zmianę względem roku poprzedniego.
• „Podsumowanie”: wyniki według mediów i budynków, przekroczenie w pieniądzach, tabela miesięczna i dwa wykresy.

Domyślnie udziały uwzględniają sezon grzewczy dla ciepła i gazu, a próg „Blisko limitu” wynosi 90% (zmienisz go w „Słownikach”). Wpisz dane za poprzedni rok do tego samego dziennika, a zmiana procentowa policzy się sama. Plik działa w Excelu 2007 i nowszym oraz w LibreOffice, bez makr. Jest w zestawie pusty i z fikcyjnym przykładem. Taryfy i limity porównaj z własnymi dokumentami, bo obliczenia są orientacyjne.

FAQ

Jak często wpisywać odczyty?

Raz w miesiącu wystarczy do kontroli limitów. Jeśli chcesz szybciej wychwytywać awarie, np. wyciek wody, odczytuj liczniki częściej i miej osobny arkusz roboczy.

Co zrobić, gdy limit jest przekroczony?

Najpierw sprawdź, czy to sezon, czy anomalia: porównaj z tym samym miesiącem roku poprzedniego i z innymi budynkami. Dopiero potem szukaj przyczyny, np. nieszczelnej instalacji lub zmiany sposobu użytkowania.

Czy da się prowadzić kilka budynków i mediów w jednym pliku?

Tak, jeśli każdy wiersz ma budynek i medium. Formuły SUMA.WARUNKÓW oddzielają je od siebie, a podsumowanie pokazuje wyniki w obu przekrojach.

Skąd wziąć limity?

Z umów, decyzji i dokumentów wewnętrznych Twojej jednostki. Jeśli ich nie ma, możesz przyjąć jako limit średnie zużycie z ostatniego roku i obniżać je o wybrany procent jako cel oszczędnościowy.