Tämä opetusohjelma osoittaa, miten Erinomainenel SUMIF ja SUMIFS Functioita Excelissä ja Google Sheetsissä tietyn kriteerin täyttävien tietojen laskemiseksi yhteen.
SUMIF -toiminnon yleiskatsaus
Voit käyttää Excelin SUMIF -funktiota solujen summaan, jotka sisältävät tietyn arvon, summan, joka on suurempi tai yhtä suuri kuin arvo jne.
(Huomaa, miten kaavasyötteet näkyvät)
SUMIF -toiminnon syntaksi ja argumentit:
| 1 | = SUMIF (alue, ehdot, [summa_alue]) |
valikoima - Solualue, johon haluat soveltaa ehtoja.
kriteeri - kriteerit, joita käytetään määritettäessä lisättävät solut.
summa_alue - [valinnainen] Solut, jotka lisätään yhteen. Jos summa_alue jätetään pois, alueen solut lasketaan yhteen.
Mikä on SUMIF -toiminto?
SUMIF -toiminto on yksi laskentataulukoiden vanhemmista toiminnoista. Sitä käytetään skannaamaan solualue, joka tarkistaa tietyn kriteerin, ja lisää sitten arvot kyseistä arvoa vastaavalle alueelle. Alkuperäinen SUMIF -toiminto rajoittui vain yhteen kriteeriin. Vuoden 2007 jälkeen luotiin SUMIFS -toiminto, joka mahdollistaa lukuisia kriteerejä. Suurin osa yleisestä käytöstä pysyy samana näiden kahden välillä, mutta syntaksissa on joitain kriittisiä eroja, joista keskustelemme tässä artikkelissa.
Jos et ole jo tehnyt sitä, voit tarkastella paljon vastaavaa rakennetta ja esimerkkejä COUNTIFS -artikkelissa.
Perusesimerkki
Tarkastellaan tätä luetteloa kirjatusta myynnistä ja haluamme tietää kokonaistulot.
Koska meillä oli kulu, negatiivinen arvo, emme voi tehdä vain perussummaa. Sen sijaan haluamme laskea yhteen vain arvot, jotka ovat suurempia kuin 0. "Suurempi kuin 0" on kriteerimme SUMIF -funktiossa. Meidän kaava tämän toteamiseksi on
| 1 | = SUMIF (A2: A7, "> 0") |
Esimerkki kahdesta sarakkeesta
Alkuperäinen SUMIF -toiminto on suunniteltu siten, että voit soveltaa ehtoa summa -alueelle, jonka haluat summaa, mutta useimmiten sinun on sovellettava yhtä tai useampaa ehtoa muihin sarakkeisiin. Tarkastellaan tätä taulukkoa:
Jos nyt käytämme alkuperäistä SUMIF -funktiota selvittääksemme, kuinka monta banaania meillä on (lueteltu solussa D1), meidän on annettava haluamasi alue summa viimeisenä argumenttina, ja niin meidän kaava olisi
| 1 | = SUMIF (A2: A7, D1, B2: B7) |
Kuitenkin, kun ohjelmoijat lopulta ymmärsivät, että käyttäjät halusivat antaa useamman kuin yhden kriteerin, SUMIFS -toiminto luotiin. Jotta voidaan luoda yksi rakenne, joka toimisi monenlaisille kriteereille, SUMIFS edellyttää, että summa -alue on lueteltu ensin. Esimerkissämme tämä tarkoittaa, että kaavan on oltava
| 1 | = SUMIFS (B2: B7, A2: A7, D1) |
HUOMAUTUS: Nämä kaksi kaavaa saavat saman tuloksen ja voivat näyttää samanlaisilta, joten kiinnitä tarkasti huomiota siihen, mitä toimintoa käytetään, jotta voit luetella kaikki argumentit oikeassa järjestyksessä.
Päivämäärien kanssa työskentely, useita kriteerejä
Kun käsittelet päivämääriä laskentataulukossa, vaikka päivämäärä on mahdollista syöttää suoraan kaavaan, on parasta käyttää päivämäärää solussa, jotta voit vain viitata soluun kaavassa. Tämä auttaa esimerkiksi tietokonetta tietämään, että haluat käyttää päivämäärää 27.5.2020, ei numeroa 5 jaettuna 27 jaettuna 2022.
Katsotaanpa seuraavaa taulukkoamme, joka tallentaa sivuston kävijämäärät kahden viikon välein.
Voimme määrittää tarkasteltavan alueen alku- ja loppupisteet kohdissa D2 ja E2. Kaavamme tämän alueen kävijämäärien laskemiseksi voisi olla seuraava:
| 1 | = SUMIFS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2) |
Huomaa, kuinka pystyimme yhdistämään "=" -vertailut soluviittauksiin ehtojen luomiseksi. Lisäksi vaikka molempia ehtoja sovellettiin samaan solualueeseen (A2: A7), sinun on kirjoitettava alue kahdesti, kerran kutakin kriteeriä kohden.
Useita sarakkeita
Kun käytät useita ehtoja, voit soveltaa niitä samaan alueeseen kuin edellisessä esimerkissä tai voit soveltaa niitä eri alueisiin. Yhdistämme esimerkkitietomme tähän taulukkoon:
Olemme määrittäneet joitakin soluja, jotta käyttäjä voi kirjoittaa etsimänsä solut E2 - G2. Tarvitsemme siis kaavan, joka laskee yhteen helmikuussa poimittujen omenoiden kokonaismäärän. Kaavamme näyttää tältä:
| 1 | = SUMIFS (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2) |
SUMIFS OR -tyyppisellä logiikalla
Tähän asti käyttämämme esimerkit ovat olleet AND -pohjaista vertailua, jossa etsimme rivejä, jotka täyttävät kaikki kriteerit. Tarkastellaan nyt tapausta, kun haluat etsiä mahdollisuutta, että rivi täyttää yhden tai toisen kriteerin.
Katsotaanpa tätä myyntiluetteloa:
Haluaisimme laskea yhteen sekä Adamin että Bobin kokonaismyynnin. Tätä varten sinulla on pari vaihtoehtoa. Yksinkertaisin on lisätä kaksi SUMIFSia yhteen seuraavasti:
| 1 | = SUMIFS (B2: B7, A2: A7, "Adam")+SUMIFS (B2: B7, A2: A7, "Bob") |
Tässä meillä on ollut tietokone, joka laskee yksittäiset pisteemme ja lisäämme ne sitten yhteen.
Seuraava vaihtoehto on hyvä silloin, kun sinulla on enemmän kriteerialueita, joten et halua joutua kirjoittamaan koko kaavaa uudelleen toistuvasti. Edellisessä kaavassa käskimme tietokonetta manuaalisesti lisäämään kaksi eri SUMIFSia yhteen. Voit kuitenkin tehdä tämän myös kirjoittamalla kriteerit taulukkoon, kuten tämä:
| 1 | = SUMMA (SUMIFS (B2: B7, A2: A7, {"Adam", "Bob"})) |
Katso, miten taulukko on rakennettu kiharakiinnikkeiden sisään. Kun tietokone arvioi tämän kaavan, se tietää, että haluamme laskea SUMIFS -funktion jokaiselle matriisimme kohteelle ja luoda siten numeroryhmän. Ulompi SUM -funktio ottaa sitten kyseisen numeroryhmän ja muuttaa sen yhdeksi numeroksi. Kaavan arvioinnin läpi se näyttää tältä:
| 123 | = SUMMA (SUMIFS (B2: B7, A2: A7, {"Adam", "Bob"}))= SUMMA (27401, 43470)= 70871 |
Saamme saman tuloksen, mutta pystyimme kirjoittamaan kaavan hieman ytimekkäämmin.
Tyhjien asioiden käsittely
Joskus tietojoukossasi on tyhjiä soluja, jotka sinun on joko löydettävä tai vältettävä. Näiden kriteerien määrittäminen voi olla hieman hankalaa, joten katsotaanpa toista esimerkkiä.
Huomaa, että solu A3 on todella tyhjä, kun taas solussa A5 on kaava, joka palauttaa nollapituisen merkkijonon ””. Jos haluamme löytää kokonaissumman todella tyhjiä soluja, käytämme kriteeriä "=", ja kaavamme näyttäisi tältä:
| 1 | = SUMIFS (B2: B7, A2: A7, "=") |
Toisaalta, jos haluamme saada summan kaikista soluista, jotka näyttävät visuaalisesti tyhjiltä, muutamme kriteerit muotoon "" ja kaava näyttää
| 1 | = SUMIFS (B2: B7, A2: A7, "") |
Käännetään se ympäri: entä jos haluat löytää ei-tyhjien solujen summan? Valitettavasti nykyinen rakenne ei anna sinun välttää nollapituista merkkijonoa. Voit käyttää kriteeriä "", mutta kuten esimerkistä näet, se sisältää edelleen rivin 5 arvon.
| 1 | = SUMIFS (B2: B7, A2: A7, "") |
Jos sinun ei tarvitse laskea soluja, jotka sisältävät nollapituisia merkkijonoja, sinun kannattaa harkita LEN -toiminnon käyttöä SUMPRODUCTissa
SUMIF Google Sheetsissä
SUMIF -toiminto toimii täsmälleen samalla tavalla Google Sheetsissä kuin Excelissä:
