Laadige alla töövihiku näide
See õpetus näitab, kuidas Funktsioon Excel SUMPRODUCT Excelis.
SUMPRODUCT Funktsiooni ülevaade
Funktsioon SUMPRODUCT Korrutab numbrimassiive ja summeerib saadud massiivi.
SUMPRODUCT Exceli töölehe funktsiooni kasutamiseks valige lahter ja tippige:
(Pange tähele, kuidas valemisisendid ilmuvad)
Funktsioon SUMPRODUCT Süntaks ja sisendid:
| 1 | = SUMPRODUCT (massiiv1, massiiv2, massiiv3) |
massiiv1 - numbrimassiivid.
Mis on funktsioon SUMPRODUCT?
Funktsioon SUMPRODUCT on üks Exceli võimsamaid funktsioone. Selle nimi võib panna teid uskuma, et see on mõeldud ainult põhilisteks matemaatilisteks arvutusteks, kuid seda saab kasutada palju enamaks.
Massiivid
SUMPRODUCT nõuab massiivide sisendeid.
Esiteks, mida me mõtleme "massiivi" all? Massiiv on lihtne elementide rühm (nt numbrid), mis on paigutatud kindlasse järjekorda, nagu lahtrite vahemik. Seega, kui teil oleks lahtrites A1: A3 numbrid 1, 2, 3, loeks Excel seda massiivina {1,2,3}. Tegelikult saate sisestada {1,2,3} otse Exceli valemitesse ja see tunneb massiivi ära.
Me räägime allpool massiividest rohkem, kuid vaatame kõigepealt lihtsat näidet.
Põhiline matemaatika
Vaatame SUMPRODUCTi põhinäidet, kasutades seda kogumüügi arvutamiseks.
Meil on oma toodete tabel ja me tahame arvutada kogumüüki. Sul on kiusatus lihtsalt uus veerg lisada, võtta müüdud koguse * hind ja seejärel uus veerg kokku võtta. Selle asemel saate aga lihtsalt kasutada funktsiooni SUMPRODUCT. Lähme läbi valemi:
| 1 | = SUMPRODUCT (A2: A4, B2: B4) |
Funktsioon laadib numbrivahemikud massiividesse, korrutab need üksteise vastu ja summeerib seejärel tulemused:
| 1234 | = SUMPRODUCT ({100, 50, 10}, {6, 7, 5})= SUMPRODUCT ({100 * 6, 50 * 7, 10 * 5})= SUMPRODUCT ({600, 350, 50}= 1000 |
SUMPRODUCT Funciton suutis meie jaoks kõik numbrid korrutada ja liita.
Kaalutud keskmine
Veel üks juhtum, kus on kasulik kasutada SUMPRODUCT -i, on kaalutud keskmise arvutamine. See juhtub kõige sagedamini koolitööga tegelemisel, seega kaaluge järgmist tabelit.
Näeme, kui palju viktoriinid, testid ja kodutööd üldise hinde jaoks väärt on, aga ka seda, milline on iga konkreetse eseme praegune keskmine. Saame siis üldhinnangu arvutada kirjutades
| 1 | = SUMPRODUCT (B2: B4, C2: C4) |
Meie funktsioon korrutab uuesti massiivide iga üksuse enne kogusumma summeerimist. See toimib nii
| 123 | = SUMPRODUCT ({30%, 50%, 20%}, {73%, 90%, 95%})= SUMPRODUCT ({22%, 45%, 19%})= 86% |
Mitu veergu
Teine koht, kus me võiksime SUMPRODUCT -i kasutada, on veelgi rohkem veerge, mida tuleb üksteise vastu korrutada. Vaatame näidet, kus peame arvutama saematerjali tükkide mahu.
Selle asemel, et luua iga rea kogumüügi arvutamiseks abiveerg, saame seda teha ühe valemiga. Meie valem saab olema
| 1 | = SUMPRODUCT (B2: B5, C2: C5, D2: D5) |
Iga massiivi esimesed üksused korrutatakse üksteise vastu (nt 4 * 2 * 1 = 8). Seejärel teine (4 * 2 * 2 = 16) ja 3rdjne. Üldiselt annab see tootevaliku, mis näeb välja nagu {8, 16, 16, 32). Siis oleks kogumaht selle massiivi summa, 72.
Üks kriteerium
Olgu, lisame veel ühe keerukuse kihi. Oleme näinud, et SUMPRODUCT saab hakkama numbrimassiividega, aga mis siis, kui tahame kriteeriume kontrollida? Noh, saate luua ka massiive loogiliste väärtuste jaoks (loogilised väärtused on väärtused, mis on tõesed või valed).
Võtke näiteks põhimassiiv {1, 2, 3}. Loome vastava massiivi, mis näitab, kas iga number on suurem kui 1. See massiiv näeks välja nagu {FALSE, TRUE, TRUE}.
See on valemites äärmiselt kasulik, sest saame tõe / vale väärtuse hõlpsasti teisendada 1 / 0. Vaatame näidet.
Kasutades allolevat tabelit, tahame arvutada "Mitu ühikut oli punane?"
Me saame seda teha järgmise valemi abil:
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "punane")) |
"Oota! Mis seal topelt miinuse sümboliga on? " sa ütled. Mäletate, kuidas ma ütlesin, et saame tõest/valest teisendada 1/0 -ks? Me teeme seda, sundides arvutit tegema matemaatilist toimingut. Sel juhul ütleme: "Võtke negatiivne väärtus ja võtke siis uuesti negatiivne". Selle välja kirjutades muutub meie massiiv järgmiselt:
| 123 | {Tõsi, tõsi, vale}{-1, -1, 0}{1, 1, 0} |
Niisiis, tagasi täieliku SUMPRODUCT valemi juurde, laaditakse see meie massiividesse ja seejärel korrutatakse niimoodi
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0})= SUMPRODUCT ({100, 50, 0})= 150 |
Pange tähele, kuidas 3rd üksusest sai 0, sest kõik, mis korrutatakse 0 -ga, muutub nulliks.
Mitu kriteeriumi
Saame oma funktsiooni laadida kuni 255 massiivi, seega saame kindlasti laadida rohkem kriteeriume. Vaatame seda suuremat tabelit, kuhu oleme lisanud müüdud kuu.
Kui tahame teada, kui palju müüdud esemeid oli punane ja olid veebruaris, võiksime oma valemi kirjutada nagu
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "Red"), -(C2: C4 = "Feb")) |
Seejärel hindab arvuti meie massiive ja korrutab. Oleme juba käsitlenud, kuidas tõese/vale massiivid muutuvad 1/0 -ks, nii et jätan selle sammu praegu vahele.
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0}, {0, 1, 1})= SUMPRODUCT ({0, 50, 0})= 50 |
Meie näites oli ainult üks rida, mis vastas kõigile kriteeriumidele, kuid tegelike andmetega oleksite võinud mitu rida kokku liita.
Komplekssed kriteeriumid
Olgu, siiani ei pruugi te sellest muljet avaldada, sest kõiki meie näiteid oleks saanud teha muude funktsioonidega, näiteks SUMIF või COUNTIF. Nüüd teeme midagi nende muude funktsioonidega ei saa teha. Varem oli meie veerus Kuu kuude tegelikud nimed. Mis siis, kui sellel oleksid kuupäevad?
Me ei saa praegu SUMIF -i teha, sest SUMIF ei saa hakkama vajalike kriteeriumidega. SUMPRODUCT saab aga hakkama massiivi manipuleerimisega ja sügavama testi tegemisega. Oleme juba massiividega manipuleerinud, kui oleme tõe/vale tõlkinud 1/0. Me manipuleerime selle massiiviga funktsiooniga MONTH. Siin on täielik valem, mida hakkame kasutama
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "punane"), -(KUU (C2: C4) = 2)) |
Vaatame 3rd massiivi tihedamalt. Esiteks, meie valem eraldab kuu numbri igast kuupäevast C2: C4. See annab meile {1, 2, 2}. Järgmisena kontrollime, kas see väärtus on 2. Nüüd näeb meie massiiv välja selline: {False, True, True}. Teeme uuesti kahekordse miinuse ja tulemuseks on {0, 1, 1}. Oleme nüüd tagasi sarnases kohas, mis oli näites 3, ja meie valem võib meile öelda, et veebruaris müüdi 50 ühikut, mis olid punased.
Kahekordne miinus vs korrutamine
Kui olete varem näinud funktsiooni SUMPRODUCT kasutusel, võisite näha pisut teistsugust märget. Kahekordse miinuse asemel võite kirjutada
| 1 | = SUMPRODUCT (A2: A4*(B2: B4 = "Red")*(KUU (C2: C4) = 2)) |
Valem töötab endiselt samamoodi, me lihtsalt ütleme arvutile käsitsi, et tahame massiive korrutada. SUMPRODUCT tegi seda niikuinii, nii et seal ei muutu matemaatika toimimine. Matemaatika sooritamine muudab meie tõese/vale väärtuse 1/0 samaks. Niisiis, miks erinevus?
Enamasti pole sellel liiga palju tähtsust ja see sõltub kasutaja eelistustest. Siiski on vähemalt üks juhtum, kus on vaja korrutada.
SUMPRODUCTi kasutamisel eeldab arvuti, et kõik argumendid (massiiv1, massiiv2 jne) on ühesuurused. See tähendab, et neil on sama arv ridu või veerge. Siiski saate teha SUMPRODUCTiga kahemõõtmelise massiivi arvutamise, mida näeme järgmises näites. Kui te seda teete, on massiivid erineva suurusega, nii et peame kontrollist "kõik ühesuurused" mööda minema.
Kaks mõõdet
Kõigi eelnevate näidete puhul käisid meie massiivid samas suunas. SUMPRODUCT saab hakkama kahes suunas toimuvate asjadega, nagu näeme järgmises tabelis.
Siin on meie müüdud ühikute tabel, kuid andmed paigutatakse ümber, kus kategooriad üleval asuvad. Kui tahame teada, kui palju objekte oli punane ja A -kategooria, võime kirjutada
| 1 | = SUMPRODUCT ((A2: A4 = "Punane")*(B1: C1 = "A")*B2: C4) |
Mis siin toimub?? Tuleb välja, et korrutame kahes eri suunas. Selle visualiseerimist on raskem teha ainult kirjaliku lausega, nii et meil on paar pilti, mis meid aitavad. Esiteks, meie rea kriteeriumid (kas see on punane?) Korrutatakse massiivi igas reas.
| 1 | = SUMPRODUCT ((A2: A4 = "PUNANE")*B2: C4) |
Järgmisena korrutatakse veerukriteeriumid (kas see on A -kategooria?) Igas veerus
| 1 | = SUMPRODUCT ((A2: A4 = "Punane")*(B1: C1 = "A")*B2: C4) |
Pärast seda, kui mõlemad kriteeriumid on oma töö teinud, on ainsad nullideta numbrid 5 ja 10. SUMPRODUCT annab meile vastuseks kogusumma 15.
Mäletate, kuidas me rääkisime sellest, et massiivid peavad olema ühesuurused, kui te ei tee kahte mõõdet? See oli osaliselt õige. Vaatab uuesti massiive, mida kasutasime oma valemis. The kõrgus kahest meie massiivist on sama ja laius kahest meie massiivist on samad. Niisiis, peate ikkagi veenduma, et asjad lähevad õigesti ritta, kuid saate seda teha erinevates mõõtmetes.
Kaks mõõdet ja keeruline
Mitu korda esitatakse meile andmeid, mis pole meie valemite jaoks sobivaima paigutusega. Võiksime proovida seda käsitsi ümber korraldada või olla oma valemitega targemad. Vaatleme järgmist tabelit.
Siin on meie toodete ja müügi andmed iga kuu kokku segatud. Kuidas saaksime teada, kui palju objekte on Bob aasta jooksul müünud?
Selleks kasutame kahte lisafunktsiooni: SEARCH ja ISNUMBER. Funktsioon OTSI võimaldab meil otsida päise lahtritest oma märksõna „üksused”. Selle funktsiooni väljund läheb kas numbri või vea tõttu (kui märksõna ei leitud). Seejärel kasutame teisendamiseks ISNUMBER seda väljund meie Boole'i väärtustesse. Meie valem näeb välja nagu allpool.
Nüüd peaksite olema esimese massiiviga üsna tuttav. See loob väljundi nagu {0, 1, 0, 1}. Järgmine kriteeriumide massiiv, millest me just rääkisime. See loob kõigi lahtrite jaoks numbri, milles on kirje „Üksused”, ja viga teistes {5, #N/A !, 5, #N/A!}. Seejärel teisendab ISNUMBER selle loogiliseks {True, False, True, False}. Siis kui korrutame, hoiab see alles esimese ja kolmanda veeru väärtusi. Kui kõik massiivid on üksteise vastu korrutatud, on meil ainsad numbrivabad numbrid siin esile tõstetud:
| 1 | = SUMPRODUCT ((A2: A5 = "Bob")*(ISNUMBER (OTSI ("Üksused", B1: E1))*B2: E5)) |
SUMPRODUCT lisab need kõik kokku ja saame oma lõpptulemuse 29.
SUMPRODUCT Or
Tekib palju olukordi, kus tahaksime väärtusi kokku võtta, kui meie kriteeriumide veerul on üks väärtus VÕI teine väärtus. Saate seda teha rakenduses SUMPRODUCT, lisades üksteisele kaks kriteeriumimassiivi.
Selles näites tahame liita nii punase kui ka sinise jaoks müüdud ühikud.
Meie valem näeb välja selline
| 1 | = SUMPRODUCT (A2: A7, (B2: B7 = "Red")+(B2: B7 = "Blue")) |
Vaatame punaste kriteeriumide massiivi. See toodab massiivi, mis näeb välja selline: {1, 1, 0, 0, 0, 0}. Siniste kriteeriumide massiiv näeb välja selline: {0, 0, 1, 0, 1, 0}. Kui need kokku liita, näeb uus massiiv välja selline: {1, 1, 1, 0, 1, 0}. Näeme, kuidas need kaks massiivi on segunenud üheks kriteeriumimassiiviks. Funktsioon korrutab selle meie esimese massiiviga ja saame tulemuseks {100, 50, 10, 0, 75, 0}. Pange tähele, et rohelise väärtused on nullitud. SUMPRODUCTi viimane samm on kõigi numbrite liitmine, et jõuda meie lahenduseni 235.
Siinkohal üks ettevaatlik sõna. Olge ettevaatlik, kui kriteeriumide massiivid ei välista üksteist. Meie näites võivad veeru B väärtused olla kas punased või sinised, kuid me teadsime, et need ei saa kunagi olla mõlemad. Mõelge, kas me oleksime selle valemi kirjutanud:
| 1 | = SUMPRODUCT (A2: A7, (A2: A7> = 50)+(B2: B7 = "Sinine")) |
Meie eesmärk on leida siniseid esemeid, mida müüdi või mille kogus oli üle 50. Kuid need tingimused ei ole ainuõiguslikud, kuna üks rida võib olla veerus A mõlemad üle 50 ja ole sinine. Selle tulemusel näeb esimene kriteeriumimassiiv välja selline: {1, 1, 0, 1, 1, 0}, teine kriteeriumimassiiv on {0, 0, 1, 0, 1, 0}. Nende liitmisel saadi {1, 1, 1, 1, 2, 0}. Kas näete, kuidas meil on nüüd 2? Kui see jäetakse üksi, kahekordistab selle rea väärtus, muutes 75 väärtuse 150 -ks ja saame vale tulemuse. Selle parandamiseks kontrollime oma massiivi väliste kriteeriumidega, näiteks nii:
| 1 | = SUMPRODUCT (A2: A7, -((A2: A7> = 50)+(B2: B7 = "Sinine")> 0)) |
Nüüd, pärast kahe sisemise kriteeriumi massiivi liitmist, kontrollime, kas tulemus on suurem kui 0. See vabaneb kahest, mis meil varem oli, ja selle asemel on meil massiiv nagu {1, 1, 1 , 1, 1, 0}, mis annab õige tulemuse.
SUMPRODUCT Täpne
Enamik Exceli funktsioone ei ole tõstutundlikud, kuid mõnikord peame suutma teha otsinguid tõstutundlikkust silmas pidades. Kui soovitud tulemus on numbriline, saame selle saavutada, kasutades funktsiooni SUMPRODUCT täpset funktsiooni. Mõelge järgmisele tabelile:
Soovime leida punkti „ABC123” hinded. Tavaliselt võrdleb funktsioon EXACT kahte üksust ja tagastab loogilise väljundi, mis näitab, kas need kaks on täpselt sama. Kuna oleme aga SUMPRODUCTis, teab meie arvuti, et me tegeleme massiividega, ja saab võrrelda ühte üksust massiivi iga üksusega. Meie valem näeb välja selline
| 1 | = SUMPRODUCT (-TÄPSELT ("ABC123", A2: A5), B2: B5) |
Seejärel kontrollib funktsioon EXACT iga üksust jaotises A2: A5, et näha, kas see vastab väärtusele ja suurtähele. See toodab massiivi, mis näeb välja nagu {0, 1, 0, 0}. Kui korrutada B2: B5 vastu, saab massiivist {0, 2, 0, 0}. Pärast lõplikku summeerimist saame lahenduse 2.
SUMPRODUCT Google'i arvutustabelites
Funktsioon SUMPRODUCT töötab Google'i arvutustabelites täpselt samamoodi nagu Excelis:
SUMPRODUCT Näited VBA -s
VBA -s saate kasutada ka funktsiooni SUMPRODUCT. Tüüp: application.worksheetfunction.sumproduct (massiiv1, massiiv2, massiiv3)
Järgmiste VBA avalduste täitmine
| 1 | Vahemik ("B10") = Application.WorksheetFunction.SumProduct (Vahemik ("A2: A7"), vahemik ("B2: B7")) |
annab järgmised tulemused
Funktsiooniargumentide (massiiv1 jne) jaoks saate need kas otse funktsiooni sisestada või määrata selle asemel kasutatavad muutujad.
