Podsumuj komórki spełniające wiele kryteriów za pomocą programu Excel SUMPRODUCT

Podsumuj komórki spełniające wiele kryteriów za pomocą programu Excel SUMPRODUCT
Podsumuj komórki spełniające wiele kryteriów za pomocą programu Excel SUMPRODUCT
Anonim

01 z 02

Sumowanie komórek mieszczących się między dwiema wartościami

Image
Image
Sumowanie komórek danych spełniających wiele kryteriów za pomocą programu Excel SUMPRODUCT.

Lifewire

Funkcja SUMPRODUCT w programie Excel to bardzo wszechstronna funkcja, która daje różne wyniki w zależności od sposobu wprowadzania argumentów funkcji.

Zwykle, jak sama nazwa wskazuje, SUMPRODUCT mnoży elementy jednej lub więcej tablic, aby uzyskać ich produkt, a następnie dodaje lub sumuje produkty.

Zmieniając składnię funkcji, można jej jednak używać do sumowania tylko danych w komórkach, które spełniają określone kryteria.

Od wersji Excel 2007 program zawiera dwie funkcje - SUMA. JEŻELI i SUMA. WIELKIE - które sumują dane w komórkach spełniających jedno lub więcej kryteriów.

Czasami jednak łatwiej jest pracować z SUMPRODUCT, jeśli chodzi o znalezienie wielu warunków odnoszących się do tego samego zakresu, jak pokazano na powyższym obrazku.

SUMPRODUCT Składnia funkcji do sumowania komórek

Składnia używana do uzyskania polecenia SUMPRODUCT do sumowania danych w komórkach spełniających określone warunki to:

=SUMPRODUCT([warunek1][warunek2][tablica])

warunek1, warunek2 - warunki, które muszą być spełnione zanim funkcja znajdzie iloczyn tablicy.

tablica - ciągły zakres komórek

Przykład: sumowanie danych w komórkach spełniających wiele warunków

Przykład na powyższym obrazku dodaje dane w komórkach w zakresie od D1 do E6, które są od 25 do 75.

Wchodzenie do funkcji SUMPRODUCT

Ponieważ w tym przykładzie użyto nieregularnej formy funkcji SUMA. PRODUKT, nie można użyć okna dialogowego funkcji do wprowadzenia funkcji i jej argumentów. Zamiast tego funkcję należy wpisać ręcznie do komórki arkusza roboczego.

  1. Kliknij komórkę B7 w arkuszu, aby uczynić ją aktywną komórką;
  2. Wprowadź następującą formułę do komórki B7: =SUMPRODUCT(($A$2:$B$6>25)($A$2:$B$6<75)(A2:B6))
  3. Odpowiedź 250 powinna pojawić się w komórce B7
  4. Odpowiedź została uzyskana poprzez dodanie pięciu liczb z zakresu (40, 45, 50, 55 i 60), które są pomiędzy 25 a 75. Łącznie jest to 250

Przełamywanie formuły SUMPRODUCT

Gdy jako argumenty używane są warunki, funkcja SUMPRODUCT ocenia każdy element tablicy względem warunku i zwraca wartość logiczną (PRAWDA lub FAŁSZ).

Dla celów obliczeń program Excel przypisuje wartość 1 tym elementom tablicy, które mają wartość PRAWDA (spełniają warunek) i wartość 0dla elementów tablicy, które są FALSE (nie spełniają warunku).

Na przykład liczba 40:

  • jest TRUE dla pierwszego warunku, więc wartość 1 jest przypisana w pierwszej tablicy;
  • jest TRUE dla drugiego warunku, więc wartość 1 jest przypisywana w drugiej tablicy.

Numer 15:

  • jest FALSE dla pierwszego warunku, więc wartość 0 jest przypisana w pierwszej tablicy;
  • jest TRUE dla drugiego warunku, więc wartość 1 jest przypisywana w drugiej tablicy.

Odpowiadające jedynki i zera w każdej tablicy są mnożone przez siebie:

  • Dla liczby 40 - mamy 1 x 1 zwracający wartość 1;
  • Dla liczby 15 - mamy 0 x 1 zwracające wartość 0.

Mnożenie jedynek i zer przez zakres

Te jedynek i zera są następnie mnożone przez liczby z zakresu A2: B6

W ten sposób otrzymujemy liczby, które zostaną zsumowane przez funkcję.

Działa to, ponieważ:

  • 1 razy dowolna liczba jest równa oryginalnej liczbie
  • 0 razy dowolna liczba równa się 0

Więc otrzymujemy:

  • 140=40

    015=0

    022=0

    145=45

    150=50

    155=55

    025=0

    075=0

    160=600100=0

Podsumowanie wyników

SUMPRODUCT następnie podsumowuje powyższe wyniki, aby znaleźć odpowiedź.

40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0=250

Zalecana: