Excel 2021 i Microsoft 365. Przetwarzanie danych za pomocą tabel przestawnych - Bill Jelen

Bill Jelen

Przekład: Joanna Zatorska, Krzysztof Kapustka

APN Promise, Warszawa 2022

Excel 2021 i Microsoft 365. Przetwarzanie danych za pomocą tabel przestawnych

Authorized Polish translation of the English language edition entitled Microsoft Excel Pivot Table Data Crunching (Office 2021 And Microsoft 365), by Bill Jelen, ISBN: 978-0-13-752183-8

Copyright ? 2022 by Pearson Education, Inc.

All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording or by any information storage retrieval system, without permission from Pearson Education, Inc.

Polish language edition published by APN PROMISE SA Copyright ? 2022

Autoryzowany przekład z wydania w języku angielskim, zatytułowanego:Microsoft Excel Pivot Table Data Crunching (Office 2021 And Microsoft 365), by Bill Jelen, ISBN: 978-0-13-752183-8

Wszystkie prawa zastrzeżone. Żadna część niniejszej książki nie może być powielana ani rozpowszechniana w jakiejkolwiek formie i w jakikolwiek sposób (elektroniczny, mechaniczny), włącznie z fotokopiowaniem, nagrywaniem na taśmy lub przy użyciu innych systemów bez pisemnej zgody wydawcy.

APN PROMISE SA, ul. Domaniewska 44a, 02-672 Warszawatel. +48 22 35 51 600e-mail: wydawnictwo@promise.pl

Książka ta przedstawia poglądy i opinie autora. Przykłady firm, produktów, osób i wydarzeń opisane w niniejszej książce są fikcyjne i nie odnoszą się do żadnych konkretnych firm, produktów, osób i wydarzeń, chyba że zostanie jednoznacznie stwierdzone, że jest inaczej. Ewentualne podobieństwo do jakiejkolwiek rzeczywistej firmy, organizacji, produktu, nazwy domeny, adresu poczty elektronicznej, logo, osoby, miejsca lub zdarzenia jest przypadkowe i niezamierzone.

Microsoft oraz znaki towarowe wymienione na stronie http://www.microsoft.com/about/legal/en/us/IntellectualProperty/Trademarks/EN-US.aspx są zastrzeżonymi znakami towarowymi grupy Microsoft. Wszystkie inne znaki towarowe są własnością ich odnośnych właścicieli.

APN PROMISE SA dołożyła wszelkich starań, aby zapewnić najwyższą jakość tej publikacji. Jednakże nikomu nie udziela się rękojmi ani gwarancji. APN PROMISE SA nie jest w żadnym wypadku odpowiedzialna za jakiekolwiek szkody będące następstwem korzystania z informacji zawartych w niniejszej publikacji, nawet jeśli APN PROMISE została powiadomiona o możliwości wystąpienia szkód.

ISBN: 978-83-7541-484-4 (druk), 978-83-7541-485-1 (ebook)

Przekład: Joanna Zatorska, Krzysztof KapustkaRedakcja: Marek WłodarzKorekta: Ewa SwędrowskaSkład i łamanie: MAWart Marek Włodarz

Howie'mu Dickermanowi z firmy Microsoft. Świetnej emerytury!- Bill Jelen

Spis treści

Podziękowania

O autorze

Wprowadzenie

Rozdział 1

Podstawy tabel przestawnych

Dlaczego należy używać tabel przestawnych

Kiedy używać tabel przestawnych

Anatomia tabeli przestawnej

Obszar wartości

Obszar wierszy

Obszar kolumn

Obszar filtrów

Za kulisami tabel przestawnych

Wsteczna zgodność tabel przestawnych

Uwagi dotyczące zgodności

Kolejne kroki

Rozdział 2

Tworzenie prostej tabeli przestawnej

Dane powinny mieć układ tabelaryczny

Unikanie zapisywania danych w nagłówkach sekcji

Unikanie powtarzania grup jako kolumn

Eliminowanie brakujących danych i pustych komórek w danych źródłowych

Stosowanie odpowiedniego formatowania pól

Podsumowanie dotyczące poprawnego formatu danych źródłowych

Tworzenie prostej tabeli przestawnej

Dodawanie pól do raportu

Podstawy układu raportu tabeli przestawnej

Dodawanie warstw do tabeli przestawnej

Zmiana układu tabeli przestawnej

Tworzenie filtra raportu

Funkcje Recommended PivotTable oraz Analyze Data

Filtrowanie raportów z użyciem fragmentatorów

Tworzenie standardowego fragmentatora

Tworzenie fragmentatora osi czasu

Dotrzymywanie kroku zmianom w danych źródłowych

Radzenie sobie ze zmianami w istniejących danych źródłowych

Obsługa rozszerzonego zakresu danych źródłowych po dodaniu wierszy lub kolumn

Udostępnianie lub tworzenie nowej pamięci podręcznej tabeli przestawnej

Efekty uboczne współdzielenia pamięci podręcznej tabel przestawnych

Oszczędzanie czasu dzięki nowym narzędziom tabel przestawnych

Opóźnianie aktualizacji układu

Zaczynamy od nowa jednym kliknięciem

Zmiana lokalizacji tabeli przestawnej

Kolejne kroki

Rozdział 3

Dostosowywanie tabeli przestawnej

Wprowadzanie typowych zmian kosmetycznych

Stosowanie stylu tabeli w celu przywrócenia linii siatki

Zmiana formatu liczbowego w celu uwzględniania separatorów tysięcy

Zastępowanie pustych wartości zerami

Zmiana nazwy pola

Zmiany układu raportu

Użycie układu kompaktowego

Użycie układu konspektu

Użycie tradycyjnego układu tabelarycznego

Kontrolowanie pustych wierszy, sum końcowych i innych ustawień

Dostosowywanie wyglądu tabeli przestawnej za pomocą stylów i motywów

Dostosowywanie stylu

Modyfikowanie stylów za pomocą motywów dokumentu

Zmiana obliczeń sumarycznych

Zmiana obliczeń w polu wartości

Pokazywanie wartości procentowej całości

Użycie opcji % Of w celu porównania jednego wiersza z drugim

Pokazywanie kolejności

Śledzenie sumy bieżącej i wartości procentowej sumy bieżącej

Wyświetlanie zmiany względem poprzedniego pola

Śledzenie wartości procentowej elementu nadrzędnego

Śledzenie względnej ważności za pomocą opcji Index

Dodawanie i usuwanie sum częściowych

Wyłączanie sum częściowych dotyczących wielu pól wierszy

Dodawanie kilku sum częściowych do jednego pola

Formatowanie jednej komórki jest nową funkcją Microsoft 365

Kolejne kroki

Rozdział 4

Grupowanie, sortowanie i filtrowanie danych tabel przestawnych

Korzystanie z okna PivotTable Fields

Dokowanie i oddokowywanie okna PivotTable Fields

Minimalizowanie okna PivotTable Fields

Zmiana organizacji okna PivotTable Fields

Korzystanie z list w sekcji obszarów

Sortowanie tabeli przestawnej

Sortowanie klientów w kolejności od najwyższego do najniższego przychodu

Używanie ręcznej sekwencji sortowania

Sortowanie za pomocą list niestandardowych

Filtrowanie tabeli przestawnej: informacje ogólne

Korzystanie z filtrów dla pól wierszy i kolumn

Filtrowanie za pomocą pól wyboru

Filtrowanie za pomocą pola wyszukiwania

Filtrowanie za pomocą opcji Label Filters

Filtrowanie kolumny etykiety za pomocą informacji w kolumnie wartości

