Policz odrębne wartości w tabeli przestawnej programu Excel (łatwy przewodnik krok po kroku)

Tabele przestawne Excela są niesamowite (wiem, że wspominam o tym za każdym razem, gdy piszę o tabelach przestawnych, ale to prawda).

Dzięki podstawowej wiedzy i odrobinie przeciągania i upuszczania możesz wykonać mnóstwo pracy w ciągu kilku sekund.

Chociaż wiele można zrobić za pomocą kilku kliknięć w tabelach przestawnych, istnieją pewne rzeczy, które wymagają kilku dodatkowych kroków lub odrobiny obejścia.

Jedną z takich rzeczy jest liczenie różnych wartości w tabeli przestawnej.

W tym samouczku pokażę, jak liczyć różne wartości, a także wartości unikalne w tabeli przestawnej programu Excel.

Ale zanim przejdę do tego, jak liczyć różne wartości, ważne jest, aby zrozumieć różnicę między „wyraźną liczbą” a „unikalną liczbą”

Wyraźna liczba kontra unikalna liczba

Chociaż mogą wydawać się tym samym, to nie jest.

Poniżej znajduje się przykład, w którym znajduje się zestaw danych z nazwami i osobno wymieniłem unikalne i odrębne nazwy.

Unikalne wartości/nazwy to te, które występują tylko raz. Oznacza to, że wszystkie nazwy, które się powtarzają i mają duplikaty, nie są niepowtarzalne. Unikalne nazwy są wymienione w kolumnie C w powyższym zbiorze danych

Odrębne wartości/nazwy to te, które występują co najmniej raz w zbiorze danych. Jeśli więc nazwa pojawia się trzy razy, nadal jest liczona jako jedna odrębna nazwa. Można to osiągnąć, usuwając zduplikowane wartości/nazwy i zachowując wszystkie odrębne. Odrębne nazwy są wymienione w kolumnie B w powyższym zestawie danych.

Opierając się na tym, co widziałem, większość przypadków, gdy ludzie mówią, że chcą uzyskać unikalną liczbę w tabeli przestawnej, w rzeczywistości mają na myśli odrębną liczbę, o czym mówię w tym samouczku.

Policz odrębne wartości w tabeli przestawnej programu Excel

Załóżmy, że masz dane sprzedaży, jak pokazano poniżej:

Kliknij tutaj, aby pobrać przykładowy plik i podążaj dalej

Mając powyższy zestaw danych, powiedzmy, że chcesz znaleźć odpowiedź na następujące pytania:

1. Ilu przedstawicieli handlowych jest w każdym regionie (co jest niczym innym jak odrębną liczbą przedstawicieli handlowych w każdym regionie)?
2. Ilu przedstawicieli handlowych sprzedało drukarkę w latach 2021-2022?

Chociaż tabele przestawne mogą błyskawicznie podsumowywać dane za pomocą kilku kliknięć, aby uzyskać liczbę odrębnych wartości, musisz wykonać jeszcze kilka kroków.

Jeśli używasz Excel 2013 lub późniejsze wersje, w tabeli przestawnej jest wbudowana funkcja, która szybko daje wyraźną liczbę. A jeśli używasz Excel 2010 lub wcześniejsze wersje, będziesz musiał zmodyfikować dane źródłowe, dodając kolumnę pomocnika.

W tym samouczku omówiono następujące dwie metody:

• Dodanie kolumny pomocniczej w oryginalnym zestawie danych w celu zliczania unikalnych wartości (działa we wszystkich wersjach).
• Dodanie danych do modelu danych i użycie opcji Distinct Count (dostępnej w Excel 2013 i późniejszych wersjach).

Istnieje trzecia metoda, którą Roger pokazuje w tym artykule (którą nazywa metodą Pivot the Pivot Table).

Zacznijmy!

Dodawanie kolumny pomocniczej w zbiorze danych

Uwaga: Jeśli korzystasz z programu Excel 2013 i nowszych wersji, pomiń tę metodę i przejdź do następnej (ponieważ wykorzystuje wbudowaną funkcję tabeli przestawnej - Wyraźna liczba).

Jest to łatwy sposób na zliczanie odrębnych wartości w tabeli przestawnej, ponieważ wystarczy dodać kolumnę pomocniczą do danych źródłowych. Po dodaniu kolumny pomocniczej możesz użyć tego nowego zestawu danych do obliczenia odrębnej liczby.

Chociaż jest to łatwe obejście, istnieją pewne wady tej metody (omówione w dalszej części tego samouczka).

Pozwól, że najpierw pokażę, jak dodać kolumnę pomocnika i uzyskać odrębną liczbę.

Załóżmy, że mam zestaw danych, jak pokazano poniżej:

Dodaj następującą formułę w kolumnie F i zastosuj ją do wszystkich komórek, które zawierają dane w sąsiednich kolumnach.

