=ILE.NIEPUSTYCH(UNIKATOWE(A2:A9)) (po angielsku =COUNTA(UNIQUE(A2:A9))) liczy, ile różnych wartości jest w A2:A9. UNIKATOWE (UNIQUE) zwraca każdą wartość raz, a ILE.NIEPUSTYCH (COUNTA) liczy tę listę. Wymaga Excela 2021 albo Microsoft 365; starsze wersje są opisane niżej. Tabele pokazują formuły po angielsku, ale możesz w nich wpisywać formuły także po polsku, ze średnikami.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Unique list | Count | |
| 2 | Ana | Ana | 5 | |
| 3 | Ben | Ben | ||
| 4 | Ana | Cara | ||
| 5 | Cara | Dan | ||
| 6 | Ben | Eva | ||
| 7 | Dan | |||
| 8 | Ana | |||
| 9 | Eva |
=ILE.NIEPUSTYCH(UNIKATOWE(A2:A9))Osiem zamówień złożyło pięciu klientów. C2 rozlewa listę imion zwróconych przez UNIKATOWE, żebyś widział, co jest liczone, a D2 liczy ją bez potrzeby trzymania listy w arkuszu. Zmień A9 na Ana, a wynik spadnie do 4; wpisz nowe imię, a wzrośnie.
UNIKATOWE nie rozróżnia wielkości liter, więc Ana i ana liczą się jako jeden klient.
Liczenie unikatowych wartości w starszym Excelu
Excel 2019 i starsze nie mają funkcji UNIKATOWE. Klasyczna formuła to:
=SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9))
W polskim Excelu: =SUMA.ILOCZYNÓW(1/LICZ.JEŻELI(A2:A9;A2:A9)).
LICZ.JEŻELI z całym zakresem jako kryterium zwraca dla każdego wiersza, ile razy występuje wartość z tego wiersza. Imię, które występuje 3 razy, dostaje 3 w każdym swoim wierszu, więc 1/3 jest dodawane trzy razy i imię daje w sumie dokładnie 1. Kolumna B pokazuje liczbę dla każdego wiersza, a kolumna C ułamek.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Customer | Times | 1/Times | Count | |
| 2 | Ana | 3 | 0.33 | 5 | |
| 3 | Ben | 2 | 0.50 | 5.00 | |
| 4 | Ana | 3 | 0.33 | ||
| 5 | Cara | 1 | 1.00 | ||
| 6 | Ben | 2 | 0.50 | ||
| 7 | Dan | 1 | 1.00 | ||
| 8 | Ana | 3 | 0.33 | ||
| 9 | Eva | 1 | 1.00 |
=SUMA.ILOCZYNÓW(1/LICZ.JEŻELI(A2:A9;A2:A9))Każdy z trzech wierszy Any dodaje 0,33, każdy z dwóch wierszy Bena dodaje 0,50, a trzy pojedyncze imiona dodają po 1: razem 5, tyle samo co SUMA kolumny pomocniczej. Przy dziesiątkach tysięcy wierszy ta formuła jest wolna, bo LICZ.JEŻELI przegląda cały zakres raz na każdy wiersz; UNIKATOWE nie ma tego kosztu.
Różne a unikatowe: wartości, które występują tylko raz
„Unikatowe” oznacza dwa różne wyniki. Ten powyżej liczy różne wartości: każde imię raz. Drugi liczy wartości, które występują dokładnie raz, na przykład klientów, którzy zamówili tylko jeden raz. UNIKATOWE robi to, gdy jej trzeci argument, exactly_once (dokładnie_raz), ma wartość PRAWDA.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Count | Result | |
| 2 | Ana | Distinct | 5 | |
| 3 | Ben | Exactly once | 3 | |
| 4 | Ana | Exactly once, older Excel | 3 | |
| 5 | Cara | |||
| 6 | Ben | |||
| 7 | Dan | |||
| 8 | Ana | |||
| 9 | Eva |
=ILE.NIEPUSTYCH(UNIKATOWE(A2:A9;;PRAWDA))Pięciu różnych klientów, ale tylko trzech z nich, Cara, Dan i Eva, zamówiło raz. Wersja dla starszego Excela liczy wiersze, w których LICZ.JEŻELI daje dokładnie 1. Jeśli każda wartość się powtarza, UNIKATOWE z exactly_once zwraca #CALC! (w polskim Excelu #OBL!; tabele pokazują angielskie nazwy błędów), a ILE.NIEPUSTYCH liczy ten błąd jako 1; wersja z SUMA.ILOCZYNÓW daje 0.
Liczenie unikatowych wartości z warunkiem
Aby policzyć różnych klientów w jednym regionie, najpierw przefiltruj wiersze, a potem policz to, co zostało. FILTRUJ (FILTER) zostawia wiersze North, a UNIKATOWE usuwa powtórzenia.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Customer | Region | Region | Customers | |
| 2 | Ana | North | North | 3 | |
| 3 | Ben | South | South | 3 | |
| 4 | Ana | North | North, older Excel | 3 | |
| 5 | Cara | North | |||
| 6 | Ben | North | |||
| 7 | Dan | South | |||
| 8 | Ana | North | |||
| 9 | Eva | South |
=ILE.NIEPUSTYCH(UNIKATOWE(FILTRUJ(A2:A9;B2:B9=D2)))North ma pięć zamówień od trzech klientów: Any, Cary i Bena. E3 liczy South w ten sam sposób. E4 to wersja dla Excela 2019 i starszego: LICZ.WARUNKI liczy każdą parę klient i region, a warunek zostawia tylko ułamki North.
Jeśli żaden wiersz nie pasuje, FILTRUJ zwraca #CALC!, a ILE.NIEPUSTYCH liczy ten błąd jako jedną wartość: wpisz West w D2, a E2 pokaże 1, a nie 0. Objęcie formuły funkcją JEŻELI.BŁĄD nie pomoże, bo ILE.NIEPUSTYCH nie zwraca błędu. Policz zamiast tego wiersze wyniku, co przekazuje błąd dalej: =JEŻELI.BŁĄD(ILE.WIERSZY(UNIKATOWE(FILTRUJ(A2:A9;B2:B9="West")));0) zwraca 0.
Liczenie unikatowych wartości bez pustych komórek
Pusta komórka w zakresie staje się jeszcze jedną „wartością”. UNIKATOWE zwraca ją jako 0, a ILE.NIEPUSTYCH liczy to 0, więc dla Ana, pustej komórki, Ben, Ana, pustej komórki, Cara i Ben Excel daje:
=COUNTA(UNIQUE(A2:A8)) 4 three names plus the 0 for the empty cells
W polskim Excelu: =ILE.NIEPUSTYCH(UNIKATOWE(A2:A8)).
W starszej formule pusty wiersz sprawia, że LICZ.JEŻELI zwraca 0, więc 1/0 daje #DZIEL/0! (po angielsku #DIV/0!). Najpierw usuń puste komórki:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Formula | Count | |
| 2 | Ana | Skip blanks | 3 | |
| 3 | Older Excel | 3 | ||
| 4 | Ben | |||
| 5 | Ana | |||
| 6 | ||||
| 7 | Cara | |||
| 8 | Ben |
=ILE.NIEPUSTYCH(UNIKATOWE(FILTRUJ(A2:A8;A2:A8<>"")))Obie formuły liczą trzech klientów. FILTRUJ z A2:A8<>"" usuwa puste komórki, zanim zobaczy je UNIKATOWE. W starszej formule A2:A8&"" zamienia każdą pustą komórkę w pusty tekst, więc LICZ.JEŻELI nigdy nie zwraca 0, a (A2:A8<>"") nadaje tym wierszom wagę 0.
Ćwiczenie: policz produkty
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Product | Count | Result | |
| 2 | 1001 | Apple | Products | ||
| 3 | 1002 | Pear | |||
| 4 | 1003 | Apple | |||
| 5 | 1004 | Plum | |||
| 6 | 1005 | Pear | |||
| 7 | 1006 | Apple | |||
| 8 | 1007 | Plum | |||
| 9 | 1008 | Fig |
Twoja kolej: Policz, ile różnych produktów występuje w B2:B9. Wpisz formułę w E2.
Która formuła dla twojego Excela
| Co liczysz | Excel 365 / 2021 | Excel 2019 i starszy |
|---|---|---|
| Różne wartości | =ILE.NIEPUSTYCH(UNIKATOWE(A2:A9)) | =SUMA.ILOCZYNÓW(1/LICZ.JEŻELI(A2:A9;A2:A9)) |
| Wartości występujące raz | =ILE.NIEPUSTYCH(UNIKATOWE(A2:A9;;PRAWDA)) | =SUMA.ILOCZYNÓW(--(LICZ.JEŻELI(A2:A9;A2:A9)=1)) |
| Różne, z warunkiem | =ILE.NIEPUSTYCH(UNIKATOWE(FILTRUJ(A2:A9;B2:B9="North"))) | =SUMA.ILOCZYNÓW((B2:B9="North")/LICZ.WARUNKI(A2:A9;A2:A9;B2:B9;B2:B9)) |
| Różne, bez pustych | =ILE.NIEPUSTYCH(UNIKATOWE(FILTRUJ(A2:A9;A2:A9<>""))) | =SUMA.ILOCZYNÓW((A2:A9<>"")/LICZ.JEŻELI(A2:A9;A2:A9&"")) |
W tabeli przestawnej to samo bez formuły robi podsumowanie „Distinct Count” (liczba unikatowych), ale tylko wtedy, gdy tabela przestawna zostanie utworzona z zaznaczoną opcją „Dodaj te dane do modelu danych”. Aby usunąć powtórzenia zamiast je liczyć, zobacz usuwanie duplikatów.
Najczęściej zadawane pytania
Jak policzyć unikatowe wartości w Excelu?
W Excelu 365 albo 2021 użyj =ILE.NIEPUSTYCH(UNIKATOWE(A2:A9)): UNIKATOWE podaje każdą wartość raz, a ILE.NIEPUSTYCH liczy tę listę. W starszych wersjach użyj =SUMA.ILOCZYNÓW(1/LICZ.JEŻELI(A2:A9;A2:A9)).
Jak policzyć wartości, które występują tylko raz?
Ustaw trzeci argument UNIKATOWE, exactly_once, na PRAWDA: =ILE.NIEPUSTYCH(UNIKATOWE(A2:A9;;PRAWDA)). Dla Ana, Ana, Ben daje 1, bo tylko Ben występuje raz. W Excelu 2019 i starszym użyj =SUMA.ILOCZYNÓW(--(LICZ.JEŻELI(A2:A9;A2:A9)=1)).
Jak policzyć unikatowe wartości z warunkiem?
Najpierw filtruj, potem licz: =ILE.NIEPUSTYCH(UNIKATOWE(FILTRUJ(A2:A9;B2:B9="North"))) liczy różnych klientów w wierszach North. Jeśli żaden wiersz nie pasuje, ILE.NIEPUSTYCH liczy błąd #OBL! funkcji FILTRUJ jako 1, więc gdy to możliwe, użyj =JEŻELI.BŁĄD(ILE.WIERSZY(UNIKATOWE(FILTRUJ(A2:A9;B2:B9="North")));0).
Jak policzyć unikatowe wartości i pominąć puste komórki?
Usuń puste komórki przed UNIKATOWE: =ILE.NIEPUSTYCH(UNIKATOWE(FILTRUJ(A2:A9;A2:A9<>""))). W starszym Excelu pomija je =SUMA.ILOCZYNÓW((A2:A9<>"")/LICZ.JEŻELI(A2:A9;A2:A9&"")).
