Обобщение
Функцията SUMPRODUCT умножава диапазони или масиви заедно и връща сумата от продукти. Това звучи скучно, но SUMPRODUCT е невероятно универсална функция, която може да се използва за преброяване и сумиране като COUNTIFS или SUMIFS, но с по-голяма гъвкавост. Други функции могат лесно да се използват в SUMPRODUCT за допълнително разширяване на функционалността.
Предназначение
Умножете, след това сумирайте масивиВърната стойност
Резултатът от умножени и сумирани масивиСинтаксис
= SUMPRODUCT (масив1, (масив2), …)Аргументи
- array1 - Първият масив или диапазон за умножение, след което добавете.
- array2 - (по избор) Вторият масив или диапазон за умножение, след което добавете.
Версия
Excel 2003Бележки за употреба
Функцията SUMPRODUCT работи с масиви, но не изисква нормален синтаксис на масива (Ctrl + Shift + Enter) за въвеждане. Целта на функцията SUMPRODUCT е да умножава, а след това да сумира масиви. Ако е предоставен само един масив, SUMPRODUCT просто ще сумира елементите в масива. Могат да се доставят до 30 масива.
Когато за първи път се сблъскате с SUMPRODUCT, той може да изглежда скучен, сложен и дори безсмислен. Но SUMPRODUCT е удивително гъвкава функция с много приложения. Тъй като ще обработва масиви елегантно и без оплаквания, можете да го използвате за обработка на диапазони от клетки по умни, елегантни начини (вижте връзките към примери за формули на тази страница).
За да илюстрираме как работи SUMPRODUCT, ето няколко често срещани примера.
ПРОДУКТ за условни суми и отброявания
Да приемем, че имате някои данни за поръчки в A2: B6, със състояние в колона A, продажби в колона B:
| A | Б. | |
| 1 | Щат | Продажби |
| 2 | UT | 75 |
| 3 | CO | 100 |
| 4 | TX | 125 |
| 5 | CO | 125 |
| 6 | TX | 150 |
Използвайки SUMPRODUCT, можете да преброите общите продажби за Тексас ("TX") с тази формула:
=SUMPRODUCT(--(A2:A6="TX"))
И можете да сумирате общите продажби за Тексас ("TX") с тази формула:
=SUMPRODUCT(--(A2:A6="TX"),B2:B6)
Забележка: Не се бъркайте с двойно отрицателно. Това е често срещан трик, използван в по-усъвършенстваните формули на Excel за принуждаване на TRUE и FALSE стойности на 1 и 0. Вижте повече по-долу …
За примера по-горе, тук е виртуално представяне на двата масива, първоначално обработени от SUMPRODUCT:
| масив1 | масив2 |
| НЕВЯРНО | 75 |
| НЕВЯРНО | 100 |
| ВЯРНО | 125 |
| НЕВЯРНО | 125 |
| ВЯРНО | 150 |
Each array has 5 items. The first array contains the TRUE / FALSE values that result from the expression A2:A6="TX", and the second array is the contents of B2:B6. Each item in the first array will be multiplied by the corresponding item in the second array. However, in the current state, the result of SUMPRODUCT will be zero because the TRUE and FALSE values will be treated as zero. We need the items in array1 to be numeric - they need to be "coerced" into ones and zeros. This is where the double-negative comes in.
By using the double negative -- (double unary, for you technical types) we are able to coerce the TRUE/FALSE into the numeric values one and zero, as shown in the virtual representation below. The last column "Product" represents the result of multiplying the two arrays together. The summed result, 275, is the value that SUMPRODUCT returns.
| array1 | array2 | Product | ||
| 0 | * | 75 | = | 0 |
| 0 | * | 100 | = | 0 |
| 1 | * | 125 | = | 125 |
| 0 | * | 125 | = | 0 |
| 1 | * | 150 | = | 150 |
| Sum | 275 | |||
Using the curly brace syntax for arrays, the example looks like this after coercion:
=SUMPRODUCT((0,0,1,0,1),(75,100,125,125,150))
and like this after multiplication:
=SUMPRODUCT((0,0,125,0,150))
This example expands on the ideas above with more detail.
SUMPRODUCT with other functions
SUMPRODUCT can use other functions directly. You might see SUMPRODUCT used with the LEN function to count total characters in a range, or with functions like ISBLANK, ISTEXT, etc. These are not normally array functions, but when they are given a range, they create a "result array". Because SUMPRODUCT is built to work with arrays, it it able to perform calculations on the arrays directly. This can be a good way to save space in a worksheet, by eliminating the need for a "helper" column.
For example, assume you have 10 different text values in A1:A10 and you want to count the total characters for all 10 values. You could add a helper column in column B that uses this formula: LEN(A1) to calculate the characters in each cell. Then you could use SUM to add up all 10 numbers. However, using SUMPRODUCT, you can write a formula like this:
=SUMPRODUCT(LEN(A1:A10))
When used with a range like A1:A10, LEN will return an array of 10 values. Then SUMPRODUCT will simply sum all values and return the result, with no helper column needed.
See examples below many other ways to use SUMPRODUCT.
Notes:
- SUMPRODUCT treats non-numeric items in arrays as zeros.
- Array arguments must must be the same size. Otherwise, SUMPRODUCT will generate a #VALUE! error value.
- Логическите тестове в масивите ще създадат TRUE и FALSE стойности. В повечето случаи ще искате да ги принудите към 1 и 0.
- SUMPRODUCT често може да използва директно резултата от други функции (вижте примерите за формули по-долу)
