Note: The other languages of the website are Google-translated. Back to English

Policz unikalne wartości liczbowe na podstawie kryteriów w Excelu

W arkuszu programu Excel może wystąpić problem polegający na zliczaniu unikalnych wartości liczbowych na podstawie określonego warunku. Na przykład, jak mogę policzyć unikalne wartości ilości produktu „T-shirt” z raportu, jak pokazano na zrzucie ekranu? W tym artykule pokażę kilka formuł, aby osiągnąć to zadanie w Excelu.


Policz unikatowe wartości liczbowe na podstawie kryteriów w programie Excel 2019, 2016 i wcześniejszych

W programie Excel 2019 i wcześniejszych wersjach można łączyć funkcje SUMA, CZĘSTOTLIWOŚĆ i JEŻELI, aby utworzyć formułę do liczenia unikalnych wartości na podstawie kryteriów, ogólna składnia to:

{=SUM(--(FREQUENCY(IF(criteria_range=criteria,range),range)>0))}
Array formula, should press Ctrl + Shift + Enter keys together.
  • criteria_range: zakres komórek zgodny z określonymi kryteriami;
  • criteria: Warunek, na podstawie którego chcesz zliczać unikatowe wartości;
  • range: Zakres komórek z unikalnymi wartościami do zliczenia.

Zastosuj poniższą formułę do pustej komórki i naciśnij Ctrl + Shift + Enter klucze, aby uzyskać poprawny wynik, patrz zrzut ekranu:

=SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))


Wyjaśnienie wzoru:

=SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))

  • IF(A2:A12=E2,C2:C12): Ta funkcja JEŻELI zwraca wartość z kolumny C, jeśli produktem w kolumnie A jest „T-shirt”, wynikiem jest tablica w następujący sposób: {FALSE;300;500;FALSE;400;FALSE;300;FALSE;FALSE; FAŁSZ;350}.
  • FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)= FREQUENCY({FALSE;300;500;FALSE;400;FALSE;300;FALSE;FALSE;FALSE;350},{200;300;500;350;400;450;300;550;200;260;350}): Funkcja CZĘSTOTLIWOŚĆ jest używana do zliczania wszystkich wartości numerycznych na liście tablicy i zwracania wyniku w następujący sposób: {0;2;1;1;1;0;0;0;0;0;0;0} .
  • --(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0)=--({0;2;1;1;1;0;0;0;0;0;0;0}>0): Sprawdź, czy każda wartość w tablicy jest większa niż 0 i uzyskaj wynik w następujący sposób: {FAŁSZ;PRAWDA;PRAWDA;PRAWDA;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ}. A następnie podwójny znak ujemny zamienia PRAWDA i FAŁSZ na jedynki i zera, zwracając taką tablicę: {1;0;0;1;1;1;1;0;0;0;0;0}.
  • SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))=SUM({0;1;1;1;1;0;0;0;0;0;0;0}): Na koniec użyj funkcji SUMA, aby dodać te wartości i uzyskać łączną liczbę: 4.

Porady:

Jeśli chcesz policzyć unikalne wartości na podstawie więcej niż jednego warunku, wystarczy dodać do formuły inne kryteria za pomocą znaku *:

=SUM(--(FREQUENCY(IF((criteria,_range1=criteria1)* (criteria,_range2=criteria2)*…,range),range)>0))

Policz unikalne wartości liczbowe na podstawie kryteriów w Excel 365

W programie Excel 365 połączenie funkcji WIERSZE, UNIKATOWE i FILTR może pomóc w liczeniu unikalnych wartości liczbowych na podstawie kryteriów, ogólna składnia to:

=ROWS(UNIQUE(FILTER(range,criteria_range=criteria)))
  • range: Zakres komórek z unikalnymi wartościami do zliczenia.
  • criteria_range: zakres komórek zgodny z określonymi kryteriami;
  • criteria: Warunek, na podstawie którego chcesz zliczać unikatowe wartości;

Skopiuj lub wprowadź następującą formułę do komórki i naciśnij Wchodzę klawisz, aby zwrócić wynik, zobacz zrzut ekranu:

=ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))


Wyjaśnienie wzoru:

=ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))

  • A2:A12=E2: to wyrażenie sprawdza, czy wartość w komórce E2 należy do zakresu A2:A12, i otrzymuje następujący wynik: {FAŁSZ;PRAWDA;PRAWDA;FAŁSZ;PRAWDA;FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA}.
  • FILTER(C2:C12,A2:A12=E2): Funkcja CZĘSTOTLIWOŚĆ jest używana do zliczania wszystkich wartości numerycznych na liście tablicy i zwracania wyniku w następujący sposób: {0;2;1;1;1;0;0;0;0;0;0;0} .
  • UNIQUE(FILTER(C2:C12,A2:A12=E2))=UNIQUE({300;500;400;300;350}): Tutaj funkcja UNIQUE jest używana do wyodrębnienia unikalnych wartości z tablicy list, aby uzyskać następujący wynik: {300;500;400;350}.
  • ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))=ROWS({300;500;400;350}): Funkcja WIERSZE zwraca liczbę wierszy na podstawie zakresu komórek lub tablicy, więc wynik to: 4.

