„Microsoft Excel“ (dažnai vadinama „Excel“) yra galinga skaičiuoklių programinė įranga, skirta dideliems duomenų rinkiniams saugoti. Be to, tai leidžia vartotojams tvarkyti ir analizuoti duomenis ir netgi atlikti pagrindinius ir sudėtingus įrašytų duomenų skaičiavimus. Pagrindinės „Excel“ duomenų analizės užduotys daugiausia apima vidurkio (arba aritmetinio vidurkio), režimo ir medianos apskaičiavimą arba radimą.
Šioje pamokoje aptariame įvairius nuoseklius metodus arba sprendimus, kaip apskaičiuoti vidurkį programoje „Excel“. Prieš aptardami metodus, pirmiausia trumpai supraskime vidurkio sąvoką ir jos naudą.
Kas yra „Excel“ reikšmė?
Programoje „Excel“ aritmetinis vidurkis yra ne kas kita, o vidutinis skaičius, kuris paprastai apskaičiuojamas sudedant visus duoto duomenų rinkinio skaičius ir padalijus sumą iš bendro nurodytų elementų reikšmių skaičiaus (arba skaičiaus).
Nors realiame gyvenime lengva apskaičiuoti mažų duomenų rinkinių aritmetinį vidurkį, dirbti su dideliais duomenų rinkiniais tampa sunku. Čia pravers tokia galinga skaičiuoklių programa kaip „Excel“. Jis gali lengvai apskaičiuoti nuo šimtų iki tūkstančių skaičių vidurkį. Be to, jis yra mažiau linkęs į klaidas.
Yra daug esminių Vidurkio panaudojimo būdų analizuojant duomenis, pavyzdžiui:
- Norėdami palyginti istorinius duomenis
- Paryškinti KPI (pagrindinius našumo rodiklius) ir citatas
- Norėdami rasti nurodytų duomenų taškų vidurio tašką
- Nustatyti projektų valdymo ir verslo planus
Pagrindinis scenarijus, kai gali tekti apskaičiuoti vidurkį programoje „Excel“, yra tada, kai turime sužinoti vidutinį prekės pardavimą per visą mėnesį, ketvirtį, metus ir pan.
Kaip apskaičiuoti vidurkį „Excel“?
Deja, nėra apibrėžtos MEAN funkcijos duomenų aritmetiniam vidurkiui apskaičiuoti arba nustatyti. Tačiau yra keletas veiksmingų būdų. Paprastai galime taikyti matematinę sąvoką: pridėti vertes, o tada padalyti jų sumą iš reikšmių skaičiaus Excel langeliuose. Be to, yra tokių funkcijų kaip AVERAGE, AVERAGEA, AVERAGEIF ir AVERAGEIFS, kurios taip pat padeda apskaičiuoti aritmetinį vidurkį įvairiais naudojimo atvejais.
Norėdami geriau suprasti, kaip šios sąvokos veikia apskaičiuojant aritmetinį vidurkį, aptarkime kiekvieną atskirai pateikdami pavyzdį:
Vidurkio skaičiavimas „Excel“: bendrasis metodas
„Excel“ lape galime taikyti funkcijas SUM ir COUNT, kad galėtume pridėti verčių ir padalyti jas iš įtrauktų reikšmių skaičiaus.
Pavyzdžiui, tarkime, kad pateikėme skaičius nuo 1 iki 5 ir turime apskaičiuoti aritmetinį vidurkį. Matematiškai visus šiuos skaičius pirmiausia sumuojame, t.y. (1+2+3+4+5). Vėliau jų SUM dalijame iš 5, nes tai yra bendras nurodytų skaičių skaičius.
Taigi visa vidurkio skaičiavimo formulė tampa tokia:
(1+2+3+4+5)/ 5
Tai grąžina aritmetinį vidurkį 3.
Programoje „Excel“ galime naudoti aukščiau pateiktą matematinę formulę, sujungdami ją su funkcijomis SUM ir COUNT tokiu būdu:
gimp kaip panaikinti pasirinkimą
=SUM(1,2,3,4,5)/SKAIČIUS(1,2,3,4,5)
Čia funkcija SUM sudeda nurodytas reikšmes, o funkcija COUNT nustato nurodytų reikšmių skaičių.
Dabar, jei įrašysime šiuos penkis skaičius diapazone A1:A5, toliau pateikta formulė langelyje A6 apskaičiuos aritmetinį vidurkį:
=SUMMA(A1:A5)/SKAIČIUS(A1:A5)
Rezultatas (arba vidurkis), gautas aukščiau esančiame paveikslėlyje, yra toks pat, kokį apskaičiavome pagal matematinę formulę, t. 3.
Vidurkio skaičiavimas programoje Excel: AVERAGE funkcija
Kadangi vidurkis yra vidurkis, „Excel“ funkcija AVERAGE yra efektyviausias būdas apskaičiuoti aritmetinį vidurkį. Tai yra viena dažniausiai naudojamų statistinių funkcijų tam tikro diapazono vidurkiui apskaičiuoti, jei nėra specialių sąlygų ar apribojimų. Tai yra įmontuota „Excel“ funkcija.
Sintaksė
„Excel“ funkcijos AVERAGE sintaksė yra tokia:
=VIDUTINIS(skaičius1, [skaičius2], ...)
Skaičius1 yra būtinas argumentas, o tai reiškia, kad vienas argumentas yra privalomas funkcijoje AVERAGE. Tačiau galime pateikti kelis argumentus skirtingomis formomis, pavyzdžiui, skaičiais, langeliais, diapazonais, masyvais, konstantomis ir tt Skaičiuodami aritmetinį vidurkį, funkcijoje AVERAGE turėtume pateikti bent du argumentus.
Vidurkio skaičiavimo veiksmai naudojant AVERAGE funkciją
Dabar supraskime, kaip veikia funkcija AVERAGE, ir, naudodami pavyzdinius duomenis, apskaičiuokime skaičių grupės Excel lape aritmetinį vidurkį.
Tarkime, kai kuriuos vaisius išvardijame Excel lape su atitinkamomis kainomis. Vaisių pavadinimai įrašomi A stulpelyje (A2:A9), o jų kainos – kitame B stulpelyje (B2:B9). B10 langelyje turime rasti arba apskaičiuoti vidutinę kainą (arba kainų aritmetinį vidurkį).
Norėdami apskaičiuoti aritmetinį vidurkį mūsų pavyzdiniame darbalapyje, turime atlikti šiuos veiksmus:
- Pirmiausia turime pasirinkti arba paryškinti langelį, kad įrašytume gautą aritmetinį vidurkį, ty langelį B10.
- Toliau turime įterpti funkcijos pavadinimą, prasidedantį lygybės ženklu, ir funkcijoje pateikti reikiamus argumentus. Mūsų atveju įvedame lygybės ženklą (=) ir funkciją AVERAGE. Taip pat galime pasirinkti funkciją iš „Excel“ siūlomų funkcijų sąrašo.
- Įvedę funkcijos pavadinimą, turime įvesti pradinį skliaustelį ir pateikti efektyvų duomenų rinkinių diapazoną. Kadangi duomenų turime gretimuose langeliuose arba diapazone (nuo B2 iki B9), visą diapazoną funkcijoje galime perduoti tokiu būdu:
- Galiausiai turime įvesti baigiamąjį skliaustelį ir paspausti Įeikite klaviatūros klavišą, kad gautumėte efektyvų rezultatą arba aritmetinį vidurkį duotam duomenų rinkiniui. Žemiau esančiame paveikslėlyje parodytas kainų aritmetinis vidurkis langeliuose nuo B2 iki B9:
Aukščiau esančiame paveikslėlyje panaudojome gretimus langelius ir vienu kartu pateikėme visą duomenų rinkinių spektrą. Tačiau taip pat galime apskaičiuoti ne gretimų langelių vidurkį mūsų „Excel“ lape, pateikdami langelius atskirai.
Tarkime, kad reikia apskaičiuoti tik trijų vaisių kainų aritmetinį vidurkį: Obuoliai, mangai ir vynuogės. Tokiu atveju kiekvieną langelį galime perduoti atskirai funkcijoje tokiu būdu:
=VIDUTINIS(B2,B3,B6)
Čia B2, B3 ir B6 yra atskiros ląstelės su Apple, Mango ir Grapes kainomis.
Nors funkcija AVERAGE gali būti naudojama su tuščiais langeliais, tekstais, nuliais, loginėmis reikšmėmis ir net mišriais argumentais, skaičiavimo metu ji nepaiso tuščių langelių, teksto ir loginių reikšmių. Tačiau nulinės reikšmės langeliai yra įtraukti į funkciją AVERAGE.
Vidurkio skaičiavimas programoje „Excel“: Funkcija AVERAGEA
Funkcija AVERAGEA yra šiek tiek patobulinta funkcijos AVERAGE versija. Funkcija AVERAGEA taip pat padeda apskaičiuoti nurodytų duomenų rinkinių aritmetinį vidurkį ir veikia visose „Excel“ versijose. Vienas iš AVERAGEA funkcijos pranašumų yra tai, kad skaičiuojant vidurkį programoje „Excel“ ji gali apimti tekstines reikšmes ir Būlio duomenų tipus. Teksto reikšmės įvertinamos kaip nuliai, o Būlio reikšmės TRUE ir FALSE įvertinamos atitinkamai kaip 1 ir 0.
Sintaksė
du prie vieno multiplekserio
„Excel“ funkcijos AVERAGEA sintaksė yra tokia:
=VIDUTINIS(reikšmė1, [reikšmė2], ...)
Kaip ir funkcija AVERAGE, būtinas pirmasis funkcijos AVERAGEA argumentas. Tačiau prireikus galime naudoti kelis argumentus.
Funkcijų AVERAGE ir AVERAGEA sintaksė yra beveik panaši; tačiau jų darbo koncepcija šiek tiek skiriasi. Funkcija AVERAGE gali apskaičiuoti tik skaitinių reikšmių aritmetinį vidurkį, o funkcija AVERAGEA gali apskaičiuoti tą patį bet kokio tipo duomenims, įskaitant tekstą ir Būlio vertes.
Vidurkio skaičiavimo veiksmai naudojant AVERAGEA funkciją
Perimkime ankstesnius pavyzdinius duomenų rinkinius, kuriuose A stulpelyje yra vaisių pavadinimai (A2:A9), o jų kainos B stulpelyje (B2:B9). Tačiau šį kartą neturime vynuogių kainos (įrašyta kaip „Nepasiekiama“ atitinkamame langelyje B6), kaip parodyta toliau:
Mes galime atlikti toliau nurodytus veiksmus ir apskaičiuoti vidutinę vertę naudodami AVERAGEA funkciją:
- Kaip ir ankstesnis metodas, pirmiausia pasirenkame gautą langelį B10, kad įrašytume vidurkį. Tada įvedame lygybės ženklą (=), įvedame arba pasirenkame mūsų funkciją AVERAGEA ir perduodame visą duomenų diapazoną funkcijai, esančiam skliausteliuose.
- Galiausiai turime paspausti Įeikite raktas norint gauti efektyvią produkciją (arba vidurkį).
Aukščiau pateiktame paveikslėlyje pateikiama vidutinė išvestis 57,125 naudojant AVERAGEA funkciją.
Jei šiame pavyzdyje naudosime AVERAGE funkciją ir apskaičiuosime aritmetinį vidurkį, pamatysime didžiulį skirtumą tarp abiejų funkcijų gautų rezultatų. Vidutinė išvestis 65,285 rodoma naudojant AVERAGE funkciją, kaip parodyta toliau:
Aritmetinio vidurkio skirtumai šiame pavyzdyje yra prasmingi, nes abiejų šių funkcijų padalijimų skaičius skiriasi. Funkcijos AVERAGEA skaičius yra 8, nes ji taip pat įvertina TEKSTAS kaip tinkamus duomenis. Tačiau funkcijai AVERAGE padalijimų skaičius yra 7, nes į skaičiavimą neįtraukiami TEKSTO duomenys.
Vietoj TEKSTO duomenų, jei duomenų rinkinyje yra nulis (0), abiem funkcijoms (AVERAGE ir AVERAGEA) bus rodomas tas pats rezultatas (arba aritmetinis vidurkis), nes abiejose skaičiavime yra nulis kaip tinkami duomenys.
Vidurkio skaičiavimas programoje „Excel“: funkcija AVERAGEIF
Funkcija AVERAGEIF yra atitinkama funkcijos AVERAGE funkcija ir padeda apskaičiuoti aritmetinį vidurkį, esant tam tikrai sąlygai arba kriterijams. Naudodami funkciją AVERAGEIF, galime apskaičiuoti tik tam tikrų skaičių, atitinkančių nurodytus kriterijus, vidurkį. „Excel“ pirmiausia įvertina konkrečius kriterijus, pagal šiuos kriterijus nustato efektyvias langelius ir atitinkamai apskaičiuoja tik esančių langelių vidurkį.
Sintaksė
„Excel“ funkcijos AVERAGEIF sintaksė yra tokia:
styga tuščia
=VIDUTINIS AIF(diapazonas, kriterijai, [vidutinis_diapazonas])
Čia diapazonas ir kriterijai yra būtini argumentai, o vidutinis_diapazonas yra neprivalomas.
- Diapazone yra langelių, kuriuos reikia patikrinti pagal nurodytus kriterijus.
- Kriterijus nurodo argumentą, kuriame galime nurodyti norimą sąlygą, kurios reikia laikytis. Tai gali apimti tekstą, skaitines reikšmes, langelių nuorodas, diapazoną ir loginę išraišką.
- Vidutinis diapazonas yra argumentas, kuriame minime langelius (arba diapazoną), kurių vidurkį norime apskaičiuoti. Jei šis argumentas nenurodytas, „Excel“ automatiškai naudoja diapazoną.
Vidurkio skaičiavimo veiksmai naudojant AVERAGEIF funkciją
Tame pačiame pavyzdyje tarkime, kad kai kuriuos vaisius pateikiame kelis kartus skirtingomis kainomis. Šį kartą turime apskaičiuoti tik bet kurio konkretaus pasikartojančio vaisiaus (t. y. obuolio) B11 langelyje vidurkį. Taigi, mes naudojame funkciją AVERAGEIF ir taikome sąlygą skaičiuodami vidurkį. Norėdami apskaičiuoti vidutinę Apple kainą, turime atlikti šiuos veiksmus:
- Pirmiausia pasirenkame gautą langelį (B11) ir įterpiame AVERAGEIF formulę tokiu būdu:
=VIDUTINIS IF(A2:B9, 'Apple', B2:B9)
Čia A2:B9 reiškia efektyvų diapazoną, Apple – norimus kriterijus/sąlygą, o B2:B9 – vidutinį_diapazoną.
Čia rankiniu būdu pateikėme pasirenkamą vidutinį_ diapazoną, kad įtrauktume tik langelius su kainomis. Mūsų pavyzdyje, jei neperduodame vidurkio_diapazono, funkcija naudos pirminį diapazoną (A2:B9) su tekstiniais duomenimis ir sukels #DIV! klaida.
Pastaba: būtina pažymėti, kad funkcija AVERAGEIF nepaiso tuščių langelių savo argumente vidutinis_diapazonas. Be to, jei kuris nors kriterijuose nurodytas langelis yra tuščias, funkcija traktuoja jį kaip nulinės reikšmės langelį.
Vidurkio skaičiavimas programoje Excel: AVERAGEIFS funkcija
Funkcija AVERAGEIFS yra išplėstinė arba išplėstinė funkcijos AVERAGEIF versija. Funkcija AVERAGEIF leidžia vienu metu taikyti tik vieną sąlygą. Tačiau naudodami AVERAGEIFS funkciją galime taikyti kelis kriterijus, kurie bus tikrinami vienu metu. Be to, funkcija AVERAGEIFS leidžia mums naudoti langelius su Būlio reikšmėmis TRUE ir FALSE, todėl galime lengvai patikrinti kelis kriterijus. Tai taip pat neįmanoma naudojant AVERAGEIF funkciją.
Sintaksė
„Excel“ funkcijos AVERAGEIFS sintaksė yra tokia:
=VIDUTINIS AIFS(vidutinis_diapazonas, 1 kriterijų_diapazonas1, kriterijai1, [2 kriterijų_diapazonas2, kriterijai2], ...)
Čia vidutinis_diapazonas, kriterijai1_diapazonas1 ir kriterijai1 yra būtini argumentai. Tačiau galime pateikti pasirenkamus argumentus, pagrįstus mūsų veiksmingais duomenų rinkiniais. Be to, kriterijus1 nurodo sąlygą, kurią reikia išbandyti diapazone1, kriterijus2 – sąlygą, kurią reikia išbandyti diapazone 2, ir kt.
Vidurkio skaičiavimo veiksmai naudojant AVERAGEIFS funkciją
Panagrinėkime tuos pačius duomenų pavyzdžius, kuriuos naudojome ankstesniame metode. Ankstesniu metodu naudojome funkciją AVERAGEIF ir apskaičiavome pasikartojančių vaisių (obuolių) kainų vidurkį. Tokiu atveju turėjome tik vieną sąlygą.
Tarkime, kad turime apskaičiuoti Apple kainų vidurkį, bet įtraukti tik tuos langelius, kurių kainos viršija 40.
Šiuo atveju turime du skirtingus kriterijus, todėl funkcijos AVERAGEIF naudoti negalima.
1 kriterijai: Diapazone A2:A9 pradinis kriterijus yra patikrinti, ar „Apple“ atitinka trys langeliai (arba eilutės).
2 kriterijai: Diapazone B2:B9 kiti kriterijai – patikrinti kainas, viršijančias 40 (>40), kurias tenkina dvi iš trijų langelių (arba eilutės) su vaisių obuoliu.
Taigi, jei matematiškai apskaičiuojame vidurkį, naudojame toliau pateiktą formulę:
(95+68)/2
Tai grąžina vidutinę vertę 81.5. Patikrinkime, ar gauname tuos pačius rezultatus naudodami „Excel“ funkciją AVERAGEIFS.
Kai mums reikia patikrinti sąlygas „Excel“, dažniausiai pasitelkiame loginį operatorių ARBA. Operatorius ARBA grąžina TRUE ir FALSE, priklausomai nuo to, ar tenkinama nurodyta sąlyga. Pavyzdžiui, tarkime, kad turime patikrinti, ar langelyje A2 yra „Apple“. Tokiu atveju galime naudoti OR operatorių taip:
=ARBA(A2='Apple')
Aukščiau nurodytą sąlygą įterpiame į savo pavyzdinį lapą, kad išbandytume arba surastume ląsteles su obuoliu. Taigi, sukuriame naują stulpelį (C) ir langelyje C2 įrašome konkrečią sąlygą. Po to tą pačią sąlygą nukopijuojame likusiuose C stulpelio langeliuose. Tai atrodo taip:
Aukščiau pateiktame paveikslėlyje parodyta, kad operatorius ARBA pateikia TRUE langeliams, atitinkantiems vaisių obuolį, o kitiems – FALSE. Remdamiesi šiuo rezultatu, dabar taikome AVERAGEIFS funkciją ir įterpiame kelias sąlygas TRUE reikšmės langeliams tokiu būdu:
- Kaip ir kiti metodai, mes vėl pirmiausia pasirenkame gautą langelį (ty B12).
- Tada įvedame AVERAGEIFS formulę tokiu būdu:
=VIDUTINIAI IFS(B2:B9,C2:C9,'TRUE',B2:B9,'>40')
Pagal aukščiau pateiktą AVERAGEIFS formulę įtraukėme tik vaisius „Apple“ ir kainas, viršijančias 40. - Galiausiai turime paspausti Įeikite raktas formulės rezultatui gauti. Mūsų duomenų rinkiniui formulė grąžino aritmetinį vidurkį 81,5, toks pat kaip ir mūsų matematiškai apskaičiuotas rezultatas.
Ypatingi atvejai
Nors skaičiuodami aritmetinį vidurkį programoje „Excel“, galime turėti įvairių scenarijų, čia aptariami du dažniausiai pasitaikantys scenarijai. Šiais atvejais turime būti atsargūs skaičiuodami aritmetinį vidurkį.
Vidurkio, įskaitant nulius, apskaičiavimas
Kai reikia apskaičiuoti vidurkį su nuliais, paprastai galime naudoti funkciją AVERAGE ir pateikti efektyvius langelius, įskaitant nulinės vertės langelius. Funkcija AVERAGE įtraukia nulius kaip galiojančius skaičius.
Toliau pateiktame duomenų rinkinio pavyzdyje taikome funkciją AVERAGE, kad apskaičiuotume dviejų skirtingų mokinių pažymių vidurkį:
Aukščiau pateiktame paveikslėlyje parodytos skirtingos abiejų mokinių vidutinės vertės. Jei žiūrėtume į pažymius, abiejų mokinių balų suma yra vienoda. Antrasis studentas turi nulį vieno dalyko balų (nulinės reikšmės langelis), o pirmojo studento nėra (teksto reikšmės langelis) viename dalyke.
Kadangi funkcija AVERAGE nepaiso tekstinių reikšmių, bet apima nulines reikšmes, abiejų mokinių padalijimo skaičius skiriasi. Pirmojo studento dalybos yra 4, o kito studento - 5. Taigi skiriasi vidutiniai rezultatai. Tačiau, jei reikia, mes taip pat galime aiškiai išskirti nulines reikšmes.
Vidurkio, neįskaitant nulių, skaičiavimas
Kai reikia apskaičiuoti vidurkį, neįskaitant nulių, turime pasinaudoti Excel AVERAGEIF funkcija. Šioje funkcijoje galime taikyti konkrečią sąlygą, nurodydami „Excel“ neįtraukti nulinės reikšmės langelių skaičiavimo metu.
Toliau pateiktą AVERAGEIF formulę galima naudoti norint neįtraukti nulinių reikšmių skaičiuojant vidurkį programoje „Excel“.
=VIDUTINISJEI(ląstelė arba diapazonas,'>0')
styginių kūrimo priemonė java
Jei įterpsime aukščiau pateiktą formulę savo pavyzdiniams duomenims, abiejų mokinių vidutinės reikšmės rezultatai bus vienodi.
Aukščiau pateikta AVERAGEIF formulė nepaiso teksto ir nulinės reikšmės langelių. Taigi, padalijimo skaičiai yra vienodi (t. y. 4), taip pat ir abiejų mokinių pažymių aritmetinis vidurkis.
Svarbūs dalykai, kuriuos reikia atsiminti
- Svarbu pažymėti, kad bet kuri „Excel“ funkcija, įskaitant AVERAGE, AVERAGEA, AVERAGEIF ir AVERAGEIFS, gali priimti daugiausia 255 argumentus.
- Rankiniu būdu įvesti funkcijos pavadinimą Excel langelyje nerekomenduojama. Pasirinkus norimą funkciją iš pasiūlymų sąrašo, geriau išvengti klaidingai parašytų funkcijų pavadinimų ir pašalinti rezultato klaidas.