Wersja tabeli przestawnej Excel 2010 została wzbogacona o nową, super fajną funkcję - Slicers.
Fragmentator tabeli przestawnej umożliwia filtrowanie danych po wybraniu co najmniej jednej opcji w polu Fragmentator (jak pokazano poniżej).
W powyższym przykładzie Fragmentator to pomarańczowe pole po prawej stronie, a tabelę przestawną można łatwo filtrować, klikając przycisk regionu we fragmentatorze.
Zacznijmy.
Kliknij tutaj Pobierz przykładowe dane i podążaj dalej.
Wstawianie fragmentatora do tabeli przestawnej programu Excel
Załóżmy, że masz zbiór danych, jak pokazano poniżej:
Jest to fikcyjny zestaw danych (sprzedaż detaliczna w USA) obejmujący 1000 wierszy. Korzystając z tych danych, stworzyliśmy tabelę przestawną, która pokazuje łączną sprzedaż dla czterech regionów.
Czytaj więcej: Jak stworzyć tabelę przestawną od podstaw.
Po umieszczeniu tabeli przestawnej możesz wstawić fragmentatory.
Ktoś może zapytać - po co mi krajalnice?
Możesz potrzebować fragmentatorów, gdy nie chcesz całej tabeli przestawnej, ale tylko jej części. Na przykład, jeśli nie chcesz widzieć sprzedaży dla wszystkich regionów, ale tylko dla Południa lub Południa i Zachodu, możesz wstawić fragmentator i szybko wybrać żądane regiony, dla których chcesz uzyskać dane sprzedażowe.
Fragmentatory to bardziej wizualny sposób, który umożliwia filtrowanie danych tabeli przestawnej na podstawie wyboru.
Oto kroki, aby wstawić fragmentator do tej tabeli przestawnej:
- Zaznacz dowolną komórkę w tabeli przestawnej.
- Przejdź do Wstaw -> Filtr -> Fragmentator.
- W oknie dialogowym Wstaw fragmentatory wybierz wymiar, dla którego masz możliwość filtrowania danych. Pole fragmentatora wyświetli wszystkie dostępne wymiary i możesz wybrać jeden lub więcej niż jeden wymiar naraz. Na przykład, jeśli wybiorę tylko region, wstawi tylko pole Fragmentator regionu, a jeśli wybiorę oba regiony i typ sprzedawcy, wstawią dwa fragmentatory.
- Kliknij OK. Spowoduje to wstawienie fragmentatorów do arkusza.
Zwróć uwagę, że fragmentator automatycznie zidentyfikuje wszystkie unikatowe elementy wybranego wymiaru i wyświetli je w polu fragmentatora.
Po wstawieniu fragmentatora możesz filtrować dane, klikając element. Na przykład, aby uzyskać sprzedaż tylko dla regionu Południe, kliknij Południe. Zauważysz, że wybrany element ma inny odcień koloru w porównaniu z innymi elementami na liście.
Możesz także wybrać wiele elementów naraz. Aby to zrobić, przytrzymaj klawisz Control i kliknij te, które chcesz wybrać.
Jeśli chcesz wyczyścić wybór, kliknij ikonę filtra (z czerwonym krzyżykiem) w prawym górnym rogu.
Wstawianie wielu fragmentatorów do tabeli przestawnej
Możesz także wstawić wiele fragmentatorów, wybierając więcej niż jeden wymiar w oknie dialogowym Wstaw fragmentatory.
Aby wstawić wiele fragmentatorów:
- Zaznacz dowolną komórkę w tabeli przestawnej.
- Przejdź do Wstaw -> Filtr -> Fragmentator.
- W oknie dialogowym Wstaw fragmentatory wybierz wszystkie wymiary, dla których chcesz uzyskać fragmentatory.
- Kliknij OK.
Spowoduje to wstawienie wszystkich wybranych fragmentatorów do arkusza.
Zauważ, że te fragmentatory są ze sobą połączone. Na przykład, jeśli wybiorę opcję „Mid West” w filtrze Region i „Multiline” w filtrze Retailer Type, wyświetli się sprzedaż tylko dla wszystkich sprzedawców multiline w regionie Mid West.
Ponadto, jeśli wybiorę Mid West, zwróć uwagę, że opcja Specialty w drugim filtrze ma jaśniejszy odcień niebieskiego (jak pokazano poniżej). Wskazuje to, że nie ma danych dla detalistów specjalistycznych w regionie Mid West.
Krajalnice vs. Filtry raportów
Jaka jest różnica między fragmentatorami a filtrami raportów?
Krajalnice wyglądają super fajnie i są łatwe w użyciu. Siła tabeli przestawnej polega na tym, że nie potrzebujesz wielu umiejętności, aby z niej korzystać. Wszystko, co musisz zrobić, to przeciągnąć i upuścić oraz kliknąć tu i tam, a w ciągu kilku sekund otrzymasz gotowy raport.
Chociaż filtry raportów dobrze sobie z tym radzą, fragmentatory jeszcze bardziej ułatwiają filtrowanie tabeli przestawnej i/lub przekazywanie jej komukolwiek bez znajomości programu Excel lub tabel przestawnych. Ponieważ jest to tak intuicyjne, nawet ta osoba może sama korzystać z tych fragmentatorów, klikając na nie i filtrując dane. Ponieważ są to filtry wizualne, każdy może łatwo to zrozumieć, nawet jeśli używa go po raz pierwszy.
Oto kilka kluczowych różnic między fragmentatorami a filtrami raportów:
- Fragmentatory nie zajmują stałej komórki w arkuszu. Możesz je przenosić jak każdy inny obiekt lub kształt. Filtry raportów są powiązane z komórką.
- Filtry raportów są połączone z określoną tabelą przestawną. Z drugiej strony fragmentatory można łączyć z wieloma tabelami przestawnymi (jak zobaczymy w dalszej części tego samouczka).
- Ponieważ filtr raportu zajmuje stałą komórkę, łatwiej jest go zautomatyzować za pomocą VBA. Z drugiej strony fragmentator jest obiektem i wymagałby bardziej złożonego kodu.
Formatowanie krajalnicy
Fragmentator zapewnia dużą elastyczność, jeśli chodzi o formatowanie.
Oto rzeczy, które możesz dostosować we fragmentatorze.
Modyfikowanie kolorów krajalnicy
Jeśli nie podobają Ci się domyślne kolory fragmentatora, możesz je łatwo zmodyfikować.
- Wybierz fragmentator.
- Przejdź do Narzędzia fragmentatora -> Opcje -> Style fragmentatora. Tutaj znajdziesz wiele różnych opcji. Wybierz ten, który Ci się podoba, a Twój fragmentator natychmiast otrzyma to formatowanie.
Jeśli nie lubisz domyślnych stylów, możesz stworzyć własne. Aby to zrobić, wybierz opcję Nowy styl fragmentatora i określ własne formatowanie.
Pobieranie wielu kolumn w polu fragmentatora
Domyślnie fragmentator ma jedną kolumnę i wszystkie elementy wybranego wymiaru są w niej wymienione. Jeśli masz wiele elementów, Slicer wyświetla pasek przewijania, za pomocą którego możesz przejść przez wszystkie elementy.
Możesz chcieć, aby wszystkie elementy były widoczne bez konieczności przewijania. Możesz to zrobić, tworząc wielokolumnowy fragmentator.
Aby to zrobić:
- Wybierz fragmentator.
- Przejdź do Narzędzia fragmentatora -> Opcje -> Przyciski.
- Zmień wartość Kolumny na 2.
Spowoduje to natychmiastowe podzielenie elementów we fragmentatorze na dwie kolumny. Możesz jednak uzyskać coś tak okropnego, jak pokazano poniżej:
To wygląda na zagracone, a pełne nazwy nie są wyświetlane. Aby wyglądał lepiej, zmieniasz rozmiar krajalnicy, a nawet znajdujących się w niej przycisków.
Aby to zrobić:
- Wybierz fragmentator.
- Przejdź do Narzędzia fragmentatora -> Opcje.
- Zmień wysokość i szerokość przycisków i fragmentatora. (Zauważ, że możesz również zmienić rozmiar fragmentatora, po prostu zaznaczając go i używając myszy, aby dostosować krawędzie. Jednak, aby zmienić rozmiar przycisku, musisz dokonać zmian tylko w opcjach).
Zmienianie/usuwanie nagłówka krajalnicy
Domyślnie fragmentator wybiera nazwę pola z danych. Na przykład, jeśli stworzę fragmentator dla regionów, nagłówek automatycznie będzie miał postać „Region”.
Możesz zmienić nagłówek lub całkowicie go usunąć.
Oto kroki:
- Kliknij prawym przyciskiem myszy fragmentator i wybierz Ustawienia fragmentatora.
- W oknie dialogowym Ustawienia fragmentatora zmień podpis nagłówka na odpowiedni.
- Kliknij OK.
Zmieniłoby to nagłówek we fragmentatorze.
Jeśli nie chcesz widzieć nagłówka, odznacz opcję Wyświetl nagłówek w oknie dialogowym.
Sortowanie przedmiotów w krajalnicy
Domyślnie elementy we fragmentatorze są sortowane w porządku rosnącym w przypadku tekstu i Starsze do nowszego w przypadku liczb/dat.
Możesz zmienić ustawienie domyślne, a nawet użyć własnych niestandardowych kryteriów sortowania.
Oto jak to zrobić:
- Kliknij prawym przyciskiem myszy fragmentator i wybierz Ustawienia fragmentatora.
- W oknie dialogowym Ustawienia fragmentatora możesz zmienić kryteria sortowania lub użyć własnych niestandardowych kryteriów sortowania.
- Kliknij OK.
Czytaj więcej: Jak tworzyć niestandardowe listy w Excelu (aby stworzyć własne kryteria sortowania)
Ukrywanie elementów bez danych z pola krajalnicy
Może się zdarzyć, że niektóre pozycje w tabeli przestawnej nie zawierają żadnych danych. W takich przypadkach możesz sprawić, by fragmentatory ukryły ten przedmiot.
Na przykład na poniższym obrazku mam dwa fragmentatory (jeden dla regionu, a drugi dla typu detalisty). Kiedy wybieram Mid West, pozycja Specialty w drugim filtrze przybiera jasnoniebieski odcień wskazujący, że nie ma w nim danych.
W takich przypadkach możesz wybrać, aby w ogóle go nie wyświetlać.
Oto kroki, aby to zrobić:
- Kliknij prawym przyciskiem myszy fragmentator, w którym chcesz ukryć dane, i wybierz Ustawienia fragmentatora.
- W oknie dialogowym Ustawienia krajalnicy z opcjami „Sortowanie i filtrowanie elementów” zaznacz opcję „Ukryj elementy bez danych”.
- Kliknij OK.
Podłączanie krajalnicy do wielu tabel przestawnych
Fragmentator można połączyć z wieloma tabelami przestawnymi. Po połączeniu możesz użyć jednego fragmentatora do jednoczesnego filtrowania wszystkich połączonych tabel przestawnych.
Pamiętaj, że aby połączyć różne tabele przestawne z fragmentatorem, tabele przestawne muszą dzielić tę samą pamięć podręczną przestawną. Oznacza to, że zostały one utworzone przy użyciu tych samych danych lub jedna z tabel przestawnych została skopiowana i wklejona jako oddzielna tabela przestawna.
Czytaj więcej: Co to jest pamięć podręczna tabeli przestawnej i jak z niej korzystać?
Poniżej znajduje się przykład dwóch różnych tabel przestawnych. Zwróć uwagę, że w tym przypadku krajalnica działa tylko dla tabeli przestawnej po lewej stronie (i nie ma wpływu na tabelę po prawej).
Aby połączyć ten fragmentator z obiema tabelami przestawnymi:
- Kliknij prawym przyciskiem myszy fragmentator i wybierz opcję Połączenia raportu. (Alternatywnie można również wybrać fragmentator i przejść do Narzędzia fragmentatora -> Opcje -> Fragmentator -> Połączenia raportów).
- W oknie dialogowym Połączenia raportów zobaczysz wszystkie nazwy tabel przestawnych, które współużytkują tę samą pamięć podręczną. Wybierz te, które chcesz połączyć z fragmentatorem. W tym przypadku mam tylko dwie tabele przestawne i obie połączyłem za pomocą krajalnicy.
- Kliknij OK.
Teraz twój Slicer jest połączony z obiema tabelami przestawnymi. Po dokonaniu wyboru we fragmentatorze filtrowanie nastąpi w obu tabelach przestawnych (jak pokazano poniżej).
Tworzenie dynamicznych wykresów przestawnych za pomocą fragmentatorów
Tak jak używasz fragmentatora z tabelą przestawną, możesz go również używać z wykresami przestawnymi.
Coś, jak pokazano poniżej:
Oto jak możesz stworzyć ten dynamiczny wykres:
- Wybierz dane i przejdź do Wstaw -> Wykresy -> Wykres przestawny.
- W oknie dialogowym Utwórz wykres przestawny upewnij się, że zakres jest poprawny i kliknij przycisk OK. Spowoduje to wstawienie wykresu przestawnego do nowego arkusza.
- Dokonaj wyboru pól (lub przeciągnij i upuść pola do sekcji obszaru), aby uzyskać odpowiedni wykres przestawny. W tym przykładzie mamy wykres, który pokazuje sprzedaż według regionu przez cztery kwartały. (Przeczytaj tutaj, jak grupować daty jako kwartały).
- Po przygotowaniu wykresu przestawnego przejdź do Wstaw -> Fragmentator.
- Wybierz odpowiedni wymiar fragmentatora z wykresem. W tym przypadku chcę określić typ sprzedawcy, więc sprawdzam ten wymiar.
- Sformatuj wykres i fragmentator i gotowe.
Pamiętaj, że możesz połączyć wiele fragmentatorów z tym samym wykresem przestawnym, a także możesz połączyć wiele wykresów z tym samym fragmentatorem (w ten sam sposób, w jaki połączyliśmy wiele tabel przestawnych z tym samym fragmentatorem).
Kliknij tutaj, aby pobrać przykładowe dane i spróbować samemu.
Możesz również polubić następujące samouczki dotyczące tabel przestawnych:
- Jak grupować daty w tabeli przestawnej programu Excel.
- Jak grupować liczby w tabeli przestawnej w programie Excel.
- Jak odświeżyć tabelę przestawną w programie Excel.
- Przygotowanie danych źródłowych do tabeli przestawnej.
- Jak dodać i używać pola obliczeniowego tabeli przestawnej programu Excel.
- Jak zastosować formatowanie warunkowe w tabeli przestawnej w programie Excel.
- Jak zastąpić puste komórki zerami w tabelach przestawnych programu Excel.