Tworzenie raportu o pięciu najwyższych wartościach za pomocą filtra Top 10

Filtrowanie za pomocą filtrów daty w menu etykiety

Filtrowanie za pomocą obszaru Filters

Dodawanie pól do obszaru Filters

Wybieranie jednego elementu z filtra

Wybieranie wielu elementów z filtra

Replikowanie raportu tabeli przestawnej dla każdego elementu w filtrze

Filtrowanie z użyciem fragmentatorów i osi czasu

Filtrowanie na podstawie daty za pomocą osi czasu

Obsługa wielu tabel przestawnych za pomocą jednego zestawu fragmentatorów

Grupowanie i tworzenie hierarchii w tabeli przestawnej

Grupowanie pól liczbowych

Ręczne grupowanie pól dat

Uwzględnianie lat podczas grupowania według miesięcy

Grupowanie pól daty według tygodni

Tworzenie łatwego raportu rok do roku

Tworzenie hierarchii

Kolejne kroki

Rozdział 5

Wykonywanie obliczeń w tabelach przestawnych

Wprowadzenie do pól i elementów obliczanych

Metoda 1: Ręczne dodawanie pola obliczanego do danych źródłowych

Metoda 2: Użycie formuły poza tabelą przestawną w celu utworzenia pola obliczanego

Metoda 3: Wstawianie pola obliczanego bezpośrednio do tabeli przestawnej

Tworzenie pola obliczanego

Tworzenie elementu obliczanego

Działanie reguł i mankamenty obliczeń tabel przestawnych

Kolejność pierwszeństwa operatorów

Korzystanie z odwołań do komórek i zakresów nazwanych

Korzystanie z funkcji arkuszy

Korzystanie ze stałych

Odwołania do sum

Reguły specyficzne dla pól obliczanych

Reguły specyficzne dla elementów obliczanych

Zarządzanie obliczeniami w tabelach przestawnych i ich utrzymanie

Edytowanie i usuwanie obliczeń w tabelach przestawnych

Zmiana kolejności rozwiązywania elementów obliczanych

Dokumentowanie formuł

Kolejne kroki

Rozdział 6

Korzystanie z wykresów przestawnych i innych metod wizualizacji

Czym naprawdę są wykresy przestawne?

Tworzenie wykresu przestawnego

Działanie przycisków pól przestawnych

Tworzenie wykresu przestawnego od podstaw

Reguły tabel przestawnych

Zmiany w źródłowej tabeli przestawnej mają wpływ na wykres przestawny

Rozmieszczenie pól danych w tabeli przestawnej może nie sprzyjać wykresom przestawnym

W programie Excel nadal istnieje kilka ograniczeń formatowania

Alternatywy dla wykresów przestawnych

Metoda 1.: Przekształcanie tabeli przestawnej w sztywne wartości

Metoda 2: Usunięcie źródłowej tabeli przestawnej

Metoda 3: Dystrybucja obrazu wykresu przestawnego

Metoda 4: Użycie komórek połączonych z tabelą przestawną jako źródła danych dla wykresu

Formatowanie warunkowe tabel przestawnych

Przykład formatowania warunkowego

Wstępnie zaprogramowane scenariusze dla poziomów warunkowych

Tworzenie własnych reguł formatowania warunkowego

Kolejne kroki

Rozdział 7

Analizowanie różnych źródeł danych za pomocą tabel przestawnych

Korzystanie z modelu danych

Tworzenie pierwszego modelu danych

Zarządzanie relacjami w funkcji Data Model

Dodawanie nowej tabeli do modelu danych

Ograniczenia modelu danych

Tworzenie tabeli przestawnej za pomocą zewnętrznych źródeł danych

Tworzenie tabel przestawnych na podstawie danych z programu Microsoft Access

Tworzenie tabel przestawnych na podstawie danych z bazy SQL Server

Wykorzystanie Power Query do uzyskiwania i przekształcania danych

Podstawy funkcji Power Query

Zastosowane kroki

Odświeżanie danych Power Query

Zarządzanie istniejącymi zapytaniami

Działania na poziomie kolumny

Akcje tabel

Typy połączeń Power Query

Jeszcze jeden przykład Power Query

Kolejne kroki

Rozdział 8

Udostępnianie pulpitów za pomocą usługi Power BI

Zapoznanie z programem Power BI Desktop

Przygotowanie danych w programie Excel

Importowanie danych do programu Power BI

Wprowadzenie do interfejsu Power BI

Przygotowanie danych w programie Power BI

Definiowanie synonimów w programie Power BI Desktop

Budowanie interaktywnego raportu

Tworzenie pierwszej wizualizacji

Tworzenie drugiej wizualizacji

Filtrowanie między wykresami

Tworzenie hierarchii szczegółowości

Importowanie niestandardowej wizualizacji

Publikowanie w Power BI

Projektowanie dla urządzeń mobilnych

Publikowanie w przestrzeni roboczej

Kolejne kroki

Rozdział 9

Korzystanie z formuł modułów z modelem danych lub danymi OLAP

Przekształcanie tabeli przestawnej do formuł modułów

Wprowadzenie do technologii OLAP

Łączenie się z modułem OLAP

Struktura modułu OLAP

Ograniczenia tabel przestawnych OLAP

Tworzenie modułu offline

Wychodzenie poza formę tabeli przestawnej za pomocą funkcji modułów

Zapoznanie z funkcjami modułów

Dodawanie obliczeń do tabel przestawnych OLAP

Tworzenie miar obliczanych

Tworzenie obliczanych członków

Zarządzanie obliczeniami OLAP

Wykonywanie analiz warunkowych na danych OLAP

Kolejne kroki

Rozdział 10

Odblokowywanie funkcji za pomocą modelu danych i Power Pivot

Zastępowanie funkcji VLOOKUP modelem danych

Odblokowywanie ukrytych funkcji za pomocą modelu danych

Obliczanie unikalnych wartości w tabeli przestawnej

Uwzględnianie odfiltrowanych elementów w sumach

Tworzenie mediany w tabeli przestawnej za pomocą miar DAX

Raportowanie tekstu w obszarze Values

Przetwarzanie wielkich zbiorów danych za pomocą Power Query

Dodawanie nowej kolumny za pomocą Power Query

Power Query przypomina rejestrator makr, ale jest lepsze

Unikanie siatki programu Excel poprzez wczytanie danych do modelu danych

Dodawanie połączonej tabeli

Definiowanie relacji między dwoma tabelami przy użyciu widoku diagramu

Dodawanie kolumn obliczanych do siatki Power Pivot

Sortowanie kolumny według innej kolumny

Tworzenie tabeli przestawnej z modelu danych

Zaawansowane techniki Power Pivot

Obsługa skomplikowanych relacji

Korzystanie z analizy czasowej

Obchodzenie ograniczeń modelu danych

Inne korzyści funkcji Power Pivot

Tworzenie wszystkich następnych tabel przestawnych przy użyciu modelu danych

Więcej informacji

Kolejne kroki

Rozdział 11

Analizowanie danych geograficznych za pomocą funkcji 3D Map

Analizowanie danych geograficznych za pomocą funkcji 3D Map

Przygotowywanie danych dla 3D Map

Geokodowanie danych

Tworzenie wykresu kolumnowego w 3D Map

Nawigacja na mapie

Oznaczanie punktów etykietą

Tworzenie wykresów kołowych lub bąbelkowych na mapie

Korzystanie z map cieplnych i map regionów

Ustawienia 3D Map

Dostosowywanie 3D Map

Łączenie dwóch zbiorów danych

