Prosta tabela przestawna do liczenia wartości unikatowych

Wydaje się, że jest to prosta tabela przestawna do nauki. Chciałbym policzyć unikalne wartości dla określonej wartości, według której się grupuję.

Na przykład mam to:

ABC   123
ABC   123
ABC   123
DEF   456
DEF   567
DEF   456
DEF   456

To, czego chcę, to tabela przestawna, która pokazuje mi to:

ABC   1
DEF   2

Prosta tabela przestawna, którą utworzę, po prostu daje mi to (liczbę wierszy):

ABC   3
DEF   4  

Ale zamiast tego chcę mieć liczbę unikalnych wartości.

To, co naprawdę próbuję zrobić, to dowiedzieć się, które wartości w pierwszej kolumnie nie mają takiej samej wartości w drugiej kolumnie we wszystkich wierszach. Innymi słowy, „ABC” jest „dobre”, „DEF” jest „złe”

Jestem pewien, że jest łatwiejszy sposób, ale pomyślałem, że spróbuję tabeli przestawnej ...

Wstaw trzecią kolumnę i w komórce C2wklej tę formułę

=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)

i skopiuj go. Teraz utwórz swoją oś w oparciu o pierwszą i trzecią kolumnę. Zobacz migawkę

AKTUALIZACJA: Możesz to teraz zrobić automatycznie w programie Excel 2013. Utworzyłem to jako nową odpowiedź, ponieważ moja poprzednia odpowiedź faktycznie rozwiązuje nieco inny problem.

Jeśli masz tę wersję, wybierz dane, aby utworzyć tabelę przestawną, a podczas tworzenia tabeli upewnij się, że pole wyboru „Dodaj te dane do modelu danych” jest zaznaczone (patrz poniżej).

Następnie po otwarciu tabeli przestawnej normalnie utwórz wiersze, kolumny i wartości. Następnie kliknij pole, dla którego chcesz obliczyć odrębną liczbę, i edytuj Ustawienia wartości pola:

Na koniec przewiń w dół do ostatniej opcji i wybierz „Odrębna liczba”.

Powinno to zaktualizować wartości tabeli przestawnej, aby pokazać dane, których szukasz.

Chciałbym dodać do tego zestawu dodatkową opcję, która nie wymaga formuły, ale może być pomocna, jeśli chcesz policzyć unikalne wartości w zestawie w dwóch różnych kolumnach. Korzystając z oryginalnego przykładu, nie miałem:

ABC   123  
ABC   123  
ABC   123   
DEF   456  
DEF   567  
DEF   456  
DEF   456

i chcesz, aby wyglądał jak:

ABC   1  
DEF   2

Ale coś bardziej jak:

ABC   123  
ABC   123  
ABC   123  
ABC   456  
DEF   123  
DEF   456  
DEF   567  
DEF   456  
DEF   456

i chciałem, żeby wyglądało to następująco:

ABC  
   123    3  
   456    1  
DEF  
   123    1  
   456    3  
   567    1

Najlepszym sposobem na przeniesienie moich danych do tego formatu, a następnie dalsze manipulowanie nimi, jest użycie:

Po wybraniu opcji „Suma uruchomiona w” wybierz nagłówek dodatkowego zestawu danych (w tym przypadku będzie to nagłówek lub tytuł kolumny zestawu danych zawierającego 123, 456 i 567). W ten sposób uzyskasz maksymalną wartość z łączną liczbą elementów w tym zestawie w ramach podstawowego zestawu danych.

Następnie skopiowałem te dane, wkleiłem je jako wartości, a następnie umieściłem w innej tabeli przestawnej, aby łatwiej nimi manipulować.

Do Twojej wiadomości, miałem około ćwierć miliona wierszy danych, więc działało to o wiele lepiej niż niektóre podejścia oparte na formułach, szczególnie te, które próbują porównać dwie kolumny / zestawy danych, ponieważ ciągle zawieszały aplikację.

Najłatwiejszym sposobem jest użycie Distinct Countopcji pod Value Field Settings( kliknij lewym przyciskiem myszy pole w Valuespanelu). Opcja dla Distinct Countznajduje się na samym dole listy.

Oto przed (TOP; normal Count) i po (BOTTOM; Distinct Count)

Zobacz unikalne przedmioty Debry Dalgleish's Count

Nie jest konieczne sortowanie tabeli, aby poniższa formuła zwracała 1 dla każdej unikalnej wartości.

zakładając, że zakres tabeli dla danych przedstawionych w pytaniu to A1: B7 wprowadź w komórce C1 następującą formułę:

=IF(COUNTIF($B$1:$B1,B1)>1,0,COUNTIF($B$1:$B1,B1))

Skopiuj tę formułę do wszystkich wierszy, a ostatni wiersz będzie zawierał:

=IF(COUNTIF($B$1:$B7,B7)>1,0,COUNTIF($B$1:$B7,B7))

Powoduje to zwrócenie 1 przy pierwszym znalezieniu rekordu i 0 dla wszystkich później.

