Policz unikalne wartości w programie Excel za pomocą funkcji LICZ.JEŻELI

W tym samouczku dowiesz się, jak zliczać unikatowe wartości w programie Excel za pomocą formuł (funkcje LICZ.JEŻELI i SUMAPRODUKT).

Jak liczyć unikalne wartości w Excelu?

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

Na potrzeby tego samouczka zakres A2:A10 będę nazywał NAMES. W przyszłości będziemy używać tego nazwanego zakresu we wzorach.

Zobacz też: Jak tworzyć nazwane zakresy w programie Excel.

W tym zestawie danych występuje powtórzenie z zakresu NAMES. Aby uzyskać liczbę unikalnych nazw z tego zestawu danych (A2:A10), możemy użyć kombinacji funkcji LICZ.JEŻELI i SUMAPRODUKT, jak pokazano poniżej:

=PROD.SUMA(1/LICZ.JEŻELI(NAZWY,NAZWY))

Jak działa ta formuła?

Rozłóżmy tę formułę, aby lepiej zrozumieć:

• LICZ.JEŻELI(NAZWY,NAZWY)
  • Ta część formuły zwraca tablicę. W powyższym przykładzie byłoby to {2;2;3;1;3;1;2;3;2}. Liczby tutaj wskazują, ile razy wartość występuje w danym zakresie komórek.
    Na przykład imię to Bob, które występuje dwa razy na liście, dlatego zwróci liczbę 2 dla Boba. Podobnie, Steve występuje trzykrotnie, a zatem 3 jest zwracane dla Steve'a.
• 1/LICZ.JEŻELI(NAZWY,NAZWY)
  • Ta część formuły zwróci tablicę - {0.5;0.5;0.3333333333333333;1;0.3333333333333333;1;0.5;0.3333333333333333;0.5}
    Ponieważ podzieliliśmy 1 przez tablicę, zwraca tę tablicę.
    Na przykład pierwszy element tablicy zwrócony powyżej to 2. Kiedy 1 jest dzielone przez 2, zwraca 0,5.
• SUMPRODUCT(1/COUNTIF(NAZWY,NAZWY))
  • SUMPRODUCT po prostu dodaje wszystkie te liczby. Zauważ, że jeśli Bob występuje dwa razy na liście, powyższa tablica zwraca .5 wszędzie tam, gdzie imię Boba pojawiło się na liście. Podobnie, ponieważ Steve pojawia się na liście trzykrotnie, tablica zwraca .33333333 za każdym razem, gdy pojawia się imię Steve. Gdy dodamy liczby dla każdej nazwy, zawsze zwróci 1. A jeśli dodamy wszystkie liczby, zwróci całkowitą liczbę unikalnych nazw na liście.

Ta formuła działa dobrze, dopóki w zakresie nie będzie żadnych pustych komórek. Ale jeśli masz puste komórki, zwróci #DIV/0! błąd.

Jak obchodzić się z pustymi komórkami?

Najpierw zrozummy, dlaczego zwraca błąd, gdy w zakresie znajduje się pusta komórka. Załóżmy, że mamy zestaw danych, jak pokazano poniżej (komórka A3 jest pusta):

Teraz, jeśli użyjemy tej samej formuły, której użyliśmy powyżej, część LICZ.JEŻELI zwraca tablicę {2;0;3;1;3;1;2;3;1}. Ponieważ w komórce A3 nie ma tekstu, jego liczba jest zwracana jako 0.

A ponieważ dzielimy 1 przez całą tablicę, zwraca #DIV/0! błąd.

Aby obsłużyć ten błąd podziału w przypadku pustych komórek, użyj poniższej formuły:

=PRODUKTSUMA((1/LICZ.JEŻELI(NAZWY;NAZWY&””)))

Jedną ze zmian wprowadzonych w tej formule jest część kryteriów funkcji LICZ.JEŻELI. Użyliśmy NAMES&”” zamiast NAMES. W ten sposób formuła zwróci liczbę pustych komórek (wcześniej zwracała 0 w przypadku pustej komórki).

UWAGA: Ta formuła zliczy puste komórki jako unikalną wartość i zwróci ją w wyniku.

W powyższym przykładzie wynik powinien wynosić 5, ale zwraca 6, ponieważ pusta komórka jest liczona jako jedna z unikalnych wartości.

Oto formuła, która zajmuje się pustymi komórkami i nie liczy ich w końcowym wyniku:

=PRODUKTSUMA((NAZWY””)/LICZ.JEŻELI(NAZWY,NAZWY&””))

W tej formule zamiast 1 jako licznika użyliśmy NAZW””. Zwraca tablicę TRUE i FALSE. Zwraca FALSE za każdym razem, gdy jest pusta komórka. Ponieważ w obliczeniach PRAWDA równa się 1, a FAŁSZ równa się 0, puste komórki nie są liczone, ponieważ licznik wynosi 0 (FAŁSZ).

Teraz, gdy mamy gotowy podstawowy szkielet formuły, możemy pójść o krok dalej i policzyć różne typy danych.

Jak liczyć unikalne wartości w Excelu, które są tekstem

Użyjemy tej samej koncepcji omówionej powyżej, aby utworzyć formułę, która będzie zliczać tylko wartości tekstowe, które są unikalne.

Oto formuła, która zlicza unikalne wartości tekstowe w programie Excel:

=PRODUKTSUMA((CZYTEKST(NAZWY)/LICZ.JEŻELI(NAZWY,NAZWY&””)))

Jedyne, co zrobiliśmy, to użycie formuły CZY.TEKST(NAZWY) jako licznika. Zwraca TRUE, gdy komórka zawiera tekst, i FALSE, jeśli go nie ma. Nie zliczy pustych komórek, ale zliczy komórki, które mają pusty ciąg („”).

Jak liczyć unikalne wartości w Excelu, które są numeryczne?

Oto formuła, która zlicza unikalne wartości liczbowe w Excelu

=SUMPRODUCT((CZY.LICZBA(NAZWY))/LICZ.JEŻELI(NAZWY,NAZWY&””))

Tutaj używamy ISNUMBER(NAMES) jako licznika. Zwraca TRUE, jeśli komórka zawiera numeryczny typ danych, a FALSE, jeśli nie. Nie liczy pustych komórek.