Microsoft Excel (pogosto imenovan Excel) je zmogljiva programska oprema za preglednice, zasnovana za shranjevanje velikih nizov podatkov. Poleg tega uporabnikom omogoča organiziranje in analizo podatkov ter celo izvajanje osnovnih do kompleksnih izračunov na posnetih podatkih. Bistvene naloge Excelove analize podatkov vključujejo predvsem izračun ali iskanje srednje vrednosti (ali aritmetične sredine), načina in mediane.
V tej vadnici razpravljamo o različnih metodah po korakih ali rešitvah za izračun srednje vrednosti v Excelu. Preden razpravljamo o metodah, najprej na kratko razumemo koncept Mean in njegove prednosti.
Kaj je povprečje v Excelu?
V Excelu aritmetična sredina ni nič drugega kot povprečno število, ki se običajno izračuna tako, da seštejejo vsa števila danega niza podatkov in nato delijo vsoto s skupnim številom vrednosti (ali številom) danih elementov.
Čeprav je v resničnem življenju enostavno izračunati aritmetično sredino za majhne nize podatkov, je delo z velikimi nizi podatkov težko. Tu pride prav zmogljiv program za preglednice, kot je Excel. Z lahkoto lahko izračuna povprečje stotih do tisočih števil. Poleg tega je manj nagnjen k napakam.
Obstaja veliko bistvenih uporab srednje vrednosti pri analizi podatkov, kot so:
- Za primerjavo zgodovinskih podatkov
- Za poudarjanje KPI-jev (Key Performance Indicators) in citatov
- Za iskanje sredine za dane podatkovne točke
- Določiti vodenje projektov in poslovne načrte
Osnovni scenarij, ko bomo morda morali izračunati povprečje v Excelu, je, ko moramo ugotoviti povprečno prodajo artikla v celem mesecu, četrtletju, letu itd.
Kako izračunati povprečje v Excelu?
Na žalost ni določene funkcije MEAN za izračun ali določitev aritmetične sredine za podatke. Vendar pa obstaja nekaj učinkovitih načinov. Na splošno lahko običajno uporabimo matematični koncept seštevanja vrednosti in nato delitev njihove vsote s številom vrednosti v Excelovih celicah. Poleg tega obstajajo obstoječe funkcije, kot so AVERAGE, AVERAGEA, AVERAGEIF in AVERAGEIFS, ki nam prav tako pomagajo izračunati aritmetično sredino v različnih primerih uporabe.
Da bi bolje razumeli, kako ti koncepti delujejo pri izračunu aritmetične sredine, razpravljajmo o vsakem posebej s primerom:
Izračun srednje vrednosti v Excelu: splošna metoda
Uporabimo lahko funkciji SUM in COUNT v Excelovem listu, da uporabimo pristop seštevanja vrednosti in njihovega deljenja s številom vključenih vrednosti.
Denimo, da smo podali števila od 1 do 5 in moramo izračunati aritmetično sredino. Matematično najprej seštejemo vsa ta števila, tj. (1+2+3+4+5). Nato njihovo SUM delimo s 5, ker je to skupno število danih števil.
2 proti 1 multiplekser
Tako celotna formula za izračun srednje vrednosti postane naslednja:
(1+2+3+4+5)/ 5
To vrne aritmetično sredino 3.
V Excelu lahko uporabimo zgornjo matematično formulo tako, da jo združimo s funkcijama SUM in COUNT na naslednji način:
=SUM(1,2,3,4,5)/ŠTEV(1,2,3,4,5)
Tukaj funkcija SUM sešteje dane vrednosti, medtem ko funkcija COUNT določi število danih vrednosti.
Zdaj, če posnamemo teh pet števil v območju A1:A5, bo spodnja formula v celici A6 izračunala aritmetično sredino:
=SUM(A1:A5)/ŠTEV(A1:A5)
Rezultat (ali povprečje), dobljen na zgornji sliki, je enak tistemu, ki smo ga izračunali iz matematične formule, tj. 3.
Izračun srednje vrednosti v Excelu: funkcija AVERAGE
Ker je Mean povprečje, je Excelova funkcija AVERAGE najučinkovitejši način za izračun aritmetične sredine. Je ena najpogosteje uporabljenih statističnih funkcij za izračun povprečja za določen obseg, če ni posebnih pogojev ali omejitev. Je vgrajena funkcija Excel.
Sintaksa
kmp algoritem
Sintaksa Excelove funkcije AVERAGE je naslednja:
=AVERAGE(število1, [število2], ...)
Število1 je zahtevani argument, kar pomeni, da je en argument obvezen v funkciji AVERAGE. Vendar pa lahko posredujemo več argumentov v različnih oblikah, kot so števila, celice, obsegi, nizi, konstante itd. Pri izračunu aritmetične sredine moramo v funkciji AVERAGE posredovati vsaj dva argumenta.
Koraki za izračun srednje vrednosti s funkcijo AVERAGE
Razumejmo zdaj delovanje funkcije AVERAGE in izračunajmo aritmetično sredino za skupino števil na Excelovem listu z uporabo vzorčnih podatkov.
Recimo, da na Excelovem listu navedemo nekaj sadja z ustreznimi cenami. Imena sadja so zapisana v stolpcu A (A2:A9), njihove cene pa v naslednjem stolpcu B (B2:B9). Poiskati ali izračunati moramo povprečno ceno (ali aritmetično sredino cen) v celici B10.
Za izračun aritmetične sredine v našem primeru delovnega lista moramo izvesti naslednje korake:
- Najprej moramo izbrati ali označiti celico za zapis nastale aritmetične sredine, tj. celico B10.
- Nato moramo vstaviti ime funkcije, ki se začne z znakom enačaja, in zagotoviti potrebne argumente v funkciji. V našem primeru vnesemo enačaj (=) in funkcijo AVERAGE. Funkcijo lahko izberemo tudi s seznama funkcij, ki jih predlaga Excel.
- Po vnosu imena funkcije moramo vnesti začetni oklepaj in podati naš efektivni obseg nizov podatkov. Ker imamo podatke v sosednjih celicah ali obseg (od B2 do B9), lahko celoten obseg posredujemo v funkciji na naslednji način:
- Nazadnje moramo vnesti zaključni oklepaj in pritisniti Vnesite na tipkovnici, da dobite efektivni rezultat ali aritmetično sredino za dani niz podatkov. Spodnja slika prikazuje aritmetično sredino za cene v celicah od B2 do B9:
Na zgornji sliki smo uporabili sosednje celice in naenkrat posredovali celoten obseg nizov podatkov. Vendar pa lahko povprečje izračunamo tudi za nesosednje celice v našem Excelovem listu tako, da celice vnesemo posamično.
Recimo, da moramo izračunati samo aritmetično sredino cen samo za tri sadeže: Jabolko, mango in grozdje. V tem primeru lahko vsako celico posebej posredujemo v funkciji na naslednji način:
=POVPREČNO(B2,B3,B6)
Tu so B2, B3 in B6 posamezne celice s cenami jabolk, manga in grozdja.
Čeprav lahko funkcijo AVERAGE uporabljate s praznimi celicami, besedili, ničlami, logičnimi vrednostmi in celo mešanimi argumenti, med izračunom prezre prazne celice, besedilo in logične vrednosti. Vendar so celice z ničelno vrednostjo vključene v funkcijo AVERAGE.
Izračun srednje vrednosti v Excelu: funkcija AVERAGEA
Funkcija AVERAGEA je nekoliko naprednejša različica funkcije AVERAGE. Funkcija AVERAGEA nam pomaga izračunati tudi aritmetično sredino za podane nize podatkov in deluje v vseh različicah Excela. Ena od prednosti funkcije AVERAGEA je, da lahko med izračunom povprečja v Excelu vključuje besedilne vrednosti in tipe logičnih podatkov. Besedilne vrednosti so ovrednotene kot ničle, medtem ko sta logični vrednosti TRUE in FALSE ovrednoteni kot 1 oziroma 0.
Sintaksa
Sintaksa Excelove funkcije AVERAGEA je naslednja:
=AVERAGEA(vrednost1, [vrednost2], ...)
Tako kot funkcija AVERAGE je prvi argument v funkciji AVERAGEA obvezen. Vendar pa lahko po potrebi uporabimo več argumentov.
Sintaksa funkcij AVERAGE in AVERAGEA je skoraj podobna; vendar je njihov koncept dela nekoliko drugačen. Funkcija AVERAGE lahko izračuna samo aritmetično sredino za številske vrednosti, medtem ko lahko funkcija AVERAGEA izračuna enako za katero koli vrsto podatkov, vključno z besedilom in logičnimi vrednostmi.
css krepko besedilo
Koraki za izračun srednje vrednosti s funkcijo AVERAGEA
Ponovno vzemimo prejšnje primere nizov podatkov, kjer imamo imena sadja v stolpcu A (A2:A9) in njihove cene v stolpcu B (B2:B9). Vendar pa tokrat nimamo cene za grozdje (zabeleženo kot 'Ni na voljo' v ustrezni celici B6), kot je prikazano spodaj:
Izvedemo lahko spodnje korake in izračunamo srednjo vrednost s funkcijo AVERAGEA:
- Tako kot pri prejšnji metodi najprej izberemo nastalo celico B10, da zabeležimo povprečje. Nato vtipkamo enačaj (=), vnesemo ali izberemo našo funkcijo AVERAGEA in posredujemo celoten obseg podatkov funkciji v oklepaju.
- Nazadnje moramo pritisniti Vnesite ključ za pridobitev učinkovitega rezultata (ali povprečja).
Zgornja slika vrne srednjo vrednost 57,125 s funkcijo AVERAGEA.
Če v tem primeru uporabimo funkcijo AVERAGE in izračunamo aritmetično sredino, opazimo ogromno razliko med rezultati, dobljenimi z obema funkcijama. Povprečni rezultat 65,285 se prikaže s funkcijo AVERAGE, kot je prikazano spodaj:
Razlike v aritmetični sredini v tem primeru so smiselne, ker je številka deljenja različna za obe funkciji. Število številk za funkcijo AVERAGEA je 8, saj ovrednoti tudi TEXT kot veljaven podatek. Toda za funkcijo AVERAGE je števec deljenja 7, ker v svoj izračun ne vključuje podatkov TEXT.
Če nabor podatkov vsebuje nič (0), se bo namesto podatkov TEXT pojavil isti rezultat (ali aritmetična sredina) za obe funkciji (AVERAGE in AVERAGEA), ker obe vključujeta nič kot veljaven podatek v izračunu.
Izračun srednje vrednosti v Excelu: funkcija AVERAGEIF
Funkcija AVERAGEIF je ustrezna funkcija funkcije AVERAGE in nam pomaga izračunati aritmetično sredino z danim pogojem ali kriterijem. S funkcijo AVERAGEIF lahko izračunamo povprečje le za določena števila, ki sledijo določenim kriterijem. Excel najprej ovrednoti določena merila, poišče učinkovite celice po teh merilih in nato ustrezno izračuna povprečje samo za locirane celice.
Sintaksa
Sintaksa Excelove funkcije AVERAGEIF je naslednja:
=AVERAGEAIF(razpon, kriteriji, [povprečni_razpon])
Tukaj sta obseg in kriterij zahtevana argumenta, medtem ko je povprečni_obseg neobvezen.
- Obseg vsebuje celice, ki jih je treba preizkusiti glede na podana merila.
- Merilo se nanaša na argument, kjer lahko določimo želeni pogoj, ki mu je treba slediti. Vključuje lahko besedilo, številske vrednosti, sklic na celice, obseg in logični izraz.
- Povprečni_obseg je argument, kjer omenjamo celice (ali obseg), za katere želimo izračunati povprečje. Če ta argument ni podan, Excel samodejno uporabi obseg.
Koraki za izračun srednje vrednosti s funkcijo AVERAGEIF
V istem primeru predpostavimo, da nekaj sadja večkrat navedemo z različnimi cenami. Tokrat moramo samo izračunati povprečje za določeno ponavljajoče se sadje (tj. jabolko) v celici B11. Torej uporabimo funkcijo AVERAGEIF in uporabimo pogoj pri izračunu povprečja. Za izračun srednje cene za Apple moramo izvesti spodnje korake:
- Najprej izberemo nastalo celico (B11) in vstavimo formulo AVERAGEIF na naslednji način:
=AVERAGEIF(A2:B9, 'Apple', B2:B9)
Tukaj A2:B9 predstavlja efektivni obseg, Apple predstavlja naša želena merila/pogoj in B2:B9 predstavlja povprečni_razpon.
Tu smo ročno vnesli izbirni povprečni_razpon, da vključimo samo celice s cenami. Če v našem primeru ne posredujemo povprečnega_razpona, bo funkcija uporabila primarni obseg (A2:B9) z besedilnimi podatki in povzročila #DIV! napaka.
Opomba: Upoštevati je treba, da funkcija AVERAGEIF prezre prazne celice v svojem argumentu povprečni_obseg. Poleg tega, če je katera koli celica, določena v merilih, prazna, jo funkcija obravnava kot celico z ničelno vrednostjo.
Izračun srednje vrednosti v Excelu: funkcija AVERAGEIFS
java typeof spremenljivka
Funkcija AVERAGEIFS je razširjena ali napredna različica funkcije AVERAGEIF. Funkcija AVERAGEIF nam omogoča uporabo le enega pogoja naenkrat. Vendar pa lahko s funkcijo AVERAGEIFS uporabimo več meril, ki jih je treba testirati hkrati. Poleg tega nam funkcija AVERAGEIFS omogoča uporabo celic z logičnimi vrednostmi TRUE in FALSE, kar nam pomaga enostavno preizkusiti več meril. To tudi ni mogoče s funkcijo AVERAGEIF.
Sintaksa
Sintaksa Excelove funkcije AVERAGEIFS je naslednja:
=AVERAGEAIFS(povprečni_razpon, kriterij1_razpon1, kriterij1, [kriterij2_obseg2, kriterij2], ...)
Tukaj so obvezni argumenti povprečni_obseg, merilo1_obseg1 in merilo1. Lahko pa podamo neobvezne argumente na podlagi naših učinkovitih podatkovnih nizov. Poleg tega se kriterij1 nanaša na pogoj, ki ga je treba preizkusiti na obsegu1, kriterij2 se nanaša na pogoj, ki ga je treba preizkusiti na obsegu2, in več.
Koraki za izračun srednje vrednosti s funkcijo AVERAGEIFS
Oglejmo si iste primere podatkov, ki smo jih uporabili v prejšnji metodi. V prejšnji metodi smo uporabili funkcijo AVERAGEIF in izračunali povprečje za cene ponovljenega sadja (jabolka). V tem primeru smo imeli le en pogoj.
Zdaj pa predpostavimo, da moramo izračunati povprečje za cene Appla, vendar vključiti samo tiste celice, kjer so cene nad 40.
V tem primeru imamo dva različna merila, zato funkcije AVERAGEIF ni mogoče uporabiti.
Merilo 1: V obsegu A2:A9 je prvotno merilo preverjanje »Apple«, ki ga izpolnjujejo tri celice (ali vrstice)
Merilo 2: V razponu B2:B9 je drugo merilo preverjanje cen nad 40 (>40), ki jih izpolnjujeta dve od treh celic (ali vrstica) s sadnim jabolkom
nat proti postelji
Če torej matematično izračunamo povprečje, uporabimo spodnjo formulo:
(95+68)/2
To vrne srednjo vrednost 81.5. Preverimo, ali dobimo enake rezultate s funkcijo AVERAGEIFS v Excelu.
Ko moramo preizkusiti pogoje v Excelu, si običajno pomagamo z logičnim operatorjem ALI. Operator OR vrne TRUE in FALSE, odvisno od tega, ali dani pogoj izpolnjuje. Denimo, da moramo preveriti, ali celica A2 vsebuje Apple. V tem primeru lahko uporabimo operator OR takole:
=ALI(A2='Apple')
Zgornji pogoj vstavimo v naš vzorčni list, da preizkusimo ali poiščemo celice s sadnim jabolkom. Torej ustvarimo nov stolpec (C) in vnesemo določen pogoj v celico C2. Nato isti pogoj kopiramo v preostale celice stolpca C. Videti je takole:
Zgornja slika prikazuje, da operator OR vrne TRUE za celice, ki ustrezajo sadju Apple, medtem ko za druge vrne FALSE. Na podlagi tega rezultata zdaj uporabimo funkcijo AVERAGEIFS in vstavimo več pogojev za celice vrednosti TRUE na naslednji način:
- Kot pri drugih metodah tudi tukaj najprej izberemo nastalo celico (tj. B12).
- Nato vnesemo formulo AVERAGEIFS na naslednji način:
=AVERAGEIFS(B2:B9;C2:C9;'TRUE';B2:B9;'>40')
Z zgornjo formulo AVERAGEIFS smo vključili samo sadje 'Apple' in cene nad 40. - Nazadnje moramo pritisniti Vnesite ključ za pridobitev rezultata formule. Za naš nabor podatkov je formula vrnila aritmetično sredino 81,5, enako kot naš matematično izračunan rezultat.
Posebni primeri
Čeprav imamo pri izračunu aritmetične sredine v Excelu lahko različne scenarije, sta tukaj obravnavana dva najpogostejša. V teh primerih moramo biti pri izračunu aritmetične sredine previdni.
Izračun povprečja vključno z ničlami
Ko moramo izračunati povprečje z ničlami, lahko običajno uporabimo funkcijo AVERAGE in zagotovimo efektivne celice, vključno s celicami z ničelno vrednostjo. Funkcija AVERAGE v svojem izračunu vključuje ničle kot veljavna števila.
V naslednjem vzorčnem naboru podatkov uporabimo funkcijo AVERAGE za izračun povprečja za dve različni oceni učencev:
Zgornja slika prikazuje različne srednje vrednosti za oba učenca. Če pogledamo ocene, je seštevek ocen pri obeh učencih enak. Drugi učenec ima pri enem predmetu nič (celica z ničelno vrednostjo), medtem ko je prvi učenec pri enem predmetu odsoten (celica z besedilno vrednostjo).
Ker funkcija AVERAGE ignorira besedilne vrednosti, vendar vključuje ničelne vrednosti, se število deljenj razlikuje za oba učenca. Za prvega učenca je razdeljevalec 4, za drugega pa 5. Tako obstaja razlika v srednjih rezultatih. Vendar pa lahko ničelne vrednosti po potrebi tudi izrecno izključimo.
Izračun srednje vrednosti brez ničel
Ko moramo izračunati povprečje brez ničel, moramo izkoristiti Excelovo funkcijo AVERAGEIF. V tej funkciji lahko uporabimo poseben pogoj, ki Excelu naroči, naj med izračunom ne vključi celic z ničelno vrednostjo.
Spodnjo formulo AVERAGEIF lahko uporabite za izključitev ničelnih vrednosti pri izračunu srednje vrednosti v Excelu:
=AVERAGEIF(celica ali obseg,'>0')
Če vstavimo zgornjo formulo za naše vzorčne podatke, se za oba učenca prikažejo enaki rezultati srednje vrednosti.
Zgornja formula AVERAGEIF prezre besedilo in celice z ničelno vrednostjo. Število delilnikov je torej enako (tj. 4), prav tako pa tudi aritmetična sredina za ocene obeh učencev.
Pomembne točke, ki si jih morate zapomniti
- Pomembno je vedeti, da lahko katera koli funkcija v Excelu, vključno z AVERAGE, AVERAGEA, AVERAGEIF in AVERAGEIFS, sprejme največ 255 argumentov.
- Ročno vnašanje imena funkcije v Excelovo celico ni priporočljivo. Če izberete želeno funkcijo s seznama predlogov, je bolje, da se izognete napačno črkovanim imenom funkcij in odpravite napake v rezultatih.