W tym artykule dowiemy się, jak liczyć wiersze z kryteriami SUMPRODUCT w programie Excel.
Scenariusz:
W prostych słowach, podczas pracy nad danymi czasami musimy policzyć komórki, w których 2 zakresy spełniają kryteria. Licznik warunkowy można wykonać za pomocą funkcji LICZ.JEŻELI, ale nie jest to jedyna funkcja, która może to zrobić. Funkcja SUMPRODUCT to wszechstronna funkcja, której można używać do liczenia według kryteriów, czasami nawet lepiej niż funkcja countif.
Jak rozwiązać problem?
W tym artykule będziemy musieli użyć funkcji SUMPRODUCT. Teraz zrobimy formułę z tych funkcji. Tutaj otrzymujemy dane z dwóch zakresów i musimy policzyć wartości, w których formuła spełnia kryteria.
Wzór ogólny:
= PRODUKT SUMA ( -- ( zakres1 operator zakres2 ) )
zakres1 : I zakres
zasięg2 : 2. zakres
operator : kryteria, warunek podany jako operator między 2 zakresami
Przykład:
Wszystko to może być trudne do zrozumienia. Przetestujmy więc tę formułę, uruchamiając ją na poniższym przykładzie.
Oto rekord z ostatnich 7 wielkich meczów klubów kibica Premier League.
Mamy rekord z ostatnich 7 meczów, w których strzelił gole gospodarzy, drużyna przeciw, gole gospodarzy i gole przeciwnika.
Po pierwsze, musimy znaleźć liczbę meczów wygranych u siebie przez jakąkolwiek drużynę.
Teraz użyjemy następującego wzoru, aby uzyskać liczbę gier, które wygrały u siebie.
Użyj formuły:
= PRODUKT SUMA ( -- ( E5:E11 > F5:F11 ))
E5:E11 : 1. zakres, gole strzelone u siebie
F5:F11 : 2. zakres, przeciwnik strzelił bramki
> : kryteria, warunek podany jako większy niż operator
Wyjaśnienie:
- Warunkiem, który musi być spełniony jest to, że gole strzelone u siebie muszą być większe niż gole strzelone przez przeciwnika.
- E5:E11 ( zakres1 ) musi być większe niż F5:F11 ( zakres2 ).
- -- operator używany do konwersji wartości TRUE na 1 i wartości Fałsz na 0.
- Funkcja SUMPRODUCT pobiera sumę 1s w zwróconej tablicy, która będzie liczbą wymaganych wygranych.
Tutaj zakres jest podany jako odwołanie do komórki. Naciśnij Enter, aby uzyskać liczbę.
Jak widać, suma meczów, w których gospodarze mieli przewagę i wygrali, to 4 mecze.
Powyższy przykład był łatwy. Żeby było ciekawiej policzymy ile meczów wygrała drużyna z całych danych.
Po pierwsze, musimy znaleźć liczbę meczów wygranych u siebie przez jakąkolwiek drużynę.
Teraz użyjemy następującego wzoru, aby obliczyć liczbę gier, człowieku. Zjednoczeni wygrali.
Użyj formuły:
= PRODUKT SUMA ( -- ( C5:C11 = G5 ) * ( E5:E11 > F5:F11 ) + ( D5:D11 = G5 ) * ( E5:E11 < F5:F11 ) )
Wyjaśnienie:
- (C5:C11 = G5 ) * ( E5:E11 > F5:F11 ) sprawdza drużynę gospodarzy jako Man. Zjednoczeni i strzelili więcej goli niż przeciwna drużyna.
- ( D5:D11 = G5 ) * ( E5:E11 < F5:F11 ) sprawdza przeciw drużynie jako Man. United i strzelił więcej goli niż gospodarze.
- Funkcja SUMPRODUCT pobiera sumę 1s w zwróconej tablicy, która będzie liczbą wymaganych wygranych przez Man. Zjednoczony.
Tutaj zakres jest podany jako odwołanie do komórki. Naciśnij Enter, aby uzyskać liczbę.
Jak widać w sumie mecze gdzie Man. Drużyna United wygrała 2 mecze.
Oto kilka uwag obserwacyjnych pokazanych poniżej.
Uwagi:
- Formuła działa tylko z liczbami.
- Formuła działa tylko wtedy, gdy w tabeli przeglądowej nie ma duplikatów
- Funkcja SUMPRODUCT traktuje wartości nieliczbowe jako 0s.
- Funkcja SUMPRODUCT traktuje wartość logiczną TRUE jako 1 i False jako 0.
- Tablica argumentów musi mieć taką samą długość jak funkcja
Mam nadzieję, że ten artykuł o tym, jak zliczyć liczbę zwrotów, jeśli z SUMPRODUCT w programie Excel jest objaśniający. Więcej artykułów na temat funkcji SUMPRODUCT znajdziesz tutaj. Podziel się swoim zapytaniem poniżej w polu komentarza. Pomożemy Ci.
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 w zależności od innych wartości komórek w programie Excel.
LICZNIKI Dopasowania 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, które mają wiele kryteriów zgodnych 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, które zwiększą Twoją produktywność
Edytuj listę rozwijaną
Absolutne odniesienie w Excelu
Jeśli z formatowaniem warunkowym
Jeśli z symbolami wieloznacznymi
Przeglądaj według daty
Konwertuj cale na stopy i cale w programie Excel 2016
Dołącz do imienia i nazwiska w programie Excel
Policz komórki pasujące do A lub B