Si të llogarisni GPA në Excel. Llogaritja e mesatares aritmetike në Excel

Excel e ka bërë llogaritjen e mesatares së qelizave të shumta një detyrë shumë të lehtë - thjesht përdorni funksionin MESATAR(MESATARE). Por, çka nëse disa vlera kanë më shumë peshë se të tjerat? Për shembull, në shumë kurse, testet kanë më shumë peshë sesa detyrat. Për raste të tilla, është e nevojshme të llogaritet mesatare e ponderuar.

Excel nuk ka një funksion për llogaritjen e mesatares së ponderuar, por ekziston një funksion që do të bëjë pjesën më të madhe të punës për ju: SUMPRODUCT(SUM PRODUKTI). Dhe edhe nëse nuk e keni përdorur kurrë më parë këtë veçori, deri në fund të këtij artikulli do ta përdorni si një profesionist. Metoda që përdorim funksionon në çdo version të Excel, si dhe në fletëllogaritëse të tjera si Google Sheets.

Ne përgatisim tryezën

Nëse do të llogaritni një mesatare të ponderuar, do t'ju duhen të paktën dy kolona. Kolona e parë (kolona B në shembullin tonë) përmban pikët për çdo detyrë ose test. Kolona e dytë (kolona C) përmban peshat. Më shumë peshë do të thotë më shumë ndikim i detyrës ose testit në notën përfundimtare.

Për të kuptuar se çfarë është pesha, mund ta mendoni si përqindje të notës suaj përfundimtare. Në fakt, nuk është kështu, pasi në këtë rast peshat duhet të mblidhen deri në 100%. Formula që do të analizojmë në këtë mësim do të llogarisë gjithçka në mënyrë korrekte dhe nuk varet nga sasia në të cilën shtohen peshat.

Ne futim formulën

Tani që tabela jonë është gati, ne shtojmë formulën në qelizë B10(çdo qelizë bosh do të bëjë). Si me çdo formulë tjetër në Excel, ne fillojmë me një shenjë të barabartë (=).

Pjesa e parë e formulës sonë është funksioni SUMPRODUCT(SUM PRODUKTI). Argumentet duhet të futen në kllapa, kështu që ne i hapim ato:

SUMPRODUCT(
=SUMPRODUCT(

Më pas, shtoni argumentet e funksionit. SUMPRODUCT(SUMPRODUCT) mund të ketë argumente të shumta, por zakonisht përdoren dy. Në shembullin tonë, argumenti i parë do të jetë një varg qelizash. B2:B9 A që përmban pikët.

SUMPRODUCT(B2:B9
=SUMPRODUCT(B2:B9

Argumenti i dytë do të jetë një varg qelizash C2: C9, e cila përmban peshat. Këto argumente duhet të ndahen me një pikëpresje (presje). Kur gjithçka është gati, mbyllni kllapat:

SUMPRODUCT(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)

Tani le të shtojmë pjesën e dytë të formulës sonë, e cila do të ndajë rezultatin e llogaritur me funksionin SUMPRODUCT(SUMPRODUCT) me shumën e peshave. Ne do të diskutojmë më vonë pse kjo është e rëndësishme.

Për të kryer operacionin e ndarjes, ne vazhdojmë formulën e futur tashmë me simbolin / (pjesë e drejtë), dhe më pas shkruani funksionin SHUMË(SHUMË):

SUMPRODUCT(B2:B9;C2:C9)/SUM(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(

Për funksionin SHUMË(SUM) ne do të specifikojmë vetëm një argument - një varg qelizash C2: C9. Mos harroni të mbyllni kllapat pas futjes së argumentit:

SUMPRODUCT(B2:B9;C2:C9)/SUM(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)

Gati! Pasi të keni shtypur tastin Hyni, Excel do të llogarisë mesataren e ponderuar. Në shembullin tonë, rezultati përfundimtar do të jetë 83,6 .

Si punon

Le të zbërthejmë secilën pjesë të formulës, duke filluar me funksionin SUMPRODUCT(SUMPRODUCT) për të kuptuar se si funksionon. Funksioni SUMPRODUCT(SUMPRODUCT) llogarit produktin e pikëve të secilit artikull dhe peshën e tij, dhe më pas mbledh të gjitha produktet që rezultojnë. Me fjalë të tjera, funksioni gjen shumën e produkteve, pra emrin. Pra për Detyrat 1 shumëzo 85 me 5, dhe për Testa shumëzo 83 me 25.

Nëse po pyesni veten pse duhet të shumëzojmë vlerat në pjesën e parë, imagjinoni që sa më e lartë të jetë pesha e artikullit, aq më shumë herë duhet të marrim parasysh notën për të. Për shembull, Detyra 2 numërohen 5 herë dhe Provimi përfundimtar- 45 herë. Kjo është arsyeja pse Provimi përfundimtar ka një ndikim më të madh në rezultatin përfundimtar.

Për krahasim, kur llogaritet mesatarja e zakonshme aritmetike, çdo vlerë merret parasysh vetëm një herë, domethënë të gjitha vlerat kanë peshë të barabartë.

Nëse mund të shikoni nën kapuçin e një funksioni SUMPRODUCT(SUMPRODUCT), pamë që në fakt ajo beson këtë:

=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)

Për fat të mirë, nuk kemi nevojë të shkruajmë një formulë kaq të gjatë sepse SUMPRODUCT(SUMPRODUCT) i bën të gjitha këto automatikisht.

Vetë funksioni SUMPRODUCT(SUMPRODUCT) na kthen një numër të madh − 10450 . Në këtë pikë, pjesa e dytë e formulës hyn në lojë: /SUM(C2:C9) ose /SUM(C2:C9), e cila e kthen rezultatin në diapazonin normal të pikëve, duke dhënë përgjigjen 83,6 .

Pjesa e dytë e formulës është shumë e rëndësishme sepse ju lejon të korrigjoni automatikisht llogaritjet. Mos harroni se peshat nuk duhet të mblidhen deri në 100%? E gjithë kjo falë pjesës së dytë të formulës. Për shembull, nëse rrisim një ose më shumë vlera të peshës, pjesa e dytë e formulës thjesht do të pjesëtohet me vlerën më të madhe, duke rezultuar përsëri në përgjigjen e saktë. Ose mund t'i bëjmë peshat shumë më të vogla, për shembull duke specifikuar vlera si 0,5 , 2,5 , 3 ose 4,5 , dhe formula do të vazhdojë të funksionojë si duhet. Është e mrekullueshme, apo jo?

Lloji më i zakonshëm i mesatares është mesatarja aritmetike.

mesatare e thjeshtë aritmetike

Mesatarja e thjeshtë aritmetike është termi mesatar, në përcaktimin e të cilit vëllimi i përgjithshëm i një atributi të caktuar në të dhëna shpërndahet në mënyrë të barabartë midis të gjitha njësive të përfshira në këtë popullatë. Kështu, prodhimi mesatar vjetor për punëtor është një vlerë e tillë e vëllimit të prodhimit që do të binte mbi secilin punonjës nëse i gjithë vëllimi i prodhimit do të shpërndahej në mënyrë të barabartë midis të gjithë punonjësve të organizatës. Vlera mesatare aritmetike e thjeshtë llogaritet me formulën:

mesatare e thjeshtë aritmetike- E barabartë me raportin e shumës së vlerave individuale të një veçorie me numrin e veçorive në agregat

Shembulli 1 . Një ekip prej 6 punëtorësh merr 3 3.2 3.3 3.5 3.8 3.1 mijë rubla në muaj.

Gjeni pagën mesatare
Zgjidhja: (3 + 3.2 + 3.3 +3.5 + 3.8 + 3.1) / 6 = 3.32 mijë rubla.

Mesatarja e ponderuar aritmetike

Nëse vëllimi i grupit të të dhënave është i madh dhe përfaqëson një seri shpërndarjeje, atëherë llogaritet një mesatare aritmetike e ponderuar. Kështu përcaktohet çmimi mesatar i ponderuar për njësi prodhimi: kostoja totale e prodhimit (shuma e produkteve të sasisë së tij dhe çmimi i një njësie prodhimi) pjesëtohet me sasinë totale të prodhimit.

Ne e paraqesim këtë në formën e formulës së mëposhtme:

Mesatarja aritmetike e ponderuar- është e barabartë me raportin (shuma e produkteve të vlerës së atributit me shpeshtësinë e përsëritjes së këtij atributi) me (shumën e frekuencave të të gjitha atributeve) Përdoret kur variantet e popullsisë së studiuar ndodhin një pabarabartë numri i herëve.

Shembulli 2 . Gjeni pagat mesatare të punonjësve të dyqaneve në muaj

Paga mesatare mund të merret duke pjesëtuar pagën totale me numrin e përgjithshëm të punëtorëve:

Përgjigje: 3,35 mijë rubla.

Mesatarja aritmetike për një seri intervali

Kur llogaritet mesatarja aritmetike për një seri variacionesh intervali, mesatarja për çdo interval përcaktohet fillimisht si gjysma e kufirit të sipërm dhe të poshtëm, dhe më pas mesatarja e të gjithë serisë. Në rastin e intervaleve të hapura, vlera e intervalit të poshtëm ose të sipërm përcaktohet nga vlera e intervaleve ngjitur me to.

Mesataret e llogaritura nga seritë e intervalit janë të përafërta.

Shembulli 3. Përcaktoni moshën mesatare të studentëve në departamentin e mbrëmjes.

Mesataret e llogaritura nga seritë e intervalit janë të përafërta. Shkalla e përafrimit të tyre varet nga shkalla në të cilën shpërndarja aktuale e njësive të popullsisë brenda intervalit afrohet uniforme.

Gjatë llogaritjes së mesatareve, jo vetëm vlerat absolute, por edhe ato relative (frekuenca) mund të përdoren si pesha:

Mesatarja aritmetike ka një numër karakteristikash që zbulojnë më plotësisht thelbin e saj dhe thjeshtojnë llogaritjen:

1. Prodhimi i mesatares dhe i shumës së frekuencave është gjithmonë i barabartë me shumën e prodhimeve të variantit dhe të frekuencave, d.m.th.

2. Mesatarja aritmetike e shumës së vlerave të ndryshme është e barabartë me shumën e mesatareve aritmetike të këtyre vlerave:

3. Shuma algjebrike e devijimeve të vlerave individuale të atributit nga mesatarja është zero:

4. Shuma e devijimeve në katror të opsioneve nga mesatarja është më e vogël se shuma e devijimeve në katror nga çdo vlerë tjetër arbitrare, d.m.th.

Kur punoni me shprehje numerike, ndonjëherë lind nevoja për të llogaritur vlerën mesatare të tyre. quhet mesatarja aritmetike. Në Excel, një redaktues i fletëllogaritjes nga Microsoft, është e mundur të mos llogaritet manualisht, por të përdoren mjete speciale. Në këtë artikull do të paraqiten metoda që ju lejojnë të zbuloni dhe shfaqni mesataren aritmetike.

Metoda 1: standarde

Para së gjithash, le të analizojmë metodën se si të llogarisim mesataren aritmetike në Excel, e cila përfshin përdorimin e një mjeti standard për këtë. Metoda është më e thjeshta dhe më e përshtatshme për t'u përdorur, por gjithashtu ka disa disavantazhe. Por rreth tyre më vonë, por tani le të kalojmë në detyrë.

Zgjidhni qelizat në kolonën ose rreshtin që përmbajnë vlerat numerike që do të llogariten.
Shkoni te skeda "Home".
Në shiritin e veglave në kategorinë "Editing", klikoni në butonin "AutoSum", por duhet të klikoni në shigjetën pranë tij në mënyrë që të shfaqet një listë rënëse.
Në të duhet të klikoni në artikullin "Mesatar".

Sapo ta bëni këtë, rezultati i llogaritjes së mesatares aritmetike të vlerave të zgjedhura do të shfaqet në qelizën pranë tij. Vendndodhja e tij do të varet nga blloku i të dhënave, nëse keni zgjedhur një rresht, atëherë rezultati do të jetë në të djathtë të përzgjedhjes, nëse kolona është më poshtë.

Por, siç u përmend më herët, kjo metodë ka të metat e saj. Pra, nuk do të jeni në gjendje të llogarisni vlerën nga një sërë qelizash ose qelizash të vendosura në vende të ndryshme. Për shembull, nëse tabela juaj ka dy kolona me vlera numerike ngjitur me to, atëherë duke i zgjedhur ato dhe duke kryer hapat e mësipërm, do të merrni rezultatin për secilën kolonë veç e veç.

Metoda 2: Përdorimi i magjistarit të funksionit

Ka shumë mënyra për të gjetur mesataren aritmetike në Excel, dhe është e natyrshme që me ndihmën e tyre është e mundur të anashkalohen kufizimet që nënkupton metoda e mëparshme. Tani do të flasim për kryerjen e llogaritjeve duke përdorur Function Wizard. Pra, këtu është ajo që ju duhet të bëni.

Duke klikuar butonin e majtë të miut, zgjidhni qelizën në të cilën dëshironi të shihni rezultatin e llogaritjes.
Hapni dritaren e Funksionit Wizard duke klikuar në butonin "Fut Funksionin" që ndodhet në të majtë të shiritit të formulës ose duke përdorur tastet kryesore Shift+F3.
Në dritaren që shfaqet, gjeni rreshtin "MESATARE" në listë, zgjidhni atë dhe klikoni butonin "OK".
Do të shfaqet një dritare e re për futjen e argumenteve të funksionit. Në të do të shihni dy fusha: "Numri1" dhe "Numri2".
Në fushën e parë, vendosni adresat e qelizave në të cilat ndodhen vlerat numerike për llogaritjen. Kjo mund të bëhet si me dorë ashtu edhe me ndihmën e një mjeti të veçantë. Në rastin e dytë, klikoni në butonin e vendosur në anën e djathtë të fushës së hyrjes. Dritarja e Wizard do të shembet dhe do t'ju duhet të zgjidhni qelizat për llogaritjen me miun.
Nëse një gamë tjetër qelizash me të dhëna ndodhet diku tjetër në fletë, atëherë specifikojeni atë në fushën "Numri2".
Bëni futjen e të dhënave derisa të keni futur të gjitha të nevojshmet.
Klikoni butonin OK.

Pas përfundimit të hyrjes, dritarja Wizard do të mbyllet dhe rezultati i llogaritjes do të shfaqet në qelizën që keni zgjedhur që në fillim. Tani ju e dini mënyrën e dytë se si të llogaritni mesataren aritmetike në Excel. Por jo e fundit, kështu që ne vazhdojmë.

Metoda 3: Përmes shiritit të formulave

Kjo metodë, si të llogarisni mesataren aritmetike në Excel, nuk është shumë e ndryshme nga ajo e mëparshme, por në disa raste mund të duket më e përshtatshme, kështu që ia vlen ta renditni atë. Në pjesën më të madhe, kjo metodë ofron vetëm një mënyrë alternative për të thirrur Function Wizard.

Sapo të përfundojnë të gjitha veprimet e listës, para jush do të shfaqet dritarja Function Wizard, ku duhet të futni argumentet. Ju tashmë e dini se si ta bëni këtë nga metoda e mëparshme, të gjitha veprimet pasuese nuk janë të ndryshme.

Metoda 4: futja manuale e një funksioni

Nëse dëshironi, mund të shmangni ndërveprimin me magjistarin e funksionit nëse e dini formulën mesatare aritmetike në Excel. Në disa situata, futja e tij me dorë do ta përshpejtojë procesin e llogaritjes shumë herë.

Për të kuptuar të gjitha nuancat, duhet të shikoni sintaksën e formulës, duket kështu:

AVERAGE(adresa_qelize(numri), adresa_qelize(numri))

Nga sintaksa rrjedh se në argumentet e funksionit është e nevojshme të përshkruhet ose adresa e gamës së qelizave në të cilat ndodhen numrat që do të numërohen, ose vetë numrat që do të llogariten drejtpërdrejt. Në praktikë, përdorimi i kësaj metode është si më poshtë:

MESATAR (C4:D6;C8:D9)

Metoda 5: llogaritja sipas kushteve

zgjidhni qelizën në të cilën do të kryhet llogaritja;
klikoni në butonin "fut funksionin";
në dritaren e magjistarit që shfaqet, në listë, zgjidhni rreshtin "kur";
klikoni OK.

Pas kësaj, do të shfaqet një dritare për futjen e argumenteve të funksionit. Është shumë e ngjashme me atë që u demonstrua më parë, vetëm tani ka një fushë shtesë - "Kushti". Është në të që duhet të futet kushti. Pra, duke futur "> 1500", do të merren parasysh vetëm ato vlera që janë më të mëdha se ato të specifikuara.

17.02.2017

Excel është një spreadsheet. Mund të përdoret për të krijuar një sërë raportesh. Në këtë program, është shumë i përshtatshëm për të kryer llogaritje të ndryshme. Shumë prej tyre nuk përdorin as gjysmën e aftësive të Excel.

Mund t'ju duhet të gjeni vlerën mesatare të numrave në shkollë, si dhe gjatë punës. Mënyra klasike për të përcaktuar mesataren aritmetike pa përdorur programe është mbledhja e të gjithë numrave dhe më pas pjesëtimi i shumës që rezulton me numrin e termave. Nëse numrat janë mjaft të mëdhenj, ose nëse operacioni duhet të kryhet shumë herë për qëllime raportimi, llogaritjet mund të zgjasin shumë. Ky është një humbje joracionale e kohës dhe përpjekjes, është shumë më mirë të përdorni aftësitë e Excel.

Gjetja e mesatares aritmetike

Shumë të dhëna tashmë janë regjistruar fillimisht në Excel, por nëse kjo nuk ndodh, është e nevojshme të transferohen të dhënat në një tabelë. Çdo numër për llogaritjen duhet të jetë në një qelizë të veçantë.

Metoda 1: Llogaritni vlerën mesatare përmes "Function Wizard"

Në këtë metodë, ju duhet të shkruani një formulë për llogaritjen e mesatares aritmetike dhe ta zbatoni atë në qelizat e specifikuara.

Problemi kryesor i kësaj metode është se ju duhet të futni manualisht qelizat për çdo term. Nëse ka shumë numra, kjo nuk është shumë e përshtatshme.

Metoda 2: Llogaritja automatike e rezultatit në qelizat e zgjedhura

Në këtë metodë, llogaritja e mesatares aritmetike kryhet vetëm me disa klikime të miut. Shumë i përshtatshëm për çdo numër numrash.

Disavantazhi i kësaj metode është llogaritja e vlerës mesatare vetëm për numrat e vendosur aty pranë. Nëse termat e nevojshëm janë të shpërndara, atëherë ato nuk mund të zgjidhen për llogaritje. Nuk është e mundur as të zgjidhen dy kolona, me ç'rast rezultatet do të paraqiten veçmas për secilën prej tyre.

Metoda 3: Përdorimi i shiritit të formulës

Një mënyrë tjetër për të shkuar në dritaren e funksionit:

Mënyra më e shpejtë, në të cilën nuk keni nevojë të kërkoni për një kohë të gjatë në meny, keni nevojë për artikuj.

Metoda 4: Hyrja manuale

Nuk është e nevojshme të përdorni mjetet në menunë Excel për të llogaritur vlerën mesatare, mund të shkruani manualisht funksionin e nevojshëm.

Një mënyrë e shpejtë dhe e përshtatshme për ata që preferojnë të krijojnë formula me duart e tyre, në vend që të kërkojnë programe të gatshme në meny.

Falë këtyre veçorive, është shumë e lehtë për të llogaritur vlerën mesatare të çdo numri, pavarësisht nga numri i tyre, dhe gjithashtu mund të përpiloni statistika pa i llogaritur ato manualisht. Me ndihmën e mjeteve të programit Excel, çdo llogaritje është shumë më e lehtë për t'u bërë sesa në mendje ose duke përdorur një kalkulator.