Przejdź do głównej zawartości

Policz unikalne wartości za pomocą kryteriów w Excelu

Aby policzyć tylko unikatowe wartości na podstawie określonych kryteriów w innej kolumnie, można zastosować formułę tablicową opartą na funkcjach SUMA, CZĘSTOTLIWOŚĆ, PODAJ.POZYCJĘ i WIERSZ. Ten przewodnik krok po kroku pomoże Ci przejść przez najbardziej denerwujące użycie formuły.

Jak liczyć unikalne wartości z kryteriami w programie Excel?

Jak pokazano w poniższej tabeli produktów, istnieje kilka zduplikowanych produktów sprzedawanych w tym samym sklepie w różnych terminach, teraz chcę uzyskać unikalną liczbę produktów sprzedanych w sklepie A, możesz zastosować poniższą formułę.

Ogólne formuły

{=SUM(--(FREQUENCY(IF(range=criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.firstcell)+1)>0))}

Argumenty

łodzie: zakres komórek zawiera wartość, która spełnia kryteria;
kryteria: Kryteria, na podstawie których chcesz zliczać unikalne wartości;
walc: zakres komórek, z których chcesz zliczać unikalne wartości;
Vals.pierwszakomórka: pierwsza komórka zakresu, z którego chcesz zliczać unikalne wartości.

Uwaga: Ta formuła musi być wprowadzona jako formuła tablicowa. Po zastosowaniu formuły, jeśli wokół formuły są owinięte nawiasami klamrowymi, pomyślnie utworzona zostanie formuła tablicowa.

Jak korzystać z tych formuł?

1. Wybierz pustą komórkę, aby umieścić wynik.

2. Wprowadź do niego poniższą formułę, a następnie naciśnij Ctrl + Shift + Wchodzę klawisze jednocześnie, aby uzyskać wynik.

=SUM(--(FREQUENCY(IF(E3:E16=H3,MATCH(D3:D16,D3:D16,0)),ROW(D3:D16)-ROW(D3)+1)>0))

Uwagi: W tej formule E3:E16 to zakres zawierający wartość zgodną z kryteriami, H3 zawiera kryteria, D3:D16 to zakres zawierający unikalne wartości, które chcesz policzyć, a D3 to pierwsza komórka D3:D16. Możesz je zmienić według potrzeb.

Jak działa ta formuła?

{=SUM(--(FREQUENCY(IF(E3:E16=H3,MATCH(D3:D16,D3:D16,0)),ROW(D3:D16)-ROW(D3)+1)>0))}

  • IF(E3:E16=H3,MATCH(D3:D16,D3:D16,0)):
1) E3:E16=H3: Tutaj sprawdź, czy wartość A istnieje w zakresie E3:E16 i zwraca TRUE, jeśli zostanie znaleziona, zwraca FALSE, jeśli nie. Otrzymasz tablicę taką jak ta {PRAWDA;FAŁSZ;FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ;PRAWDA;FAŁSZ;}.
2) DOPASUJ(D3:D16;D3:D16,0): Funkcja PODAJ.POZYCJĘ pobiera pierwszą lokalizację każdego elementu w zakresie D3:D16 i zwraca tablicę taką jak ta {1;2;3;2;1;1;3;2;1;1;1;2;3; 2}.
  • IF({TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;},{1;2;3;2;1;1;3;2;1;1;1;2;3;2}): Teraz dla każdej wartości TRUE w tablicy 1 otrzymamy odpowiednią pozycję w tablicy 2, a dla FALSE otrzymamy FALSE. Tutaj otrzymasz nową tablicę jako {1;FAŁSZ;FAŁSZ;2;FAŁSZ;FAŁSZ;3;FAŁSZ;FAŁSZ;1;FAŁSZ;FAŁSZ;3;FAŁSZ}.
  • WIERSZ(D3:D16)-WIERSZ(D3)+1: Tutaj funkcja WIERSZ zwraca numer wiersza odwołania D3:D16 i D3, a otrzymasz {3;4;5;6;7;8;9;10;11;12;13;14;15;16} -{3}+1.
  • Każda liczba w tablicy odejmuje liczbę 3, następnie dodaje 1 i ostatecznie zwraca {1;2;3;4;5;6;7;8;9;10;11;12;13;14}.
  • FREQUENCY({1;FALSE;FALSE;2;FALSE;FALSE;3;FALSE;FALSE;1;FALSE;FALSE;3;FALSE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14}): Tutaj funkcja CZĘSTOTLIWOŚĆ zwraca częstotliwość każdej liczby w podanej tablicy: {2;1;2;0;0;0;0;0;0;0;0;0;0;0;XNUMX}.
  • =SUM(--({2;1;2;0;0;0;0;0;0;0;0;0;0;0}>0)):
1) {2;1;2;0;0;0;0;0;0;0;0;0;0;0}>0: Każda liczba w tablicy jest porównywana z 0 i zwraca TRUE, jeśli jest większa od 0, w przeciwnym razie zwraca FALSE. Otrzymasz tablicę TRUE FALSE taką jak ta {PRAWDA;PRAWDA;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ};
2) --{PRAWDA;PRAWDA;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ}: Te dwa znaki minus konwertują „PRAWDA” na 1 i „FAŁSZ” na 0. Tutaj otrzymasz nową tablicę jako{1;1;1;0;0;0;0;0;0;0;0;0 ;0;0}.
3) SUM{1;1;1;0;0;0;0;0;0;0;0;0;0;0}: Funkcja SUMA sumuje wszystkie liczby w tablicy i zwraca wynik końcowy jako 3.

