Ta vadnica prikazuje, kako uporabiti funkciji Excel AVERAGEIF in AVERAGEIFS v Excelu in Google Preglednicah za povprečje podatkov, ki izpolnjujejo določena merila.
AVERAGEIF Pregled funkcije
Funkcijo AVERAGEIF v Excelu lahko uporabite za štetje celic, ki vsebujejo določeno vrednost, štetje celic, ki so večje ali enake vrednosti itd.
Če želite uporabiti funkcijo delovnega lista Excel AVERAGEIF, izberite celico in vnesite:
(Upoštevajte, kako so prikazani vnosi formule)
Sintaksa in argumenti funkcije AVERAGEIF:
= AVERAGEIF (obseg, merila, [povprečni_razpon])
obseg - Obseg celic za štetje.
merila - Merila, ki nadzorujejo, katere celice je treba šteti.
povprečni_razpon - [neobvezno] Povprečne celice. Če izpustite, se uporabi obseg.
Kaj je funkcija AVERAGEIF?
Funkcija AVERAGEIF je ena starejših funkcij, ki se uporabljajo v preglednicah. Uporablja se za skeniranje skozi vrsto celic, ki preverjajo določeno merilo, nato pa poda povprečje (znano tudi kot matematično povprečje), če vrednosti v območju, ki ustrezajo tem vrednostim. Prvotna funkcija AVERAGEIF je bila omejena le na eno merilo. Po letu 2007 je bila ustvarjena funkcija AVERAGEIFS, ki omogoča množico meril. Večina splošne uporabe med obema ostaja enaka, vendar obstajajo nekatere kritične razlike v skladnji, o katerih bomo razpravljali v tem članku.
Če tega še niste storili, si lahko v članku COUNTIFS ogledate večino podobne strukture in primerov.
Osnovni primer
Razmislimo o tem seznamu zabeležene prodaje in želimo vedeti povprečni dohodek.
Ker smo imeli strošek, negativno vrednost, ne moremo narediti le osnovnega povprečja. Namesto tega želimo povprečiti le vrednosti, ki so večje od 0. "Več kot 0" je tisto, kar bodo naši kriteriji v funkciji AVERAGEIF. Naša formula, da to trdimo, je
= Povprečje (A2: A7, "> 0")
Primer z dvema stolpcema
Čeprav je bila prvotna funkcija AVERAGEIF zasnovana tako, da lahko uporabite merilo za obseg števil, ki jih želite sešteti, boste morali večino časa uporabiti eno ali več meril za druge stolpce. Poglejmo to tabelo:
Zdaj, če uporabimo izvirno funkcijo AVERAGEIF, da ugotovimo, koliko banan imamo v povprečju. Naša merila bomo postavili v celico D1 in podali moramo obseg, ki ga želimo povprečje kot zadnji argument in tako bi bila naša formula
= Povprečje (A2: A7, D1, B2: B7)
Ko pa so programerji na koncu spoznali, da želijo uporabniki podati več kot eno merilo, je bila ustvarjena funkcija AVERAGEIFS. Da bi ustvarili eno strukturo, ki bi delovala za poljubno število meril, AVERAGEIFS zahteva, da je najprej naveden obseg vsote. V našem primeru to pomeni, da mora biti formula
= Povprečje (B2: B7, A2: A7, D1)
OPOMBA: Ti dve formuli dobita enak rezultat in si lahko izgledata podobno, zato bodite pozorni na to, katero funkcijo uporabljate, da vse argumente navedete v pravilnem vrstnem redu.
Delo z datumi, več meril
Ko delate z datumi v preglednici, čeprav je datum mogoče vnesti neposredno v formulo, je najbolje, da imate datum v celici, tako da se lahko preprosto sklicujete na celico v formuli. Tako računalnik na primer pomaga vedeti, da želite uporabiti datum 27.5.2020 in ne številke 5, deljeno s 27, deljeno z 2022.
Poglejmo našo naslednjo tabelo, ki beleži število obiskovalcev spletnega mesta vsaka dva tedna.
Lahko določimo začetno in končno točko območja, ki ga želimo pogledati v D2 in E2. Naša formula za iskanje povprečnega števila obiskovalcev v tem območju bi lahko bila:
= Povprečje (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2)
Upoštevajte, kako smo lahko združili primerjave »=« z referencami celic, da smo ustvarili merila. Čeprav sta bila oba merila uporabljena za isti obseg celic (A2: A7), morate obseg zapisati dvakrat, enkrat za vsako merilo.
Več stolpcev
Če uporabljate več meril, jih lahko uporabite za isti obseg, kot smo ga uporabili v prejšnjem primeru, ali pa jih lahko uporabite za različne obsege. Združimo naše vzorčne podatke v to tabelo:
Uporabnikom smo nastavili nekaj celic, da v celice od E2 do G2 vnesejo tisto, kar želijo iskati. Zato potrebujemo formulo, ki bo seštela skupno število jabolk, nabranih februarja. Naša formula izgleda takole:
= Povprečje (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2)
POVPREČNI Z logiko tipa OR
Do sedaj so bili vsi primeri, ki smo jih uporabili, primerjava na osnovi AND, kjer iščemo vrstice, ki izpolnjujejo vsa naša merila. Zdaj bomo razmislili o primeru, ko želite poiskati možnost, da vrstica izpolnjuje eno ali drugo merilo.
Poglejmo ta seznam prodaje:
Radi bi sešteli povprečno prodajo za Adama in Boba. Najprej hitra razprava o povprečnih vrednostih. Če imate neenako število stvari, na primer 3 vnose za Adama in 2 za Boba, ne morete preprosto vzeti povprečja prodaje vsake osebe. To je znano kot vzemanje povprečja povprečja, na koncu pa nepravično ponderirate element, ki ima malo vnosov. Če je tako z vašimi podatki, bi morali izračunati povprečje na "ročni" način: vzemite vsoto vseh svojih predmetov, deljeno s številom vaših predmetov. Če želite pregledati, kako to storiti, si oglejte članke tukaj:
Če je število vnosov enako, na primer v naši tabeli, imate na voljo nekaj možnosti. Najenostavnejše je, da tako skupaj dodate dva povprečja in nato delite z 2 (število elementov na našem seznamu)
= (Povprečje (B2: B7, A2: A7, "Adam")+povprečje (B2: B7, A2: A7, "Bob"))/2
Tukaj smo imeli računalnik izračunati naše posamezne rezultate in jih nato seštejemo.
Naša naslednja možnost je dobra, če imate več razponov meril, na primer, da vam ni treba večkrat prepisovati celotne formule. V prejšnji formuli smo računalniku ročno povedali, naj skupaj doda dva različna povprečja. To pa lahko storite tudi tako, da svoja merila zapišete v matriko, na primer:
= Povprečno (povprečje (B2: B7, A2: A7, {"Adam", "Bob"}))
Poglejte, kako je matrika sestavljena znotraj kodrastih oklepajev. Ko računalnik ovrednoti to formulo, bo vedel, da želimo izračunati funkcijo AVERAGEIFS za vsako postavko v našem nizu in tako ustvariti niz številk. Zunanja funkcija AVERAGE bo nato vzela ta niz številk in jih spremenila v eno samo številko. Ko bi stopili skozi oceno formule, bi to izgledalo tako:
= POVPREČNO (POVPREČNO (B2: B7, A2: A7, {"Adam", "Bob"})) = POVPREČNO (13701, 21735) = 17718
Dobili smo enak rezultat, vendar smo lahko formulo zapisali nekoliko bolj jedrnato.
Ukvarjanje s prazninami
Včasih bodo v vašem naboru podatkov prazne celice, ki jih morate poiskati ali se jim izogniti. Določitev meril za te je lahko nekoliko težavna, zato poglejmo še en primer.
Upoštevajte, da je celica A3 resnično prazna, celica A5 pa ima formulo, ki vrne niz dolžine »«. Če želimo najti skupno povprečje resnično prazne celice, uporabili bi merilo "=", naša formula pa bi izgledala tako:
= Povprečje (B2: B7, A2: A7, "=")
Po drugi strani pa, če želimo doseči povprečje za vse celice, ki so vizualno videti prazne, spremenimo merila na »«, formula pa izgleda tako
= Povprečje (B2: B7, A2: A7, "")
Obrnimo to: kaj, če želite najti povprečje nepraznih celic? Na žalost trenutna zasnova ne omogoča izogibanja nizu ničelne dolžine. Uporabite lahko merilo »«, vendar, kot vidite v primeru, še vedno vključuje vrednost iz 5. vrstice.
= Povprečje (B2: B7, A2: A7, "")
Če ne želite šteti celic, ki vsebujejo nize dolžine nič, razmislite o uporabi funkcije LEN znotraj SUMPRODUCT
AVERAGEIF v Google Preglednicah
Funkcija AVERAGEIF deluje v Google Preglednicah popolnoma enako kot v Excelu:
