Przejdź do głównej zawartości

Jak liczyć unikalne wartości na podstawie wielu kryteriów w programie Excel?

W tym artykule przedstawię kilka przykładów zliczania unikatowych wartości w oparciu o jedno lub więcej kryteriów w arkuszu. Poniższe szczegółowe kroki mogą ci pomóc.

Policz unikalne wartości na podstawie jednego kryterium

Policz unikalne wartości na podstawie dwóch podanych dat

Policz unikalne wartości na podstawie dwóch kryteriów

Policz unikalne wartości na podstawie trzech kryteriów


strzałka niebieski prawy bąbelek Policz unikalne wartości na podstawie jednego kryterium

Na przykład mam następujący zakres danych, teraz chcę policzyć unikalny produkt sprzedawany przez Tomka.

liczba dokumentów jest unikalna z wieloma kryteriami 1

Wpisz tę formułę w pustą komórkę, w której chcesz uzyskać wynik, G2, na przykład:

= SUMA (JEŻELI ("Tom" = 2 $ C $: 20 $ C $, 1 / (COUNTIFS (2 $ C $: 20 $ C $, "Tomek", 2 $ AUD 20: 2 AUD, 20 AUD 0: XNUMX AUD) ), XNUMX)), a następnie naciśnij Shift + Ctrl + Enter klucze razem, aby uzyskać poprawny wynik, patrz zrzut ekranu:

liczba dokumentów jest unikalna z wieloma kryteriami 2

Note: W powyższym wzorze „Tomek”To kryterium nazwy, na podstawie którego chcesz liczyć, C2: C20 czy komórki zawierają kryteria nazwy, A2: A20 to komórki, dla których chcesz policzyć unikatowe wartości.


strzałka niebieski prawy bąbelek Policz unikalne wartości na podstawie dwóch podanych dat

Aby obliczyć unikalne wartości między dwiema podanymi datami, na przykład chcę policzyć unikalny produkt między zakresem dat 2016/9/1 a 2016/9/30, zastosuj następującą formułę:

= SUMA (JEŻELI (D $ 2: D $ 20 <= DATA (2016, 9, 30) * (2 $ D $: 20 $ D> = DATA (2016, 9, 1)), 1 / COUNTIFS (2 $ A $ : 20 AUD, 2 AUD 20: 2 AUD, 20 USD: 2016 USD, „<=” & DATA (9, 30, 2), 20 USD D 2016: 9 USD, "> =" & DATA (1, 0, XNUMX))), XNUMX), a następnie naciśnij Shift + Ctrl + Enter klucze razem, aby uzyskać unikalny wynik, patrz zrzut ekranu:

liczba dokumentów jest unikalna z wieloma kryteriami 3

Note: W powyższym wzorze data 2016,9,1 i 2016,9,30 to data rozpoczęcia i data zakończenia, na podstawie których chcesz liczyć, D2: D20 czy komórki zawierają kryteria daty, A2: A20 to komórki, z których chcesz policzyć unikatowe wartości.


strzałka niebieski prawy bąbelek Policz unikalne wartości na podstawie dwóch kryteriów

Jeśli chcesz policzyć wyjątkowy produkt, który Tomek sprzedaje we wrześniu, pomoże Ci poniższa formuła.

Wprowadź tę formułę w pustą komórkę, aby wyświetlić wynik, na przykład H2.

= SUMA (JEŻELI (("Tom" = C $ 2: $ 20 C $) * ($ D 2 $: $ D 20 $ <= DATA (2016, 9, 30) * ($ D $ 2: $ D 20 $> = DATA ( 2016, 9, 1))), 1 / COUNTIFS (2 C $: 20 C $, "Tom", 2 AUD 20: 2 AUD, 20 AUD 2: 20 AUD, 2016 $ D $: 9 $, " <= "& DATA (30, 2, 20), 2016 $ D $: 9 $ D,"> = "& DATA (1, 0, XNUMX))), XNUMX) a następnie naciśnij Shift + Ctrl + Enter klucze razem, aby uzyskać unikalny wynik, patrz zrzut ekranu:

liczba dokumentów jest unikalna z wieloma kryteriami 4

Uwagi:

1. W powyższym wzorze „Tomek”To kryterium nazwy, 2016,9,1 i 2016,9,30 czy dwie daty, na podstawie których chcesz liczyć, C2: C20 to komórki zawierają kryteria nazwy, a D2: D20 czy komórki zawierają datę, A2: A20 to zakres komórek, dla których chcesz policzyć unikatowe wartości.

2. Jeśli musisz użyć „or”Kryteria liczenia unikalnych wartości, takie jak obliczanie produktów sprzedawanych przez Tomka lub w regionie Południowym, należy zastosować następującą formułę:

=SUM(--(FREQUENCY(IF(("Tom"=$C$2:$C$20)+("South"=$B$2:$B$20), COUNTIF($A$2:$A$20, "<"&$A$2:$A$20), ""), COUNTIF($A$2:$A$20, "<"&$A$2:$A$20))>0))i pamiętaj, aby nacisnąć Shift + Ctrl + Enter klucze razem, aby uzyskać unikalny wynik, patrz zrzut ekranu:

liczba dokumentów jest unikalna z wieloma kryteriami 5


strzałka niebieski prawy bąbelek Policz unikalne wartości na podstawie trzech kryteriów

