W tym artykule nauczymy się liczyć wiele zakresów z jednym kryterium w programie Microsoft 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. W programie Excel możesz wykonywać zadania, takie jak operacje w wielu zakresach, korzystając z formuły wyjaśnionej poniżej. Kryteria można zastosować do tekstu, liczby lub dowolnego częściowego dopasowania w programie Excel. Kryteria wewnątrz formuły realizowane za pomocą operatorów. Operatory takie jak równa się ( = ), mniejszy niż równy ( <= ), Lepszy niż ( > ) lub nie równa się ( ).
Jak rozwiązać problem?
W przypadku tego problemu będziemy musieli użyć funkcji SUMPRODUCT. Teraz zrobimy formułę z funkcji. Tutaj otrzymujemy dwa zakresy danych i musimy policzyć wiersze spełniające 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 zwróconej tablicy
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ń dyplomatów, 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ć, tylko raz 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 też jeden i ten sam czas, kiedy prezydent USA "Barack Obama" odwiedził Indie, gdzie kwestie również wynoszą 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.
Mam nadzieję, że ten artykuł o Policz wiele zakresów z jednym kryterium w programie Microsoft Excel jest objaśniający. Więcej artykułów na temat obliczania wartości i powiązanych formuł programu Excel 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 e-mail.
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 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 wybierając kryteria z listy opcji w komórce kryteriów w programie Excel za pomocą narzędzia do sprawdzania poprawności danych.
LICZNIKI Dopasowanie dwóch kryteriów : wiele kryteriów pasuje do różnych list w tabeli za pomocą funkcji LICZ.WARUNKI w programie Excel
LICZNIKI Z LUB dla wielu kryteriów : dopasuj dwie lub więcej nazw na tej samej liście, używając kryteriów OR zastosowanych na liście w programie Excel.
Jak korzystać z Countif w VBA w programie Microsoft Excel : Policz komórki z kryteriami przy użyciu kodu języka Visual Basic for Applications w makrach programu Excel.
Jak używać symboli wieloznacznych w programie Excel : Policz komórki pasujące do fraz na listach tekstowych za pomocą symboli wieloznacznych ( * , ? , ~ )w Excelu
Popularne artykuły :
Jak korzystać z funkcji IF w programie Excel? : Instrukcja IF w programie Excel sprawdza warunek i zwraca określoną wartość, jeśli warunek jest PRAWDA, lub zwraca inną określoną wartość, jeśli jest FAŁSZ.
Jak korzystać z funkcji 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 SUMIF w programie Excel? : To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.
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.