See õpetus näitab, kuidas kasutada Exceli AVERAGEIF ja AVERAGEIFS funktsioone Excelis ja Google'i arvutustabelites teatud kriteeriumidele vastavate andmete keskmiseks.
Funktsiooni AVERAGEIF ülevaade
Excelis funktsiooni AVERAGEIF abil saate loendada lahtreid, mis sisaldavad konkreetset väärtust, loendada lahtreid, mis on suuremad või võrdsed väärtusega jne.
Funktsiooni AVERAGEIF Excel töölehe kasutamiseks valige lahter ja tippige:
(Pange tähele, kuidas valemisisendid ilmuvad)
Funktsiooni AVERAGEIF süntaks ja argumendid:
= AVERAGEIF (vahemik, kriteeriumid, [keskmine_vahemik])
vahemik - Loendatavate rakkude vahemik.
kriteeriumid - kriteeriumid, mis kontrollivad, milliseid lahtreid loendada tuleks.
keskmine_vahemik - [valikuline] Lahtrid keskmiselt. Kui see välja jäetakse, kasutatakse vahemikku.
Mis on funktsioon AVERAGEIF?
Funktsioon AVERAGEIF on üks vanemaid arvutustabelites kasutatavaid funktsioone. Seda kasutatakse lahtrite vahemiku skannimiseks, kontrollides konkreetset kriteeriumi ja andes seejärel keskmise (aka matemaatilise keskmise), kui väärtused vahemikus vastavad nendele väärtustele. Algne AVERAGEIF -funktsioon piirdus vaid ühe kriteeriumiga. Pärast 2007. aastat loodi funktsioon AVERAGEIFS, mis võimaldab paljusid kriteeriume. Enamik üldist kasutust jääb nende kahe vahel samaks, kuid süntaksis on mõned kriitilised erinevused, mida arutame kogu selle artikli jooksul.
Kui te pole seda juba teinud, saate suure osa sarnasest struktuurist ja näidetest läbi vaadata artiklis COUNTIFS.
Põhinäide
Vaatame seda registreeritud müügi loendit ja tahame teada keskmist sissetulekut.
Kuna meil oli kulu, negatiivne väärtus, ei saa me lihtsalt teha keskmist. Selle asemel tahame keskmistada ainult väärtusi, mis on suuremad kui 0. „Suurem kui 0” on meie kriteeriumid funktsioonis AVERAGEIF. Meie valem selle kinnitamiseks on
= KESKMINE (A2: A7, "> 0")
Kahe veeru näide
Kuigi algne funktsioon AVERAGEIF oli loodud selleks, et saaksite rakendada kriteeriume arvude vahemikule, mille soovite summeerida, peate suure osa ajast rakendama teistele veergudele ühe või mitu kriteeriumi. Vaatleme seda tabelit:
Kui me kasutame esialgset funktsiooni AVERAGEIF, et teada saada, kui palju banaane meil keskmiselt on. Paneme oma kriteeriumid lahtrisse D1 ja peame andma soovitud vahemiku keskmine viimase argumendina ja nii oleks ka meie valem
= KESKMINE (A2: A7, D1, B2: B7)
Kui aga programmeerijad lõpuks mõistsid, et kasutajad soovivad anda rohkem kui ühe kriteeriumi, loodi funktsioon AVERAGEIFS. Selleks, et luua üks struktuur, mis toimiks mis tahes arvu kriteeriumide jaoks, nõuab AVERAGEIFS, et summa vahemik on kõigepealt loetletud. Meie näites tähendab see, et valem peab olema
= KESKMINE (B2: B7, A2: A7, D1)
MÄRKUS. Need kaks valemit annavad sama tulemuse ja võivad tunduda sarnased, seega pöörake hoolikalt tähelepanu sellele, millist funktsiooni kasutatakse, et veenduda, et loetlete kõik argumendid õiges järjekorras.
Kuupäevadega töötamine, mitu kriteeriumi
Arvutustabelis kuupäevadega töötades, kuigi kuupäeva on võimalik sisestada otse valemisse, on hea tava, kui kuupäev on lahtris, nii et saate valemile lihtsalt viidata. Näiteks aitab see arvutil teada, et soovite kasutada kuupäeva 27.5.2020, mitte numbrit 5 jagatuna 27 -ga jagatuna 2022. aastaks.
Vaatame järgmist tabelit, mis registreerib saidi külastajate arvu iga kahe nädala tagant.
D2 ja E2 saame määrata vahemiku algus- ja lõpp -punkti, mida soovime vaadata. Meie valem, et leida selles vahemikus keskmine külastajate arv, võiks olla järgmine:
= KESKMINE (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2)
Pange tähele, kuidas kriteeriumide loomiseks suutsime võrdluse „=” lahterviidetega ühendada. Isegi kui mõlemat kriteeriumi rakendati samale lahtrivahemikule (A2: A7), peate vahemiku kirjutama kaks korda, üks kord iga kriteeriumi kohta.
Mitu veergu
Kui kasutate mitut kriteeriumi, saate neid rakendada samale vahemikule, nagu me tegime eelmise näitega, või saate neid rakendada erinevatele vahemikele. Kombineerime oma näidisandmed sellesse tabelisse:
Oleme seadistanud mõned lahtrid, et kasutaja saaks sisestada otsitava lahtritesse E2 kuni G2. Vajame seega valemit, mis liidab kokku veebruaris korjatud õunte koguarvu. Meie valem näeb välja selline:
= KESKMISED (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2)
VÕI tüüpi loogikaga KESKMISED
Siiani on kõik meie kasutatud näited olnud JA -põhine võrdlus, kus otsime ridu, mis vastavad kõigile meie kriteeriumidele. Nüüd kaalume juhtumit, kui soovite otsida võimalust, et rida vastab ühele või teisele kriteeriumile.
Vaatame seda müügiloendit:
Soovime liita nii Adami kui ka Bobi keskmise müügi. Esiteks kiire arutelu keskmiste võtmise üle. Kui teil on ebaühtlane arv asju, näiteks 3 kirjet Aadama ja 2 Bobi kohta, ei saa te lihtsalt võtta iga inimese müügi keskmist. See on tuntud kui keskmiste keskmiste väärtuste võtmine ja lõpuks annate ebaõiglase kaalu üksusele, millel on vähe kirjeid. Kui see on teie andmete puhul nii, peate arvutama keskmise „käsitsi”: arvutage kõigi üksuste summa ja jagades nende arvuga. Kuidas seda teha, saate vaadata artikleid siit:
Nüüd, kui kirjete arv on sama, näiteks meie tabelis, on teil paar võimalust. Lihtsaim on liita kaks KESKMIST vahemaad kokku ja jagada seejärel kahega (meie loendis olevate üksuste arv)
= (KESKMINE (B2: B7, A2: A7, "Adam")+KESKMINE (B2: B7, A2: A7, "Bob"))/2
Siin lasime arvutil arvutada oma individuaalsed skoorid ja seejärel liidame need kokku.
Meie järgmine valik sobib siis, kui teil on rohkem kriteeriumivahemikke, nii et te ei soovi kogu valemit korduvalt ümber kirjutada. Eelmises valemis käskisime arvutil käsitsi lisada kaks erinevat KESKMIST. Kuid saate seda teha ka siis, kui kirjutate oma kriteeriumid massiivi sisse, näiteks:
= KESKMINE (AVERAGEIFS (B2: B7, A2: A7, {"Adam", "Bob"}))
Vaadake, kuidas massiiv on lokkisulgude sisse ehitatud. Kui arvuti seda valemit hindab, saab ta teada, et tahame arvutada iga massiivi üksuse jaoks funktsiooni AVERAGEIFS, luues seeläbi numbrimassiivi. Väline KESKMINE funktsioon võtab selle numbrimassiivi ja muudab selle üheks numbriks. Valemihindamist läbides näeks see välja selline:
= KESKMINE (AVERAGEIFS (B2: B7, A2: A7, {"Adam", "Bob"}))) = KESKMINE (13701, 21735) = 17718
Saame sama tulemuse, kuid suutsime valemi natuke napisõnalisemalt välja kirjutada.
Toorikutega tegelemine
Mõnikord on teie andmekogul tühjad lahtrid, mida peate leidma või vältima. Nende kriteeriumide seadmine võib olla pisut keeruline, nii et vaatame teist näidet.
Pange tähele, et lahter A3 on tõeliselt tühi, samas kui lahtril A5 on valem, mis tagastab nullpikkuse stringi "". Kui tahame leida kogu keskmise tõesti tühjad lahtrid, kasutaksime kriteeriumi "=" ja meie valem näeks välja selline:
= KESKMINE (B2: B7, A2: A7, "=")
Teisest küljest, kui tahame saada kõigi visuaalselt tühjade lahtrite keskmist, muudame kriteeriumid väärtuseks „” ja valem näeb välja selline
= KESKMINE (B2: B7, A2: A7, "")
Pöörame selle ümber: mis siis, kui soovite leida tühjade lahtrite keskmist? Kahjuks ei lase praegune disain teil nullpikkust stringi vältida. Võite kasutada kriteeriumi „”, kuid nagu näites näete, sisaldab see siiski rea 5 väärtust.
= KESKMINE (B2: B7, A2: A7, "")
Kui te ei pea arvestama lahtreid, mis sisaldavad nullpikkusi, peaksite kaaluma funktsiooni LEN kasutamist SUMPRODUCT -is
AVERAGEIF Google'i arvutustabelites
Funktsioon AVERAGEIF töötab Google'i arvutustabelites täpselt samamoodi nagu Excelis:
