Liczenie unikalnych elementów w tabeli przestawnej

Jak policzyć unikalne elementy (count distinct) w tabeli przestawnej Excela. Trzy metody, dla różnych wersji Excela. Pobierz darmowy skoroszyt, aby podążać za Tobą

Dziękowania dla Rogera Goviera, który stworzył ten samouczek i skoroszyt.

Unikalne elementy

Jeśli używasz tabeli przestawnej do podsumowania danych, możesz mieć pytania takie jak:

• Ilu unikalnych (odrębnych) klientów dokonało zakupów w każdym regionie?
• Ile unikalnych produktów zostało sprzedanych w każdym sklepie?
• Ilu unikalnych sprzedawców sprzedało każdy produkt w każdym regionie?

W zwykłej tabeli przestawnej nie ma wbudowanej funkcji „Unique Count”, ale w tym poradniku zobaczysz, jak uzyskać unikalną liczbę elementów w tabeli przestawnej.

• W Excelu 2013 i nowszych wersjach, utwórz Model Danych
• W Excelu 2010 i nowszych wersjach, użyj techniki „Tabela przestawna”.
• Or, w starszych wersjach, dodaj nową kolumnę do danych źródłowych, i Użyj CountIf.
• Jeśli dodatek Power Pivot jest zainstalowany, użyj go, aby pokazać odrębne liczenie

Ten film pokazuje kroki, aby pokazać odrębne liczenie za pomocą Modelu danych.

Wprowadzenie

Często mamy duże zbiory danych, które chcemy przeanalizować za pomocą tabeli przestawnej, ale chcemy tylko policzyć unikalne wystąpienia niektórych elementów danych. Niestety, tabele przestawne nigdy nie miały wbudowanej funkcji zliczania unikalnych wartości.

W tym przykładzie, przykładowy plik zawiera 4999 rekordów, które pokazują sprzedaż produktów, wraz z regionem i nazwiskiem sprzedawcy. Kilka pierwszych rekordów jest pokazanych na poniższym zrzucie ekranu. Przykładowy plik można pobrać z poniższego linku.

Tworzenie tabeli przestawnej z tych danych dałoby nam coś takiego jak poniżej, gdybyśmy dodali Osobę do obszaru Wartości w tabeli przestawnej i podsumowali ją liczbą.

Jednakże daje nam to liczbę wszystkich transakcji, a nie liczbę unikalnych osób, które dokonały tych transakcji w każdym Regionie.

Uzyskanie unikalnej liczby osób

Istnieją obejścia, których można użyć, aby uzyskać unikalną liczbę osób:

• Metoda 1: W Excelu 2013 i nowszych, dodaj dane źródłowe tabeli przestawnej do Modelu danych, a unikalna liczba osób może być łatwo wykonana.
• Metoda 2: W programie Excel 2010 i nowszych użyj techniki „pivot a pivot”.
• Metoda 3: W starszych wersjach programu Excel dodaj kolumnę do danych źródłowych, aby umieścić 1 w komórkach w wierszu, w którym wartość jest unikalna, a 0 w pozostałych komórkach. Następnie sumowanie tej dodatkowej kolumny zapewnia unikalną wartość.
• Metoda 4: Użyj PowerPivot, aby utworzyć tabelę przestawną i użyj jej funkcji do utworzenia unikalnej liczby. Zobacz szczegóły poniżej.

Wszystkie cztery metody są opisane poniżej, wraz z porównaniem wydajności między metodami 2 i 3.

1. Dodaj do Modelu danych – Excel 2013 i nowsze

W programie Excel 2013, jeśli dodasz dane źródłowe tabeli przestawnej do Modelu danych skoroszytu, łatwo jest utworzyć unikalną liczbę.

UWAGA: Ta technika tworzy tabelę przestawną opartą na OLAP, która ma pewne ograniczenia, takie jak brak grupowania i brak obliczonych pól lub obliczonych elementów. Jeśli potrzebujesz tych ograniczonych funkcji, spróbuj zamiast tego metody „pivot a pivot”.

Tworzenie unikalnej liczby (Distinct Count)

Aby utworzyć tabelę przestawną, wykonaj następujące kroki:

• Wybierz komórkę w tabeli danych źródłowych.
• Na dole okna dialogowego Utwórz tabelę przestawną dodaj zaznaczenie do „Dodaj te dane do modelu danych”
• Kliknij OK

Aby ustawić układ tabeli przestawnej, wykonaj następujące czynności:

• W tabeli przestawnej dodaj Region do obszaru Wiersz.
• Dodaj te 3 pola do obszaru Values — Person, Units, Value
• Pole Person zawiera tekst, więc domyślnie ustawione jest na Count of Person. Count pokazuje całkowitą liczbę transakcji w każdym regionie, a nie unikalną liczbę osób

Aby uzyskać unikalną liczbę osób w każdym regionie, wykonaj następujące kroki:

• Kliknij prawym przyciskiem myszy jedną z wartości w polu Person
• Kliknij Value Field Settings

• Na liście Summarize Value Field By przewiń do dołu, i kliknij Distinct Count, a następnie kliknij OK

Pole Person zmienia się i zamiast pokazywać całkowitą liczbę transakcji, pokazuje odrębną liczbę nazwisk sprzedawców.

2. Pivot the Pivot – Excel 2010

Aby uzyskać unikalną liczbę sprzedawców na region w Excelu 2010, możemy utworzyć raport przestawny, a następnie „Pivot the Pivot”, aby uzyskać ostateczny wynik.

Utwórz pierwszą tabelę przestawną

• Utwórz tabelę przestawną z tych danych, z Regionem i Osobą w obszarze Wiersze
• Dodaj Jednostki i Wartość w obszarze Wartości. Ponieważ Osoba jest polem tekstowym, tabela przestawna automatycznie wyświetli ją jako „Count of”.
• Formatuj tabelę przestawną za pomocą tabelarycznego układu raportu
• Ustaw wszystkie etykiety elementów, aby powtarzały się w każdym wierszu.
• Zmień podpisy, aby usunąć „Suma”
  TIP: Wpisz oryginalną nazwę pola, ze znakiem spacji na końcu, np. „jednostki”

Teraz wygląda to jak normalna tabela danych, ale jest to podsumowanie oryginalnej pełnej tabeli danych z 4 999 wierszami.

Twórz nazwany zakres dla komórki początkowej

Następnie nadamy nazwę pierwszej komórce tabeli przestawnej.

• Wybierz komórkę B2, która jest nagłówkiem w kolumnie Region w tabeli przestawnej
• Kliknij na pasku formuły i wpisz nazwę komórki — pvtStart
• Naciśnij Enter, aby dokończyć nadawanie nazwy

Twórz zakres nazw dla wiersza końcowego

Kolejnym krokiem jest obliczenie położenia ostatniego wiersza w tabeli przestawnej. Użyjemy funkcji MATCH z bardzo dużą liczbą, aby znaleźć ostatnią liczbę w kolumnie D i uzyskać jej numer wiersza. Formuła ta będzie działać tylko w kolumnach z liczbami.

• W arkuszu Dane, w komórce M2, wpisz następującą formułę, aby obliczyć ostatni wiersz w kolumnie Jednostki tabeli przestawnej:

  =MATCH(9.9E+307,Metoda_1!D:D,1)

• Aby nazwać tę komórkę, zaznacz komórkę M2, a w polu Nazwa wpisz PvtEnd i naciśnij Enter.

Twórz dynamiczny Zakres nazwany

Następnie utworzymy dynamiczny Zakres nazwany, aby odnosić się do tej nowej tabeli. Zakres ten będzie zaczynał się w komórce o nazwie pvtstart (komórka nagłówka Region), a kończył w ostatnim wierszu danych w kolumnie E (zakres nazwany, PvtEnd).

• Na karcie Formuły na wstążce kliknij przycisk Zdefiniuj nazwę
• W oknie dialogowym Nowa nazwa wpisz nazwę myData
• W rozwijanym polu Zakres wybierz pozycję Skoroszyt
• W polu Odwołuje się do wpisz formułę:
  =pvtStart:INDEX(Metoda_1!$E:$E, PvtEnd)
• Kliknij OK, aby utworzyć zakres nazwany

Utwórz drugą tabelę przestawną

Na koniec utwórz drugą tabelę przestawną na podstawie zakresu dynamicznego myData.

Wstaw Region w obszarze Wiersz, a Osobę, Jednostki i Wartość w obszarze Wartości

