Excel — довольно популярная бухгалтерская программа. И специалисты в этой области часто используют функцию СУММПРОИЗВ в своем непростом деле. Это позволяет быстро рассчитывать заработную плату.
Как понимает внимательный читатель, с помощью этой формулы можно резюмировать два произведения. Факторы могут быть отдельными диапазонами или целыми массивами данных. Кроме того, эту формулу можно использовать вместо формулы массива. Но обо всем по порядку.
Функция СУММПРОИЗВ — подробное описание
Несмотря на кажущуюся на первый взгляд простоту, эта функция не так проста. А также спектр его применения. Это потому, что он выполняет несколько действий одновременно. Таким образом, с его помощью вы можете суммировать значения на основе определенных критериев. Это также позволяет вам избавиться от необходимости использовать формулы массива в вашей работе, которые чаще всего приходится использовать для стандартной функции СУММ.
Сами методы следующие:
- Определение суммы произведений массива.
- Проверка стоимости на соответствие определенным критериям, получение их произведений и суммирование.
- Используйте вместо формулы массива.
На самом деле существует гораздо больше возможных вариантов методов использования функции СУММПРОИЗВ. Опытный пользователь Excel может легко комбинировать несколько функций, чтобы таблица работала так, как он хочет.
Синтаксис функции СУММПРОИЗВ
В качестве аргументов этой функции используется набор диапазонов, которые сначала умножаются друг на друга, а затем полученные результаты складываются. Аргументы разделяются точкой с запятой. Важно отметить, что массивы не могут быть разных типов. Проще говоря, вы можете использовать только вертикальные или только горизонтальные диапазоны.
Давайте рассмотрим наиболее простой для понимания пример использования этой функции. А дальше мы будем постепенно усложнять задачу, демонстрируя все новые и новые возможности ее использования.
Итак, у нас есть таблица, в которой заранее известны значения длины и ширины различных прямоугольников. Нам поставили задачу определить сумму их площадей. Вы можете обойтись без функции СУММПРОИЗВ, вычислив площадь, вручную умножив длину на ширину и затем сложив полученные значения. В результате получается такой громоздкий стол.
Однако вы можете значительно упростить задачу, используя функцию СУММПРОИЗВ, используя два аргумента: диапазон всех длин и диапазон всех значений ширины. После этого программа сама выполнит все необходимые действия.
Мы видим, что результат использования функции СУММПРОИЗВ идентичен.
Внимание! Мы использовали только значения длины и ширины в качестве аргументов функции. Столбец с областью для каждого оставлен для визуального сравнения функций СУММ и СУММПРОИЗВ.
Функция СУММПРОИЗВ с условием
В чистом виде функция СУММПРОИЗВ используется редко. Особенно в бухгалтерском учете. Трудно представить себе ситуацию, в которой непосредственно каждый день будет полезен подсчет суммы произведений. Однако бывают и такие ситуации. Например, если ставится задача умножить обменный курс на сумму в долларах, то получите общий финансовый показатель, основанный на стоимости каждого продукта в национальной валюте. Но такая задача по-прежнему возникает довольно редко.
Кроме того, эта формула очень часто используется для отображения значений в зависимости от определенных условий.
гораздо проще рассмотреть это на реальном примере. Допустим, у нас есть небольшой график расходов организации за месяц. Перед нами стоит задача определить, сколько денег было потрачено соответственно за первые два месяца года в сумме по всем статьям расходов.
Чтобы сделать это с помощью этой функции, вам нужно использовать ее и вначале отметить два условия. Каждое из условий заключено в круглые скобки. Поскольку в нашем случае должны выполняться одновременно два критерия, мы использовали символ звездочки (*). Синтаксис можно увидеть на этом скриншоте. Вам просто нужно подставить соответствующие значения для вашего случая в нужные места. И готово!
Давайте подробнее рассмотрим темы:
- Первое условие написано в первых скобках.
- Второе условие записано между вторыми скобками.
- Третий аргумент — это массив, из которого нужно добавить.
Итоговый результат 3700 руб. Если распространить эту формулу на остальные строки и заменить термины в каждой из них, можно получить конкретные значения для другого отчетного периода или другой статьи расходов. К сожалению, лучшего способа автоматизировать этот процесс нет. Придется вручную изменить некоторые индикаторы. Но это жизнь. Правда, если вы научитесь писать макросы, вы сможете достичь любого уровня автоматизации при работе с электронными таблицами.
Сравнение в функции СУММПРОИЗВ
Сравнение — один из способов использования функции СУММПРОИЗВ. Он работает так, как если бы он был основан на политике. Намного проще сразу продемонстрировать работу сравнения в функции СУММПРОИЗВ на конкретном примере. Допустим, мы усложнили задачу. Теперь нам не нужно определять все затраты, только небольшие. Допустим, это затраты, размер которых меньше 1000 руб.
Мы используем те же аргументы в функции, но добавляем к ним оператор сравнения. То есть в нашем случае — D: D
Что это за индикатор? И это та тысяча, на которую в начале года купили карандаши. И после добавления дополнительного критерия ответ был следующим.
Теперь давайте снова расширим формулу на все оставшиеся ячейки и частично отредактируем информацию. Далее мы увидим общую сумму, потраченную на каждую статью расходов.
Стандартное использование СУММПРОИЗВ
Конечно, нет ничего скучнее и обыденнее, чем использование стандартной функции СУММПРОИЗВ. И все это очень просто и в то же время понятно. Однако мы приводим еще один пример и общую формулу для этой функции.
= СУММПРОИЗВ (Матрица1; Матрица2; …)
То есть функция складывает по два числа из каждого массива попарно, а затем умножает их друг на друга. Преимущество этой функции в том, что вы можете обойтись без дополнительных столбцов, как описано в примере выше. Все необходимые операции выполняются автоматически. Все можно выразить простой формулой.
Количество доступных диапазонов ограничено 255. Но этого более чем достаточно. Главное требование — они должны быть одного размера.
По сути, функция похожа на это выражение.
= B2 * DO2 + B3 * DO3 + B4 * DO4 + B5 * DO5
Логические связки И и ИЛИ (AND и OR)
В приведенном выше примере мы описали, как можно создать логическую связку And. Но что, если нам нужен хотя бы один из критериев для соответствия описанным ячейкам? В этом случае вам нужно использовать знак плюса, как показано на этом снимке экрана.
Нахождение суммы произведений элементов массивов
Теперь давайте более подробно объясним, как можно использовать функцию для нахождения суммы произведений элементов массива.
Синтаксис такой же, потому что эта формула действительно работает с массивами данных. Поэтому имеет большие перспективы одновременно в разных сферах деятельности.
Эта функция выполняет поэлементные операции со всеми диапазонами (даже если их 200), которые являются частью массива.
Обязательным требованием к массивам является их одинаковый размер. Если массивы содержат другое количество элементов, будет возвращена ошибка # ЗНАЧ!.
Как правило, эта формула не предназначена для использования с нечисловыми значениями. В таких случаях они будут считаны равными нулю. Но в некоторых случаях вы можете работать с текстовыми значениями. Как правило, это когда речь идет о выполнении определенных условий. Разберемся в этой теме подробнее.
Суммирование и подсчет значений отвечающих критериям
Если мы напишем такую формулу, результат будет равен нулю: = СУММПРОИЗВ (A3: A6> 2). Если вы перейдете к строке формулы и выберите часть, которая написана в скобках, затем нажмите F9, вы получите матрицу: ИСТИНА: ИСТИНА и ИСТИНА: ЛОЖЬ.
Если получим такой результат, значит, все нормально.
Здесь необходимо учитывать один нюанс: если значение ИСТИНА равно единице, а ЛОЖЬ равно нулю, это не означает, что с этими числами можно выполнять какие-либо математические операции. Для этого нужно использовать два знака минус, которые находятся рядом друг с другом. В этом случае массив будет преобразован в числовую форму, и вы сможете выполнять с ним математические операции.
Вопрос в том, как можно использовать критерии по отношению к текстовым значениям. Вот простая формула, после которой все сразу становится понятно.
= SUMPRODOTTO (- (B3: B6 = «яблоки»))
То есть мы заключаем слово в кавычки, а затем просто определяем необходимые ячейки для этого текстового значения. Точно так же вы можете использовать логические операторы для управления диапазоном по нескольким критериям.
Проверка нескольких условий
Чтобы проверить соответствие нескольким условиям одновременно, критерии необходимо умножить или сложить (используйте знак +). То есть он использует логические операторы, описанные выше.
СУММПРОИЗВ как формула массива
Функцию СУММПРОИЗВ также можно использовать как формулу массива. Когда это делать? Первый, когда нужно произвести операции с большим количеством значений. В этом случае вы не можете использовать формулу массива СУММ, а просто замените ее функцией СУММПРОИЗВ. Есть несколько примеров того, как вы можете использовать это на практике.
= СУММПРОИЗВ (- ПУСТОЙ (D2: D23)) — в этом случае в диапазоне ищутся пустые ячейки, которые затем подсчитываются.
= СУММПРОИЗВ ((A3: A6> СРЕДНЕЕ (A3: A6)) * (A3: A6)) — здесь выбираются значения больше среднего арифметического, затем вычисляется их сумма.
Подсчет по данным из закрытого файла
А теперь о главном. Как только вы это узнаете, вы, наконец, влюбитесь в эту функцию. Дело в том, что он умеет находить информацию в тех файлах, которые в данный момент не открыты. Как известно, многие другие функции умеют искать только в тех книгах, которые ранее были открыты на компьютере. В этом случае пользователь может даже не открывать их. Это особенно полезно, когда на компьютере мало ресурсов (например, таблица большая, в браузере открыто много необходимых параллельных вкладок, а ОЗУ на компьютере мало). В этом случае эта функция будет очень кстати.
Вот скриншот, на котором мы получаем данные по восточному филиалу компании.
Как это работает в других формулах (вернее, не работает)? Если документ, из которого берется информация, в данный момент не открыт, появляется ошибка # ЗНАЧ !. В случае использования функции СУММПРОИЗВ, если вы попытаетесь прочитать данные из файла, вы сможете это сделать. Это открывает огромные возможности, поскольку позволяет использовать дешевые компьютеры для выполнения сложных вычислений или сравнений.
Таким образом, функция СУММПРОИЗВ позволяет использовать весь спектр функциональных возможностей электронных таблиц, от функций, для которых она была создана, и заканчивая нестандартными применениями. Как видите, ничего сложного в этом нет. Это намного проще в использовании, чем массивы функций (хотя в этой теме нет ничего сложного). Однако функция СУММПРОИЗВ полностью избавляет от необходимости использовать формулу массива для этого типа вычислений. И этому можно только радоваться.