Excel i Microsoft 365 Przetwarzanie danych za pomocą tabel przestawnych. Zawiera omówienie tabel dynamicznych, Power Query i funkcji Copilot - Bill Jelen

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

Authorized Polish translation of the English language edition entitled Microsoft Excel Pivot Table Data Crunching: Including Dynamic Arrays, Power Query, and Copilot, by Bill Jelen, ISBN: 978-0-13-540879-7

Copyright ? 2025 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 ? 2025

Autoryzowany przekład z wydania w języku angielskim, zatytułowanego:Microsoft Excel Pivot Table Data Crunching: Including Dynamic Arrays, Power Query, and Copilot, by Bill Jelen, ISBN: 978-0-13-540879-7

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-580-3 (druk), 978-83-7541-581-0 (ebook)

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

Pracę tę poświęcam Kim Sekas i Stacie Fuss z mojej porannej grupy ćwiczeniowej Supernatural VR. Za każdym razem, gdy zaczynałem jakieś nieistotne analizy problemów Excela, przypominały mi, że mam jakieś rozdziały do przejrzenia i zaktualizowania.

- 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

Formatowanie danych źródłowych przed utworzeniem 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 Analyze Data, Copilot i Recommended PivotTable

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

Wyświetlanie szczegółów dla dowolnej komórki w obszarze wartości

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

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: Dystrybuowanie wykresu przestawnego jako obrazu

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

Wykorzystywanie Power Query do uzyskiwania i przekształcania danych

Podstawy funkcji Power Query

Zastosowane kroki

Odświeżanie danych Power Query

Typy połączeń Power Query

Jeszcze jeden przykład Power Query

Kolejne kroki

Rozdział 8

Udostępnianie tablic kontrolnych 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ł kostek z modelem danych lub danymi OLAP

Przekształcanie tabeli przestawnej do formuł kostek

Wprowadzenie do technologii OLAP

Łączenie się z kostką OLAP

Struktura kostki OLAP

Ograniczenia tabel przestawnych OLAP

Tworzenie kostki offline

Wychodzenie poza formę tabeli przestawnej za pomocą funkcji kostek

Zapoznanie z funkcjami kostek

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 XLOOKUP modelem danych

Odblokowywanie ukrytych funkcji za pomocą modelu danych

Obliczanie unikatowych wartości w tabeli przestawnej

Uwzględnianie odfiltrowanych elementów w sumach

Obliczanie 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

Tworzenie spłaszczonej tabeli przestawnej

Obchodzenie ograniczeń modelu danych

Inne zalety 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

Odblokowywanie skoroszytów pobranych z sieci

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

Wersje programu Excel i VBA

Tworzenie tabeli przestawnej 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

Drugie podejście do tabel przestawnych: 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 jako 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 danych 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

Zebranie wszystkiego razem

Tworzenie tabel przestawnych w Excel Online za pomocą języka TypeScript

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: Określanie zachowania 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: Uzyskanie "rozszerzonej funkcji 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

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ą formuł, języka Python lub Power Query

Tworzenie raportów krzyżowych przy użyciu funkcji GROUPBY lub PIVOTBY

Składnia funkcji GROUPBY

Tworzenie prostego podsumowania za pomocą GROUPBY

Sortowanie wyników funkcji GROUPBY i PIVOTBY

Przenoszenie podsumowań na górę raportu

Wyświetlanie sum częściowych dla wielu pól wierszy przy użyciu funkcji GROUPBY

Przechodzenie od funkcji GROUPBY do PIVOTBY

Zalety i wady funkcji GROUPBY i PIVOTBY

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

Zalety i wady używania Power Query?

Tworzenie raportów krzyżowych przy użyciu języka Python

Wprowadzanie danych do ramki danych Pythona

Używanie funkcji pivot_table języka Python

Grupowanie dat w tabelach przestawnych Pythona

Zalety i wady używania Pythona

Kolejne kroki

Rozdział 18

Używanie sztucznej inteligencji i funkcji Copilot do budowania tabel przestawnych

Dodawanie kolumny do danych za pomocą AI

Instalowanie i konfigurowanie dodatku Excel Labs

Tworzenie zapytania przy użyciu formuły

Unikanie wielokrotnego obliczania każdej formuły

Wykorzystanie funkcji LABS.GENERATIVEAI do pobierania odpowiedzi

Używanie funkcji Analyze Data do znajdowania trendów i stawiania pytań