Pokazuje to liczbę unikalnych sprzedawców w poszczególnych regionach, jak pokazano poniżej. Na przykład, w pierwszej tabeli przestawnej, region Wschód pokazał 8 unikalnych nazwisk w kolumnie Osoba, i taka jest liczba w tej tabeli przestawnej.

Odświeżanie tabel przestawnych

Naturalnie, ponieważ w tym rozwiązaniu występują 2 tabele przestawne, obie muszą być odświeżane po dodaniu lub zmianie danych w tabeli źródłowej. Istotne jest, aby najpierw odświeżyć pierwszą tabelę przestawną, a następnie odświeżyć ostatnią tabelę przestawną.

Możesz odświeżyć tabele przestawne ręcznie lub użyć makra. Aby użyć makra, dodaj jedną z poniższych procedur do arkusza z ostateczną tabelą przestawną.

A) Jeśli obie tabele przestawne znajdują się w tym samym arkuszu, użyj następującego kodu:

Private Sub Worksheet_Activate() Me.PivotTables(1).PivotCache.Refresh Me.PivotTables(2).PivotCache.Refresh End Sub

B) Jeśli tabele przestawne znajdują się w różnych arkuszach, użyj następującego kodu (zastąp nazwę swojego arkusza nazwą „yoursheetname”):

Private Sub Worksheet_Activate() Sheets("yoursheetname").PivotTables(1).PivotCache.Refresh Me.PivotTables(1).PivotCache.Refresh End Sub

(Opcjonalnie) Nazwij zakres danych tabeli przestawnej

Jeśli używasz makr do odświeżania tabel przestawnych, możesz również użyć makra, aby zresetować zakres nazwany myData po każdym odświeżeniu. Dodaj następującą procedurę do swojego skoroszytu, a następnie wywołaj tę procedurę SetmyData w procedurach Worksheet_Activate.

Sub SetmyData()'Change "PT_A" to whatever name'you have given to your first Pivot Table'that is to be used as the source'for for the final Pivot Table'Also change the sheet name'from "Method_1" to'your sheet name if it is differentDim PTName As StringDim ShName As StringPTName = "PT_A"ShName = "Method_1"ThisWorkbook.Names.Add _ Name:="myData", _ RefersTo:=Sheets(ShName) _ .PivotTables(PTName) _ .TableRange1End Sub

3. Użyj COUNTIF — Excel 2007 i wcześniejsze

We wcześniejszych wersjach Excela tabele przestawne nie mają opcji powtarzania etykiet wierszy. Aby uzyskać unikalną liczbę sprzedawców w poszczególnych regionach, możesz dodać nową kolumnę w danych źródłowych i wprowadzić formułę COUNTIF

Przykładowy plik możesz pobrać z poniższego linku.

Dodaj formułę COUNTIF

Postępuj zgodnie z poniższymi krokami, aby dodać formułę:

W danych źródłowych dodaj nagłówek nowej kolumny — „Unique” — w komórce J1

W komórce J2 wprowadź tę formułę i skopiuj ją do ostatniego wiersza danych:

=IF(COUNTIF($E$5:E5,E5)>1,0,1)

Pierwsze odwołanie w zakresie ($E$5) jest bezwzględne, a drugie względne (E5), więc w miarę kopiowania formuły w dół strony, więc zakres będzie się rozszerzał od $E$5:E5 do $E$5:E6, następnie $E$5:E7 i tak dalej.

W każdym wierszu sprawdzamy, ile razy wystąpiła wartość w kolumnie E (Osoba), aż do bieżącego wiersza. Jeśli jest ona większa niż jeden raz, wynikiem jest zero, a więc ta osoba nie jest liczona ponownie. Dzięki temu możemy zobaczyć, ile razy w zbiorze danych pojawiła się dana osoba.

Po dodaniu dodatkowej kolumny, dane wyglądałyby tak. Drugie wystąpienie „Harry” zwraca zero w kolumnie Unikat.

UWAGA: Jeśli osoby o tym samym nazwisku sprzedają w różnych Regionach:

• W Excelu 2007 lub nowszym: Użyj COUNTIFS, aby sprawdzić wiele kryteriów. Na przykład, =IF(COUNTIFS($D$5:$D5,D5,$E$5:E5,E5)=1,1,0)
• W programie Excel 2003 lub wcześniejszym, połącz nazwy Regionów i Osób w nową kolumnę, a następnie użyj formuły COUNTIF, aby znaleźć unikalne wartości w tej połączonej kolumnie.

