01 z 02
Součet buněk, které spadají mezi dvě hodnoty
Lifewire
Funkce SUMPRODUCT v Excelu je velmi všestranná funkce, která poskytuje různé výsledky v závislosti na způsobu zadávání argumentů funkce.
Normálně, jak název napovídá, SUMPRODUCT vynásobí prvky jednoho nebo více polí, aby získal svůj produkt, a poté sečte nebo sečte produkty dohromady.
Úpravou syntaxe funkce ji však lze 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čtou 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 rozsahu, jak je znázorněno na obrázku výše.
SUMPRODUCT Syntaxe funkce 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])
condition1, condition2 – podmínky, které musí být splněny, než funkce najde součin pole.
array – 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, nelze pro zadání funkce a jejích argumentů použít dialogové okno funkce. Místo toho je třeba funkci zadat ručně do buňky listu.
- Kliknutím na buňku B7 v listu z ní uděláte aktivní buňku;
- 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 objevit v buňce B7
- Odpověď byla získána sečtením pěti čísel v rozsahu (40, 45, 50, 55 a 60), které jsou mezi 25 a 75. Celkový počet 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í booleovskou hodnotu (TRUE nebo FALSE).
Pro účely výpočtů Excel přiřadí hodnotu 1 těm prvkům pole, které jsou PRAVDA (splňují podmínku) a hodnotu 0pro prvky pole, které jsou NEPRAVDA (nesplňují podmínku).
Například číslo 40:
- je PRAVDA pro první podmínku, takže v prvním poli je přiřazena hodnota 1;
- je PRAVDA pro druhou podmínku, takže ve druhém poli je přiřazena hodnota 1.
Číslo 15:
- je NEPRAVDA pro první podmínku, takže v prvním poli je přiřazena hodnota 0;
- je PRAVDA pro druhou podmínku, takže ve druhém poli je přiřazena hodnota 1.
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:
- 1krát se libovolné číslo rovná původnímu číslu
- 0 krát jakékoli číslo je rovno 0
Takže skončíme s:
-
140=40
015=0
022=0
145=45
150=50
155=55
025=0
075=0
160=600100=0
Součet výsledků
SUMPRODUCT pak sečte výše uvedené výsledky a najde odpověď.
40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0=250
