Ako som už spomenul v mnohých svojich blogoch, že SUMPRODUKT je veľmi univerzálna funkcia a môže byť použitý na rôzne účely. V tomto článku uvidíme, ako môžeme túto funkciu použiť na počítanie hodnôt s viacerými kritériami ALEBO.
Generický vzorec SUMPRODUCT na počítanie s viacerými alebo kritériami
| = SUMPRODUCT (-((((kritériá1)+(kritériá2)+… )>0) |
Kritériá 1: Toto sú akékoľvek kritériá, ktoré vracajú pole TRUE a FALSE.
Kritérium 2: Toto je ďalšie kritérium, ktoré chcete skontrolovať. Podobne môžete mať toľko kritérií, koľko chcete.
Vyššie uvedený generický vzorec je často upravovaný tak, aby vyhovoval požiadavkám počítať s viacerými kritériami OR. Základný vzorec je však tento. Najprv uvidíme na príklade, ako to funguje a potom prediskutujeme ďalšie scenáre, v ktorých budete musieť tento vzorec trochu upraviť.
Príklad: Počítajte používateľov podľa kódu predajcu alebo rokuzápasy Použitie SUMPRODUCT
Takže tu máme množinu dát predajcov. Údaje obsahujú veľa stĺpcov. Čo musíme urobiť, je spočítať počet používateľov, ktorí majú kód „INKA“ alebo rok, je „2016“. Uistite sa, že ak má niekto oboje (kód ako „inka“ a rok 2016), malo by sa to počítať ako 1.
Takže tu máme dve kritériá. Používame vyššie uvedený vzorec SUMPRODUCT:
| = SUMPRODUCT (-(((Kód = I3)+(rok = K3))> 0)) |
Tu sú kód a rok pomenované rozsahy.
Toto vráti 7.
V údajoch máme 5 záznamov kódu INKA a 4 záznamy roku 2016. Ale 2 záznamy majú ako kód aj rok „INKA“ a rok 2016. Tieto záznamy sa počítajú ako 1. A takto získame 7.
Ako to funguje?
Pozrime sa teda na to, ako je vzorec vyriešený krok za krokom, potom prediskutujem, ako funguje.
| =SUMPRODUCT(-((((Kód = I3)+(rok = K3))> 0)) |
| 1=>SUMPRODUCT(-((({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE;…}+{FALSE; FALSE; FALSE; TRUE; TRUE; …})> 0)) |
| 2=>SUMPRODUCT(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0)) |
| 3=>SUMPRODUCT(-({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; …}) |
| 4=>SUMPRODUCT({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0}) |
| 5=>7 |
V prvom kroku sa hodnota I3 („INKA“) porovná s každou bunkou v rozsahu kódu. To vráti pole TRUE a FALSE. PRAVDA pre každý zápas. Aby som ušetril miesto, nezobrazil som všetky TRUE-FALSE. Podobne je hodnota K3 (2016) priradená ku každej bunke v rozsahu rokov.
V nasledujúcom kroku pridáme tieto dve polia, ktorých výsledkom je nové pole číselných hodnôt. Ako možno viete, v programe Excel sa s hodnotou TRUE zaobchádza ako s 1 a s FALSE s 0. Keď sa teda sčítajú PRAVDA a PRAVDA, dostaneme 2 a zvyšok môžete pochopiť.
V ďalšom kroku skontrolujeme, ktorá hodnota je v poli väčšia ako 0. Toto znova prevedie pole na skutočné falošné pole. Pre každú hodnotu 0, ktorú dostaneme, sa nepravda a zvyšok prevedú na pravdivé. Teraz je našou odpoveďou počet TRUE hodnôt v poli. Ako ich však spočítame? Tu je postup.
Dvojité záporné znamienka (-) sa používajú na konverziu booleovských hodnôt na 1 s a 0 s. Takže každá PRAVDA hodnota v poli je prevedená na 1 a FALSE na 0.
V poslednom kroku SUMPRODUCT sumarizuje toto pole a dostaneme odpoveď ako 7.
Pridanie ďalších alebo kritérií na počítanie pomocou SUMPRODUCT
Ak teda potrebujete pridať ďalšie alebo kritériá na počítanie, stačí pridať kritériá pomocou znamienka + k funkcii.
Ak napríklad chcete k uvedenému vzorcu pridať ďalšie kritériá tak, aby sa sčítal počet zamestnancov, ktorí predali viac ako 5 produktov. Vzorec SUMPRODUCT bude jednoducho vyzerať takto:
| = SUMPRODUCT (-((((Kód = I3)+(Rok = K3)+(Predaj> 5))> 0)) |
Jednoduché! nie?
Povedzme však, že chcete mať dve kritériá Kód rozsah. Povedzme, že chcete počítať „INKB“. Ako to teda urobíte? Jedna metóda používa vyššie uvedenú techniku, ale bude sa opakovať. Povedzme, že chcem pridať ďalších 10 kritérií z rovnakého rozsahu. V takýchto prípadoch táto technika nie je taká múdra na počítanie s SUMPRODUCT.
Povedzme, že máme údaje usporiadané takto.
Kódy kritérií sú v jednom riadku I2: J2. Tu je dôležité usporiadanie údajov. Vzorec SUMPRODUCT pre tri nastavenia počtu ALEBO kritérií bude:
| = SUMPRODUCT (-(((Kód = I2: J2)+(rok = I3: J3))> 0)) |
Toto je vzorec SUMPRODUCT na počítanie s viacerými kritériami, keď je za sebou napísaných viac kritérií z jedného rozsahu.
Tým sa vráti správna odpoveď, ktorá je 10.
Ak do J3 napíšete ktorýkoľvek rok, do vzorca sa pripočíta aj tento počet.
Používa sa, ak sú kritériá v jednom riadku. Bude to fungovať, ak sú kritériá v jednom stĺpci pre rovnaký rozsah? Nie.
V tomto prípade máme na počítanie viac kódov, ale tieto kódy typov sú zapísané v jednom stĺpci. Keď použijeme vyššie uvedený vzorec SUMPRODUCT, zobrazí sa chyba #N/A. Nebudeme sa zaoberať tým, ako k tejto chybe došlo, pretože bude tento článok príliš dlhý. Pozrime sa, ako to môžeme dosiahnuť.
Aby tento vzorec fungoval, musíte zabaliť kritériá kódu do funkcie TRANSPOSE. Vďaka tomu bude vzorec fungovať.
| = SUMPRODUCT (-(((Kód = TRANSPOSE (H3: H4))+(Rok = TRANSPOSE (I3: I4)))> 0)) |
Toto je vzorec na počítanie s viacerými alebo podmienkami v rovnakom rozsahu, keď sú kritériá uvedené v stĺpci.
Takže áno, kamarát, dúfam, že som mal dosť jasno a malo to zmysel. Dúfam, že to slúži tvojmu účelu byť tu. Ak tento vzorec váš problém nevyriešil, dajte mi vedieť svoje požiadavky v sekcii komentárov nižšie. Rád vám akýmkoľvek spôsobom pomôžem. Môžete spomenúť všetky Excel/VBA súvisiace s pochybnosťami. Do tej doby sa učte, pokračujte v excelovaní.
Ako používať funkciu SUMPRODUCT v programe Excel: Vráti SUMU po vynásobení hodnôt vo viacerých poliach v programe Excel. Túto funkciu je možné použiť na vykonanie viacerých úloh. Je to jedna z najuniverzálnejších funkcií.
COUNTIFS s rozsahom dynamických kritérií : Na počítanie s rozsahom dynamických kritérií jednoducho používame funkciu NEPRIAMY. Táto funkcia môže
COUNTIFS s ALEBO pre viac kritérií : Pomocou funkcie OR spočítajte bunky, ktoré majú viacero kritérií. Na vloženie logiky ALEBO do funkcie COUNTIFS nebudete potrebovať funkciu OR.
Použitie IF s funkciami AND / OR v programe Microsoft Excel : Tieto logické funkcie sa používajú na vykonávanie výpočtov viacerých kritérií. S IF Ak sa na zahrnutie alebo vylúčenie zhody používajú funkcie OR a AND.
Ako používať funkciu ALEBO v programe Microsoft Excel : Funkcia sa používa na zahrnutie všetkých hodnôt TRUE do viacerých kritérií.
Ako počítať bunky, ktoré obsahujú to alebo ono, v programe Excel v programe Excel : Do buniek, ktoré obsahujú to alebo ono, môžeme použiť funkciu SUMPRODUCT. Takto vykonáte tieto výpočty.
Populárne články:
50 skratiek programu Excel na zvýšenie produktivity | Vykonajte svoju úlohu rýchlejšie. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.
Ako používať funkciu Excel VLOOKUP| Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať Excel Funkcia COUNTIF| Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.
Ako používať funkciu SUMIF v programe Excel | Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.