Utwórz tabelę przestawną

Aby zobaczyć liczbę unikatów w tabeli przestawnej:

• Utwórz tabelę przestawną z tych danych, z Regionem w obszarze Wiersze
• Dodaj Unikat, Jednostki i Wartość w obszarze Wartości.
• Zmień nagłówek „Unique” na „Count of Person” lub „Person”

Widzimy, że jest 30 osób, które dokonały sprzedaży, a także liczbę każdej z nich, która znajduje się w każdym regionie, a jeśli zdecydujemy się rozwinąć dowolny region, zobaczymy indywidualne sumy dla każdej osoby.

Porównanie wydajności

Jeśli to możliwe, użyj metody „pivot a pivot”, która jest znacznie szybsza.

Metoda COUNTIF

Użycie formuły COUNTIF w danych źródłowych działa i daje nam pożądany wynik, a na relatywnie małym zbiorze danych metoda ta jest do zaakceptowania. Jednak jest ona bardzo kosztowna pod względem czasu przetwarzania, a na bardzo dużych zbiorach danych może być niezwykle powolna.

Użycie programu „FastExcel” Charlesa Williamsa do obliczenia czasu potrzebnego na wykonanie obliczeń w skoroszycie daje wynik pokazany poniżej, gdzie czas potrzebny na ponowne przeliczenie arkusza danych wynosi 224 milisekundy.

Metoda Pivot a Pivot

Metoda „Pivot a Pivot” również dała poprawny wynik, z unikalną liczbą osób, zgodnie z wymaganiami. Ponadto nie wymaga ona dodatkowych kolumn w danych źródłowych ani wprowadzania żadnych nowych formuł.

Wynikowy skoroszyt jest mniejszy i ponownie przy użyciu „FastExcel” Charlesa Williamsa do obliczenia czasu obliczania arkusza Dane jest niewiarygodnie szybki 1.2 milisekundy

Przy małych zestawach danych czas przeliczania może nie być krytyczny, ale kiedy mamy do czynienia z większymi zestawami danych o 300 000 do 400 000 wierszy, wtedy te różnice stałyby się bardzo istotne.

Power Pivot

Jeśli posiadasz zainstalowany dodatek PowerPivot, możesz go użyć do pokazania odrębnej liczby dla pola. Ten film pokazuje kroki tworzenia tabeli przestawnej Power Pivot i dodawania pola z unikalną liczbą.

Aby uzyskać pisemne instrukcje, zobacz kroki w Excelu 2013 na moim blogu o tabelach przestawnych. Istnieją również instrukcje dla Excela 2010.

Plik przykładowy: Aby podążać za filmem, pobierz plik przykładowy, który został użyty do tego filmu

Pobierz pliki przykładowe

• Model danych: Pobierz przykładowy skoroszyt Data Model, który zawiera przykład dla Metody 1 (Model danych). Plik jest spakowany i jest w formacie xlsx. Plik nie zawiera makr.
• Bez modelu danych: Pobierz przykładowy skoroszyt roboczy bez Modelu danych, który zawiera przykłady dla Metody 2 (przestawienie a przestawienie) i Metody 3 (kolumna w danych źródłowych). Plik jest spakowany i jest w formacie xlsb. Plik nie zawiera makr.
• Power Pivot Video: Aby podążać za filmem Power Pivot w Metodzie 4, pobierz przykładowy plik, który został użyty w filmie

Powiązane poradniki

Nazwy zakresów

Liczenie unikalnych w PowerPivot

Przykładowe pliki – Roger Govier

O programiście

Roger Govier jest Excel MVP z siedzibą w Wielkiej Brytanii, który wykonuje zadania w Excelu i VBA dla klientów na całym świecie. Chociaż lubi intelektualne wyzwania związane z rozwiązywaniem problemów z funkcjami arkusza, Roger twierdzi, że jest z natury leniwy, więc zawsze szuka szybkiego i prostego sposobu na dostarczenie solidnych, wykonalnych rozwiązań.

Znajdź więcej tutoriali i przykładowych plików Rogera tutaj: Sample Files – Roger Govier

Możesz skontaktować się z Rogerem na stronie: [email protected]

Roger Govier