Animowanie danych w czasie

Tworzenie wycieczki

Tworzenie wideo w 3D Map

Kolejne kroki

Rozdział 12

Ulepszanie raportów tabel przestawnych za pomocą makr

Korzystanie z makr w raportach tabel przestawnych

Rejestrowanie makra

Tworzenie interfejsu użytkownika z kontrolkami formularza

Modyfikowanie zarejestrowanego makra w celu dodania nowych funkcji

Wstawianie kontrolki paska przewijania

Tworzenie makra w Power Query

Kolejne kroki

Rozdział 13

Tworzenie tabel przestawnych za pomocą VBA lub TypeScript

Włączanie VBA w swojej kopii programu Excel

Korzystanie z pliku w formacie umożliwiającym używanie makr

Visual Basic Editor

Narzędzia języka Visual Basic

Rejestrator makr

Zrozumienie kodu zorientowanego obiektowo

Sztuczki profesjonalistów

Pisanie kodu obsługującego zakres danych dowolnej wielkości

Korzystanie z super-zmiennych: zmienne obiektowe

Użycie With oraz End With w celu skrócenia kodu

Zrozumieć wersje

Tworzenie tabeli przestawnej w programie Excel za pomocą VBA

Dodawanie pól do obszaru Data

Formatowanie tabeli przestawnej

Radzenie sobie z ograniczeniami tabel przestawnych

Wypełnianie pustych komórek w obszarze danych

Wypełnianie pustych komórek w obszarze wierszy

Zapobieganie błędom po wstawieniu lub usunięciu komórek

Kontrolowanie sum końcowych

Przekształcanie tabeli przestawnej w wartości

Tabela przestawna 201: tworzenie raportu prezentującego przychody według kategorii

Upewnienie się, że korzystamy z układu tabelarycznego

Grupowanie dat w lata

Usuwanie pustych komórek

Kontrolowanie kolejności sortowania za pomocą funkcji AutoSort

Zmiana domyślnego formatu liczbowego

Ukrywanie sum częściowych dla wielu pól wierszy

Kopiowanie gotowej tabeli przestawnej w postaci wartości do nowego skoroszytu

Ostateczne formatowanie

Dodawanie sum częściowych w celu uzyskania łamania strony

Zebranie kodu w całość

Obliczenia za pomocą tabeli przestawnej

Rozwiązywanie problemów z co najmniej dwoma polami danych

Korzystanie z obliczeń innych niż Sum

Użycie obliczanych pól danych

Korzystanie z elementów obliczanych

Obliczanie grup

Wykonywanie innych obliczeń za pomocą funkcji Show Values As

Zaawansowane techniki tabel przestawnych

Korzystanie z funkcji AutoShow w celu utworzenia streszczenia

Filtrowanie zbioru rekordów za pomocą funkcji ShowDetail

Tworzenie raportów dla każdego regionu lub modelu

Ręczne filtrowanie co najmniej dwóch elementów w tabeli przestawnej

Korzystanie z filtrów konceptualnych

Korzystanie z filtra wyszukiwania

Konfigurowanie fragmentatorów w celu filtrowania tabeli przestawnej

Używanie modelu danych w programie Excel

Dodanie obydwu tabel do modelu danych

Tworzenie relacji między dwiema tabelami

Definiowanie pamięci podręcznej i tworzenie tabeli przestawnej

Dodawanie pól modelu do tabeli przestawnej

Dodawanie pól liczbowych do obszaru Values

Podsumowanie

Tworzenie tabel przestawnych za pomocą TypeScript w Excel Online

Kolejne kroki

Rozdział 14

Zaawansowane wskazówki i techniki dotyczące tabel przestawnych

Wskazówka 1: Wymuszanie automatycznego odświeżania tabel przestawnych

Wskazówka 2: Jednoczesne odświeżanie wszystkich tabel przestawnych w skoroszycie

Wskazówka 3: Sortowanie elementów danych w unikalnej kolejności, innej niż rosnąco i malejąco

Wskazówka 4: Używanie (lub unikanie używania) list niestandardowych do sortowania tabel przestawnych

Wskazówka 5: Zmiana zachowania wszystkich przyszłych tabel przestawnych za pomocą ustawień domyślnych

Wskazówka 6: Przekształcanie tabel przestawnych w sztywne dane

Wskazówka 7: Wypełnianie pustych komórek pozostałych po polach wierszy

Opcja 1: Implementacja funkcji Repeat All Item Labels

Opcja 2: Użycie funkcji Go To Special programu Excel

Wskazówka 8: Dodawanie pola z kolejnością do tabeli przestawnej

Wskazówka 9: Zmniejszanie rozmiaru raportów tabel przestawnych

Usuwanie arkusza z danymi źródłowymi

Wskazówka 10: Tworzenie automatycznie rozszerzalnego zakresu danych

Wskazówka 11: Porównywanie tabel za pomocą tabel przestawnych

Wskazówka 12: Automatyczne filtrowanie tabeli za pomocą funkcji AutoFilter

Wskazówka 13: Wymuszanie dwóch formatów liczbowych w tabeli przestawnej

Wskazówka 14: Formatowanie poszczególnych wartości w tabeli przestawnej

Wskazówka 15: Formatowanie sekcji tabeli przestawnej

Wskazówka 16: Tworzenie rozkładu częstotliwości za pomocą tabeli przestawnej

Wskazówka 17: Wykorzystanie tabeli przestawnej do rozłożenia zbioru danych na osobne zakładki

Wskazówka 18: Nakładanie ograniczeń na tabele i pola przestawne

Ograniczenia w tabeli przestawnej

Ograniczenia pól przestawnych

Wskazówka 19: Wykorzystanie tabeli przestawnej do rozłożenia zbioru danych na osobne skoroszyty

Wskazówka 20: Wyznaczanie zmiany procentowej względem ubiegłego roku

Wskazówka 21: Dwukierunkowa funkcja VLOOKUP za pomocą Power Query

Wskazówka 22: Fragmentator do kontrolowania danych z dwóch różnych zbiorów danych

Wskazówka 23: Formatowanie fragmentatorów

Kolejne kroki

Rozdział 15

Dr. Jekyll i Mr. GetPivotData

Unikanie nieprzyjemnego problemu GetPivotData

Unikanie funkcji GetPivotData poprzez wpisanie formuły

Wyłączanie funkcji GetPivotData

Dlaczego firma Microsoft zmusza nas do korzystania z funkcji GetPivotData

Rozwiązywanie problemów z tabelami przestawnymi za pomocą funkcji GetPivotData

Tworzenie brzydkiej tabeli przestawnej

Tworzenie szablonu raportu

Wypełnianie szablonu raportu za pomocą funkcji GetPivotData

Aktualizowanie raportu w nadchodzących miesiącach

Kolejne kroki

Rozdział 16

Tworzenie tabel przestawnych w Excel Online

Logowanie do aplikacji Excel Online

Tworzenie tabeli przestawnej w Excel Online

Zmiana opcji tabeli przestawnej w Excel Online

Co z pozostałymi funkcjami?

Kolejne kroki

Rozdział 17

Przestawianie kolumn bez użycia tabeli przestawnej za pomocą tablic dynamicznych lub Power Query

Tworzenie raportów krzyżowych z użyciem zaawansowanych filtrów i tabeli danych

Pozyskiwanie unikalnej listy wartości z użyciem filtra zaawansowanego

Agregowanie przychodów za pomocą funkcji DSUM

Replikowanie funkcji DSUM dla każdej kombinacji sektora i regionu

Jakie są zalety i wady tej metody?

