Stiahnite si ukážkový zošit
Tento návod ukazuje, ako používať Excel SUMPRODUCT Funkcia v programe Excel.
Prehľad funkcií SUMPRODUCT
Funkcia SUMPRODUCT vynásobí polia čísel a sčíta výsledné pole.
Ak chcete použiť funkciu pracovného hárka programu SUMPRODUCT Excel, vyberte bunku a zadajte:
(Všimnite si, ako sa zobrazujú vstupy vzorcov)
Funkcia SUMPRODUCT Syntax a vstupy:
| 1 | = SUMPRODUCT (pole1, pole2, pole3) |
pole 1 - Polia čísel.
Čo je funkcia SUMPRODUCT?
Funkcia SUMPRODUCT je jednou z výkonnejších funkcií v programe Excel. Jeho názov vás môže viesť k presvedčeniu, že je určený iba na základné matematické výpočty, ale dá sa použiť aj na oveľa viac.
Polia
SUMPRODUCT vyžaduje zadanie polí.
Po prvé, čo rozumieme pod pojmom „pole“? Pole je jednoduchá skupina položiek (napr. Čísla) usporiadaných v konkrétnom poradí, rovnako ako rozsah buniek. Ak by ste teda mali čísla 1, 2, 3 v bunkách A1: A3, Excel by to prečítal ako pole {1,2,3}. V skutočnosti môžete {1,2,3} zadať priamo do vzorcov programu Excel a rozpozná pole.
Nižšie budeme hovoriť o poliach, ale najprv sa pozrime na jednoduchý príklad.
Základná matematika
Pozrime sa na základný príklad SUMPRODUCT a používame ho na výpočet celkových tržieb.
Máme tabuľku našich produktov a chceme vypočítať celkové tržby. Budete v pokušení jednoducho pridať nový stĺpec, vziať predanú cenu * cenu a potom nový stĺpec zhrnúť. Namiesto toho však môžete jednoducho použiť funkciu SUMPRODUCT. Poďme sa pozrieť na vzorec:
| 1 | = SUMPRODUCT (A2: A4, B2: B4) |
Funkcia načíta rozsahy čísel do polí, vynásobí ich navzájom a potom spočíta výsledky:
| 1234 | = SUMPRODUCT ({100, 50, 10}, {6, 7, 5})= SUMPRODUCT ({100 * 6, 50 * 7, 10 * 5})= SUMPRODUCT ({600, 350, 50}= 1000 |
Funkcia SUMPRODUCT Funciton nám dokázala vynásobiť všetky čísla A urobiť súčet.
Vážený priemer
Ďalším prípadom, kedy je užitočné použiť SUMPRODUCT, je, keď potrebujete vypočítať vážený priemer. K tomu najčastejšie dochádza pri práci v škole, preto sa pozrime na nasledujúcu tabuľku.
Vidíme, ako veľmi stoja kvízy, testy a domáce úlohy z hľadiska celkového hodnotenia, ako aj to, aký je aktuálny priemer pre každú konkrétnu položku. Potom môžeme vypočítať celkovú známku
| 1 | = SUMPRODUCT (B2: B4, C2: C4) |
Pred súčtom súčtov naša funkcia opäť vynásobí každú položku v poliach. Takto to funguje
| 123 | = SUMPRODUCT ({30%, 50%, 20%}, {73%, 90%, 95%})= SUMPRODUCT ({22%, 45%, 19%})= 86% |
Viac stĺpcov
Ďalšie miesto, ktoré by sme mohli použiť SUMPRODUCT, je ešte viac stĺpcov, ktoré je potrebné všetky navzájom vynásobiť. Pozrime sa na príklad, kde musíme vypočítať objem v kusoch reziva.
Namiesto vytvárania pomocného stĺpca na výpočet celkového predaja pre každý riadok to môžeme urobiť pomocou jedného vzorca. Náš vzorec bude
| 1 | = SUMPRODUCT (B2: B5, C2: C5, D2: D5) |
Prvé položky každého poľa sa navzájom znásobia (napr. 4 * 2 * 1 = 8). Potom druhý (4 * 2 * 2 = 16) a 3rdatď. Celkovo to bude produkt radu produktov, ktoré vyzerajú ako {8, 16, 16, 32). Potom by celkový objem bol súčtom tohto poľa, 72.
Jedno kritérium
Dobre, pridajme ďalšiu vrstvu zložitosti. Videli sme, že SUMPRODUCT dokáže spracovať množiny čísel, ale čo keď chceme skontrolovať kritériá? Môžete tiež vytvoriť polia pre booleovské hodnoty (booleovské hodnoty sú hodnoty, ktoré sú PRAVDA alebo NEPRAVDA).
Vezmite napríklad základné pole {1, 2, 3}. Vytvorme zodpovedajúce pole, ktoré indikuje, či je každé číslo väčšie ako 1. Toto pole by vyzeralo ako {FALSE, TRUE, TRUE}.
To je vo vzorcoch veľmi užitočné, pretože PRAVDU / NEPRAVDU môžeme ľahko previesť na 1 / 0. Pozrime sa na príklad.
Pomocou nižšie uvedenej tabuľky chceme vypočítať „Koľko predaných kusov bolo červených?“
Môžeme to urobiť pomocou tohto vzorca:
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "červená")) |
"Počkaj! Čo je tam so symbolom dvojitých mínus? “ ty hovoríš. Pamätáte si, ako som povedal, že môžeme previesť z True/False na 1/0? Robíme to tak, že prinútime počítač vykonať matematickú operáciu. V tomto prípade hovoríme „vezmite zápornú hodnotu a potom znova vezmite zápornú hodnotu“. Keď to napíšeme, naše pole sa zmení takto:
| 123 | {Pravda, pravda, lož}{-1, -1, 0}{1, 1, 0} |
Takže späť k úplnému vzorcu SUMPRODUCT, načítava sa do našich polí a potom sa násobí, takto
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0})= SUMPRODUCT ({100, 50, 0})= 150 |
Všimnite si, ako 3rd položka sa stala 0, pretože čokoľvek vynásobené 0 sa stane nulou.
Viac kritérií
Do našej funkcie môžeme načítať až 255 polí, takže určite môžeme načítať viac kritérií. Pozrime sa na túto väčšiu tabuľku, kde sme pridali predaný mesiac.
Ak chceme vedieť, koľko predaných položiek bolo červených a boli v mesiaci február, mohli by sme napísať náš vzorec ako
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "červená"), -(C2: C4 = "február")) |
Počítač by potom vyhodnotil naše polia a znásobil by sa. Už sme rozoberali, ako sa polia True/False menia na 1/0, takže tento krok zatiaľ preskočím.
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0}, {0, 1, 1})= SUMPRODUCT ({0, 50, 0})= 50 |
V našom príklade sme mali iba jeden riadok, ktorý zodpovedal všetkým kritériám, ale pri skutočných údajoch ste mohli mať spolu spojených viac riadkov, ktoré ste potrebovali.
Komplexné kritériá
Dobre, až do tohto bodu možno nebudete ohromení, pretože všetky naše príklady bolo možné vykonať pomocou iných funkcií, ako je SUMIF alebo COUNTIF. Teraz urobíme niečo pre tieto ďalšie funkcie nemôže urobiť. Predtým mal náš stĺpec Mesiac skutočné názvy mesiacov. Čo keby namiesto toho boli dátumy?
Teraz nemôžeme urobiť SUMIF, pretože SUMIF nedokáže zvládnuť kritériá, ktoré potrebujeme. SUMPRODUCT nás však zvládne manipulovať s poľom a vykonať hlbší test. S poliami sme už manipulovali, keď sme preložili hodnotu True/False na 1/0. S týmto poľom budeme manipulovať pomocou funkcie MONTH. Toto je úplný vzorec, ktorý použijeme
| 1 | = SUMPRODUKT (A2: A4, -(B2: B4 = "červená"), -(MESIAC (C2: C4) = 2)) |
Pozrime sa na 3rd zoradiť bližšie. Najprv náš vzorec extrahuje číslo mesiaca z každého dátumu v C2: C4. To nám poskytne {1, 2, 2}. Ďalej skontrolujeme, či sa táto hodnota rovná 2. Teraz naše pole vyzerá ako {False, True, True}. Znova urobíme dvojnásobok mínus a máme {0, 1, 1}. Teraz sme späť na podobnom mieste, aké sme mali v príklade 3, a náš vzorec nám bude môcť povedať, že vo februári sa predalo 50 kusov, ktoré boli červené.
Dvojité mínus vs. násobenie
Ak ste predtým videli funkciu SUMPRODUCT v prevádzke, možno ste videli trochu iný zápis. Namiesto použitia dvojitého mínusu môžete písať
| 1 | = SUMPRODUCT (A2: A4*(B2: B4 = "červená")*(MESIAC (C2: C4) = 2)) |
Vzorec bude fungovať stále rovnako, iba počítaču ručne povieme, že chceme znásobiť polia. SUMPRODUCT to aj tak urobí, takže v spôsobe, akým matematika funguje, sa nič nemení. Vykonaním matematickej operácie sa naša pravda/nepravda zmení na 1/0 toho istého. Prečo teda ten rozdiel?
Väčšinou na tom príliš nezáleží a závisí to od preferencií používateľov. Existuje však najmenej jeden prípad, keď je potrebné znásobenie.
Keď používate SUMPRODUCT, počítač očakáva, že všetky argumenty (pole1, pole2 atď.) Budú mať rovnakú veľkosť. To znamená, že majú rovnaký počet riadkov alebo stĺpcov. Môžete však urobiť to, čo je známe ako výpočet dvojrozmerného poľa, pomocou SUMPRODUCT, ktorý uvidíme v nasledujúcom príklade. Keď to urobíte, polia majú rôzne veľkosti, takže musíme obísť kontrolu „všetkých rovnakých veľkostí“.
Dva rozmery
Všetky predchádzajúce príklady smerovali naše polia rovnakým smerom. SUMPRODUCT zvládne veci prebiehajúce v dvoch smeroch, ako uvidíme v nasledujúcej tabuľke.
Tu je naša tabuľka predaných jednotiek, ale údaje sú usporiadané tak, aby kategórie prešli cez vrchol. Ak chceme zistiť, koľko položiek bolo červených a v kategórii A, môžeme písať
| 1 | = SUMPRODUCT ((A2: A4 = "červená")*(B1: C1 = "A")*B2: C4) |
Čo sa tu deje?? Ukazuje sa, že sa budeme množiť v dvoch rôznych smeroch. Vizualizáciu je ťažšie vykonať iba pomocou napísanej vety, takže máme niekoľko obrázkov, ktoré nám pomôžu. Najprv sa naše kritériá riadkov (je červená?) Vynásobia každým riadkom v poli.
| 1 | = SUMPRODUCT ((A2: A4 = "ČERVENÁ")*B2: C4) |
Ďalej sa v každom stĺpci vynásobia kritériá stĺpcov (je to kategória A?)
| 1 | = SUMPRODUCT ((A2: A4 = "červená")*(B1: C1 = "A")*B2: C4) |
Potom, čo obe tieto kritériá urobia svoju prácu, zostanú iba nuly 5 a 10. SUMPRODUCT nám potom dá celkovú hodnotu 15 ako našu odpoveď.
Pamätáte si, ako sme hovorili o poliach, ktoré musia mať rovnakú veľkosť, pokiaľ nerobíte dve dimenzie? To bolo čiastočne správne. Znovu sa zameriava na polia, ktoré sme použili v našom vzorci. The výška z dvoch našich polí je rovnaká a šírka z dvoch našich polí sú rovnaké. Stále sa však musíte uistiť, že veci budú správne zoradené, ale môžete to urobiť v rôznych dimenziách.
Dva rozmery a komplex
Mnohokrát sa nám zobrazia údaje, ktoré nie sú v najlepšom rozložení vhodnom pre naše vzorce. Mohli by sme to skúsiť manuálne preusporiadať, alebo môžeme byť múdrejší so svojimi vzorcami. Pozrime sa na nasledujúcu tabuľku.
Tu máme údaje o našich položkách a tržbách zmiešané za každý mesiac. Ako by sme zistili, koľko položiek Bob predal za celý rok?
Na tento účel použijeme dve ďalšie funkcie: VYHĽADÁVAŤ a ISNUMBER. Funkcia VYHĽADÁVAŤ nám umožní vyhľadať naše kľúčové slovo „položky“ v bunkách hlavičky. Výstup z tejto funkcie bude zadaný buď číslom, alebo chybou (ak sa kľúčové slovo nenájde). Potom použijeme na konverziu ISNUMBER že výstup do našich booleovských hodnôt. Náš vzorec bude vyzerať nižšie.
Prvé pole by ste už mali celkom dobre poznať. Vytvorí sa výstup ako {0, 1, 0, 1}. Ďalšie pole kritérií, o ktorom sme práve hovorili. Vytvorí číslo pre všetky bunky, v ktorých sú uvedené „položky“, a pre ostatné {5, #N/A !, 5, #N/A!}. ISNUMBER to potom skonvertuje na booleovskú hodnotu {True, False, True, False}. Keď sa potom vynásobíme, zachová sa iba hodnota z prvého a tretieho stĺpca. Potom, čo sa všetky polia množia proti sebe, jediné nenulové čísla, ktoré budeme mať, sú tie, ktoré sú tu zvýraznené:
| 1 | = SUMPRODUCT ((A2: A5 = "Bob")*(ISNUMBER (VYHĽADÁVANIE ("Položky", B1: E1))*B2: E5)) |
SUMPRODUCT ich potom všetky spočíta a dostaneme konečný výsledok 29.
SUMPRODUCT Or
Mnoho situácii nastáva, keď by sme chceli byť schopní zhrnúť hodnoty, ak má náš stĺpec kritérií jednu hodnotu ALEBO inú hodnotu. Môžete to dosiahnuť v SUMPRODUCT pridaním dvoch polí kritérií proti sebe.
V tomto prípade chceme sčítať jednotky predané za červenú aj modrú.
Náš vzorec bude vyzerať takto
| 1 | = SUMPRODUCT (A2: A7, (B2: B7 = "červená")+(B2: B7 = "modrá")) |
Pozrime sa na červené pole kritérií. Výsledkom bude pole, ktoré vyzerá takto: {1, 1, 0, 0, 0, 0}. Modré pole kritérií bude vyzerať ako {0, 0, 1, 0, 1, 0}. Keď ich spojíte, nové pole bude vyzerať ako {1, 1, 1, 0, 1, 0}. Vidíme, ako sa tieto dve polia spojili do jedného poľa kritérií. Funkcia to potom vynásobí naším prvým poľom a dostaneme {100, 50, 10, 0, 75, 0}. Všimnite si, že hodnoty pre zelenú boli vynulované. Posledným krokom SUMPRODUKTU je sčítanie všetkých čísel, aby sme dosiahli naše riešenie 235.
Tu je potrebné dávať pozor. Dávajte si pozor na to, keď sa polia kritérií navzájom nevylučujú. V našom prípade hodnoty v stĺpci B mohli byť buď červené alebo modré, ale vedeli sme, že to nikdy nemôže byť oboje. Zvážte, či sme napísali tento vzorec:
| 1 | = SUMPRODUCT (A2: A7, (A2: A7> = 50)+(B2: B7 = "modrá")) |
Našim cieľom je nájsť modré položky, ktoré boli predané alebo boli v množstve vyššom ako 50. Tieto podmienky však nie sú exkluzívne, pretože v jednom riadku môže byť v stĺpci A aj viac ako 50 kusov. a byť modrý. To by malo za následok, že prvé pole kritérií bude vyzerať ako {1, 1, 0, 1, 1, 0}, druhé pole kritérií bude {0, 0, 1, 0, 1, 0}. Ich sčítaním vzniklo {1, 1, 1, 1, 2, 0}. Vidíte, ako tam teraz máme dvojku? Ak by sme to nechali tak, SUMPRODUCT by v tomto riadku zdvojnásobil hodnotu, zmenil by hodnotu 75 na 150 a dostali by sme nesprávny výsledok. Aby sme to napravili, vložíme do nášho poľa kontrolu vonkajších kritérií takto:
| 1 | = SUMPRODUCT (A2: A7, -((A2: A7> = 50)+(B2: B7 = "modrá")> 0)) |
Teraz, keď sa dve polia vnútorných kritérií spoja, skontrolujeme, či je výsledok väčší ako 0. Tým sa odstránia 2, ktoré sme mali predtým, a namiesto toho budeme mať pole ako {1, 1, 1 , 1, 1, 0}, ktoré prinesie správny výsledok.
SUMPRODUCT Presný
Väčšina funkcií v Exceli nerozlišuje malé a veľké písmená, ale niekedy musíme byť schopní vykonať vyhľadávanie s ohľadom na veľkosť písmen. Keď je požadovaný výsledok numerický, môžeme to dosiahnuť pomocou funkcie EXACT vo funkcii SUMPRODUCT. Zvážte nasledujúcu tabuľku:
Chceme nájsť skóre pre položku „ABC123“. Funkcia EXACT za normálnych okolností porovná dve položky a vráti booleovský výstup s uvedením, či tieto dve položky sú presne tak rovnaký. Keďže sme však v rámci SUMPRODUKTU, náš počítač bude vedieť, že pracujeme s poliami, a bude schopný porovnať jednu položku s každou položkou v poli. Náš vzorec bude vyzerať takto
| 1 | = SUMPRODUCT (-PRESNE ("ABC123", A2: A5), B2: B5) |
Funkcia EXACT potom skontroluje každú položku v formáte A2: A5 a zistí, či sa zhoduje s hodnotou a veľkými písmenami. Výsledkom bude pole, ktoré vyzerá ako {0, 1, 0, 0}. Pri vynásobení proti B2: B5 sa pole zmení na {0, 2, 0, 0}. Po konečnom súčte dostaneme riešenie 2.
SUMPRODUCT v Tabuľkách Google
Funkcia SUMPRODUCT funguje v Tabuľkách Google úplne rovnako ako v programe Excel:
SUMPRODUCT Príklady vo VBA
Vo VBA môžete použiť aj funkciu SUMPRODUCT. Typ: application.worksheetfunction.sumproduct (pole1, pole2, pole3)
Vykonanie nasledujúcich vyhlásení VBA
| 1 | Rozsah ("B10") = Application.WorksheetFunction.SumProduct (rozsah ("A2: A7"), rozsah ("B2: B7")) |
prinesie nasledujúce výsledky
Pokiaľ ide o argumenty funkcie (pole1 atď.), Môžete ich buď zadať priamo do funkcie, alebo definovať premenné, ktoré sa namiesto toho použijú.
