Как использовать СУММПРОИЗВ ЕСЛИ в Excel (с примером)

8 апреля 2022 г.

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

Что такое СУММПРОИЗВ ЕСЛИ?

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

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

Связанный: Как включить навыки работы с Excel в свое резюме

Когда использовать СУММПРОИЗВ ЕСЛИ

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

Сравнение больших значений

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

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

Завершение финансовых документов

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

Проведение обширных статистических исследований

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

Объединение нескольких источников информации

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

Как использовать СУММПРОИЗВ ЕСЛИ

Используйте эти шаги при добавлении функции СУММПРОИЗВ ЕСЛИ в свои электронные таблицы. Эти шаги особенно полезны при использовании одного критерия для функции СУММПРОИЗВ ЕСЛИ:

1. Поймите правильный синтаксис

Синтаксис использования функции СУММПРОИЗВ ЕСЛИ в Excel:

=СУММПРОИЗВ(ЕСЛИ(диапазон критериев=критерий, диапазон значений1*диапазон значений2))

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

2. Настройте столы

Если у вас уже есть таблица с данными, которые вы хотите использовать, начиная с ячейки A1, вы можете создать вторую таблицу рядом, чтобы дать вашей функции место для возврата значений. Например, ваша основная таблица может состоять из трех столбцов и четырех строк. Ваша первая строка может включать заголовки для ваших данных, таких как товар, общая цена и количество проданных единиц. Под товаром у вас могут быть продукты с пометками “Обувь”, “Книги” и “Платья” с соответствующими данными о продажах в следующих столбцах.

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

3. Введите массив и критерий в формулу.

Если вы хотите определить общий объем продаж платьев, используя приведенную выше таблицу, вы должны ввести критерии в ячейку второй таблицы, которая включает название вашего товара. Например, если вы рассчитывали продажи платьев, вы можете ввести E4 в качестве критерия в свою формулу, которую вы вводите в ячейку F4. Вы бы заставили функцию сканировать ваши записи в строке A на предмет значения в E4, то есть «Платья», а затем дать ей указание умножить соответствующие значения в столбцах общей цены и проданных единиц. Эта функция будет выглядеть так:

“=СУММПРОИЗВ(ЕСЛИ($A$2:$A$4=E4,$B$2:$B$4*$C$2:$C$4))”

Эта функция указывает, что если в ячейках с A2 по A4 есть значение, равное значению в E4, то Excel умножит значение в соответствующей строке из ячеек B2 в B4 на значение в соответствующей строке из ячеек C2 в C4.

4. Добавьте несколько критериев для ваших нужд

Скопируйте и вставьте эту формулу в несколько ячеек по мере необходимости, чтобы вычислить похожие результаты. Например, формула на третьем шаге рассчитывала только четвертую строку, в которой были платья. Вы можете настроить формулу для расчета «Обувь», используя значение в E2 второй таблицы для критериев этой формулы в ячейках F2 и F4:

=СУММПРОИЗВ(ЕСЛИ($A$2:$A$4=E4,ЕСЛИ($A$2:$A$4=E2,$B$2:$B$4*$C$2:$C$4)))

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

Пример СУММПРОИЗВ ЕСЛИ

Вот пример, который следует учитывать при расчете СУММПРОИЗВ ЕСЛИ:

У вас есть электронная таблица продаж, которая начинается с ваших заголовков в первой строке и включает названия ваших продуктов в столбце A, их количество продаж в столбце B и цену продажи в столбце C, что будет выглядеть так:

НазванияКоличество продажЦенаДетские бутылочки605,00Подгузники20010,00Соски533,00Затем вы делаете вторую таблицу рядом с теми же названиями продуктов в столбце со столбцом «Общий объем продаж» справа от него. Если вы хотите рассчитать свои продажи пустышек в ячейке, чтобы перейти в ячейку F4 вашей второй таблицы, и использовать значение «Соски» в ячейке E4 в качестве критерия, ваша функция СУММПРОИЗВ ЕСЛИ будет выглядеть так:

=СУММПРОИЗВ(ЕСЛИ($A$2:$A$4=$E4,$B$2:$B$4*$C$2:$C$4))

Эта формула будет определять, равны ли какие-либо значения в столбце A значению в ячейке E4, то есть «Соски». Поскольку ячейка A4 соответствует критериям, функция умножает значение в ячейке B4 на значение в ячейке C4, или 53, умноженное на 3. Это даст вам результат 159 в ячейке F4, что означает, что вы продали пустышек на 159 долларов за этот период.

Обратите внимание, что ни одна из компаний, упомянутых в этой статье, не связана с компанией Indeed.