Aby policzyć unikalny produkt według trzech kryteriów, formuła może być bardziej złożona. Powiedzmy, obliczając unikalne produkty, które Tomek sprzedaje we wrześniu oraz w regionie Północ. Zrób tak:

Wprowadź tę formułę do pustej komórki, aby wyświetlić wynik, I2, na przykład:

= SUMA (JEŻELI (("Tom" = C $ 2: $ 20 C $) * ($ D 2 $: $ D 20 $ <= DATA (2016, 9, 30)) * ($ D $ 2: $ D 20 $> = DATA (2016, 9, 1)) * ("Północ" = 2 $ B $: 20 $ B $), 1 / COUNTIFS (2 $ C $: 20 $ C $, "Tomek", 2 AUD 20: 2 AUD, 20 AUD : 2 A $, 20 $ D $ 2016: $ D 9 $, "<=" & DATA (30, 2, 20), 2016 $ D $ 9: $ D 1 $, "> =" & DATA (2, 20, 0), XNUMX B $ : XNUMX $ B $, "Północ")), XNUMX), a następnie naciśnij Shift + Ctrl + Enter klucze razem, aby uzyskać unikalny wynik, patrz zrzut ekranu:

liczba dokumentów jest unikalna z wieloma kryteriami 6

Najlepsze narzędzia biurowe

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 wyszukiwanie: Wiele kryteriów VLookup    Wiele wartości VLookup  |   Przeglądanie pionowe na wielu arkuszach   |   Wyszukiwanie rozmyte ....
Zaawansowana lista rozwijana: Szybko twórz listę rozwijaną   |  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 zakresy i kolumny ...
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ędzi12 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, Podział komórki, ...)   |   ... i więcej

Zwiększ swoje umiejętności Excela dzięki Kutools for Excel i doświadcz wydajności jak nigdy dotąd. Kutools dla programu Excel oferuje ponad 300 zaawansowanych funkcji zwiększających produktywność i oszczędzających czas.  Kliknij tutaj, aby uzyskać funkcję, której najbardziej potrzebujesz...

karta kte 201905


Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę

  • Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
  • Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
  • Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
Comments (19)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
For all the above formula an you suggest a non array formula as my data runs to 25000 rows. I need a free suggestions and not paid ones
This comment was minimized by the moderator on the site
Hi,
This is great - except I cant get it to work for what I require
I have two sheets - a Summary sheet, and another sheet containing data
The dates are dynamic - so you enter the date ranges in the Summary sheet in two cells (from B2 to D2)
When I replace DATE(2022,6,1) with B2 it comes back with "a value used in the formula is the wrong data type"
When I test with putting DATE(2022,6,1) and DATE (2022,6,30) in the from - to parts in the formula - I get 0 as the result - which is wrong.
Note: I'm in Ireland - so the date format here is dd.mm.yy - changing things doesn't fix - and adds confusion tbh
My formula is
=SUM(IF(Sheet4!$C$2:Sheet4!$C$65<=(D2)*(Sheet4!$C$2:Sheet4!$C$65>=(B2)), 1/COUNTIFS(Sheet4!$A$2:Sheet4!$A$65, Sheet4!$A$2:Sheet4!$A$65, Sheet4!$C$2:Sheet4!$C$65, "<="&D2,Sheet4!$C$2:Sheet4!$C$65, ">="&B2))),0)
Where Sheet4 contains the data, C2:C65 are cells with dates, A2:A65 are cells with project numbers - where there maybe duplicates
Any help - greatly appreciated,
Thanks
This comment was minimized by the moderator on the site
1 month2 brand name 3 executive wise4 mix party nameCount unique party name
This comment was minimized by the moderator on the site
Count Unique Values Based On four Criteria
This comment was minimized by the moderator on the site
I am trying to use this method to calculate unique customers for a particular product (where a customer may have bought multiple times, but I want unique customers). If I enter the formula but limit the range to a subset of just 5 rows that I know contain a duplicate customer, it works fine. But when I apply to the whole column, e.g. $D:$D, it calculates endlessly; if it finishes, it returns a wrong result. But now it's not even finishing and I have to end the Excel process. Is this just too costly in terms of CPU to apply to a large volume of data (e.g. 1500 rows)?
This comment was minimized by the moderator on the site
I ma getting value in point which is not possible So please help me Out

{=SUM(IF(("Regular"='Raw Data'!$G$5:$G$1785)*('Raw Data'!$D$5:$D$1785<=DATE(2019,6,30)*('Raw Data'!$D$5:$D$1785>=DATE(2019,6,1))),1/COUNTIFS('Raw Data'!$B$5:$B$1785,'Raw Data'!$B$5:$B$1785,'Raw Data'!$D$5:$D$1785,"<="&DATE(2019,6,30),'Raw Data'!$D$5:$D$1785,">="&DATE(2019,6,1))),0)}
This comment was minimized by the moderator on the site
my question.
I mean that filtered rows , and not count hidden rows.
This comment was minimized by the moderator on the site
"if count visible rows."
I mean filtered rows , and not count rows hidden.
This comment was minimized by the moderator on the site
Ffrom this article formula,
if count visible rows. how can add or edit formula?
This comment was minimized by the moderator on the site
The greater and less than date criteria is a distracting example of how to use the sumif array.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations