Součet buněk, které splňují více kritérií, v programu Excel SUMPRODUCT

Součet buněk, které spadají mezi dvě hodnoty

Funkce SUMPRODUCT v aplikaci Excel je velmi univerzální funkce, která poskytne různé výsledky v závislosti na způsobu zadávání argumentů funkce. Normálně, jak název napovídá, SUMPRODUCT znásobí prvky jednoho nebo více polí, aby získal svůj produkt, a poté produkty sčítá nebo sčítá dohromady. Úpravou syntaxe funkce ji však lze použít k součtu pouze dat v buňkách, která splňují konkrétní kritéria. Od aplikace Excel 2007 obsahuje program dvě funkce – SUMIF a SUMIFS -, které budou sčítat data v buňkách, které splňují jedno nebo více stanovených kritérií. Občas je však snazší pracovat se SUMPRODUCT, pokud jde o nalezení více podmínek vztahujících se ke stejnému rozsahu, jak je znázorněno na obrázku výše.

Syntaxe funkce SUMPRODUCT k součtu buněk

Syntaxe použitá k získání SUMPRODUCT k součtu dat v buňkách, které splňují konkrétní podmínky, je: = SUMPRODUCT ([condition1] * [condition2] * [array]) podmínka1, podmínka2 – podmínky, které musí být splněny, než funkce najde produkt pole. pole – souvislý rozsah buněk

Příklad: Součet 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, nelze k zadání funkce a jejích argumentů použít dialogové okno funkce. Místo toho musí být funkce zadána ručně do buňky listu.

1. Klikněte na buňku B7 v listu, aby se stala aktivní buňkou;
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ěl objevit v buňce B7
4. Odpověď byla získána přidáním pěti čísel v rozsahu (40, 45, 50, 55 a 60), které jsou mezi 25 a 75. Celkem je 250

Rozebrat vzorec SUMPRODUCT

Když jsou pro jeho argumenty použity podmínky, SUMPRODUCT vyhodnotí každý prvek pole proti podmínce a vrátí logickou hodnotu (TRUE nebo FALSE). Pro účely výpočtů aplikace Excel přiřadí hodnotu 1 pro ty prvky pole, které mají hodnotu TRUE (splňují podmínku) a hodnotu 0 pro prvky pole, které jsou NEPRAVDA (nesplňují podmínku). Například číslo 40:

• je PRAVDA pro první podmínku, takže hodnota 1 je přiřazen v prvním poli;
• je PRAVDA pro druhou podmínku, takže hodnota 1 je přiřazen 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 PRAVDA pro druhou podmínku, takže hodnota 1 je přiřazen ve druhém poli.

Odpovídající nuly a nuly v každém poli se vynásobí společně:

• Pro číslo 40 – máme 1 x 1 vracející hodnotu 1;
• Pro číslo 15 – máme 0 x 1 vracející hodnotu 0.

Násobení jedniček a nul rozsahem

Tyto a nuly se poté vynásobí čísly v rozsahu A2: B6.

Tímto způsobem získáme čísla, která funkce sčítá. Funguje to proto, že:

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

Takže skončí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

Souhrn výsledků

SUMPRODUCT poté shrnuje výše uvedené výsledky, aby našel odpověď. 40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0 = 250