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