2 Sposoby Obliczania Liczby Odrębnej za Pomocą Tabel Przestawnych

Podsumowanie: Poznaj dwa sposoby na rozwiązanie problemu analizy danych, obliczania liczby porządkowej, za pomocą tabel przestawnych.

Poziom umiejętności: Średniozaawansowany

Tutorial wideo

Pobierz plik Excel

Załączyłem zarówno oryginalny plik jak i plik z rozwiązaniem do pobrania tutaj:

Data Analysis Challenge.xlsx (16.1 KB)

Data Analysis Challenge – Pivot Table Solution FINAL.xlsx (118.4 KB)

Counting Unique Rows

W tym wpisie przyjrzymy się dwóm różnym sposobom liczenia unikalnych wierszy przy użyciu tabel przestawnych. Te dwie metody zostały zgłoszone jako rozwiązania do zadania analizy danych, które możesz znaleźć tutaj:

Excel Data Analysis Challenge

Podsumowując wyzwanie, chcemy stworzyć raport podsumowujący liczbę transakcji według etapu, ale w danych CRM jest wiele wierszy na każdą transakcję. Musimy więc znaleźć sposób na utworzenie odrębnej liczby (zliczającej unikalne wiersze) dla każdej transakcji, aby móc je zsumować.

Przy okazji, dziękuję wszystkim, którzy nadesłali rozwiązania do wyzwania związanego z danymi! Było wiele świetnych zgłoszeń.

Rozwiązanie #1 – Użycie kolumny pomocniczej

Wspaniałą rzeczą w tym rozwiązaniu jest to, że może być ono użyte w każdej wersji Excela.

Zacznij od przekształcenia swoich danych w tabelę Excela. Aby to zrobić, zaznacz dowolną komórkę w zbiorze danych i kliknij przycisk Formatuj jako tabelę na karcie głównej. Kliknij prawym przyciskiem myszy żądany format tabeli i wybierz opcję Zastosuj i wyczyść formatowanie.

Kliknij OK, gdy pojawi się okno Formatuj jako tabelę.

Teraz, gdy dane są w formacie tabeli, dodaj kolumnę pomocniczą po prawej stronie tabeli i oznacz ją Deal Count. Użyj funkcji COUNTIF, której zakresem jest kolumna Deal ID, a kryterium komórka w kolumnie Deal ID odpowiadająca wierszowi, w którym się znajdujesz.

Formuła zwróci liczbę wierszy dla każdego numeru Deal ID. Jeśli podzielimy formułę na liczbę 1, to w każdej z tych komórek otrzymamy ułamki, które po zsumowaniu będą liczyć po jednym wpisie dla każdej transakcji.

Zmianę formuły można zobaczyć tutaj w kolorze zielonym:

=1/COUNTIF(,])

Teraz, gdy mamy już te ułamki, które dadzą nam wyraźną liczbę podczas tworzenia naszej tabeli przestawnej, możemy przejść dalej i utworzyć tabelę przestawną, wybierając opcję Tabela przestawna na karcie Wstawianie.

Aby utworzyć nasz raport podsumowujący przy użyciu nowej tabeli przestawnej, umieść etap sprzedaży w obszarze Wiersze, a liczbę transakcji w obszarze Suma wartości.

To da nam raport podsumowujący, którego szukamy, z liczbą transakcji w każdym etapie sprzedaży.

Miłą rzeczą w używaniu tabeli przestawnej jest to, że gdy dodajemy lub usuwamy pozycje danych źródłowych, możemy odświeżyć tabelę przestawną ( Alt + F5 ), aby uwzględnić te zmiany.

Rozwiązanie # 2 – Użycie Power Pivot

To rozwiązanie jest dostępne tylko dla wersji Excela 2013 lub nowszych dla Windows.

Nadal chcemy, aby nasze dane były sformatowane jako tabela Excela, ale nie potrzebujemy kolumny pomocniczej do tego rozwiązania.

Tym razem, kiedy tworzymy naszą tabelę przestawną, zaznaczamy pole z napisem Dodaj tę tabelę do modelu danych. (Data Model to inne określenie dla PowerPivot.)

Gdy tym razem zbudujemy tabelę przestawną, przeciągniemy Deal ID do obszaru Suma wartości.

To początkowo daje nam liczby, których nie chcemy w naszym raporcie podsumowującym. Aby to naprawić, kliknij prawym przyciskiem myszy na nagłówek kolumny Suma ID transakcji i wybierz Ustawienia pola wartości. Spowoduje to otwarcie okna, w którym możemy wybrać Distinct Count jako typ obliczeń.

Funkcja Distinct Count przechodzi przez kolumnę Deal ID i daje nam liczbę unikalnych wartości, więc nasz raport podsumowujący będzie wyglądał tak samo jak w przypadku rozwiązania #1.

Porównanie tych dwóch rozwiązań

Oba te rozwiązania są świetne, ponieważ mogą być odświeżane, gdy nowe dane są dodawane do tabeli źródłowej.

Zaletą rozwiązania nr 1 jest to, że można je wykonać w dowolnej wersji programu Excel. Z tego powodu, jeśli używasz 2013 lub nowszej w systemie Windows, rozwiązanie nr 2 jest lepszym rozwiązaniem. To dlatego, że rozwiązanie # 1 dostaje nieporęczne, gdy próbujesz filtrować dane w dół (powiedzmy, dla określonego produktu) lub użyć slicerów, aby rozebrać dane dalej.

Jeśli chciałbyś dowiedzieć się więcej na temat korzystania z tabel przestawnych, mam osobny wpis na blogu, który możesz sprawdzić tutaj: Wprowadzenie do tabel przestawnych i dashboardów.

Inne rozwiązania

Zostało zgłoszonych wiele innych świetnych rozwiązań. Obejmowały one użycie Power Query i nowych funkcji dynamicznych. Przyjrzymy się im w kolejnych postach, ale chciałem zacząć od tych dwóch, ponieważ były one bardziej uniwersalne pod względem dostępu do wersji Excela.