Ovaj vodič prikazuje kako koristiti Excel AVERAGEIF i AVERAGEIFS funkcije u Excelu i Google tablicama za prosjek podataka koji zadovoljavaju određene kriterije.
AVERAGEIF Pregled funkcije
Možete koristiti funkciju AVERAGEIF u Excelu za brojanje ćelija koje sadrže određenu vrijednost, brojanje ćelija koje su veće ili jednake vrijednosti itd.
Da biste koristili funkciju AVERAGEIF Excel radnog lista, odaberite ćeliju i upišite:
(Obratite pažnju na to kako se pojavljuju unosi formule)
AVERAGEIF Sintaksa i argumenti funkcije:
= AVERAGEIF (raspon, kriteriji, [prosječni_ raspon])
domet - Raspon stanica za brojanje.
kriterijima - Kriteriji koji kontroliraju koje ćelije treba brojati.
prosječan_ raspon - [izborno] Stanice za prosjek. Kad se izostavi, koristi se raspon.
Što je funkcija AVERAGEIF?
Funkcija AVERAGEIF jedna je od starijih funkcija koja se koristi u proračunskim tablicama. Koristi se za skeniranje kroz niz ćelija radi provjere određenog kriterija, a zatim daje prosjek (poznat i kao matematička sredina) ako vrijednosti u rasponu odgovaraju tim vrijednostima. Izvorna funkcija AVERAGEIF bila je ograničena na samo jedan kriterij. Nakon 2007. stvorena je funkcija AVERAGEIFS koja dopušta mnoštvo kriterija. Većina opće uporabe ostaje ista među njima, ali postoje neke kritične razlike u sintaksi o kojima ćemo govoriti u ovom članku.
Ako već niste, većinu slične strukture i primjera možete pregledati u članku COUNTIFS.
Osnovni primjer
Razmotrimo ovaj popis zabilježene prodaje i želimo znati prosječan prihod.
Budući da smo imali trošak, negativnu vrijednost, ne možemo napraviti samo osnovni prosjek. Umjesto toga, želimo prosjek samo vrijednosti koje su veće od 0. "Više od 0" će biti naši kriteriji u funkciji AVERAGEIF. Naša formula da to kažemo jest
= PROSJEČNO (A2: A7, "> 0")
Primjer s dvije kolone
Iako je izvorna funkcija AVERAGEIF osmišljena kako bi vam omogućila da primijenite kriterij na raspon brojeva koje želite zbrojiti, velik dio vremena morat ćete primijeniti jedan ili više kriterija na druge stupce. Razmotrimo ovu tablicu:
Sada, ako upotrijebimo izvornu funkciju AVERAGEIF kako bismo saznali koliko banana imamo u prosjeku. Stavit ćemo naše kriterije u ćeliju D1 i morat ćemo dati raspon koji želimo prosjek kao posljednji argument, pa bi i naša formula bila
= PROSJEČNO (A2: A7, D1, B2: B7)
Međutim, kada su programeri na kraju shvatili da korisnici žele dati više od jednog kriterija, stvorena je funkcija AVERAGEIFS. Kako bi se stvorila jedna struktura koja bi radila za bilo koji broj kriterija, AVERAGEIFS zahtijeva da se prvo navede raspon zbroja. U našem primjeru to znači da formula mora biti
= PROSJEČNI BROJ (B2: B7, A2: A7, D1)
NAPOMENA: Ove dvije formule dobivaju isti rezultat i mogu izgledati slično, stoga pažljivo obratite pozornost na to koja se funkcija koristi kako biste bili sigurni da ste sve argumente naveli ispravnim redoslijedom.
Rad s datumima, više kriterija
Prilikom rada s datumima u proračunskoj tablici, iako je moguće unijeti datum izravno u formulu, najbolje je da datum bude u ćeliji tako da se možete jednostavno pozvati na ćeliju u formuli. Na primjer, ovo pomaže računalu da zna da želite koristiti datum 27.5.2020., A ne broj 5 podijeljen s 27 podijeljen s 2022. godinom.
Pogledajmo sljedeću tablicu koja bilježi broj posjetitelja web stranice svaka dva tjedna.
Možemo odrediti početnu i završnu točku raspona koji želimo pogledati u D2 i E2. Naša formula za pronalaženje prosječnog broja posjetitelja u ovom rasponu mogla bi biti:
= PROSJEČNI BROJ (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2)
Zapazite kako smo uspjeli spojiti usporedbe "=" s referencama ćelija da bismo stvorili kriterije. Također, iako su se oba kriterija primjenjivala na isti raspon ćelija (A2: A7), morate ispisati raspon dva puta, jednom po svakom kriteriju.
Više stupaca
Kada koristite više kriterija, možete ih primijeniti na isti raspon kao što smo to učinili u prethodnom primjeru, ili ih možete primijeniti na različite raspone. Kombinirajmo naše uzorke podataka u ovu tablicu:
Postavili smo neke ćelije za korisnika da unese ono što želi pretraživati u ćelijama E2 do G2. Stoga nam je potrebna formula koja će zbrajati ukupan broj ubranih jabuka u veljači. Naša formula izgleda ovako:
= PROSJEČNI (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2)
PROSJEČNI sa logikom tipa ILI
Do sada su svi primjeri koje smo koristili bili usporedba temeljena na I, gdje tražimo retke koji zadovoljavaju sve naše kriterije. Sada ćemo razmotriti slučaj kada želite tražiti mogućnost da red zadovolji jedan ili drugi kriterij.
Pogledajmo ovaj popis prodaje:
Željeli bismo zbrojiti prosječnu prodaju i za Adama i za Boba. Prvo, kratka rasprava o uzimanju prosjeka. Ako imate nejednak broj stvari, na primjer 3 unosa za Adama i 2 za Boba, ne možete jednostavno uzeti prosjek prodaje svake osobe. To je poznato kao uzimanje prosjeka prosjeka, pa na kraju dajete nepošteno ponderiranje stavci koja ima malo unosa. Ako je to slučaj s vašim podacima, morali biste izračunati prosjek na "ručni" način: uzmite zbroj svih svojih stavki podijeljen s brojem vaših stavki. Kako biste pogledali kako to učiniti, možete pogledati članke ovdje:
Sada, ako je broj unosa isti, na primjer u našoj tablici, onda imate nekoliko mogućnosti koje možete učiniti. Najjednostavnije je zbrojiti dva PROSJEČNA zajedno, a zatim podijeliti s 2 (broj stavki na našem popisu)
= (PROSJEČNI (B2: B7, A2: A7, "Adam")+PROSJEČNI (B2: B7, A2: A7, "Bob"))/2
Ovdje smo dobili računalo da izračuna naše pojedinačne rezultate, a zatim ih zbrojimo.
Naša je sljedeća opcija dobra ako imate više raspona kriterija, pa ne želite ponavljati cijelu formulu. U prethodnoj formuli ručno smo rekli računalu da doda dva različita prosječna prosjeka. Međutim, to možete učiniti i tako da svoje kriterije upišete unutar niza, na sljedeći način:
= PROSJEČNO (PROSJEČNO (B2: B7, A2: A7, {"Adam", "Bob"}))
Pogledajte kako je niz konstruiran unutar kovrčavih zagrada. Kad računalo procijeni ovu formulu, znat će da želimo izračunati funkciju AVERAGEIFS za svaku stavku u našem nizu, stvarajući tako niz brojeva. Vanjska funkcija AVERAGE tada će uzeti niz brojeva i pretvoriti ga u jedan broj. Korakom kroz evaluaciju formule to bi izgledalo ovako:
= PROSJEČNO (PROSJEČNO (B2: B7, A2: A7, {"Adam", "Bob"})) = PROSJEČNO (13701, 21735) = 17718
Dobivamo isti rezultat, ali smo formulu mogli sažetije zapisati.
Suočavanje s prazninama
Ponekad će vaš skup podataka imati prazne ćelije koje morate pronaći ili izbjeći. Postavljanje ovih kriterija može biti malo zeznuto, pa pogledajmo drugi primjer.
Imajte na umu da je ćelija A3 doista prazna, dok ćelija A5 ima formulu koja vraća niz "" "nulte duljine. Ako želimo pronaći ukupni prosjek od uistinu prazne ćelije, upotrijebili bismo kriterij “=”, a naša bi formula izgledala ovako:
= PROSJEČNI BROJ (B2: B7, A2: A7, "=")
S druge strane, ako želimo dobiti prosjek za sve ćelije koje vizualno izgledaju prazne, promijenit ćemo kriterij u "", a formula izgleda ovako
= PROSJEČNI BROJ (B2: B7, A2: A7, "")
Okrenimo se: što ako želite pronaći prosjek ćelija koje nisu prazne? Nažalost, trenutni dizajn ne dopušta izbjegavanje niza nulte duljine. Možete koristiti kriterij "", ali kao što vidite u primjeru, on i dalje uključuje vrijednost iz retka 5.
= PROSJEČNI BROJ (B2: B7, A2: A7, "")
Ako ne morate brojati ćelije koje sadrže nizove nulte duljine, razmislite o upotrebi funkcije LEN unutar SUMPRODUCT -a
AVERAGEIF u Google tablicama
Funkcija AVERAGEIF radi potpuno isto u Google tablicama kao i u Excelu:
