Sumuj komórki, które spełniają wiele kryteriów dzięki programowi Excel SUMPRODUCT

01 z 02

Sumuj komórki, które dzielą dwie wartości

Przegląd SUMPRODUCT

Funkcja SUMPRODUCT w Excelu jest bardzo wszechstronną funkcją, która daje różne wyniki w zależności od sposobu wprowadzania argumentów funkcji.

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

Dostosowując składnię funkcji, można ją jednak wykorzystać do sumowania wyłącznie danych w komórkach spełniających określone kryteria.

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

Czasami jednak SUMPRODUCT jest łatwiejszy w pracy, gdy chodzi o znalezienie wielu warunków odnoszących się do tego samego zakresu, co pokazano na powyższym obrazku.

SUMPRODUCT Składnia funkcji do komórek sumy

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

= SUMPRODUCT (warunek1 * warunek2 * tablica)

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

array - ciągły zakres komórek

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

Przykład na powyższym rysunku dodaje dane w komórkach w zakresie od D1 do E6 w zakresie od 25 do 75.

Wprowadzanie funkcji SUMPRODUCT

Ponieważ w tym przykładzie użyto nieregularnej formy funkcji SUMPRODUCT, okna dialogowego funkcji nie można użyć do wprowadzenia funkcji i jej argumentów. Zamiast tego funkcja musi zostać wpisana ręcznie do komórki arkusza roboczego.

1. Kliknij komórkę B7 w arkuszu, aby stała się 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 powinien pojawić się w komórce B7
4. Odpowiedź uzyskano, dodając pięć liczb z przedziału (40, 45, 50, 55 i 60) o wartości od 25 do 75. Łącznie 250

Łamanie formuły SUMPRODUCT

Gdy warunki są używane dla jego argumentów, funkcja SUMA_OBRAFT ocenia każdy element tablicy na podstawie warunku i zwraca wartość logiczną (TRUE lub FALSE).

Do celów obliczeń program Excel przypisuje wartość 1 dla tych elementów tablicy, które są PRAWDZIWE (spełniają warunek) i wartości 0 dla elementów tablicy, które są FALSE (nie spełniają warunku).

Na przykład liczba 40:

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

liczba 15:

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

Odpowiadające sobie zery w każdej tablicy są mnożone razem:

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

Kontynuuj czytanie poniżej

02 z 02

Pomnożenie On i Zero przez Range

Te liczby i zera są następnie mnożone przez liczby w zakresie A2: B6.

Ma to na celu podanie liczb, które będą sumowane przez funkcję.

Działa to, ponieważ:

• 1-krotność dowolnej liczby jest równa pierwotnej liczbie
• 0 razy dowolna liczba jest równa 0

W efekcie otrzymujemy:

• 1 * 40 = 400 * 15 = 00 * 22 = 01 * 45 = 451 * 50 = 501 * 55 = 550 * 25 = 00 * 75 = 01 * 60 = 600 * 100 = 0

Podsumowując wyniki

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

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