Tabela przestawna podsumowuje listę: grupuje wiersze według jednego lub kilku pól i dla każdej grupy sumuje, liczy albo uśrednia pole liczbowe. Zamiast pisać formuły, przeciągasz pola na miejsce, co czyni ją najszybszym sposobem na odpowiedź na pytania „ile na ...?”.
Budowanie tabeli
Wyobraź sobie listę szkółki roślin. A2:A7 zawiera oddział (North, South, North, South, North, South), B2:B7 roślinę (Rose, Rose, Fern, Fern, Rose, Fern), a C2:C7 ilość (10, 6, 4, 9, 5, 3). Kliknij w środku listy i wybierz Wstawianie > Tabela przestawna. Na liście pól przeciągnij Branch do Wiersze, a Quantity do Wartości.
Otrzymujesz North 19 i South 18, z sumą całkowitą 37. Przeciągnij Plant do Kolumny, a tabela stanie się siatką oddział na roślinę: Rose 15 i 6, Fern 4 i 12. Cztery obszary to Filtry, Kolumny, Wiersze i Wartości.
Suma, liczba, średnia
Obszar Wartości domyślnie używa Sumy dla liczb, ale możesz to zmienić w Ustawieniach pola wartości: liczba, średnia, minimum, maksimum. Możesz też użyć Pokaż wartości jako do procentów sumy całkowitej. Jeśli kolumna liczbowa pokazuje Liczbę zamiast Sumy, to pewnie niektóre jej komórki są tekstem.
Kształtowanie wyniku
Wybierz Pokaż wartości jako > % sumy całkowitej, a tabela szkółki pokaże North jako 51.4%, a South jako 48.6%, bo 19 i 18 to udziały w 37. Tabelę przestawną możesz sortować według sum, filtrować do jednej rośliny w obszarze Filtry i dwukrotnie kliknąć dowolną sumę, aby otworzyć wiersze, z których się składa, na nowym arkuszu. Ten ostatni trik to szybki sposób, by sprawdzić, dlaczego liczba wygląda dziwnie.
Jeśli lista ma kolumnę z datą, kliknij prawym przyciskiem datę w tabeli i wybierz Grupuj, aby podsumować według miesiąca, kwartału albo roku. A jeśli klikniesz komórkę tabeli przestawnej podczas pisania formuły, Excel może wstawić formułę GETPIVOTDATA, która odwołuje się do tej liczby po nazwie, więc nadal zadziała, gdy układ się zmieni.
Odświeżanie i czyszczenie danych źródłowych
Tabela przestawna nie aktualizuje się, gdy zmienia się lista. Kliknij ją prawym przyciskiem i wybierz Odśwież, albo naciśnij Alt+F5. Aby odświeżyć naraz wszystkie tabele przestawne w skoroszycie, naciśnij Ctrl+Alt+F5. Żeby nowe wiersze dołączały do tabeli przestawnej, najpierw zamień źródło w tabelę za pomocą Ctrl+T. Stały zakres źródłowy sam nie rośnie, więc wiersze dopisane pod nim będą brakować. Jest też ustawienie odświeżania przy otwieraniu pliku.
- Każda kolumna potrzebuje nagłówka, bez pustych nagłówków.
- Bez pustych wierszy i scalonych komórek wewnątrz listy.
- Jeden rodzaj wartości w kolumnie: nie mieszaj liczb z tekstem.
- Pilnuj jednolitej pisowni: „North” i „North ” (ze spacją na końcu) to dwie różne grupy.
Sprawdzanie i wybór
Zawsze sprawdź tabelę przestawną raz za pomocą formuły. Dla szkółki =SUMA.JEŻELI(A2:A7;"North";C2:C7) powinna zwrócić 19, a =SUMA(C2:C7) sumę całkowitą 37. Jeśli wyniki się nie zgadzają, szukaj liczb zapisanych jako tekst albo filtrowanych pól.
Użyj tabeli przestawnej do eksploracji i szybkich podsumowań z wieloma grupami. Używaj formuł, gdy wynik musi stać w stałej komórce, zasila inne obliczenia albo aktualizuje się od razu, bez odświeżania. Wiele dobrych skoroszytów używa obu.