Суммирование по условию excel: Пример функции СУММЕСЛИ для суммирования в Excel по условию

СУММЕСЛИМН (функция СУММЕСЛИМН) — Служба поддержки Office

Функция СУММЕСЛИМН — одна из математических и тригонометрических функций, которая суммирует все аргументы, удовлетворяющие нескольким условиям. Например, с помощью функции СУММЕСЛИМН можно найти число всех розничных продавцов, (1) проживающих в одном регионе, (2) чей доход превышает установленный уровень.

Это видео — часть учебного курса Усложненные функции ЕСЛИ.

Синтаксис

СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)

Имя аргумента	Описание
Диапазон_суммирования    (обязательный аргумент)	Диапазон ячеек для суммирования.
Диапазон_условия1    (обязательный аргумент)	Диапазон, в котором проверяется Условие1. Диапазон_условия1 и Условие1 составляют пару, определяющую, к какому диапазону применяется определенное условие при поиске. Соответствующие значения найденных в этом диапазоне ячеек суммируются в пределах аргумента Диапазон_суммирования.
Условие1    (обязательный аргумент)	Условие, определяющее, какие ячейки суммируются в аргументе Диапазон_условия1. Например, условия могут вводится в следующем виде: 32, «>32», B4, «яблоки» или «32».
Диапазон_условия2, Условие2, …    (необязательный аргумент)	Дополнительные диапазоны и условия для них. Можно ввести до 127 пар диапазонов и условий.

Примеры

Чтобы использовать эти примеры в Excel, выделите нужные данные в таблице, щелкните их правой кнопкой мыши и выберите команду Копировать. На новом листе щелкните правой кнопкой мыши ячейку A1 и в разделе Параметры вставки выберите команду Использовать форматы конечных ячеек.

Проданное количество	Продукт	Продавец
5	Яблоки	Артем
4	Яблоки	Ольга
15	Артишоки	Артем
3	Артишоки	Ольга
22	Бананы	Артем
12	Бананы	Ольга
10	Морковь	Артем
33	Морковь	Ольга
Формула	Описание
=СУММЕСЛИМН(A2:A9; B2:B9; «=Я*»; C2:C9; «Артем»)	Суммирует количество продуктов, названия которых начинаются с Я и которые были проданы продавцом Артем. Для поиска совпадающих названий товаров в Criteria_range1 B2:B9 в нем используется поддиапный знак * в «Условия1»(«=A*»), а в Criteria_range2 C2:C9 — имя «Том». Затем функция суммирует соответствующие обоим условиям значения в диапазоне ячеек, заданном аргументом Диапазон_суммирования (A2:A9). Результат — 20.
=СУММЕСЛИМН(A2:A9; B2:B9; «<>Бананы»; C2:C9; «Артем»)	Суммирует количество продуктов, которые не являются бананами и которые были проданы продавцом по имени Артем. Она исключает бананы, используя<>в «Условия1»«<>Бананы»и ищет имя «Том»в Criteria_range2 C2:C9. Затем функция суммирует соответствующие обоим условиям значения в диапазоне ячеек, заданном аргументом Диапазон_суммирования (A2:A9). Результат — 30.

Распространенные неполадки

Проблема	Описание
Вместо ожидаемого результата отображается 0 (нуль).	Если выполняется поиск текстовых значений, например имени человека, убедитесь в том, что значения аргументов Условие1, 2 заключены в кавычки.
Неверный результат возвращается в том случае, если диапазон ячеек, заданный аргументом Диапазон_суммирования, содержит значение ИСТИНА или ЛОЖЬ.	Значения ИСТИНА и ЛОЖЬ в диапазоне ячеек, заданных аргументом Диапазон_суммирования, оцениваются по-разному, что может приводить к непредвиденным результатам при их суммировании. Ячейки в аргументе Диапазон_суммирования, которым присвоено значение ИСТИНА, оцениваются как 1. Ячейки, которым присвоено значение ЛОЖЬ, оцениваются как 0 (ноль).

Рекомендации

Необходимые действия	Описание
Использование подстановочных знаков	Подстановочные знаки, такие как вопросительный знак (?) или звездочка (*), в аргументах Условие1, 2 можно использовать для поиска сходных, но не совпадающих значений. Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (~) перед вопросительным знаком. Например, формула =СУММЕСЛИМН(A2:A9; B2:B9; «=Я*»; C2:C9; «Арте?») будет суммировать все значения с именем, начинающимся на «Арте» и оканчивающимся любой буквой.
Различия между функциями СУММЕСЛИ и СУММЕСЛИМН	Порядок аргументов в функциях СУММЕСЛИ и СУММЕСЛИМН различается. Например, в функции СУММЕСЛИМН аргумент Диапазон_суммирования является первым, а в функции СУММЕСЛИ — третьим. Этот момент часто является источником проблем при использовании данных функций. При копировании и изменении этих похожих формул нужно следить за правильным порядком аргументов.
Одинаковое количество строк и столбцов для аргументов, задающих диапазоны ячеек	Аргумент Диапазон_условия должен иметь то же количество строк и столбцов, что и аргумент Диапазон_суммирования.

К началу страницы

У вас есть вопрос об определенной функции?

Задать вопрос на форуме сообщества, посвященном Excel

Помогите нам улучшить Excel

У вас есть предложения по улучшению следующей версии Excel? Если да, ознакомьтесь с темами на портале пользовательских предложений для Excel.

См. также

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

Функция СУММЕСЛИ суммирует только те значения, которые соответствуют одному условию

Функция СУММКВ суммирует несколько значений, предварительно возведя каждое из них в квадрат

Функция СЧЁТЕСЛИ подсчитывает только те значения, которые соответствуют одному условию

Функция СЧЁТЕСЛИМН подсчитывает только те значения, которые соответствуют нескольким условиям

Функция ЕСЛИМН (Microsoft 365, Excel 2016 и более поздние 2016)

Общие сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Поиск ошибок в формулах

Математические и тригонометрические функции

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Суммирование ячеек в Excel по условию

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

Суммирование ячеек в Excel по условию

 

Если вы хотите узнать подробную инструкцию, как создать таблицу в Excel 2013 с точными размерами в см., вы можете прочитать статью об этом на нашем портале.

 

Функция «СУММ»

Данной функцией можно воспользоваться при обработке больших чисел, которые просто нужно сложить, не отделяя ячейки по каким-то критериям. Буквально, как «+» в калькуляторе. Для выполнения обычного сложения необходимо:

1. Выделить нужную вам ячейку, нажав на нее один раз левой кнопкой мыши.

   Щелкаем левой кнопкой мышки по пустой ячейке, где будем вводить функцию

2. Выбрать «fx» и в списке найти действие «СУММ», это можно сделать в категории «Полный алфавитный перечень» или в «Математических».

   Нажимаем по значку функции «fx»

   В списке «Категория» выбираем «Полный алфавитный перечень»

   Находим и щелкаем по функции «СУММ», нажимаем «ОК»

3. Кликните на «ОК», появится окно «Аргументы функции». В нем можно сложить значения ячеек или вбить дополнительные цифры.

   Ставим курсор мышки в поле «Число1», щелкаем по ячейке со значением

   Ставим курсор мышки поочередно в поля «Число2» и «Число3», щелкаем по ячейкам со значением, нажимаем «ОК»

   Важно! Знайте, что программа проигнорирует логическое или текстовое значения.

4. Нажмите «ОК».

   В выделенной ранее ячейке появится результат функции «СУММ»

При суммировании чисел с одним условием применяйте функцию «СУММЕСЛИ»

Для этого:

1. Выберите ячейку, где вы хотите вывести результат. Кликните по ней единожды левой кнопкой мыши.

   Щелкаем левой кнопкой мышки по пустой ячейке, где будем вводить функцию

2. Нажмите на кнопку «fx» («Вставить функцию»), которая расположена перед строкой формул.

   Нажимаем по значку функции «fx»

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

   В списке «Категория» выбираем «Математичсекие», находим и щелкаем по функции «СУММЕСЛИ», затем «ОК»

4. Проявится всплывающее меню «Аргументы функции».

   Окно «Аргументы функции»

5. Заполните диапазон и критерий. Предположим, в нашем случае нам нужно узнать, на какую сумму продал товара Васечкин. Выделим ячейки с продавцами, большим плюсом нажмем на первую и, не отпуская, доведем до последней ячейки в диапазоне.

   Большим плюсом нажмем на первую ячейку и, не отпуская, доведем до последней ячейки в диапазоне

6. В строку «Критерий» впишем «Васечкин» (можно писать как с кавычками, так и нет, программа их выставит автоматически).

   В поле «Критерий» вписываем «Васечкин»

   Примечание! Можно вводить также математическое выражение.

7. Отмечаем диапазон суммирования из столбца «Сумма».

   В поле «Диапазон суммирования», вводим ячейки из столбца «Сумма», выделив первую ячейку и, не отпуская левую кнопку мышки, протягиваем до последней ячейки

8. Нажмите «ОК». Результат действия появится в выбранной ранее ячейке.

   Проверяем данные, нажимаем «ОК»

   В ячейке появится результат, заданной функции

На заметку! Эту функцию также можно ввести вручную, используя базовую запись: «=СУММЕСЛИ(x), где х – диапазон, критерий и диапазон суммирования, которые перечисляются через «;». Например, «=СУММЕСЛИ(А1:А2;«Условие»;В1:В2)».

Вводим вручную данные, в выбранную ячейку, нажимаем «Enter»

Однако, если нужно отобрать информацию по нескольким разным критериям?

 

Если вы хотите узнать, как написать формулу в Excel, а также обучиться азам работы в Excel, вы можете прочитать статью об этом на нашем портале.

 

Функция «СУММЕСЛИМН»

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

1. Отметьте ячейку.

   Щелкаем левой кнопкой мышки по пустой ячейке, где будем вводить функцию

2. Нажмите на «fx» или «Вставить функцию».

   Нажимаем по значку функции «fx»

3. Появится окно, где из перечня выберите функцию «СУММЕСЛИМН».

   Выбираем функцию «СУММЕСЛИМН»

4. Кликните на «ОК» или два раза нажмите на «СУММЕСЛИМН».

   Щелкаем по функции дважды или нажимаем «ОК»

5. Проявится меню «Аргументы функции».

   В поле «Диапазон суммирования», вводим ячейки из столбца «Сумма», протянув левую кнопку мышки от первой до последней ячейки

   Важно! Обратите внимание – в отличие от «СУММЕСЛИ», в данном окне сначала задается диапазон суммирования, а потом уже условия. Также можно ввести до 127 условий.

6. Заполните диапазоны условий и сами условия.

   В поле «Диапазон условия1» вводим ячейки из столбца «Товар», выделив их левой кнопкой мышки

   В поле «Условие1» вводим «яблоки» или другое значение, результат которого мы хотим получить

7. Нажмите «ОК».

   Щелкаем по кнопке «ОК»

   Примечание! Более подробную инструкцию вы можете найти в этой статье чуть выше.

8. Предположим, нам нужно узнать, на какую сумму Васечкин продал яблок. У нас есть всего 2 условия – продавец должен быть Васечкин, а товар – яблоки. В нашем случае, аргументы функции будут выглядеть следующим образом.

   Вводим данные в соответствующие поля, задаем необходимые условия в кавычках, щелкаем по кнопке «ОК»

   Результат, высчитанный функцией «СУММЕСЛИМН»

Эту функцию также можно ввести вручную, однако это будет довольно сложно, поскольку присутствует много условий. Чтобы программа работала корректно, и вы не потеряли время, пытаясь найти ошибку в длинной записи, рекомендуем все же воспользоваться действием через «fx».

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

