01 от 01
Функция Excel SUMPRODUCT
Претеглена спрямо непретеглен среден преглед
Обикновено при изчисляването на средната или аритметичната стойност всеки брой има еднаква стойност или тегло.
Средната стойност се изчислява, като се добави диапазон от номера заедно и след това се раздели тази сума на броя на стойностите в диапазона .
Пример ще бъде (2 + 3 + 4 + 5 + 6) / 5, който дава непропорционална средна стойност от 4.
В Excel тези изчисления се извършват лесно с помощта на функцията AVERAGE .
Среднопретеглената, от друга страна, счита, че един или повече номера от диапазона са повече или имат по-голяма тежест от другите числа.
Например, някои марки в училище, като междинни и окончателни изпити, обикновено струват повече от редовни тестове или задания.
Ако се използва усредняване за изчисляване на крайната оценка на студента, средната и крайната изпити ще получат по-голяма тежест.
В Excel, претеглените средни стойности могат да бъдат изчислени чрез функцията SUMPRODUCT .
Как функционира функцията SUMPRODUCT
Това, което SUMPRODUCT прави, е да умножи елементите на две или повече масиви и след това да добави или сумира продуктите.
Например, в ситуация, при която две масиви с четири елемента се въвеждат като аргументи за функцията SUMPRODUCT:
- първият елемент на масива1 се умножава по първия елемент в масив2;
- вторият елемент на масива1 се умножава от втория елемент на масива2;
- третият елемент на масива1 се умножава с третия елемент на масива2;
- четвъртият елемент на масива1 се умножава от четвъртия елемент на масива2.
След това продуктите на четирите операции за умножение се сумират и връщат от функцията като резултат.
Синтаксис и аргументи за функцията Excel SUMPRODUCT
Синтаксисът на функцията се отнася до оформлението на функцията и включва името на функцията, скобите и аргументите.
Синтаксисът за функцията SUMPRODUCT е:
= SUMPRODUCT (масив1, масив2, масив3, ... масив255)
Аргументите за функцията SUMPRODUCT са:
array1: (задължително) първия аргумент на масива.
array2, array3, ... array255: (по избор) допълнителни масиви, до 255. С две или повече маси, функцията умножава елементите на всеки масив заедно и след това добавя резултатите.
- елементите на масива могат да бъдат препратки към местоположението на данните в работния лист или номерата, разделени от аритметични оператори - като знаци плюс (+) или минус (-). Ако номерата се въвеждат без да бъдат разделени от операторите, Excel ги третира като текстови данни. Тази ситуация е включена в примера по-долу.
Забележка :
Всички аргументи на масива трябва да са със същия размер. Или, с други думи, трябва да има един и същ брой елементи във всеки масив. Ако не, SUMPRODUCT връща #VALUE! стойност на грешката.
Ако някои масивни елементи не са числа - като например текстови данни - SUMPRODUCT ги третира като нули.
Пример: Изчислете претеглената средна стойност в Excel
Примерът, показан на изображението по-горе, изчислява претеглената средна стойност за крайната оценка на ученика, като използва функцията SUMPRODUCT.
Функцията изпълнява това чрез:
- умножаване на различните марки по техния индивидуален тегловен коефициент;
- добавяне на продуктите от тези операции по умножение заедно;
- разделя горната сума на общия коефициент на претегляне 7 (1 + 1 + 2 + 3) за четирите оценки.
Въвеждане на формула за претегляне
Както повечето други функции в Excel, SUMPRODUCT обикновено се въвежда в работен лист, използвайки диалоговия прозорец на функцията. Тъй като обаче формулата за претегляне използва SUMPRODUCT по нестандартен начин - резултатът на функцията се дели на фактора на теглото - формулата за претегляне трябва да бъде въведена в клетка на работен лист .
Следните стъпки бяха използвани за въвеждане на формула за претегляне в клетка С7:
- Кликнете върху клетката C7, за да станете активната клетка - мястото, където ще се покаже крайният знак на ученика
- Въведете следната формула в клетката:
= SUMPRODUCT (B3: В6, C3: С6) / (1 + 1 + 2 + 3)
Натиснете клавиша Enter на клавиатурата
- Отговор 78.6 трябва да се появи в клетка C7 - отговорът ви може да има повече десетични знаци
Непретеглената средна стойност за същите четири оценки ще бъде 76.5
Тъй като студентът имаше по-добри резултати за своите средни и последни изпити, теглото на средната стойност помогна да се подобри цялостната му оценка.
Формулани вариации
За да се подчертае, че резултатите от функцията SUMPRODUCT са разделени на сумата от теглата за всяка група за оценка, делителят - частта, която прави разделянето, е въведен като (1 + 1 + 2 + 3).
Общата формула за претегляне може да бъде опростена чрез въвеждане на номер 7 (сумата от теглата) като делител. Формулата тогава би била:
= SUMPRODUCT (B3: В6, C3: С6) / 7
Този избор е добър, ако броят на елементите в масива за претегляне е малък и те лесно могат да се добавят заедно, но той става по-малко ефективен, тъй като броят на елементите в масива за претегляне се увеличава, което допълнително усложнява добавянето им.
Друг вариант и може би най-добрият избор - тъй като той използва референции на клетки, а не числа в общия делител - би означавало да се използва функцията SUM за общата сума на делителя с формулата:
= SUMPRODUCT (B3: В6, C3: С6) / SUM (B3: В6)
Обикновено е най-добре да въведете по-скоро референтни клетки, отколкото реални числа, докато опростява актуализирането им, ако данните на формулата се променят.
Например, ако факторите за претегляне за Задания бяха променени на 0.5 в примера и за Тестовете до 1.5, първите две форми на формулата би трябвало да бъдат редактирани ръчно, за да се коригира делителят.
При третата вариация само данните в клетките B3 и B4 трябва да бъдат актуализирани и формулата ще преизчисли резултата.