Tworzenie raportu krzyżowego za pomocą trzech dynamicznych formuł tablicowych

Pozyskiwanie unikalnej listy wartości z użyciem tablic dynamicznych

Wypełnianie kwot przychodów przy użyciu funkcji SUMIFS

Jakie są wady i zalety tej metody?

Tworzenie raportu krzyżowego w Power Query

Wprowadzanie danych do Power Query

Podsumowywanie przychodów według sektora i regionu w Power Query

Sortowanie i przestawianie w Power Query

Czyszczenie i ostatnie kroki

Jakie są zalety i wady tego rozwiązania?

Kolejne kroki

Rozdział 18

Anulowanie przestawienia kolumn w Power Query

Dane w nagłówkach tworzą złe tabele przestawne

Przekształcanie danych za pomocą polecenia Unpivot w Power Query

Anulowanie przestawienia kolumn z dwóch wierszy nagłówków

Anulowanie przekształcenia kolumn z komórki z ogranicznikiem do postaci nowych wierszy

Konkluzja

Posłowie

Angielskie i polskie nazwy funkcji

Polecamy także

Podziękowania

Dziękuję zespołowi rozwijającemu program Excel w firmie Microsoft za odpowiedzi na pytania dotyczące różnych funkcji. Dziękuję całej społeczności z portalu MrExcel.com, ludziom pasjonującym się programem Excel. Dziękuję Bobowi Umlasowi za redakcję techniczną tej książki oraz Kughenom za zarządzanie projektem. Na koniec dziękuję swojej żonie Mary Ellen, za wsparcie podczas pisania tej książki.

-Bill Jelen

O autorze

Bill Jelen, nagrodzony tytułem Excel MVP oraz właściciel serwisu MrExcel.com, pracował z arkuszami kalkulacyjnymi od 1985, a w 1998 uruchomił serwis MrExcel.com. Bill był regularnym gościem programu Call for Help z Leo Laporte i wyprodukował ponad 2400 codziennych epizodów podkastów wideo, Learn Excel from MrExcel. Jest autorem 64 książek o programie Microsoft Excel, a także redaguje miesięczną kolumnę o tym programie w magazynie Strategic Finance. Przed uruchomieniem serwisu MrExcel.com, Bill spędził 12 lat pracując jako analityk finansowy w działach finansowym, reklamowym, księgowości oraz operacyjnym firmy publicznej wycenianej na 500 milionów dolarów. Mieszka w Merritt Island, w stanie Floryda, z żoną Mary Ellen.

Wprowadzenie

Tabela przestawna jest najpotężniejszym narzędziem dostępnym w programie Excel. Tabele przestawne pojawiły się w latach 90-tych XX wieku, gdy firmy Microsoft i Lotus walczyły ze sobą o dominację na rynku arkuszy kalkulacyjnych. Wyścig o ciągłe dodawanie ulepszonych funkcji do produktów w połowie lat 90-tych XX wieku doprowadził do rozwoju wielu wspaniałych funkcji, ale żadna z nich nie mogła się równać z tabelą przestawną.

Za pomocą tabeli przestawnej można w ciągu kilku sekund przekształcić milion wierszy danych transakcyjnych w raport podsumowania. Jeśli możemy przeciągać myszą, możemy utworzyć tabelę przestawną. Oprócz szybkiego podsumowywania i obliczania danych, tabele przestawne umożliwiają zmianę analizy w locie poprzez proste przenoszenie pól z jednego obszaru raportu do drugiego.

Żadne inne narzędzie w programie Excel nie daje nam takiej elastyczności i możliwości analitycznych jak tabela przestawna. Narzędzia Power Query, które zadebiutowały między wydaniami Excel 2013 i Excel 2016 mają zbliżone możliwości do tabeli przestawnej. Pewne przykłady zastosowania narzędzi Power Query zobaczymy w rozdziale 17., "Przestawianie kolumn bez użycia tabeli przestawnej za pomocą tablic dynamicznych lub Power Query" oraz 18., "Anulowanie przestawienia kolumn w Power Query".

Czego dowiemy się z tej książki

Powszechnie wiadomo, że prawie 60 procent użytkowników programu Excel nie korzysta wcale z 80 procent możliwości programu Excel - co oznacza, że większość osób nie wykorzystuje pełni możliwości narzędzi dostępnych w programie Excel. Spośród tych narzędzi, dotychczas najdoskonalszym jest tabela przestawna. Chociaż tabele przestawne stanowią sedno programu Excel już od prawie 30 lat, pozostają jednym z najbardziej niedocenianych narzędzi w całym pakiecie Microsoft Office.

Jeśli ktoś zwrócił uwagę na tę książkę, zapewne słyszał już o tabelach przestawnych - a być może miał okazję z nich korzystać. Wie też, że tabele przestawne oferują możliwości, których nie używa i chce się dowiedzieć, jak za ich pomocą szybko zwiększyć swoją wydajność.

W pierwszych dwóch rozdziałach utworzymy proste tabele przestawne, zwiększymy wydajność i utworzymy raporty w ciągu kilku minut zamiast godzin. Po przeczytaniu pierwszych siedmiu rozdziałów będziemy mogli utworzyć skomplikowane raporty przestawne z możliwością wyświetlenia szczegółów. Utworzymy też wykresy uzupełniające tabele. Po ukończeniu tej książki będziemy mogli zbudować dynamiczny system raportujący, oparty na tabelach przestawnych.

Nowe funkcje w tabelach przestawnych programu Microsoft 365 Excel

Tabele przestawne możemy teraz tworzyć także w programie Excel Online. Nie oferują one wszystkich funkcji dostępnych w programie Excel dla Windows, ale już sama możliwość tworzenia tabel przestawnych jest znaczącym krokiem na przód w internetowej wersji programu Excel. W poprzedniej edycji tej książki, wydanej zaledwie trzy lata temu, napisałem, że program Excel Online nigdy nie będzie umożliwiał tworzenia tabel przestawnych - dzisiaj mamy już taką możliwość. Nie możemy ich dostosowywać tak, jak w wersji dla Windows, ale możemy je tworzyć.

Office 365 oferuje nową funkcję Analyze Data (Analiza danych), której działanie opiera się na sztucznej inteligencji. Wystarczy zaznaczyć zbiór danych złożony z maksymalnie 250 000 komórek i poprosić program Excel o przeanalizowanie tych danych. Excel zaproponuje nam około 30 interesujących analiz, wliczając w to kilka tabel przestawnych.

Funkcja Analyze Data pozwala nam zadawać pytania na temat naszych danych. Istnieje bardzo duża szansa, że odpowiedź na takie pytanie będzie miała formę tabeli przestawnej lub wykresu przestawnego. Tym samym funkcje Analyze Data i Ask a Question (Zadaj pytanie) stają się nowymi punktami wyjścia do tworzenia tabel przestawnych.

Jeśli ktoś nie miał możliwości zapoznania się z edycją 2019 tej książki, warto przypomnieć, że od czasu wydania programu Excel 2016 pojawiły się w nim następujące nowe funkcje:

Możemy zdefiniować domyślne ustawienia dla wszystkich następnych tabel przestawnych. Automatyczne grupowanie dat w tabelach przestawnych wprowadzone w programie Excel 2016 można teraz wyłączyć. Puste komórki w kolumnie z komórkami liczbowymi będą traktowane jak wartości liczbowe i domyślnie zostanie zastosowane sumowanie zamiast zliczania. Funkcja Power Pivot jest wbudowana we wszystkie wersje programu Excel 2019 i późniejsze oraz w pakiet Office 365 dla systemu Windows.

