Запуск функции: Выберите нужную ячейку > выберите Формулы вкладку > Математика и триггеры > СУММЕСЛИМН.
Или: выберите нужную ячейку > выберите Вставить функцию > Math & Trig > СУММЕСЛИМН для запуска функции.
В этой статье объясняется, как использовать функцию СУММЕСЛИМН в Excel 2019, 2016, 2013, 2010, Excel для Microsoft 365, Excel 2019 для Mac, Excel 2016 для Mac, Excel для Mac 2011 и Excel Online.
Ввод обучающих данных
Image
Первым шагом к использованию функции СУММЕСЛИМН в Excel является ввод данных.
Введите данные в ячейки от D1 до F11 рабочего листа Excel, как показано на изображении выше.
Функция СУММЕСЛИМН и критерии поиска (менее 275 заказов и агентов по продажам из восточного региона продаж) находятся в строке 12 под данными.
Учебные инструкции не включают шаги по форматированию рабочего листа. Хотя форматирование не помешает выполнению учебного пособия, ваш рабочий лист будет выглядеть иначе, чем в показанном примере. Функция СУММЕСЛИМН даст вам те же результаты.
Синтаксис функции СУММЕСЛИМН
Image
В Excel синтаксис функции относится к макету функции и включает имя функции, квадратные скобки и аргументы.
В функции можно указать до 127 пар Criteria_range / Criteria.
Запуск функции СУММЕСЛИМН
Image
Хотя функцию СУММЕСЛИМН можно просто ввести непосредственно в ячейку на листе, многим людям проще использовать диалоговое окно функции для ввода функции.
Нажмите ячейку F12, чтобы сделать ее активной ячейкой; F12 - здесь вы войдете в функцию СУММЕСЛИМН.
Перейдите на вкладку Формулы.
Нажмите Math & Trig в группе библиотеки функций.
Нажмите СУММЕСЛИМН в списке, чтобы вызвать диалоговое окно функции СУММЕСЛИМН.
Excel Online не имеет вкладки «Формулы». Чтобы использовать СУММЕСЛИМН в Excel Online, перейдите к Вставка > Функция.
Нажмите на ячейку F12, чтобы сделать ее активной ячейкой, чтобы вы могли ввести функцию СУММЕСЛИМН.
Нажмите СУММЕСЛИМН в списке, чтобы запустить функцию.
Данные, которые мы вводим в пустые строки диалогового окна, будут формировать аргументы функции СУММЕСЛИМН.
Эти аргументы сообщают функции, какие условия мы проверяем и какой диапазон данных суммировать, когда он удовлетворяет этим условиям.
Ввод аргумента Sum_range
Image
Аргумент Sum_range содержит ссылки на ячейки данных, которые мы хотим сложить.
В этом руководстве данные для аргумента Sum_range помещаются в столбец Total Sales.
Учебные шаги
Нажмите на строку Sum_range в диалоговом окне.
Выделите ячейки с F3 по F9 на листе, чтобы добавить ссылки на эти ячейки в строку Sum_range.
Ввод аргумента Criteria_range1
Image
В этом руководстве мы пытаемся сопоставить два критерия в каждой записи данных:
Торговые агенты из Восточного региона продаж
Торговые агенты, совершившие менее 275 продаж в этом году
Аргумент Criteria_range1 указывает диапазон ячеек, в которых функция СУММЕСЛИМН должна искать при попытке сопоставить первый критерий: восточный регион продаж.
Учебные шаги
Нажмите на строку Criteria_range1 в диалоговом окне.
Выделите ячейки с D3 по D9 на листе, чтобы ввести ссылки на эти ячейки в качестве диапазона, в котором функция будет искать.
Ввод аргумента Criteria1
Image
Первый критерий, которому мы должны соответствовать, это если данные в диапазоне D3:D9 равны Востоку.
Хотя фактические данные, такие как слово Восток, можно ввести в диалоговое окно для этого аргумента, обычно лучше добавить данные в ячейку на листе, а затем ввести ссылку на эту ячейку в диалоговое окно.
Учебные шаги
Нажмите на строку Criteria1 в диалоговом окне.
Нажмите ячейку D12, чтобы ввести ссылку на эту ячейку. Функция будет искать в диапазоне, выбранном на предыдущем шаге, данные, соответствующие этим критериям.
Как ссылки на ячейки повышают универсальность SUMIFS
Если ссылка на ячейку, например D12, введена в качестве аргумента критерия, функция СУММЕСЛИМН будет искать совпадения с любыми данными в этой ячейке на листе.
Поэтому, найдя объем продаж для восточного региона, будет легко найти те же данные для другого региона продаж, просто заменив восток на север или запад в ячейке D12. Функция автоматически обновится и отобразит новый результат.
Ввод аргумента Criteria_range2
Image
Аргумент Criteria_range2 указывает диапазон ячеек, в которых функция СУММЕСЛИМН должна найти соответствие второму критерию: торговые агенты, которые продали менее 275 заказов в этом году.
Нажмите на строку Criteria_range2 в диалоговом окне.
Выделите ячейки от E3 до E9 на листе, чтобы ввести эти ссылки на ячейки в качестве второго диапазона, в котором функция будет выполнять поиск.
Ввод аргумента Criteria2
Image
Второй стандарт, которому мы стремимся соответствовать, это если данные в диапазоне E3:E9 меньше 275 заказов на продажу.
Как и в случае с аргументом Criteria1, мы будем вводить в диалоговое окно ссылку на ячейку для местоположения Criteria2, а не сами данные.
Нажмите на строку Criteria2 в диалоговом окне.
Нажмите ячейку E12, чтобы ввести ссылку на эту ячейку. Функция будет искать в диапазоне, выбранном на предыдущем шаге, данные, соответствующие критериям.
Нажмите OK, чтобы завершить функцию СУММЕСЛИМН и закрыть диалоговое окно.
Нулевой ответ (0) появится в ячейке F12 (ячейке, где мы ввели функцию), потому что мы еще не добавили данные в поля Criteria1 и Criteria2 (C12 и D12). Пока мы этого не сделаем, функции нечего будет складывать, поэтому сумма остается равной нулю.
Добавление критериев поиска и завершение обучения
Image
Последним шагом в руководстве является добавление данных в ячейки рабочего листа, указанные как содержащие аргументы Criteria.
Справку по этому примеру см. на изображении выше.
В ячейке D12 введите East и нажмите клавишу Enter на клавиатуре.
В ячейке E12 введите <275 и нажмите клавишу Enter на клавиатуре (кнопка «<» - это символ меньше, чем в Excel).
Ответ $119, 719.00 должен появиться в ячейке F12.
Только две записи, те, что в строках 3 и 4, соответствуют обоим критериям, поэтому функция суммирует только общие продажи для этих двух записей.
Сумма 49 017 долларов и 70 702 долларов составляет 119 719 долларов.
Когда вы нажимаете на ячейку F12, полная функция=СУММЕСЛИМН(F3:F9, D3:D9, D12, E3:E9, E12) появляется в строке формул над рабочим листом.
Как работает функция СУММЕСЛИМН
Image
Обычно СУММЕСЛИМН работает со строками данных, называемыми записями. В записи все данные в каждой ячейке или поле в строке связаны между собой, например, название компании, адрес и номер телефона.
Аргумент СУММЕСЛИМН ищет определенные критерии в двух или более полях в записи, и только если он находит соответствие для каждого указанного поля, данные для этой записи суммируются.
В пошаговом руководстве SUMIF мы сопоставили единственный критерий агентов по продажам, которые продали более 250 заказов за год.
В этом руководстве мы установим два условия с помощью СУММЕСЛИМН: условие торговых агентов в восточном регионе продаж, у которых за последний год было менее 275 продаж.
Установку более двух условий можно выполнить, указав дополнительные аргументы Criteria_range и Criteria для СУММЕСЛИМН.
Аргументы функции СУММЕСЛИМН
Аргументы функции сообщают ей, какие условия проверять и какой диапазон данных суммировать, когда они удовлетворяют этим условиям.
Все аргументы в этой функции обязательны.
Sum_range - данные в этом диапазоне ячеек суммируются, когда найдено совпадение между всеми указанными Criteria и соответствующими им аргументами Criteria_range.
Criteria_range - группа ячеек, в которой функция должна искать соответствие соответствующему аргументу Criteria.
Criteria - это значение сравнивается с данными в соответствующем.
Criteria_range - фактические данные или ссылка на ячейку данных для аргумента.
Используйте функцию Excel СРЗНАЧЕСЛИ, чтобы игнорировать нули при поиске среднего значения для диапазона данных в электронной таблице. Обновлено для включения Excel 2019
Узнайте, как объединить функции МЕДИАНА и ЕСЛИ в Excel в формуле массива, чтобы найти средние значения данных, отвечающие определенным критериям. Обновлено для включения Excel 2019
