Note: The other languages of the website are Google-translated. Back to English

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:

=SUMPRODUCT((logical1)*(logical2))
  • 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:

=SUMPRODUCT(($C$2:$C$10>$B$2:$B$10)*($A$2:$A$10=E2))

Uwagi: 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

Chcesz szybko i perfekcyjnie zakończyć swoją codzienną pracę? Kutools dla programu Excel oferuje 300 zaawansowanych zaawansowanych funkcji (Łącz skoroszyty, sumuj według koloru, dziel zawartość komórek, konwertuj daty itd.) i oszczędzaj 80% czasu.

  • Zaprojektowany dla 1500 scenariuszy pracy, pomaga rozwiązać 80% problemów z Excelem.
  • Zmniejsz tysiące kliknięć klawiatury i myszy każdego dnia, odciąż zmęczone oczy i dłonie.
  • Zostań ekspertem Excela w 3 minuty. Nie musisz już pamiętać żadnych bolesnych formuł i kodów VBA.
  • 30-dniowy nieograniczony bezpłatny okres próbny. 60-dniowa gwarancja zwrotu pieniędzy. Bezpłatna aktualizacja i wsparcie przez 2 lata.
Wstążka programu Excel (z zainstalowanym Kutools dla programu Excel)

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 zakładki do pakietu Office (w tym Excel), podobnie jak Chrome, Firefox i nowy Internet Explorer.
Zrzut ekranu programu Excel (z zainstalowaną kartą Office)
Sortuj komentarze według
Komentarze (2)
Brak ocen. Oceń jako pierwszy!
Ten komentarz został zminimalizowany przez moderatora na stronie
=SUMPRODUCT({Tablica Prawda/Fałsz}) nie zlicza już wartości Prawda w tablicy (jak w formułach SUMA lub LICZBA).
Ale możesz wymusić konwersję Prawda/Fałsz na 1 i 0, dodając Operator „--” tuż przed tablicą:
=SUMPRODUCT(--{Tablica Prawda/Fałsz}).
Możesz również wpisać ten operator zaraz po znaku mnożenia, podając dziwny operator '*--'.

W tym przykładzie działającymi formułami byłyby:
=SUMPRODUCT(--($C$2:$C$10>$B$2:$B$10)*--($A$2:$A$10=E2))
Ten komentarz został zminimalizowany przez moderatora na stronie
Witam profesorze X,

Masz rację pod jednym względem. Podwójna wartość ujemna (--) jest jednym z kilku sposobów wymuszenia wartości TRUE i FALSE na ich liczbowe odpowiedniki, 1 i 0. Gdy mamy jedynek i zer, możemy wykonywać różne operacje na tablicach z logiką Boole'a.

Ale nasza formuła nie potrzebuje podwójnej wartości ujemnej (--), dzięki czemu formuła jest bardziej zwarta. Dzieje się tak, ponieważ matematyczna operacja mnożenia (*) automatycznie konwertuje wartości TRUE i FALSE na jedynki i zera. Miłego dnia.

Pozdrawiamy,
Mandy
Nie ma tu jeszcze żadnych komentarzy
Zostaw swój komentarz
Publikowanie jako gość
×
Oceń ten post:
0   Postacie
Sugerowane lokalizacje

Bądż na bieżąco

Prawa autorskie © 2009 - www.extendoffice.com. | Wszelkie prawa zastrzeżone. Zasilany przez ExtendOffice, | Mapa strony
Microsoft i logo Office są znakami towarowymi lub zastrzeżonymi znakami towarowymi Microsoft Corporation w Stanach Zjednoczonych i / lub innych krajach.
Chronione przez Sectigo SSL