Видео — Суммирование по условию в Excel, функция «СУММЕСЛИМН»

Выборочные вычисления по одному или нескольким критериям

Постановка задачи

Имеем таблицу по продажам, например, следующего вида:

Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина «Копейка».

Способ 1. Функция СУММЕСЛИ, когда одно условие

Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в «Копейку», например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig). Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:

Жмем ОК и вводим ее аргументы:

• Диапазон — это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае — это диапазон с фамилиями менеджеров продаж.
• Критерий — это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и  ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак — один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий ?????. А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву «П», а заканчивается на «В» — критерий П*В. Строчные и прописные буквы не различаются.
• Диапазон_суммирования — это те ячейки, значения которых мы хотим сложить, т.е. нашем случае — стоимости заказов.

Способ 2. Функция СУММЕСЛИМН, когда условий много

Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для «Копейки»), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) — в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:

При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3—Условие3), и четвертую, и т.д. — при необходимости.

Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться — см. следующие способы.

Способ 3. Столбец-индикатор

Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в «Копейку» и от Григорьева, то в ячейке этого столбца будет значение 1, иначе — 0. Формула, которую надо ввести в этот столбец очень простая:

=(A2=»Копейка»)*(B2=»Григорьев»)

Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:

Способ 4. Волшебная формула массива

Если вы раньше не сталкивались с такой замечательной возможностью Excel как формулы массива, то советую почитать предварительно про них много хорошего здесь. Ну, а в нашем случае задача решается одной формулой:

=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)

После ввода этой формулы необходимо нажать не Enter, как обычно, а Ctrl + Shift + Enter — тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.

Способ 4. Функция баз данных БДСУММ

В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM), которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев — ячейки, содержащие условия отбора — и указать затем этот диапазон функции как аргумент:

=БДСУММ(A1:D26;D1;F1:G2)

Выборочные вычисления по одному или нескольким критериям

Постановка задачи

Имеем таблицу по продажам, например, следующего вида:

Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина «Копейка».

Способ 1. Функция СУММЕСЛИ, когда одно условие

Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в «Копейку», например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig). Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:

Жмем ОК и вводим ее аргументы:

• Диапазон — это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае — это диапазон с фамилиями менеджеров продаж.
• Критерий — это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и  ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак — один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий ?????. А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву «П», а заканчивается на «В» — критерий П*В. Строчные и прописные буквы не различаются.
• Диапазон_суммирования — это те ячейки, значения которых мы хотим сложить, т.е. нашем случае — стоимости заказов.

Способ 2. Функция СУММЕСЛИМН, когда условий много

Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для «Копейки»), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) — в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:

При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3—Условие3), и четвертую, и т.д. — при необходимости.

Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться — см. следующие способы.

Способ 3. Столбец-индикатор

Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в «Копейку» и от Григорьева, то в ячейке этого столбца будет значение 1, иначе — 0. Формула, которую надо ввести в этот столбец очень простая:

=(A2=»Копейка»)*(B2=»Григорьев»)

Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:

Способ 4. Волшебная формула массива

Если вы раньше не сталкивались с такой замечательной возможностью Excel как формулы массива, то советую почитать предварительно про них много хорошего здесь. Ну, а в нашем случае задача решается одной формулой:

=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)

После ввода этой формулы необходимо нажать не Enter, как обычно, а Ctrl + Shift + Enter — тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.

Способ 4. Функция баз данных БДСУММ

В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM), которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев — ячейки, содержащие условия отбора — и указать затем этот диапазон функции как аргумент:

=БДСУММ(A1:D26;D1;F1:G2)

Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям

Хитрости » 11 Июнь 2011       Дмитрий       284945 просмотров

---

Представим себе таблицу, в которой в строках вперемешку указаны названия отделов(или счетов, или еще чего-то).

Суммируем ячейки по критерию
Необходимо вычислить общую сумму по каждому отделу. Многие делают это при помощи фильтра и записи ручками в ячейки.
Хотя сделать это можно легко и просто при помощи всего одной функции — СУММЕСЛИ.
СУММЕСЛИ(SUMIF) – Суммирует ячейки, удовлетворяющие заданному условию (условие можно задать только одно). Эту функцию так же можно применить, если таблица разбита в столбцах на периоды(помесячно, в каждом месяце по три столбца — Доход|Расход|Разница) и необходимо подсчитать общую сумму за все периоды только по Доходу, Расходу и Разнице.

Всего для СУММЕСЛИ предусмотрено три аргумента: Диапазон, Критерий, Диапазон_Суммирования.
=СУММЕСЛИ(A1:A20000;A1;B1:B20000)
=SUMIF(A1:A20000,A1,B1:B20000)

• Диапазон(A1:A20000) — указывается диапазон с критериями. Т.е. столбец, в котором искать значение, указанное аргументом Критерий.
• Критерий(A1)- значение(текстовое или числовое, а так же дата), которое необходимо найти в Диапазоне. Может содержать символы подстановки «*» и «?». Т.е. указав в качестве Критерия «*масса*» будут просуммированы значения, в которых встречается слово «масса». При этом слово «масса» может либо встречаться в любом месте текста, либо в ячейке может быть только одно это слово. А указав «масса*», будут просуммированы все значения, начинающиеся на «масса». «?» — заменяет лишь один символ, т.е. указав «мас?а» вы сможете просуммировать строки и со значением «масса» и со значением «маска» и т.д.
  Если критерий записан в ячейке и надо все же использовать подстановочные символы, то можно сделать ссылку на эту ячейку добавив нужное. Допустим, надо просуммировать значения, содержащие слово «итог». Слово «итог» записано в ячейке A1, в столбце A при этом могут встречаться различные по написанию значения, содержащие слово «итог»: «итоги за июнь», «итоги за июль», «итоги за март». Формула тогда должна выглядеть так:
  =СУММЕСЛИ(A1:A20000;»*»&A1&»*»;B1:B20000)
  «*»&A1&»*» — знак &(амперсанд) объединяет несколько значений в одно. Т.е. в результате получится «*итог*».
  Чтобы лучше понять принцип работы формул лучше использовать инструмент Вычислить формулу: Как просмотреть этапы вычисления формул
  Все текстовые критерии и критерии с логическими и математическими знаками необходимо заключать в двойные кавычки (=СУММЕСЛИ(A1:A20000;»итог»;B1:B20000)). Если критерием является число, использовать кавычки не требуется. Если требуется найти непосредственно вопросительный знак или звездочку, необходимо поставить перед ним знак «тильды» (~).
  Про тильду и её особенности можно узнать в этой статье: Как заменить/удалить/найти звездочку?
• Диапазон_Суммирования(B1:B20000)(необязательный аргумент) — указывается диапазон сумм или числовых значений, которые необходимо просуммировать.

Как это работает: функция ищет в Диапазоне значение, указанное аргументом Критерий, и при нахождении совпадения суммирует данные, указанные аргументом Диапазон_Суммирования. Т.е. если у нас в столбце А название отдела, а в столбце В суммы, то указав в качестве критерия «Отдел развития» результатом функции будет сумма всех значений столбца В, напротив которых в столбце А встречается «Отдел развития». Фактически Диапазон_Суммирования может не совпадать по размеру с аргументом Диапазон и ошибки самой функции это не вызовет. Однако при определении ячеек для суммирования, в качестве начальной ячейки для суммирования будет использована верхняя левая ячейка аргумента Диапазон_Суммирования, а затем суммируются ячейки, соответствующие по размеру и форме аргументу Диапазон.

Некоторые особенности
Последний аргумент функции(Диапазон_Суммирования — B1:B20000) является необязательным. А это значит, что его можно не указывать. Если его не указать, то функция просуммирует значения, указанные аргументом Диапазон. Для чего это нужно. Например, Вам необходимо получить сумму только тех чисел, которые больше нуля. В столбце А суммы. Тогда функция будет иметь такой вид:
=СУММЕСЛИ(A1:A20000;»>0″)

Что следует стоит учитывать:диапазон_суммирования и диапазон должны быть равны по количеству строк. Иначе можно получить неверный результат. Оптимально, если это будет выглядеть как в приведенных мной формулах: диапазон и диапазон_суммирования начинаются с одной строки и имеют одинаковое количество строк: A1:A20000; B1:B20000

Ложка дегтя: — СУММЕСЛИ(а так же СУММЕСЛИМН, СЧЁТЕСЛИ и иже с ними) всегда стремятся преобразовать все значения аргументов к типам. Это значит, что если у нас в ячейке записано число 23 — оно будет воспринято к

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

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

Функция СУММ – простое суммирование ячеек в Excel

Функция СУММ вычисляет сумму всех своих аргументов. Она является наиболее часто используемой функцией в Excel. К примеру, нам необходимо сложить значения в трех ячейках. Мы, конечно же, можем воспользоваться обычным оператором суммирования:

Но мы также можем воспользоваться функцией СУММ и записать формулу следующим образом:

Поскольку функция СУММ поддерживает работу не только с отдельными ячейками, но и целыми диапазонами, то вышеприведенную формулу можно модифицировать:

Истинная мощь функции СУММ раскрывается, когда необходимо сложить большое количество ячеек в Excel. В примере ниже требуется просуммировать 12 значений. Функция СУММ позволяет сделать это несколькими щелчками мышью, если же использовать оператор сложения, то провозиться придется долго.

В следующем примере функция СУММ складывает весь столбец А, а это 1048576 значений:

Следующая формула подсчитывает сумму всех ячеек, содержащихся на рабочем листе Лист1. Чтобы данная формула не вызвала циклической ошибки, ее необходимо использовать на другом рабочем листе Excel (отличном от Лист1).

Функция СУММ может принимать до 255 аргументов и суммировать сразу несколько несмежных диапазонов или ячеек:

Если среди суммируемых значений содержится текст, то функция СУММ их игнорирует, т.е. не включает в расчет:

Если же текстовые значения попытаться сложить оператором суммирования, то формула вернет ошибку:

Функция СУММ достаточно универсальна и позволяет использовать в качестве своих аргументов не только ссылки на ячейки и диапазоны, но и различные математические операторы и даже другие функции Excel:

Если интересно узнать, как посчитать накопительную сумму в Excel, обратитесь к этому уроку.

СУММЕСЛИ – условная сумма в Excel

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

Например, следующая формула суммируем только положительные числа диапазона A1:A10. Обратите внимание, что условие заключено в двойные кавычки.

В качестве условия можно использовать значение ячейки. В этом случае, поменяв условие, изменится и результат:

Меняем условие, меняется и результат:

Условие можно комбинировать, используя оператор конкатенации. В примере ниже формула вернет сумму значений, которые больше значения в ячейке B1.

Во всех примерах, приведенных ранее, мы производили суммирование и проверяли условие по одному и тому же диапазону. А что делать, если необходимо просуммировать один диапазон, а условие проверять по-другому?

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

В следующем примере мы просуммируем общую стоимость всех проданных фруктов. Для этого воспользуемся следующей формулой:

Нажав Enter получим результат:

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

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

Оцените качество статьи. Нам важно ваше мнение:

Как использовать функцию СУММЕСЛИ в Excel для условного суммирования ячеек

В этом руководстве на простом английском языке объясняется функция СУММЕСЛИ в Excel и приводится ряд примеров формулы СУММЕСЛИ для чисел, текста, дат и подстановочных знаков.

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

Хорошо то, что функция СУММЕСЛИ идентична во всех версиях Excel с 2016 по 2003 год. Еще одна замечательная новость заключается в том, что если вы потратите некоторое время на изучение СУММЕСЛИ, вам потребуется совсем немного усилий, чтобы понять других. Функции «ЕСЛИ», такие как СУММЕСЛИМН, СЧЁТЕСЛИ, СЧЁТЕСЛИ, СРЕДНЕЛИ и т. Д.

