V tomto článku sa naučíme, ako počítať riadky s kritériami SUMPRODUCT v programe Excel.
Scenár:
Jednoducho povedané, pri práci s údajmi niekedy musíme spočítať bunky, v ktorých 2 rozsahy spĺňajú kritériá. Podmienený počet je možné vykonať pomocou funkcie COUNTIF, ale nie je to jediná funkcia, ktorá to môže urobiť. Funkcia SUMPRODUCT je všestranná funkcia, ktorú je možné použiť na počítanie s kritériami, niekedy dokonca lepšiu ako funkcia countif.
Ako problém vyriešiť?
V tomto článku budeme musieť používať funkciu SUMPRODUCT. Teraz z týchto funkcií vytvoríme vzorec. Tu sú uvedené údaje dvoch rozsahov a musíme spočítať hodnoty, kde vzorec spĺňa kritériá.
Všeobecný vzorec:
= SUMPRODUCT ( - (rozsah1 operátor rozsah2))
rozsah 1 : 1. rozsah
rozsah 2 : 2. rozsah
operátor: kritériá, podmienka daná ako operátor medzi 2 rozsahmi
Príklad:
Toto všetko môže byť mätúce na pochopenie. Otestujme teda tento vzorec spustením na príklade uvedenom nižšie.
Máme tu záznam za posledných 7 veľkých zápasov fanklubov Premier League.
Máme rekord za posledných 7 zápasov, v ktorých domáci strelili tím, proti tímu, domáci a strelili góly súper.
Po prvé, musíme zistiť počet zápasov, ktoré každý tím vyhral doma.
Teraz použijeme nasledujúci vzorec na výpočet počtu hier, ktoré sme vyhrali doma.
Použite vzorec:
= SUMPRODUCT ( - (E5: E11> F5: F11))
E5: E11 : 1. rozsah, domáci strieľali góly
F5: F11 : 2. rozsah, súper strieľal góly
>: kritériá, podmienka daná ako väčšia ako operátor
Vysvetlenie:
- Podmienkou, ktorú je potrebné splniť, je, že strelené góly domácich musia byť vyššie ako góly strelené súperom.
- E5: E11 (rozsah1) musí byť väčší ako F5: F11 (rozsah2).
- -- operátor použitý na konverziu hodnoty TRUE na 1 a nepravdivej hodnoty na 0.
- Funkcia SUMPRODUCT získa súčet 1 s vo vrátenom poli, čo bude počet požadovaných výhier.
Tu je rozsah uvedený ako referencia bunky. Počítadlo získate stlačením klávesu Enter.
Ako vidíte, celkové zápasy, v ktorých mali domáci tím výhodu a vyhrali, sú 4 zápasy.
Vyššie uvedený príklad bol ľahký. Aby to bolo zaujímavé, budeme do celých údajov počítať, koľko zápasov tím vyhral.
Po prvé, musíme zistiť počet zápasov, ktoré každý tím vyhral doma.
Teraz použijeme nasledujúci vzorec na výpočet počtu hier, človeče. United vyhrali.
Použite vzorec:
= SUMPRODUCT ( - (C5: C11 = G5) * (E5: E11> F5: F11) + (D5: D11 = G5) * (E5: E11 <F5: F11))
Vysvetlenie:
- (C5: C11 = G5) * (E5: E11> F5: F11) kontroluje domáci tím ako muž. United a strelili viac gólov ako tím súpera.
- (D5: D11 = G5) * (E5: E11 <F5: F11) kontroluje tím proti mužstvu. United a strelili viac gólov ako domáci.
- Funkcia SUMPRODUCT získa súčet 1 s vo vrátenom poli, čo bude počet požadovaných výhier od človeka. United.
Tu je rozsah uvedený ako referencia bunky. Počítadlo získate stlačením klávesu Enter.
Ako vidíte celkový počet zápasov, kde muž. Tím United vyhral 2 zápasy.
Tu je niekoľko pozorovacích poznámok uvedených nižšie.
Poznámky:
- Vzorec funguje iba s číslami.
- Vzorec funguje iba vtedy, ak vo vyhľadávacej tabuľke nie sú žiadne duplikáty
- Funkcia SUMPRODUCT považuje nečíselné hodnoty za 0 s.
- Funkcia SUMPRODUCT považuje logickú hodnotu TRUE za 1 a False za 0.
- Pole argumentov musí mať rovnakú dĺžku ako funkcia
Dúfam, že tento článok o tom, ako vrátiť počet, ak je v programe SUMPRODUCT v programe Excel vysvetľujúci. Tu nájdete ďalšie články o funkciách SUMPRODUCT. Podeľte sa o svoj dotaz nižšie v poli pre komentáre. Pomôžeme vám.
Ako používať funkciu SUMPRODUCT v programe Excel: Vráti SUMU po vynásobení hodnôt vo viacerých poliach v programe Excel.
COUNTIFS s rozsahom dynamických kritérií : Počet buniek závislých od iných hodnôt buniek v programe Excel.
COUNTIFS dve zhody kritérií: V programe Excel spočítajte bunky zodpovedajúce dvom rôznym kritériám v zozname.
COUNTIFS s ALEBO pre viac kritérií : Pomocou funkcie OR spočítajte bunky, ktoré majú viacero kritérií.
Funkcia COUNTIFS v programe Excel : Počet buniek závislých od iných hodnôt buniek.
Ako používať Countif vo VBA v programe Microsoft Excel : Počítanie buniek pomocou kódu Visual Basic for Applications.
Ako používať zástupné znaky v programe Excel : Spočítajte bunky zodpovedajúce frázam pomocou zástupných znakov v programe Excel
Populárne články
50 skratiek programu Excel na zvýšenie produktivity
Upravte rozbaľovací zoznam
Absolútna referencia v Exceli
Ak s podmieneným formátovaním
Ak so zástupnými znakmi
Vlookup podľa dátumu
V programe Excel 2016 preveďte palce na nohy a palce
Pripojte mená a priezviská v Exceli
Spočítajte bunky, ktoré sa zhodujú s A alebo B