Zadawanie pytań Analyze Data

Używanie funkcji Copilot do tworzenia tabel lub wykresów przestawnych

Używanie funkcji Copilot do generowania kolumn obliczanych

Używanie funkcji Copilot do wyróżniania, sortowania lub filtrowania

Używanie zaawansowanych funkcji Copilot z językiem Python

Złączenie danych z dwóch tabel i utworzenie tabeli przestawnej

Tworzenie wykresu typu chmura słów

Utworzenie wykresu dla rzutów w meczu koszykówki

Wykonanie analizy sentymentu dla tekstu

Rozwiązanie problemu plecakowego

Ogólne wrażenia na temat funkcji Copilot w Excelu

Używanie funkcji Copilot w OneDrive do analizowania sprawozdań finansowych w programie Excel

Używanie AI do wyszukiwania i podsumowywania filmów na temat Excela w YouTube

Używanie funkcji Copilot do pisania kodu w językach VBA, Python, RegEx albo M

Tworzenie tabeli przestawnej przy użyciu kodu VBA generowanego przez Copilot

Tworzenie tabeli przestawnej przy użyciu kodu Pythona generowanego przez Copilot

Tworzenie kodu M dla Power Query przy użyciu Copilota

Generowanie wyrażeń regularnych za pomocą Copilota

Dla rozrywki: Używanie funkcji Copilot do pisania poezji lub piosenek o Excelu

Kolejne kroki

Rozdział 19

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 przestawienia komórek z rozdzielanymi danymi do nowych wierszy

Konkluzja

Posłowie

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. Dziękuję Mike'owi Alexandrowi za to, że współtworzył ze mną pierwsze cztery wydania tej książki. Wyrazy wdzięczności należą się też Suatowi Ozgurowi - znanemu jako Batcoder - za recenzję mojego kodu z rozdziału 13. 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 2600 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ą formuł, języka Python lub Power Query" oraz 19., "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 Excel

Firma Microsoft wprowadziła ostatnio dwie nowe funkcje tablicowe, które ułatwiają tworzenie czegoś w rodzaju tabeli przestawnej, ale przy użyciu formuł: PIVOTBY oraz GROUPBY*. Główną przewagą formuł nad tabelami przestawnymi jest to, że formuły są przeliczane automatycznie, gdy nastąpi zmiana danych leżących w tle. Często się zdarza, że tworzymy raporty lub tablice kontrolne dla jakiegoś menedżera trzy poziomy wyżej w hierarchii i nie możemy po prostu polegać na tym, że będzie on(a) pamiętać o kliknięciu przycisku Refresh w menu tabeli przestawnej (a nawet, że w ogóle wie o istnieniu takiego przycisku). W takich sytuacjach użycie funkcji PIVOTBY gwarantuje, że nasza tablica kontrolna jest aktualna.

Od wielu lat dostępna jest funkcja Analyze Data, która często umożliwia utworzenie tabeli przestawnej, po prostu zadając pytanie dotyczące naszych danych. Funkcjonalność Analyze Data jest przetestowana i niezawodna w tym sensie, że zawsze zwraca wiarygodne wyniki.

Wraz z tym wydaniem książki pojawiła się zupełnie nowa funkcjonalność - Copilot. Uwolnienie jej całego potencjału wymaga dodatkowych opłat. Często pozwala uzyskać raport równie dokładny, jak ten tworzony przez Analyze Data. Czasami jednak tworzy raporty niedokładne lub wręcz błędne. Ale co najmniej, skoro Copilot wiąże się z dodatkowym wydatkiem, powinniśmy bardziej doceniać świetną funkcję Analyze Data, która jest dostępna bez dodatkowych kosztów w ramach subskrypcji Microsoft 365.

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 naprzó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 0-1).

Rysunek 0-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 0-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 trzema naciśnięciami klawiszy.

Rysunek 0-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 0-3.

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

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.

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 produkcyjnej? 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. Wykonaj poniższe kroki:

Kliknij zakładkę Insert (Wstawianie) na wstążce. Kliknij Recommended PivotTables (Polecane tabele przestawne). Przewiń listę sugerowanych tabel, szukając takiej, która zawiera Revenue, Product oraz Region. Być może trzeba będzie kliknąć See All 10 Results (Pokaż wszystkie 10 wyników) poniżej czwartej propozycji. Jak widać na rysunku 0-4, można będzie wybrać wariant Revenue By Region And Product albo Revenue By Product And Region. Każdy z nich będzie dobry. Kliknij przycisk + Existing Sheet (Dodaj do istniejącego arkusza).

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