СУММЕСЛИ в Excel — синтаксис и использование

Функция СУММЕСЛИ, также известная как условная сумма Excel, используется для добавления ячеек на основе определенного условия или критерия.

Если вам довелось прочитать учебник «СЧЁТЕСЛИ» в этом блоге, у вас не возникнет проблем с пониманием СУММЕСЛИ в Excel, поскольку его синтаксис и использование аналогичны.Итак, синтаксис функции СУММЕСЛИ:

СУММЕСЛИ (диапазон; критерий; [диапазон_суммы])

Как видите, функция СУММЕСЛИ имеет 3 аргумента — первые 2 обязательны, а 3 ^rd необязательны.

1. диапазон — диапазон ячеек, которые будут оцениваться по вашим критериям, например A1: A10.
2. критерии — условие, которое необходимо выполнить. Критерии могут быть предоставлены в форме числа, текста, даты, логического выражения, ссылки на ячейку или другой функции Excel.Например, вы можете ввести такие критерии, как «5», «вишня», «25.10.2014», «<5», «A1» и т. Д.

   Примечание. Обратите внимание, что любые текстовые критерии или критерии, содержащие математические символы, должны быть заключены в двойные кавычки («). Для числовых критериев двойные кавычки не требуются.

3. диапазон_суммы — ячейки для суммирования при выполнении условия. Этот аргумент является необязательным, и вам нужно использовать его только в том случае, если вы хотите суммировать ячейки, отличные от определенных в аргументе диапазона.Если аргумент sum_range опущен, Excel будет суммировать те же ячейки, к которым применяются критерии (т.е. указанные в аргументе диапазона).

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

   Теперь давайте определим аргументы для нашей формулы СУММЕСЛИ:

   • диапазон: A2: A8
   • критерий: «бананы»
   • диапазон сумм: C2: C8

   Соединяя это вместе, получаем: = СУММЕСЛИ (A2: A8, «бананы», C2: C8)

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

   = СУММЕСЛИ (A2: A8, F1, C2: C8)

   Примечание. Параметр sum_range фактически указывает только крайнюю левую верхнюю ячейку диапазона для суммирования. Оставшаяся область определяется размерами аргумента диапазона.

На практике это означает, что аргумент sum_range не обязательно должен быть того же размера, что и аргумент диапазона, т.е.е. в нем может быть разное количество строк и столбцов. Однако первая ячейка (то есть верхняя левая ячейка ) диапазона суммы всегда должна быть правой. Например, в приведенной выше формуле СУММЕСЛИ вы можете указать C2, C2: C4 или даже C2: C100 в качестве аргумента sum_range, и результат все равно будет тем же. Однако лучше всего использовать диапазон и сумму_диапазона одинакового размера.

Дело в том, что Microsoft Excel не полагается на способность пользователя предоставить диапазон соответствия и параметры sum_range, поэтому, чтобы избежать возможных проблем с несогласованностью и предотвратить ошибки, он определяет диапазон суммы самостоятельно следующим образом.Он принимает верхнюю левую ячейку в аргументе sum_range в качестве начальной ячейки (C2 в нашем примере SUMIF в Excel), а затем включает столько столбцов и строк, сколько содержится в аргументе диапазона (в нашем случае это 1 столбец x 7 строк, т. Е. C2: C8).

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

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

СУММЕСЛИ больше, меньше или равно

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

Примечание. Обратите внимание, что в формулах Excel СУММЕСЛИ оператор сравнения, за которым следует число или текст, всегда следует заключать в двойные кавычки («»).

Критерии	Оператор	Пример формулы	Описание
Сумма, если больше	>	= СУММЕСЛИ (A2: A10, "> 5")	Просуммируйте значения более 5 в ячейках A2: A10.
Сумма, если меньше	<	= СУММЕСЛИ (A2: A10; «<10»; B2: B10)	Суммируйте значения в ячейках B2: B10, если соответствующее значение в столбце A меньше 10.
Сумма, если равна	= (можно не указывать)	= СУММЕСЛИ (A2: A10, "=" & D1) или = СУММЕСЛИ (A2: A10, D1)	Просуммируйте значения в ячейках A2: A10, которые равны значению в ячейке D1.
Сумма, если не равна	<>	= СУММЕСЛИ (A2: A10, «<>» & D1, B2: B10)	Просуммируйте значения в ячейках B2: B10, если соответствующая ячейка в столбце A не равна значению в ячейке D1.
Сумма, если она больше или равна	> =	= СУММЕСЛИ (A2: A10, "> = 5")	Суммируйте значения больше или равные 5 в диапазоне A2: A10.
Сумма, если она меньше или равна	<=	= СУММЕСЛИ (A2: A10, «<= 10», B2: B10)	Суммируйте значения в ячейках B2: B10, если соответствующее значение в столбце A меньше или равно 10.

Как использовать СУММЕСЛИ в Excel с текстовыми критериями

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

Обратите внимание, что вам потребуются разные формулы СУММЕСЛИ для точного и частичного совпадения, как показано в таблице ниже.

Критерии	Пример формулы	Описание
Сумма, если равна	Точное соответствие: = СУММЕСЛИ (A2: A8, «бананы», C2: C8)	Сумма значений в ячейках C2: C8, если соответствующая ячейка в столбце A содержит в точности слово «бананы» и никаких других слов или символов.Ячейки, содержащие «зеленые бананы», «зеленые бананы» или «бананы!» не включены.
	Частичное совпадение: = СУММЕСЛИ (A2: A8, «* бананы *», C2: C8)	Сумма значений в ячейках C2: C8, если соответствующая ячейка в столбце A содержит слово «бананы», отдельно или в сочетании с любыми другими словами. Ячейки, содержащие «зеленые бананы» или «зеленые бананы», суммируются.
Сумма, если не равна	Точное соответствие: = СУММЕСЛИ (A2: A8, «<> бананы», C2: C8)	Сумма значений в ячейках C2: C8, если соответствующая ячейка в столбце A содержит любое значение, кроме «бананов».Если ячейка содержит «бананы» вместе с некоторыми другими словами или символами, например «желтые бананы» или «желтые бананы», такие ячейки суммируются.
	Частичное совпадение: = СУММЕСЛИ (A2: A8, «<> * бананы *», C2: C8)	Сумма значений в ячейках C2: C8, если соответствующая ячейка в столбце A не содержит слова «бананы», отдельно или в сочетании с любыми другими словами. Ячейки, содержащие «желтые бананы» или «желтые бананы», не суммируются.

Дополнительные сведения о частичном совпадении см. В примерах СУММЕСЛИ с подстановочными знаками.

А теперь давайте посмотрим на точную формулу «Сумма, если не равно» в действии. Как показано на скриншоте ниже, он добавляет запасы всех товаров, кроме "бананов голдфингер":

= СУММЕСЛИ (A2: A7, «<> бананы голдфингер», C2: C7)

Примечание. Как и большинство других функций Excel, СУММЕСЛИ нечувствительна к регистру, что означает, что «<> бананы», «<> бананы» и «<> бананы» дадут точно такой же результат.

Использование операторов сравнения со ссылками на ячейки

Если вы хотите сделать еще один шаг и получить более универсальную формулу СУММЕСЛИ в Excel, вы можете заменить числовое или текстовое значение в критериях ссылкой на ячейку, например:
= СУММЕСЛИ (A2: A8, "<>" & F1, C2: C8)

В этом случае вам не придется изменять формулу для условного суммирования значений на основе других критериев - вы просто вводите новое значение в ячейку, на которую указывает ссылка.

Примечание. Когда вы используете логическое выражение со ссылкой на ячейку, вы должны использовать двойные кавычки ("") для начала текстовой строки и амперсанд (&) для объединения и завершения строки, например "<>" & F1.

Оператор «равно» (=) можно опустить перед ссылкой на ячейку, поэтому обе приведенные ниже формулы эквивалентны и верны:

Формула 1: = СУММЕСЛИ (A2: A8, "=" & F1, C2: C8)

Формула 2: = СУММЕСЛИ (A2: A8, F1, C2: C8)

Формулы СУММЕСЛИ с подстановочными знаками

Если вы хотите условно суммировать ячейки на основе «текстовых» критериев и хотите суммировать по частичному совпадению , то вам необходимо использовать подстановочные знаки в формуле СУММЕСЛИ.

Нам доступны следующие подстановочные знаки:

• Звездочка ( * ) - представляет любое количество символов
• Вопросительный знак (?) - представляет собой один символ в определенной позиции.

Пример 1. Сумма значений на основе частичного совпадения

Предположим, вы хотите просуммировать суммы, относящиеся ко всем сортам бананов. Следующие формулы СУММЕСЛИ подойдут для вас:

= СУММЕСЛИ (A2: A8, «* бананы *», C2: C8) - критерий включает текст, заключенный в звездочки (*).

= СУММЕСЛИ (A2: A8, "*" & F1 & "*", C2: C8) - критерий включает ссылку на ячейку, заключенную в звездочки, обратите внимание на использование амперсанда (&) до и после ссылки на ячейку для объединения строка.

Если вы хотите подсчитать только те ячейки, которые начинаются или заканчиваются определенным текстом, добавьте только один * либо до, либо после текста:

= СУММЕСЛИ (A2: A8, «бананы *», C2: C8) - сумма значений в C2: C8, если соответствующая ячейка в столбце A начинается со слова «бананы».

= СУММЕСЛИ (A2: A8, «* бананы», C2: C8) - сумма значений в C2: C8, если соответствующая ячейка в столбце A заканчивается словом «бананы».

Наконечник. Чтобы использовать подстановочные знаки со ссылками на ячейки , объедините их с помощью оператора конкатенации (&). В этом примере вы также можете использовать следующую формулу для суммирования всех "бананов" на складе: = СУММЕСЛИ (A2: A8, "*" & F1 & "*", C2: C8)

Пример 2. Сумма значений с заданным количеством символов

Если вы хотите суммировать некоторые значения, состоящие ровно из 6 букв, вы должны использовать следующую формулу:

= СУММЕСЛИ (A2: A8, «??????», C2: C8)

Пример 3.Ячейки суммы, соответствующие текстовым значениям

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

= СУММЕСЛИ (A2: A8, «? *», C2: C8) - складывает значения в ячейках C2: C8, если соответствующая ячейка в столбце A содержит хотя бы 1 символ.

= СУММЕСЛИ (A2: A8, «*», C2: C8) - включает кажущиеся пустыми ячейки, содержащие строки нулевой длины, возвращаемые некоторыми другими формулами, например.г. = "" .

Обе приведенные выше формулы игнорируют нетекстовые значения, такие как ошибки, логические значения, числа и даты.

Пример 4. Лечить * или? как обычно иероглифы

Если вы хотите либо *, либо? чтобы обрабатываться как литерал, а не как подстановочный знак, используйте предшествующую тильду (~). Например, следующая формула СУММЕСЛИ добавит значения в ячейки C2: C8, если ячейка в столбце A в той же строке содержит вопросительный знак:

= СУММЕСЛИ (A2: A8, «~?», C2: C8)

Суммирование наибольшего или наименьшего числа в диапазоне

Чтобы сложить наибольшее или наименьшее число в диапазоне, используйте функцию СУММ вместе с функцией НАИБОЛЬШИЙ или МАЛЕНЬКИЙ соответственно.

Пример 1. Складываем несколько наибольших / наименьших чисел

Если вы хотите суммировать всего несколько чисел, например 5, вы можете ввести их прямо в формулу, например:

= СУММ (НАИБОЛЬШИЙ (B1: B10, {1,2,3,4,5})) - суммировать 5 самых больших чисел

