Sečtete buňky, které splňují více kritérií s Excelem SUMPRODUCT

Sečíst buňky, které spadají mezi dvě hodnoty

Funkce SUMPRODUCT v Excelu je velmi všestranná funkce, která poskytuje různé výsledky v závislosti na způsobu argumenty jsou zadány.

Normálně, jak jeho název napovídá, SUMPRODUCT násobí prvky jednoho nebo více pole získat jejich produkt a poté přidat nebo sečíst produkty dohromady.

Úpravou funkcí syntaxlze jej však použít k sečtení pouze dat v buňkách, které splňují určitá kritéria.

Od Excelu 2007 obsahuje program dvě funkce - SUMIF a SUMIFS - která sečte data v buňkách, které splňují jedno nebo více nastavených kritérií.

Někdy je však se SUMPRODUCT snazší pracovat, pokud jde o nalezení více podmínek týkajících se stejného rozsah jak je znázorněno na obrázku výše.

Syntaxe funkce SUMPRODUCT k součtu buněk

Syntaxe použitá k tomu, aby SUMPRODUCT sečetl data v buňkách, které splňují specifické podmínky, je:

= SUMPRODUCT([podmínka1] * [podmínka2] * [pole])

podmínka1, podmínka2 - podmínky, které musí být splněny, než funkce najde součin pole.

pole – souvislý rozsah buněk.

Příklad: Sčítání dat v buňkách, které splňují více podmínek

Příklad na obrázku výše přidává data do buněk v rozsahu D1 až E6, které jsou mezi 25 a 75.

Vstup do funkce SUMPRODUCT

Protože tento příklad používá nepravidelný tvar funkce SUMPRODUCT, funkce dialogové okno nelze použít pro zadání funkce a jejích argumentů. Místo toho je třeba funkci zadat ručně do a pracovní list buňka.

1. Klikněte na buňku B7 v pracovním listu, aby to bylo aktivní buňka;
2. Do buňky B7 zadejte následující vzorec:=SUMPRODUCT(($A$2:$B$6>25)*($A$2:$B$6<75)*(A2:B6))
3. Odpověď 250 by se měla objevit v buňce B7
4. K odpovědi se dospělo sečtením pěti čísel v rozsahu (40, 45, 50, 55 a 60), které jsou mezi 25 a 75. Celkem jich je 250

Rozdělení vzorce SUMPRODUCT

Když jsou pro své argumenty použity podmínky, SUMPRODUCT vyhodnotí každý prvek pole podle podmínky a vrátí a Booleovská hodnota (Pravda nebo lež).

Pro účely výpočtů Excel přiřadí hodnotu 1 pro ty prvky pole, které jsou TRUE (splňují podmínku) a mají hodnotu 0 pro prvky pole, které jsou NEPRAVDA (nesplňují podmínku).

Například číslo 40:

• je TRUE pro první podmínku, takže hodnota 1 je přiřazen v prvním poli;
• je TRUE pro druhou podmínku, takže hodnota 1 je přiřazena ve druhém poli.

Číslo 15:

• je FALSE pro první podmínku, takže hodnota 0 je přiřazen v prvním poli;
• je TRUE pro druhou podmínku, takže hodnota 1 je přiřazena ve druhém poli.

Odpovídající jedničky a nuly v každém poli se vynásobí dohromady:

• Pro číslo 40 - máme 1 x 1 vrací hodnotu 1;
• Pro číslo 15 - máme 0 x 1 vrací hodnotu 0.

Násobení jedniček a nul rozsahem

Tyto jedničky a nuly se pak vynásobí čísly v rozsahu A2: B6.

To se provádí, abychom dostali čísla, která budou funkcí sečtena.

Funguje to, protože:

• 1 krát libovolné číslo se rovná původnímu číslu
• 0 krát jakékoli číslo se rovná 0

Takže končíme s:

• 1 * 40 = 40
  0 * 15 = 0
  0 * 22 = 0
  1 * 45 = 45
  1 * 50 = 50
  1 * 55 = 55
  0 * 25 = 0
  0 * 75 = 0
  1 * 60 = 60
  0 * 100 = 0

Shrnutí výsledků

SUMPRODUCT pak sečte výše uvedené výsledky, aby našel odpověď.

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