Souhrnné buňky, které spadají mezi dvěma hodnotami
Přehled SUMPRODUCT
Funkce SUMPRODUCT v aplikaci Excel je velmi univerzální funkce, která poskytuje různé výsledky v závislosti na způsobu zadávání argumentů funkce.
Obvykle, jak název napovídá, SUMPRODUCT násobí prvky jedné nebo více polí, aby získal svůj produkt a poté přidá nebo součtu produktů dohromady.
Úpravou syntaxe funkce se však může použít k součtu pouze dat v buňkách, které splňují specifická kritéria.
Od programu Excel 2007 obsahuje program dvě funkce - SUMIF a SUMIFS -, které sumarizují data v buňkách, které splňují jedno nebo více nastavených kritérií.
Občas je však s SUMPRODUCT snadnější pracovat, pokud jde o nalezení více podmínek týkajících se stejného rozsahu, jaký je znázorněn na obrázku výše.
Syntaxe funkce SUMPRODUCT k souhrnným buňkám
Syntaxe použitá k získání SUMPRODUCT ke součtovým datům v buňkách, které splňují určité podmínky, je:
= SUMPRODUCT (podmínka1 * podmínka2 * pole)
podmínka1, podmínka2 - podmínky, které musí být splněny předtím, než funkce nalezne produkt pole.
pole - souvislý rozsah buněk
Příklad: Shrnutí dat v buňkách, které splňují více podmínek
Příklad na obrázku výše přidává data v buňkách v rozsahu D1 až E6, které jsou mezi 25 a 75.
Zadání funkce SUMPRODUCT
Protože tento příklad používá nepravidelný tvar funkce SUMPRODUCT, dialogové okno funkce nelze použít k zadání funkce a jejích argumentů. Místo toho musí být funkce zadána ručně do buňky listu.
- Klikněte na buňku B7 v listu, aby se stala aktivní buňkou;
- Do buňky B7 zadejte následující vzorec: = SUMPRODUCT (($ A $ 2: $ B $ 6> 25) * ($ A $ 2: $ B $ 6 <75) * (A2: B6))
- Odpověď 250 by se měla zobrazit v buňce B7
- Odpověď byla dosažena přidáním pěti čísel v rozmezí (40, 45, 50, 55 a 60), které se pohybují mezi 25 a 75. Celkově je to 250
Zrušení vzorce SUMPRODUCT
Při použití podmínek pro jeho argumenty SUMPRODUCT vyhodnocuje každý prvek matice proti stavu a vrací Booleovu hodnotu (TRUE nebo FALSE).
Pro účely výpočtů Excel přiřadí hodnotu 1 pro ty prvky pole, které jsou TRUE (splňují podmínku) a hodnotu 0 pro prvky pole, které jsou FALSE (nesplňují podmínku).
Například číslo 40:
- je TRUE pro první podmínku, takže hodnota 1 je přiřazena v prvním poli;
- je TRUE pro druhou podmínku, takže hodnota 1 je přiřazen v druhém poli.
číslo 15:
- je FALSE pro první podmínku, takže hodnota 0 je přiřazena v prvním poli;
- je TRUE pro druhou podmínku, takže hodnota 1 je přiřazen v druhém poli.
Odpovídající ty a nuly v každém poli jsou násobeny:
- Pro číslo 40 - máme 1 x 1 vrací hodnotu 1;
- Pro číslo 15 - máme 0 x 1, vrací hodnotu 0.
Pokračujte ve čtení níže
02 z 02Vynásobení těch a nul pomocí rozsahu
Ty a nuly se pak vynásobí počty v rozsahu A2: B6.
To je děláno, abychom nám poskytli čísla, která budou součtem funkce.
To funguje proto, že:
- 1 x libovolné číslo se rovná původnímu číslu
- 0 krát libovolné číslo se rovná 0
Takže skončujeme s:
- 1 * 40 = 400 * 15 = 00 * 22 = 01 * 45 = 451 * 50 = 501 * 55 = 550 * 25 = 00 * 75 = 01 * 60 = 600 * 100 = 0
Shrnutí výsledků
SUMPRODUCT pak shrne výše uvedené výsledky najít odpověď.
40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0 = 250