= СУММ (МАЛЫЙ (B1: B10, {1,2,3,4,5})) - суммировать 5 наименьших чисел

Примечание. Если есть 2 или более номеров, которые занимают последнее место, будет включен только первый. Как видите, в приведенном выше примере не складываются вторые 9.

Пример 2. Сложить много верхних / нижних чисел

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

= СУММ (БОЛЬШОЙ (B1: B50, СТРОКА (КОСВЕННАЯ ("1:15"))))

= СУММ (МАЛЫЙ (B1: B50, СТРОКА (КОСВЕННАЯ ("1:15"))))

Поскольку это формулы массива, не забудьте ввести их в виде массива, нажав Ctrl + Shift + Enter.

Пример 3. Суммируйте переменное количество наибольших / наименьших значений

Если вы не хотите изменять формулу каждый раз, когда вы хотите суммировать разное количество ячеек, вы можете поместить это число в какую-либо ячейку вместо того, чтобы вводить его непосредственно в формулу. Затем вы можете использовать функции СТРОКА и ДВССЫЛ, как показано выше, и обратиться к ячейке, содержащей переменную, в нашем случае ячейке E1:

= SUM (LARGE (B1: B50, ROW (INDIRECT ("1:" & E1)))) - суммирует переменное количество верхних значений

= SUM (SMALL (B1: B50, ROW (INDIRECT ("1:" & E1)))) - суммирует переменное количество нижних значений

Помните, что это формулы массива, поэтому вам нужно нажать Ctrl + Shift + Enter, чтобы завершить их.

Как суммировать ячейки, соответствующие пустым ячейкам

Если «пусто» означает ячейки, которые не содержат абсолютно ничего - ни формулы, ни строки нулевой длины, возвращаемой какой-либо другой функцией Excel, тогда используйте "=" в качестве критерия, как в следующей формуле СУММЕСЛИ:

= СУММЕСЛИ (A2: A10; «=»; C2: C10)

Если "blank" включает строки нулевой длины (например, ячейки с формулой = "" ), тогда используйте "" в качестве критерия:

= СУММЕСЛИ (A2: A10, "", C2: C10)

Обе приведенные выше формулы оценивают ячейки в столбце A, и если обнаруживаются пустые ячейки, добавляются соответствующие значения из столбца C.

Как суммировать ячейки, соответствующие непустым ячейкам

Если вы хотите просуммировать значения ячеек в столбце C, когда соответствующая ячейка в столбце A не пуста, используйте «<>» в качестве критерия в формуле СУММЕСЛИ:

= СУММЕСЛИ (A2: A10; «<>»; C2: C10)

В приведенной выше формуле подсчитываются значения, соответствующие всем непустым ячейкам, включая строки нулевой длины.

Как использовать СУММЕСЛИ в Excel с датами

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

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

Критерии	Пример формулы	Описание
Сумма значений на определенную дату.	= СУММЕСЛИ (B2: B9, «29.10.2014», C2: C9)	Сумма значений в ячейках C2: C9, если соответствующая дата в столбце B - 29 октября 2014 г.
Сумма значений, если соответствующая дата больше или равна заданной дате.	= СУММЕСЛИ (B2: B9, "> = 29.10.2014", C2: C9)	Сумма значений в ячейках C2: C9, если соответствующая дата в столбце B больше или равна 29 октября 2014 года.
Сумма значений, если соответствующая дата больше даты в другой ячейке.	= СУММЕСЛИ (B2: B9, ">" & F1, C2: C9)	Сумма значений в ячейках C2: C9, если соответствующая дата в столбце B больше даты в F1.

Если вы хотите суммировать значения на основе текущей даты, вам нужно использовать СУММЕСЛИ в Excel в сочетании с функцией СЕГОДНЯ (), как показано ниже:

Критерии	Пример формулы
Сумма значений на основе текущей даты.	= СУММЕСЛИ (B2: B9, СЕГОДНЯ (), C2: C9)
Сумма значений, соответствующих предыдущей дате, то есть до сегодняшнего дня.	= СУММЕСЛИ (B2: B9, «<» & СЕГОДНЯ (), C2: C9)
Сумма значений, соответствующих будущей дате, т.е.е. после сегодняшнего дня.	= СУММЕСЛИ (B2: B9, «>» & СЕГОДНЯ (), C2: C9)
Сумма значений, если дата приходится на неделю (например, сегодня + 7 дней).	= СУММЕСЛИ (B2: B9, "=" & СЕГОДНЯ () + 7, C2: C9)

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

Как суммировать значения в заданном диапазоне дат

На форумах и в блогах, посвященных Excel, часто задают следующий вопрос - «Как суммировать две даты?»

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

= СУММЕСЛИ (B2: B9, «> = 10/1/2014», C2: C9) - СУММЕСЛИ (B2: B9, «> = 11/1/2014», C2: C9)

Эта формула суммирует значения в ячейках C2: C9, если дата в столбце B находится между 1 октября 2014 г. и 31 октября 2014 г. включительно.

Эта формула на первый взгляд может показаться немного сложной, но при ближайшем рассмотрении она кажется довольно простой.Первая функция СУММЕСЛИ складывает все ячейки в C2: C9, где соответствующая ячейка в столбце B больше или равна дате начала (Oct-1 в этом примере). Затем вам просто нужно вычесть все значения, которые выпадают после даты окончания (31 октября), которые возвращаются второй функцией СУММЕСЛИ.

Как суммировать значения в нескольких столбцах

Чтобы лучше понять проблему, давайте рассмотрим следующий пример. Предположим, у вас есть сводная таблица ежемесячных продаж. Поскольку он был консолидирован из нескольких региональных репостов, для одного и того же продукта существует несколько записей:

Итак, как вы определяете общее количество яблок, проданных во всех штатах за последние три месяца?

Как вы помните, размеры sum_range определяются размерами параметра range.Вот почему вы не можете использовать такую ​​формулу, как = СУММЕСЛИ (A2: A9, «яблоки», C2: E9) , потому что она суммирует значения, соответствующие «Яблокам», только в столбце C. Это не то, что мы ищем, правда?

Наиболее логичное и простое решение, которое напрашивается само собой, - это создать вспомогательный столбец, который вычисляет отдельные промежуточные итоги для каждой строки, а затем ссылаться на этот столбец в критерии sum_range.

Поместите простую формулу СУММ в ячейку F2, затем заполните столбец F: = СУММ (C2: E2)

После этого вы можете написать обычную формулу СУММЕСЛИ, например:

= СУММЕСЛИ (A2: A9, «яблоки», F2: F9)
или
= СУММЕСЛИ (A2: A9, h2, F2: F9)

В приведенных выше формулах sum_range точно того же размера, что и range, т.е.е. 1 столбец и 8 строк, поэтому они возвращают правильный результат:

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

= СУММ (СУММЕСЛИ (A2: A9, I1, C2: C9), СУММЕСЛИ (A2: A9, I1, D2: D9), СУММЕСЛИ (A2: A9, I1, E2: E9))

Другой способ - использовать более сложную формулу массива (не забудьте нажать Ctrl + Shift + Enter):
{= SUM ((C2: C9 + D2: D9 + E2: E9) * (- (A2: A9 = I1)))}

Обе приведенные выше формулы вернут в нашем случае 2070.

Почему моя формула СУММЕСЛИ не работает?

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

1. Параметры range и sum_range должны быть диапазонами, а не массивами

Первый (диапазон) и третий (диапазон_суммирования) параметры вашей формулы СУММЕСЛИ всегда должны быть диапазонами.Если вы попытаетесь передать что-нибудь еще, например массив типа {1,2,3}, Excel выдаст сообщение об ошибке.

Правильная формула: = СУММЕСЛИ (A1: A3, «цветок», C1: C3)

Неправильная формула: = СУММЕСЛИ ({"цветок", "дерево", "куст"}, "цветок", C1: C3)

2. Как суммировать значения из других листов или книг

Как почти любая другая функция Excel, СУММЕСЛИ может ссылаться на другие листы и книги, если они в данный момент открыты.

Например, следующая формула суммирует значения в ячейках F2: F9 на листе 1 книги 1, если соответствующая ячейка в столбце A, если тот же лист содержит «яблоки»:

= СУММЕСЛИ ([Книга1.xlsx] Sheet1! $ A $ 2: $ A $ 9, "яблоки", [Book1.xlsx] Sheet1! $ F $ 2: $ F $ 9)

Однако эта формула не будет работать, как только Книга 1 будет закрыта. Это происходит из-за того, что диапазоны, на которые ссылаются формулы СУММЕСЛИ в закрытых книгах, лишаются ссылок на массивы, а поскольку в аргументах диапазон и диапазон_суммирования массивы не допускаются, формула СУММЕСЛИ выдаст #VALUE! ошибка.

3. Чтобы избежать проблем, убедитесь, что диапазон и сумма_диапазона имеют одинаковый размер

Как отмечалось в начале этого руководства, в современных версиях Microsoft Excel параметры range и sum_range не обязательно должны быть одинакового размера.В Excel 2000 и более ранних версиях range и sum_range разного размера могут вызывать проблемы. Однако даже в самых последних версиях Excel 2010 и Excel 2016 сложные формулы СУММЕСЛИ, в которых диапазон_суммирования имеет меньше строк и / или столбцов, чем диапазон, являются капризными. Вот почему считается хорошей практикой всегда иметь аргументы range и sum_range одного размера и формы.

4. Как сделать так, чтобы формулы СУММЕСЛИ работали быстрее

Если вы заполнили свою книгу сложными формулами СУММЕСЛИ, которые замедляют работу Excel, ознакомьтесь с этой статьей и узнайте, как заставить их работать быстрее.Статья написана довольно давно, поэтому не удивляйтесь времени расчета. Их рекомендуемые подходы и примеры формул по-прежнему актуальны и гениальны!

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

Вас также может заинтересовать

Формулы условного форматирования Excel

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

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

Формулы Excel для условного форматирования на основе значения ячейки

Предопределенные правила условного форматирования

Excel в основном предназначены для форматирования ячеек на основе их собственных значений или значений, которые вы указываете. Я говорю о панелях данных, цветовых шкалах, наборах значков и других правилах, доступных вам при нажатии кнопки Условное форматирование .

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

Как создать правило условного форматирования с помощью формулы

Чтобы настроить правило условного форматирования на основе формулы в Excel 2019, Excel 2016, Excel 2013 и Excel 2010, выполните следующие действия:

1. Выберите ячейки, которые нужно отформатировать. Вы можете выбрать один столбец, несколько столбцов или всю таблицу, если хотите применить условный формат к строкам. Наконечник. Если вы планируете добавить больше данных в будущем и хотите, чтобы правило условного форматирования применялось к новым записям автоматически, вы можете:
   • Преобразование диапазона ячеек в таблицу ( Вставка вкладки> Таблица ). В этом случае условное форматирование будет автоматически применяться ко всем новым строкам.
   • Выберите несколько пустых строк под данными, скажем, 100 пустых строк.
2. На вкладке Home в группе Styles щелкните Условное форматирование> Новое правило…
   
3. В окне Новое правило форматирования выберите Использовать формулу, чтобы определить, какие ячейки нужно форматировать .
4. Введите формулу в соответствующее поле.
5. Нажмите кнопку Формат… , чтобы выбрать собственный формат.
   
6. Переключайтесь между Font , Border и Заполните вкладки и поиграйте с различными параметрами, такими как стиль шрифта, цвет узора и эффекты заливки, чтобы настроить формат, который лучше всего подходит для вас. Если стандартной палитры недостаточно, щелкните Дополнительные цвета… и выберите любой цвет RGB или HSL по своему вкусу.Когда закончите, нажмите кнопку OK .
   
