Excel го направи пресметувањето на просекот на повеќе ќелии многу лесна задача - само користете ја функцијата Просечно (ПРОСЕК). Но, што ако некои вредности имаат поголема тежина од другите? На пример, во многу курсеви, тестовите имаат поголема тежина од задачите. За такви случаи, потребно е да се пресмета Просечна тежина.
Excel нема функција за пресметување на пондерираниот просек, но има функција која ќе го заврши најголемиот дел од работата за вас: СУМПРОДУКТ (СУМ ПРОИЗВОД). И дури и ако никогаш претходно не сте ја користеле оваа функција, до крајот на оваа статија ќе ја користите како професионалец. Методот што го користиме работи во која било верзија на Excel, како и во други табеларни пресметки како Google Sheets.
Ја подготвуваме масата
Ако сакате да пресметате пондериран просек, ќе ви требаат најмалку две колони. Првата колона (колона Б во нашиот пример) ги содржи оценките за секоја задача или тест. Втората колона (колона В) ги содржи тежините. Поголема тежина значи поголемо влијание на задачата или тестот врз конечната оценка.
За да разберете што е тежина, можете да ја замислите како процент од вашата конечна оценка. Всушност, тоа не е така, бидејќи во овој случај тежините треба да се соберат до 100%. Формулата што ќе ја анализираме во оваа лекција ќе пресмета сè правилно и не зависи од количината до која се собираат тежините.
Ја внесуваме формулата
Сега кога нашата табела е подготвена, ја додаваме формулата во ќелијата B10 (секоја празна ќелија ќе направи). Како и со секоја друга формула во Excel, започнуваме со знак за еднаквост (=).
Првиот дел од нашата формула е функцијата СУМПРОДУКТ (СУМ ПРОИЗВОД). Аргументите мора да бидат затворени во загради, па ги отвораме:
=СУММПРОИЗВ(
=SUMPRODUCT(
Следно, додадете ги функциските аргументи. СУМПРОДУКТ (SUMPRODUCT) може да има повеќе аргументи, но обично се користат два. Во нашиот пример, првиот аргумент ќе биде опсег на ќелии. Б2: Б9А што ги содржи оценките.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
Вториот аргумент ќе биде опсег на ќелии C2: C9, кој ги содржи теговите. Овие аргументи мора да се одделат со точка-запирка (запирка). Кога сè е подготвено, затворете ги заградите:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Сега да го додадеме вториот дел од нашата формула, која ќе го подели резултатот пресметан со функцијата СУМПРОДУКТ (SUMPRODUCT) со збирот на тежините. Подоцна ќе разговараме зошто е ова важно.
За извршување на операцијата за делење, ја продолжуваме веќе внесената формула со симболот / (права коса црта), а потоа напишете ја функцијата SUM (СУМ):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
За функцијата SUM (SUM) ќе наведеме само еден аргумент - опсег на ќелии C2: C9. Не заборавајте да ги затворите заградите откако ќе го внесете аргументот:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Подготвени! По притискање на копчето Внесете, Excel ќе го пресмета пондерираниот просек. Во нашиот пример, конечниот резултат ќе биде 83,6.
Како работи
Ајде да го разложиме секој дел од формулата, почнувајќи од функцијата СУМПРОДУКТ (SUMPRODUCT) за да се разбере како функционира. Функција СУМПРОДУКТ (SUMPRODUCT) го пресметува производот на резултатот на секоја ставка и неговата тежина, а потоа ги сумира сите добиени производи. Со други зборови, функцијата го наоѓа збирот на производите, па оттука и името. Значи за Задачи 1 помножи 85 со 5, и за Тестот помножи 83 со 25.
Ако се прашувате зошто треба да ги множиме вредностите во првиот дел, замислете дека колку е поголема тежината на задачата, толку повеќе пати треба да ја земеме предвид оценката за неа. На пример, Задача 2 изброи 5 пати и Завршниот испит – 45 пати. Затоа Завршниот испит има поголемо влијание врз конечната оценка.
За споредба, при пресметување на вообичаената аритметичка средина, секоја вредност се зема предвид само еднаш, односно сите вредности имаат еднаква тежина.
Ако можете да погледнете под хаубата на некоја функција СУМПРОДУКТ (SUMPRODUCT), видовме дека таа всушност верува во ова:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
За среќа, не треба да пишуваме толку долга формула затоа што СУМПРОДУКТ (SUMPRODUCT) сето ова го прави автоматски.
Функција сама по себе СУМПРОДУКТ (SUMPRODUCT) ни враќа огромен број − 10450. Во овој момент, вториот дел од формулата стапува во игра: /SUM(C2:C9) or /SUM(C2:C9), што го враќа резултатот во нормалниот опсег на резултати, давајќи го одговорот 83,6.
Вториот дел од формулата е многу важен бидејќи ви овозможува автоматски да ги коригирате пресметките. Запомнете дека тежините не треба да се собираат до 100%? Сето ова благодарение на вториот дел од формулата. На пример, ако зголемиме една или повеќе тежински вредности, вториот дел од формулата едноставно ќе се подели со поголемата вредност, што повторно ќе резултира со точен одговор. Или можеме да ги направиме тежините многу помали, на пример со наведување вредности како 0,5, 2,5, 3 or 4,5, а формулата сепак ќе работи правилно. Супер е, нели?