Tässä opetusohjelmassa selitetään SUMIF- ja SUMIFS-toimintojen ero niiden syntaksin ja käyttö, ja tarjoaa useita kaavaesimerkkejä useiden AND / OR-ehtojen summaamiseksi Excel 2019, 2016, 2013, 2010, 2007, 2003 ja sitä vanhemmissa.
Kuten kaikki tietävät, Microsoft Excel tarjoaa matriisin funktioita erilaisten laskelmien suorittamiseksi datalla. Muutama artikkeli sitten tutkimme COUNTIF ja COUNTIFS, jotka on suunniteltu solujen laskemiseen yhden ehdon ja useiden ehtojen perusteella. Viime viikolla käsittelimme Excel SUMIF -ohjelmaa, joka lisää määritettyjen kriteerien mukaiset arvot. Nyt on aika käydä läpi monikko SUMIF – Excel SUMIFS -versiosta, joka sallii arvojen yhteenlaskemisen useilla ehdoilla.
Ne, jotka tuntevat SUMIF-toiminnon, saattavat ajatella, että sen muuntaminen SUMIFSiksi vie vain ylimääräisen ”S” ja muutama lisäkriteeri. Tämä näyttäisi melko loogiselta … mutta ”looginen” se ei aina ole Microsoftin kanssa:)
- SUMIF-funktio – syntakse & käyttö
- SUMIFS-funktio – syntaksin & käyttö
- SUMIFS-järjestelmän käyttö Excelissä (JA logiikka)
- Excel SUMIF, jossa on useita ehtoja (TAI logiikka)
- Excel SUMIFS, jossa on useita TAI-ehtoja
- Excel SUM: n käyttö matriisikaavoissa
Excel SUMIF -funktio – syntaksin & käyttö
SUMIF-funktiota käytetään ehtojen yhteenlaskemiseen yhden ehdon perusteella. Keskustelimme SUMIF-syntaksista yksityiskohtaisesti edellisessä artikkelissa, joten haluan antaa sinulle vain lyhyen yhteenvedon nyt.
-
range– ehdoitasi arvioitava solualue. -
criteria– ehdon, jonka on täytyttävä, vaaditaan. -
sum_range– solut, jotka summataan, jos ehto täyttyy, valinnainen.
Kuten näette, Excel SUMIF -funktion syntakse sallii vain yhden ehdon. Ja silti sanomme, että Excel SUMIFia voidaan käyttää summaamaan arvoja useilla ehdoilla. Kuinka se voi olla? Lisäämällä useiden SUMIF-funktioiden tulokset ja käyttämällä SUMIF-kaavoja taulukkoehdoilla, kuten seuraavissa esimerkeissä osoitetaan.
Excel SUMIFS -funktio – syntaksin & käyttö
Voit käyttää Excelissä SUMIFS-arvoa ehdollisen arvosumman löytämiseen useiden ehtojen perusteella. SUMIFS-toiminto otettiin käyttöön Excel 2007: ssä, joten voit käyttää sitä kaikissa Excel 2019: n, 2016, 2013, 2010 ja 2007 moderneissa versioissa.
SUMIFS-syntaksi on hieman monimutkaisempi kuin SUMIF :
Kolme ensimmäistä argumenttia ovat pakollisia, lisäalueet ja niihin liittyvät ehdot ovat valinnaisia.
-
sum_range– yhteen tai useampia soluja vaaditaan. Tämä voi olla yksi solu, solualue tai nimetty alue. Vain solut, joissa on numeroita, lasketaan yhteen; tyhjä ja teksti-arvot ohitetaan. -
criteria_range1– vaaditaan ensimmäinen alue, joka arvioidaan liittyvien ehtojen avulla. -
criteria1– ensimmäinen edellytys, joka on täytettävä, vaaditaan. Voit toimittaa ehdot numeron, loogisen lausekkeen, soluviitteen, tekstin tai muun Excel-funktion muodossa. Voit esimerkiksi käyttää ehtoja, kuten 10, ”> = 10”, A1, ”kirsikat” tai TÄNÄÄN (). -
criteria_range2, criteria2, …– nämä ovat niihin liittyviä lisäalueita ja ehtoja, valinnaisia. Voit käyttää SUMIFS-kaavoissa enintään 127 alue- / kriteeriparia.
Ja nyt, anna ” Tarkastellaan Excel SUMIFS -kaavaa kahdella ehdolla. Oletetaan, että sinulla on taulukko, jossa luetellaan hedelmien lähetykset eri toimittajilta. Sinulla on hedelmien nimet sarakkeessa A, toimittajien nimet sarakkeessa B ja määrä sarakkeessa C. Haluat selvittää summan summasta, joka liittyy tiettyyn hedelmään ja toimittajaan, esim kaikki Peten toimittamat omenat.
Kun opit jotain uutta, on aina hyvä aloittaa yksinkertaisista asioista. Aluksi määritellään sitten kaikki argumentit SUMIFS-kaavalle:
- summa-alue – C2: C9
- kriteerialue1 – A2: A9
- kriteerit1 – ”omenat”
- kriteerialue2 – B2: B9
- kriteerit2 – ”Pete”
Kokoa nyt yllä olevat parametrit ja sinä saa seuraavan SUMIFS-kaavan:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
Tarkentaminen kaavan lisäksi voit korvata tekstikriteerit ”omenat” ja ”Pete” soluviittauksilla.Tässä tapauksessa sinun ei tarvitse muuttaa kaavaa laskeaksesi muiden toimittajien muiden hedelmien määrän:
=SUMIFS(C2:C9, A2:A9, F1, B2:B9, F2)
SUMIFS: n ja SUMIF: n käyttö Excelissä – muistettavia asioita
Koska tämän opetusohjelman tarkoituksena on kattaa kaikki mahdolliset tapoja summata arvoja useilla ehdoilla, keskustelemme kaavan esimerkeistä molempien toimintojen kanssa – Excel SUMIFS ja SUMIF useilla ehdoilla. Jotta voit käyttää niitä oikein, sinun on ymmärrettävä selvästi, mitä näillä kahdella toiminnolla on yhteistä ja millä tavalla ne ovat erilaisia.
Vaikka yhteinen osa on selkeä – samanlainen kohde ja parametrit – erot eivät ole niin ilmeisiä, mutta erittäin välttämättömiä.
1. Argumenttien järjestys
Excel SUMIF- ja SUMIFS-funktioissa argumenttien järjestys on erilainen. Erityisesti sum_range on ensimmäinen parametri SUMIFS: ssä, mutta se on SUMIF: ssä 3.
Ensi silmäyksellä voi tuntua siltä, että Microsoft vaikeuttaa tarkoituksellisesti käyttäjien oppimiskäyrää. Tarkemmin tarkastellessasi näet sen perustelut. Asia on, että sum_range on valinnainen SUMIFissa. Jos jätät sen väliin, ei ongelmaa, SUMIF-kaava summaa alueen arvot (ensimmäinen parametri).
SUMIFS-sovelluksessa sum_range on erittäin tärkeä ja pakollinen, ja siksi se on ensin. Todennäköisesti Microsoftin kaverit ajattelivat, että lisäämällä 10. tai 100. väli / kriteeripari joku saattaa unohtaa määrittää summan:)
2. Summa-alueen ja kriteerialueen tulee olla yhtä suuret.
SUMIF-funktiossa sum_range-argumentin ei tarvitse välttämättä olla samankokoinen kuin range-argumentti, kunhan sinulla on vasen yläkulma oikea solu. Excel SUMIFS -ohjelmassa jokaisen ehtoalueen on sisällettävä sama määrä rivejä ja sarakkeita kuin summa-alue-parametrilla.
Esimerkiksi =SUMIF(A2:A9,F1,C2:C18) palauttaa oikean tuloksen, koska Excel katsoo vain sum_range-argumentin vasemman yläkulman solu (tässä esimerkissä C2, mikä on oikein) ja sisältää sitten niin monta saraketta ja riviä kuin alue-argumentissa.
Okei, tarpeeksi strategiaa (eli teoriaa) , perehdytään taktiikoihin (ts. kaavan esimerkit:)
Kuinka käyttää SUMIFSia Excelissä – kaavan esimerkkejä
Hetki sitten keskustelimme yksinkertaisesta SUMIFS-kaavasta, jossa oli kaksi tekstiä Samalla tavalla voit käyttää Excel SUMIFS -ohjelmaa useilla ehdoilla, jotka ilmaistaan numeroilla, päivämäärillä, loogisilla lausekkeilla ja muilla Excel-funktioilla.
Esimerkki 1. Excel SUMIFS vertailuoperaattoreilla
Oletetaan, että haluat hedelmätoimittajien taulukossa laskea yhteen kaikki Miken toimitukset, joiden määrä on 200 tai enemmän. Tätä varten käytetään vertailuoperaattoria ”suurempi kuin o r on yhtä suuri kuin ”(> =) kriteereissä2 ja saat seuraavan SUMIFS-kaavan:
=SUMIFS(C2:C9,B2:B9,"Mike",C2:C9,">=200")
Käsittelimme kaikki mahdolliset vertailuoperaattorit yksityiskohtaisesti keskustellessamme Excel SUMIF -funktiosta, samat operaattorit voidaan käyttää SUMIFS-kriteereissä. Esimerkiksi seuraava kaava palauttaa solujen C2: C9 kaikkien arvojen summan, joka on suurempi tai yhtä suuri kuin 200 ja pienempi tai yhtä suuri kuin 300.
=SUMIFS(C2:C9, C2:C9,">=200", C2:C9,"<=300")
Esimerkki 2. Excel SUMIFS -toiminnon käyttö päivämäärillä
Jos haluat summata arvot useilla ehdoilla nykyisen päivämäärän perusteella, käytä SUMIFS-funktiota TODAY (). kriteerit, kuten alla osoitetaan. Seuraava kaava summaa sarakkeen D arvot, jos vastaava päivämäärä sarakkeessa C on viimeisten seitsemän päivän aikana, myös tänään:
=SUMIFS(D2:D10, C2:C10,">="&TODAY()-7, C2:C10,"<="&TODAY())
Vastaavalla tavalla voit käyttää Excel SUMIF -funktiota summaamaan määrätyn ajanjakson arvot. Esimerkiksi seuraava SUMIFS-kaava lisää arvot soluihin C2: C9, jos sarakkeessa B oleva päivämäärä on 1.10.2014-31.10.2014.
=SUMIFS(C2:C9, B2:B9, ">=10/1/2014", B2:B9, "<=10/31/2014")
Sama tulos voidaan saavuttaa laskemalla kahden SUMIF-funktion ero, kuten tässä esimerkissä osoitetaan – Kuinka käyttää SUMIF-arvoa tietyn ajanjakson arvojen yhteenlaskemiseen. Excel SUMIFS on kuitenkin paljon helpompi ja ymmärrettävämpi, eikö olekin?
Esimerkki 3. Excel SUMIFS tyhjillä ja tyhjillä soluilla
Kun analysoit raportteja ja muita tietoja, saattaa joutua usein laskemaan yhteen arvot, jotka vastaavat joko tyhjiä tai ei-tyhjiä soluja.
Katsotaan nyt, kuinka voit käyttää SUMIFS-kaavaa, jossa on ”tyhjä” ja ”ei tyhjä” -ehdot todellisessa tiedot.
Oletetaan, että sinulla on tilauspäivä sarakkeessa B, toimituspäivä sarakkeessa C ja määrä. sarakkeesta D. Kuinka löydät kaikki tuotteet, joita ei ole vielä toimitettu? Toisin sanoen haluat tietää arvojen summan, joka vastaa ei-tyhjiä soluja sarakkeessa B ja tyhjiä soluja sarakkeessa C.
Ratkaisu on käyttää SUMIFS-kaavaa kahdella ehdolla:
=SUMIFS(D2:D10, B2:B10,"<>", C2:C10,"=")
Excel SUMIF: in käyttäminen useilla TAI-ehdoilla
Kuten tämän opetusohjelman alussa todettiin, SUMIFS-toiminto on suunniteltu JA-logiikalla. Mutta entä jos sinun on laskettava yhteen arvot, joissa on useita TAI-ehtoja, ts. Kun ainakin yksi ehdoista täyttyy?
Esimerkki 1. SUMIF + SUMIF
Yksinkertaisin ratkaisu on summa useiden SUMIF-toimintojen palauttamat tulokset. Esimerkiksi seuraava kaava osoittaa, kuinka löytää Mike ja John toimittamien tuotteiden kokonaismäärä:
=SUMIF(C2:C9,"Mike",D2:D9) + SUMIF(C2:C9,"John",D2:D9)
Kuten näette, ensimmäinen SUMIF-funktio lisää ”Mike” -arvot, toinen SUMIF-funktio palauttaa ”John” -arvot ja lisäät nämä kaksi numeroa.
Esimerkki 2. SUM & SUMIF taulukko-argumentilla
Yllä oleva ratkaisu on hyvin yksinkertainen ja saattaa saada työn valmiiksi nopeasti, kun vain pari kriteeriä. Mutta SUMIF + SUMIF -kaava voi kasvaa valtavasti, jos haluat summata arvot useilla TAI-ehdoilla. Tässä tapauksessa parempi lähestymistapa on käyttää matriisikriteeri-argumenttia SUMIF-funktiossa. Tarkastellaan nyt tätä lähestymistapaa.
Voit aloittaa luetteloimalla kaikki ehdot pilkuilla eroteltuina ja liittämällä sitten tuloksena olevan pilkulla erotetun luettelon {curly brackets}, jota kutsutaan teknisesti matriisiksi.
3 arvosta koostuva taulukko argumentti pakottaa SUMIF-kaavan palauttamaan kolme erillistä tulosta, mutta koska kaava kirjoitetaan yhteen soluun, se palauttaisi vain ensimmäisen tuloksen – eli Johnin toimittamien tuotteiden kokonaismäärän . Saadaksesi tämän matriisikriteerityypin toimimaan, sinun on käytettävä vielä yhtä temppua – liitä SUMIF-kaava SUM-funktioon seuraavasti:
=SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9))
Kuten näette, matriisikriteerit tekevät kaavasta paljon pienikokoisemman kuin SUMIF + SUMIF, ja voit lisätä niin monta arvoa kuin haluat matriisista.
Tämä lähestymistapa toimii sekä numeroiden että tekstiarvojen kanssa. Jos sinulla esimerkiksi oli sarakkeessa C toimittajien nimien sijasta toimittajien tunnuksia, kuten 1, 2, 3 jne. ., silloin SUMIF-kaava näyttää samanlaiselta kuin tämä:
=SUM(SUMIF(C2:C9, {1,2,3} , D2:D9))
Toisin kuin tekstiarvot, numeroita ei tarvitse liittää lainausmerkkeihin taulukon argumentit.
Esimerkki 3. SUMPRODUCT & SUMIF
Siinä tapauksessa sinun on suositeltava tapa luetella ehdot joissakin soluissa pikemminkin määritä ne suoraan kaavassa, voit käyttää SUMIF-funktiota yhdessä SUMPRODUCT-funktion kanssa, joka kertoo komponentit annetuissa matriiseissa ja palauttaa näiden tuotteiden summan. div>
Missä G2: G4 ovat solut, jotka sisältävät ehtosi, toimittajiemme nimet tapauksessamme, kuten alla olevasta kuvakaappauksesta käy ilmi.
Mikään ei tietenkään kuitenkaan estä sinua luetellaan arvot SUMIF-funktion matriisikriteereissä, jos haluat:
=SUMPRODUCT(SUMIF(C2:C9, {"Mike","John","Pete"}, D2:D9))
Molempien kaavojen palauttama tulos on sama kuin kuvakaappauksessa:
Excel SUMIFS kanssa useita TAI-ehtoja
Jos haluat ehdollisesti summata arvot Excelissä ei pelkästään useilla TAI-ehdoilla, vaan useilla ehtojoukoilla, joudut käyttämään SUMIFS: ää SUMIF: n sijaan. Kaavat ovat hyvin samankaltaisia kuin olemme juuri keskustelleet.
Kuten tavallista, esimerkki voi auttaa havainnollistamaan asiaa paremmin. Lisää hedelmätoimittajien taulukkoon toimituspäivä (sarake E) ja etsi Mike, John ja Pete lokakuussa toimittama kokonaismäärä.
Esimerkki 1. SUMIFS + SUMIFS
Tämän lähestymistavan tuottama kaava sisältää paljon toistoja ja ulkonäköä hankala, mutta se on helppo ymmärtää ja mikä tärkeintä, se toimii:)
Esimerkki 2. SUM & SUMIFS taulukko argumentilla
Olen yrittänyt selittää tämän lähestymistavan olemuksen SUMIF-esimerkissä, joten nyt voimme yksinkertaisesti kopioida kaavan, muuttaa argumenttien järjestystä (kuten muistat, että SUMIF: ssä ja SUMIFS: ssa on erilainen) ja lisätä muita ehtoja. tuloksena oleva kaava on pienempi kuin SUMIFS + SUMIFS:
=SUM(SUMIFS(D2:D9,C2:C9, {"Mike", "John", "Pete"}, E2:E9,">=10/1/2014", E2:E9, "<=10/31/2014"))
Tämän kaavan palauttama tulos on täsmälleen sama kuin näet yllä olevasta kuvakaappauksesta.
Exampl e 3. SUMPRODUCT & SUMIFS
Kuten muistat, SUMPRODUCT-lähestymistapa eroaa kahdesta edellisestä siinä, että syötät kaikki ehdot erilliseen soluun pikemminkin, että ne määritetään suoraan kaavassa. Jos kyseessä on useita ehtosarjoja, SUMPRODUCT-toiminto ei riitä ja joudut käyttämään myös ISNUMBER- ja MATCH-toimintoja.
Jos oletetaan, että tarvikkeiden nimet ovat soluissa H1: H3, alkamispäivä on solussa H4 ja lopetuspäivä solussa H5, SUMPRODUCT-kaavallemme on seuraava muoto:
=SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<=H5), --(ISNUMBER(MATCH(C2:C9, H1:H3,0))), D2:D9)
Monet ihmiset ihmettelevät, miksi käyttää kaksoisviivaa (-) SUMPRODUCT-kaavoissa. Asia on, että Excel SUMPRODUCT jättää kaikki lukuun ottamatta numeerisia arvoja, kun taas kaavan vertailuoperaattorit palauttavat loogiset arvot (TOSI / EPÄTOSI), jotka eivät ole numeerisia. Jos haluat muuntaa nämä Boolen-arvot 1 ”: ksi ja 0”: ksi, käytä kaksinkertaista miinusmerkkiä, jota teknisesti kutsutaan kaksinkertaiseksi unary-operaattoriksi. Ensimmäinen unary pakottaa arvon TOSI / EPÄTOSI arvoon -1/0. Toinen unary hylkää arvot, eli kääntää merkin kääntämällä ne +1: ksi ja 0: ksi, jotka SUMPRODUCT-funktio voi ymmärtää.
Toivon, että yllä olevalla selityksellä on järkeä. Ja vaikka se ei olisikaan, muista vain tämä nyrkkisääntö – käytä kaksoisarvoista operaattoria (-), kun käytät vertailuoperaattoreita SUMPRODUCT-kaavoissa.
Excel SUM: n käyttäminen matriisikaavoissa
Kuten muistat, Microsoft otti SUMIFS-funktion käyttöön Excel 2007. Jos joku käyttää edelleen Excel 2003: ta, 2000: aa tai vanhempaa, sinun on käytettävä SUM-taulukon kaavaa lisätäksesi arvoja, joilla on useita JA-ehtoja. toimii myös Excel 2013 – 2007: n nykyaikaisissa versioissa, ja sitä voidaan pitää SUMIFS-funktion vanhanaikaisena vastineena.
Edellä mainituissa SUMIF-kaavoissa olet jo käyttänyt matriisiargumentteja, mutta matriisikaavaa on jotain erilaista.
Esimerkki 1. Summa useilla AND-ehdoilla Excel 2003: ssa ja aiemmissa
Palataan takaisin ensimmäiseen esimerkkiin, jossa saimme summasumman tietylle hedelmälle ja toimittajalle:
Kuten jo tiedät, tämä tehtävä on helppo suorittaa käyttämällä tavallinen SUMIFS-kaava:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
Katsotaan nyt, kuinka sama tehtävä voidaan suorittaa Excelin SUMIFS-vapaissa -versioissa . Ensinnäkin kirjoitat kaikki ehdot, jotka on täytettävä, muodossa range = ”condition”. Tässä esimerkissä meillä on kaksi alue / ehto paria:
Ehto 1: A2: A9 = ”omenat”
Ehto 2: B2: B9 = ”Pete”
Sitten kirjoitat SUM-kaavan, joka ”kertoo” kaikki alue / ehto parisi sulkeissa. Viimeinen kerroin on summa-alue, C2: C9 tapauksessamme:
=SUM((A2:A9="apples") * ( B2:B9="Pete") * ( C2:C9))
Kuten alla olevassa kuvakaappauksessa on esitetty, kaava toimii täydellisesti uusimmassa Excel 2013 -versiossa.
Esimerkki 2. SUM-taulukon kaavat nykyaikaisissa Excel-versioissa
Even Excelin nykyaikaisissa versioissa SUM-funktion tehoa ei pidä aliarvioida. SUM-taulukon kaava ei ole pelkästään mielen voimistelu, vaan sillä on käytännön arvoa, kuten seuraava esimerkki osoittaa.
Oletetaan , sinulla on kaksi saraketta, B ja C, ja sinun on laskettava, kuinka monta kertaa sarake C on suurempi kuin sarake B, kun sarakkeen C arvo on suurempi tai yhtä suuri kuin 10. Välitön ratkaisu, joka tulee mieleen, on SUM taulukon kaava:
=SUM((C1:C10>=10) * (C1:C10>B1:B10))
Älä näe mitään käytännön sovellusta yllä olevaan kaavaan? Ajattele sitä toisella tavalla:)
Oletetaan, että sinulla on tilausluettelo, kuten alla olevassa kuvakaappauksessa, ja haluat tietää, kuinka monta tuotetta ei ole toimitettu kokonaisuudessaan tiettyyn päivämäärään mennessä. Excelin kielelle käännettynä meillä on seuraavat ehdot:
Ehto 1: Sarakkeen B (järjestetyt kohteet) arvo on suurempi kuin 0
Ehto 2: arvo sarakkeessa C (toimitettu) vähemmän kuin sarakkeessa B
Ehto 3: Päivämäärä sarakkeessa D (eräpäivä) on alle 11.11.2014.
Kolmen alueen asettaminen / ehtoparit yhdessä, saat seuraavan kaavan:
=SUM((B2:B10>=0)*(B2:B10>C2:C10)*(D2:D10<G2))
No, tässä opetusohjelmassa käsitellyt kaavaesimerkit ovat vain raaputtaneet pintaa sille, mitä Excel SUMIFS- ja SUMIF-funktiot todella voivat tehdä. Mutta toivottavasti ne ovat auttaneet sinua osoittamaan oikeaan suuntaan ja nyt voit laskea arvot Excel-työkirjoihisi riippumatta kuinka monta monimutkaista tilannetta sinun on harkittava.
- Kuinka käyttää SUMIFia Excelissä – kaavan esimerkkejä solujen ehdolliseen yhteenlaskemiseen
- Kuinka käyttää Excel SUMIFSia ja SUMIFia useilla ehdoilla – kaavan esimerkkejä
- Kuinka käyttää VLOOKUP & SUM- tai SUMIF-funktioita Excelissä
- SUMIF Google Sheetsissä kaavan esimerkkien avulla
- COUNTIF Excelissä – lasketaan, jos ei tyhjä, suurempi kuin, kopio tai yksilöllinen
- solujen laskeminen ja summaaminen värin mukaan Excelissä