Tabela przestawna w Excelu: jak ją zrobić krok po kroku

Tabela przestawna grupuje wiersze tabeli według kategorii, na przykład Region, i sumuje liczbę, na przykład Sales, dla każdej grupy, bez formuł. Aby ją utworzyć, kliknij komórkę w danych, przejdź do Wstawianie > Tabela przestawna, naciśnij OK i przeciągnij Region do Wierszy, a Sales do Wartości. Arkusz poniżej nie jest tabelą przestawną: buduje to samo podsumowanie formułami, więc możesz patrzeć, jak zmieniają się sumy. Tabela pokazuje formuły po angielsku, ale możesz w niej wpisywać formuły także po polsku, ze średnikami: =SUMA.JEŻELI($A$2:$A$9;E2;$C$2:$C$9).

FormułyResetuj

F2fx

=SUMIF($A$2:$A$9,E2,$C$2:$C$9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Sales	% of total
2	North	Apple	120		North	455	49%
3	South	Pear	85		South	305	33%
4	North	Pear	240		East	170	18%
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =SUMA.JEŻELI($A$2:$A$9;E2;$C$2:$C$9)

UNIKATOWE (UNIQUE) wypisuje każdy region raz, a SUMA.JEŻELI (SUMIF) go sumuje: North 455, South 305 i East 170, czyli 49%, 33% i 18% z łącznych 930. Zmień C3 na 185, a suma South i wszystkie trzy procenty od razu się dostosują. Tabela przestawna pokazałaby te same liczby, ale dopiero po odświeżeniu.

Jak utworzyć tabelę przestawną

Zanim zaczniesz, sprawdź dane źródłowe: jeden wiersz nagłówków z nazwą w każdej kolumnie, jeden rekord na wiersz, bez pustych wierszy i kolumn w środku i bez wierszy sum częściowych.

1. Kliknij dowolną komórkę w danych.
2. Przejdź do Wstawianie > Tabela przestawna (w niektórych wersjach Wstawianie > Tabela przestawna > Z tabeli/zakresu).
3. Excel wypełnia zakres. Wybierz Nowy arkusz i naciśnij OK.
4. Pojawia się pusta tabela przestawna z okienkiem Pola tabeli przestawnej po prawej, które wymienia nagłówki twoich kolumn.
5. Przeciągnij Region do pola Wiersze, a Sales do pola Wartości. Tabela przestawna pokazuje każdy region raz z sumą sprzedaży obok i wierszem Suma końcowa.
6. Aby zmienić to, co jest pokazane, przeciągaj pola między polami albo poza okienko.

Jeśli nie wiesz, od czego zacząć, Wstawianie > Zalecane tabele przestawne pokazuje kilka gotowych układów dla twoich danych. Na Macu menu jest takie samo: Wstawianie > Tabela przestawna.

Wiersze, Kolumny, Wartości i Filtry

Okienko Pola tabeli przestawnej ma cztery pola, a każda tabela przestawna to wybór, która kolumna trafia do którego pola:

• Wiersze: kategorie po lewej stronie, jeden wiersz na każdą różną wartość (Region).
• Kolumny: kategorie u góry, jedna kolumna na każdą różną wartość (Product).
• Wartości: liczby do obliczenia dla każdej kombinacji. Dla kolumny liczbowej domyślna jest Suma; Licznik, Średnia, Maksimum, Minimum i inne są w Ustawieniach pola wartości.
• Filtry: pole, według którego filtrowana jest cała tabela przestawna, pokazane jako lista rozwijana nad nią.

Z Region w Wierszach, Product w Kolumnach i Sales w Wartościach tabela przestawna dla powyższych danych wygląda tak (w angielskim Excelu):

Sum of Sales   Column Labels
Row Labels     Apple   Pear   Grand Total
East              60    110           170
North            215    240           455
South            150    155           305
Grand Total      425    505           930

Wersja tego układu z formułami wypisuje regiony w dół funkcją UNIKATOWE, produkty w poziomie funkcją TRANSPONUJ(UNIKATOWE()) i oblicza każdą komórkę siatki jedną funkcją SUMA.WARUNKÓW (SUMIFS), która przyjmuje obie listy jako kryteria:

FormułyResetuj

F2fx

=SUMIFS(C2:C9,A2:A9,E2:E4,B2:B9,F1:G1)

	A	B	C	D	E	F	G
1	Region	Product	Sales			Apple	Pear
2	North	Apple	120		North	215	240
3	South	Pear	85		South	150	155
4	North	Pear	240		East	60	110
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =SUMA.WARUNKÓW(C2:C9;A2:A9;E2:E4;B2:B9;F1:G1)

E2 rozlewa North, South i East w dół, F1 rozlewa Apple i Pear w poziomie, a SUMA.WARUNKÓW w F2 wypełnia między nimi siatkę 3 na 2: jedną sumę dla każdej pary regionu i produktu. Zmień B5 z Apple na Pear, a obie komórki East się zmienią. Kolejność jest tu kolejnością pierwszego wystąpienia wartości; tabela przestawna sortuje etykiety alfabetycznie.

Licznik, średnia albo procent zamiast sumy

W tabeli przestawnej kliknij pole w polu Wartości i wybierz Ustawienia pola wartości. Karta Podsumuj wartości według przełącza między Sumą, Licznikiem, Średnią, Maksimum i Minimum; karta Pokaż wartości jako zamienia liczby na % sumy końcowej, % sumy kolumny, sumę bieżącą i inne. Każda z nich ma bezpośredni odpowiednik w formule:

FormułyResetuj

E2fx

=UNIQUE(A2:A9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Orders	Average
2	North	Apple	120		North	3	151.7
3	South	Pear	85		South	3	101.7
4	North	Pear	240		East	2	85.0
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =UNIKATOWE(A2:A9)

North ma 3 zamówienia ze średnią 151.7, South 3 ze średnią 101.7, East 2 ze średnią 85.0. Kolumna % sumy końcowej jest w pierwszym arkuszu na tej stronie. Formuły z tej tabeli to po polsku LICZ.JEŻELI i ŚREDNIA.JEŻELI: =LICZ.JEŻELI($A$2:$A$9;E2) i =ŚREDNIA.JEŻELI($A$2:$A$9;E2;$C$2:$C$9).

Filtrowanie podsumowania według jednego produktu

Pole Filtry umieszcza nad tabelą przestawną listę rozwijaną. Wersja z formułami to komórka z listą rozwijaną i SUMA.WARUNKÓW, która dodaje do SUMA.JEŻELI jeszcze jeden warunek. Wybierz produkt w F1:

FormułyResetuj

F1fx

	A	B	C	D	E	F
1	Region	Product	Sales		Product:	Apple▾ApplePear
2	North	Apple	120
3	South	Pear	85		Region	Sales
4	North	Pear	240		North	215
5	East	Apple	60		South	150
6	South	Apple	150		East	60
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.

Przy wybranym Apple North pokazuje 215, South 150, a East 60. Wybierz Pear, a zmienią się na 240, 155 i 110. Więcej warunków opisuje strona SUMA.WARUNKÓW, a jak dodać listę w Excelu, strona o liście rozwijanej.

Odświeżanie tabeli przestawnej

Tabela przestawna trzyma kopię danych źródłowych (pamięć podręczną tabeli przestawnej) i nie przelicza się, gdy zmieni się komórka w źródle. Po edycji danych:

• Kliknij prawym przyciskiem w dowolnym miejscu tabeli przestawnej i wybierz Odśwież albo naciśnij Alt+F5 w Windows.
• Dane > Odśwież wszystko (Ctrl+Alt+F5) odświeża każdą tabelę przestawną w skoroszycie.
• Aby odświeżać przy każdym otwarciu pliku, kliknij tabelę przestawną prawym przyciskiem, wybierz Opcje tabeli przestawnej i na karcie Dane zaznacz Odśwież dane podczas otwierania pliku.

Nowe wiersze dodane pod zakresem źródłowym nie są uwzględniane, nawet po odświeżeniu. Zmień zakres w Analiza tabeli przestawnej > Zmień źródło danych albo, lepiej, zamień źródło w tabelę przed utworzeniem tabeli przestawnej: zaznacz dane i naciśnij Ctrl+T albo użyj Wstawianie > Tabela. Tabela rośnie, gdy dodajesz wiersze, a tabela przestawna przejmuje je przy następnym odświeżeniu.

Suma każdego regionu jedną formułą

FormułyResetuj

F2fx

	A	B	C	D	E	F
1	Region	Product	Sales		Region	Sales
2	North	Apple	120		North
3	South	Pear	85		South
4	North	Pear	240		East
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.

Twoja kolej: W F2 zsumuj sprzedaż każdego regionu wymienionego w E2:E4 jedną formułą.

SprawdźPokaż odpowiedź

Odpowiedź rozlewa 455, 305 i 170. Podanie SUMA.JEŻELI całej listy E2:E4 jako kryterium zwraca jedną sumę na region, więc nie trzeba niczego kopiować w dół. Excel 2019 i starsze nie mają ani UNIKATOWE, ani rozlewania: wpisz regiony w E2:E4 i skopiuj w dół =SUMA.JEŻELI($A$2:$A$9;E2;$C$2:$C$9). Bez znaków $ zakresy przesuwają się w dół z każdym wierszem i sumy wychodzą złe.

GRUPUJ.WEDŁUG i PRZESTAWIAJ.WEDŁUG: tabela przestawna w jednej formule

Excel dla Microsoft 365 ma dwie funkcje, które budują całe podsumowanie jedną formułą i przeliczają się jak każda formuła, bez odświeżania. Wymagają aktualnej subskrypcji Microsoft 365. Dla powyższych danych:

=GROUPBY(A2:A9,C2:C9,SUM)

East      170
North     455
South     305
Total     930

=PIVOTBY(A2:A9,B2:B9,C2:C9,SUM)

          Apple   Pear   Total
East         60    110     170
North       215    240     455
South       150    155     305
Total       425    505     930

W polskim Excelu: =GRUPUJ.WEDŁUG(A2:A9;C2:C9;SUMA) i =PRZESTAWIAJ.WEDŁUG(A2:A9;B2:B9;C2:C9;SUMA). GRUPUJ.WEDŁUG (GROUPBY) przyjmuje pole wierszy, wartości i funkcję (SUMA, ILE.NIEPUSTYCH, ŚREDNIA, MAX, PERCENTOF). PRZESTAWIAJ.WEDŁUG (PIVOTBY) dodaje między nimi pole kolumn. Obie sortują etykiety i dodają wiersze sum, jak tabela przestawna.

Tabela przestawna czy formuły: czego użyć

Używaj tabeli przestawnej, aby eksplorować dane i raz odpowiedzieć na pytanie; formuł, gdy podsumowanie ma stać w raporcie, zasilać inne formuły i zawsze być aktualne. Aby sprawdzić liczby tabeli przestawnej, odtwórz jedną jej komórkę funkcją SUMA.WARUNKÓW: jeśli się nie zgadzają, tabela przestawna zwykle wymaga odświeżenia albo jej zakres źródłowy jest za krótki.