01 z 02
Súčet buniek, ktoré spadajú medzi dve hodnoty
Lifewire
Funkcia SUMPRODUCT v Exceli je veľmi všestranná funkcia, ktorá poskytuje rôzne výsledky v závislosti od spôsobu zadávania argumentov funkcie.
Normálne, ako už názov napovedá, SUMPRODUCT vynásobí prvky jedného alebo viacerých polí, aby získal svoj produkt a potom tieto produkty sčíta alebo sčíta.
Úpravou syntaxe funkcie ju však možno použiť na sčítanie iba údajov v bunkách, ktoré spĺňajú špecifické kritériá.
Od Excelu 2007 obsahuje program dve funkcie – SUMIF a SUMIFS – ktoré sčítajú údaje v bunkách, ktoré spĺňajú jedno alebo viac stanovených kritérií.
Niekedy sa však so SUMPRODUCTom ľahšie pracuje, keď ide o nájdenie viacerých podmienok týkajúcich sa rovnakého rozsahu, ako je znázornené na obrázku vyššie.
SUMPRODUCT Syntax funkcie k súčtu buniek
Syntax použitá na získanie súčtu údajov SUMPRODUCT v bunkách, ktoré spĺňajú špecifické podmienky, je:
=SUMPRODUCT([podmienka1][podmienka2][pole])
condition1, condition2 – podmienky, ktoré musia byť splnené, kým funkcia nájde súčin poľa.
pole – súvislý rozsah buniek
Príklad: Sčítanie údajov v bunkách, ktoré spĺňajú viaceré podmienky
Príklad na obrázku vyššie pridáva údaje do buniek v rozsahu D1 až E6, ktoré sú medzi 25 a 75.
Vstup do funkcie SUMPRODUCT
Pretože tento príklad používa nepravidelný tvar funkcie SUMPRODUCT, dialógové okno funkcie nemožno použiť na zadanie funkcie a jej argumentov. Namiesto toho musíte funkciu zadať ručne do bunky hárka.
- Kliknutím na bunku B7 v pracovnom hárku z nej urobte aktívnu bunku;
- Do bunky B7 zadajte nasledujúci vzorec: =SUMPRODUCT(($A$2:$B$6>25)($A$2:$B$6<75)(A2:B6))
- Odpoveď 250 by sa mala objaviť v bunke B7
- Odpoveď sme získali pridaním piatich čísel v rozsahu (40, 45, 50, 55 a 60), ktoré sú medzi 25 a 75. Spolu je 250
Rozdelenie vzorca SUMPRODUCT
Keď sa vo svojich argumentoch použijú podmienky, SUMPRODUCT vyhodnotí každý prvok poľa oproti podmienke a vráti boolovskú hodnotu (TRUE alebo FALSE).
Na účely výpočtov Excel priradí hodnotu 1 pre tie prvky poľa, ktoré sú TRUE (spĺňajú podmienku) a hodnotu 0pre prvky poľa, ktoré sú FALSE (nespĺňajú podmienku).
Napríklad číslo 40:
- je PRAVDA pre prvú podmienku, takže v prvom poli je priradená hodnota 1;
- je PRAVDA pre druhú podmienku, takže v druhom poli je priradená hodnota 1.
Číslo 15:
- je FALSE pre prvú podmienku, takže v prvom poli je priradená hodnota 0;
- je PRAVDA pre druhú podmienku, takže v druhom poli je priradená hodnota 1.
Zodpovedajúce jednotky a nuly v každom poli sa spolu vynásobia:
- Pre číslo 40 – máme 1 x 1 vracia hodnotu 1;
- Pre číslo 15 – 0 x 1 vracia hodnotu 0.
Vynásobenie jednotiek a núl rozsahom
Tieto jednotky a nuly sa potom vynásobia číslami v rozsahu A2: B6
Toto sa robí preto, aby sme dostali čísla, ktoré funkcia sčíta.
Funguje to, pretože:
- 1-krát sa ľubovoľné číslo rovná pôvodnému číslu
- 0-krát sa ľubovoľné číslo rovná 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
Súhrn výsledkov
SUMPRODUCT potom zosumarizuje vyššie uvedené výsledky a nájde odpoveď.
40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0=250