=JEŻELI(LICZ.WARUNKI($C$2:C2,C2$B$2:B2,B2)>1,0,1)

Powyższa formuła wykorzystuje funkcję LICZ.WARUNKI do zliczania, ile razy nazwa pojawia się w danym regionie. Należy również zauważyć, że zakres kryteriów to $C$2:C2 i $B$2:B2. Oznacza to, że stale się rozszerza, gdy schodzisz w dół kolumny.

Na przykład w komórce E2 zakresy kryteriów to $C$2:C2 i $B$2:B2, aw komórce E3 te zakresy są rozszerzane do $C$2:C3 i $B$2:B3.

Dzięki temu funkcja LICZ.WARUNKI zlicza pierwsze wystąpienie nazwy jako 1, drugie wystąpienie nazwy jako 2 i tak dalej.

Ponieważ chcemy uzyskać tylko odrębne nazwy, używana jest funkcja JEŻELI, która zwraca 1, gdy nazwa regionu pojawia się po raz pierwszy, a 0, gdy pojawia się ponownie. Dzięki temu liczone są tylko różne nazwy, a nie powtórzenia.

Poniżej przedstawiamy, jak wyglądałby Twój zbiór danych po dodaniu kolumny pomocniczej.

Teraz, gdy zmodyfikowaliśmy dane źródłowe, możemy użyć ich do utworzenia tabeli przestawnej i użyć kolumny pomocniczej, aby uzyskać odrębną liczbę przedstawicieli handlowych w każdym regionie.

Poniżej znajdują się kroki, aby to zrobić:

1. Wybierz dowolną komórkę w zestawie danych.
2. Kliknij kartę Wstaw.
3. Kliknij tabelę przestawną (lub użyj skrótu klawiaturowego - ALT + N + V)
4. W oknie dialogowym Utwórz tabelę przestawną upewnij się, że tabela/zakres jest poprawny (i zawiera kolumnę pomocnika) i zaznaczono opcję „Nowy arkusz roboczy”.
5. Kliknij OK.

Powyższe kroki spowodowały wstawienie nowego arkusza, który ma tabelę przestawną.

Przeciągnij pole „Region” w obszarze Wiersze i pole „D Count” w obszarze Wartości.

Otrzymasz tabelę przestawną, jak pokazano poniżej:

Teraz możesz zmienić nagłówek kolumny z „Suma liczby D” na „Przedstawiciel handlowy”.

Wady korzystania z kolumny pomocniczej:

Chociaż ta metoda jest dość prosta, muszę podkreślić kilka wad związanych z modyfikacją danych źródłowych w tabeli przestawnej:

• Źródło danych z kolumną pomocnika nie jest tak dynamiczne jak tabela przestawna. Chociaż możesz pokroić i pokroić dane w dowolny sposób, korzystając z tabeli przestawnej, korzystając z kolumny pomocniczej, tracisz część tej umiejętności. Załóżmy, że dodajesz kolumnę pomocniczą, aby uzyskać liczbę odrębnych przedstawicieli handlowych w każdym regionie. A co, jeśli chcesz również uzyskać wyraźną liczbę sprzedawców sprzedających drukarki. Będziesz musiał wrócić do danych źródłowych i zmodyfikować formułę kolumny pomocniczej (lub dodać nową kolumnę pomocniczą).
• Ponieważ dodajesz więcej danych do źródła tabeli przestawnej (które jest również dodawane do pamięci podręcznej przestawnej), może to prowadzić do większego rozmiaru pliku Excel.
• Ponieważ używamy formuły programu Excel, może to spowolnić działanie skoroszytu programu Excel, jeśli masz tysiące wierszy danych.

Dodaj dane do modelu danych i podsumuj za pomocą odrębnej liczby

Tabela przestawna dodała nową funkcję w programie Excel 2013, która pozwala uzyskać odrębną liczbę podczas podsumowywania zestawu danych.

Jeśli korzystasz z poprzedniej wersji, nie będziesz mógł użyć tej metody (należy spróbować dodać kolumnę pomocniczą, jak pokazano w metodzie powyżej tej).

Załóżmy, że masz zestaw danych, jak pokazano poniżej, i chcesz uzyskać liczbę unikalnych przedstawicieli handlowych w każdym regionie.

Poniżej znajdują się kroki, aby uzyskać odrębną wartość licznika w tabeli przestawnej:

1. Wybierz dowolną komórkę w zestawie danych.
2. Kliknij kartę Wstaw.
3. Kliknij tabelę przestawną (lub użyj skrótu klawiaturowego - ALT + N + V)
4. W oknie dialogowym Utwórz tabelę przestawną upewnij się, że tabela/zakres jest poprawna, a nowy arkusz w polu Wybrane.
5. Zaznacz pole, które mówi - „Dodaj te dane do modelu danych”
6. Kliknij OK.

