Töltse le a példa munkafüzetet
Ez a bemutató bemutatja, hogyan kell használni Excel SUMPRODUCT függvény az Excelben.
SUMPRODUCT Funkció áttekintés
A SUMPRODUCT függvény megszámolja a számok tömbjeit és összegzi a kapott tömböt.
A SUMPRODUCT Excel munkalap funkció használatához válasszon ki egy cellát, és írja be:
(Figyelje meg, hogyan jelennek meg a képletbemenetek)
SUMPRODUCT funkció Szintaxis és bemenetek:
| 1 | = SUMPRODUCT (tömb1, tömb2, tömb3) |
tömb1 - Számok tömbjei.
Mi a SUMPRODUCT funkció?
A SUMPRODUCT függvény az Excel egyik legerősebb funkciója. A név alapján azt hiheted, hogy csak alapvető matematikai számításokhoz készült, de sokkal többre használható.
Tömbök
A SUMPRODUCT tömbök bemenetét igényli.
Tehát először is mit értünk „tömb” alatt? A tömb egyszerű elemek (pl. Számok) csoportja, amelyek meghatározott sorrendben vannak elrendezve, akárcsak egy cellatartomány. Tehát, ha az A1: A3 cellákban az 1, 2, 3 számok szerepelnének, az Excel ezt {1, 2, 3} tömbként fogja olvasni. Valójában közvetlenül beírhatja az {1,2,3} értéket az Excel képletekbe, és felismeri a tömböt.
Az alábbiakban többet fogunk beszélni a tömbökről, de először nézzünk egy egyszerű példát.
Alap matematika
Nézzünk egy alapvető példát a SUMPRODUCT -ra, amely az összes értékesítés kiszámítására szolgál.
Megvan a terméktáblánk, és ki akarjuk számítani a teljes értékesítést. Kísértést érez, hogy csak egy új oszlopot ad hozzá, vegye fel az eladott mennyiség * árát, majd összesítse az új oszlopot. Ehelyett azonban egyszerűen használhatja a SUMPRODUCT funkciót. Nézzük végig a képletet:
| 1 | = SUMPRODUCT (A2: A4, B2: B4) |
A függvény betölti a számtartományokat tömbökbe, többszörözi őket egymás ellen, majd összegzi az eredményeket:
| 1234 | = SUMPRODUCT ({100, 50, 10}, {6, 7, 5})= SUMPRODUCT ({100 * 6, 50 * 7, 10 * 5})= SUMPRODUCT ({600, 350, 50}= 1000 |
A SUMPRODUCT Funciton meg tudta szorozni számunkra az összes számot, és elvégezte az összegzést.
Súlyozott átlag
Egy másik eset, amikor hasznos a SUMPRODUCT használata, amikor súlyozott átlagot kell kiszámítania. Ez leggyakrabban az iskolai feladatok során fordul elő, ezért vegyük figyelembe az alábbi táblázatot.
Láthatjuk, hogy mennyit érnek a vetélkedők, tesztek és házi feladatok az általános osztályzathoz képest, valamint azt is, hogy mennyi az aktuális átlag minden egyes tételnél. Ekkor írással kiszámíthatjuk a teljes osztályzatot
| 1 | = SUMPRODUCT (B2: B4, C2: C4) |
Funkciónk ismét megszorozza a tömbök minden elemét, mielőtt összegezné az összeset. Ez így működik
| 123 | = ÖSSZEFOGLALÁS ({30%, 50%, 20%}, {73%, 90%, 95%})= SUMPRODUCT ({22%, 45%, 19%})= 86% |
Több oszlop
Egy másik hely, ahol használhatjuk a SUMPRODUCT -t, még több oszlopot tartalmaz, amelyek mindegyikét meg kell szorozni. Nézzünk egy példát, ahol ki kell számolnunk a térfogatot fadarabokban.
Ahelyett, hogy létrehoznánk egy segítő oszlopot az egyes sorok teljes eladásának kiszámításához, ezt egyetlen képlettel tehetjük meg. A képletünk az lesz
| 1 | = SUMPRODUCT (B2: B5, C2: C5, D2: D5) |
Az egyes tömbök első elemei szaporodnak egymás ellen (például 4 * 2 * 1 = 8). Ezután a második (4 * 2 * 2 = 16) és 3rd, stb. Összességében ez olyan termékcsaládot eredményez, amely úgy néz ki, mint {8, 16, 16, 32). Ekkor a teljes térfogat a tömb összege, 72.
Egy kritérium
Rendben, adjunk hozzá még egy bonyolultsági réteget. Láttuk, hogy a SUMPRODUCT képes kezelni a szám tömböket, de mi van akkor, ha meg akarjuk vizsgálni a feltételeket? Nos, létrehozhat tömböket is a logikai értékekhez (a logikai értékek IGAZ vagy HAMIS értékek).
Vegyünk például egy alap tömböt {1, 2, 3}. Hozzon létre egy megfelelő tömböt, amely jelzi, ha minden szám nagyobb, mint 1. Ez a tömb így nézne ki: {FALSE, TRUE, TRUE}.
Ez rendkívül hasznos a képletekben, mert könnyen át tudjuk alakítani az IGAZ / HAMIS értéket 1 /0 -ra. Nézzünk egy példát.
Az alábbi táblázat segítségével szeretnénk kiszámítani: „Hány darab volt piros?”
Ezt a következő képlettel tehetjük meg:
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "Piros")) |
"Kitartás! Mi van a kettős mínusz szimbólummal? " te mondod. Emlékszel, hogyan mondtam, hogy az Igaz/hamis értéket 1/0 -ra tudjuk konvertálni? Ezt úgy tesszük, hogy a számítógépet matematikai műveletre kényszerítjük. Ebben az esetben azt mondjuk, hogy „vegyük a negatív értéket, majd vegyük újra a negatívat”. Ezt kiírva tömbünk a következőképpen fog változni:
| 123 | {Igaz, igaz, hamis}{-1, -1, 0}{1, 1, 0} |
Tehát visszatérve a teljes SUMPRODUCT képlethez, betöltődik a tömbjeinkbe, majd szaporodik, mint ez
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0})= SUMPRODUCT ({100, 50, 0})= 150 |
Jegyezze meg, hogy a 3rd tételből 0 lett, mert minden 0 -val megszorozva nullává válik.
Több kritérium
Akár 255 tömböt is betölthetünk a funkciónkba, így minden bizonnyal több kritériumot is betölthetünk. Nézzük ezt a nagyobb táblázatot, ahol hozzáadtuk az eladott hónapot.
Ha tudni akarjuk, hány eladott termék volt piros és február hónapban voltunk, úgy írhatnánk a képletünket
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "Piros"), -(C2: C4 = "február")) |
A számítógép ezután kiértékeli tömbjeinket, és szaporodik. Már kitértünk arra, hogy az Igaz/hamis tömbök hogyan válnak 1/0 -ra, ezért ezt a lépést most kihagyom.
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0}, {0, 1, 1})= SUMPRODUCT ({0, 50, 0})= 50 |
Példánkban csak egy sor volt, amely minden feltételnek megfelelt, de valódi adatokkal előfordulhat, hogy több sort kellett volna összeadnia.
Összetett kritériumok
Rendben, eddig talán nem leszel lenyűgözve, mert példáinkat más függvényekkel is megtehettük volna, mint például SUMIF vagy COUNTIF. Most valami más funkcióval fogunk foglalkozni nem lehet tedd. Korábban a Hónap rovatunkban a hónapok tényleges neve szerepelt. Mi lenne, ha helyette dátumok lennének?
Most nem tudunk SUMIF -ot csinálni, mert a SUMIF nem tudja kezelni a szükséges kritériumokat. A SUMPRODUCT azonban képes kezelni a tömb manipulálását és egy mélyebb vizsgálatot. Már manipuláltuk a tömböket, amikor az Igazot/hamisat 1/0 -ra fordítottuk. Ezt a tömböt a MONTH függvénnyel fogjuk manipulálni. Itt a teljes képlet, amelyet használni fogunk
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "Piros"), -(HÓNAP (C2: C4) = 2)) |
Nézzük a 3rd tömörítse szorosabban. Először is, a képletünk kivonja a hónap számát a C2: C4 minden dátumából. Ezzel {1, 2, 2} értéket kapunk. Ezután ellenőrizzük, hogy ez az érték egyenlő -e 2 -vel. Most tömbünk így néz ki: {False, True, True}. Ismét megtesszük a dupla mínuszt, és van {0, 1, 1}. Most visszatértünk egy hasonló helyre, mint a 3. példában, és a képletünkből megtudhatjuk, hogy februárban 50 darab piros volt.
Kettős mínusz vs szorzás
Ha már látta a SUMPRODUCT funkció használatát, akkor lehet, hogy kissé eltérő jelölést látott. A kettős mínusz helyett írhat
| 1 | = SUMPRODUCT (A2: A4*(B2: B4 = "Piros")*(HÓNAP (C2: C4) = 2)) |
A képlet továbbra is ugyanúgy fog működni, csak manuálisan közöljük a számítógéppel, hogy meg akarjuk szorozni a tömböket. A SUMPRODUCT ezt mindenesetre meg akarta tenni, tehát nincs változás a matematika működésében. A matematikai művelet végrehajtása során a True/False értéket 1/0 értékre alakítjuk át. Szóval, miért a különbség?
Legtöbbször ez nem számít túl sokat, és a felhasználók preferenciáitól függ. Van azonban legalább egy eset, amikor szorozni kell.
A SUMPRODUCT használatakor a számítógép elvárja, hogy az összes argumentum (tömb1, tömb2 stb.) Azonos méretű legyen. Ez azt jelenti, hogy azonos számú soruk vagy oszlopuk van. Mindazonáltal megteheti a kétdimenziós tömbszámítást a SUMPRODUCT segítségével, amelyet a következő példában látunk. Amikor ezt megteszi, a tömbök különböző méretűek, ezért ki kell hagynunk ezt az „azonos méretű” ellenőrzést.
Két dimenzió
Az összes korábbi példában tömbjeink ugyanabba az irányba mentek. A SUMPRODUCT két irányba tudja kezelni a dolgokat, amint azt a következő táblázatban látni fogjuk.
Itt található az eladott egységek táblázata, de az adatok átrendeződnek, ahol a kategóriák a tetején haladnak. Ha azt szeretnénk megtudni, hogy hány elem volt piros és az A kategória, írhatunk
| 1 | = SUMPRODUCT ((A2: A4 = "Piros")*(B1: C1 = "A")*B2: C4) |
Mi történik itt?? Kiderült, hogy kétféle irányba fogunk szaporodni. Ennek vizualizálása nehezebb, mint egy írásos mondat, ezért van néhány képünk, amelyek segítenek nekünk. Először is, a sorfeltételeink (piros?) A tömb minden sorában szaporodni fognak.
| 1 | = SUMPRODUCT ((A2: A4 = "RED")*B2: C4) |
Ezután az oszlopfeltételek (A kategória?) Minden oszlopot lefelé szoroznak
| 1 | = SUMPRODUCT ((A2: A4 = "Piros")*(B1: C1 = "A")*B2: C4) |
Miután mindkét kritérium elvégezte a dolgát, az egyetlen nem nulla az 5 és a 10. A SUMPRODUCT ezután a 15-ös végösszeget adja meg válaszként.
Emlékszel, hogyan beszéltünk arról, hogy a tömböknek azonos méretűnek kell lenniük, hacsak nem két dimenziót csinálsz? Ez részben helyes volt. Újra megnézi a képletünkben használt tömböket. Az magasság két tömbünkből ugyanaz, és a szélesség két tömbünkből ugyanaz. Tehát még mindig meg kell győződnie arról, hogy a dolgok helyesen fognak -e sorakozni, de ezt különböző dimenziókban is megteheti.
Két dimenzió és összetett
Sokszor olyan adatokat mutatunk be, amelyek nem a képleteinknek megfelelő elrendezésben vannak. Megpróbálhatjuk manuálisan átrendezni, vagy okosabbak lehetünk a képleteinkkel. Tekintsük az alábbi táblázatot.
Itt minden hónapban összevonjuk a tételeinkre és az eladásokra vonatkozó adatokat. Hogyan tudnánk megtudni, hány darabot adott el Bob egész évben?
Ehhez két további funkciót fogunk használni: SEARCH és ISNUMBER. A KERESÉS funkció lehetővé teszi, hogy a fejléc celláiban keressük a „tételek” kulcsszót. Ennek a függvénynek a kimenete vagy szám vagy hiba miatt (ha a kulcsszó nem található). Ezután az ISNUMBER számot használjuk a konverzióhoz hogy logikai értékeinkbe. A képletünk az alábbiak szerint fog kinézni.
Már eléggé ismernie kell az első tömböt. Olyan kimenetet hoz létre, mint a {0, 1, 0, 1}. A következő kritériumtömb, amiről most beszéltünk. Számot hoz létre az összes cellához, amelyben szerepel az „Elemek”, a többihez pedig hiba {5, #N/A !, 5, #N/A!}. A SZÁM akkor ezt logikai {True, False, True, False} -ra konvertálja. Aztán amikor szorzunk, akkor csak az első és a harmadik oszlop értékei maradnak meg. Miután az összes tömb megszorozódott egymással szemben, csak az itt kiemelt számok lesznek nullától eltérő számok:
| 1 | = SUMPRODUCT ((A2: A5 = "Bob")*(ISNUMBER (KERESÉS ("Elemek", B1: E1))*B2: E5)) |
A SUMPRODUCT összeadja ezeket, és megkapjuk a 29 -es végeredményt.
ÖSSZEFOGLALÁS Or
Sok olyan helyzet adódik, amikor szeretnénk összegezni az értékeket, ha a feltétel oszlopban van egy vagy másik érték. Ezt a SUMPRODUCT -ban érheti el, ha két kritériumtömböt ad egymáshoz.
Ebben a példában össze szeretnénk adni a vörös és kék árusított egységeket.
A képletünk így fog kinézni
| 1 | = SUMPRODUCT (A2: A7, (B2: B7 = "Piros")+(B2: B7 = "Kék")) |
Nézzük a vörös kritériumok tömbjét. A tömb így fog kinézni: {1, 1, 0, 0, 0, 0}. A Kék feltétel tömb így fog kinézni: {0, 0, 1, 0, 1, 0}. Ha összeadja őket, az új tömb {1, 1, 1, 0, 1, 0} formátumú lesz. Láthatjuk, hogyan keveredett a két tömb egyetlen kritériumtömbbe. A függvény ezt megszorozza az első tömbünkkel, és {100, 50, 10, 0, 75, 0} értéket kapunk. Vegye figyelembe, hogy a zöld értékek nullázódtak. A SUMPRODUCT utolsó lépése az összes szám összeadása, hogy elérjük a 235 -ös megoldást.
Itt egy óvatos szó. Legyen óvatos, ha a kritériumtömbök nem zárják ki egymást. Példánkban a B oszlop értékei lehetnek vörösek vagy kékek, de tudtuk, hogy soha nem lehet mindkettő. Gondoljuk meg, ha ezt a képletet írtuk volna:
| 1 | = SUMPRODUCT (A2: A7, (A2: A7> = 50)+(B2: B7 = "kék")) |
Célunk, hogy olyan kék árucikkeket találjunk, amelyeket eladtak vagy 50 -nél nagyobb mennyiségben értékesítettek. Ezek a feltételek azonban nem kizárólagosak, mivel az A oszlop egyetlen sora 50 feletti is lehet. és legyen kék. Ennek eredményeként az első feltételtömb {1, 1, 0, 1, 1, 0}, így a második feltételtömb {0, 0, 1, 0, 1, 0}. Ha összeadjuk őket, akkor {1, 1, 1, 1, 2, 0} lesz. Látod, hogy most ott van a 2 -es? Ha magára hagyjuk, a SUMPRODUCT végül megduplázza az adott sor értékét, a 75 -et 150 -re változtatja, és rossz eredményt kapunk. Ennek korrigálására külső kritériumellenőrzést teszünk tömbünkre, például:
| 1 | = SUMPRODUCT (A2: A7, -((A2: A7> = 50)+(B2: B7 = "kék")> 0)) |
Most, miután a két belső kritériumtömböt összeadtuk, ellenőrizni fogjuk, hogy az eredmény nagyobb -e, mint 0. Ezzel megszabadulunk az előző kettőtől, és helyette olyan tömböt kapunk, mint az {1, 1, 1 , 1, 1, 0}, amely a helyes eredményt eredményezi.
SUMPRODUCT Pontos
Az Excel legtöbb funkciója nem különbözteti meg a kis- és nagybetűket, de néha képesnek kell lennünk a kis- és nagybetűk megkülönböztetésére irányuló keresésre. Ha a kívánt eredmény számszerű, akkor ezt a SUMPRODUCT funkcióban lévő EXACT használatával érhetjük el. Tekintsük a következő táblázatot:
Meg akarjuk találni az „ABC123” tétel pontszámát. Általában a PONTOS függvény két elemet hasonlít össze, és egy logikai kimenetet ad vissza, amely jelzi, hogy a két tétel megfelel -e pontosan ugyanaz. Mivel azonban egy SUMPRODUCT -ben vagyunk, számítógépünk tudni fogja, hogy tömbökkel van dolgunk, és képes lesz összehasonlítani egy elemet a tömb minden elemével. A képletünk így fog kinézni
| 1 | = SUMPRODUCT (-PONTOS ("ABC123", A2: A5), B2: B5) |
A PONTOS funkció ezután ellenőrzi az A2: A5 minden elemét, hogy megfelel -e az értéknek és a kis- és nagybetűknek. Ez olyan tömböt eredményez, amely {0, 1, 0, 0} kinézetű. A B2: B5 ellen megszorozva a tömb {0, 2, 0, 0} lesz. A végső összegzés után megkapjuk a 2 -es megoldást.
SUMPRODUCT a Google Táblázatokban
A SUMPRODUCT funkció pontosan ugyanúgy működik a Google Táblázatokban, mint az Excelben:
ÖSSZEFOGLALÁS Példák a VBA -ban
A VBA SUMPRODUCT funkcióját is használhatja. Típus: application.worksheetfunction.sumproduct (tömb1, tömb2, tömb3)
A következő VBA utasítások végrehajtása
| 1 | Tartomány ("B10") = Application.WorksheetFunction.SumProduct (tartomány ("A2: A7"), tartomány ("B2: B7")) |
a következő eredményeket hozza
A függvény argumentumok (tömb1 stb.) Esetén megadhatja azokat közvetlenül a függvénybe, vagy definiálhat változókat.
