AVERAGEIF
Vrne aritmetično sredino vseh celic v obsegu, ki ustrezajo danemu pogoju. Funkcija AVERAGEIF sešteje vse rezultate, ki se ujemajo z logičnim preizkusom, in deli to vsoto s številom izbranih vrednosti.
AVERAGEIF(obseg;pogoj[;obseg_povprečja])
Obseg – obvezen argument. Matrika, ime imenovanega obsega ali oznaka stolpca ali vrstice, ki vsebuje števila za povprečenje oz. števila ali besedilo za pogoj.
Iskanje podpira regularne izraze. Če so ti omogočeni, lahko vnesete npr. »vse.*«, da najdete prvo mesto, kjer se nahaja »vse«, in kateremu sledi karkoli. Če želite iskati besedilo, ki je tudi regularen izraz, morate pred vsak meta-znak regularnega izraza ali operator vnesi znak »\« ali pa besedilo zaobjeti v \Q...\E. Samodejno vrednotenje nadomestnih znakov ali regularnega izraza lahko vklopite in izklopite v .
Če uporabljate funkcije, v katerih je vsaj eden argument iskalni pogoj, ki predstavlja regularni izraz, se bo niz pogojev najprej poskusil pretvoriti v števila. Primer: ».0« se pretvori v 0.0 itn. Če pretvorba uspe, iskanje ne bo vrnilo regularnega izraza, temveč število. Če preklopite na slovenske krajevne nastavitve, kjer decimalno ločilo ni pika, temveč vejica, pa pretvorba regularnega izraza deluje. Če želite vsiliti iskanje regularnega izraza namesto števila, uporabite izraz, ki ga ni mogoče pretvoriti v numeričnega, npr. ».[0]«, ».\0« ali »(?i).0«.
Obseg_povprečja – neobvezno. To je obseg vrednosti za izračun srednje vrednosti oz. povprečja.
Če obseg_povprečja ni določen, se uporabi obseg tako za izračun srednje vrednosti kot za iskanje, skladno s pogojem. Če je obseg_povprečja določen, se obseg uporabi le za preizkus izpolnjevanja pogoja, za izračun srednje vrednosti pa se uporabi obseg_povprečja.
Ta funkcija je del standarda Open Document Format for Office Applications (OpenDocument) različice 1.2 (ISO/IEC 26300:2-2015).
Če je celica v obsegu vrednosti za izračun srednje vrednosti prazna ali vsebuje besedilo, funkcija AVERAGEIF to celico prezre.
Če je celoten obseg prazen, vsebuje samo besedilo ali če nobena od vrednosti iz obsega ne ustreza pogoju (ali katera koli kombinacija teh možnosti), funkcija vrne napako #DIV/0!.
Oglejte si naslednjo tabelo
|
A |
B |
C |
|
|---|---|---|---|
|
1 |
Ime izdelka |
Prodaja |
Prihodek |
|
2 |
sirotka |
20 |
65 |
|
3 |
sir |
35 |
85 |
|
4 |
sladoled |
20 |
190 |
|
5 |
led |
17 |
180 |
|
6 |
sirotka-kozja |
ni |
ni |
V vseh spodnjih primerih vsebujejo obsegi za izračun vrstico številka 6, vendar je prezrta, ker vsebuje besedilo.
Enostavna raba
=AVERAGEIF(B2:B6;"<35")
Izračuna povprečje za vrednosti obsega B2:B6, manjše od 35. Vrne 19, ker druga vrstica ni upoštevana v izračunu.
=AVERAGEIF(B2:B6;"<"&MAX(B2:B6))
Izračuna povprečje za vrednosti v istem obsegu, ki so manjše od največje vrednosti tega obsega. Vrne 19, ker največja vrednost (druga vrstica) ni del izračuna.
=AVERAGEIF(B2:B6;">"&SMALL(B2:B6;1))
Izračuna povprečje za vrednosti istega obsega, ki so večje od prve najmanjše vrednosti tega obsega. Vrne 25, saj prva najmanjša vrednost (četrta vrstica) ni del izračuna.
Uporaba obsega povprečja
=AVERAGEIF(B2:B6;"<35";C2:C6)
Funkcija poišče vrednosti, manjše od 35, ki se nahajajo v obsegu B2:B6, ter izračuna povprečje pripadajočih vrednosti iz obsega C2:C6. Vrne 145, ker izračun ne zajema druge vrstice.
=AVERAGEIF(B2:B6;">"&MIN(B2:B6);C2:C6)
Funkcija poišče vrednosti iz obsega B2:B6, ki so večje od najmanjše vrednosti v obsegu B2:B6, in izračuna povprečje ustreznih vrednosti iz obsega C2:C6. Vrne 113,3, ker izračun ne zajema četrte vrstice (v kateri je najmanjša vrednost v obsegu B2:B6).
=AVERAGEIF(B2:B6;"<"&LARGE(B2:B6;2);C2:C6)
Funkcija poišče vrednosti v obsegu B2:B6, ki so manjše od druge največje vrednosti v obsegu B2:B6, ter izračuna povprečje pripadajočih vrednosti iz obsega C2:C6. Vrne 180, ker izračun zajema le četrto vrstico.
Uporaba regularnih izrazov
=AVERAGEIF(A2:A6;"sir";B2:B6)
Funkcija poišče celice v obsegu A2:A6, ki vsebujejo le besedo »sir«, ter izračuna povprečje pripadajočih vrednosti iz obsega B2:B6. Vrne 35, ker izračun zajema le drugo vrstico. Iskanje se izvede v obsegu A2:A6, vrnjene vrednosti pa so iz obsega B2:B6.
=AVERAGEIF(A2:A6;"sir.*";B2:B6)
Funkcija poišče celice v obsegu A2:A6, ki se začnejo s »sir« in končajo s poljubnim številom drugih znakov, ter izračuna povprečje ustreznih vrednosti iz obsega B2:B6. Vrne 27,5, ker zdaj tudi »sirotka« ustreza pogoju, zato pri izračunu upošteva prvo in drugo vrstico.
=AVERAGEIF(A2:A6;".*led.*";B2:B6)
Funkcija poišče celice v obsegu A2:A6, ki vsebujejo »led« in se začnejo ali končajo s poljubnim številom drugih znakov, ter izračuna povprečje ustreznih vrednosti iz obsega B2:B6. Vrne 18,5, saj pri izračunu upošteva le tretjo in četrto vrstico.
Sklic na celico kot pogoj
Če želite enostavno spremeniti pogoj, ga lahko določite v ločeni celici in uporabite sklic na to celico v pogoju funkcije AVERAGEIF.
=AVERAGEIF(A2:A6;".*"&E2&".*";B2:B6)
Funkcija poišče celice v obsegu A2:A6, ki vsebujejo kombinacijo znakov iz E2 in ki se začnejo in končajo s poljubnim številom drugih znakov, ter izračuna povprečje ustreznih vrednosti iz obsega B2:B6. Če je E2 = led, funkcija vrne 18,5.
=AVERAGEIF(B2:B6;"<"&E2;C2:C6)
Funkcija poišče celice v obsegu B2:B6, katerih vrednost je manjša od vrednosti, navedene v E2, ter izračuna povprečje ustreznih vrednosti iz obsega C2:C6. Če je E2 enako 35, funkcija vrne 145.