Përdorimi i Excel për të llogaritur një mesatare në bazë të kritereve

Ju mund të përdorni Excel për të llogaritur një mesatare. Imagjinoni këtë: Ju duhet të llogaritni një mesatare nga një listë numrash, duke përdorur vetëm numrat në listë që përputhen me një kusht. Me fjalë të tjera, llogaritni mesataren duke përdorur numrat nëse përputhen me një kriter të caktuar. Excel është plot me surpriza, dhe për këtë truk, ju mund të nxirrni një funksion të shkëlqyer të quajtur AVERAGEIF.

Figura e mëposhtme tregon një listë të punonjësve, së bashku me vitet kur janë punësuar, sa llogari kanë hapur deri më tani në 2016 dhe (për disa) një mesazh që thotë se po bëjnë një punë të shkëlqyer.

Në krye të fletës së punës është numri mesatar i llogarive të hapura për punonjës në vitin 2015. Më poshtë është numri i pritshëm i llogarive që do të hapen në vitin 2016. Mesatarja prej 44 për vitin 2015 është vetëm një numër statik. Viti 2015 ka përfunduar dhe kjo është mesatarja përfundimtare. Kështu është - vitin e kaluar! Po 2016? Këtu bëhet interesante.

Për të llogaritur numrin e pritshëm të llogarive për t'u hapur në vitin 2016, përdoret një llogaritje për të nxjerrë numrin si një mesatare bazuar në numrin e llogarive që janë hapur deri më tani për vitin. Kjo është një teknikë që menaxhmenti mund të përdorë për të rritur konkurrencën midis punonjësve. Me fjalë të tjera, në vend që thjesht të rregullohet një numër i pritshëm, si p.sh. 48 (që do të ishte një rritje prej 10 për qind në vitin 2015), numri i pritshëm i llogarive për t'u hapur bazohet në mënyrën se si punonjësit kanë performuar deri më tani për vitin.

Në qelizën C4, përdoret funksioni AVERAGEIF. Kështu futet:

=AVERAGEIF(C8:C16,">" & C3)

Ajo që bën kjo është të marrë mesataren e llogarive të hapura nga lista e llogarive për punonjës, duke përdorur vetëm numrat që janë më të mëdhenj se 44 të hapura në 2015. Kjo përcaktohet nga argumenti i dytë në funksionin (“>” & C3). i cili thotë të përcaktojë mesataren duke përdorur vetëm vlerat që janë më të mëdha se 44, të gjetura në qelizën C3.

Për shembull, Julie deri më tani ka hapur vetëm 40 llogari, kështu që numërimi i saj nuk përdoret në mesatare. Nga ana tjetër, Yolanda ka hapur 48 llogari, kështu që numërimi i saj përdoret në llogaritjen e mesatares.

Kur mesatarizon vetëm numrat më të mëdhenj se 44 i vitit 2015, kthehet një numër më i lartë për hapjet e pritshme për vitin 2016. Vlera e llogaritur për vitin 2016 që nga kjo pikë është 54. Pranë emrit të çdo punonjësi, mesazhi Punë e Madhe! shfaqet nëse ai punonjës po mposht mesataren. Mesazhin e marrin vetëm ata që kanë hapur të paktën 54 llogari.

Si? Duke përdorur funksionin IF! Në çdo qelizë ku shfaqet mesazhi, funksioni IF, krahason nëse numri i llogarive që punonjësi ka hapur deri tani është i barabartë ose më i madh se mesatarja e treguar në qelizën C4. Këtu është formula në qelizën D4 që tregon se Harrison po bën një punë të shkëlqyer:

=IF(C8>=$C$4, "Punë e shkëlqyer!", ")

Funksioni IF teston një kusht. Nëse kushti është i vërtetë, shfaqet përgjigja e parë. Nëse kushti nuk është i vërtetë, shfaqet përgjigja e dytë.

Deri këtu, shumë mirë! Në këtë kompani, shitësit agresivë shpërblehen dhe atyre që ngelen nuk u jepet asnjë lëvdatë.

Është koha për të barazuar fushën e lojës. Një gjë që nuk merret parasysh në këtë shembull është viti i punësimit. Në vitin 2016 janë punësuar disa punonjës, ndaj me drejtësi, numri i llogarive të hapura deri më tani në 2016 nuk duhet krahasuar me mesataren e lartë.

Figura e mëposhtme tregon një përditësim të fletës së punës. Funksioni i vetëm IF zëvendësohet me një grup funksionesh të ndërlidhura IF: IF e jashtme merr parasysh nëse viti i punësimit është 2016 (testi i gjendjes aktuale është për të parë nëse viti i punësimit është më i madh se 2015); dhe, nëse po, funksioni i brendshëm IF teston nëse numri i llogarive të hapura të punonjësit për vitin e deritanishëm është i barabartë ose më i madh se gjysma e mesatares së përgjithshme.

Tani tre rezultate të mundshme janë të mundshme për mesazhin:

Punë e mrekullueshme!
Vazhdoni të plasni! Viti nuk ka mbaruar akoma!
Asnjë mesazh

=IF((A8>$A$3), IF(C8>=($C$4/2), "Punë e shkëlqyeshme!","Vazhdo të kërcesh! Viti nuk ka mbaruar akoma!"),IF(C8>=$ 4 dollarë C "Punë e shkëlqyer!"))