Изтеглете примерната работна книга
Този урок демонстрира как да използвате Функция Excel SUMPRODUCT в Excel.
ПОДГОТОВКА Общ преглед на функциите
Функцията SUMPRODUCT умножава масиви от числа и сумира получения масив.
За да използвате функцията SUMPRODUCT Excel на работен лист, изберете клетка и въведете:
(Забележете как се появяват входовете на формулата)
Функция SUMPRODUCT Синтаксис и входове:
| 1 | = SUMPRODUCT (масив1, масив2, масив3) |
масив1 - масиви от числа.
Какво представлява функцията SUMPRODUCT?
Функцията SUMPRODUCT е една от най -мощните функции в Excel. Това е име, може да ви накара да повярвате, че е предназначено само за основни математически изчисления, но може да се използва за много повече.
Масиви
SUMPRODUCT изисква въвеждане на масиви.
И така, какво имаме предвид под „масив“? Масивът е проста група от елементи (напр. Числа), подредени в определен ред, точно като диапазон от клетки. Така че, ако имате числата 1, 2, 3 в клетки A1: A3, Excel ще прочете това като масив {1,2,3}. Всъщност можете да въведете {1,2,3} директно във формулите на Excel и той ще разпознае масива.
Ще говорим повече за масивите по -долу, но първо нека разгледаме един прост пример.
Основна математика
Нека разгледаме един основен пример за SUMPRODUCT, като го използваме за изчисляване на общите продажби.
Имаме нашата таблица с продукти и искаме да изчислим общите продажби. Ще се изкушите просто да добавите нова колона, да вземете продаденото количество * цена и след това да обобщите новата колона. Вместо това обаче можете просто да използвате функцията SUMPRODUCT. Нека преминем през формулата:
| 1 | = СУМПРОДУКТ (A2: A4, B2: B4) |
Функцията ще зареди диапазоните от числа в масиви, ще ги умножи един срещу друг и след това ще сумира резултатите:
| 1234 | = СУМПРОДУКТ ({100, 50, 10}, {6, 7, 5})= СУМПРОДУКТ ({100 * 6, 50 * 7, 10 * 5})= СУМПРОДУКТ ({600, 350, 50}= 1000 |
Функцията SUMPRODUCT успя да умножи всички числа вместо нас И да направи сумирането.
Среднопретеглена
Друг случай, в който е полезно да използвате SUMPRODUCT, е когато трябва да изчислите среднопретеглена стойност. Това най -често се случва, когато се занимавате с училищна работа, така че нека разгледаме следната таблица.
Можем да видим колко тестове, тестове и домашни работи струват за общата оценка, както и каква е текущата средна стойност за всеки конкретен елемент. След това можем да изчислим общата оценка чрез писане
| 1 | = СУМПРОДУКТ (B2: B4, C2: C4) |
Нашата функция отново умножава всеки елемент в масивите, преди да сумира общата сума. Това работи така
| 123 | = СУМПРОДУКТ ({30%, 50%, 20%}, {73%, 90%, 95%})= СУМПРОДУКТ ({22%, 45%, 19%})= 86% |
Множество колони
Друго място, което бихме могли да използваме SUMPRODUCT, е с още повече колони, които трябва да бъдат умножени една срещу друга. Нека разгледаме пример, в който трябва да изчислим обема в парчета дървен материал.
Вместо да създаваме помощна колона за изчисляване на общата продажба за всеки ред, можем да направим това с една формула. Нашата формула ще бъде
| 1 | = СУМПРОДУКТ (B2: B5, C2: C5, D2: D5) |
Първите елементи на всеки масив ще се умножат един срещу друг (напр. 4 * 2 * 1 = 8). След това втората (4 * 2 * 2 = 16) и 3rdи т.н. Като цяло, това ще произведе масив от продукти, които изглеждат като {8, 16, 16, 32). Тогава общият обем ще бъде сумата на този масив, 72.
Един критерий
Добре, нека добавим още един слой сложност. Видяхме, че SUMPRODUCT може да обработва масиви от числа, но какво ще кажете, ако искаме да проверим за критерии? Е, можете също да създадете масиви за булеви стойности (булевите стойности са стойности, които са TRUE или FALSE).
Например, вземете основен масив {1, 2, 3}. Нека създадем съответния масив, който показва дали всяко число е по -голямо от 1. Този масив би изглеждал като {FALSE, TRUE, TRUE}.
Това е изключително полезно при формулите, защото лесно можем да преобразуваме TRUE / FALSE в 1 / 0. Нека разгледаме един пример.
Използвайки таблицата по -долу, искаме да изчислим „Колко продадени единици са били червени?“
Можем да го направим с тази формула:
| 1 | = СУМПРОДУКТ (A2: A4, -(B2: B4 = "Червен")) |
„Дръж се! Какво има със символа за двоен минус там? " ти каза. Помните ли как казах, че можем да преобразуваме от True/False в 1/0? Правим това, като принуждаваме компютъра да извърши математическа операция. В този случай казваме „вземете отрицателната стойност и след това вземете отрицателната отново“. Записвайки това, нашият масив ще се промени по следния начин:
| 123 | {True, True, False}{-1, -1, 0}{1, 1, 0} |
Така че, обратно към пълната формула SUMPRODUCT, тя ще се зареди в нашите масиви и след това ще се умножи, така
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0})= СУМПРОДУКТ ({100, 50, 0})= 150 |
Обърнете внимание как 3rd елемент стана 0, защото всичко умножено по 0 става нула.
Множество критерии
Можем да заредим до 255 масива в нашата функция, така че със сигурност можем да заредим по повече критерии. Нека да разгледаме тази по -голяма таблица, където добавихме продадения месец.
Ако искаме да знаем колко продадени артикула са червени и ако бяхме през февруари, можехме да напишем формулата си така
| 1 | = СУМПРОДУКТ (A2: A4, -(B2: B4 = "Червен"), -(C2: C4 = "Февруари")) |
След това компютърът ще оцени нашите масиви и ще се умножи. Вече разгледахме как масивите True/False се променят на 1/0, така че засега ще пропусна тази стъпка.
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0}, {0, 1, 1})= СУМПРОДУКТ ({0, 50, 0})= 50 |
В нашия пример имахме само един ред, който отговаряше на всички критерии, но с реални данни може би сте имали няколко реда, които трябва да бъдат добавени заедно.
Сложни критерии
Добре, до този момент може да не сте впечатлени, защото всички наши примери биха могли да бъдат направени с помощта на други функции като SUMIF или COUNTIF. Сега ще направим нещо тези други функции не може направете. Преди това нашата колона „Месец“ имаше действителните имена на месеци. Ами ако вместо това имаше дати?
Не можем да направим SUMIF сега, защото SUMIF не може да се справи с необходимите ни критерии. Въпреки това SUMPRODUCT може да ни справи с манипулирането на масива и извършването на по -задълбочен тест. Вече манипулираме масиви, когато преведохме True/False на 1/0. Ще манипулираме този масив с функцията MONTH. Ето пълната формула, която ще използваме
| 1 | = СУМПРОДУКТ (A2: A4, -(B2: B4 = "Червен"), -(МЕСЕЦ (C2: C4) = 2)) |
Нека да разгледаме 3rd масив по -отблизо. Първо, нашата формула ще извлече номера на месеца от всяка дата в C2: C4. Това ще ни даде {1, 2, 2}. След това проверяваме дали тази стойност е равна на 2. Сега нашият масив изглежда като {False, True, True}. Отново правим двойния минус и имаме {0, 1, 1}. Сега сме отново на подобно място, което имахме в Пример 3, и нашата формула ще може да ни каже, че през февруари са продадени 50 броя, които са червени.
Двоен минус срещу умножение
Ако сте виждали функцията SUMPRODUCT в употреба преди, може да сте видели малко по -различна нотация. Вместо да използвате двоен минус, можете да пишете
| 1 | = СУМПРОДУКТ (A2: A4*(B2: B4 = "Червен")*(МЕСЕЦ (C2: C4) = 2)) |
Формулата все още ще работи по същия начин, просто ръчно казваме на компютъра, че искаме да умножим масивите. SUMPRODUCT така или иначе щеше да направи това, така че няма промяна в начина на работа на математиката. Извършването на математическата операция превръща нашите True/False в 1/0 същите. И така, защо разликата?
През повечето време това няма голямо значение и се свежда до предпочитанията на потребителя. Има поне един случай, при който е необходимо умножаване.
Когато използвате SUMPRODUCT, компютърът очаква всички аргументи (масив1, масив2 и т.н.) да бъдат с еднакъв размер. Това означава, че те имат еднакъв брой редове или колони. Можете обаче да направите това, което знаете като двуизмерно изчисление на масив с SUMPRODUCT, което ще видим в следващия пример. Когато направите това, масивите са с различни размери, така че трябва да заобиколим тази проверка за „същия размер“.
Две измерения
Всички предишни примери казват, че нашите масиви вървят в същата посока. SUMPRODUCT може да се справи с нещата, които вървят в две посоки, както ще видим в следващата таблица.
Ето нашата таблица с продадени единици, но данните се пренареждат там, където категориите вървят най -отгоре. Ако искаме да разберем колко артикула са червени и в категория А, можем да напишем
| 1 | = SUMPRODUCT ((A2: A4 = "Red")*(B1: C1 = "A")*B2: C4) |
Какво става тук ?? Оказва се, че ще се умножаваме в две различни посоки. Визуализирането на това е по -трудно само с написано изречение, така че имаме няколко изображения, които да ни помогнат. Първо, нашите критерии за ред (червен ли е?) Ще се умножат във всеки ред в масива.
| 1 | = СУМПРОДУКТ ((A2: A4 = "ЧЕРВЕН")*B2: C4) |
След това критериите за колоната (категория А ли е?) Ще умножат всяка колона
| 1 | = SUMPRODUCT ((A2: A4 = "Red")*(B1: C1 = "A")*B2: C4) |
След като и двата критерия свършат работата си, единствените останали ненули са 5 и 10. След това SUMPRODUCT ще ни даде общия брой 15 като наш отговор.
Помните ли как говорихме за масивите, които трябва да бъдат с еднакъв размер, освен ако не правите две измерения? Това беше частично вярно. Поглежда отново масивите, които използвахме във формулата. The височина на два от нашите масиви е еднакъв, а ширина два от нашите масиви са еднакви. Така че все още трябва да сте сигурни, че нещата ще се подредят правилно, но можете да го направите в различни измерения.
Две измерения и комплекс
Много пъти ни се представят данни, които не са в най -доброто оформление, подходящо за нашите формули. Можем да опитаме да го пренаредим ръчно или да сме по -умни с формулите си. Нека разгледаме следната таблица.
Тук имаме данните за нашите артикули и продажби, смесени заедно за всеки месец. Как бихме могли да разберем колко неща Боб е продал за цялата година?
За да направим това, ще използваме две допълнителни функции: SEARCH и ISNUMBER. Функцията SEARCH ще ни позволи да търсим нашата ключова дума „позиции“ в заглавните клетки. Резултатът от тази функция ще бъде или с число, или с грешка (ако ключовата дума не е намерена). След това ще използваме ISNUMBER за конвертиране че изход в нашите булеви стойности. Нашата формула ще изглежда по -долу.
Вече трябва да сте доста запознати с първия масив. Това ще създаде изход като {0, 1, 0, 1}. Следващият масив от критерии, за който току -що говорихме. Това ще създаде номер за всички клетки с „Елементи“ в тях и грешка за останалите {5, #N/A !, 5, #N/A!}. След това ISNUMBER преобразува това в булево {True, False, True, False}. Тогава, когато умножаваме, той ще запази само стойностите от първата и третата колона. След като всички масиви се умножат един срещу друг, единствените ненулеви числа, които ще имаме, са тези, подчертани тук:
| 1 | = SUMPRODUCT ((A2: A5 = "Bob")*(ISNUMBER (SEARCH ("Items", B1: E1))*B2: E5)) |
След това SUMPRODUCT ще добави всичко това и получаваме крайния резултат от 29.
СУМПРОДУКТ Or
Възникват много ситуации, в които бихме искали да можем да обобщим стойностите, ако нашата колона с критерии има една стойност ИЛИ друга стойност. Можете да постигнете това в SUMPRODUCT, като добавите два критериални масива един срещу друг.
В този пример искаме да добавим единици, продавани както за червено, така и за синьо.
Нашата формула ще изглежда така
| 1 | = СУМПРОДУКТ (A2: A7, (B2: B7 = "Червено")+(B2: B7 = "Синьо")) |
Нека да разгледаме масива от червени критерии. Той ще произведе масив, който изглежда така: {1, 1, 0, 0, 0, 0}. Масивът със сини критерии ще изглежда като {0, 0, 1, 0, 1, 0}. Когато ги добавите заедно, новият масив ще изглежда като {1, 1, 1, 0, 1, 0}. Можем да видим как двата масива са се смесили в един масив с критерии. След това функцията ще умножи това по първия ни масив и ще получим {100, 50, 10, 0, 75, 0}. Забележете, че стойностите за Green са нулирани. Последната стъпка от SUMPRODUCT е да добавим всички числа заедно, за да достигнем нашето решение от 235.
Една дума на предпазливост тук. Внимавайте кога масивите от критерии не се изключват взаимно. В нашия пример стойностите в колона В могат да бъдат или червени, или сини, но ние знаехме, че никога не може да бъде и двете. Помислете, ако бяхме написали тази формула:
| 1 | = СУМПРОДУКТ (A2: A7, (A2: A7> = 50)+(B2: B7 = "Синьо")) |
Нашето намерение е да намерим сини артикули, които са били продадени или са били в количество над 50. Тези условия обаче не са изключителни, тъй като един ред може да бъде над 50 в колона А и бъде синьо. Това би довело до това, че първият критериен масив изглежда като {1, 1, 0, 1, 1, 0}, а вторият масив от критерии е {0, 0, 1, 0, 1, 0}. Добавянето им заедно създава {1, 1, 1, 1, 2, 0}. Виждате ли как имаме 2 там? Ако бъде оставен сам, SUMPRODUCT ще завърши удвояване на стойността в този ред, променяйки 75 на 150, и ще получим грешен резултат. За да коригираме това, поставяме външна проверка на критериите в нашия масив, така:
| 1 | = СУМПРОДУКТ (A2: A7, -((A2: A7> = 50)+(B2: B7 = "Синьо")> 0)) |
Сега, след като двата вътрешни критериални масива са добавени заедно, ще проверим дали резултатът е по -голям от 0. Това се отървава от 2 -те, които имахме преди, и вместо това ще имаме масив като {1, 1, 1 , 1, 1, 0}, което ще даде правилния резултат.
СУМПРОДУКТ Точно
Повечето функции в Excel не са чувствителни към регистъра, но понякога трябва да можем да правим справка, като имаме предвид чувствителността към регистъра. Когато желаният резултат е числов, можем да постигнем това, като използваме EXACT вътре във функцията SUMPRODUCT. Помислете за следната таблица:
Искаме да намерим резултата за артикул „ABC123“. Обикновено функцията EXACT ще сравнява два елемента и ще връща булев изход, посочващ дали двата елемента са точно същото. Въпреки това, тъй като сме вътре в СУМПРОДУКТ, нашият компютър ще знае, че имаме работа с масиви и ще може да сравнява по един елемент с всеки елемент в масив. Нашата формула ще изглежда така
| 1 | = СУМПРОДУКТ (-ТОЧНО ("ABC123", A2: A5), B2: B5) |
След това функцията EXACT ще провери всеки елемент в A2: A5, за да види дали съответства на стойност и регистър. Това ще произведе масив, който изглежда като {0, 1, 0, 0}. Когато се умножи спрямо B2: B5, масивът става {0, 2, 0, 0}. След окончателното сумиране получаваме нашето решение на 2.
SUMPRODUCT в Google Таблици
Функцията SUMPRODUCT работи абсолютно същото в Google Sheets като в Excel:
ПРИМЕРИ ПРИМЕРИ В VBA
Можете също да използвате функцията SUMPRODUCT във VBA. Тип: application.worksheetfunction.sumproduct (масив1, масив2, масив3)
Изпълнение на следните VBA изявления
| 1 | Диапазон ("B10") = Application.WorksheetFunction.SumProduct (Range ("A2: A7"), Range ("B2: B7")) |
ще даде следните резултати
За аргументите на функцията (масив1 и т.н.) можете или да ги въведете директно във функцията, или да определите променливи, които да използвате вместо тях.