Studium przypadku: życie przed pojawieniem się tabel przestawnych

Załóżmy, że nasz menedżer poprosił nas o utworzenie jednostronicowego podsumowania bazy danych sprzedaży. Chciałby sprawdzić całkowity przychód według regionu i produktu. Załóżmy, że nie umiemy tworzyć tabel przestawnych. Aby wykonać to zadanie, będziemy musieli kilkadziesiąt razy nacisnąć różne klawisze lub kliknąć myszą.

Ten przykładowy zbiór danych (dostępny w ramach przykładowych plików dla tej książki - patrz strona xxxii) zawiera nagłówki w wierszu 1 oraz dane w wierszach od 2 do 564 i kolumnach od A do I.

Najpierw musimy uzyskać posortowaną listę unikalnych regionów ułożoną pionowo wzdłuż lewej krawędzi raportu podsumowania oraz posortowaną listę unikalnych produktów ułożoną poziomo na górze. W przeszłości mogło to wymagać użycia funkcji Advanced Filter (Filtr zaawansowany) lub Remove Duplicates (Usuń duplikaty). Dzisiaj możemy to zrobić znacznie prościej za pomocą formuły.

Wprowadzamy formułę =SORT(UNIQUE(B2:B564)) w komórce K2. Otrzymamy w ten sposób listę unikalnych nazw regionów w komórkach K2:K5. Aby uzyskać poziomą listę unikalnych produktów na górze raportu, wprowadzamy formułę =TRANSPOSE(SORT(UNIQUE(C2:C564))) w komórce L1. Na tym etapie, po 57 naciśnięciach klawiszy, utworzyliśmy zarys raportu, ale nie mamy jeszcze żadnych wartości (patrz rysunek I-1).

Rysunek I-1 Uzyskanie tego efektu wymagało 57 naciśnięć klawiszy.

