Формула еслимн excel

ЕСЛИМН (функция ЕСЛИМН)

Функция ЕСЛИМН проверяет соответствие одному или нескольким условиям и возвращает значение для первого условия, принимающего значение ИСТИНА. Функцию ЕСЛИМН можно использовать вместо нескольких вложенных операторов ЕСЛИ. Ее гораздо проще читать при наличии нескольких условий.

Примечание: Эта функция доступна в Windows или Mac, если у вас есть Office 2019 или подписка на Office 365. Если у вас есть подписка на Office 365, убедитесь в том, что у вас установлена последняя версия Office.

Простой синтаксис

Как правило, синтаксис функции ЕСЛИМН выглядит следующим образом:

=ЕСЛИМН(условие1; значение1; [условие2; значение2]; [условиe3; значение3])

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

Условие, принимающее значение ИСТИНА или ЛОЖЬ.

Результат, возвращаемый, если условие лог_выражение1 принимает значение ИСТИНА. Может не указываться.

Условие, принимающее значение ИСТИНА или ЛОЖЬ.

значение_если_истина2. значение_если_истина127 (необязательно)

Результат, возвращаемый, если условие лог_выражениеN принимает значение ИСТИНА. Каждый аргумент значение_если_истинаN соответствует условию лог_выражениеN. Может не указываться.

Пример 1

Формула для ячеек A2:A6:

Расшифровка: ЕСЛИ(значение A2 больше 89, возвращается «A», если значение A2 больше 79, возвращается «B», и т. д. Для всех значений меньше 59 возвращается «F»).

Пример 2

В ячейке G7 находится следующая формула:

Расшифровка: ЕСЛИ(значение в ячейке F2 равно 1, возвращаемое значение в ячейке D2, если значение в ячейке F2 равно 2, возвращается значение в ячейке D3, и т. д. Если ни одно из других условий не выполняется, возвращается значение в ячейке D8.

Замечания

Чтобы задать результат по умолчанию, введите значение ИСТИНА для последнего аргумента лог_выражение. Если ни одно из других условий не выполняется, возвращается соответствующее значение. Это показано в строках 6 и 7 примера 1 (с 58 баллами).

 Если условие лог_выражение указывается без соответствующего аргумента значение_если_истина, появляется сообщение «Указано недостаточно аргументов».

 Если аргумент лог_выражение принимает значение, отличное от ИСТИНА или ЛОЖЬ, функция возвращает ошибку #ЗНАЧ!.

 Если условия со значением ИСТИНА не найдены, возвращается ошибка #Н/Д!

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Формула еслимн excel

На своём сайте я много внимания уделял Excel функциям извлечения данных ( ИНДЕКС , ВПР , ПОИСКПОЗ , ГПР ). Они, безусловно, очень важны, но есть ещё один краеугольный класс формул, без которых просто никуда. Это, конечно же, формулы подсчёта и суммирования.

Эта статья будет посвящена формулам СУММЕСЛИМН (SUMIFS), СЧЁТЕСЛИМН (COUNTIFS) и СРЗНАЧЕСЛИМН (AVERAGEIFS). Если вы раньше использовали формулы СУММЕСЛИ (SUMIF), СЧЁТЕСЛИ (COUNTIF) или СРЗНАЧЕСЛИ (AVERAGEIF), то, ознакомившись со статьёй, можете благополучно забыть об их существовании, так как функционально *ЕСЛИМН формулы кроют *ЕСЛИ формулы, как бык овцу.

Идея формулы

Прежде, чем погружаться в детали функционирования формулы, полезно понимать, что же она делает по самой своей сути. Смотрим на рисунок.

Мы располагаем таблицей, в которой необходимо просуммировать значения из столбца Количество , но с учётом двух критериев или фильтров, если угодно. Первый критерий — столбец Магазин должен содержать значение Центр , второй критерий — столбец Товар должен содержать значение Компьютер . Оба критерия действуют одновременно или, как говорят, по » И » (а не по » ИЛИ «). То есть нас интересует, сколько компьютеров продал центральный магазин.

Для этого в ячейку G7 мы помещаем формулу СУММЕСЛИМН , в которую передаём 5 параметров:

D3:D15 — диапазон, содержащий числа, которые мы собираемся суммировать

B3:B15 — диапазон, содержащий значения для сравнения с критерием 1

G3 — ячейка, содержащая, собственно, критерий 1 — значение, которое нас интересует — » Центр «

C3:C15 — диапазон, содержащий значения для сравнения с критерием 2

G5 — ячейка, содержащая критерий 2 — » Компьютер «

Формула вернёт нам значение 14, так как только 2 строки таблицы удовлетворяют обоим нашим критериям, и обе они содержат число 7. А теперь давайте дадим более формальное описание данной функции.

Синтаксис

Вот синтаксис формулы суммирования:

= СУММЕСЛИМН ( sum_range; criteria_range_1; criteria_1 [; criteria_range_2; criteria_2 [ . ] ] )

sum_range — первый параметр — всегда диапазон суммирования.

criteria_range_1 — диапазон для тестирования на соответствие критерию 1

criteria_1 — значение критерия 1. Значение может быть в виде:

• ссылки на ячейку, например E3
• выражения в виде текстовой строки, например » >10 «
• значения в виде числа или текста, например 45 или » Москва «

criteria_range_n и criteria_n — таких критериев, которые описываются всегда двумя параметрами, может быть до 127 штук. Принцип их организации остаётся неизменным.

Некоторые важные замечания

Количество параметров будет всегда нечётным, так как есть диапазон суммирования, а критерии идут парами. Это будет выглядеть как 3, 5, 7, 9 и т.д. параметров. Что будет соответствовать 1, 2, 3, 4 и т.д. критериев отбора строк для суммирования.

Диапазоны суммирования и диапазоны критериев должны всегда иметь одинаковый размер и одинаковую ориентацию (вертикальную или горизонтальную)

Диапазоны суммирования и диапазоны критериев могут не быть векторами (то есть располагаться более чем в одной строке или одном столбце), но опять же они должны соответствовать друг другу. То есть если диапазон суммирования имеет размер 10 на 3, то и все критериальные диапазоны должны быть строго 10 на 3. В целом, я не рекомендую подобную архитектуру ваших таблиц. Вы сэкономите себе массу сил, если критерии будут располагаться в отдельных столбцах. 1 столбец — 1 критерий.

СРЗНАЧЕСЛИМН и СЧЁТЕСЛИМН

Не откладывая в долгий ящик, сразу посмотрим на синтаксис формул СРЗНАЧЕСЛИМН и СЧЁТЕСЛИМН . У СРЗНАЧЕСЛИМН отличие только в том, что она не суммирует числа, а вычисляет по ним среднюю величину.

= СРЗНАЧЕСЛИМН ( avg_range; criteria_range_1; criteria_1 [; criteria_range_2; criteria_2 [ . ] ] )

А СЧЁТЕСЛИМН считает строки, а не числа, поэтому она не имеет аналога параметров sum_range или avg_range. Таким образом её параметры описывают только критерии и количество параметров всегда должно быть чётным, в отличие от её коллег.

= СЧЁТЕСЛИМН ( criteria_range_1; criteria_1 [; criteria_range_2; criteria_2 [ . ] ] )

Файл примера

Скачать

Критерии

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

СУММЕСЛИМН и умные таблицы

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

L4 и L5 содержат значения критериев.

Критерий для периода дат

А вот как задать период дат (с. по . ) при суммировании:

Обратите внимание на очень важную конструкцию:

Это не что иное, как выражение в виде текстовой строки. Причём это не статическое выражение, которое обычно приводят в справке по СУММЕСЛИМН , типа » >= 200000 «. Это выражение динамическое, то есть — гораздо более ценное и интересное. Всё что вам нужно — это вставить между знаком операции » >= » и ссылкой на ячейку с параметром — знак операции сложения строк » & «.

Динамическая операция сравнения

А почему бы не дать на откуп пользователю право определять операцию для критерия? В предыдущем примере пользователь мог выбирать порог N в критерии » >=N «. А в этом примере пользователь определяет и порог и саму операцию! А почему нет?

В L12 у нас находится выпадающий список, ссылающийся на 4 возможные операции: >, >=, ? » — заменяет любой символ, » * » — заменяет любое количество символов (в том числе и его отсутствие). Например критерий » *т* » сработает и на слово «Центр», и на слово «Восток». А, если бы существовала такая альтернатива, то сработал бы и на слово «опт» (это, как раз случай, когда вторая звёздочка в «*т*» заменила ноль символов справа от «т»).

Ответы на сложные вопросы

При помщи *ЕСЛИМН формул можно отвечать на довольно заковыристые вопросы. Например, какая доля холодильников в Центре была продана по ценам, превышающим средние цены холодильников по всем магазинам? А вот формула, которое это расчитывает:

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

Набор магазинов

А что делать, если нам нужны продажи двух магазинов? В рамках одной формулы это решить в общем случае нельзя (метод через символы подстановки не универсален, так как магазины могут иметь полностью уникальные имена). Ответ очевиден — надо использовать 2 формулы и просто сложить их результаты. Хотя понятно, что такой метод подойдёт далеко не во всех ситуациях.

Но есть и ограничения.

Надеюсь вы убедились, что *ЕСЛИМН достаточно гибки, но есть один узкий момент, который надо хорошо понимать. Например, я хочу знать, сколько раз я торговал в Центре, предоставляя покупателям скидку более или равную 5%? Как я могу это узнать? Только вычисляя по каждой строке отношение предоставленной скидки к базовой цене. Понимаете — по каждой строке надо делать вычисление и сравнивать с 5%! Вот такое формулы *ЕСЛИМН сами, без вашей помощи сделать не смогут, так как формула один раз вычисляет критерий, а потом сравнивает его со всеми строками критериального диапазона, а нам надо это делать динамически. Но никто нам не мешает организовать дополнительный столбец, который будет предварительно считать отношение скидки к базовой цене, а после этого можно уже остальную работу поручить формуле СЧЁТЕСЛИМН (см. пример 7).

Пустые ячейки

Если вы хотите, чтобы формула *ЕСЛИМН отреагировала на пустые ячейки, то следует использовать критерии «» (пустая строка) или «=». Например, посмотрите лист Blank нашего учебного файла:

= СУММЕСЛИМН ( E3:E10 ; B3:B10 ; «=» )

однако, если пустота ячейки является следствием работы формулы (например, формулы ЕСЛИ ), то такая ячейка отреагирует только на критерий «», то есть:

= СУММЕСЛИМН ( E3:E10 ; B3:B10 ; «» )

Не пустые ячейки

А вот, если критерием является то, что ячейка хоть что-то содержит, то можно использовать такую форму:

= СУММЕСЛИМН ( E3:E10 ; B3:B10 ; «<>» )

Ну что ж, я надеюсь, что вы почувствовали всю силу, сосредоточенную в формулах этого семейства. Удачи!

Функции ОБЪЕДИНИТЬ, УСЛОВИЯ (ЕСЛИМН)

Сведения о вопросе

Ответы (26) 

* Попробуйте выбрать меньший номер страницы.

* Введите только числа.

* Попробуйте выбрать меньший номер страницы.

* Введите только числа.

__
Если мой ответ вам помог, пожалуйста, отметьте его как ответ.

Disclaimer: В ответе могут быть ссылки на сайты не на сайты Microsoft, Все советы с таких сайтов Вы выполняете на свой страх и риск.

Этот ответ помог 2 польз.

Это помогло устранить вашу проблему?

К сожалению, это не помогло.

Великолепно! Спасибо, что пометили это как ответ.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв, он поможет улучшить наш сайт.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв.

Это помогло устранить вашу проблему?

К сожалению, это не помогло.

Великолепно! Спасибо, что пометили это как ответ.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв, он поможет улучшить наш сайт.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв.

Это помогло устранить вашу проблему?

К сожалению, это не помогло.

Великолепно! Спасибо, что пометили это как ответ.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв, он поможет улучшить наш сайт.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв.

Новая Функция ЕСЛИМН впервые появилась 16 февраля 2016 года в версии 1601 (сборка 6568.2025) .

Проверьте вашу версию пакета Office 2016 и обновите до последней версии 1803 (сборка 9126.2259).

Это помогло устранить вашу проблему?

К сожалению, это не помогло.

Великолепно! Спасибо, что пометили это как ответ.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв, он поможет улучшить наш сайт.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв.

Если кликнуть на соответствующую функцию, то откроется подробное описание данной функции и вы увидите с какой версии Excel эта функция начала использоваться.

С уважением, Феликс!

Это помогло устранить вашу проблему?

К сожалению, это не помогло.

Великолепно! Спасибо, что пометили это как ответ.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв, он поможет улучшить наш сайт.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв.

Если кликнуть на соответствующую функцию, то откроется подробное описание данной функции и вы увидите с какой версии Excel эта функция начала использоваться.

С уважением, Феликс!

Это помогло устранить вашу проблему?

К сожалению, это не помогло.

Великолепно! Спасибо, что пометили это как ответ.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв, он поможет улучшить наш сайт.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв.

Не совсем верно, внимательно изучите моё вам сообщение.

Функция ЕСЛИМН появилась впервые 16 февраля 2016 года, в Office 2016 версии 1601 (сборка 6568.2025) . Сейчас на дворе, 2018 год соответственно будет уже другая актуальная версия Office 2016.

Первоначально, имело примечание, цитата:

«Примечание: Эта функция доступна только при наличии подписки на Office 365 . Если у вас есть подписка на Office 365, убедитесь, что у вас установлена последняя версия Office .»

Это помогло устранить вашу проблему?

К сожалению, это не помогло.

Великолепно! Спасибо, что пометили это как ответ.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв, он поможет улучшить наш сайт.

Использование функции СУММЕСЛИМН в Excel ее особенности примеры

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

Синтаксис СУММЕСЛИМН и распространенные ошибки

Аргументы функции СУММЕСЛИМН:

1. Диапазон ячеек для нахождения суммы. Обязательный аргумент, где указаны данные для суммирования.
2. Диапазон ячеек для проверки условия 1. Обязательный аргумент, к которому применяется заданное условие поиска. Найденные в этом массиве данные суммируются в пределах диапазона для суммирования (первого аргумента).
3. Условие 1. Обязательный аргумент, составляющий пару предыдущему. Критерий, по которому определяются ячейки для суммирования в диапазоне условия 1. Условие может иметь числовой формат, текстовый; «воспринимает» математические операторы. Например, 45; « , = и др.).



Примеры функции СУММЕСЛИМН в Excel

У нас есть таблица с данными об оказанных услугах клиентам из разных городов с номерами договоров.

Предположим, нам необходимо подсчитать количество услуг в определенном городе с учетом вида услуги.

Как использовать функцию СУММЕСЛИМН в Excel:

1. Вызываем «Мастер функций». В категории «Математические» находим СУММЕСЛИМН. Можно поставить в ячейке знак «равно» и начать вводить название функции. Excel покажет список функций, которые имеют в названии такое начало. Выбираем необходимую двойным щелчком мыши или просто смещаем курсор стрелкой на клавиатуре вниз по списку и жмем клавишу TAB.
2. В нашем примере диапазон суммирования – это диапазон ячеек с количеством оказанных услуг. В качестве первого аргумента выбираем столбец «Количество» (Е2:Е11). Название столбца не нужно включать.
3. Первое условие, которое нужно соблюсти при нахождении суммы, – определенный город. Диапазон ячеек для проверки условия 1 – столбец с названиями городов (С2:С11). Условие 1 – это название города, для которого необходимо просуммировать услуги. Допустим, «Кемерово». Условие 1 – ссылка на ячейку с названием города (С3).
4. Для учета вида услуг задаем второй диапазон условий – столбец «Услуга» (D2:D11). Условие 2 – это ссылка на определенную услугу. В частности, услугу 2 (D5).
5. Вот так выглядит формула с двумя условиями для суммирования: =СУММЕСЛИМН(E2:E11;C2:C11;C3;D2:D11;D5).

Результат расчета – 68.

Гораздо удобнее для данного примера сделать выпадающий список для городов:

Теперь можно посмотреть, сколько услуг 2 оказано в том или ином городе (а не только в Кемерово). Формулу немного видоизменим: =СУММЕСЛИМН($E$2:$E$11;$C$2:$C$11;F$2;$D$2:$D$11;$D$5).

Все диапазоны для суммирования и проверки условий нужно закрепить (кнопка F4). Условие 1 – название города – ссылка на первую ячейку выпадающего списка. Ссылку на условие 2 тоже делаем постоянной. Для проверки из списка городов выберем «Кемерово»:

Результат тот же – 68.

По такому же принципу можно сделать выпадающий список для услуг.