Excel poprosi o wskazanie początkowej komórki tabeli przestawnej. Dobrą zasadą jest pozostawienie przynajmniej jednej pustej kolumny na prawo od źródłowych danych, zatem jako punkt początkowy wybierz komórkę K2. Początkowa tabela przestawna będzie zawierać Region i Product w pierwszeej kolumnie. Naszym celem jest rozmieszczenie produktów w poprzek tabeli. W panelu PivotTable Fields (Pola tabeli przestawnej) przeciągamy pole Product z obszaru Rows (Wiersze) do obszaru Columns (Kolumny), jak na rysunku 0-5.

Po zaledwie kilku kliknięciach myszą uzyskaliśmy raport widoczny na rysunku 0-6.

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).

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

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

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 0-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. Rozdział 18, "Używanie sztucznej inteligencji i funkcji Copilot do budowania tabel przestawnych", opisuje używanie sztucznej inteligencji, a szczególnie funkcji Copilot, do tworzenia analiz. 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.

Aby uzyskać dostęp do tych plików i innych zasobów, należy odwiedzić stronę https://www.microsoftpressstore.com/store/microsoft-excel-pivot-table-data-crunching-including-9780135408797, po czym skorzystać z zakładki Downloads. Pliki te będą dostępne do 31 grudnia 2027 r.

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. Subskrypcja Microsoft 365 nie jest obowiązkowa, ale zalecana. Posiadacze "wieczystych" licencji programu Excel 2021 (i wcześniejszych) będą mogli wykonać większość, ale zdecydowanie nie wszystkie operacje pokazane w książce. W szczególności w tych wersjach oczywiście nie są dostępne nowsze funkcje programu. Dodatkowo subskrypcja Microsoft 365 zapewnia dostęp do funkcji Copilot (choć w wersji podstawowej z ograniczoną liczbą zapytań miesięcznie).

Ważne Użytkownicy wersji oprogramowania w języku polskim często żalą się na fakt, że nazwy funkcji pokazywane w różnorodnych tutorialach (angielskie) nie zgadzają się z tym, co oni znajdują w swoim programie. Dodatkowo niektóre funkcjonalności, na przykład Analyze Data, w ogóle nie są dostępne w tej wersji. Posiadacze subskrypcji mogą jednak prosto zmienić język interfejsu na angielski, pozostawiając jednak nietknięte ustawienia regionalne. W ten sposób mamy funkcję IF, a nie JEŻELI, VLOOKUP, a nie WYSZUKAJ.PIONOWO, a jednocześnie zgodny z polskimi standardami zapis dat lub znak dziesiętny. Z tego powodu tłumacze i redakcja polskiego wydania książki zdecydowanie poleca wykonanie takiej zmiany. Wymaga to jedynie otwarcia okna Opcje programu Excel i przejście do karty Język. Tam należy albo przenieść język angielski na pierwszą pozycję (jeśli jest już obecny), albo go najpierw doinstalować. Zajmuje to kilka sekund i odbywa się niemal w locie - wymaga jedynie zamknięcia i ponownego uruchomienia programu Excel.

Układ kompaktowy kontra tabelaryczny

W roku 2007 firma Microsoft zmieniła domyślny układ tabel przestawnych, wprowadzając coś, co nazywane jest układem kompaktowym. Z różnych powodów, które wyjaśnię w tym punkcie, nie jestem fanem tego układu i zawsze zmieniam ustawienia opcji programu Excel, aby moje tabele przestawne zawsze były tworzone w układzie tabelarycznym (Tabular). Podczas pracy z tą książką pomocne będzie, aby Czytelnik szybko zrozumiał kontrast pomiędzy tym, jak układ kompaktowy i tabelaryczny prezentują te same dane.

Na rysunku 0-8 kolumny N:Q pokazują tradycyjną tabelę przestawną w układzie tabelarycznym. Każde z trzech pól wierszy pojawia się w innej kolumnie zaopatrzonej w zrozumiałe nagłówki, takie jak Region, Product i Sector.

Dla kontrastu, kolumny S:T pokazują te same dane, ale w układzie kompaktowym. Zrozumiałe nagłówki z komórek N2:P2 zastąpił pojedynczy tytuł "Row Labels" w komórce S2. Wartości dla pól Region, Product i Sector są pomieszane ze sobą w kolumnie S, odróżniane jedynie różnymi poziomami wcięcia, dzięki czemu w ogóle widać, czy patrzymy na nazwę regionu, produktu, czy sektora.

