Обикновено, когато изчислявате средна или аритметична стойност, всеки брой има еднаква стойност или тегло. Средната стойност се изчислява, като се добави диапазон от номера заедно и след това се раздели тази сума на броя на стойностите в диапазона. Среднопретеглената, от друга страна, счита, че един или повече номера в диапазона са повече или имат по-голяма тежест от другите числа.
Например, някои марки в училище, като междинни и окончателни изпити, обикновено струват повече от редовни тестове или задания. Ако се използва усредняване за изчисляване на крайната оценка на студента, средните и заключителните изпити ще получат по-големи тегла. В Excel можете да изчислявате претеглените средни стойности с помощта наSUMPRODUCT функция.
Как функционира функцията SUMPRODUCT
SUMPRODUCT умножава елементите на две или повече масиви и след това добавя продуктите. Например, в ситуация, в която две масиви с четири елемента са въведени като аргументи за SUMPRODUCT функция:
- Първият елемент на array1 се умножава от първия елемент в array2.
- Вторият елемент на масива1 се умножава от втория елемент на масива2.
- Третият елемент на array1 се умножава с третия елемент на array2.
- Четвъртият елемент на масива1 се умножава от четвъртия елемент на масива2.
След това продуктите на четирите операции за умножение се сумират и връщат от функцията като резултат.
02 от 04SUMPRODUCT Синтаксис и аргументи
Синтаксисът на функцията се отнася до оформлението на функцията и включва името на функцията, скобите и аргументите. Синтаксисът за SUMPRODUCT функцията е:
= SUMPRODUCT (масив1, масив2, масив3, … масив255)
Аргументите за функцията SUMPRODUCT са:
- масив 1: Първият аргумент на масива (задължително)
- Array2, array3, … array255: Допълнителни (опционални) масиви до 255. С две или повече маси, функцията умножава елементите на всеки масив заедно и след това добавя резултатите.
Елементите на масива могат да бъдат препратки към местоположението на данните в листа или номерата, разделени от аритметични оператори, като плюс (+) или минус (-) знаци. Ако въведете номера, които не са отделени от операторите, Excel ги третира като текстови данни.
- Всички аргументи на масива трябва да са със същия размер. С други думи, във всеки масив трябва да има един и същ брой елементи. Ако не, SUMPRODUCT връща #VALUE! стойност на грешката.
- Ако елементите на масив не са числа, като например текстови данни, SUMPRODUCT третира ги като нули.
Въвеждане на формула за претегляне
Нашият пример, показан в тази статия, изчислява претеглената средна стойност за крайната оценка на ученика, използвайки SUMPRODUCT функция; функцията изпълнява това чрез:
- Умножаване на различните марки според техния индивидуален тегловен коефициент.
- Добавяне на продуктите от тези операции за умножение заедно.
- Разделяне на горната сума от общия коефициент на тежест 7 (1 + 1 + 2 + 3) за четирите оценки.
Подобно на повечето други функции в Excel, обикновено влизате SUMPRODUCT в работен лист, използващ Формула Builder; тъй като формулата за претегляне използва SUMPRODUCT по нестандартен начин, обаче - резултатът на функцията е разделен на тегловия коефициент - трябва да въведете формула за претегляне в клетка на работен лист.
= SUMPRODUCT (B3: В6, C3: С6) / (1 + 1 + 2 + 3)
- Кликнете върху клетката C7 за да стане активната клетка (мястото, където ще бъде показана крайната оценка на ученика).
- Въведете горната формула в клетката.
- НатисниВъведете на клавиатурата.
- Отговорът78.6 трябва да се появи в клетката C7 (Вашият отговор може да има повече знаци след десетичната запетая).
Непретеглената средна стойност за същите четири оценки ще бъде 76.5. Тъй като студентът имаше по-добри резултати за своите средни и последни изпити, теглото на средния показател помогна да се подобри цялостната му оценка.
04 от 04SUMPRODUCT Вариации на формулата
Да се подчертае, че резултатите от 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 трябва да бъдат актуализирани и формулата ще преизчисли резултата.