Po prostu zsumuj kolumnę w tabeli przestawnej

Moje podejście do tego problemu było trochę inne niż to, co widzę tutaj, więc podzielę się.

1. (Najpierw wykonaj kopię swoich danych)
2. Połącz kolumny
3. Usuń duplikaty z połączonej kolumny
4. Ostatni - obrót na wynikowym zestawie

Uwaga: chciałbym dołączyć obrazy, aby było to jeszcze łatwiejsze do zrozumienia, ale nie mogę, ponieważ to mój pierwszy post;)

Odpowiedź Siddhartha jest niesamowita.

jednak ta technika może powodować problemy podczas pracy z dużym zestawem danych (mój komputer zawiesił się na 50 000 wierszy). Niektóre metody mniej obciążające procesor:

Pojedyncza kontrola niepowtarzalności

1. Sortuj według dwóch kolumn (w tym przykładzie A, B)

2. Użyj formuły, która analizuje mniej danych

   =IF(SUMPRODUCT(($A2:$A3=A2)*($B2:$B3=B2))>1,0,1) 
   

Wiele kontroli niepowtarzalności

Jeśli chcesz sprawdzić niepowtarzalność w różnych kolumnach, nie możesz polegać na dwóch rodzajach.

Zamiast,

1. Sortuj pojedynczą kolumnę (A)

2. Dodaj formułę obejmującą maksymalną liczbę rekordów dla każdej grupy. Jeśli ABC może mieć 50 wierszy, formuła będzie

   =IF(SUMPRODUCT(($A2:$A49=A2)*($B2:$B49=B2))>1,0,1)
   

Excel 2013 może liczyć oddzielnie w przestawnych. Jeśli nie ma dostępu do 2013, a jest to mniejsza ilość danych, robię dwie kopie surowych danych, aw kopii b zaznaczam obie kolumny i usuwam duplikaty. Następnie wykonaj obroty i policz swoją kolumnę b.

Możesz użyć COUNTIFS dla wielu kryteriów,

= 1 / COUNTIFS (A: A, A2, B: B, B2), a następnie przeciągnij w dół. Możesz w nim umieścić dowolną liczbę kryteriów, ale ich przetworzenie zwykle zajmuje dużo czasu.

Krok 1. Dodaj kolumnę

Krok 2. Użyj wzoru =IF(COUNTIF(C2:$C$2410,C2)>1,0,1) w pierwszym rekordzie

Krok 3. Przeciągnij go do wszystkich rekordów

Krok 4. Przefiltruj „1” w kolumnie za pomocą formuły

Możesz utworzyć dodatkową kolumnę do przechowywania unikalności, a następnie zsumować to w tabeli przestawnej.

Chodzi mi o to, że komórka C1zawsze powinna być 1. Komórka C2powinna zawierać formułę =IF(COUNTIF($A$1:$A1,$A2)*COUNTIF($B$1:$B1,$B2)>0,0,1). Skopiuj tę formułę, aby komórka C3zawierała =IF(COUNTIF($A$1:$A2,$A3)*COUNTIF($B$1:$B2,$B3)>0,0,1)i tak dalej.

Jeśli masz komórkę nagłówka, będziesz chciał przenieść je wszystkie w dół wiersza, a twoja C3formuła powinna być =IF(COUNTIF($A$2:$A2,$A3)*COUNTIF($B$2:$B2,$B3)>0,0,1).

Jeśli masz posortowane dane ... sugeruję użycie następującego wzoru

=IF(OR(A2<>A3,B2<>B3),1,0)

Jest to szybsze, ponieważ do obliczeń wykorzystuje mniej komórek.

Zwykle sortuję dane według pola, które potrzebuję, aby dokładnie policzyć, a następnie używam JEŻELI (A2 = A1,0,1); otrzymasz wtedy 1 w górnym wierszu każdej grupy identyfikatorów. Proste i nie zajmuje dużo czasu, aby obliczyć na dużych zbiorach danych.

Możesz również użyć dla kolumny pomocnika VLOOKUP. Przetestowałem i wygląda trochę szybciej niż COUNTIF.

Jeśli używasz nagłówka, a dane zaczynają się w komórce A2, w dowolnej komórce w wierszu użyj tej formuły i skopiuj we wszystkich innych komórkach w tej samej kolumnie:

=IFERROR(IF(VLOOKUP(A2;$A$1:A1;1;0)=A2;0;1);1)

Znalazłem łatwiejszy sposób na zrobienie tego. Odnosząc się do przykładu Siddarth Rout, jeśli chcę policzyć unikalne wartości w kolumnie A:

• dodaj nową kolumnę C i wypełnij C2 formułą „= 1 / LICZ.JEŻELI ($ A: $ A, A2)”
• przeciągnij formułę w dół do reszty kolumny
• przestaw z kolumną A jako etykietą wiersza i sumą {kolumna C) w wartościach, aby uzyskać liczbę unikatowych wartości w kolumnie A