Excel — Zestawy w Tabelach Przestawnych (Excel 2013) — porada #371

utworzone przez Adam Kopeć | Mar 31, 2019 | Excel, Excel 2013 | 0 komentarzy

W dzisiejszym poście nauczymy się jak dodać Zestawy w tabelach przestawnych. Zestawy to nic innego jak zbiory elementów, które później łatwo możemy dodawać do tabel przestawnych. W naszym przykładzie stworzymy zestawy zawierające tylko owoce lub tylko warzywa (rys. nr 1).

Zagadnienie to omówimy sobie na podstawie przykładowych danych z rysunku nr 2.

Przede wszystkim musimy sobie stworzyć tabelę przestawną, czyli zaznaczamy pojedynczą komórkę w tabeli (lub cały zakres danych) i wybieramy polecenie Tabela przestawna z karty Wstawianie (rys. nr 3).

Otworzy nam się okno Tworzenie tabeli przestawnej, zaznaczamy, że chcemy stworzyć tabelę przestawną w istniejącym arkuszu (punkt 1) i wskazujemy komórkę (punkt 2), gdzie ma zostać wstawiona. Najważniejsza sprawa tutaj, żeby mieć dostęp do Zestawów, musimy mieć przynajmniej Excela 2013, żeby w Oknie Tworzenie tabeli przestawnej mieć możliwość zaznaczenia checboxa Dodaj te dane do modelu danych (punkt nr 3 na rysunku nr 4). Polecenie to jest związane z dodatkiem do Excela – Power Pivot, skorzystamy tutaj z jego elementów. Parametry nowej tabeli przestawnej zatwierdzamy przyciskiem OK.

Otworzy nam się okno Pola tabeli przestawnej, gdzie przeciągamy pole Produkt do obszaru etykiet wierszy i pole Wartość do obszaru podsumowań wartości. Otrzymamy tabelę przestawną przedstawioną na rysunku nr 5.

Aby mieć bardziej czytelne dane, sformatujemy sobie kolumnę Suma wartości, naciskamy prawym przyciskiem myszy na dowolną wartość z tej kolumny i za pomocą polecenia Format liczby z podręcznego menu, otworzymy okno Formatowanie komórek. Wybieramy formatowanie walutowe i zatwierdzamy przyciskiem OK (rys. nr 6).

Otrzymamy dane sformatowane wyrażone w złotówkach. W naszych danych mamy zarówno owoce jak i warzywa, a chcielibyśmy je mieć w osobnych zestawach. Musimy rozwinąć polecenie Pola, elementy i zestawy(punkt 2 na rysunku nr 7) z karty Analiza (punkt 1 na rysunku nr 7), a następnie wybrać polecenie Utwórz zestaw na podstawie elementów z wiersza (punkt 3). Ponieważ jesteśmy w modelu danych (zaznaczyliśmy ten checkbox przy tworzeniu tabeli przestawnej) nie będziemy mieć dostępu do poleceń dotyczących pól i elementów. Będziemy mieć natomiast dostęp do zestawów. Możemy utworzyć zestawy z elementów w wierszach które potem w tabeli przestawnej trafią do obszaru etykiet wiersza. Analogicznie zadziała polecenie dla kolumn, jeśli będziemy mieć jakieś elementy w tabeli przestawnej w obszarze etykiet kolumn.

W poleceniu Utwórz zestaw na podstawie elementów z wiersza, tworzymy zestaw na podstawie danych, gdzie mamy tylko jedno pole wstawione do obszaru etykiet wierszy, bo przy większej ilości pól staje się to dużo bardziej skomplikowane. Otworzy nam się okno Nowy zestaw (rys. nr 8). W pierwszym kroku musimy nazwać nasz zestaw – w naszym przykładzie zestaw będzie się nazywał Owoce (punkt nr 1 na rysunku poniżej). Następnie musimy zająć się produktami w tym zestawie. Przy każdym produkcie jest lista rozwijana ze wszystkimi elementami dostępnymi dla pola Produkt. Oprócz wymienionych elementów zawierających się w polu Produkt mamy Wszystko, czyli podsumowanie po wszystkich wartościach. Z listy usuwamy wszystkie warzywa za pomocą przycisku Usuń wiersz (punkt 2).

Zostawiamy wiersz Wszystko, ponieważ zawiera on cenę całkowitą. Kiedy zatwierdzimy Nowy zestaw przyciskiem OK otrzymamy tabelę przestawną zawierającą tylko owoce, ale suma końcowa nam się nie zmieni (będzie zawierać ceny owoców i warzyw, mimo, że wyświetlać się będą tylko owoce) – rys. nr 9.