Powyższe kroki spowodowały wstawienie nowego arkusza, który ma nową tabelę przestawną.

Przeciągnij Region w obszarze Wiersze i Przedstawiciel handlowy w obszarze Wartości. Otrzymasz tabelę przestawną, jak pokazano poniżej:

Powyższa tabela przestawna podaje całkowitą liczbę przedstawicieli handlowych w każdym regionie (a nie odrębną liczbę).

Aby uzyskać odrębną liczbę w tabeli przestawnej, wykonaj poniższe czynności:

1. Kliknij prawym przyciskiem myszy dowolną komórkę w kolumnie „Liczba przedstawicieli handlowych”.
2. Kliknij Ustawienia pola wartości
3. W oknie dialogowym Ustawienia pola wartości wybierz „Odrębna liczba” jako typ obliczenia (może być konieczne przewinięcie listy, aby ją znaleźć).
4. Kliknij OK.

Zauważysz, że nazwa kolumny zmienia się z „Liczba przedstawicieli handlowych” na „Odrębna liczba przedstawicieli handlowych”. Możesz to zmienić na co chcesz.

Niektóre rzeczy, które wiesz, kiedy dodajesz swoje dane do modelu danych:

• Jeśli zapiszesz dane w modelu danych, a następnie otworzysz w starszej wersji programu Excel, wyświetli się ostrzeżenie „Niektóre funkcje tabeli przestawnej nie zostaną zapisane”. Możesz nie widzieć odrębnej liczby (i modelu danych) po otwarciu w starszej wersji, która jej nie obsługuje.
• Po dodaniu danych do modelu danych i utworzeniu tabeli przestawnej nie zostaną wyświetlone opcje dodawania pól obliczeniowych i kolumn obliczeniowych.

Kliknij tutaj, aby pobrać przykładowy plik

Co zrobić, jeśli chcesz policzyć unikalne wartości (a nie odrębne wartości)?

Jeśli chcesz zliczać unikalne wartości, nie masz żadnej wbudowanej funkcji w tabeli przestawnej i będziesz musiał polegać tylko na kolumnach pomocniczych.

Pamiętaj - unikalne wartości i odrębne wartości to nie to samo. Kliknij tutaj, aby poznać różnicę.

Jednym z przykładów może być sytuacja, gdy masz poniższy zestaw danych i chcesz dowiedzieć się, ilu przedstawicieli handlowych jest unikalnych dla każdego regionu. Oznacza to, że działają tylko w jednym konkretnym regionie, a nie w innych.

W takich przypadkach musisz utworzyć jedną lub więcej niż jedną kolumnę pomocniczą.

W tym przypadku poniższa formuła załatwia sprawę:

= JEŻELI (JEŻELI (LICZ.JEŻELI($ C$2:$C$1001,C2,$B$2:$B1001,B2)/LICZ.JEŻELI($C$2:$C$1001,C2)1,0,1),0)

Powyższa formuła sprawdza, czy nazwa przedstawiciela handlowego występuje tylko w jednym regionie, czy w więcej niż jednym regionie. Robi to, licząc liczbę wystąpień nazwy w regionie i dzieląc ją przez całkowitą liczbę wystąpień nazwy. Jeśli wartość jest mniejsza niż 1, oznacza to, że nazwa występuje w dwóch lub więcej niż dwóch regionach.

W przypadku, gdy nazwa występuje w więcej niż jednym regionie, zwraca 0, w przeciwnym razie zwraca jedynkę.

Formuła sprawdza również, czy nazwa powtarza się w tym samym regionie, czy nie. Jeśli nazwa się powtarza, tylko pierwsze wystąpienie nazwy zwraca wartość 1, a wszystkie inne zwracają 0.

To może wydawać się nieco skomplikowane, ale znowu zależy od tego, co chcesz osiągnąć.

Jeśli więc chcesz zliczać unikatowe wartości w tabeli przestawnej, użyj kolumn pomocniczych, a jeśli chcesz zliczyć różne wartości, możesz skorzystać z wbudowanej funkcji (w programie Excel 2013 i nowszych) lub użyć kolumny pomocniczej.

Kliknij tutaj, aby pobrać przykładowy plik

Możesz również polubić następujące samouczki dotyczące tabel przestawnych:

• Jak filtrować dane w tabeli przestawnej w programie Excel
• Jak grupować daty w tabelach przestawnych w programie Excel
• Jak grupować liczby w tabeli przestawnej w programie Excel
• Jak zastosować formatowanie warunkowe w tabeli przestawnej w programie Excel
• Fragmentatory w tabeli przestawnej programu Excel
• Jak odświeżyć tabelę przestawną w programie Excel
• Usuń tabelę przestawną w programie Excel