Объяснение СУММПРОИЗВАТА: умножьте вниз, затем добавьте

1. Что он делает, в одной строке

Перед объяснением: у вас есть столбец «Единицы» и столбец «Цена единицы», а столбец «Доход» отсутствует. Скажите, как бы вы получили общий доход, не добавляя его.

=SUMPRODUCT(Orders[Units], Orders[UnitPrice])      9890
=SUM(Orders[Revenue])                              9890

Идентичный. СУММПРОИЗВ сделал то же, что и столбец «Доход», в одной ячейке, без существования столбца. Первая строка — 4 × 220 = 880, вторая строка — 10 × 85 = 850 и так далее по всем шестнадцати строкам, а затем шестнадцать продуктов складываются вместе.

Часть, которую стоит усвоить, - это умножь первое, прибавь второе. Умножение сумм двух столбцов даст 101 × 2125, что является числом, не имеющим никакого смысла. Спаривание выполняется для каждой строки, и это вся функция.

Также требуется более двух диапазонов. SUMPRODUCT(a, b, c) умножает три значения в строке. Это имеет значение в данный момент, потому что условие — это всего лишь еще один диапазон чисел.

2. Число, для которого оно существует: средневзвешенное значение.

Перед пояснением: столбец Цена за единицу товара содержит шестнадцать цен. Кто-то усредняет это и сообщает среднюю цену. Скажите, описывает ли это число бизнес.

=AVERAGE(Orders[UnitPrice])                                       132.81
=SUMPRODUCT(Orders[Units], Orders[UnitPrice]) / SUM(Orders[Units])  97.92

Разрыв почти в 35. Оба арифметически верны и отвечают на разные вопросы.

AVERAGE относится к каждому из шестнадцати Заказы как одно: семь заказов на стол по 220, пять заказов на стулья по 85, четыре заказа на лампу по 40, что в среднем составляет шестнадцать одинаковых предметов. Взвешенная версия учитывает каждый из 101 Единицы Во-первых, это то, что означает «средняя цена» для любого, кто читает отчет о продажах. 9890 выручки свыше 101 единицы — это 97,92 за единицу, и вы можете это проверить: 9890 ÷ 101 = 97,92.

Причина, по которой простой средний показатель здесь высок, заключается в том, что дорогой продукт продается небольшими партиями, а дешевый — большими. Два заказа ламп по 12 и 15 единиц в невзвешенном среднем считаются двумя товарами, точно так же, как один заказ на стол из 2 единиц считается за один. Взвешенные заказы на лампы содержат 27 единиц, а настольные — 2.

Это самая ценная вещь, которую делает эта функция, и шаблон всегда имеет одну и ту же форму:

=SUMPRODUCT(weights, values) / SUM(weights)

Единицы измерения и цена. Численность и зарплата. Стоимость заказа и ставка скидки. Численность населения и на душу населения ничего. Всякий раз, когда вы усредняете ставку, цену или процент, где-то есть столбец веса, и его отсутствие — самая распространенная тихая ошибка в бизнес-отчетности.

Представьте себе последнее среднее значение, которое вы поставили перед кем-то. Что такое столбец веса и был ли он в формуле?

3. Условия – единицы и нули

Сравнение типа Region="North" не создает фильтр, он создает столбец со значениями ИСТИНА и ЛОЖЬ, по одному на строку. Умножьте этот столбец на другой, и TRUE будет иметь значение 1, а FALSE — как 0, поэтому каждая строка, не прошедшая проверку, вносит ноль.

=SUMPRODUCT((Orders[Region]="North") * Orders[Revenue])       2495

Доход Норта без вспомогательного столбца и СУММЕСЛИ. Каждая несеверная строка была умножена на 0 и исчезла из суммы.

Сложите условия таким же образом. Умножение — это И, сложение — это ИЛИ, точно так же, как в формула ФИЛЬТР, потому что это та же арифметика.

=SUMPRODUCT((Orders[Region]="East") * (Orders[Revenue]>600) * Orders[Revenue])
2440

=SUMPRODUCT(((Orders[Region]="East") + (Orders[Region]="West")) * Orders[Revenue])
4725

