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 alebo kritériami.
Generický vzorec SUMPRODUCT na počítanie s viacerými alebo kritériami
| = SUMPRODUKT (-((((kritéria1)+(kritéria2)+…)> 0) |
Kritériá 1: Toto sú akékoľvek kritériá, ktoré vracajú pole TRUE a FALSE.
Kritériá 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ť.
Problém:
Cena v bunke by mala byť presne rozdelená na 5 podobných platieb. Pri jednoduchom delení ceny 88,04 dolára na 5 a zaokrúhlení výsledku na dve desatinné miesta dostaneme jednotlivé platby 17,61 dolára. To by však znamenalo celkovú platbu vo výške 88,05 USD, čo nie je presná čiastka, s ktorou začíname. Preto chceme vypočítať 5 podobných čísel, ktoré súčtom celkovej sumy.
Riešenie:
Najprv vypočítajte prvú platbu vydelením ceny piatimi a zaokrúhlením výsledku.
Použite funkciu ROUND v nasledujúcom vzorci:
= KOLO (B1/5,2)
Krok 2:
Vypočítajte každú zostávajúcu platbu.
Funkcie ROUND, SUM a ROW použite v nasledujúcom vzorci:
= KOLO (($ B $ 1-SÚČET ($ B $ 2: B2))/(5-RIADOK ()+ROW ($ B $ 2)), 2)
Príklad:
Počítajte používateľov, ak sa kód predajcu zhoduje alebo uvádza rok pomocou 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), má sa počítať ako 1.
Počúvajte, že 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. A tieto záznamy sa počítajú ako 1. A takto získame 7.
Pozrime sa, ako to funguje?
Pozrime sa teda na to, ako je vzorec vyriešený krok za krokom, potom prediskutujem, ako funguje.
-
- = SUMPRODUKT(-((((Kód = I3)+(rok = K3))> 0))
- SUMPRODUCT(-((({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE;…}+{FALSE; FALSE; FALSE; TRUE; TRUE; …})> 0))
- SUMPRODUCT(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0))
- SUMPRODUCT(-({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; …})
- SUMPRODUCT({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})
7
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.
Pridanie ďalších alebo kritérií na počítanie pomocou SUMPRODUCT, takže ak potrebujete pridať ďalšie alebo kritériá na počítanie, stačí pridať funkcie pomocou znamienka +.
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?
Ale povedzme, že chcete mať dve kritériá z radu Code. Povedzme, že chcete počítať „INKB“. Ako to potom robí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 nastavenie počtu ALEBO kritérií troch bude:
| = SUMPRODUCT (-(((Kód = I2: J2)+(rok = I3: J3))> 0)) |
Toto je vzorec SUMPRODUCT na počítanie s viacerými kritériami, keď je do riadka zapísaných viac kritérií z jedného rozsahu.
Vyššie uvedený vzorec vráti správnu 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 rade. Bude to fungovať, ak budú 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 dôjde, pretože tento článok je 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.
Tu sú všetky pozorovacie poznámky týkajúce sa použitia vzorca.
Poznámky:
- Vzorec funguje pre text aj pre čísla.
- Hodnoty vyhľadávania je možné zadať ako odkaz na bunku alebo priamo pomocou symbolu úvodzovky (") vo vzorci ako argumentov.
Dúfam, že ste pochopili, ako rozdeliť čiastku na rovnaké platby a vyhnúť sa chybám pri delení / zaokrúhľovaní v programe Excel. Tu nájdete ďalšie články o hodnotách vyhľadávania v Exceli a funkciách Excelu 2019. Ak sa vám páčili naše blogy, zdieľajte ich so svojimi priateľmi na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku. Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na emailovú stránku.
Na vyhľadanie hodnoty použite INDEX a MATCH : Funkcia INDEX & MATCH na vyhľadanie hodnoty podľa potreby.
Rozsah SUM s INDEX v Exceli : Pomocou funkcie INDEX nájdite SÚČET hodnôt podľa potreby.
Ako používať funkciu INDEX v programe Excel : Nájdite INDEX poľa pomocou funkcie INDEX vysvetlenej na príklade.
Ako používať funkciu MATCH v programe Excel : Nájdite MATCH v poli pomocou hodnoty INDEX vo funkcii MATCH vysvetlenej na príklade.
Ako používať funkciu LOOKUP v programe Excel : Nájdite hodnotu vyhľadávania v poli pomocou funkcie LOOKUP vysvetlenej na príklade.
Populárne články:
Ako používať funkciu IF v programe Excel : Príkaz IF v programe Excel skontroluje podmienku a vráti konkrétnu hodnotu, ak je podmienka PRAVDA, alebo vráti inú konkrétnu hodnotu, ak je NEPRAVDA.
Ako používať funkciu VLOOKUP v programe Excel : 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ť 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.
Ako používať funkciu COUNTIF v programe Excel : 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.