Powiązane funkcje

Funkcja suma Excel
Funkcja suma Excel dodaje wartości

Funkcja CZĘSTOTLIWOŚĆ Excel
Funkcja CZĘSTOTLIWOŚĆ programu Excel oblicza, jak często wartości występują w zakresie wartości, a następnie zwraca pionową tablicę liczb.

Funkcja Excel IF
Funkcja Excel IF wykonuje prosty test logiczny, który zależy od wyniku porównania i zwraca jedną wartość, jeśli wynik jest PRAWDA, lub inną wartość, jeśli wynik jest FAŁSZ.

Funkcja Excel MATCH
Funkcja Excel PODAJ.POZYCJĘ wyszukuje określoną wartość w zakresie komórek i zwraca względną pozycję tej wartości.

Funkcja Excel WIERSZ
Funkcja Excel WIERSZ zwraca numer wiersza odwołania.

Powiązane wzory

Policz liczbę widocznych wierszy na filtrowanej liście
W tym samouczku wyjaśniono, jak zliczyć liczbę widocznych wierszy na przefiltrowanej liście w programie Excel za pomocą funkcji SUMA CZĘŚCIOWA.

Policz unikalne wartości w zakresie
W tym samouczku wyjaśniono, jak liczyć tylko unikalne wartości wśród duplikatów na liście w programie Excel z określonymi formułami.

Policz widoczne wiersze z kryteriami
Ten samouczek zawiera szczegółowe kroki, które pomogą Ci zliczyć widoczne wiersze z kryteriami.

Użyj LICZ.JEŻELI w nieciągłym zakresie
W tym przewodniku krok po kroku pokazano, jak używać funkcji countif w nieciągłym zakresie w programie Excel.

Najlepsze narzędzia biurowe

Kutools dla programu Excel - pomaga wyróżnić się z tłumu

🤖 Pomocnik AI Kutools: Zrewolucjonizuj analizę danych w oparciu o: Inteligentne wykonanie   |  Wygeneruj kod  |  Twórz niestandardowe formuły  |  Analizuj dane i generuj wykresy  |  Wywołaj funkcje Kutools...
Popularne funkcje: Znajdź, wyróżnij lub zidentyfikuj duplikaty  |  Usuń puste wiersze  |  Łącz kolumny lub komórki bez utraty danych  |  Okrągły bez wzoru ...
Super VLookup: Wiele kryteriów  |  Wiele wartości  |  W wielu arkuszach  |  Wyszukiwanie rozmyte...
Adw. Lista rozwijana: Łatwa lista rozwijana  |  Zależna lista rozwijana  |  Lista rozwijana wielokrotnego wyboru...
Menedżer kolumn: Dodaj określoną liczbę kolumn  |  Przesuń kolumny  |  Przełącz stan widoczności ukrytych kolumn  |  Porównaj kolumny z Wybierz Te same i różne komórki ...
Polecane funkcje: Fokus siatki  |  Widok projektu  |  Duży pasek formuły  |  Menedżer skoroszytów i arkuszy | Biblioteka zasobów (Automatyczny tekst)  |  Selektor dat  |  Połącz arkusze  |  Szyfruj/odszyfruj komórki  |  Wysyłaj e-maile według listy  |  Super filtr  |  Specjalny filtr (filtruj pogrubienie/kursywa/przekreślenie...) ...
15 najlepszych zestawów narzędzi:  12 Tekst Tools (Dodaj tekst, Usuń znaki ...)  |  50 + Wykres rodzaje (Wykres Gantta ...)  |  40+ Praktyczne Wzory (Oblicz wiek na podstawie urodzin ...)  |  19 Wprowadzenie Tools (Wstaw kod QR, Wstaw obraz ze ścieżki ...)  |  12 Konwersja Tools (Liczby na słowa, Przeliczanie walut ...)  |  7 Połącz i podziel Tools (Zaawansowane wiersze łączenia, Podziel komórki Excela ...)  |  ... i więcej

Kutools dla programu Excel oferuje ponad 300 funkcji, Pewność, że to, czego potrzebujesz, jest w zasięgu jednego kliknięcia...

Opis

Karta Office - Włącz czytanie i edycję na kartach w Microsoft Office (w tym Excel)

  • Jedna sekunda, aby przełączać się między dziesiątkami otwartych dokumentów!
  • Zmniejsz liczbę kliknięć myszą każdego dnia, pożegnaj się z dłonią myszy.
  • Zwiększa produktywność o 50% podczas przeglądania i edytowania wielu dokumentów.
  • Wprowadza wydajne karty do pakietu Office (w tym programu Excel), podobnie jak przeglądarki Chrome, Edge i Firefox.
Comments (1)
No ratings yet. Be the first to rate!
Justine
Justine
This comment was minimized by the moderator on the site
Looking for the same formula but with one more criteria... I tried adding AND() after the IF() to have my two criterias but it didn't work. Do you have a solution?
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations