Policz unikalne wartości w programie Excel
Aby policzyć tylko unikalne wartości w programie Excel, możemy przyjąć kilka opcji, a najważniejsze z nich pokazano poniżej.
- Policz unikalne wartości za pomocą funkcji Sum i Countif.
- Policz unikalne wartości za pomocą funkcji SUMPRODUCT i Countif.
Omówmy teraz szczegółowo każdą z metod wraz z przykładem -
# 1 Policz wartości unikalne za pomocą funkcji SUMA i LICZ.JEŻELI
Załóżmy, że pracujesz jako kierownik sprzedaży i masz przed sobą dane dotyczące sprzedaży. Wielu klientów kupiło ten produkt w różnych przedziałach czasowych w miesiącu. Potrzebujesz łącznej liczby klientów w danym miesiącu.
W tym przykładzie omówię metodę zliczania unikatowych wartości za pomocą funkcji SUMA w programie Excel z funkcją LICZ.JEŻELI.
- Krok 1: Skopiuj poniższe dane do pliku Excela.
- Krok 2: Zastosuj poniższą formułę w komórce E3, aby uzyskać łączne unikalne wartości.
Pozwólcie, że omówię szczegółowo wzór.
Formuła, której tutaj użyłem, to:
Jeśli zamkniesz formułę, zostanie ona otoczona nawiasami klamrowymi. Nie weszłam do niego; jest to raczej wskazanie, że jest to formuła tablicowa.
Gdy skończysz formułę, zanim naciśniemy enter, musimy użyć klawisza:
Ctrl + Shift + Enter spowoduje automatyczne wprowadzenie tych nawiasów klamrowych.
Jest to trochę nowa formuła, jeśli po raz pierwszy pracujesz z formułami tablicowymi w programie Excel. Podzielę formułę na trzy części.
Najpierw wyjaśnię formułę LICZ.JEŻELI. Wybierz część wzoru licznika i naciśnij klawisz F9.
Teraz naciśnij klawisz F9.
Teraz dzielimy powyższe wartości przez liczbę 1.
Funkcja Now Sum dodaje wszystkie liczby, które pojawiają się na powyższym obrazku i daje sumę jako 12. Dlatego całkowita liczba unikalnych wartości na liście wynosi 12.
Jak działa kalkulacja?
- Jeśli wartości występują na liście dwa razy, to jest to ie, czyli 0,5. Jeśli wartość występuje na liście trzy razy, to jest to 1/3, czyli 0,3333.
- Na naszej liście pierwsze imię to Ruturaj, które pojawia się na liście trzy razy, więc nasz wynik pokazuje 0,33333333 jako wartość.
- Nasze drugie imię Kamal pojawia się tylko raz, a formuła brzmi tak: 1/1 I .e.1 tylko.
- Ta funkcja LICZ.JEŻELI i SUMA może dać nam całkowitą liczbę unikatowych wartości na liście.
# 2 Policz wartości unikalne za pomocą funkcji SUMPRODUCT i COUNTIF
Weź te same dane z poprzedniego przykładu.
W tym przykładzie omówię metodę zliczania unikatowych wartości za pomocą funkcji SUMA.JEŻELI z funkcją LICZ.JEŻELI. To nie jest formuła tablicowa, której zamierzam użyć w tym przykładzie, a raczej normalna formuła, której używam.
- Krok 1: Skopiuj poniższe dane do pliku Excela.
- Krok 2: Zastosuj poniższą formułę w komórce E6, aby uzyskać łączne unikalne wartości.
Pozwólcie, że omówię szczegółowo wzór.
Formuła, której tutaj użyłem, to:
W naszym poprzednim przykładzie użyłem formuły Array, tj. Zamknąłem formułę za pomocą Ctrl + Shift + Enter.
Jeśli podzielę formułę za pomocą klawisza F9, działa dokładnie tak samo, jak poprzednia.
Teraz funkcja SUMPRODUCT dodaje wszystkie liczby, które pojawiły się na powyższym obrazku i daje sumę jako 12. Dlatego całkowita liczba unikalnych wartości na liście wynosi 12.
Obsługa pustych w zakresie
Jeśli lista wartości zawiera dowolną z pustych komórek, formuła zwraca wynik jako błąd w programie Excel, tj. # DIV / 0 !.
W powyższym wierszu obrazu liczba 10 to pusty wiersz. Ponieważ istnieje pusty wiersz, formuła zwróciła wynik jako błąd, tj. # DIV / 0 !.
Możemy poradzić sobie z tego rodzaju błędami, wstawiając do niego nic („”).
Na końcu formuły countif nie potrzebujemy nic, używając symbolu ampersand, a następnie odejmujemy cały wynik o -1, ponieważ nawet pusta komórka jest traktowana przez formułę jako unikatowa wartość.
Uwaga: jeśli są dwa puste wiersze, możemy użyć -2; jeśli są trzy puste wiersze, możemy użyć -3 i tak dalej.
Rzeczy do zapamiętania
- Musimy użyć Ctrl + Shift + Enter, aby zamknąć formułę w przypadku formuł tablicowych. Nawet kiedy edytujemy formułę, nie możemy po prostu zamknąć nawiasu; musimy Ctrl + Shift + Enter.
- Możemy uzyskać unikalną listę, usuwając zduplikowane wartości z listy.
- Formuły tablicowej nie można zastosować do scalonych komórek.
