Raport z eksportu danych w Excelu: jak zrobić podsumowanie bez pracy ręcznej

Wiele osób zna ten rytuał: system wypluwa eksport do Excela, a potem przez godzinę powstają tabele zbiorcze. Sumy według miesięcy, ranking kontrahentów, porównanie z zeszłym rokiem. Za miesiąc to samo od początku, bo poprzednie zestawienie było zrobione na tamtym eksporcie.

Rozwiązaniem jest raport zbudowany na stałych formułach. Wklejasz dane w jedno miejsce, a wszystko inne przelicza się samo. Poniżej opisuję, jak taki układ zaplanować, jakich funkcji użyć i jak sprawdzić, czy dane są w ogóle poprawne. Na końcu pokazuję gotowy szablon.

Zasada: dane osobno, raport osobno

Arkusz z danymi to tylko miejsce na wklejenie eksportu. Nie dodajesz tam niczego ręcznie, poza kolumnami pomocniczymi na końcu. Raport leży na innym arkuszu i odwołuje się do danych formułami, więc nowy eksport nie niszczy układu.

Eksport powinien mieć podstawowe pola: datę, kategorię, kontrahenta, ilość i kwotę. Jeśli nagłówki różnią się od tych, które ma twój system, zapisz ich nazwy w komórkach parametrów, a formuły niech czytają je stamtąd.

Kolumny pomocnicze

Eksport trzeba znormalizować. Dodaj obok danych:

• miesiąc: =MIESIĄC(A2)
• rok: =ROK(A2)
• klucz kategoria|miesiąc, jeśli planujesz tabelę krzyżową.

Zamiast tabeli przestawnej, która wymaga odświeżania i bywa kapryśna, użyj SUMA.WARUNKÓW. Formuła zawsze przelicza się sama.

Podstawowe formuły raportu

Suma kwoty w miesiącu danego roku:

=SUMA.WARUNKÓW(Dane!$E:$E;Dane!$F:$F;B$1;Dane!$G:$G;$B$2)

gdzie F to miesiąc, G to rok, a $B$2 to rok raportu.

Wzrost względem poprzedniego miesiąca:

=JEŻELI.BŁĄD(C3/B3-1;"")

Wzrost względem tego samego miesiąca ubiegłego roku to ta sama formuła, ale z sumą z roku $B$2-1.

Macierz „kategoria × miesiąc” to SUMA.WARUNKÓW z dwoma kryteriami: kategorią w wierszu i miesiącem w kolumnie. Ranking dziesięciu największych kontrahentów zbudujesz funkcją DUŻA na liście sum według kontrahentów oraz INDEKS i PODAJ.POZYCJĘ do odczytania nazw.

Przykład z liczbami

Eksport sprzedaży z trzech miesięcy: styczeń 40 000 zł, luty 46 000 zł, marzec 41 400 zł.

Gdy dodasz drugą kolumnę z zeszłym rokiem, łatwo zobaczysz, czy lutowy wzrost był sezonowy, czy rzeczywisty.

Kontrola danych

Raport jest tyle wart, ile dane. Zanim komuś go pokażesz, sprawdź:

1. puste daty (LICZ.PUSTE),
2. tekst zamiast liczb w kolumnie kwot,
3. daty spoza okresu raportu,
4. duplikaty wierszy (LICZ.JEŻELI większe od 1).

Wiersze podejrzane oznacz kolorem, a liczniki wyświetl na osobnym arkuszu. Jedna kwota wpisana jako tekst potrafi „zniknąć” z sumy bez żadnego komunikatu.

Najczęstsze błędy

1. Edytowanie surowego eksportu ręcznie.
2. Formuły z zakresami na stałe, które nie obejmują nowych wierszy.
3. Daty zapisane jako tekst.
4. Różne nazwy tej samej kategorii w eksporcie.
5. Porównywanie miesięcy bez sprawdzenia, czy wszystkie dane z miesiąca są kompletne.

Gotowy szablon raportu z eksportu

Płatny szablon zamienia dowolny eksport (data, kategorie, kontrahent, ilość, kwota) w gotowe podsumowanie. Wklejasz dane i dostajesz sumy według miesięcy i kategorii, wzrost, rankingi i wykresy.

Arkusze:

• „Parametry”: rok raportu i nazwy kolumn twojego eksportu.
• „Dane”: nagłówek i 5000 wierszy do wklejenia oraz pomocnicze kolumny normalizacji.
• „Podsumowanie”: sumy według miesięcy, wzrost względem poprzedniego miesiąca i tego samego miesiąca ubiegłego roku, top 5 kategorii, top 10 kontrahentów, macierz „kategoria × miesiąc”, wykres słupkowy i kołowy.
• „Wybór okresu”: kwota i ilość za dowolny okres dla wybranych kategorii.
• „Kontrola danych”: puste daty, tekst zamiast liczb, daty poza okresem i duplikaty.

Ograniczenia to do 5000 wierszy, do 30 kategorii i do 200 kontrahentów. Szablon przyda się księgowym, menedżerom i kierownikom działów, a także urzędom i instytucjom, które składają raporty z eksportów systemów. Działa w Excelu 2007 i nowszym oraz w LibreOffice, bez makr. Plik jest po polsku.

FAQ

Czy raport zadziała z eksportu z dowolnego systemu?

Jeśli eksport zawiera datę, kategorię, ilość i kwotę, tak. Nazwy kolumn dopasowujesz w parametrach.

Co zrobić, gdy eksport ma więcej niż 5000 wierszy?

Podziel go na okresy, np. kwartały, i przygotuj osobny raport dla każdego, albo wcześniej zsumuj dane w systemie źródłowym.

Czy lepsza jest tabela przestawna?

Tabela przestawna jest szybka do jednorazowej analizy. Raport na formułach przydaje się, gdy ten sam układ powtarzasz co miesiąc i chcesz tylko wkleić nowe dane.

Jak uniknąć błędów w kwotach?

Zawsze zaczynaj od arkusza kontroli danych. Jeśli liczniki pokazują zero problemów, możesz ufać sumom.