Tämä opetusohjelma osoittaa, miten Excelin AVERAGEIF- ja AVERAGEIFS -toimintoja käytetään Excelissä ja Google Sheetsissä tiettyjen kriteerien mukaisten tietojen keskiarvon määrittämiseen.
AVERAGEIF -toiminnon yleiskatsaus
Voit käyttää Excelin AVERAGEIF -funktiota laskemaan soluja, jotka sisältävät tietyn arvon, laskemaan soluja, jotka ovat suurempia tai yhtä suuria kuin arvo jne.
Jos haluat käyttää AVERAGEIF Excel -laskentataulukkotoimintoa, valitse solu ja kirjoita:
(Huomaa, miten kaavasyötteet näkyvät)
AVERAGEIF -funktion syntaksi ja argumentit:
= KESKIARVO (alue, ehdot, [keskimääräinen_alue])
valikoima - Laskettavien solujen alue.
kriteeri - Kriteerit, jotka määräävät, mitkä solut tulisi laskea.
keskimääräinen_alue - [valinnainen] Solujen keskiarvo. Kun se jätetään pois, käytetään aluetta.
Mikä on AVERAGEIF -toiminto?
AVERAGEIF -toiminto on yksi laskentataulukoiden vanhemmista toiminnoista. Sitä käytetään skannaamaan solualue, joka tarkistaa tietyn kriteerin ja antaa sitten keskiarvon (eli matemaattisen keskiarvon), jos arvot vastaavat kyseistä arvoa. Alkuperäinen AVERAGEIF -toiminto rajoittui vain yhteen kriteeriin. Vuoden 2007 jälkeen luotiin AVERAGEIFS -toiminto, joka sallii lukuisia ehtoja. 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 tallennetuista myynnistä ja haluamme tietää keskimääräiset tulot.
Koska meillä oli kulu, negatiivinen arvo, emme voi tehdä vain peruskeskiarvoa. Sen sijaan haluamme keskittää vain arvot, jotka ovat suurempia kuin 0. "Suurempi kuin 0" on kriteerimme AVERAGEIF -funktiossa. Meidän kaava tämän toteamiseksi on
= KESKIARVO (A2: A7, "> 0")
Esimerkki kahdesta sarakkeesta
Vaikka alkuperäinen AVERAGEIF -toiminto on suunniteltu siten, että voit soveltaa ehtoa summa -alueelle, jonka haluat laskea yhteen, useimmiten sinun on sovellettava yhtä tai useampaa ehtoa muihin sarakkeisiin. Tarkastellaan tätä taulukkoa:
Jos nyt käytämme alkuperäistä AVERAGEIF -funktiota selvittääksemme, kuinka monta banaania meillä on keskimäärin. Laitamme kriteerit soluun D1 ja meidän on annettava haluamamme alue keskiverto viimeisenä argumenttina, ja niin meidän kaava olisi
= KESKIARVO (A2: A7, D1, B2: B7)
Kuitenkin, kun ohjelmoijat lopulta ymmärsivät, että käyttäjät halusivat antaa useamman kuin yhden kriteerin, AVERAGEIFS -toiminto luotiin. Jotta voidaan luoda yksi rakenne, joka toimisi monenlaisille kriteereille, AVERAGEIFS edellyttää, että summa -alue on lueteltu ensin. Esimerkissämme tämä tarkoittaa, että kaavan on oltava
= KESKIARVOT (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 keskimääräisen kävijämäärän löytämiseksi voisi olla:
= KESKIMÄÄRÄISET (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ä:
= KESKIMÄÄRÄISET (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2)
KESKIMÄÄRÄT 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 Adamin ja Bobin keskimääräisen myynnin. Ensinnäkin nopea keskustelu keskiarvojen ottamisesta. Jos sinulla on epätasainen määrä asioita, kuten 3 merkintää Adamille ja 2 Bobille, et voi yksinkertaisesti ottaa kunkin henkilön myynnin keskiarvoa. Tämä tunnetaan keskiarvojen laskemisena, ja lopulta annat epäoikeudenmukaisen painotuksen kohteelle, jossa on vähän merkintöjä. Jos näin on tietojen kanssa, sinun on laskettava keskiarvo "manuaalisesti": ota kaikkien kohteidesi summa jaettuna kohteidesi määrällä. Voit tarkistaa tämän tekemisen lukemalla artikkelit täältä:
Jos merkintöjen määrä on sama, kuten taulukossamme, sinulla on pari vaihtoehtoa. Yksinkertaisin on lisätä kaksi KESKIMÄÄRÄISTÄ yhteen ja jakaa sitten kahdella (luettelomme kohteiden määrä)
= (KESKIARVOT (B2: B7, A2: A7, "Adam")+KESKIMÄÄRÄISET (B2: B7, A2: A7, "Bob"))/2
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 KESKIARVOA. Voit kuitenkin tehdä tämän myös kirjoittamalla kriteerit taulukkoon, kuten tämä:
= KESKIARVO (KESKIMÄÄRÄISET (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 AVERAGEIFS -funktion kullekin matriisimme kohteelle ja luoda siten numeroryhmän. Ulompi KESKI -funktio ottaa sitten kyseisen numeroryhmän ja muuttaa sen yhdeksi numeroksi. Kaavan arvioinnin läpi se näyttää tältä:
= KESKIMÄÄRÄINEN (KESKIARVOT (B2: B7, A2: A7, {"Adam", "Bob"}))) = KESKIARVO (13701, 21735) = 17718
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ää kokonaiskeskiarvon todella tyhjiä soluja, käytämme kriteeriä "=", ja kaavamme näyttäisi tältä:
= KESKIMÄÄRÄISET (B2: B7, A2: A7, "=")
Toisaalta, jos haluamme saada kaikkien solujen, jotka näyttävät visuaalisesti tyhjiltä, keskiarvon, muutamme kriteerit arvoksi "" ja kaava näyttää
= KESKIMÄÄRÄISET (B2: B7, A2: A7, "")
Käännetään se ympäri: entä jos haluat löytää ei-tyhjien solujen keskiarvon? 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.
= KESKIMÄÄRÄISET (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
AVERAGEIF Google Sheetsissä
AVERAGEIF -toiminto toimii täsmälleen samalla tavalla Google Sheetsissä kuin Excelissä:
