Tabele podsumowań z tablicami dynamicznymi

Tabela podsumowująca przekształca dużą listę surowych wierszy w niewielki, czytelny blok: po jednym wierszu dla każdej kategorii, a obok sumy. Proszę wyobrazić sobie rejestr sprzedaży zawierający setki wierszy, który zmienia się w schludną tabelę pokazującą każdy region i jego łączny przychód.

Dawniej trzeba było ręcznie utworzyć tabelę przestawną i ją odświeżać. Nowoczesne rozwiązanie wykorzystuje formuły tablic dynamicznych, które aktualizują się natychmiast po zmianie danych. Bez przycisków i bez odświeżania.

W tej lekcji zostaną połączone trzy zaawansowane narzędzia: UNIQUE do wyświetlania kategorii, SUMIFS do sumowania każdej z nich oraz FILTER do pobierania pasujących wierszy. Razem tworzą aktualizowane na żywo podsumowanie.

Proszę wyobrazić sobie arkusz o nazwie Sales z trzema kolumnami: Region w kolumnie A, Product w kolumnie B i Amount w kolumnie C, wypełnionymi wierszami od 2 do 200.

Naszym celem jest podsumowanie pokazujące każdy unikatowy region i jego łączną sprzedaż. Pierwszym wyzwaniem jest uzyskanie czystej listy regionów bez ręcznego wpisywania ich, ponieważ później mogą pojawić się nowe regiony.

• A2:A200 zawiera wiele powtarzających się nazw regionów, takich jak East, West, East, North.
• Potrzebujemy tylko: East, West, North, przy czym każdy z nich ma wystąpić raz.

Ta lista unikatowych wartości stanowi podstawę całego podsumowania.

Funkcja UNIQUE przyjmuje zakres i zwraca każdą wartość tylko raz. Rozlewa wynik, co oznacza, że jedna formuła wypełnia tyle komórek, ile jest unikatowych wartości.

Proszę wpisać ją w komórce E2, a lista regionów pojawi się automatycznie poniżej:

Jeśli później do danych zostanie dodany nowy region, rozlana lista samoczynnie się powiększy. Nie trzeba nigdy edytować formuły.

Teraz potrzebujemy łącznej wartości Amount dla każdego regionu w kolumnie E. SUMIFS dodaje wartości z jednego zakresu tylko wtedy, gdy wartości w innym zakresie spełniają określony warunek.

Składnia ma postać SUMIFS(sum_range, criteria_range, criteria). Proszę umieścić tę formułę w F2, obok pierwszego regionu:

Odwołanie E2# jest tu kluczowe. Znak # oznacza cały rozlany zakres zaczynający się w E2. Dzięki temu jedna formuła sumuje każdy region zwrócony przez UNIQUE.

Odwołanie do rozlanego zakresu E2# zawsze wskazuje cały blok utworzony przez formułę, niezależnie od tego, jak bardzo się on rozrośnie. To właśnie sprawia, że podsumowanie jest dynamiczne.

Gdy UNIQUE znajdzie 3 regiony, E2# obejmuje 3 komórki, a SUMIFS zwraca 3 sumy. Gdy liczba regionów wzrośnie do 5, oba zakresy rozszerzą się razem, bez żadnych zmian.

• E2 = tylko pojedyncza komórka na górze.
• E2# = cała rozlana tablica zaczynająca się w E2.

Proszę dobrze poznać znak #, ponieważ stanowi on podstawę formuł używanych w dashboardach.

Podsumowanie jest czytelniejsze, gdy sumy są uporządkowane. Proszę zastosować funkcję SORT do listy regionów, aby kategorie pojawiły się alfabetycznie, albo posortować całą tabelę według sumy.

Aby wyświetlić regiony alfabetycznie w E2:

Ponieważ sumy w F nadal odwołują się do E2#, sortowanie regionów automatycznie dopasuje do nich sumy. Obie kolumny pozostaną zsynchronizowane.

Czasami potrzebne są wiersze źródłowe dla jednej kategorii, a nie tylko suma. FILTER zwraca każdy wiersz spełniający warunek i rozlewa wynik.

Aby wyświetlić wszystkie wiersze sprzedaży, w których Region jest równy wartości w komórce H1:

Jeśli w H1 znajduje się East, zostanie wyświetlony każdy wiersz dotyczący East. Po zmianie H1 na West cały blok natychmiast się zaktualizuje. To podstawa widoku szczegółowego w dashboardzie.

FILTER zgłasza błąd #CALC!, gdy nic nie pasuje. Aby zachować czytelność, należy podać opcjonalny trzeci argument zawierający komunikat zastępczy.

Trzeci argument jest wyświetlany, gdy nie znaleziono żadnych dopasowań:

Teraz region bez sprzedaży wyświetla przyjazny komunikat zamiast błędu. Na dashboardach zawsze należy dodawać taki komunikat zastępczy, aby przypadkowy wybór nie zaburzył układu.

Podsumowanie często pokazuje nie tylko kwotę, lecz także liczbę zamówień w każdym regionie. COUNTIFS zlicza wiersze spełniające warunek, podobnie jak SUMIFS, ale nie wymaga zakresu sumowania.

Proszę umieścić tę formułę w kolumnie G, obok sum:

Teraz podsumowanie złożone z trzech kolumn zawiera Region, Total Sales i Order Count, a wszystkie wartości są sterowane przez pojedynczą rozlaną listę regionów w E2#. Wszystko aktualizuje się razem.

Oto pełny schemat rozmieszczony obok siebie:

• E2: =SORT(UNIQUE(Sales!A2:A200)) wyświetla regiony.
• F2: =SUMIFS(Sales!C2:C200, Sales!A2:A200, E2#) sumuje wartości dla każdego regionu.
• G2: =COUNTIFS(Sales!A2:A200, E2#) zlicza wartości dla każdego regionu.

Tylko formułę w E2 wpisuje się ręcznie; F i G rozlewają się dzięki odwołaniu z użyciem znaku #. Dodanie nowej sprzedaży w dowolnym miejscu arkusza Sales aktualizuje wszystkie trzy kolumny bez klikania.

Podsumowanie oparte na formułach ma rzeczywiste zalety w porównaniu z ręcznym wpisywaniem wartości lub odświeżaniem tabeli przestawnej:

• Aktualne: przelicza się natychmiast po zmianie danych.
• Samoczynnie dopasowujące rozmiar: nowe kategorie pojawiają się automatycznie dzięki UNIQUE i odwołaniu z użyciem znaku #.
• Przejrzyste: każdy może odczytać logikę bezpośrednio z komórki.

Wadą jest to, że zakresy rozlane potrzebują pustego miejsca, w które mogą się powiększyć. W późniejszej lekcji omówione zostaną zablokowane rozlania. Na razie proszę pozostawić wolne miejsce poniżej formuł.

Została utworzona tabela podsumowująca, która sama się utrzymuje:

• UNIQUE wyświetla każdą kategorię raz i rozlewa wynik.
• SORT porządkuje tę listę, zwiększając jej czytelność.
• SUMIFS i COUNTIFS sumują wartości i zliczają każdą kategorię, korzystając z odwołania do rozlanego zakresu E2#.
• FILTER pobiera pasujące wiersze do widoku szczegółowego i wyświetla komunikat zastępczy, gdy nie ma dopasowań.

Ponieważ każda formuła korzysta z rozlanej listy, dodanie nowych danych aktualizuje całe podsumowanie bez ręcznych czynności. Następnie zostaną odtworzone pełne raporty w stylu tabel przestawnych, wyłącznie za pomocą formuł.