Policz wiersze, jeśli spełniasz wiele kryteriów w programie Excel
Policz liczbę wierszy w zakresie na podstawie wielu kryteriów, z których niektóre zależą od testów logicznych działających na poziomie wiersza, funkcja SUMPRODUCT w programie Excel może wyświadczyć Ci przysługę.
Na przykład mam raport produktu z planowaną i rzeczywistą sprzedażą, teraz chcę policzyć wiersze zawierające Apple, którego rzeczywista sprzedaż jest większa niż planowana sprzedaż, jak pokazano na zrzucie ekranu. Aby rozwiązać to zadanie, najskuteczniejszą funkcją jest funkcja SUMPRODUCT.
Policz wiersze, jeśli spełniają wiele kryteriów za pomocą funkcji SUMPRODUCT
Policz wiersze, jeśli spełniają wiele kryteriów za pomocą funkcji SUMPRODUCT
Aby policzyć wiersze, jeśli spełniają wiele kryteriów, za pomocą funkcji SUMPRODUCT w programie Excel, ogólna składnia to:
- logical1, logical2:Wyrażenia logiczne używane do porównywania wartości.
1. Aby zliczyć liczbę wierszy Apple, których rzeczywista sprzedaż jest większa niż planowana, zastosuj poniższy wzór:
Note: W powyższym wzorze, C2:C10>B2:B10 jest pierwszym wyrażeniem logicznym, które porównuje wartości w kolumnie C z wartościami w kolumnie B; A2:A10=E2 jest drugim wyrażeniem logicznym, które sprawdza, czy komórka E2 istnieje w kolumnie A.
2. Następnie naciśnij Wchodzę klucz, aby uzyskać wymagany wynik, patrz zrzut ekranu:
Wyjaśnienie wzoru:
=SUMPRODUCT(($C$2:$C$10>$B$2:$B$10)*($A$2:$A$10=E2))
- $C$2:$C$10>$B$2:$B$10: To wyrażenie logiczne jest używane do porównywania wartości w kolumnie C z wartościami w kolumnie B w każdym wierszu, jeśli wartość w kolumnie C jest większa niż wartość w kolumnie B, wyświetlana jest wartość TRUE, w przeciwnym razie zostanie wyświetlona wartość FALSE i zwróci wartości tablicy: {PRAWDA;FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;PRAWDA}.
- $A$2:$A$10=E2:To wyrażenie logiczne służy do sprawdzania, czy komórka E2 znajduje się w zakresie A2:A10. Otrzymasz więc następujący wynik: {PRAWDA;FAŁSZ;PRAWDA;FAŁSZ;PRAWDA;PRAWDA;FAŁSZ;PRAWDA;FAŁSZ}.
- ($C$2:$C$10>$B$2:$B$10)*($A$2:$A$10=E2): Operacja mnożenia służy do mnożenia tych dwóch tablic w jedną tablicę w celu zwrócenia wyniku w następujący sposób: {1;0;1;0;0;0;0;1;0}.
- SUMPRODUCT(($C$2:$C$10>$B$2:$B$10)*($A$2:$A$10=E2))= SUMPRODUCT({1;0;1;0;0;0;0;1;0}): Ten SUMPRODUCT dodaje liczby w tablicy i zwraca wynik: 3.
Zastosowana funkcja względna:
- SUMPRODUCT:
- Funkcja SUMPRODUCT może służyć do mnożenia dwóch lub więcej kolumn lub tablic, a następnie do uzyskania sumy iloczynów.
Więcej artykułów:
- Policz wiersze, jeśli spełniają kryteria wewnętrzne
- Załóżmy, że masz raport sprzedaży produktów z tego roku i zeszłego roku, a teraz może być konieczne policzenie produktów, w których sprzedaż w tym roku jest większa niż w zeszłym roku lub sprzedaż w tym roku jest mniejsza niż w zeszłym roku, jak poniżej pokazano zrzut ekranu. Zwykle można dodać kolumnę pomocniczą do obliczania różnicy sprzedaży między dwoma latami, a następnie użyć funkcji LICZ.JEŻELI, aby uzyskać wynik. Ale w tym artykule przedstawię funkcję SUMPRODUCT, aby uzyskać wynik bezpośrednio bez żadnej kolumny pomocniczej.
- Policz dopasowania między dwiema kolumnami
- Na przykład mam dwie listy danych w kolumnie A i kolumnie C, teraz chcę porównać dwie kolumny i policzyć, czy wartość w kolumnie A znajduje się w kolumnie C w tym samym wierszu, co pokazano na zrzucie ekranu. W takim przypadku funkcja SUMPRODUCT może być najlepszą funkcją do rozwiązania tego zadania w programie Excel.
- Policz liczbę komórek równa jednej z wielu wartości
- Przypuśćmy, że mam listę produktów w kolumnie A, teraz chcę uzyskać całkowitą liczbę określonych produktów Apple, Grape i Lemon, które są wymienione w zakresie C4: C6 z kolumny A, jak pokazano na poniższym zrzucie ekranu. Zwykle w programie Excel proste funkcje LICZ.JEŻELI i LICZ.JEŻELI nie będą działać w tym scenariuszu. W tym artykule omówię, jak szybko i łatwo rozwiązać to zadanie za pomocą kombinacji funkcji SUMPRODUCT i LICZ.JEŻELI.
Najlepsze narzędzia biurowe
Kutools dla programu Excel - pomaga wyróżnić się z tłumu
Kutools dla programu Excel oferuje ponad 300 funkcji, Pewność, że to, czego potrzebujesz, jest w zasięgu jednego kliknięcia...
Karta Office - Włącz czytanie i edycję na kartach w Microsoft Office (w tym Excel)
- Jedna sekunda, aby przełączać się między dziesiątkami otwartych dokumentów!
- Zmniejsz liczbę kliknięć myszą każdego dnia, pożegnaj się z dłonią myszy.
- Zwiększa produktywność o 50% podczas przeglądania i edytowania wielu dokumentów.
- Wprowadza wydajne karty do pakietu Office (w tym programu Excel), podobnie jak przeglądarki Chrome, Edge i Firefox.