Rysunek 0-8 Nagłówek Row Labels w komórce S2 jest wskazówką, że nasze tabele przestawne domyślnie tworzone są w układzie Compact.

Tym, co mnie irytuje w układzie kompaktowym, jest to, jak Excel pomieszał ze sobą regiony, produkty i sektory w kolumnie S. Każdy człowiek czytający ten raport może zauważyć zróżnicowanie dzięki różnym wcięciom. Jednak żadna funkcja wykonująca obliczenia nie będzie w stanie odróżnić tego, czy dane pole jest wcięte o osiem, czy o cztery spacje.

Z doświadczenia wiem, że w wielu sytuacjach będę przekształcał moje tabele przestawne w wartości statyczne, aby móc wykorzystać tę niezmienną wersję do innych celów. Te inne zastosowania niemal zawsze będą potrzebować, aby każde pole znalazło się w swojej własnej kolumnie.

Jeśli ktoś będzie wykonywać przykłady przedstawione w książce i nagle okaże się, że otrzyma tabelę przestawną w układzie kompaktowym, może mieć pewność, że liczby w jego tabeli i te pokazane w książce są takie same, jedynie inaczej sformatowane.

Każdą tabelę przestawną można przekształcić do innego układu, używając karty Design (Projektowanie) we wstążce. Wystarczy otworzyć listę rozwijaną Report Layout (Układ raportu) i wybrać pomiędzy opcją Compact a Tabular.

Jeśli ktoś zgadza się ze mną, że tabela przestawna pokazana na rysunku 0-8 w kolumnach N:Q jest lepsza od tej widocznej w kolumnach S:T, może wykonać poniższe kroki, aby zmienić domyślne ustawienia w swojej instalacji programu Excel.

Otwórz menu File (Plik) i wybierz Options (Opcje). W oknie dialogowym Excel Options (Opcje programu Excel) wybierz kategorię Data (Dane) w lewym pasku nawigacyjnym. Kliknij najwyższy przycisk Edit Default Layout (Edytuj układ domyślny) obok wpisu Make Changes To The Default Layout Of Pivot Tables (Wprowadź zmiany w domyślnym układzie tabel przestawnych). Otwórz listę rozwijaną Report Layout (Układ raportu) i wybierz Show in Tabular Form (Pokaż w formie tabelarycznej). Zaznacz pole wyboru obok wpisu Repeat All Item Labels (Powtórz wszystkie etykiety elementów). Kliknij przycisk PivotTable Options... (Opcje tabeli przestawnej) w prawym dolnym rogu okna dialogowego. Wyświetlone okno PivotTable Options zawiera pięć kart. Na karcie Layout & Format (Układ i formatowanie) kliknij w polu For Empty Cells Show (Dla pustych komórek pokaż) i wpisz zero (0). Kliknij OK trzykrotnie, aby zamknąć wszystkie otwarte okna dialogowe.

Teraz twoje tabele przestawne będą domyślnie tworzone w układzie widocznym po lewej stronie rysunku 0-8.

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://www.microsoftpressstore.com/store/microsoft-excel-pivot-table-data-crunching-including-9780135408797

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.

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 pierścienia obiektywu 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 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 punktów widzenia. 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 widok 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ę dynamiczną 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

Maksymalny limit

Liczba raportów PivotTable na arkusz

Ograniczona dostępną pamięcią

Unikatowe elementy na pole

1048576

Liczba pól wierszy lub kolumn w raporcie

Ograniczona dostępną pamięcią

Liczba filtrów w raporcie PivotTable

256 (może być ograniczona dostępną pamięcią)

Liczba pól wartości w raporcie PivotTable

256

Liczba formuł elementów obliczanych w raporcie PivotTable

Ograniczona dostępną pamięcią

Liczba filtrów w raporcie PivotChart

256 (może być ograniczona dostępną pamięcią)

Liczba pól wartości w raporcie PivotChart

256

Liczba formuł elementów obliczanych w raporcie PivotChart

Ograniczona dostępną pamięcią

Długość nazwy MDX dla elementu tabeli przestawnej

32767

Długość relacyjnego ciągu PivotTable

32767

Elementy wyświetlane w liście rozwijanej filtru

10000

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).