Проверьте второе: Восток — 3040, Запад — 1685, и 3040 + 1685 = 4725. Скобки вокруг добавленной пары не являются обязательными, и их забвение является наиболее распространенной ошибкой в ​​условии ИЛИ.

Одно предупреждение о построении OR со сложением: если строка может удовлетворять обоим условиям, ее флаг становится равным 2 и она учитывается дважды. Регион не может быть одновременно Востоком и Западом, поэтому здесь безопасно. «Доход более 500 или количество единиц более 5» небезопасно, и исправление состоит в сравнении с нулем: --(((a)+(b))>0).

4. Счет с двойным минусом

Чтобы подсчитывать, а не подводить итоги, вам нужно, чтобы сами флаги суммировались. Но СУММПРОИЗВ игнорирует логические значения, когда они приходят сами по себе, поэтому сначала флаги необходимо преобразовать в числа, а стандартным способом является два знака минус.

=SUMPRODUCT(--(Orders[Revenue]>600))                    8
=SUMPRODUCT(--(Orders[Units]>5), --(Orders[UnitPrice]<100))   8

Двойной минус, обычно называемый двойным унарным, — это не такой трюк, как арифметическое выполнение дважды. Первый минус превращает ИСТИНА в -1, второй превращает -1 в 1, и то же самое происходит с ЛОЖЬ и 0. Умножение на 1 будет работать одинаково, как и добавление 0; знаки минуса — это самый короткий способ записать это.

Восемь заказов превышают 600. Восемь заказов также имеют более пяти единиц и цену ниже 100, что представляет собой подсчет с двумя условиями без вспомогательного столбца и без COUNTIFS.

Смешивать две формы — это нормально, и это стоит знать: аргумент флага производит подсчет, а размещение рядом с ним диапазона значений дает условную сумму.

=SUMPRODUCT(--(Orders[Revenue]>600), Orders[Revenue])     6475

5. Сравнение двух столбцов построчно

Это задача, которую SUMIFS на самом деле не может выполнить, поскольку ее критерии сравнивают столбец с фиксированным значением, а не с другим столбцом.

=SUMPRODUCT(--(Orders[Revenue] > Orders[UnitPrice] * 3))     12

Двенадцать заказов стоят более чем в три раза больше их цены за единицу товара, и это еще один способ сказать, что двенадцать заказов были рассчитаны на сумму более трех единиц. Сравнение происходит один раз для каждой строки с собственными значениями этой строки.

Та же форма отвечает на вопросы, которые на самом деле возникают: сколько счетов было оплачено после установленного срока, --(PaidDate > DueDate); сколько позиций было ниже себестоимости, --(Price < Cost); сколько месяцев не удалось достичь цели, --(Actual < Target). Каждый из них представляет собой сравнение двух столбцов, и ни один из них не является SUMIFS.

6. Где он по-прежнему превосходит SUMIFS

SUMIFS появился намного позже SUMPRODUCT и взял на себя большую часть повседневной работы. Его легче читать, он быстрее на больших диапазонах и для простой условной суммы это правильный ответ. Свой собственный гид освещает этот случай должным образом.

Сюда по-прежнему относятся четыре ситуации.

Построчное сравнение, как в пятом разделе.

Условие, которое необходимо просчитать. SUMIFS не может применить функцию к тестируемому диапазону, поэтому группировка по месяцам означает добавление столбца месяца. СУММПРОИЗВ может вычислить его на месте:

=SUMPRODUCT(--(MONTH(Orders[OrderDate])=3), Orders[Revenue])     2585

Условие, возникающее в результате поиска. В таблице заказов нет столбца категории; Категория находится в таблице продуктов. СУММПРОИЗВ может выполнять поиск каждой строки внутри условия:

=SUMPRODUCT((INDEX(Products[Category],
             MATCH(Orders[Product], Products[Product], 0)) = "Lighting")
            * Orders[Revenue])
1600

same shape, "Furniture"                                          8290

1600 и 8290 в сумме дают 9890, поэтому каждый заказ был классифицирован. Это условный итог перекрестной таблицы в одной ячейке, без добавления столбца ни в одну из таблиц, и он состоит из шаблон поиска ты уже знаешь.

Взвешенная арифметика любого рода, это второй раздел и причина, по которой эту функцию вообще стоит изучать. Эта же форма формирует стоимость товаров из прайс-листа:

