Kuten olen maininnut monissa blogeissani, että SUMPRODUCT on erittäin monipuolinen toiminto ja sitä voidaan käyttää moniin tarkoituksiin. Tässä artikkelissa näemme, kuinka voimme käyttää tätä toimintoa laskeaksesi arvoja, joilla on useita tai ehtoja.
Yleinen SUMPRODUCT -kaava, joka lasketaan useilla tai kriteereillä
| = SUMPRODUCT (-(((ehto1)+(ehto2)+…)> 0) |
Kriteerit 1: Tämä on mikä tahansa ehto, joka palauttaa taulukon TOSI ja EPÄTOSI.
Kriteerit 2: Tämä on seuraava kriteeri, jonka haluat tarkistaa. Samoin sinulla voi olla niin monta kriteeriä kuin haluat.
Yllä olevaa yleistä kaavaa muutetaan usein vastaamaan vaatimuksia, jotka lasketaan useilla OR -kriteereillä. Mutta peruskaava on tämä. Ensin näemme kuinka tämä toimii esimerkin kautta ja sen jälkeen keskustelemme muista skenaarioista, joissa sinun on muutettava tätä kaavaa hieman.
Ongelma:
Solun hinta on jaettava tarkasti viiteen samanlaiseen maksuun. Kun yksinkertaisesti jakaa hinta 88,04 dollaria viidellä ja pyöristetään tulos kahdella desimaalilla, saamme yksittäisiä maksuja 17,61 dollaria. Tämä maksaisi kuitenkin yhteensä 88,05 dollaria, mikä ei ole tarkka summa, josta aloitamme. Siksi haluamme laskea viisi samanlaista lukua, jotka muodostavat kokonaissumman.
Ratkaisu:
Laske ensimmäinen maksu jakamalla hinta 5: llä ja pyöristämällä tulos.
Käytä ROUND -funktiota seuraavassa kaavassa:
= KERROS (B1/5,2)
Vaihe 2:
Laske jokainen jäljellä oleva maksu.
Käytä ROUND-, SUM- ja ROW -funktioita seuraavassa kaavassa:
= ROUND (($ B $ 1-SUM ($ B $ 2: B2))/(5-ROW ()+ROW ($ B $ 2)), 2)
Esimerkki:
Laske käyttäjät, jos jälleenmyyjän koodi on sama tai vuosi, käyttämällä SUMPRODUCT -ohjelmaa
Tässä on siis myyjien tietojoukko. Tiedot sisältävät monia sarakkeita. Meidän on laskettava niiden käyttäjien määrä, joilla on koodi "INKA" tai vuosi on "2016". Varmista, että jos jollakin on molemmat (koodi "inka" ja vuosi 2016), se lasketaan yhdeksi.
Joten kuule, meillä on kaksi kriteeriä. Käytämme yllä mainittua SUMPRODUCT -kaavaa:
| = SUMPRODUCT (-((((koodi = I3)+(vuosi = K3))> 0)) |
Tässä koodi ja vuosi on nimetty alueiksi. Tämä palauttaa 7.
Aineistossa on 5 tietuetta INKA -koodista ja 4 tietuetta vuodelta 2016. Mutta kahdessa tietueessa on sekä "INKA" että vuosi 2016 koodina ja vuosi. Ja nämä tietueet lasketaan 1. Ja näin saamme 7.
Katsotaan miten se toimii?
Joten katsotaanpa kuinka kaava ratkaistaan askel askeleelta, sitten keskustelen siitä, miten se toimii.
-
- = SUMPRODUCT(-(((koodi = I3)+(vuosi = 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
Kaksoismerkkejä (-) käytetään muuntamaan totuusarvot 1s ja 0s. Joten jokainen TOSI -arvo taulukossa muutetaan yhdeksi ja EPÄTOSI arvoksi 0.
Lisää tai ehtojen lisääminen laskemista varten SUMPRODUCT -toiminnolla, joten jos sinun on lisättävä enemmän tai ehtoja laskettavaksi, voit lisätä funktioon vain ehtoja käyttämällä + -merkkiä.
Jos esimerkiksi haluat lisätä toisen kriteerin yllä olevaan kaavaan niin, että se lisää niiden työntekijöiden määrän, jotka ovat myyneet yli 5 tuotetta. SUMPRODUCT -kaava näyttää yksinkertaisesti tältä:
| = SUMPRODUCT (-(((koodi = I3)+(vuosi = K3)+(myynti> 5))> 0)) |
Yksinkertainen! eikö olekin?
Oletetaan kuitenkin, että haluat saada kaksi kriteeriä koodialueelta. Oletetaan, että haluat laskea "INKB". Miten sitten teet tämän? Yksi tapa on käyttää yllä olevaa tekniikkaa, mutta se olisi toistuvaa. Oletetaan, että haluan lisätä 10 muuta kriteeriä samalta alueelta. Tällaisissa tapauksissa tämä tekniikka ei ole kovin fiksu laskettaessa SUMPRODUCT -ohjelmaa.
Oletetaan, että meillä on tällaisia tietoja.
Ehtokoodit ovat yhdellä rivillä I2: J2. Tietojen järjestely on tässä tärkeä. SUMPRODUCT -kaava 3 TAI ehtojen laskenta -asetukselle on:
| = SUMPRODUCT (-((((koodi = I2: J2)+(vuosi = I3: J3))> 0)) |
Tämä on SUMPRODUCT -kaava, joka lasketaan useilla ehdoilla, kun useita kriteerejä yhdestä alueesta kirjoitetaan riville.
Yllä oleva kaava palauttaa oikean vastauksen, joka on 10.
Jos kirjoitat minkä tahansa vuoden kohtaan J3, kaava lisää myös tämän määrän.
Tätä käytetään, kun kriteerit ovat yhdellä rivillä. Toimiiko se, kun saman sarakkeen kriteerit samalle alueelle? Ei.
Tässä esimerkissä meillä on useita koodeja laskettavaksi, mutta nämä tyyppikoodit on kirjoitettu yhteen sarakkeeseen. Kun käytämme yllä olevaa SUMPRODUCT -kaavaa, saamme virheen #N/A. Emme tutki, miten tämä virhe tulee, koska tämä tekee tästä artikkelista liian pitkän. Katsotaanpa, miten voimme saada tämän toimimaan.
Jotta tämä kaava toimisi, sinun on pakattava koodiehdot TRANSPOSE -funktioon. Tämä saa kaavan toimimaan.
| = SUMPRODUCT (-(((Koodi = TRANSPOSE (H3: H4))+(Vuosi = TRANSPOSE (I3: I4)))> 0)) |
Tämä on kaava laskemiseen useilla tai ehdoilla samalla alueella, kun kriteerit on lueteltu sarakkeessa.
Tässä on kaikki huomautukset kaavan käytöstä.
Huomautuksia:
- Kaava toimii sekä tekstille että numeroille.
- Hakuarvot voidaan antaa soluviittauksena tai suoraan käyttämällä lainausmerkkiä (") kaavassa argumentteina.
Toivottavasti ymmärsit kuinka jakaa summa yhtä suuriksi maksuiksi välttäen jako- / pyöristysvirheet Excelissä. Tutustu muihin artikkeleihin Excel -haun arvosta ja Excel 2019 -toiminnoista täältä. Jos pidit blogistamme, jaa se ystävillesi Facebookissa. Voit myös seurata meitä Twitterissä ja Facebookissa. Haluaisimme kuulla sinusta, kerro meille, kuinka voimme parantaa, täydentää tai innovoida työtämme ja parantaa sitä sinulle. Kirjoita meille sähköpostitse.
Käytä INDEX- ja MATCH -toimintoja arvon etsimiseen : INDEX & MATCH -funktio arvon etsimiseen tarpeen mukaan.
SUM -alue ja INDEX Excelissä : Käytä INDEX -toimintoa löytääksesi tarvittavien arvojen SUMMA.
INDEX -toiminnon käyttäminen Excelissä : Etsi taulukon INDEX käyttämällä INDEX -funktiota, joka on selitetty esimerkillä.
MATCH -toiminnon käyttäminen Excelissä : Etsi MATCH taulukosta käyttämällä INDEX -arvoa MATCH -funktion sisällä esimerkin avulla.
LOOKUP -toiminnon käyttäminen Excelissä : Etsi hakuarvo taulukosta käyttämällä LOOKUP -funktiota, joka on selitetty esimerkillä.
Suosittuja artikkeleita:
IF -toiminnon käyttäminen Excelissä : Excelin IF -käsky tarkistaa ehdon ja palauttaa tietyn arvon, jos ehto on TOSI, tai palauttaa toisen arvon, jos EPÄTOSI.
VLOOKUP -toiminnon käyttäminen Excelissä : Tämä on yksi eniten käytetyistä ja suosituimmista Excel -toiminnoista, jota käytetään arvon etsimiseen eri alueilta ja taulukoilta.
SUMIF -toiminnon käyttäminen Excelissä : Tämä on toinen kojelaudan olennainen toiminto. Tämä auttaa sinua laskemaan yhteen arvot tietyissä olosuhteissa.
COUNTIF -toiminnon käyttäminen Excelissä : Laske arvot olosuhteilla tämän hämmästyttävän toiminnon avulla. Sinun ei tarvitse suodattaa tietoja laskeaksesi tiettyjä arvoja. Laskutoiminto on välttämätön kojelaudan valmistelemiseksi.