W tym artykule dowiemy się, jak liczyć wiersze, jeśli spełnia wiele kryteriów w programie Excel.
Scenariusz:
Mówiąc prościej, podczas pracy z tabelami danych czasami musimy policzyć komórki, w których więcej niż dwa zakresy spełniają kryteria. Można to zrobić za pomocą wzoru wyjaśnionego poniżej.
Jak rozwiązać problem?
W przypadku tego problemu będziemy musieli użyć funkcji SUMPRODUCT. Tutaj otrzymujemy dwa zakresy i potrzebujemy liczby wierszy spełniającej 3 kryteria. Funkcja SUMPRODUCT zwraca SUMA odpowiednich wartości PRAWDA (jako 1) i ignoruje wartości odpowiadające wartościom FAŁSZ (jako 0) w zwracanej pojedynczej tablicy, w której warunki były PRAWDA.
Wzór ogólny:
| = PRODUKT SUMA ( ( rng_1 op_1 kryt_1 ) + 0 , ( rng_2 op_2 kryt_1 ) + 0 , rng_2 op_2 kryt_1 ) + 0 ) |
rng : zakres do wyszukania
crit : kryteria do zastosowania
op : operator kryteriów, warunek podany jako operator pomiędzy zakresem a kryteriami
+0 : konwertuje wartości logiczne na binarne (0 i 1).
Przykład:
Wszystko to może być trudne do zrozumienia. Przetestujmy więc tę formułę, uruchamiając ją na poniższym przykładzie.
Tutaj musimy znaleźć liczbę wierszy wymienionych w zakresie mającym 3 warunki. Tutaj mamy listę spotkań dyplomatycznych, które odbyły się między Indiami a USA od 2014 roku. Tabela przedstawia prezydenta / premiera z etykietą kraju i rokiem. Tabela jest również podzielona na części reprezentujące kraj pochodzenia oraz listę krajów odwiedzających.
Warunki wymienione poniżej:
Prezydent USA „Barack Obama odwiedził Indie z problemami poniżej 2.
Użyj formuły:
| = PRODUKT SUMA ( ( C4:C10 = "Barack Obama" ) + 0 , ( F4:F10 = "Indie" ) + 0 , ( G4:G10 < 2 ) + 0 ) ) |
C4:C10="Barack Obama" : Prezydent pasujący do "Baracka Obamy" na liście gości.
F4:F10="Indie" : kraj gospodarza odpowiadający "Indii".
G4:G10<2 : problemy mniej niż dwa.
+0 : konwertuje wartości logiczne na binarne (0 i 1).
Tutaj zakres jest podany jako odwołanie do komórki. Naciśnij Enter, aby uzyskać liczbę.
Jak widać, pewnego razu prezydent USA Barack Obama odwiedził Indie, co miało miejsce w 2015 roku. Pokazuje to, że formuła wyodrębnia liczbę dopasowań w odpowiedniej tablicy. Ponieważ jest 1 przypadek, gdy prezydent USA „Barack Obama” odwiedził Indie, gdzie kwestie również są równe 1, czyli mniej niż 2
Z równymi kryteriami:
Powyższy przykład był łatwy. Aby było to interesujące, policzymy, ile razy Stany Zjednoczone gościły Indie począwszy od 2014 roku według danych.
Warunki wymienione poniżej:
Indie hostowane przez USA z problemami są równe 2.
Użyj formuły:
| =PROD.SUMA ( ( F4:F10 = "USA" ) + 0 , ( D4:D10 = "Indie" ) + 0 , ( G4:G10 = 2 ) + 0 ) |
F4:F10="US" : kraj hosta pasujący do "USA".
D4:D10="Indie" : kraj odwiedzający pasujący do "Indii".
G4:G10=2 : liczba emisji równa się dwóm.
+0 : konwertuje wartości logiczne na binarne (0 i 1).
Tutaj zakres jest podany jako odwołanie do komórki. Naciśnij Enter, aby uzyskać liczbę.
Jak widać, są 2 razy, w których Stany Zjednoczone gościły Indie, a problemy są równe dwóm. Pokazuje to, że formuła wyodrębnia liczbę dopasowanych czasów w odpowiedniej tablicy. Ponieważ jest 5 razy, kiedy Stany Zjednoczone gościły Indie, ale problemy były 1 lub 3, ale tutaj potrzebujemy, aby problemy były dopasowane do 2.
Z kryteriami większymi niż:
Tutaj, żeby było ciekawiej, policzymy, ile razy prezydent USA „Donald Trump” gościł indyjskiego premiera począwszy od 2014 roku według danych.
Warunki wymienione poniżej:
Prezydent USA „Donald Trump” gościł Indie z problemami większymi niż 1.
Użyj formuły:
| =PROD.SUMA ( ( C4:C10 = "Donald Trump" ) + 0 , ( F4 : F10 = "Indie" ) + 0 , ( G4:G10 >1 ) + 0 ) |
F4:F10="US" : prezydent gospodarz pasujący do "Donald Trump".
D4:D10="Indie" : kraj odwiedzający pasujący do "Indii".
G4:G10=2 : liczba emisji równa się dwóm.
+0 : konwertuje wartości logiczne na binarne (0 i 1).
Tutaj zakres jest podany jako odwołanie do komórki. Naciśnij Enter, aby uzyskać liczbę.
Jak widać, kiedyś prezydent USA „Donald Trump” gościł Indie i emisje większe niż dwa. Pokazuje to, że formuła wyodrębnia liczbę dopasowanych czasów w odpowiedniej tablicy. Ponieważ są 2 razy, kiedy prezydent USA „Donald Trump” gościł Indie, ale problemy wynosiły 1 lub 3, ale tutaj potrzebujemy, aby problemy były większe niż 1, czyli 3 kłamstwa w roku 2019.
Z kwestiami nieuwzględnionymi w Kryteriach:
Tutaj, aby było to łatwe i wygodne do zrozumienia, policzymy, ile razy w sumie prezydent USA odwiedził Indie począwszy od 2014 roku według danych.
Warunki wymienione poniżej:
Prezydent USA odwiedził Indie łącznie od 2014 roku.
Użyj formuły:
| =PRODUKCJASUMA((F4:F10="Indie")+0,(D4:D10="USA")+0) |
F4:F10="US" : kraj hosta pasujący do "USA".
D4:D10="Indie" : kraj odwiedzający pasujący do "Indii".
G4:G10=2 : liczba emisji równa się dwóm.
+0 : konwertuje wartości logiczne na binarne (0 i 1).
Tutaj zakres jest podany jako odwołanie do komórki. Naciśnij Enter, aby uzyskać liczbę.
Jak widać, 2 razy Stany Zjednoczone odwiedziły Indie i wydali więcej niż dwa. Pokazuje to, że formuła wyodrębnia liczbę dopasowanych czasów w odpowiedniej tablicy. Jak kiedyś prezydent USA „Barack Obama” odwiedził Indie w 2015 roku i raz prezydent USA „Donald Trump” odwiedził Indie w 2020 roku.
Jako kryteria możesz też stosować zakresy. Policz komórki, w których 2 zakresy spełniają kryteria. Dowiedz się więcej o Countif z SUMPRODUCT w programie Excel tutaj.
Oto kilka uwag obserwacyjnych pokazanych poniżej.
Uwagi:
- Formuła działa tylko z liczbami.
- Tablice w formule muszą mieć taką samą długość, ponieważ formuła zwraca błąd, gdy nie.
- Funkcja SUMPRODUCT traktuje wartości nieliczbowe jako 0s.
- Funkcja SUMPRODUCT traktuje wartość logiczną TRUE jako 1 i False jako 0.
- Argument tablica musi mieć ten sam rozmiar, w przeciwnym razie funkcja zwróci błąd.
- Funkcja SUMPRODUCT zwraca sumę po pobraniu poszczególnych produktów w odpowiedniej tablicy.
- Operatory takie jak równa się ( = ), mniejszy niż równy ( <= ), Lepszy niż ( > ) lub nie równa się ( ) można wykonać w ramach zastosowanej formuły, wyłącznie z liczbami.
Mam nadzieję, że ten artykuł na temat liczenia wierszy spełniających wiele kryteriów w programie Excel jest objaśniający. Więcej artykułów na temat formuł liczenia znajdziesz tutaj. Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku. Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na stronie e-mail
Znajdź ostatni wiersz danych z liczbami w Excelu : W zakresie wartości tekstowych znajdź ostatnie dane w programie Excel.
Jak korzystać z funkcji SUMA PRODUKT w programie Excel: Zwraca SUMA po pomnożeniu wartości w wielu tablicach w programie Excel.
LICZ.IFS z zakresem kryteriów dynamicznych : Policz komórki na podstawie innych wartości komórek w programie Excel.
LICZNIKI Dopasowanie dwóch kryteriów : Policz komórki spełniające dwa różne kryteria na liście w programie Excel.
LICZNIKI Z LUB dla wielu kryteriów : Policz komórki spełniające wiele kryteriów za pomocą funkcji LUB.
Funkcja LICZ.WARUNKI w programie Excel : Policz komórki w zależności od innych wartości komórek.
Jak korzystać z Countif w VBA w programie Microsoft Excel : Policz komórki przy użyciu kodu Visual Basic for Applications.
Jak używać symboli wieloznacznych w programie Excel : Policz komórki pasujące do fraz za pomocą symboli wieloznacznych w programie Excel
popularne artykuły
50 skrótów Excela, aby zwiększyć produktywność : Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Jak używać tFunkcja WYSZUKAJ.PIONOWO w programie Excel : Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak korzystać z funkcji LICZ.JEŻELI w programie Excel : Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMIF w programie Excel? : To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.
