Несмотря на то, что вы читаете эту статью в ознакомительных целях и детали, вероятно, исчезнут из вашей памяти, вы должны запомнить один важный момент: Функция SUMPROIZE в Excel работает с таблицами.
Функция СУММПРОИЗВ в Excel — применение, синтаксис, примеры
Excel — довольно популярная программа в бухгалтерии. И профессионалы в этой области часто используют функцию SUMPROIZE в своей нелегкой работе. Это позволяет им быстро рассчитать заработную плату.
Как может понять интуитивный читатель, эта формула также может быть использована для суммы двух продуктов. Множителями могут быть как отдельные диапазоны, так и целые наборы данных. Кроме того, этот тип может быть использован в качестве замены типа массива. Но сначала о главном.
- Функция СУММПРОИЗВ — подробное описание
- Синтаксис функции СУММПРОИЗВ
- Функция СУММПРОИЗВ с условием
- Сравнение в функции СУММПРОИЗВ
- Стандартное использование СУММПРОИЗВ
- Логические связки И и ИЛИ (AND и OR)
- Нахождение суммы произведений элементов массивов
- Суммирование и подсчет значений отвечающих критериям
- Проверка нескольких условий
- СУММПРОИЗВ как формула массива
- Подсчет по данным из закрытого файла
Функция СУММПРОИЗВ — подробное описание
Хотя на первый взгляд это может показаться простым, эта функция не так проста. Он также не является очень универсальным в своем использовании. Это потому, что он делает много вещей одновременно. Например, его можно использовать для добавления значений на основе определенных критериев. Кроме того, она устраняет необходимость в формулах массива, которые очень часто встречаются в стандартной функции SUM.
Методы следующие:
- Определение суммы произведений массива.
- Проверка значения на предмет соответствия определенным критериям, получение их произведений и суммирования.
- Использование в качестве замены формулы массива.
На самом деле, существует множество других возможных вариаций методов использования функции SUMPROIZE. Опытный пользователь Excel может легко комбинировать несколько функций, чтобы придать электронной таблице нужный вид.
Синтаксис функции СУММПРОИЗВ
Функция принимает в качестве аргументов ряд диапазонов, сначала перемножает их вместе, а затем складывает результаты. Аргументы разделяются точкой с запятой. Важно отметить, что таблицы не могут быть разных типов. Проще говоря, можно использовать только вертикальные или горизонтальные области.
Для иллюстрации возьмем простейший пример этой функции. Затем мы будем постепенно усложнять задачу, демонстрируя все новые и новые особенности его использования.
Итак, у нас есть таблица, в которой заранее известны значения длины и ширины различных прямоугольников. Наша задача — определить сумму их площадей. Можно обойтись без SUMMprov и вычислить площадь путем умножения длины на ширину вручную, а затем сложить полученные значения. В результате получается очень громоздкая таблица.
Его можно упростить с помощью функции SUMMPROIZV, которая содержит два аргумента: диапазон всех значений длины и диапазон всех значений ширины. Затем программа выполняет все необходимые операции.
Как видите, результат идентичен при использовании функции SUMPROIZE.
Предупреждение. В качестве аргументов функции мы использовали только значения длины и ширины. Соответствующий столбец диапазона используется для сравнения функций SUMM и SUMMPROIZV.
Функция СУММПРОИЗВ с условием
Чистая функция SUMMPROIZE используется редко. Особенно в области бухгалтерского учета. Трудно представить себе ситуацию, когда подсчет суммы продуктов каждый день сразу же пригодится. Тем не менее, такие ситуации случаются. Например, когда цель состоит в том, чтобы умножить обменный курс на сумму в долларах, а затем получить экономический итог, основанный на стоимости каждого продукта в национальной валюте. Но такая задача все еще достаточно редка.
Сравнение в функции СУММПРОИЗВ
Сравнение является одним из применений функции SUMMproV. Он выполняется так же, как если бы он был основан на критерии. Гораздо проще объяснить, как работает сравнение в функции SMPROIZV, на конкретном примере. Предположим, что задача усложнилась. Теперь нам не нужно определять все затраты, а только незначительные. Предположим, что это затраты, которые составляют менее 1 000 рублей.
Мы используем те же аргументы в функции, но добавляем к ним реляционный оператор. То есть, в нашем случае — D:D.
Что это за число? А это тысяча, на которую в начале года были куплены карандаши. А после добавления еще одного критерия ответ будет следующим.
Теперь давайте снова распространим формулу на все остальные ячейки и изменим некоторые данные. Затем посмотрим на общую сумму, потраченную на каждую статью расходов.
Вычисление суммы произведения
Из названия самой операции ясно, что сумма произведений является сложением результатов умножения отдельных чисел. В Excel это можно сделать с помощью простой математической формулы или специальной функции SUM. Давайте рассмотрим эти методы по отдельности.
Большинство пользователей знают, что значительное количество математических операций можно выполнить в Excel, просто поместив символ «=» в пустую ячейку, а затем записав выражение в соответствии с правилами математики. Этот метод также можно использовать для нахождения суммы произведений. Программа сразу же рассчитывает продукты в соответствии с математическими правилами, а затем складывает их.
- Устанавливаем знак «равно» (=) в ячейке, в которой будет выводиться результат производимых вычислений. Записываем туда выражение суммы произведений по следующему шаблону: =a1*b1*…+a2*b2*…+a3*b3*…+… Например, таким способом можно рассчитать выражение: =54*45+15*265+47*12+69*78
Способ 2: работа со ссылками
Эту формулу можно заменить ссылкой на ячейки, в которых находятся продукты. Эти ссылки можно ввести вручную, но удобнее выбрать соответствующую ячейку, содержащую число после символа ‘=’, ‘+’ или ‘*’.
- Итак, сразу записываем выражение, где вместо чисел указаны ссылки на ячейки.
- Затем, чтобы произвести подсчет, кликаем по кнопке Enter. Результат вычисления будет выведен на экран.
Конечно, этот тип вычислений довольно прост и интуитивно понятен, но если таблица содержит много значений, которые нужно перемножить, а затем просуммировать, этот метод может занять много времени.
Способ 3: использование функции СУММПРОИЗВ
Для вычисления суммы итогов квотирования некоторые пользователи предпочитают использовать функцию SUMMPROIZE, которая была разработана специально для этой операции.
Название оператора говорит само за себя. Преимущество этого метода перед предыдущим заключается в том, что его можно использовать для обработки целых полей вместо того, чтобы обрабатывать каждое число или ячейку по отдельности.
Синтаксис этой функции следующий:
Аргументами этого оператора являются диапазоны данных. Они сгруппированы по наборам множителей. То есть, если исходить из описанной выше схемы (a1*b1*…+a2*b2*…+a3*b3*…+…), то первая таблица содержит множители группы a, вторая — группы b, третья — группы c и так далее. Эти ряды должны быть одного типа и длины. Они могут быть расположены вертикально или горизонтально. Всего этот оператор может работать с количеством аргументов от 2 до 255.
Формула SUMPROIZE может быть записана непосредственно в ячейку для отображения результата. Однако многим пользователям проще и удобнее выполнять вычисления с помощью мастера функций.
- Выделяем ячейку на листе, в которой будет выводиться итоговый результат. Жмем на кнопку «Вставить функцию». Она оформлена в виде пиктограммы и располагается слева от поля строки формул.
- После того, как пользователь произвел данные действия, запускается Мастер функций. В нем открывается список всех, за малым исключением, операторов, с которыми можно работать в Экселе. Чтобы найти нужную нам функцию, переходим в категорию «Математические» или «Полный алфавитный перечень». После того, как нашли наименование «СУММПРОИЗВ», выделяем его и жмем на кнопку «OK» .
- Запускается окно аргументов функции СУММПРОИЗВ. По количеству аргументов оно может иметь от 2 до 255 полей. Адреса диапазонов можно вбивать вручную. Но это займет значительное количество времени. Можно сделать несколько по-другому. Устанавливаем курсор в первое поле и выделяем с зажатой левой кнопкой мыши массив первого аргумента на листе. Таким же образом поступаем со вторым и со всеми последующими диапазонами, координаты которых сразу же отображаются в соответствующем поле. После того, как все данные введены, жмем на кнопку «OK» в нижней части окна.
- После этих действий программа самостоятельно производит все требуемые вычисления и выводит итоговый результат в ту ячейку, которая была выделена в первом пункте данной инструкции.
Примечания
- Аргументы, которые являются массивами, должны иметь одинаковые размерности. В противном случае функция СУММПРОИЗВ возвращает значение ошибки #ЗНАЧ!. Например, =СУММПРОИВ(C2:C10;D2:D5) возвращает ошибку, так как диапазоны не одного размера.
- В функции СУММПРОИВТ ненумерические записи массива обрабатывают их так, как если бы они были нулями.
- Для лучшей производительности не следует использовать суммпроив с полными ссылками на столбцы. Рассмотрим функцию =СУММПРОИВ(A:A;B:B), чтобы умножить 1 048 576 ячеек в столбце A на 1 048 576 ячеек в столбце B перед их добавлением.
Чтобы создать формулу в примере выше, введите =SUMPROIZE(C2:C5;D2:D5) и нажмите Enter. Каждая ячейка в столбце C умножается на соответствующую ячейку в той же строке в столбце D, и результаты складываются. Общая сумма продуктов составляет $78,97.
Чтобы ввести большую формулу, которая дает тот же результат, введите =C2*D2+C3*D3+C4*D4+C5*D5 и нажмите Enter. После нажатия клавиши Enter результат будет таким же: 78,97 USD. Ячейка C2 умножается на D2, результат прибавляется к результату ячейки C3, умноженному на ячейку D3, и так далее.
Пример 2
В следующем примере sumPRODUCT возвращает общий объем чистых продаж на одного сотрудника отдела продаж, который включает как общий объем продаж, так и расходы на одного сотрудника. В данном случае мы используем рабочий лист Excel, в котором вместо стандартных диапазонов Excel используются структурированные отчеты. Здесь видно, что строки по продажам, расходам и дистрибьюторам упорядочены по названиям.
Формула имеет вид: =SUMPROIM((((Table1Sales)+(Table1Expense))*(Table1Agent=B8)) и возвращает общую сумму всех продаж и расходов агента, перечисленных в ячейке B8.
Пример 3
В данном примере мы хотим найти общую сумму для определенного продукта, который был продан в определенном регионе. В этом случае, сколько вишен было продано в восточном регионе?
Вот формула: =СУММПРОИВ((B2:B9=B12)*(C2:C9=C12)*D2:D9). Сначала оно умножает количество вхождений восточного на количество совпадающих вишней. Наконец, она суммирует значения соответствующих строк в столбце Продажи. Чтобы узнать, Excel вычисляет формулу, выйдите из ячейки формулы, а затем перейдите в>Вычислить формулу>.
Сумма произведений с несколькими условиями
Обычно существует несколько способов выполнения одной и той же задачи в Microsoft Excel. Но когда речь идет о сравнении двух или более массивов, особенно в разных условиях, SUMPROIZE является наиболее эффективным, если не единственным решением. Ну, это либо тип LUTTER, либо тип массива.
Предположим, у вас есть список продуктов в столбце A, данные о запланированных продажах в столбце B и фактические продажи в столбце C. Ваша цель — выяснить, сколько товаров из общего ассортимента было продано меньше, чем планировалось. Для этого можно использовать один из следующих вариантов формулы SUMMPROIZE:
Где C2:C10 — фактические продажи, а B2:B10 — запланированные продажи.
Но что делать, если у вас более одного заболевания? Предположим, вы хотите подсчитать, как часто яблоки продавались хуже, чем планировалось. Решение состоит в том, чтобы добавить в формулу еще один критерий:
Или вы можете использовать следующий синтаксис:
Теперь давайте потратим немного времени, чтобы увидеть, что именно делают приведенные выше формулы. Я думаю, что стоит потратить время, потому что многие другие типы SUMMPROIZE работают по той же логике.
Как работает формула СУММПРОИЗВ с одним условием
Давайте начнем с более простого случая. Нам нужно сравнить числа в двух колонках строка за строкой и сообщить, как часто числа в колонке C меньше чисел в колонке B (т.е. план продаж не выполняется):
Здесь мы имеем булеву таблицу со значениями TRUE и FALSE, где TRUE означает, что заданное условие выполнено (т.е. значение в столбце C меньше значения в столбце B в той же строке), а FALSE означает, что условие не выполнено.
Двойное отрицание (—) преобразует логические значения TRUE и FALSE в единицы и нули: .
Другой способ преобразования логических значений в числа — умножить таблицу на 1:
Поскольку в данном случае используется только одна таблица, формула SUMMPROIZE просто складывает единицы в результирующей таблице, и мы получаем искомое число. Это не сложно, не так ли?
Как работает формула СУММПРОИЗВ с несколькими условиями
Когда формула SUMMPROIZE в Excel содержит две или более таблиц, она перемножает соответствующие элементы всех таблиц, а затем находит сумму этих произведений.
Как вы помните, мы использовали следующие формулы, чтобы узнать, как часто количество фактических продаж (колонка C) было меньше запланированных продаж (колонка B) яблок (колонка A):
Единственное техническое различие между этими формулами заключается в способе преобразования TRUE и FALSE в 1 и 0 с помощью операции двойного отрицания или умножения. В результате получаются две матрицы с единицами и нулями:
Операция умножения, выполняемая SUMMPROIZE, объединяет эти числа в таблицу. Поскольку при умножении на ноль всегда получается 0, а 1 появляется только при выполнении обоих условий, подсчитываются только те строки, в которых выполнены оба условия:
Как называются числа при умножении?
Как и в случае сложения и вычитания, числа умножения имеют свои названия. Первое число при умножении называется первым множителем. Второе число при умножении называется вторым множителем. Результат умножения называется произведением.
Произведение любого целого числа a и нуля равно нулю. Чтобы найти произведение нескольких чисел, нужно найти произведение двух первых чисел, умножить на третье число и так далее. Степенью целого числа a с натуральным показателем n (n>1) является произведением n множителей, где каждый множитель равен a.
Что обозначает произведение числа?
В арифметике умножение понимается как уменьшение суммы на одно и то же слагаемое. Например, обозначение 5*3 означает «5 сложить 3 раза», то есть это просто краткое обозначение для 5+5+5. Результат умножения называется произведением, а числа, которые умножаются, называются множителями или факторами.
Разность — результат вычитания; произведение — результат умножения; сумма — результат сложения; делитель — результат деления.
Что такое сумма и разность?
Разница является результатом вычитания. Сумма является результатом сложения. Сумма является результатом сложения.
Как назвать одним словом плюс, минус, деление и умножение? Екатерина Н. Соедините все эти слова с выражениями: математические или арифметические действия (операции). Сложение — это «сумма», вычитание — «разность», деление — «коэффициент», а умножение — «произведение».
