01 от 02
Сума на клетки, които попадат между две стойности
Lifewire
Функцията SUMPRODUCT в Excel е много гъвкава функция, която дава различни резултати в зависимост от начина, по който се въвеждат аргументите на функцията.
Обикновено, както подсказва името му, SUMPRODUCT умножава елементите на един или повече масиви, за да получи техния продукт и след това събира или сумира продуктите заедно.
Чрез коригиране на синтаксиса на функцията, обаче, тя може да се използва за сумиране само на данните в клетки, които отговарят на определени критерии.
От Excel 2007 програмата съдържа две функции - SUMIF и SUMIFS - които ще сумират данни в клетки, които отговарят на един или повече зададени критерии.
Понякога обаче със SUMPRODUCT е по-лесно да се работи, когато става въпрос за намиране на множество условия, свързани със същия диапазон, както е показано на изображението по-горе.
Синтаксис на функцията SUMPRODUCT за сумиране на клетки
Синтаксисът, използван за получаване на SUMPRODUCT за сумиране на данни в клетки, които отговарят на конкретни условия, е:
=SUMPRODUCT([условие1][условие2][масив])
условие1, условие2 - условията, които трябва да бъдат изпълнени, преди функцията да намери произведението на масива.
масив - непрекъснат диапазон от клетки
Пример: Сумиране на данни в клетки, които отговарят на множество условия
Примерът в изображението по-горе добавя данните в клетки в диапазона D1 до E6, които са между 25 и 75.
Въвеждане на функцията SUMPRODUCT
Тъй като този пример използва неправилна форма на функцията SUMPRODUCT, диалоговият прозорец на функцията не може да се използва за въвеждане на функцията и нейните аргументи. Вместо това функцията трябва да бъде въведена ръчно в клетка на работен лист.
- Щракнете върху клетка B7 в работния лист, за да я направите активната клетка;
- Въведете следната формула в клетка B7: =SUMPRODUCT(($A$2:$B$6>25)($A$2:$B$6<75)(A2:B6))
- Отговорът 250 трябва да се появи в клетка B7
- Отговорът беше получен чрез добавяне на петте числа в диапазона (40, 45, 50, 55 и 60), които са между 25 и 75. Общата сума е 250
Разбивка на формулата SUMPRODUCT
Когато се използват условия за неговите аргументи, SUMPRODUCT оценява всеки елемент от масива спрямо условието и връща булева стойност (TRUE или FALSE).
За целите на изчисленията Excel присвоява стойност 1 за тези елементи на масива, които са TRUE (отговарят на условието) и стойност 0за елементи на масива, които са FALSE (не отговарят на условието).
Например числото 40:
- е ВЯРНО за първото условие, така че в първия масив е присвоена стойност 1;
- е TRUE за второто условие, така че във втория масив се присвоява стойност 1.
Число 15:
- е FALSE за първото условие, така че стойност 0 е присвоена в първия масив;
- е TRUE за второто условие, така че във втория масив се присвоява стойност 1.
Съответните единици и нули във всеки масив се умножават заедно:
- За числото 40 - имаме 1 x 1, което връща стойност 1;
- За числото 15 - имаме 0 x 1, което връща стойност 0.
Умножение на единиците и нулите по диапазона
Тези единици и нули след това се умножават по числата в диапазона A2: B6
Това се прави, за да ни дадат числата, които ще бъдат сумирани от функцията.
Това работи, защото:
- 1 пъти всяко число е равно на оригиналното число
- 0 пъти всяко число е равно на 0
Така че завършваме с:
-
140=40
015=0
022=0
145=45
150=50
155=55
025=0
075=0
160=600100=0
Сумиране на резултатите
SUMPRODUCT след това обобщава горните резултати, за да намери отговора.
40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0=250
