Как использовать функцию SUM & IF вместо этого для SUMPRODUCT или SUMIFS функции в Excel
В этой статье мы узнаем, как использовать функцию ЕСЛИ вместо СУММПРОИЗВ и СУММЕСЛИМН в Excel.
Сценарий:
Проще говоря, при работе с длинным разрозненным набором данных иногда нам нужно найти сумму чисел с некоторыми критериями по ней. Например, поиск суммы зарплат в определенном отделе или наличие нескольких критериев по дате, именам, отделу или даже числовым данным, таким как зарплаты ниже значения или количество выше значения. Для этого обычно используется функция СУММПРОИЗВ или СУММЕСЛИМН. Но вы не поверите, вы выполняете ту же функцию с функцией ЕСЛИ базовой функции Excel.
Как решить проблему?
Вы, должно быть, думаете, как это возможно, чтобы выполнять логические операции над массивами таблиц с помощью функции ЕСЛИ. Функция ЕСЛИ в Excel очень полезна, она поможет вам справиться с некоторыми сложными задачами в Excel или на любых других языках программирования. Функция IF проверяет условия на массиве, соответствующие требуемым значениям, и возвращает результат в виде массива, соответствующего условиям True как 1 и False как 0.
Для этой задачи мы будем использовать следующие функции:
-
link: / math-and-trig-excel-sum-function [функция СУММ] -
link: / tips-if-condition-in-excel [IF function]
Нам потребуются эти вышеупомянутые функции и некоторые основные принципы работы с данными. логические условия на массивы можно применять с помощью логических операторов. Эти логические операторы работают как с текстом, так и с числами. Ниже приведена общая формула. \ {} * фигурные скобки — это волшебный инструмент для выполнения формул массива с функцией ЕСЛИ.
Общая формула:
|
\{* =
(logical_1) (logical_2) … (logical_n) , sum_array ) ) }* |
Примечание. Для фигурных скобок (\ {}) Используйте Ctrl + Shift + Enter * при работе с массивами или диапазонами в Excel. По умолчанию в формуле будут созданы фигурные скобки. НЕ пытайтесь жестко кодировать фигурные скобки.
Логический 1: проверяет условие 1 на массиве 1 Логический 2: проверяет условие 2 на массиве 2 и так далее sum_array: array, выполняется сумма операции
Пример:
Все это может сбивать с толку. Итак, давайте протестируем эту формулу, запустив ее на примере, показанном ниже. Здесь у нас есть данные о доставленных товарах в разные города, а также соответствующие поля категорий и количества. Здесь у нас есть данные, и нам нужно найти количество файлов cookie, отправленных в Бостон, где их количество превышает 40.
Таблица данных и таблица критериев показаны на изображении выше. Для понимания мы использовали именованные диапазоны для используемых массивов. Именованные диапазоны перечислены ниже.
Здесь:
Город, определенный для массива A2: A17.
Категория, определенная для массива B2: A17.
Количество, определенное для массива C2: C17.
Теперь вы готовы получить желаемый результат, используя формулу ниже.
Используйте формулу:
|
\{ =
(City=»Boston») (Category=»Cookies») (Quantity>40) , Quantity)) }* |
Пояснение:
-
Город = «Бостон»: проверяет значения в диапазоне городов на соответствие с «Бостон».
-
Category = «Cookies»: проверяет значения в диапазоне категорий на соответствие с «Cookies».
-
Количество> 40: проверяет значения в диапазоне количества до мА. Количество — это массив, в котором требуется сумма.
-
Функция ЕСЛИ проверяет все критерии, а символ звездочки (*) умножает все результаты массива.
СУММ (ЕСЛИ (\ {0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0}, \ {33; 87; 58; 38; 54; 51; 28; 36; 28; 44; 23; 27; 43; 42; 33; 30}))
-
Теперь функция ЕСЛИ возвращает только количества, соответствующие единицам, а остальные игнорируются.
-
Функция СУММ возвращает СУММ.
Теперь количество, соответствующее единицам, складывается только для получения результата.
Как видите, возвращается количество 43, но в «Бостон» доставлено три заказа на печенье, имеющих количество 38, 36 и 43. Нам нужна сумма количества, когда количество превышает 40. Таким образом, формула возвращает только 43. Теперь используйте другие критерии, чтобы получить СУММУ для города: «Лос-Анджелес» и категории: «Бары» и количества меньше 50.
Используйте формулу *
|
\{* =
«Los Angeles») (Category=»Bars») (Quantity < 50), Quantity ) ) } |
Как видите, формула возвращает в качестве результата значения 86. Это сумма 2 заказов, удовлетворяющих условиям, имеющим количество 44 и 42.
Эта статья иллюстрирует замену link: / logic-formulas-excel-nested-if-function [вложенная формула ЕСЛИ] на один ЕСЛИ в формуле массива. Это можно использовать для упрощения сложных формул. Однако эта конкретная проблема может быть решена с помощью `link: / summing-excel-sumifs-function [SUMIFS] ʻor link: / summing-excel-sumproduct-function [SUMPRODUCT] `function.
Использование функции СУММПРОИЗВ:
Функция СУММПРОИЗВ возвращает сумму соответствующих значений в массиве. Таким образом, мы получим массивы, возвращающие единицы для значений оператора True и 0 для значений оператора False. Таким образом, последняя сумма будет соответствовать тому, где все утверждения верны.
Используйте формулу:
|
= ( — (City = «Boston») , — (Category = «Cookies») , — (Quantity > 40) , Quantity ) |
-: операция, используемая для преобразования всех значений ИСТИНА в 1 и Ложь в 0.
Функция СУММПРОИЗВ повторно проверяет СУММУ количества, возвращаемого функцией СУММ и ЕСЛИ, описанной выше.
Аналогично для второго примера результат остается таким же.
Как видите, функция СУММПРОИЗВ может выполнять ту же задачу.
Вот все заметки по использованию формулы.
Примечания:
-
Сумма_массив в формуле работает только с числами.
-
Если формула возвращает ошибку #VALUE, проверьте, должны ли присутствовать фигурные скобки в формуле, как показано в примерах в статье.
-
Отрицание (-) char изменяет значения, TRUE или 1 на FALSE или 0 и FALSE или 0 на TRUE или 1.
-
Такие операции, как равно (=), меньше, чем равно (⇐), больше (>) или не равно (<> *), могут выполняться в рамках применяемой формулы, только с числами.
Надеюсь, эта статья о том, как использовать функцию ЕСЛИ вместо СУММПРОИЗВ и СУММЕСЛИМН в Excel, является пояснительной. Дополнительные статьи о формулах суммирования можно найти здесь. Если вам понравились наши блоги, поделитесь ими со своими друзьями на Facebook. А также вы можете подписаться на нас в Twitter и Facebook. Мы будем рады услышать от вас, дайте нам знать, как мы можем улучшить, дополнить или усовершенствовать нашу работу и сделать ее лучше для вас. Напишите нам на [email protected]
Статьи по теме:
link: / summing-excel-sumproduct-function [Как использовать функцию СУММПРОИЗВ в Excel]: возвращает СУММ после умножения значений в нескольких массивах в Excel.
link: / summing-sum-if-date-is-between [SUM if date is between]: возвращает СУММУ значений между заданными датами или периодом в Excel.
link: / summing-sum-if-date-more-than-given-date [Сумма, если дата больше заданной даты]: * Возвращает СУММУ значений после заданной даты или периода в Excel.
link: / summing-2-way-to-sum-by-month-in-excel [2 способа суммирования по месяцам в Excel]: * Возвращает СУММУ значений за данный конкретный месяц в excel.
link: / summing-how-to-sum-multiple-columns-with-condition [Как суммировать несколько столбцов с условием]: * Возвращает СУММУ значений по нескольким столбцам, имеющим условие в Excel.
Популярные статьи:
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-Повышение-продуктивность [50 ярлыков Excel для повышения производительности]: быстрее справляйтесь с задачей. Эти 50 ярлыков заставят вас работать в Excel еще быстрее.
link: / formulas-and-functions-Introduction-of-vlookup-function [Как использовать функцию ВПР в Excel]: Это одна из наиболее часто используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листы. link: / tips-countif-in-microsoft-excel [Как использовать функцию СЧЁТЕСЛИ в Excel]: Подсчет значений с условиями с помощью этой удивительной функции. Вам не нужно фильтровать данные для подсчета определенных значений. Функция Countif важна для подготовки вашей приборной панели.
link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel]: Это еще одна важная функция информационной панели. Это поможет вам суммировать значения для конкретных условий.