Lataa esimerkkityökirja
Tämä opetusohjelma osoittaa, miten Excel SUMPRODUCT -toiminto Excelissä.
SUMPRODUCT -toiminnon yleiskatsaus
SUMPRODUCT -funktio kertoo numeroitaulukon ja summaa tuloksena olevan taulukon.
Jos haluat käyttää SUMPRODUCT Excel -laskentataulukkotoimintoa, valitse solu ja kirjoita:
(Huomaa, miten kaavasyötteet näkyvät)
SUMPRODUCT -toiminto Syntaksi ja tulot:
| 1 | = SUMPRODUCT (array1, array2, array3) |
taulukko 1 - Taulukot numeroita.
Mikä on SUMPRODUCT -toiminto?
SUMPRODUCT -toiminto on yksi tehokkaimmista Excelin toiminnoista. Sen nimi saattaa saada sinut uskomaan, että se on tarkoitettu vain matematiikan peruslaskelmiin, mutta sitä voidaan käyttää paljon enemmän.
Taulukot
SUMPRODUCT vaatii matriisituloja.
Joten mitä me ensin tarkoitamme "matriisilla"? Taulukko on yksinkertainen joukko kohteita (esim. Numeroita), jotka on järjestetty tiettyyn järjestykseen, aivan kuten solualue. Joten jos sinulla olisi numerot 1, 2, 3 soluissa A1: A3, Excel lukisi tämän taulukkona {1,2,3}. Itse asiassa voit kirjoittaa {1,2,3} suoraan Excel -kaavoihin ja se tunnistaa taulukon.
Puhumme lisää alla olevista matriiseista, mutta katsotaan ensin yksinkertainen esimerkki.
Perusmatematiikka
Katsotaanpa perusesimerkki SUMPRODUCTista sen avulla kokonaismyynnin laskemiseen.
Meillä on tuotetaulukko, ja haluamme laskea kokonaismyynnin. Sinulla on houkutus lisätä vain uusi sarake, ottaa myyty määrä * hinta ja tehdä yhteenveto uudesta sarakkeesta. Sen sijaan voit kuitenkin käyttää SUMPRODUCT -toimintoa. Käydään läpi kaava:
| 1 | = SUMPRODUCT (A2: A4, B2: B4) |
Funktio lataa numeroalueet matriiseiksi, monistaa ne toisiaan vastaan ja summaa sitten tulokset:
| 1234 | = SUMPRODUCT ({100, 50, 10}, {6, 7, 5})= SUMPRODUCT ({100 * 6, 50 * 7, 10 * 5})= SUMPRODUCT ({600, 350, 50})= 1000 |
SUMPRODUCT Funciton pystyi kertomaan kaikki numerot puolestamme ja tekemään yhteenvedon.
Painotettu keskiarvo
Toinen tapaus, jossa SUMPRODUCTin käyttäminen on hyödyllistä, on silloin, kun sinun on laskettava painotettu keskiarvo. Tämä tapahtuu useimmiten koulutehtäviä käsiteltäessä, joten tarkastelemme seuraavaa taulukkoa.
Voimme nähdä, kuinka paljon tietokilpailut, testit ja kotitehtävät ovat arvokkaita kohti yleistä arvosanaa, sekä kuinka paljon nykyinen keskiarvo on kullekin kohteelle. Voimme laskea kokonaisarvosanan kirjoittamalla
| 1 | = SUMPRODUCT (B2: B4, C2: C4) |
Toimintomme kertoo jälleen jokaisen taulukon kohteen ennen kokonaissumman laskemista. Tämä onnistuu näin
| 123 | = SUMPRODUCT ({30%, 50%, 20%}, {73%, 90%, 95%})= SUMPRODUCT ({22%, 45%, 19%})= 86% |
Useita sarakkeita
Toinen paikka, jossa voimme käyttää SUMPRODUCTia, on vielä enemmän sarakkeita, jotka kaikki on kerrottava toisiaan vastaan. Katsotaanpa esimerkkiä, jossa meidän on laskettava tilavuus sahatavarana.
Sen sijaan, että luodaan apusarake, joka laskee kunkin rivin kokonaismyynnin, voimme tehdä tämän yhdellä kaavalla. Meidän kaava tulee olemaan
| 1 | = SUMPRODUCT (B2: B5, C2: C5, D2: D5) |
Jokaisen taulukon ensimmäiset kohteet kerrotaan toisiaan vastaan (esim. 4 * 2 * 1 = 8). Sitten toinen (4 * 2 * 2 = 16) ja 3rd, jne. Kaiken kaikkiaan tämä tuottaa tuotevalikoiman, joka näyttää {8, 16, 16, 32). Sitten kokonaistilavuus olisi kyseisen taulukon summa, 72.
Yksi kriteeri
Okei, lisätään vielä yksi monimutkaisuuskerros. Olemme nähneet, että SUMPRODUCT pystyy käsittelemään numeroita, mutta entä jos haluamme tarkistaa kriteerit? Voit myös luoda matriiseja Boolen arvoille (Boolen arvot ovat arvoja, jotka ovat TOSI tai EPÄTOSI).
Ota esimerkiksi perusmatriisi {1, 2, 3}. Luo vastaava taulukko, joka osoittaa, onko jokainen numero suurempi kuin 1. Tämä taulukko näyttäisi tältä {FALSE, TRUE, TRUE}.
Tämä on erittäin hyödyllistä kaavoissa, koska voimme helposti muuttaa TOSI / EPÄTOSI 1 / 0. Katsotaanpa esimerkkiä.
Alla olevan taulukon avulla haluamme laskea "Kuinka monta yksikköä oli punaista?"
Voimme tehdä sen tällä kaavalla:
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "Punainen")) |
"Pidä kiinni! Mitä siinä on kaksois miinus -symboli? " sinä sanot. Muistatko, kuinka sanoin, että voisimme muuttaa True/False -muodosta 1/0? Teemme tämän pakottamalla tietokoneen tekemään matemaattisen operaation. Tässä tapauksessa sanomme "ota negatiivinen arvo ja ota sitten negatiivinen uudelleen". Kirjoittaessamme sen valikoimamme muuttuu näin:
| 123 | {Totta, totta, väärää}{-1, -1, 0}{1, 1, 0} |
Joten takaisin koko SUMPRODUCT -kaavaan, se latautuu matriiseihimme ja lisääntyy sitten näin
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0})= SUMPRODUCT ({100, 50, 0})= 150 |
Huomaa, kuinka 3rd kohdasta tuli 0, koska kaikki kerrottuna 0: lla muuttuu nollaksi.
Useita kriteerejä
Voimme ladata jopa 255 matriisia toimintoomme, joten voimme varmasti ladata lisää ehtoja. Katsotaanpa tätä suurempaa taulukkoa, johon olemme lisänneet myydyn kuukauden.
Jos haluamme tietää, kuinka monta myytyä tuotetta oli punaista ja olimme helmikuussa, voisimme kirjoittaa kaavamme kuten
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "Red"), -(C2: C4 = "Feb")) |
Tietokone arvioi sitten matriisejamme ja moninkertaistuu. Olemme jo käsitelleet, kuinka True/False -taulukot muutetaan 1/0: ksi, joten ohitan tämän vaiheen nyt.
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0}, {0, 1, 1})= SUMPRODUCT ({0, 50, 0})= 50 |
Esimerkissämme oli vain yksi rivi, joka vastasi kaikkia ehtoja, mutta todellisilla tiedoilla sinulla olisi voinut olla useita rivejä, jotka tarvitsisit liittää yhteen.
Monimutkaiset kriteerit
Okei, tähän asti et ehkä ole vaikuttunut, koska kaikki esimerkimme olisi voitu tehdä käyttämällä muita toimintoja, kuten SUMIF tai COUNTIF. Nyt aiomme tehdä jotain noita muita toimintoja ei voi tehdä. Aiemmin kuukausisarakkeessa oli kuukausien todelliset nimet. Entä jos sillä olisi päivämäärät?
Emme voi tehdä SUMIFia nyt, koska SUMIF ei pysty käsittelemään tarvitsemiamme kriteerejä. SUMPRODUCT pystyy kuitenkin käsittelemään taulukkoa ja tekemään syvemmän testin. Olemme jo manipuloineet matriiseja, kun olemme kääntäneet tosi/epätosi 1/0: ksi. Käsittelemme tätä matriisia MONTH -funktiolla. Tässä on koko kaava, jota aiomme käyttää
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "Punainen"), -(KUUKAUSI (C2: C4) = 2)) |
Katsotaanpa 3rd järjestää tarkemmin. Ensinnäkin kaavamme poimii kuukauden numeron jokaisesta päivämäärästä C2: C4: ssä. Tämä antaa meille {1, 2, 2}. Seuraavaksi tarkistamme, onko arvo sama kuin 2. Nyt taulukko näyttää {False, True, True}. Teemme kaksinkertaisen miinuksen jälleen, ja meillä on {0, 1, 1}. Olemme nyt samassa paikassa kuin esimerkissä 3, ja kaavamme voi kertoa meille, että helmikuussa myytiin 50 yksikköä, jotka olivat punaisia.
Kaksinkertainen miinus vs. kertominen
Jos olet nähnyt SUMPRODUCT -toiminnon käytössä aiemmin, olet ehkä nähnyt hieman erilaisen merkintätavan. Kaksinkertaisen miinuksen sijaan voit kirjoittaa
| 1 | = SUMPRODUCT (A2: A4*(B2: B4 = "Punainen")*(KUUKAUSI (C2: C4) = 2)) |
Kaava toimii edelleen samalla tavalla, kerromme vain manuaalisesti tietokoneelle, että haluamme kertoa matriiseja. SUMPRODUCT aikoi tehdä tämän joka tapauksessa, joten matematiikan toiminta ei muutu. Matemaattisen operaation suorittaminen muuttaa True/False -arvon 1/0: ksi. Joten miksi ero?
Useimmiten sillä ei ole liikaa väliä, ja se riippuu käyttäjien mieltymyksistä. On kuitenkin ainakin yksi tapaus, jossa tarvitaan kertomista.
Kun käytät SUMPRODUCTia, tietokone odottaa kaikkien argumenttien (taulukko1, taulukko2 jne.) Olevan samankokoisia. Tämä tarkoittaa, että niillä on sama määrä rivejä tai sarakkeita. Voit kuitenkin tehdä niin sanotun kaksiulotteisen matriisilaskennan SUMPRODUCTilla, jonka näemme seuraavassa esimerkissä. Kun teet niin, taulukot ovat erikokoisia, joten meidän on ohitettava tämä "kaikki samankokoinen" -valinta.
Kaksi ulottuvuutta
Kaikissa aiemmissa esimerkeissä matriisimme menivät samaan suuntaan. SUMPRODUCT pystyy käsittelemään asioita kahteen suuntaan, kuten näemme seuraavassa taulukossa.
Tässä on myytyjen yksiköiden taulukko, mutta tiedot järjestetään uudelleen, missä luokat menevät ylhäältä. Jos haluamme selvittää, kuinka monta tuotetta oli punaisia ja luokassa A, voimme kirjoittaa
| 1 | = SUMPRODUCT ((A2: A4 = "Punainen")*(B1: C1 = "A")*B2: C4) |
Mitä täällä tapahtuu?? On käynyt ilmi, että tulemme lisääntymään kahteen eri suuntaan. Tämän visualisoiminen on vaikeampaa vain kirjoitetulla lauseella, joten meillä on muutama kuva auttamaan meitä. Ensinnäkin rivikriteerimme (onko se punainen?) Moninkertaistuvat jokaisen taulukon rivin poikki.
| 1 | = SUMPRODUCT ((A2: A4 = "PUNAINEN")*B2: C4) |
Seuraavaksi sarakkeen kriteerit (onko se luokka A?) Kerrotaan alaspäin jokaisessa sarakkeessa
| 1 | = SUMPRODUCT ((A2: A4 = "Punainen")*(B1: C1 = "A")*B2: C4) |
Kun molemmat kriteerit ovat tehneet tehtävänsä, ainoat nollat, jotka eivät ole jäljellä, ovat 5 ja 10. SUMPRODUCT antaa meille vastaukseksi yhteensä 15.
Muistatko, kuinka puhuimme siitä, että matriisien on oltava samankokoisia, ellet tee kahta ulottuvuutta? Se oli osittain oikein. Näyttää jälleen taulukot, joita käytimme kaavassamme. The korkeus kahdesta ryhmästämme on sama, ja leveys kahdesta ryhmästämme on sama. Joten sinun on vielä varmistettava, että asiat järjestyvät oikein, mutta voit tehdä sen eri ulottuvuuksilla.
Kaksi ulottuvuutta ja monimutkainen
Monta kertaa meille esitetään tietoja, jotka eivät ole parhaassa kaavoillemme sopivassa asettelussa. Voisimme yrittää järjestää sen manuaalisesti tai olla fiksumpia kaavojemme avulla. Tarkastellaan seuraavaa taulukkoa.
Tässä meillä on tietoja tuotteistamme ja myynnistämme joka kuukausi. Miten saisimme tietää, kuinka monta tavaraa Bob on myynyt koko vuoden?
Käytämme tätä varten kahta lisätoimintoa: SEARCH ja ISNUMBER. HAKU -toiminnon avulla voimme etsiä avainsanamme "kohteet" otsikkosoluista. Tämän toiminnon tulos tulee joko numerolla tai virheellä (jos avainsanaa ei löydy). Käytämme sitten ISNUMBER -muunnosta että tulos Boolen arvoihin. Kaavamme näyttää tältä.
Sinun pitäisi olla jo hyvin perehtynyt ensimmäiseen taulukkoon. Se luo tuloksen, kuten {0, 1, 0, 1}. Seuraava kriteeriryhmä, josta juuri puhuimme. Se luo numeron kaikille soluille, joissa on "Items", ja virhe muille {5, #N/A !, 5, #N/A!}. ISNUMBER muuntaa tämän sitten Boolen arvoksi {True, False, True, False}. Sitten kun kerromme, se säilyttää vain ensimmäisen ja kolmannen sarakkeen arvot. Kun kaikki taulukot kertovat toisiaan vastaan, meillä on ainoat nollasta poikkeavat numerot, jotka on korostettu tässä:
| 1 | = SUMPRODUCT ((A2: A5 = "Bob")*(ISNUMBER (HAKU ("Kohteet", B1: E1))*B2: E5)) |
SUMPRODUCT laskee nämä kaikki yhteen ja saamme lopputuloksemme 29.
SUMPRODUCT Or
On monia tilanteita, joissa haluaisimme pystyä laskemaan yhteen arvot, jos kriteerisarakkeessa on yksi arvo TAI toinen arvo. Voit tehdä tämän SUMPRODUCTissa lisäämällä kaksi kriteeritaulukkoa toisiaan vastaan.
Tässä esimerkissä haluamme lisätä sekä punaiselle että siniselle myydyt yksiköt.
Kaavamme näyttää tältä
| 1 | = SUMPRODUCT (A2: A7, (B2: B7 = "Punainen")+(B2: B7 = "Sininen")) |
Katsotaanpa punaista kriteeristöä. Se tuottaa taulukon, joka näyttää tältä: {1, 1, 0, 0, 0, 0}. Sininen kriteeritaulukko näyttää tältä: {0, 0, 1, 0, 1, 0}. Kun lisäät ne yhteen, uusi taulukko näyttää tältä: {1, 1, 1, 0, 1, 0}. Voimme nähdä, kuinka nämä kaksi taulukkoa ovat sulautuneet yhteen kriteeritaulukkoon. Funktio kertoo sen sitten ensimmäisellä matriisillamme, ja saamme {100, 50, 10, 0, 75, 0}. Huomaa, että vihreän arvot on nollattu. SUMPRODUCTin viimeinen vaihe on yhdistää kaikki numerot yhteen ja saavuttaa 235 -ratkaisumme.
Tässä yksi varoituksen sana. Ole varovainen, kun kriteeritaulukot eivät sulje toisiaan pois. Esimerkissämme sarakkeen B arvot voivat olla joko punaisia tai sinisiä, mutta tiesimme, että ne eivät voi koskaan olla molemmat. Mieti, olisimmeko kirjoittaneet tämän kaavan:
| 1 | = SUMPRODUCT (A2: A7, (A2: A7> = 50)+(B2: B7 = "sininen")) |
Tarkoituksenamme on löytää sinisiä tuotteita, joita myytiin tai joiden määrä oli yli 50. Nämä ehdot eivät kuitenkaan ole yksinomaisia, koska yksi rivi voi olla molemmat yli 50 sarakkeessa A ja olla Sininen. Tämä johtaisi siihen, että ensimmäinen kriteeritaulukko näyttää tältä: {1, 1, 0, 1, 1, 0}, ja toinen kriteeritaulukko on {0, 0, 1, 0, 1, 0}. Niiden yhdistäminen tuotti {1, 1, 1, 1, 2, 0}. Näetkö kuinka meillä on nyt 2? Jos jätetään yksin, SUMPRODUCT kaksinkertaistaa rivin arvon ja muuttaa 75: n 150: ksi, ja saamme väärän tuloksen. Korjataksemme tämän, teemme joukkoomme ulkoisen kriteerin tarkistuksen, kuten:
| 1 | = SUMPRODUCT (A2: A7, -((A2: A7> = 50)+(B2: B7 = "sininen")> 0)) |
Nyt kun kaksi sisäistä kriteeritaulukkoa on laskettu yhteen, tarkistamme, onko tulos suurempi kuin 0. Tämä eroaa kahdesta aiemmasta, ja sen sijaan meillä on taulukko, kuten {1, 1, 1 , 1, 1, 0}, joka tuottaa oikean tuloksen.
SUMPRODUCT Tarkka
Useimmat Excelin toiminnot eivät erota kirjainkokoa, mutta joskus meidän on pystyttävä tekemään haku kirjainkoko huomioon ottaen. Kun haluttu tulos on numeerinen, voimme saavuttaa tämän käyttämällä TARKKAA SUMPRODUCT -toiminnon sisällä. Harkitse seuraavaa taulukkoa:
Haluamme löytää pisteet kohteelle “ABC123”. Normaalisti EXACT -toiminto vertaa kahta kohdetta ja palauttaa Boolen tuloksen ja ilmoittaa, ovatko nämä kaksi kohdetta tarkalleen sama. Koska olemme kuitenkin SUMPRODUCT -tuotteessa, tietokoneemme tietää, että käsittelemme matriiseja, ja pystyy vertaamaan yhtä kohdetta kunkin taulukon kohteen kanssa. Kaavamme näyttää tältä
| 1 | = SUMPRODUCT (-EXACT ("ABC123", A2: A5), B2: B5) |
EXACT -toiminto tarkistaa jokaisen kohdan A2: A5 kohteen nähdäkseen, vastaako se arvoa ja kirjainta. Tämä tuottaa taulukon, joka näyttää tältä: {0, 1, 0, 0}. Kun kerrotaan B2: B5: ää vastaan, taulukosta tulee {0, 2, 0, 0}. Lopullisen summauksen jälkeen saamme ratkaisumme 2.
SUMPRODUCT Google Sheetsissä
SUMPRODUCT -toiminto toimii täsmälleen samalla tavalla Google Sheetsissä kuin Excelissä:
SUMPRODUCT Esimerkkejä VBA: sta
Voit myös käyttää SUMPRODUCT -toimintoa VBA: ssa. Tyyppi: application.worksheetfunction.sumproduct (array1, array2, array3)
Seuraavien VBA -lausekkeiden suorittaminen
| 1 | Alue ("B10") = Application.WorksheetFunction.SumProduct (Alue ("A2: A7"), alue ("B2: B7")) |
tuottaa seuraavat tulokset
Funktioargumenteille (taulukko1 jne.) Voit joko syöttää ne suoraan funktioon tai määrittää sen sijaan käytettävät muuttujat.
