Raporty w stylu tabel przestawnych za pomocą formuł

Tabela przestawna krzyżowo zestawia dane: wiersze zawierają jedną kategorię, kolumny drugą, a komórki siatki zawierają sumy. Klasyczny przykład to Region z boku, Quarter u góry oraz Sales w każdej komórce.

Tabele przestawne są bardzo przydatne, ale wymagają ręcznego odświeżania i zajmują stały obszar. Tabela przestawna oparta na formułach odtwarza się na bieżąco za każdym razem, gdy zmienią się dane.

W tej lekcji zostaną rozmieszczone nagłówki wierszy i kolumn oraz obszar zawierający formuły SUMIFS, które automatycznie obliczą każde przecięcie.

Zostanie użyty arkusz o nazwie Sales z następującymi kolumnami: Region w A, Quarter w B i Amount w C, w wierszach od 2 do 500.

Raport, który chcemy uzyskać, wygląda tak:

• Nagłówki wierszy: każdy unikatowy Region w kolumnie E.
• Nagłówki kolumn: Q1, Q2, Q3, Q4 w wierszu 1, od F do I.
• Obszar danych: łączna wartość Amount dla każdej pary Region i Quarter.

Każda komórka obszaru danych odpowiada na jedno pytanie: ile ten region sprzedał w tym kwartale?

Nagłówkami wierszy są unikatowe regiony. Proszę użyć UNIQUE razem z SORT, aby rozlać je w dół kolumny E i zachować ich uporządkowanie.

Proszę umieścić tę formułę w E2:

Regiony wypełnią teraz samoczynnie komórkę E2 i kolejne komórki poniżej. Podobnie jak w tabelach podsumowujących, ta lista stanowi punkt odniesienia dla całej siatki.

Nagłówki kolumn to kwartały rozmieszczone w jednym wierszu. Można wpisać Q1, Q2, Q3, Q4 ręcznie albo rozlać je poziomo, umieszczając TRANSPOSE wokół funkcji UNIQUE.

W F1 ta formuła rozmieści unikatowe kwartały wzdłuż górnej krawędzi:

TRANSPOSE przekształca pionową listę w poziomą, dzięki czemu kolumna kwartałów staje się wierszem nagłówków. Obie osie siatki są teraz gotowe.

Teraz należy wypełnić obszar danych. Każda komórka potrzebuje sumy dla regionu z danego wiersza i kwartału z danej kolumny. SUMIFS z łatwością obsługuje dwa warunki.

W pierwszej komórce obszaru danych, F2, należy wpisać:

Formuła odczytuje wartości Amount, gdy Region jest równy etykiecie po lewej, a Quarter jest równy nagłówkowi powyżej. Jest to pojedyncze przecięcie tabeli przestawnej.

Znaki dolara umożliwiają wypełnienie całej siatki przez kopiowanie jednej formuły. Proszę przeanalizować mieszane odwołania:

• $E2 blokuje kolumnę E, ale pozwala zmieniać wiersz, dzięki czemu każdy wiersz odczytuje własny region.
• F$1 blokuje wiersz 1, ale pozwala zmieniać kolumnę, dzięki czemu każda kolumna odczytuje własny kwartał.
• $C$2:$C$500 jest całkowicie zablokowane, ponieważ zakres danych nigdy się nie przesuwa.

Proszę skopiować F2 we wszystkich kolumnach kwartałów i we wszystkich wierszach regionów. Każda komórka prawidłowo dopasuje się samoczynnie.

Po prawidłowym utworzeniu F2 należy ją zaznaczyć i przeciągnąć uchwyt wypełniania w prawo przez kolumny kwartałów, a następnie w dół przez wiersze regionów. Excel samodzielnie przepisze odwołania względne.

• Komórka G2 używa Region $E2 i Quarter G$1.
• Komórka F3 używa Region $E3 i Quarter F$1.

Wynikiem jest pełne zestawienie krzyżowe z sumą dla każdego przecięcia. Nie jest potrzebny kreator tabeli przestawnej, a raport przelicza się natychmiast po zmianie danych w Sales.

Rzeczywista tabela przestawna pokazuje sumy całkowite. Proszę dodać kolumnę Total po prawej stronie oraz wiersz Total na dole, używając zwykłej funkcji SUM dla każdego wiersza i każdej kolumny.

Aby obliczyć sumę wiersza pierwszego regionu, należy umieścić tę formułę w kolumnie znajdującej się za ostatnim kwartałem:

Aby obliczyć sumę kolumny, należy zsumować komórki obszaru danych danego kwartału w dół wierszy. Te sumy na krawędziach sprawiają, że raport jest kompletny, i pozwalają szybko sprawdzić poprawność liczb.

Jeśli używane narzędzie to obsługuje, można uniknąć kopiowania, przekazując odwołania do rozlanych zakresów bezpośrednio do SUMIFS. Jako kryteriów należy użyć rozlanych nagłówków.

Ta pojedyncza formuła sumuje każde przecięcie regionu i kwartału:

W tym przypadku E2# jest pionową listą regionów, a F1# poziomą listą kwartałów. Excel łączy je w pełną siatkę za jednym razem. Metoda przeciągania jest bardziej zgodna z różnymi wersjami narzędzi, ale ta wersja jest eleganckim, nowoczesnym rozwiązaniem.

Raporty dostarczają więcej informacji, gdy pokazują udział, a nie tylko kwoty. Proszę dodać kolumnę wyrażającą sumę każdego regionu jako procent sumy całkowitej.

Jeśli suma wiersza regionu znajduje się w J2, a suma całkowita w J10, należy wpisać:

Zablokowanie sumy całkowitej za pomocą $J$10 pozwala wypełnić formułę w dół dla wszystkich regionów, a dzielnik zawsze pozostaje taki sam. Po sformatowaniu kolumny jako procentowej od razu widać, które regiony mają największy udział.

Kilka dobrych nawyków pomaga zachować niezawodność tabeli przestawnej opartej na formułach:

• Należy odwoływać się do pełnych, odpowiednio dużych zakresów, takich jak wiersze od 2 do 500, aby uwzględnić nowe wiersze.
• Zakresy danych należy blokować za pomocą pełnych kotwic $; tylko odwołania do nagłówków powinny się zmieniać.
• Należy pozostawić wolne miejsce poniżej i po prawej stronie, aby rozlane nagłówki i sumy miały gdzie się zmieścić.

Jeśli raport zostanie poprawnie przygotowany, nie będzie wymagał żadnej konserwacji. Wystarczy wpisać nowe dane sprzedaży, a siatka, sumy i etykiety zaktualizują się samoczynnie.

Proszę sprawdzić, jak dobrze zostały opanowane mieszane odwołania, na których opiera się tabela przestawna zbudowana za pomocą formuł.

Tabela przestawna została odtworzona wyłącznie za pomocą formuł:

• UNIQUE w połączeniu z SORT utworzyły nagłówki wierszy w rozlanej kolumnie.
• TRANSPOSE rozmieściła nagłówki kolumn w jednym wierszu.
• SUMIFS z mieszanymi odwołaniami $E2 i F$1 wypełniła każde przecięcie, przez przeciąganie albo za pomocą odwołań do rozlanych zakresów, takich jak E2# i F1#.
• SUM dodała sumy całkowite na krawędziach.

Cała siatka przelicza się na bieżąco. Następnie dashboard zostanie uzupełniony o interaktywne listy rozwijane sterujące metrykami.