=SUMPRODUCT(Orders[Units],
            INDEX(Products[Cost], MATCH(Orders[Product], Products[Product], 0)))
6076

Это также двигатель любой модели оценки, в которой набор весов соответствует набору мер. Индекс риска, который окрашивает сам себя этот шаблон построен полностью.

7. Два способа поломки

Перед пояснением: один из ваших диапазонов имеет шестнадцать строк, а другой — пятнадцать. Предскажите, что произойдет.

=SUMPRODUCT(Orders[Units], G2:G16)      #VALUE!

Ошибка разных размеров. Здесь нет частичного спаривания и молчаливого усечения, что является наилучшим возможным поведением: несовпадение — это именно та вещь, которая привела бы к правдоподобному неправильному итогу в менее строгой функции. Когда вы увидите это, посчитайте строки в каждом аргументе.

Текст ведет себя по-разному в двух формах., и это действительно удивляет людей. Если между диапазонами стоят запятые, текст рассматривается как ноль, и формула возвращает ответ. Со звездочками текст не может быть умножен и в формуле возникают ошибки.

=SUMPRODUCT(Orders[Units], Orders[Rep])        0
=SUMPRODUCT(Orders[Units] * Orders[Rep])       #VALUE!

Толерантность в форме запятой звучит дружелюбнее и более опасна. Столбец, состоящий в основном из чисел с несколькими текстовыми записями, возвращает сумму, меньшую ровно на эти строки, без ошибок, что является той же ошибкой, что и Числа, хранящиеся в виде текста носить другую шляпу. Если общая сумма выглядит немного заниженной, проверьте типы, прежде чем проверять формулу.

Практическое правило: используйте запятые, когда каждый аргумент действительно числовой, и используйте звездочки, когда задействованы условия, то есть тогда, когда вам все равно нужна строгость.

8. Когда его не следует использовать

Три случая, если говорить прямо, потому что на странице о функции также должно быть указано, где она останавливается.

Простой условный итог. SUMIFS легче читать и быстрее проверять. Используйте СУММПРОИЗВ, когда СУММИФ не может выразить условие, не раньше.

Ссылки целыми столбцами на большом листе. СУММПРОИЗВ оценивает каждую строку, которую вы ему даете, поэтому A:A означает миллион строк на каждое условие и на формулу. В большой модели это классическая причина книги, которая пересчитывается в течение нескольких секунд после каждого нажатия клавиши. Наведите его на столбец таблицы.

Когда вам нужно увидеть строки. СУММПРОИЗВ возвращает одно число и ничего не показывает о том, какие строки внесли вклад. Если вопрос в том, «что заказывает», то это фильтр или FILTER формула, а не это.

Крайние случаи, которые ловят людей

Шесть, которые стоит знать.

Пустые ячейки. Рассматривается как ноль, поэтому пробел в столбце весов автоматически удаляет эту строку из средневзвешенного значения. Перед делением посчитайте пробелы.

ИЛИ условия, которые перекрываются. Добавление двух флагов дает 2 для строки, совпадающей с обоими, что приводит к двойному счету. Завернуть в --(( ... )>0) всякий раз, когда оба могут быть правдой.

Отсутствуют скобки вокруг условия. --Orders[Revenue]>600 это не то же самое, что --(Orders[Revenue]>600), и первый выдаёт скорее что-то уверенно неправильное, чем ошибку.

Ошибки в любом месте диапазона. Один #N/A в столбце приводит к ошибке всего СУММПРОИЗВ. Это правильное поведение, и его сокрытие превращает видимую проблему в тихое занижение.

Диапазоны, начинающиеся в разных строках. SUMPRODUCT(A2:A17, B3:B18) имеет одинаковый размер и объединяет каждую строку с неправильным партнером. Это дает чистую, правдоподобную и совершенно неверную цифру. Укажите оба столбца таблицы, и смещение станет невозможным.

Внутри него нестабильные функции. СУММПРОИЗВ, содержащий что-то, что постоянно пересчитывается, например функцию, считывающую сегодняшнюю дату, пересчитывая каждую строку при каждом изменении. Вычислите изменчивую часть один раз в отдельной ячейке и обратитесь к ней.