Microsoft Excel (često se naziva i Excel) moćan je softver za proračunske tablice dizajniran za pohranu velikih skupova podataka. Također, omogućuje korisnicima organiziranje i analizu podataka, pa čak i izvođenje osnovnih do složenih izračuna na snimljenim podacima. Osnovni zadaci analize podataka programa Excel uglavnom uključuju izračunavanje ili pronalaženje srednje vrijednosti (ili aritmetičke sredine), modusa i medijana.
U ovom vodiču raspravljamo o raznim metodama korak po korak ili rješenjima kako izračunati srednju vrijednost u Excelu. Prije rasprave o metodama, prvo ćemo ukratko razumjeti koncept srednje vrijednosti i njegove prednosti.
Što je srednja vrijednost u Excelu?
U Excelu aritmetička sredina nije ništa drugo nego prosječni broj koji se obično izračunava zbrajanjem svih brojeva zadanog skupa podataka i zatim dijeljenjem zbroja s ukupnim brojem vrijednosti (ili brojem) zadanih stavki.
Iako je lako izračunati aritmetičku sredinu za male skupove podataka u stvarnom životu, postaje teško raditi s velikim skupovima podataka. Tu moćan program za proračunske tablice kao što je Excel dobro dolazi. Može lako izračunati srednju vrijednost stotina do tisuća brojeva. Također, manje je sklon greškama.
Postoje mnoge bitne upotrebe srednje vrijednosti u analizi podataka, kao što su:
- Za usporedbu povijesnih podataka
- Za isticanje KPI-jeva (Key Performance Indicators) i citata
- Da biste pronašli središnju točku za dane podatkovne točke
- Odrediti upravljanje projektima i poslovne planove
Osnovni scenarij kada trebamo izračunati srednju vrijednost u Excelu je kada trebamo saznati prosječnu prodaju artikla u cijelom mjesecu, kvartalu, godini itd.
Kako izračunati srednju vrijednost u Excelu?
Nažalost, ne postoji definitivna funkcija MEAN za izračunavanje ili određivanje aritmetičke sredine za podatke. Međutim, postoji nekoliko učinkovitih načina. Općenito, obično možemo primijeniti matematički koncept zbrajanja vrijednosti i zatim dijeljenja njihovog zbroja s brojem vrijednosti unutar ćelija programa Excel. Nadalje, postoje postojeće funkcije kao što su AVERAGE, AVERAGEA, AVERAGEIF i AVERAGEIFS, koje nam također pomažu izračunati aritmetičku sredinu u različitim slučajevima upotrebe.
Da bismo dobili bolju predodžbu o tome kako ovi koncepti funkcioniraju za izračunavanje aritmetičke sredine, razmotrimo svaki pojedinačno na primjeru:
Izračunavanje srednje vrijednosti u Excelu: Opća metoda
Možemo primijeniti funkcije SUM i COUNT u Excel tablici kako bismo koristili pristup zbrajanja vrijednosti i njihovog dijeljenja s brojem uključenih vrijednosti.
Na primjer, pretpostavimo da smo dali brojeve od 1 do 5 i trebamo izračunati aritmetičku sredinu. Matematički, prvo zbrojimo sve te brojeve, tj. (1+2+3+4+5). Nakon toga, njihov SUM dijelimo s 5 jer je to ukupan broj zadanih brojeva.
Stoga cijela formula za izračunavanje srednje vrijednosti postaje sljedeća:
(1+2+3+4+5)/ 5
Ovo vraća aritmetičku sredinu 3.
grupiranje
U Excelu možemo koristiti gornju matematičku formulu kombinirajući je s funkcijama SUM i COUNT na sljedeći način:
=SUM(1,2,3,4,5)/BROJ(1,2,3,4,5)
Ovdje funkcija SUM zbraja zadane vrijednosti, dok funkcija COUNT određuje broj zadanih vrijednosti.
Sada, ako zabilježimo ovih pet brojeva u rasponu A1:A5, donja formula u ćeliji A6 izračunat će aritmetičku sredinu:
=SUM(A1:A5)/BROJ(A1:A5)
Rezultat (ili srednja vrijednost) dobiven na gornjoj slici isti je kao onaj koji smo izračunali iz matematičke formule, tj. 3.
Izračunavanje srednje vrijednosti u Excelu: funkcija AVERAGE
Budući da je srednja vrijednost prosjek, Excelova funkcija AVERAGE je najučinkovitiji način za izračunavanje aritmetičke sredine. To je jedna od najčešće korištenih statističkih funkcija za izračunavanje srednje vrijednosti za određeni raspon ako nema posebnih uvjeta ili ograničenja. To je ugrađena Excel funkcija.
Sintaksa
Sintaksa Excelove funkcije AVERAGE je sljedeća:
=PROSJEK(broj1, [broj2], ...)
Broj1 je obavezni argument, što znači da je jedan argument obavezan u funkciji AVERAGE. Međutim, možemo navesti više argumenata u različitim oblicima kao što su brojevi, ćelije, rasponi, nizovi, konstante itd. Prilikom izračunavanja aritmetičke sredine, trebali bismo navesti najmanje dva argumenta u funkciji AVERAGE.
Koraci za izračunavanje srednje vrijednosti pomoću funkcije AVERAGE
Razumimo sada kako radi funkcija AVERAGE i izračunajmo aritmetičku sredinu za grupu brojeva u Excel tablici pomoću uzorka podataka.
Pretpostavimo da smo naveli neko voće u Excel listu s pripadajućim cijenama. Nazivi voća bilježe se u stupac A (A2:A9), a njihove cijene u sljedeći stupac B (B2:B9). Trebamo pronaći ili izračunati prosječnu cijenu (ili aritmetičku sredinu za cijene) u ćeliji B10.
markdown slika
Moramo izvršiti korake u nastavku kako bismo izračunali aritmetičku sredinu u našem oglednom radnom listu:
- Prvo moramo odabrati ili označiti ćeliju za snimanje rezultantne aritmetičke sredine, tj. ćeliju B10.
- Zatim moramo umetnuti naziv funkcije koji počinje znakom jednakosti i unijeti potrebne argumente u funkciju. U našem slučaju upisujemo znak jednakosti (=) i funkciju AVERAGE. Također možemo odabrati funkciju s popisa funkcija koje predlaže Excel.
- Nakon unosa naziva funkcije, moramo unijeti početnu zagradu i dati naš efektivni raspon skupova podataka. Budući da podatke imamo u susjednim ćelijama ili rasponu (od B2 do B9), cijeli raspon možemo proslijediti u funkciju na sljedeći način:
- Na kraju, moramo unijeti završnu zagradu i pritisnuti Unesi tipku na tipkovnici za dobivanje efektivnog rezultata ili aritmetičke sredine za dani skup podataka. Donja slika prikazuje aritmetičku sredinu za cijene u ćelijama od B2 do B9:
Na gornjoj slici upotrijebili smo susjedne ćelije i isporučili cijeli raspon skupova podataka u jednom potezu. Međutim, također možemo izračunati srednju vrijednost za nesusjedne ćelije u našem Excel listu tako da unesemo ćelije pojedinačno.
Pretpostavimo da samo trebamo izračunati aritmetičku sredinu cijena za samo tri vrste voća: Jabuka, mango i grožđe. U tom slučaju svaku ćeliju zasebno možemo proslijediti u funkciju na sljedeći način:
=PROSJEK(B2,B3,B6)
Ovdje su B2, B3 i B6 pojedinačne ćelije s cijenama jabuke, manga i grožđa.
Iako se funkcija AVERAGE može koristiti s praznim ćelijama, tekstovima, nulama, logičkim vrijednostima, pa čak i miješanim argumentima, ona tijekom izračuna zanemaruje prazne ćelije, tekst i logičke vrijednosti. Međutim, ćelije s nultom vrijednošću uključene su u funkciju AVERAGE.
Izračunavanje srednje vrijednosti u Excelu: funkcija AVERAGEA
Funkcija AVERAGEA malo je naprednija verzija funkcije AVERAGE. Funkcija AVERAGEA također nam pomaže izračunati aritmetičku sredinu za zadane skupove podataka i radi u svim verzijama Excela. Jedna od prednosti funkcije AVERAGEA je ta što može uključiti tekstualne vrijednosti i tipove Booleovih podataka dok izračunava srednju vrijednost u Excelu. Tekstualne vrijednosti se vrednuju kao nule, dok se Booleove vrijednosti TRUE i FALSE vrednuju kao 1 odnosno 0.
foreach tipkalopis
Sintaksa
Sintaksa Excelove funkcije AVERAGEA je sljedeća:
=PROSJEČNOA(vrijednost1, [vrijednost2], ...)
Kao i funkcija AVERAGE, prvi argument u funkciji AVERAGEA je obavezan. Međutim, po potrebi možemo koristiti više argumenata.
Sintaksa funkcija AVERAGE i AVERAGEA gotovo je slična; međutim, njihov koncept rada je malo drugačiji. Funkcija AVERAGE može izračunati samo aritmetičku sredinu za numeričke vrijednosti, dok funkcija AVERAGEA može izračunati isto za bilo koju vrstu podataka, uključujući tekst i Booleove vrijednosti.
Koraci za izračunavanje srednje vrijednosti pomoću funkcije AVERAGEA
Uzmimo ponovno prethodni primjer skupova podataka gdje imamo nazive voća u stupcu A (A2:A9) i njihove cijene u stupcu B (B2:B9). Međutim, ovaj put nemamo cijenu za grožđe (zabilježeno kao 'Nije dostupno' u odgovarajućoj ćeliji B6), kao što je prikazano u nastavku:
Možemo izvršiti korake u nastavku i izračunati srednju vrijednost pomoću funkcije AVERAGEA:
- Kao i kod prethodne metode, najprije odabiremo rezultirajuću ćeliju B10 da zabilježimo srednju vrijednost. Zatim upisujemo znak jednakosti (=), unosimo ili odabiremo našu funkciju AVERAGEA i prosljeđujemo cijeli raspon podataka funkciji unutar zagrade.
- Na kraju, moramo pritisnuti Unesi ključ za dobivanje efektivnog izlaza (ili srednje vrijednosti).
Gornja slika vraća srednju vrijednost od 57,125 s funkcijom AVERAGEA.
Ako u ovom primjeru koristimo funkciju AVERAGE i izračunamo aritmetičku sredinu, vidjet ćemo veliku razliku između rezultata dobivenih objema funkcijama. Prosječna izlazna vrijednost od 65,285 pojavljuje se s funkcijom AVERAGE, kao što je prikazano u nastavku:
Razlike u aritmetičkoj sredini u ovom primjeru imaju smisla jer je broj dijeljenja različit za obje ove funkcije. Broj brojeva za funkciju AVERAGEA je 8, budući da ona također procjenjuje TEXT kao valjane podatke. Ali za funkciju AVERAGE broj dijeljenja je 7 budući da ne uključuje podatke TEXT u svoj izračun.
Umjesto TEXT podataka, ako skup podataka sadrži nulu (0), pojavit će se isti rezultat (ili aritmetička sredina) za obje funkcije (AVERAGE i AVERAGEA) jer obje uključuju nulu kao važeće podatke u izračunu.
Izračunavanje srednje vrijednosti u Excelu: funkcija AVERAGEIF
Funkcija AVERAGEIF relevantna je funkcija funkcije AVERAGE i pomaže nam izračunati aritmetičku sredinu s danim uvjetom ili kriterijem. Pomoću funkcije AVERAGEIF možemo izračunati srednju vrijednost samo za određene brojeve koji slijede navedene kriterije. Excel prvo procjenjuje specifične kriterije, locira efektivne ćelije prema tim kriterijima i zatim u skladu s tim izračunava srednju vrijednost samo za locirane ćelije.
Sintaksa
Sintaksa Excelove funkcije AVERAGEIF je sljedeća:
=AVERAGEAIF(raspon, kriteriji, [prosječni_raspon])
Ovdje su raspon i kriterij obavezni argumenti, dok je prosječni_raspon opcionalan.
- Raspon sadrži ćelije koje se testiraju prema zadanim kriterijima.
- Kriterij se odnosi na argument u kojem možemo navesti željeni uvjet koji treba slijediti. Može uključivati tekst, numeričke vrijednosti, referencu ćelije, raspon i logički izraz.
- Prosječna_raspon je argument u kojem spominjemo ćelije (ili raspon) za koje želimo izračunati srednju vrijednost. Ako ovaj argument nije naveden, Excel automatski koristi raspon.
Koraci za izračunavanje srednje vrijednosti pomoću funkcije AVERAGEIF
U istom primjeru, pretpostavimo da neko voće navodimo više puta s različitim cijenama. Ovaj put trebamo samo izračunati srednju vrijednost za bilo koje određeno ponovljeno voće (tj. jabuku) u ćeliji B11. Dakle, koristimo funkciju AVERAGEIF i primjenjujemo uvjet dok izračunavamo srednju vrijednost. Moramo izvršiti korake u nastavku kako bismo izračunali srednju cijenu za Apple:
- Prvo odabiremo rezultantnu ćeliju (B11) i umećemo formulu AVERAGEIF na sljedeći način:
=AVERAGEIF(A2:B9, 'Apple', B2:B9)
Ovdje A2:B9 predstavlja efektivni raspon, Apple predstavlja naše željene kriterije/uvjete, a B2:B9 predstavlja prosječni_raspon.
Ovdje smo ručno unijeli izborni prosječni_raspon kako bismo uključili samo ćelije s cijenama. U našem primjeru, ako ne proslijedimo prosječni_raspon, funkcija će koristiti primarni raspon (A2:B9) s tekstualnim podacima i izazvati #DIV! greška.
Napomena: bitno je napomenuti da funkcija AVERAGEIF zanemaruje prazne ćelije u svom argumentu prosječni_raspon. Osim toga, ako je bilo koja ćelija navedena u kriterijima prazna, funkcija je tretira kao ćeliju nulte vrijednosti.
Izračunavanje srednje vrijednosti u Excelu: funkcija AVERAGEIFS
Funkcija AVERAGEIFS je proširena ili napredna verzija funkcije AVERAGEIF. Funkcija AVERAGEIF omogućuje nam da primijenimo samo jedan po jedan uvjet. Međutim, s funkcijom AVERAGEIFS možemo primijeniti više kriterija koji se testiraju istovremeno. Osim toga, funkcija AVERAGEIFS omogućuje nam upotrebu ćelija s Booleovim vrijednostima TRUE i FALSE, što nam pomaže da jednostavno testiramo više kriterija. Ovo također nije moguće s funkcijom AVERAGEIF.
Sintaksa
Sintaksa Excelove funkcije AVERAGEIFS je sljedeća:
=AVERAGEAIFS(prosječni_raspon, kriterij1_raspon1, kriterij1, [kriterij2_raspon2, kriterij2], ...)
Ovdje su prosječni_raspon, kriterij1_raspon1 i kriterij1 potrebni argumenti. Međutim, možemo dostaviti izborne argumente na temelju naših efektivnih skupova podataka. Nadalje, kriterij1 se odnosi na uvjet koji treba testirati na rasponu1, kriterij2 se odnosi na uvjet koji treba testirati na rasponu2 i više.
Koraci za izračunavanje srednje vrijednosti pomoću funkcije AVERAGEIFS
Razmotrimo iste primjere podataka koje smo koristili u prethodnoj metodi. U prethodnoj metodi koristili smo funkciju AVERAGEIF i izračunali srednju vrijednost za cijene ponovljenog voća (jabuka). U tom slučaju imali smo samo jedan uvjet.
Sada, pretpostavimo da moramo izračunati srednju vrijednost za cijene Applea, ali uključiti samo one ćelije u kojima su cijene iznad 40.
U ovom slučaju imamo dva različita kriterija, pa se funkcija AVERAGEIF ne može koristiti.
rujira banerjee
Kriterij 1: U rasponu A2:A9, početni kriterij je provjera 'Applea' koji zadovoljava tri ćelije (ili reda)
Kriterij 2: U rasponu B2:B9, drugi kriterij je provjeriti cijene iznad 40 (>40) koje zadovoljavaju dvije od tri ćelije (ili red) s voćnom jabukom
Stoga, ako matematički izračunamo srednju vrijednost, koristimo sljedeću formulu:
(95+68)/2
Ovo vraća srednju vrijednost od 81.5. Provjerimo dobivamo li iste rezultate s funkcijom AVERAGEIFS u Excelu.
Kada trebamo testirati uvjete u Excelu, obično koristimo pomoć logičkog operatora OR. Operator OR vraća TRUE i FALSE ovisno o tome zadovoljava li zadani uvjet. Na primjer, pretpostavimo da trebamo provjeriti sadrži li ćelija A2 Apple. U ovom slučaju, operator OR možemo koristiti ovako:
=ILI(A2='Jabuka')
Gornji uvjet umećemo u našu tablicu s primjerima kako bismo testirali ili locirali ćelije s voćem Jabuka. Dakle, stvaramo novi stupac (C) i upisujemo određeni uvjet u ćeliju C2. Nakon toga kopiramo isti uvjet u preostale ćelije stupca C. To izgleda ovako:
Gornja slika pokazuje da operator OR vraća TRUE za ćelije koje odgovaraju plodu Apple, dok za ostale vraća FALSE. Na temelju ovog rezultata sada primjenjujemo funkciju AVERAGEIFS i umećemo više uvjeta za ćelije TRUE vrijednosti na sljedeći način:
- Kao i druge metode, opet prvo odabiremo rezultantnu ćeliju (tj. B12).
- Zatim unosimo formulu AVERAGEIFS na sljedeći način:
=PROSJEČNOIFS(B2:B9;C2:C9;'ISTINA';B2:B9;'>40')
Uz gornju AVERAGEIFS formulu, uključili smo samo voće 'Jabuka' i cijene iznad 40. - Na kraju, moramo pritisnuti Unesi ključ za dobivanje rezultata formule. Za naš skup podataka, formula je vratila aritmetičku sredinu od 81.5, isto kao i naš matematički izračunati rezultat.
Posebni slučajevi
Iako možemo imati različite scenarije prilikom izračunavanja aritmetičke sredine u Excelu, ovdje se raspravlja o dva najčešća. Moramo biti oprezni kada računamo aritmetičku sredinu u ovim slučajevima.
Izračunavanje srednje vrijednosti uključujući nule
Kada trebamo izračunati srednju vrijednost s nulama, normalno možemo koristiti funkciju AVERAGE i dostaviti efektivne ćelije, uključujući ćelije s nultom vrijednošću. Funkcija AVERAGE uključuje nule kao važeće brojeve u svom izračunu.
algoritam sortiranja umetanjem
U sljedećem uzorku skupa podataka primjenjujemo funkciju AVERAGE za izračun srednje vrijednosti za dvije različite ocjene učenika:
Gornja slika prikazuje različite srednje vrijednosti za oba učenika. Ako pogledamo ocjene, zbroj ocjena je isti za oba učenika. Drugi učenik ima nula ocjena iz jednog predmeta (polje s nultom vrijednošću), dok je prvi učenik izostao (polje s tekstualnim vrijednostima) iz jednog predmeta.
Budući da funkcija AVERAGE zanemaruje tekstualne vrijednosti, ali uključuje nulte vrijednosti, broj dijeljenja varira za oba učenika. Za prvog učenika broj odjeljaka je 4, dok je za drugog učenika 5. Dakle, postoji razlika u srednjim rezultatima. Međutim, također možemo eksplicitno isključiti nulte vrijednosti ako je potrebno.
Izračunavanje srednje vrijednosti bez nula
Kada trebamo izračunati srednju vrijednost isključujući nule, moramo iskoristiti Excelovu funkciju AVERAGEIF. Možemo primijeniti određeni uvjet u ovoj funkciji, nalažući Excelu da ne uključuje ćelije s nultom vrijednošću tijekom izračuna.
Donja formula AVERAGEIF može se koristiti za isključivanje nultih vrijednosti pri izračunavanju srednje vrijednosti u Excelu:
=AVERAGEIF(ćelija ili raspon,'>0')
Ako umetnemo gornju formulu za naše uzorke podataka, isti rezultati srednje vrijednosti pojavljuju se za oba učenika.
Gornja formula AVERAGEIF zanemaruje tekst i ćelije s nultom vrijednošću. Dakle, brojevi podjela su isti (tj. 4), kao i aritmetička sredina za ocjene oba učenika.
Važne točke koje treba zapamtiti
- Važno je napomenuti da svaka funkcija u Excelu, uključujući AVERAGE, AVERAGEA, AVERAGEIF i AVERAGEIFS, može prihvatiti najviše 255 argumenata.
- Ne preporučuje se ručno upisivanje naziva funkcije u Excel ćeliju. Odabirom željene funkcije s popisa prijedloga bolje je izbjeći pogrešno napisane nazive funkcija i eliminirati pogreške u rezultatima.