Excel e ka bërë llogaritjen e mesatares së qelizave të shumta një detyrë shumë të lehtë – thjesht përdorni funksionin MESATARE (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 mesatarja 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 ta 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(
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:B9A që përmban pikët.
=СУММПРОИЗВ(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:
=СУММПРОИЗВ(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 SUM (SHUMË):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
Për funksionin SUM (SUM) ne do të specifikojmë vetëm një argument - një varg qelizash C2: C9. Mos harroni të mbyllni kllapat pasi të futni argumentin:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Gati! Pasi të keni shtypur tastin Shkruani, Excel do të llogarisë mesataren e ponderuar. Në shembullin tonë, rezultati përfundimtar do të jetë 83,6.
Si punon kjo
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 Testi 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 madhe të jetë pesha e detyrës, 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ë notën përfundimtare.
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ë, ne nuk kemi nevojë të shkruajmë një formulë kaq të gjatë sepse SUMPRODUCT (SUMPRODUCT) i bën të gjitha këto automatikisht.
Një funksion më vete 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) or /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 or 4,5, dhe formula do të vazhdojë të funksionojë si duhet. Është e mrekullueshme, apo jo?