Zostawiony w zestawie element Wszystko zawsze będzie pokazywać cenę całkowitą (razem warzyw i owoców), więc usuniemy go sobie, żeby nie przeszkadzał nam w obliczeniach. Wybieramy polecenie Zarządzaj zestawami z karty Analiza (rys. nr 10).

Otworzy nam się okno Menedżer zestawów, w którym zaznaczamy interesujący nas zestaw i klikamy przycisk Edytuj (oznaczony strzałką na rysunku nr 11).

Otworzy się okno Modyfikuj zestaw, gdzie musimy usnąć wiersz Wszystko, wprowadzone zmiany zatwierdzamy przyciskiem OK (rys. nr 12)

Otrzymamy tabelę przestawną z samymi owocami, co jest ważne wybraliśmy tutaj zestaw Owoce a nie produkt, więc Excel sam podstawi nam ten zestaw do obszaru etykiet wierszy (rys. nr 13).

Analogicznie możemy stworzyć zestaw dla warzyw. Możemy to zrobić na tej samej tabeli przestawnej, zaznaczamy dowolną komórkę w tabeli, wybieramy polecenie Utwórz zestaw na podstawie elementów z wiersza z karty Analiza, ale wyświetli nam się komunikat, Excel nie pozwoli nam stworzyć zestawu na innym zestawie. Musimy najpierw usunąć istniejący zestaw z tabeli przestawnej (rys. nr 14).

Najłatwiej skopiować sobie tabele przestawną i w oknie Pola tabeli przestawnej odznaczyć Zestaw Owoce2, a następnie przeciągnąć pole Produkt do obszaru etykiet wierszy (jak przy tworzeniu tabeli przestawnej od nowa). Teraz na podstawie dowolnej komórki w kolumnie Produkt możemy sobie stworzyć nowy Zestaw. Kolejno karta Analiza, potem Pola, elementy i zestawy, a następnie Utwórz zestaw na podstawie elementów wiersza (jak na rysunku nr 7)

W oknie Nowy zestaw usuwamy wszystkie owoce z listy i wiersz Wszystko analogicznie jak na rysunku nr 8. Zmieniamy nazwę zestawu na Warzywa2 i zatwierdzamy przyciskiem OK. Otrzymamy tabelę przestawną z zestawem Warzywa2 (rys. nr 15).

Mamy teraz 2 osobne tabele przestawne, górna z zestawem Owoce2 i dolna z zestawem Warzywa2.

Załóżmy że chcemy tym razem podsumować dane w tabeli dla województw. Skopiujemy tabelę przestawną z owocami i wyłączymy zestaw owoce w oknie Pola tabeli przestawnej. Następnie przeciągniemy pole Województwa do obszaru etykiet wierszy to otrzymamy tabelę przedstawioną na rysunku nr 16.

Teraz chcemy podsumować dane dla województw po sprzedaży owoców. Dzięki temu że mamy stworzony zestaw Owoce2, to nie musimy przeciągać pola Produkt a następnie przefiltrowywać tabeli po tych produktach, które nie są owocami, wystarczy że przeciągniemy sobie do obszaru etykiet wierszy pole Owoce2. Wtedy automatycznie powstanie tabela przestawna przedstawiająca sprzedaż owoców w danych województwach (rys. nr 17).

Analogicznie dla podsumowania po województwach pod kątem sprzedaży warzyw. Odznaczamy zestaw Owoce2 i zaznaczamy Warzywa2, Excel automatycznie stworzy nam odpowiednią tabelę (rys. nr 18).

Podsumowując, jeśli często działamy na tabelach przestawnych, możemy oszczędzić dużo czasu. Wystarczy stworzyć zestawy danych, których później możemy używać do podsumowywania danych. Tak jak wspominałem wcześniej możemy tworzyć zestawy dla 2 pól z obszaru etykiet wierszy, ale wtedy w oknie Nowy zestaw mamy bardzo dużo elementów (rys. nr 19)

Na koniec ciekawostka, jeśli zależy nam na innej kolejności elementów w danych zestawach to w oknie Modyfikuj zestaw możemy zmieniać tą kolejność za pomocą strzałek oznaczonych na rysunku nr 20.

Przedsprzedaż Mistrz Excela II wydanie

Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą 😀)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.

Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".

Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach 😉 i z dużą ilością zdjęć.

Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.

Aktualnie w przedsprzedaży możesz mieć Mistrza Excela w obniżonej cenie:
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.

W ramach przedsprzedaży możesz mieć całą książkę za jedyne 30 zł (łącznie z darmową wysyłką listem ekonomicznym), a jeśli zależy Ci na dedykacji wystarczy, że dołożysz 10 zł.