Porady:

1. Jeśli dopasowana wartość nie istnieje w zakresie danych, otrzymasz wartość błędu, aby zastąpić wartość błędu 0, zastosuj następującą formułę:

=IFERROR(ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2))), 0)

2. Aby policzyć unikalne wartości na podstawie więcej niż jednego warunku, wystarczy dodać do formuły inne kryteria ze znakiem * w następujący sposób:

=ROWS(UNIQUE(FILTER(range,(criteria_range1=criteria1)* (criteria_range2=criteria2)*…)))

Zastosowana funkcja względna:

  • SUMA:
  • Funkcja Excel SUMA zwraca sumę podanych wartości.
  • CZĘSTOTLIWOŚĆ:
  • Funkcja CZĘSTOTLIWOŚĆ oblicza, jak często wartości występują w zakresie wartości, a następnie zwraca pionową tablicę liczb.
  • WYDZIWIANIE:
  • Funkcja WIERSZE zwraca liczbę wierszy w danym odwołaniu lub tablicy.
  • JEDYNY W SWOIM RODZAJU:
  • Funkcja UNIQUE zwraca listę unikalnych wartości z listy lub zakresu.
  • FILTR:
  • Funkcja FILTER pomaga filtrować zakres danych na podstawie zdefiniowanych kryteriów.

Więcej artykułów:

  • Policz unikalne wartości liczbowe lub daty w kolumnie
  • Przypuśćmy, że masz listę liczb, które zawierają kilka duplikatów, teraz chcesz policzyć liczbę unikalnych wartości lub wartości pojawiają się tylko raz na liście, jak pokazano na poniższym zrzucie ekranu. W tym artykule omówimy kilka przydatnych formuł do szybkiego i łatwego rozwiązywania tego zadania w programie Excel.
  • Policz wszystkie dopasowania / duplikaty między dwiema kolumnami
  • Porównanie dwóch kolumn danych i policzenie wszystkich dopasowań lub duplikatów w tych dwóch kolumnach może być częstym zadaniem dla większości z nas. Na przykład masz dwie kolumny nazw, niektóre nazwy pojawiają się zarówno w pierwszej, jak i drugiej kolumnie, teraz chcesz policzyć wszystkie dopasowane nazwy (dopasowania znajdujące się w dowolnym miejscu w dwóch kolumnach) między dwiema kolumnami, jak pokazano na zrzucie ekranu, ten samouczek wprowadzi kilka formuł na osiągnięcie tego celu w Excelu.
  • Policz liczbę komórek równa jednej z wielu wartości
  • Przypuśćmy, że mam listę produktów w kolumnie A, teraz chcę uzyskać całkowitą liczbę określonych produktów Apple, Grape i Lemon, które są wymienione w zakresie C4: C6 z kolumny A, jak pokazano na poniższym zrzucie ekranu. Zwykle w programie Excel proste funkcje LICZ.JEŻELI i LICZ.JEŻELI nie będą działać w tym scenariuszu. W tym artykule omówię, jak szybko i łatwo rozwiązać to zadanie za pomocą kombinacji funkcji SUMPRODUCT i LICZ.JEŻELI.

Najlepsze narzędzia biurowe

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

Chcesz szybko i perfekcyjnie zakończyć swoją codzienną pracę? Kutools dla programu Excel oferuje 300 zaawansowanych zaawansowanych funkcji (Łącz skoroszyty, sumuj według koloru, dziel zawartość komórek, konwertuj daty itd.) i oszczędzaj 80% czasu.

  • Zaprojektowany dla 1500 scenariuszy pracy, pomaga rozwiązać 80% problemów z Excelem.
  • Zmniejsz tysiące kliknięć klawiatury i myszy każdego dnia, odciąż zmęczone oczy i dłonie.
  • Zostań ekspertem Excela w 3 minuty. Nie musisz już pamiętać żadnych bolesnych formuł i kodów VBA.
  • 30-dniowy nieograniczony bezpłatny okres próbny. 60-dniowa gwarancja zwrotu pieniędzy. Bezpłatna aktualizacja i wsparcie przez 2 lata.
Wstążka programu Excel (z zainstalowanym Kutools dla programu Excel)

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 zakładki do pakietu Office (w tym Excel), podobnie jak Chrome, Firefox i nowy Internet Explorer.
Zrzut ekranu programu Excel (z zainstalowaną kartą Office)
Sortuj komentarze według
Komentarze (0)
Brak ocen. Oceń jako pierwszy!
Nie ma tu jeszcze żadnych komentarzy
Zostaw swój komentarz
Publikowanie jako gość
×
Oceń ten post:
0   Postacie
Sugerowane lokalizacje

Bądż na bieżąco

Prawa autorskie © 2009 - www.extendoffice.com. | Wszelkie prawa zastrzeżone. Zasilany przez ExtendOffice, | Mapa strony
Microsoft i logo Office są znakami towarowymi lub zastrzeżonymi znakami towarowymi Microsoft Corporation w Stanach Zjednoczonych i / lub innych krajach.
Chronione przez Sectigo SSL