01 z 02
Sumowanie komórek mieszczących się między dwiema wartościami
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.
- Kliknij komórkę B7 w arkuszu, aby uczynić ją aktywną komórką;
- Wprowadź następującą formułę do komórki B7: =SUMPRODUCT(($A$2:$B$6>25)($A$2:$B$6<75)(A2:B6))
- Odpowiedź 250 powinna pojawić się w komórce B7
- 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