7. Убедитесь, что в разделе Preview отображается нужный вам формат, и если это так, нажмите кнопку OK , чтобы сохранить правило. Если вас не очень устраивает предварительный просмотр формата, нажмите кнопку Format… еще раз и внесите изменения.
   

Наконечник. Каждый раз, когда вам нужно отредактировать формулу условного форматирования, нажмите F2, а затем перейдите в нужное место в формуле с помощью клавиш со стрелками.Если вы попытаетесь использовать стрелку, не нажимая F2, в формулу будет вставлен диапазон, а не просто перемещать указатель вставки. Чтобы добавить ссылку на определенную ячейку в формулу, нажмите F2 второй раз, а затем щелкните эту ячейку.

Примеры формул условного форматирования Excel

Теперь, когда вы знаете, как создавать и применять условное форматирование Excel на основе другой ячейки, давайте продолжим и посмотрим, как использовать различные формулы Excel на практике:

Наконечник. Для правильной работы формулы условного форматирования Excel всегда следуйте этим простым правилам.

Формулы для сравнения значений (числа и текст)

Как вы знаете, Microsoft Excel предоставляет несколько готовых к использованию правил для форматирования ячеек со значениями больше, меньше или равными указанному вами значению ( Условное форматирование> Правила выделения ячеек ). Однако эти правила не работают, если вы хотите условно отформатировать определенные столбцы или целые строки на основе значения ячейки в другом столбце. В данном случае вы используете аналогичные формулы:

Состояние	Пример формулы
Равно	= В2 = 10
Не равно	= B2 <> 10
Больше	= B2> 10
Больше или равно	= $ B2> = 10
Менее	= B2 <10
Меньше или равно	= B2 <= 10
Между	= И ($ B2> 5, $ B2 <10)

На снимке экрана ниже показан пример формулы Больше, чем , которая выделяет названия продуктов в столбце A, если количество товаров на складе (столбец C) больше 0.Обратите внимание, что формула применима только к столбцу A ($ A $ 2: $ A $ 8). Но если вы выберете всю таблицу (в нашем случае $ A $ 2: $ E $ 8), это выделит целые строки на основе значения в столбце C.

Аналогичным образом вы можете создать правило условного форматирования для сравнения значений двух ячеек. Например:

= $ A2 <$ B2 - форматировать ячейки или строки, если значение в столбце A меньше, чем соответствующее значение в столбце B.

= $ A2 = $ B2 - форматировать ячейки или строки, если значения в столбцах A и B совпадают.

= $ A2 <> $ B2 - форматировать ячейки или строки, если значение в столбце A не такое же, как в столбце B.

Как вы можете видеть на снимке экрана ниже, эти формулы работают как для текстовых значений, так и для чисел.

Формулы И и ИЛИ

Если вы хотите отформатировать таблицу Excel на основе 2 или более условий, используйте функцию = И или = ИЛИ:

Состояние	Формула	Описание
Если оба условия соблюдены	= И ($ B2 <$ C2, $ C2 <$ D2)	Форматирует ячейки, если значение в столбце B меньше, чем в столбце C, и , если значение в столбце C меньше, чем в столбце D.
При выполнении одного из условий	= ИЛИ ($ B2 <$ C2, $ C2 <$ D2)	Форматирует ячейки, если значение в столбце B меньше, чем в столбце C, или , если значение в столбце C меньше, чем в столбце D.

На скриншоте ниже мы используем формулу = AND ($ C2> 0, $ D2 = "Worldwide") , чтобы изменить цвет фона строк, если количество товаров на складе (столбец C) больше чем 0, и если продукт доставляется по всему миру (столбец D).Обратите внимание, что формула работает с текстовыми значениями , а также с числами .

Естественно, вы можете использовать два, три или более условий в формулах = И и = ИЛИ.

Это основные формулы условного форматирования, которые вы используете в Excel. Теперь давайте рассмотрим несколько более сложные, но гораздо более интересные примеры.

Условное форматирование пустых и непустых ячеек

Я думаю, что все знают, как форматировать пустые, а не пустые ячейки в Excel - вы просто создаете новое правило типа « Форматировать только ячейки, содержащие» и выбираете Пробелы или Без пробелов .

Но что, если вы хотите отформатировать ячейки в определенном столбце, если соответствующая ячейка в другом столбце пуста или не пуста? В этом случае вам нужно будет снова использовать формулы Excel:

Формула для пробелов : = $ B2 = "" - форматирование выбранных ячеек / строк, если соответствующая ячейка в столбце B пуста.

Формула для непустых значений : = $ B2 <> "" - форматирование выбранных ячеек / строк, если соответствующая ячейка в столбце B не пуста.

Примечание. Формулы выше будут работать для ячеек, которые «визуально» пусты или не пусты. Если вы используете какую-либо функцию Excel, которая возвращает пустую строку, например = if (false, "OK", "") , и вы не хотите, чтобы такие ячейки считались пустыми, используйте вместо них следующие формулы: = isblank (A1) = true или = isblank (A1) = false для форматирования пустых и непустых ячеек соответственно.

А вот пример того, как можно использовать приведенные выше формулы на практике.Предположим, у вас есть столбец (B), который представляет собой « Дата продажи », а другой столбец (C) «Поставка ». Эти 2 столбца имеют значение только в том случае, если была совершена продажа и доставлен товар. Итак, вы хотите, чтобы вся строка стала оранжевой, когда вы совершите продажу; и когда товар доставлен, соответствующая строка должна стать зеленой. Для этого вам необходимо создать 2 правила условного форматирования со следующими формулами:

• Оранжевые строки (ячейка в столбце B не пуста): = $ B2 <> ""
• Зеленые строки (ячейки в столбце B и столбце C не пусты): = AND ($ B2 <> "", $ C2 <> "")

Еще одна вещь, которую вам нужно сделать, это переместить второе правило наверх и установить флажок Stop if true рядом с этим правилом:

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

Формулы Excel для работы с текстовыми значениями

Если вы хотите отформатировать определенный столбец (столбцы), когда другая ячейка в той же строке содержит определенное слово, вы можете использовать формулу, описанную в одном из предыдущих примеров (например, = $ D2 = "Worldwide"). Однако это будет работать только для с точным соответствием .

Для частичного совпадения необходимо использовать ПОИСК (без учета регистра) или НАЙТИ (с учетом регистра).

Например, для форматирования выбранных ячеек или строк, если соответствующая ячейка в столбце D содержит слово « Worldwide », используйте формулу ниже. Эта формула найдет все такие ячейки, независимо от того, где в ячейке находится указанный текст, включая « Ships Worldwide », « Worldwide, за исключением… » и т. Д .:

= ПОИСК ("Весь мир", $ D2)> 0

Если вы хотите затенять выбранные ячейки или строки, если содержимое ячейки начинается с текста поиска, используйте этот:

= ПОИСК («Весь мир», $ D2)> 1

Формулы Excel для выделения дубликатов

Если вашей задачей является условное форматирование ячеек с повторяющимися значениями, вы можете воспользоваться предопределенным правилом, доступным в разделе Условное форматирование> Правила выделения ячеек> Повторяющиеся значения… В следующей статье содержится подробное руководство по использованию этой функции: Как автоматически выделять дубликаты в Excel.

Однако в некоторых случаях данные выглядят лучше, если вы окрашиваете выбранные столбцы или целые строки, когда повторяющиеся значения встречаются в другом столбце. В этом случае вам нужно будет снова использовать формулу условного форматирования Excel, и на этот раз мы будем использовать формулу СЧЕТЕСЛИ . Как вы знаете, эта функция Excel подсчитывает количество ячеек в указанном диапазоне, соответствующих одному критерию.

Выделить дубликаты, включая 1 ^st вхождений

= COUNTIF ($ A $ 2: $ A $ 10, $ A2)> 1 - эта формула находит повторяющиеся значения в указанном диапазоне в столбце A (A2: A10 в нашем случае), включая первые вхождения.

Если вы решите применить правило ко всей таблице, все строки будут отформатированы, как вы видите на снимке экрана ниже. Я решил изменить цвет шрифта в этом правиле, просто для разнообразия 🙂

Выделить дубликаты без 1 ^st вхождений

Чтобы игнорировать первое вхождение и выделять только последующие повторяющиеся значения, используйте эту формулу: = СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2)> 1

Выделить последовательные дубликаты в Excel

Если вы предпочитаете выделять только дубликаты в последовательных строках, вы можете сделать это следующим образом.Этот метод работает для любых типов данных: чисел, текстовых значений и дат.

• Выберите столбец, в котором вы хотите выделить дубликаты, без заголовка столбца .
• Создайте правила условного форматирования, используя эти простые формулы:
  Правило 1 (синий): = $ A1 = $ A2 - выделяет 2 ^{и все последующие вхождения, если таковые имеются.
  Правило 2 (зеленый): = $ A2 = $ A3 - выделяет вхождение 1 st .}

В приведенных выше формулах A - это столбец, который нужно проверить на наличие дубликатов, $ A1 - заголовок столбца, $ A2 - это первая ячейка с данными.

Важно! Для правильной работы формул важно, чтобы правило 1, выделяющее 2 ^и и все последующие повторяющиеся вхождения, было первым правилом в списке, особенно если вы используете два разных цвета.

Выделить повторяющиеся строки

Если вы хотите применить условный формат, когда повторяющиеся значения встречаются в двух или более столбцах, вам нужно будет добавить в таблицу дополнительный столбец, в котором вы объедините значения из ключевых столбцов, используя простую формулу, подобную этой = A2 & B2 .После этого вы применяете правило, используя любой вариант формулы СЧЁТЕСЛИ для дубликатов (с или без вхождений 1 ^st ). Естественно, вы можете скрыть дополнительный столбец после создания правила.

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

В этом примере, чтобы выделить повторяющиеся строки с первыми вхождениями , создайте правило со следующей формулой:
= COUNTIFS ($ A $ 2: $ A $ 11, $ A2, $ B $ 2: $ B $ 11, $ B2) > 1

Чтобы выделить повторяющиеся строки без первых вхождений , используйте следующую формулу:
= СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2, $ B $ 2: $ B2, $ B2)> 1

Сравнить 2 столбца на наличие дубликатов

Одна из наиболее частых задач в Excel - проверка двух столбцов на наличие повторяющихся значений - i.е. найти и выделить значения, которые существуют в обоих столбцах. Для этого вам нужно будет создать правило условного форматирования Excel для каждого столбца с комбинацией функций = ISERROR () и = MATCH () :

Для столбца A: = ISERROR (MATCH (A1, $ B $ 1: $ B $ 10000,0)) = FALSE

для столбца B: = ISERROR (MATCH (B1, $ A $ 1: $ A $ 10000,0)) = FALSE

Примечание. Для правильной работы таких условных формул очень важно применять правила ко всем столбцам, например.г. = $ A: $ и = $ B: $ B.

Вы можете увидеть пример практического использования на следующем снимке экрана, где выделены дубликаты в столбцах E и F.

Как видите, формулы условного форматирования Excel неплохо справляются с дублированием. Однако для более сложных случаев я бы рекомендовал использовать надстройку Duplicate Remover, специально разработанную для поиска, выделения и удаления дубликатов в Excel на одном листе или между двумя электронными таблицами.

Формулы для выделения значений выше или ниже среднего

Когда вы работаете с несколькими наборами числовых данных, функция AVERAGE () может оказаться полезной для форматирования ячеек, значения которых ниже или выше среднего в столбце.

Например, вы можете использовать формулу = $ E2 для условного форматирования строк, в которых количество продаж ниже среднего, как показано на снимке экрана ниже. Если вы ищете обратное, то есть, чтобы затенить продукты, эффективность которых выше среднего, замените "<" на ">" в формуле: = $ E2> AVERAGE ($ E $ 2: $ E $ 8) .


Как выделить ближайшее значение в Excel

Если у меня есть набор чисел, могу ли я использовать условное форматирование Excel, чтобы выделить число в этом наборе, которое наиболее близко к нулю? Это то, что хотела узнать одна из читательниц нашего блога, Джессика.Вопрос очень ясный и простой, но ответ слишком длинный для разделов комментариев, поэтому вы видите здесь решение 🙂

Пример 1. Найдите ближайшее значение, включая точное совпадение

В нашем примере мы найдем и выделим число, которое ближе всего к нулю. Если набор данных содержит один или несколько нулей, все они будут выделены. Если 0 нет, то будет выделено ближайшее к нему значение, положительное или отрицательное.

Во-первых, вам нужно ввести следующую формулу в любую пустую ячейку на вашем листе, вы сможете скрыть эту ячейку позже, если это необходимо.Формула находит число в заданном диапазоне, которое наиболее близко к указанному вами числу, и возвращает абсолютное значение этого числа (абсолютное значение - это число без знака):

= МИН (АБС (B2: D13- (0)))

В приведенной выше формуле B2: D13 - это диапазон ячеек, а 0 - это число, для которого вы хотите найти ближайшее соответствие. Например, если вы ищете значение, наиболее близкое к 5, формула изменится на: = MIN (ABS (B2: D13- (5)))

Примечание. Это формула массива , поэтому вам нужно нажать Ctrl + Shift + Enter вместо простого ввода Enter, чтобы завершить его.

А теперь вы создаете правило условного форматирования со следующей формулой, где B3 - это верхняя правая ячейка в вашем диапазоне, а $ C $ 2 в ячейке с приведенной выше формулой массива:

= ИЛИ (B3 = 0- $ C $ 2, B3 = 0 + $ C $ 2)

Обратите внимание на использование абсолютных ссылок в адресе ячейки, содержащей формулу массива ($ C $ 2), поскольку эта ячейка является постоянной.Кроме того, вам нужно заменить 0 на число, для которого вы хотите выделить ближайшее совпадение. Например, если бы мы хотели выделить ближайшее к 5 значение, формула изменилась бы на: = ИЛИ (B3 = 5- $ C $ 2, B3 = 5 + $ C $ 2)

Пример 2. Выделите значение, наиболее близкое к заданному, но НЕ точное соответствие

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

Например, следующая формула массива находит значение, наиболее близкое к 0 в указанном диапазоне, но игнорирует нули, если они есть:

= МИН (АБС (B3: C13- (0)) + (10 ^ 0 * (B3: C13 = 0)))

Не забудьте нажать Ctrl + Shift + Enter после того, как вы закончите вводить формулу массива.

Формула условного форматирования такая же, как в приведенном выше примере:

= ИЛИ (B3 = 0- $ C $ 2, B3 = 0 + $ C $ 2)

Однако, поскольку наша формула массива в ячейке C2 игнорирует точное совпадение, правило условного форматирования также игнорирует нули и выделяет значение 0,003, которое является наиболее близким совпадением.


Если вы хотите найти значение, ближайшее к какому-либо другому числу в таблице Excel, просто замените «0» нужным числом как в массиве, так и в формулах условного форматирования.

Я надеюсь, что формулы условного форматирования, которые вы изучили в этом руководстве, помогут вам разобраться в любом проекте, над которым вы работаете. Если вам нужно больше примеров, ознакомьтесь со следующими статьями:

Почему условное форматирование в Excel работает неправильно?

Если ваше правило условного форматирования не работает должным образом, хотя формула явно верна, не расстраивайтесь! Скорее всего, это не из-за какой-то странной ошибки в условном форматировании Excel, а из-за крошечной ошибки, не очевидной на первый взгляд.Попробуйте выполнить 6 простых шагов по устранению неполадок ниже, и я уверен, что ваша формула заработает:

1. Правильно используйте абсолютные и относительные адреса ячеек. Очень сложно вывести общее правило, которое будет работать в 100% случаев. Но чаще всего вы будете использовать абсолютный столбец (с $) и относительную строку (без $) в ссылках на ячейки, например = A1> 1 .

   Имейте в виду, что формулы = A1 = 1 , = A $ 1 = 1 и = A $ 1 = 1 дадут разные результаты.Если вы не уверены, какой из них правильный в вашем случае, вы можете попробовать все 🙂 Для получения дополнительной информации см. Относительные и абсолютные ссылки на ячейки в условном форматировании Excel.

2. Проверить применяемый диапазон. Проверьте, применяется ли ваше правило условного форматирования к правильному диапазону ячеек. Основное правило таково - выберите все ячейки / строки, которые вы хотите отформатировать, но не включайте заголовки столбцов.
3. Напишите формулу для верхней левой ячейки. В правилах условного форматирования ссылки на ячейки относятся к самой левой верхней ячейке в применяемом диапазоне.Поэтому всегда пишите формулу условного форматирования для 1-й строки с данными.

   Например, если ваши данные начинаются со строки 2, вы помещаете = A $ 2 = 10 , чтобы выделить ячейки со значениями, равными 10, в всех строках . Распространенная ошибка - всегда использовать ссылку на первую строку (например, = A $ 1 = 10 ). Помните, что вы ссылаетесь на строку 1 в формуле только в том случае, если ваша таблица не имеет заголовков и ваши данные действительно начинаются в строке 1. Наиболее очевидным признаком этого случая является то, что правило работает, но форматирует значения не в тех строках, которые должны .

4. Проверьте созданное правило. Еще раз проверьте правило в диспетчере правил условного форматирования. Иногда без всякой причины Microsoft Excel искажает только что созданное правило. Итак, если правило не работает, перейдите к Условное форматирование> Управление правилами и проверьте формулу и диапазон, к которому она применяется. Если вы скопировали формулу из Интернета или другого внешнего источника, убедитесь, что используются прямые кавычки .
5. Отрегулируйте ссылки на ячейки при копировании правила. Если вы копируете условное форматирование Excel с помощью Format Painter, не забудьте настроить все ссылки на ячейки в формуле.
6. Разбиение сложных формул на простые элементы. Если вы используете сложную формулу Excel, включающую несколько различных функций, разделите ее на простые элементы и проверьте каждую функцию по отдельности.

И, наконец, если вы попробовали все шаги, но ваше правило условного форматирования все еще работает неправильно, напишите мне в комментариях, и мы постараемся разобраться в этом вместе 🙂

В моей следующей статье мы рассмотрим возможности условного форматирования дат в Excel.Увидимся на следующей неделе и спасибо за чтение!

Вас также может заинтересовать

Оператор ЕСЛИ в Excel с несколькими условиями И / ИЛИ, вложенными формулами ЕСЛИ и т. Д.

В части 1 нашего руководства по функциям ЕСЛИ в Excel мы начали изучать основные принципы функции ЕСЛИ в Excel. Как вы помните, мы обсудили несколько формул IF для чисел, дат и текстовых значений, а также то, как написать оператор IF для пустых и непустых ячеек.

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

Как использовать функцию ЕСЛИ в Excel с несколькими условиями

Таким образом, может быть 2 основных типа множественных условий - с логикой И и ИЛИ. Следовательно, ваша функция ЕСЛИ должна включать функцию И или ИЛИ в логический тест, соответственно.

• И функция. Если ваш логический тест содержит функцию И, Microsoft Excel вернет ИСТИНА, если выполнены все условия ; в противном случае возвращается FALSE.
• Функция ИЛИ. Если вы используете функцию ИЛИ в логическом тесте, Excel вернет ИСТИНА, если выполнено любое из условий ; ЛОЖЬ в противном случае.

Чтобы лучше проиллюстрировать суть дела, давайте рассмотрим несколько примеров IF с несколькими условиями.

Пример 1. Использование функции ЕСЛИ & И в Excel

Предположим, у вас есть таблица с результатами двух экзаменов. Первая оценка, указанная в столбце C, должна быть равна или больше 20. Вторая оценка, указанная в столбце D, должна быть равна или превышать 30.Только при соблюдении обоих вышеперечисленных условий студент сдает заключительный экзамен.

Самый простой способ составить правильную формулу - сначала записать условие, а затем включить его в аргумент logical_test функции ЕСЛИ:

Условие: И (B2> = 20, C2> = 30)

Формула ЕСЛИ / И:

= ЕСЛИ ((И (C2> = 20, D2> = 30)), «Пройден», «Не пройден»)

Легко, правда? Формула сообщает Excel, что нужно вернуть «Пройдено», если значение в столбце C> = 20 И значение в столбце D> = 30.В противном случае формула вернет «Неудача». Скриншот ниже подтверждает правильность нашей функции ЕСЛИ / И в Excel:


Примечание. Microsoft Excel проверяет все условия в функции И, даже если одно из уже проверенных условий оценивается как ЛОЖЬ. Такое поведение немного необычно, поскольку в большинстве языков программирования последующие условия не проверяются, если какой-либо из предыдущих тестов вернул FALSE.

На практике кажущаяся правильной формула ЕСЛИ / И может привести к ошибке из-за этой специфичности.Например, приведенная ниже формула вернет «Делить на нулевую ошибку» (# DIV / 0!), Если ячейка A2 равна 0:

.

= ЕСЛИ (И (A2 <> 0, (1 / A2)> 0,5), «Хорошо», «Плохо»)

Во избежание этого следует использовать вложенную функцию ЕСЛИ:

= ЕСЛИ (A2 <> 0, ЕСЛИ ((1 / A2)> 0,5, «Хорошо», «Плохо»), «Плохо»)

Пример 2. Использование ЕСЛИ с функцией ИЛИ в Excel

Аналогичным образом используется комбинация функций ЕСЛИ и ИЛИ. Отличие от формулы ЕСЛИ / И, описанной выше, заключается в том, что Excel возвращает ИСТИНА, если выполняется хотя бы одно из указанных условий.

Итак, если мы изменим приведенную выше формулу следующим образом:

= ЕСЛИ ((ИЛИ (C2> = 20, D2> = 30)), «Пройден», «Не пройден»)

В столбце E будет оценка «Пройдено», если первая оценка равна или больше 20 ИЛИ вторая оценка равна или больше 30.

Как вы видите на скриншоте ниже, у наших студентов больше шансов сдать выпускной экзамен с такими условиями (Скотту особенно не повезло, проиграв всего 1 балл 🙂

Дополнительные примеры формул можно найти в функции Excel ЕСЛИ ИЛИ.

Пример 3. Использование IF с функциями И и ИЛИ

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

В приведенной выше таблице предположим, что у вас есть следующие критерии для оценки успеваемости студентов:

• Условие 1: столбец C> = 20 и столбец D> = 25
• Условие 2: столбец C> = 15 и столбец D> = 20

Если любое из вышеперечисленных условий выполнено, итоговый экзамен считается сданным, в противном случае - неудачным.

Формула может показаться сложной, но через мгновение вы убедитесь, что это не так! Вам просто нужно выразить два условия в виде операторов И и заключить их в функцию ИЛИ, так как вам не требуется, чтобы оба условия выполнялись, достаточно любого:

ИЛИ (И (C2> = 20, D2> = 25), И (C2> = 15, D2> = 20)

Наконец, используйте указанную выше функцию ИЛИ в качестве логической проверки в функции ЕСЛИ и укажите аргументы value_if_true и value_if_false. В результате вы получите следующую формулу ЕСЛИ с несколькими условиями И / ИЛИ:

= ЕСЛИ (ИЛИ (И (C2> = 20, D2> = 25), И (C2> = 15, D2> = 20)), «Пройден», «Не прошел»)

На скриншоте ниже видно, что формула у нас верна:

Естественно, вы не ограничены использованием только двух функций И ​​/ ИЛИ в формулах ЕСЛИ Excel.Вы можете использовать столько логических функций, сколько требует ваша бизнес-логика, при условии, что:

• В Excel 2016, 2013, 2010 и 2007 ваша формула включает не более 255 аргументов, а общая длина формулы не превышает 8192 символа.
• В Excel 2003 и ниже вы можете использовать до 30 аргументов, а общая длина вашей формулы не должна превышать 1024 символа.

Использование нескольких операторов ЕСЛИ в Excel (вложенные функции ЕСЛИ)

Если вам нужно создать более сложные логические тесты для ваших данных, вы можете включить дополнительные операторы IF в аргументы value_if_true и value_if_false ваших формул IF Excel.Эти множественные функции ЕСЛИ называются вложенными функциями ЕСЛИ , и они могут оказаться особенно полезными, если вы хотите, чтобы ваша формула возвращала 3 или более разных результатов.

Вот типичный пример: предположим, вы хотите не просто квалифицировать результаты учащихся как «Успешно / Неудачно», а определить общую оценку как « Хорошо, », « удовлетворительно, » и « Плохо, ». Например:

• Хорошо: 60 или более (> = 60)
• Удовлетворительно: от 40 до 60 (от> 40 до <60)
• Плохо: 40 или меньше (<= 40)

Для начала вы можете добавить дополнительный столбец (E) со следующей формулой, которая суммирует числа в столбцах C и D:

= C2 + D2

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

= ЕСЛИ (E2> = 60, «хорошо», ЕСЛИ (E2> 40, «удовлетворительно», «плохо»))

Как видите, в этом случае достаточно одной вложенной функции ЕСЛИ. Естественно, вы можете вложить больше функций ЕСЛИ, если хотите. Например:

= ЕСЛИ (E2> = 70, «Отлично», ЕСЛИ (E2> = 60, «Хорошо», ЕСЛИ (E2> 40, «Удовлетворительно», «Плохо»)))

Приведенная выше формула добавляет еще одно условие - общая оценка от 70 баллов квалифицируется как «Отлично».

Дополнительные сведения о Excel IF с несколькими условиями см. В разделе Как использовать вложенный IF в Excel.

Использование Excel IF в формулах массива

Как и другие функции Excel, IF можно использовать в формулах массива. Такая формула может понадобиться, если вы хотите оценить каждый элемент массива при выполнении оператора IF.

Например, следующая формула массива СУММ / ЕСЛИ демонстрирует, как можно суммировать ячейки в указанном диапазоне на основе определенного условия, а не складывать фактические значения:

= СУММ (ЕСЛИ (B1: B5 <= 1,1,2))

Формула присваивает определенное количество «баллов» каждому значению в столбце B - если значение равно или меньше 1, оно приравнивается к 1 баллу; и 2 балла присваиваются каждому значению больше 1.Затем функция СУММ складывает полученные единицы и двойки, как показано на скриншоте ниже.

Примечание. Поскольку это формула массива, не забудьте нажать Ctrl + Shift + Enter, чтобы ввести ее правильно.

Использование функции ЕСЛИ вместе с другими функциями Excel

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

Пример 1. Использование IF с функциями SUM, AVERAGE, MIN и MAX

При обсуждении вложенных функций ЕСЛИ мы написали формулу, которая возвращает разные оценки (отлично, хорошо, удовлетворительно или плохо) на основе общего балла каждого учащегося. Как вы помните, мы добавили новый столбец с формулой, которая вычисляет сумму баллов в столбцах C и D.

Но что, если ваша таблица имеет предопределенную структуру, не допускающую никаких изменений? В этом случае вместо добавления вспомогательного столбца вы можете добавлять значения непосредственно в формулу If, например:

= ЕСЛИ ((C2 + D2)> = 60, «Хорошо», ЕСЛИ ((C2 + D2) => 40, «Удовлетворительно», «Плохо»))

Хорошо, но что, если ваша таблица содержит много отдельных оценок, скажем, 5 разных столбцов или больше? Суммирование такого количества цифр непосредственно в формуле ЕСЛИ сделало бы его чрезвычайно большим.Альтернативой является включение функции СУММ в логический тест IF, например:

= ЕСЛИ (СУММ (C2: F2)> = 120, «Хорошо», ЕСЛИ (СУММ (C2: F2)> = 90, «Удовлетворительно», «Плохо»))

Аналогичным образом вы можете использовать другие функции Excel в логической проверке ваших формул ЕСЛИ:

ЕСЛИ и СРЕДНЕЕ:

= ЕСЛИ (СРЕДНИЙ (C2: F2)> = 30, «Хорошо», ЕСЛИ (СРЕДНИЙ (C2: F2)> = 25, «Удовлетворительно», «Плохо»))

Формула перенастраивает «Хорошо», если средний балл в столбцах C: F равен или больше 30, «Удовлетворительно», если средний балл от 29 до 25 включительно, и «Плохо», если меньше 25.

IF и MAX / MIN:

Чтобы найти самый высокий и самый низкий балл, вы можете использовать функции MAX и MIN соответственно. Предполагая, что столбец F является столбцом общих баллов, приведенные ниже формулы работают неплохо:

МАКС: = ЕСЛИ (F2 = МАКС ($ F $ 2: $ F $ 10), "Лучший результат", "")

МИН: = ЕСЛИ (F2 = МИН ($ F $ 2: $ F $ 10), «Худший результат», «»)

Если вы предпочитаете, чтобы результаты Min и Max были в одном столбце, вы можете вложить одну из вышеуказанных функций в другой, например:

= ЕСЛИ (F2 = MAX ($ F $ 2: $ F $ 10), «Лучший результат», IF (F2 = MIN ($ F $ 2: $ F $ 10), «Худший результат», «»))

Аналогичным образом можно использовать функцию ЕСЛИ с функциями настраиваемого рабочего листа.Например, вы можете использовать его с функциями GetCellColor / GetCellFontColor для возврата различных результатов в зависимости от цвета ячейки.

Кроме того, Excel предоставляет ряд специальных функций ЕСЛИ для анализа и расчета данных в различных условиях.

Например, чтобы подсчитать количество появлений текстового или числового значения на основе одного или нескольких условий, вы можете использовать COUNTIF и COUNTIFS соответственно. Чтобы узнать сумму значений на основе указанного условия (условий), используйте функции СУММЕСЛИ или СУММЕСЛИМН.Чтобы вычислить среднее значение по определенным критериям, используйте СРЕДНЕЕСЛИ или СРЕДНИМЕСЛИ.

Подробные пошаговые примеры формул можно найти в следующих руководствах:

Пример 2. ЕСЛИ с функциями ISNUMBER и ISTEXT

Вы уже знаете способ выделить пустые и непустые ячейки с помощью функции ISBLANK. Microsoft Excel предоставляет аналогичные функции для идентификации текстовых и числовых значений - ISTEXT и ISNUMBER соответственно.

Вот пример вложенной функции ЕСЛИ Excel, которая возвращает «Текст», если ячейка B1 содержит какое-либо текстовое значение, «Число», если B1 содержит числовое значение, и «Пусто», если ячейка B1 пуста.

= ЕСЛИ (ISTEXT (B1), «Текст», IF (ISNUMBER (B1), «Number», IF (ISBLANK (B1), «Blank», «»)))

Примечание. Обратите внимание, что в приведенной выше формуле отображается « Число » для числовых значений и дат. Это связано с тем, что Microsoft Excel хранит даты в виде чисел, начиная с 1 января 1900 г., что равно 1.

Пример 3. Использование результата, возвращенного IF, в другой функции Excel

Иногда можно достичь желаемого результата, встраивая оператор IF в какую-либо другую функцию Excel, а не используя другую функцию в логическом тесте.

Вот еще один способ совместного использования функций CONCATINATE и IF:

= CONCATENATE («Вы выполнили», ЕСЛИ (C1> 5, «фантастика!», «Хорошо»))

Я считаю, что вам вряд ли нужно объяснять, что делает формула, особенно если посмотреть на снимок экрана ниже:

Функция ЕСЛИ против ЕСЛИ ОШИБКА и IFNA

Обе функции, ЕСЛИОШИБКА и IFNA, используются для перехвата ошибок в формулах Excel и замены их другим вычислением, предопределенным значением или текстовым сообщением.В более ранних версиях Excel вместо этого можно использовать комбинации IF ISERROR и IF ISNA.

Разница в том, что IFERROR и ISERROR обрабатывают все возможные ошибки Excel, включая #VALUE !, # N / A, #NAME?, #REF !, #NUM !, # DIV / 0 !, и #NULL !. IFNA и ISNA специализируются исключительно на ошибках # N / A.

Вот простейший пример формулы ЕСЛИОШИБКА:

= ЕСЛИОШИБКА (B2 / C2, «К сожалению, произошла ошибка»)

Как видно на скриншоте выше, столбец D отображает частное деления значения в столбце B на значение в столбце C.Вы также можете увидеть два сообщения об ошибках в ячейках D2 и D5, потому что все знают, что нельзя делить число на ноль.

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

= ЕСЛИ (C2 = 0, «Извините, произошла ошибка», B2 / C2)

И это все, что я могу сказать об использовании функции ЕСЛИ в Excel.Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Вас также может заинтересовать

Создание условных формул - Excel

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

А B Данные 15 9 8 Звездочки Виджеты Формула Описание (результат) = ЕСЛИ (A2 = 15, «ОК», «НЕ ОК») Если значение в ячейке A2 равно 15, вернуть «ОК.«В противном случае верните« Not OK ». (OK) = ЕСЛИ (A2 <> 15, «ОК», «НЕ ОК») Если значение в ячейке A2 не равно 15, верните «ОК». В противном случае верните «Не в порядке». (Не в порядке) = ЕСЛИ (НЕ (A2 <= 15), «ОК», «НЕ ОК») Если значение в ячейке A2 не меньше или равно 15, вернуть «ОК.«В противном случае верните« Not OK ». (Not OK) = ЕСЛИ (A5 <> «ЗВЕЗДЫ», «ОК», «НЕ ОК») Если значение в ячейке A5 не равно «ЗВЕЗДОЧКИ», верните «ОК». В противном случае верните «Не в порядке». (Не в порядке) = ЕСЛИ (И (A2> A3, A2 Если значение в ячейке A2 больше, чем значение в A3, а значение в A2 также меньше, чем значение в A4, верните «OK.«В противном случае верните« Not OK ». (Not OK) = ЕСЛИ (И (A2 <> A3, A2 <> A4), «ОК», «Не в порядке») Если значение в ячейке A2 не равно A3, а значение в A2 также не равно значению в A4, верните «OK». В противном случае верните «Не в порядке». (ОК) = ЕСЛИ (ИЛИ (A2> A3, A2 Если значение в ячейке A2 больше значения в A3 или значение в A2 меньше значения в A4, верните «OK.«В противном случае верните« Not OK ». (OK) = ЕСЛИ (ИЛИ (A5 <> «Звездочки», A6 <> «Виджеты»), «ОК», «Не в порядке») Если значение в ячейке A5 не равно «Sprockets» или значение в A6 не равно «Widgets», верните «OK». В противном случае верните «Не в порядке». (Не в порядке) = ЕСЛИ (ИЛИ (A2 <> A3, A2 <> A4), «ОК», «Не ОК») Если значение в ячейке A2 не равно значению в A3 или значение в A2 не равно значению в A4, верните «OK.«В противном случае верните« Not OK ». (OK)

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

Примечание. До Excel 365 в Excel не было специальной функции для подсчета уникальных значений. Эта формула показывает один способ подсчета уникальных значений, если они являются числовыми. Если у вас есть текстовые значения или сочетание текста и ...

Раскрывающийся список настроен с помощью простого правила проверки данных на основе «списка»: Красный, синий, зеленый, все Именованные диапазоны «цвет» (C5: C15) и «количество» (D5: D15) предназначены только для удобства.Формула в G5 выполняет ...

По сути, эта формула использует ИНДЕКС для создания такой расширяющейся ссылки: ИНДЕКС ([Color], 1): [@ Color] // расширение диапазона В левой части двоеточия (:) функция ИНДЕКС возвращает ссылку на ...

Правила проверки данных срабатывают, когда пользователь добавляет или изменяет значение ячейки. В этом случае нам нужна формула, которая возвращает ЛОЖЬ, если сумма записей в C6: C9 равна 1000 или меньше.Используем СУММ ...

В простейшей форме SMALL возвращает «N-е наименьшее» значение в диапазоне с помощью этой конструкции: = МАЛЕНЬКИЙ (диапазон; N) Так, например: = МАЛЕНЬКИЙ (C5: C12,2) вернет 2-е наименьшее значение в диапазоне C5: C12, которое составляет ...

В этой формуле используется так называемая «смешанная ссылка» для создания «расширяющегося диапазона». Смешанная ссылка - это ссылка, которая включает как абсолютную, так и относительную части.В данном случае формула СУММ относится к диапазону ...

Ядром этой формулы является СЧЁТЕСЛИ, которая настроена на подсчет трех отдельных значений с использованием подстановочных знаков: СЧЁТЕСЛИ (B5; {"x *", "y *", "z *"} Звездочка (*) - это подстановочный знак для одного или нескольких ...

Цель этой формулы - продемонстрировать, как другие формулы и функции могут быть вложены в функцию ЕСЛИ. Функция ЕСЛИ принимает три таких аргумента: = ЕСЛИ (логический_тест; значение_если_ истинно; значение_если_ ложь...

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

В этой формуле используются структурированные ссылки для ссылки на столбец «Сумма» в каждой таблице. Структурированные ссылки в этой формуле преобразуются в обычные ссылки, например: = СУММ (Таблица1 [сумма], Таблица2 [сумма]) = СУММ (C7: C11...

Функция СУММПРОИЗВ принимает один или несколько массивов, умножает массивы вместе и возвращает «сумму продуктов» в качестве окончательного результата. Если указан только один массив, СУММПРОИЗВ просто возвращает сумму элементов в ...

Средневзвешенное значение, также называемое средневзвешенным, - это среднее значение, при котором одни значения имеют большее значение, чем другие. Другими словами, некоторые значения имеют больший «вес». Мы можем рассчитать средневзвешенное значение, умножив значения на...

Проблема с формулами поиска, которые извлекают более одного совпадения, заключается в управлении дубликатами (т. Е. Множественными совпадениями). Формулы поиска, такие как ВПР и ИНДЕКС + ПОИСКПОЗ, могут легко найти первое совпадение, но это намного сложнее ...

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

По сути, эта формула имеет такую ​​простую схему: = СУММ (первый: текущий) Где «первая» - это первая ячейка в столбце «Итого», а «текущая» - это ссылка на ячейку в текущей строке столбца «Итого». Кому ...

Условное форматирование с формулами (10 примеров)

Условное форматирование - отличный способ быстро визуализировать данные в электронной таблице. С помощью условного форматирования вы можете делать такие вещи, как выделять даты в следующие 30 дней, отмечать проблемы с вводом данных, выделять строки, содержащие основных клиентов, отображать дубликаты и многое другое.

Excel поставляется с большим количеством «предустановок», которые позволяют легко создавать новые правила без формул. Однако вы также можете создавать правила со своими собственными формулами. Используя собственную формулу, вы берете на себя условие, которое запускает правило, и можете применять именно ту логику, которая вам нужна. Формулы дают вам максимальную мощность и гибкость.

Например, с помощью предустановки «Равно» легко выделить ячейки, равные «яблоку».

Но что, если вы хотите выделить ячейки, равные «яблоку», «киви» или «лайму»? Конечно, вы можете создать правило для каждого значения, но это большая проблема.Вместо этого вы можете просто использовать одно правило на основе формулы с функцией ИЛИ:

Вот результат применения правила к диапазону B4: F8 в этой таблице:

Вот точная формула:

 
 = ИЛИ (B4 = «яблоко», B4 = «киви», B4 = «лайм») 

Быстрый запуск

Вы можете создать правило условного форматирования на основе формул за четыре простых шага:

1. Выберите ячейки, которые нужно отформатировать.

2.Создайте правило условного форматирования и выберите вариант формулы

3. Введите формулу, которая возвращает ИСТИНА или ЛОЖЬ.

4. Задайте параметры форматирования и сохраните правило.

Функция ISODD возвращает ИСТИНА только для нечетных чисел, срабатывая правило:

Видео: Как применить условное форматирование с формулой

Логика формул

Формулы, применяющие условное форматирование, должны возвращать ИСТИНА или ЛОЖЬ или их числовые эквиваленты.Вот несколько примеров:

Все приведенные выше формулы возвращают ИСТИНА или ЛОЖЬ, поэтому они отлично работают как триггер для условного форматирования.

Когда условное форматирование применяется к диапазону ячеек, введите ссылки на ячейки относительно первой строки и столбца в выделенном фрагменте (т. Е. В верхней левой ячейке). Уловка для понимания того, как работают формулы условного форматирования, заключается в том, чтобы визуализировать одну и ту же формулу, применяемую к каждой ячейке в выделенном фрагменте , при этом ссылки на ячейки обновляются как обычно.Представьте, что вы ввели формулу в левую верхнюю ячейку выделенного фрагмента, а затем скопировали формулу по всему выделенному фрагменту. Если вы боретесь с этим, см. Раздел «Формулы-пустышки» ниже.

Примеры формул

Ниже приведены примеры настраиваемых формул, которые можно использовать для применения условного форматирования. Некоторые из этих примеров могут быть созданы с использованием встроенных предустановок Excel для выделения ячеек, но пользовательские формулы могут выходить далеко за рамки предустановок, как вы можете видеть ниже.

Основные заказы из Техаса

Чтобы выделить строки, которые представляют заказы из Техаса (сокращенно TX), используйте формулу, которая блокирует ссылку на столбец F:

Дополнительные сведения см. В этой статье: Выделение строк с помощью условного форматирования.

Видео: Как выделить строки с условным форматированием

Выделите даты в ближайшие 30 дней

Чтобы выделить даты, наступающие в ближайшие 30 дней, нам нужна формула, которая (1) гарантирует, что даты находятся в будущем, и (2) гарантирует, что даты находятся в 30 днях или меньше от сегодняшнего дня. Один из способов сделать это - использовать функцию И вместе с функцией СЕЙЧАС, например:

С текущей датой 18 августа 2016 года условное форматирование выделяет даты следующим образом:

Функция СЕЙЧАС возвращает текущую дату и время.Подробнее о том, как работает эта формула, см. В этой статье: Выделение дат в ближайшие N дней.

Выделить различия столбцов

Учитывая два столбца, которые содержат аналогичную информацию, вы можете использовать условное форматирование, чтобы выявить тонкие различия. Формула, используемая для запуска форматирования ниже:

См. Также: версия этой формулы, в которой функция ТОЧНОСТЬ используется для сравнения с учетом регистра.

Выделить отсутствующие значения

Чтобы выделить значения в одном списке, которые отсутствуют в другом, можно использовать формулу, основанную на функции СЧЁТЕСЛИ:

Эта формула просто проверяет каждое значение в списке List A на соответствие значениям в именованном диапазоне «список» (D5: D10).Когда счетчик равен нулю, формула возвращает ИСТИНА и запускает правило, которое выделяет значения в List A , которые отсутствуют в List B .

Видео: как найти пропущенные значения с помощью COUNTIF

Выделите недвижимость с 3 и более спальнями до $ 350 000

Чтобы найти в этом списке объекты недвижимости с как минимум 3 спальнями, но стоимостью менее 300 000 долларов США, вы можете использовать формулу, основанную на функции И:

Знак доллара ($) фиксирует ссылку на столбцы C и D, а функция AND используется для проверки ИСТИНА обоих условий.В строках, где функция И возвращает ИСТИНА, применяется условное форматирование:

Выделить верхние значения (динамический пример)

Хотя в Excel есть предустановки для «верхних значений», в этом примере показано, как сделать то же самое с формулой и как формулы могут быть более гибкими. Используя формулу, мы можем сделать рабочий лист интерактивным - когда значение в F2 обновляется, правило мгновенно реагирует и выделяет новые значения.

Формула, используемая для этого правила:

Где «данные» - это именованный диапазон B4: G11, а «вход» - именованный диапазон F2.На этой странице есть подробности и полное объяснение.

Диаграммы Ганта

Вы не поверите, но вы даже можете использовать формулы для создания простых диаграмм Ганта с условным форматированием, например:

На этом листе используются два правила: одно для полосок, а другое - для затенения выходных дней:

 
 = AND (D $ 4> = $ B5, D $ 4 <= $ C5) // бары
= WEEKDAY (D $ 4,2)> 5 // выходные 

В этой статье объясняется формула для столбцов, а в этой статье объясняется формула для затенения в выходные дни.

Простое окно поиска

Один интересный трюк, который вы можете сделать с условным форматированием, - это создать простое окно поиска. В этом примере правило выделяет ячейки в столбце B, содержащие текст, введенный в ячейку F2:

.

Используемая формула:

Для получения дополнительной информации и полного объяснения см .:

Устранение неисправностей

Если правила условного форматирования не срабатывают правильно, скорее всего, проблема связана с формулой.Во-первых, убедитесь, что вы начали формулу со знака равенства (=). Если вы забудете этот шаг, Excel незаметно преобразует всю формулу в текст, делая ее бесполезной. Чтобы исправить это, просто удалите двойные кавычки, добавленные Excel с обеих сторон, и убедитесь, что формула начинается с равно (=).

Если формула введена правильно, но правило не срабатывает, возможно, вам придется копнуть немного глубже. Обычно вы можете использовать клавишу F9 для проверки результатов в формуле или использовать функцию Evaluate для пошагового выполнения формулы.К сожалению, вы не можете использовать эти инструменты с формулами условного форматирования, но вы можете использовать технику, называемую «фиктивными формулами».

Формулы-пустышки

Формулы-пустышки - это способ проверить формулы условного форматирования прямо на листе, чтобы вы могли видеть, что они на самом деле делают. Это может значительно сэкономить время, когда вы изо всех сил пытаетесь заставить ссылки на ячейки работать правильно.

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

Видео: проверка условного форматирования с помощью фиктивных формул

Ограничения

Условное форматирование на основе формул имеет некоторые ограничения:

1. Вы не можете применять значки, цветовые шкалы или гистограммы с настраиваемой формулой. Вы ограничены стандартным форматированием ячеек, включая числовые форматы, шрифт, цвет заливки и параметры границы.
2. Вы не можете использовать определенные конструкции формул, такие как объединения, пересечения или константы массива, для критериев условного форматирования.
3. Вы не можете ссылаться на другие книги в формуле условного форматирования.

Иногда можно обойти № 2 и № 3. Вы можете переместить логику формулы в ячейку на листе, а затем вместо этого ссылаться на эту ячейку в формуле. Если вы пытаетесь использовать константу массива, попробуйте вместо этого создать именованный диапазон.

Дополнительные ресурсы по формуле CF

.