Następnie musimy skorzystać z dość nowej funkcji SUMIFS* i obliczyć całkowity przychód dla każdego regionu i produktu. Jak widać na rysunku I-2, można to osiągnąć za pomocą formuły =SUMIFS(G2:G564,B2:B564,K2#,C2:C564,L1#). Wymaga to wpisania 40 znaków i naciśnięcia Enter. Wpisujemy nagłówek Total w wierszu oraz kolumnie podsumowania. Można to wykonać za pomocą dziewięciu uderzeń klawiszy, jeśli wpiszemy pierwszy nagłówek, naciśniemy Ctrl+Enter, aby pozostać w komórce, a następnie użyjemy polecenia Copy, wybierzemy komórkę przeznaczoną na drugi nagłówek i wkleimy tytuł. Jeśli zaznaczymy zakres komórek K1:P6 i naciśniemy Alt+= (czyli Alt i znak równości), możemy dodać formułę podsumowania za pomocą trzech naciśnięć klawiszy.

Rysunek I-2 Gdyby nie tablice dynamiczne, formuła w komórce L2 musiałaby zawierać znaki dolara, a następnie zostać skopiowana do wszystkich szesnastu komórek pokazujących liczby.

Tą metodą, wymagającą 110 kliknięć lub uderzeń klawiszy, uzyskamy ładny raport podsumowania, widoczny na rysunku I-3. Gdyby ktoś umiał wykonać to w ciągu 5 lub 10 minut, prawdopodobnie byłby dumny z biegłości, z jaką posługuje się programem Excel; wśród tych 110 czynności znajduje się kilka dobrych sztuczek.

Rysunek I-3 Po wykonaniu zaledwie 110 czynności możemy się cieszyć raportem podsumowania.

Przekazujemy raport menedżerowi. Po kilku minutach wraca z następującymi wymaganiami, które oczywiście wymagają sporych przeróbek:

Czy można umieścić produkty pionowo wzdłuż krawędzi, a regiony poziomo na górze? Czy mogę uzyskać taki sam raport, lecz tylko dla klientów z branży przemysłowej? Czy mogę zobaczyć zyski zamiast przychodów? Czy można skopiować ten raport dla każdego z klientów?

Wynalezienie tabeli przestawnej

To, kiedy wynaleziono tabele przestawne, pozostaje sprawą dyskusyjną. To zespół programu Excel wymyślił termin pivot table (tabela przestawna), który pojawił się w programie w 1993. Jednak koncepcja nie była nowa. Pito Salas i jego zespół z firmy Lotus pracowali nad analogicznym pomysłem w 1986 roku i wydali Lotus Improv w roku 1991. Jeszcze wcześniej funkcję podobną do tabel przestawnych oferowała firma Javelin.

Główna koncepcja tabel przestawnych opiera się na osobnym przechowywaniu danych, formuł i widoków danych. Każda kolumna ma nazwę, a dane można grupować i organizować przeciągając nazwy pól w różne miejsca raportu.

Studium przypadku: życie po pojawieniu się tabel przestawnych

Załóżmy, że zmęczyła nas ciężka praca polegająca na przerabianiu raportów za każdym razem, gdy menedżer zażyczy sobie zmiany. Mamy szczęście: raport z poprzedniego studium przypadku można wykonać za pomocą tabeli przestawnej. Excel oferuje nam 10 miniatur zalecanych tabel przestawnych, które ułatwią nam zadanie. Wykonamy poniższe kroki:

Klikamy zakładkę Insert (Wstawianie) na wstążce. Klikamy Recommended PivotTables (Polecane tabele przestawne). Pierwszym zalecanym elementem jest Revenue By Region (patrz rysunek I-4).

Rysunek I-4 Pierwsza zalecana tabela przestawna najbardziej przypomina docelowy raport.

Klikamy OK, aby zaakceptować pierwszą tabelę przestawną. W panelu PivotTable Fields (Pola tabeli przestawnej) przeciągamy pole Product do obszaru Columns (Kolumny) (patrz rysunek I-5).

Rysunek I-5 Aby sfinalizować raport, przeciągnijmy nagłówek Product do obszaru Columns.

Po czterech kliknięciach myszą uzyskaliśmy raport widoczny na rysunku I-6.

Rysunek I-6 Ten raport można utworzyć za pomocą czterech kliknięć myszą.

Ponadto, gdy menedżer wróci do nas z podobną prośbą, jak we wcześniejszym studium przypadku, do tabeli przestawnej można z łatwością wprowadzić zmiany. Oto krótkie omówienie zmian, jakie nauczymy się wprowadzać w następnych rozdziałach:

Czy można umieścić produkty pionowo wzdłuż krawędzi, a regiony poziomo na górze? (Ta zmiana zajmie nam 10 sekund: wystarczy przeciągnąć nagłówek Product do obszaru Rows (Wiersze), a nagłówek Region do obszaru Columns). Czy mogę uzyskać taki sam raport, lecz tylko dla klientów z branży przemysłowej? (15 sekund: wybieramy Insert Slicer (Wstaw fragmentator), Sector; klikamy OK; klikamy Manufacturing). Czy mogę zobaczyć zyski zamiast przychodów? (10 sekund: wystarczy usunąć zaznaczenie pola obok Revenue i zaznaczyć pole obok Profit). Czy można skopiować ten raport dla każdego z klientów? (30 sekund: przenieśmy pole Customer do obszaru Filter (Filtry), otwórzmy listę obok przycisku Options, wybierzmy Show Report Filter Pages (Pokaż strony filtru raportu), kliknijmy OK).

Tworzenie tabeli przestawnej z użyciem sztucznej inteligencji

Nowe narzędzie Analyze Data analizuje zbiory danych z wykorzystaniem sztucznej inteligencji. Możemy wprowadzić pytanie w języku naturalnym, a Excel na jego podstawie utworzy tabelę przestawną.

Mając zaznaczoną jedną komórkę w naszym zbiorze danych, wybieramy polecenie Analyze Data dostępne po prawej stronie zakładki Home (Narzędzia główne). Pojawi się okno Analyze Data zawierające kilka proponowanych analiz. W polu Ask a Question widocznym w górnej części okna wpisz Revenue by Product and Region as Table i naciśnij Enter.

Excel narysuje miniaturę tego raportu. Kliknij +Insert PivotTable (Wstaw tabelę przestawną) na dole tej miniatury.

Rysunek I-7 Nieco więcej pisania, ale proces tworzenia jest znacznie prostszy.

Uwaga Program Microsoft Excel ulega nieustannym ulepszeniom i modyfikacjom, które są szczególnie dostrzegalne dla subskrybentów Microsoft 365, gdyż nowości są dodawane sukcesywnie, bez oczekiwania na kolejne "duże" wydanie. Niektóre funkcje są dostępne jedynie w tej wersji programu. Oznacza to, że zrzuty ekranowe prezentowane w książce, a także nazwy poleceń, okien dialogowych, paneli lub ich rozmieszczenie mogą ulec zmianie pomiędzy czasem publikacji a chwilą, gdy Czytelnik będzie czytał tę książkę. Jednak zasadnicza treść pozostaje w mocy, a takie zmiany są zasadniczo kosmetyczne i nie powinny wpłynąć na możliwość wykonania proponowanych ćwiczeń.

Dla kogo jest ta książka

Ta książka zawiera wystarczająco kompleksowe informacje dla doświadczonych analityków, a także zwykłych użytkowników programu Excel.

Zakładamy, że czytelnicy bez przeszkód poruszają się w środowisku programu Excel oraz że dysponują dużymi zbiorami danych, które chcą podsumować.

Organizacja książki

Większość zawartości tej książki dotyczy funkcji tabel przestawnych, które można obsłużyć za pomocą interfejsu użytkownika programu Excel. Rozdział 10., "Odblokowywanie funkcji za pomocą modelu danych i Power Pivot" wykorzystuje okno Power Pivot. Rozdział 13., "Tworzenie tabel przestawnych za pomocą VBA lub TypeScript" opisuje tworzenie tabel przestawnych w potężnym języku makr programu Excel, czyli VBA. Każdy kto zna podstawy przygotowania danych, kopiowania, wklejania oraz wpisywania prostych formuł, nie powinien mieć problemów ze zrozumieniem koncepcji opisanych w tej książce.

Dodatkowa zawartość

Przykładowe pliki zawierają wszystkie zbiory danych wykorzystane podczas pisania tej książki. Dzięki temu można przećwiczyć koncepcje przedstawione w tej książce. Przykładowe pliki są dostępne na stronie:

https://www.microsoftpressstore.com/store/microsoft-excel-pivot-table-data-crunching-office-2021-9780137521838

Wymagania systemowe

Aby utworzyć i uruchomić przykłady zaprezentowane w tej książce, potrzebne jest następujące oprogramowanie i sprzęt:

Microsoft Excel na komputerze z systemem Windows (tak, Excel działa na iPadzie i na tablecie z Androidem, ale żadna z tych wersji jeszcze długo nie będzie wspierać tworzenia tabel przestawnych). Użytkownicy programu Excel na Macach mogą korzystać z podstawowych koncepcji tabel przestawnych. Funkcje Power Query i Power Pivot nie będą działać na komputerach Mac. Użytkownicy programu Excel Online będą w stanie utworzyć większość z tabel przestawnych przedstawionych w tej książce, ale z mocno ograniczonym formatowaniem.

Errata, aktualizacje i wsparcie dla książki

Dołożyliśmy wszelkich starań, aby zagwarantować wysoką jakość tej książki i towarzyszących jej treści. Aktualizacje do tej książki - w postaci listy przesłanych poprawek - są dostępne na poniższej stronie:

https:// MicrosoftPressStore.com/Excel365pivotdata/errata

Jeśli ktoś znajdzie błąd, który nie został jeszcze opublikowany, zapraszamy do przesłania go na tej samej stronie.

Jeśli ktoś potrzebuje dodatkowej pomocy, zapraszamy do odwiedzenia strony:

MicrosoftPressStore.com/Support

Informujemy, że pod powyższym adresem nie można uzyskać wsparcia dla oprogramowania i sprzętu firmy Microsoft. Pomoc związaną z oprogramowaniem i sprzętem firmy Microsoft można uzyskać pod adresem http://support.microsoft.com.

Pozostańmy w kontakcie

Nie traćmy kontaktu! Jesteśmy na Twitterze:

https://twitter.com/MicrosoftPress

https://twitter.com/MrExcel

Rozdział 1

Podstawy tabel przestawnych

Zagadnienia omawiane w tym rozdziale: Dlaczego należy używać tabel przestawnych Kiedy należy używać tabel przestawnych Anatomia tabeli przestawnej Co się dzieje za kulisami tabel przestawnych Zgodność wsteczna tabel przestawnych

Wyobraźmy sobie, że Excel jest ogromną skrzynką zawierającą różnorodne narzędzia. Tabela przestawna jest zasadniczo jednym z narzędzi z przybornika programu Excel. Gdybyśmy chcieli porównać tabelę przestawną z rzeczywistym fizycznym narzędziem, które można wziąć do ręki, na myśl przychodzi obiektyw zmiennoogniskowy aparatu.

Gdy spoglądamy przez obiektyw na jakiś przedmiot, widzimy go na różne sposoby. Po obróceniu aparatu widoczne są inne szczegóły obiektu. Sam obiekt się nie zmienia. Nie jest też połączony z obiektywem. Obiektyw jest po prostu narzędziem, za pomocą którego można uzyskać unikalny perspektywiczny podgląd zwykłego obiektu.

Wyobraźmy sobie, że tabela przestawna jest obiektywem zmiennoogniskowym, przez który spoglądamy na zbiór danych. Gdy spojrzymy na zbiór danych za pośrednictwem tabeli przestawnej, dojrzymy szczegóły, których mogliśmy wcześniej nie zauważyć. Możemy pomniejszyć, aby uzyskać widok podsumowania, lub powiększyć, aby przestudiować szczegóły jednej sekcji danych. Ponadto, za pomocą tabel przestawnych możemy przyjrzeć się danym z różnych perspektyw. Sam zbiór danych się nie zmienia i nie jest powiązany z tabelą przestawną. Tabela przestawna jest zwykłym narzędziem, za pomocą którego tworzymy unikalny perspektywiczny widok na podstawie swoich danych.

Tabela przestawna umożliwia tworzenie interaktywnego widoku na podstawie zbioru danych, zwanego raportem tabeli przestawnej. Za pomocą raportu tabeli przestawnej można szybko i łatwo skategoryzować dane w grupy, utworzyć sensowne podsumowanie wielkich zbiorów danych, a także wykonać różnorodne obliczenia w znacznie krótszym czasie, niż gdybyśmy musieli wykonywać te operacje ręcznie. Jednak prawdziwa potęga raportów tabel przestawnych kryje się w możliwości interaktywnego przeciągania pól w raporcie, dynamicznych zmianach perspektywy oraz przeliczania wartości sumarycznych w reakcji na zmiany w bieżącym widoku.

Dlaczego należy używać tabel przestawnych

Z zasady wszystkie działania wykonywane w programie Excel można podzielić na trzy kategorie:

Obliczanie danych Przekształcanie (formatowanie) danych Filtrowanie, aby zobaczyć określone części danych

Chociaż powyższe zadania możemy sobie ułatwić za pomocą wielu wbudowanych narzędzi i wzorów, użycie tabel przestawnych jest zwykle najszybszym i najwydajniejszym sposobem na obliczanie i formowanie danych. Spójrzmy na jeden prosty przykład potwierdzający tę regułę.

Podaliśmy swojemu menedżerowi pewne informacje dotyczące przychodów osiągniętych w poszczególnych miesiącach. Menedżer dodał do arkusza swoją uwagę i odesłał go mailem. Jak widać na rysunku 1-1, chciałby, abyśmy dodali wiersz prezentujący obciążenia w ujęciu miesięcznym.

Rysunek 1-1 Jak można by się spodziewać, menedżer zmienia swoje wymagania po otrzymaniu pierwszej wersji raportu.

Aby spełnić nowe wymagania, wykonujemy zapytanie w swoim starszym systemie, który dostarcza potrzebne dane. Jak zwykle dane są sformatowane w sposób, który przyprawia nas o ból zębów. Zamiast danych w rozbiciu na miesiące, starszy system zwraca szczegóły transakcji w podziale na dni, jak na rysunku 1-2.

Rysunek 1-2 Dane uzyskane ze starszego systemu są podzielone na dni, zamiast na miesiące.

Naszym wyzwaniem jest przeliczenie całkowitej sumy obciążeń w dolarach w każdym miesiącu i sformatowanie wyników w sposób pasujący do formatu oryginalnego raportu. Ostateczny raport powinien wyglądać jak na rysunku 1-3.

Rysunek 1-3 Naszym celem jest uzyskanie podsumowania w ujęciu miesięcznym i przestawienie danych do formatu poziomego.

Aby uzyskać ten wynik ręcznie, musielibyśmy posłużyć się sprytną formułą tablicową: =SUM(FILTER(New!$C$2:$C$2616,TEXT(New!$B$2:$B$2616,"MMM")=B2)).

Natomiast utworzenie takiego samego raportu za pomocą tabeli przestawnej wymaga tylko 8 kliknięć myszą:

Utworzenie raportu tabeli przestawnej: 5 kliknięć Zgrupowanie danych według miesięcy: 3 kliknięcia

Obydwie metody prowadzą do uzyskania identycznego podzbioru danych, który można wkleić do gotowego raportu, jak na rysunku 1-4.

Rysunek 1-4 Po dodaniu obciążeń do raportu można obliczyć przychód netto.

Dzięki wykorzystaniu tabeli przestawnej w powyższym zadaniu nie tylko zmniejszyliśmy liczbę działań o ponad połowę, ale także ograniczyliśmy możliwość popełnienia błędu. Ponadto, tabele przestawne umożliwiają szybkie i łatwe przekształcanie i formatowanie danych.

Ten przykład dowodzi, że korzystanie z tabel przestawnych nie polega jedynie na przeprowadzaniu obliczeń i wyliczaniu wartości sumarycznych na podstawie danych. Dzięki tabelom przestawnym wiele zadań możemy wykonać szybciej i lepiej, niż za pomocą konwencjonalnych funkcji i wzorów. Za pomocą tabel przestawnych możemy na przykład natychmiastowo przestawić wielkie grupy danych do ułożenia pionowego lub poziomego. Za ich pomocą można szybko znaleźć i policzyć unikalne wartości występujące w zbiorze danych. Ponadto, możemy też przygotować dane do utworzenia wykresów.

Podsumowując, tabele przestawne mogą znacznie zwiększyć naszą wydajność i ograniczyć błędy podczas wykonywania wielu zadań w programie Excel. Tabele przestawne nie rozwiążą naszych wszystkich problemów, ale jeśli poznamy chociaż podstawowe możliwości tego narzędzia, możemy wspiąć się na wyżyny analizy danych oraz produktywności.

Kiedy używać tabel przestawnych

Wielkie zbiory danych, ciągle zmieniające się spontaniczne żądania dotyczące danych, a także wielowarstwowe raporty mogą bez wątpienia zredukować naszą wydajność, jeśli wykonujemy je ręcznie. Podejmowanie się ręcznego wykonania jednego z tych zadań oznacza nie tylko dużą stratę czasu, ale także ryzyko popełnienia w analizie wielu błędów. Jak zatem zdecydować, że potrzebna jest nam tabela przestawna, zanim będzie na to za późno?

Ogólnie rzecz biorąc, tabela przestawna przyda się nam w każdej z poniższych sytuacji:

Dysponujemy ogromną ilością danych transakcyjnych, które coraz trudniej jest przeanalizować i utworzyć sensowne podsumowanie. Chcemy znaleźć relacje i pogrupować swoje dane. Musimy znaleźć listę unikalnych wartości dla jednego z pól danych. Musimy znaleźć trendy w danych w różnych okresach czasu. Spodziewamy się częstych zmian w wymaganiach dotyczących analizy danych. Musimy obliczyć sumy częściowe, które często muszą uwzględniać nowe dane. Musimy przeorganizować dane do formatu ułatwiającego utworzenie wykresu.

Anatomia tabeli przestawnej

Ponieważ o elastyczności, a zarazem o ostatecznej funkcjonalności tabeli przestawnej, decyduje jej anatomia, pełne zrozumienie tego narzędzia byłoby trudne bez zrozumienia jego podstawowej struktury.

Tabela przestawna składa się z następujących czterech obszarów:

Obszar wartości Obszar wierszy Obszar kolumn Obszar filtrów

Dane umieszczone w tych obszarach definiują zarówno użyteczność, jak i wygląd tabeli przestawnej. Z tworzeniem tabel przestawnych zapoznamy się w następnym rozdziale, natomiast w kolejnym podrozdziale przygotujemy się do tego, przyglądając się bliżej wymienionym wyżej czterem obszarom, a także ich funkcjom.

Obszar wartości

Obszar wartości jest przedstawiony na rysunku 1-5. Jest to duży prostokątny obszar poniżej i z prawej strony nagłówków. W tym przykładzie obszar wartości zawiera sumę pola Revenue.

W obszarze wartości przeprowadzane są obliczenia. Ten obszar musi obejmować co najmniej jedno pole i wykonywać co najmniej jedną operację obliczeniową na wartościach tego pola. W tym obszarze umieszczamy pola danych, które chcemy zmierzyć lub na których chcemy dokonać obliczeń. Obszar wartości może zawierać sumę przychodów, całkowitą liczbę jednostek oraz średnią cenę.

Rysunek 1-5 Sercem tabeli przestawnej jest obszar wartości. Ten obszar zwykle zawiera sumę wartości co najmniej jednego pola liczbowego.

W obszarze wartości to samo pole możemy umieścić dwukrotnie, jednak w tym przypadku za każdym razem musimy wykonać inne obliczenia. Na przykład kierownik działu reklamy może nas poprosić o obliczenie sumy przychodów, procentu z całości przychodu oraz kolejności i sumy bieżącej przychodów.

Obszar wierszy

Obszar wierszy, widoczny na rysunku 1-6, składa się z nagłówków znajdujących się z lewej strony tabeli przestawnej.

Po dodaniu pola do obszaru wierszy, z lewej strony tabeli przestawnej zostaną wyświetlone jedna pod drugą unikalne wartości znajdujące się w tym polu. Obszar wierszy zwykle zawiera co najmniej jedno pole, chociaż może też być pusty. We wcześniejszym przykładzie przytoczonym w tym rozdziale, w którym musieliśmy utworzyć jednowierszowy raport o obciążeniach, obszar wierszy nie zawierał żadnych pól.

W tym obszarze można upuścić pola danych, na podstawie których chcemy dokonać grupowania i kategoryzacji - na przykład produkty, nazwy i lokalizacje.

Rysunek 1-6 Nagłówki z lewej strony tabeli przestawnej wchodzą w skład obszaru wierszy.

Obszar kolumn

Obszar kolumn składa się z nagłówków, znajdujących się w górnej części tabeli przestawnej. W tabeli przestawnej przedstawionej na rysunku 1-7 w obszarze kolumn znajduje się pole Month.

Po upuszczeniu pól w obszarze kolumn dane zostaną rozmieszczone w kolumnach. Obszar kolumn doskonale nadaje się do przedstawiania trendu w czasie. W tym obszarze warto umieszczać pola danych, które pokazują jakiś trend, lub które chcielibyśmy umieścić obok siebie - na przykład miesiące, okresy i lata.

Rysunek 1-7 Obszar kolumn znajduje się w górnej części tabeli. W tym przykładzie zawiera listę unikalnych miesięcy występujących w naszym zbiorze danych.

Obszar filtrów

Obszar filtrów jest opcjonalnym zbiorem jednej lub kilku list rozwijalnych, znajdujących się w górnej części tabeli przestawnej. Na rysunku 1-8 obszar filtrów zawiera pole Region, a tabela wyświetla wszystkie regiony.

Rysunek 1-8 Pola filtrów doskonale nadają się do szybkiego filtrowania raportów. Lista Region w komórce B1 umożliwia wydrukowanie tego raportu dla menedżera zajmującego się konkretnym regionem.

Gdy upuścimy pola w obszarze filtrów, będziemy mogli filtrować dane znajdujące się w tych polach. Obszar filtrów jest opcjonalny i przydaje się, gdy chcemy dynamicznie przefiltrować wyniki. W obszarze tym można upuszczać pola, które chcemy wyizolować i podkreślić - na przykład regiony, rodzaje działalności biznesowej i pracownicy.

Niektóre z najpopularniejszych funkcji obszaru filtrów zostały zastąpione przez fragmentatory i osie czasu. Z technicznego punktu widzenia fragmentator nie jest częścią tabeli przestawnej. Jest to filtr zewnętrzny, w ramach którego jeden fragmentator może zostać podłączony do wielu tabel przestawnych. Przydaje się to zwłaszcza podczas tworzenia pulpitów. Więcej szczegółów na ten temat znajdziemy w podrozdziale "Filtrowanie z użyciem fragmentatorów i osi czasu" w rozdziale 4.

Za kulisami tabel przestawnych

Warto pamiętać, że użycie tabel przestawnych wiąże się z pewnymi niedogodnościami związanymi z wielkością pliku i wykorzystaniem pamięci w systemie. Aby zrozumieć, co to oznacza, sprawdźmy, co kryje się za kulisami tworzenia tabeli przestawnej.

Gdy inicjujemy tworzenie raportu tabeli przestawnej, program Excel wykonuje migawkę zbioru danych i zapisuje ją w pamięci podręcznej, czyli w specjalnym podsystemie pamięci, w którym przechowywany jest duplikat źródła danych, umożliwiający szybki dostęp do danych. Chociaż ta pamięć podręczna nie jest fizycznym obiektem, który można by zobaczyć, można ją porównać do kontenera przechowującego migawkę źródła danych.

Dzięki użyciu pamięci podręcznej zamiast oryginalnego źródła danych, możemy odnieść korzyści z optymalizacji. Wszelkie zmiany wprowadzone w raporcie tabeli przestawnej, takie jak zmiana kolejności pól, dodanie nowych pól lub ukrycie elementów, odbywają się szybko z minimalnym obciążeniem.

Ważne Wszystkie zmiany wprowadzone w źródle danych nie zostaną uwzględnione w raporcie tabeli przestawnej, dopóki nie wykonamy kolejnej migawki źródła danych lub nie "odświeżymy" pamięci podręcznej tabeli przestawnej. Odświeżanie jest proste i polega na kliknięciu tabeli przestawnej prawym przyciskiem myszy, a następnie na wybraniu opcji Refresh (Odśwież). Można też kliknąć duży przycisk Refresh (Odśwież) znajdujący się na zakładce Options.

Wsteczna zgodność tabel przestawnych

W prawie każdej nowej wersji programu Excel pojawiają się funkcje, które nie działają w poprzednich wersjach programu.

Formatowanie zastosowane do punktu danych w Microsoft 365 nie będzie działać w programie Excel 2016 lub wcześniejszym. Osie czasu utworzone w programie Excel 2013 nie będą działać w programie Excel 2010 lub wcześniejszym. Fragmentatory utworzone w programie Excel 2010 lub nowszym nie będą działać w programie Excel 2007 lub wcześniejszym.

Limity dla tabeli przestawnej opartej na pamięci podręcznej pozostają niezmienione od czasu wydania programu Excel 2007. Jeśli zachodzi potrzeba przekroczenia tych limitów, należy zapoznać się z rozdziałem 10., "Odblokowywanie funkcji za pomocą modelu danych i Power Pivot".

Tabela 1-1 Ograniczenia dotyczące tabel przestawnych

Kategoria

Pliki .xlsx

Liczba pól w wierszach

1 048 576 (może być ograniczona przez dostępną pamięć)

Liczba pól w kolumnach

16 384

Liczba pól stron

16 384

Liczba pól danych

16 384

Liczba znaków w jednej komórce

32 768

Liczba unikalnych elementów w jednym polu tabeli przestawnej

1 048 576 (może być ograniczona przez dostępną pamięć)

Liczba elementów obliczanych

Ograniczona przez dostępną pamięć

Liczba raportów tabeli przestawnej w jednym arkuszu roboczym

Ograniczona przez dostępną pamięć

Uwagi dotyczące zgodności

W programie Excel dostępne jest narzędzie umożliwiające identyfikację wszelkich problemów związanych ze zgodnością wsteczną. Aby sprawdzić zgodność, należy wybrać kolejno File (Plik), Info (Informacje), Check For Issues (Wyszukaj problemy), Check Compatibility (Sprawdź zgodność), zgodnie z rysunkiem 1-9.

Rysunek 1-9 Narzędzie Check Compatibility znajduje się na liście Check For Issues.

W oknie dialogowym Compatibility Checker (Sprawdzania zgodności) należy wybrać z listy Select Versions To Show (Wybierz wersje do pokazania) wersje programu Excel, z których mogą korzystać nasi współpracownicy. W ten sposób wyświetlimy w oknie dialogowym problemy związane z tabelami przestawnymi (patrz rysunek 1-10). Musimy rozwiązać wszystkie problemy oznaczone etykietą "Significant Loss Of Functionality" (Znacząca utrata funkcjonalności). Elementy oznaczone etykietą "Minor Loss Of Fidelity" (Nieznaczna utrata wierności) dotyczą problemów z formatowaniem.

Rysunek 1-10 Zanim zapiszemy plik dla wcześniejszej wersji programu Excel, narzędzie Compatibility Checker zgłosi wszystkie problemy ze zgodnością.

Kolejne kroki

W następnym rozdziale dowiemy się, jak przygotować dane do utworzenia tabeli przestawnej. Rozdział 2., "Tworzenie prostej tabeli przestawnej" omawia również tworzenie pierwszego raportu tabeli przestawnej za pomocą okna dialogowego Create PivotTable (Tworzenie tabeli przestawnej).