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
- Policz unikalne wartości liczbowe na podstawie kryteriów w Excel 365
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:
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:
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 *:
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:
- 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:
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łę:
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:
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
Kutools dla programu Excel oferuje ponad 300 funkcji, Pewność, że to, czego potrzebujesz, jest w zasięgu jednego kliknięcia...
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.
