Как прописать формулу с условием в экселе: Формула условия в excel

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

10074
20.02.2013
Скачать пример

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

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

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

Способ 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)

8.3 Условные функции — Excel для принятия решений

Мы использовали функции, начиная с главы 2, для возврата значений на основе математических и статистических функций, таких как СУММ, СРЗНАЧ и СЧЁТ. В главе 3 мы узнали, как настроить логические тесты, заставить Excel оценить наши условия для всех элементов в наших диапазонах, а затем обработать все значения для нас. В главе 5 мы изучили, как интерпретировать направления, настраивать параметры и фильтровать наши данные в зависимости от множества критериев, используя таблицы Excel, фильтры и слайсеры.

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

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

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

Таблица 1: Математические и статистические функции.

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

СУММЕСЛИ по существу спрашивает: что вы хотите сложить, исходя из каких критериев. Синтаксис СУММЕСЛИ начинается с нашей функции, затем в круглых скобках мы должны указать Excel, каков диапазон значений (текст или числа, пробелы будут игнорироваться), мы хотим, чтобы он суммировался на основе каких критериев . Наши критерии могут быть одним значением, например числом 42 или «> 42», ссылкой на ячейку, где находится наш критерий. Если вы используете текст или операторы =, >, >= и т. д., убедитесь, что они заключены между ” “, иначе Excel вернет ошибку. Аргумент [SUM_RANGE] в квадратных скобках указывает, что эта часть аргумента является необязательной, мы можем использовать ее, если хотим сложить что-то кроме диапазон мы указали ранее.

Например, мы хотим знать, сколько студентов в штате Техас. В предыдущих главах мы изучили различные способы ответа на этот вопрос. Теперь мы хотим использовать нашу условную функцию SUM, чтобы вернуть это значение.

1. Откройте файл Excel с данными оценочной карты колледжа. (Вы можете скачать новую копию отсюда.)
2. Вставьте несколько дополнительных строк над таблицей данных. Подойдет три или четыре.
3. Учитывая, что в этом наборе данных более 120 столбцов, вы можете выбрать, щелкнуть правой кнопкой мыши и скрыть столбцы, которые в данный момент не используете.
4. Преобразуйте свой диапазон в таблицу Excel, чтобы в формуле использовались ваши имена полей (заголовки столбцов)
5. Щелкните любую из ячеек над первыми несколькими столбцами и начните ввод формулы, введя знак =.
6. Теперь вам нужно указать Excel, что вы хотите, чтобы он просматривал диапазон сокращений штатов (STABBR), используйте TX в качестве критерии , затем СУММУЛИРУЙТЕ значения в столбце, показывающем количество студентов (UGDS). Попробуйте сделать это самостоятельно или воспользуйтесь рисунком ниже. Обратите внимание, как структура таблицы Excel позволяет легко увидеть, на какой столбец вы ссылаетесь, в отличие от использования ссылок на диапазоны.
   Рис. 1. Функция СУММЕСЛИ в использовании.
7. Обратите внимание на цветные границы вокруг каждого активного диапазона в формуле.
8. Теперь продолжайте и проверьте ответ, используя другие способы получения правильного ответа. Таким образом, вы можете подтвердить, что ваша формула выполнила свою работу. Вы можете создать таблицу Excel и отфильтровать ее по Техасу, а затем использовать итоговую строку, чтобы СУММИТЬ количество студентов. Вы также можете создать сводную таблицу, используя поля «Сокращение штата» и «Количество студентов», чтобы вернуть выходные данные. Совпадают ли ваши ответы?
9. Сделайте еще несколько вариантов ответа на этот вопрос в зависимости от городов, штатов, регионов, типов МЕСТНОСТИ и многого другого!

Функция СУММЕСЛИМН суммирует все аргументы, соответствующие нескольким критериям (support. office.com).

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

=СУММЕСЛИМН(сумма_диапазон, критерий_диапазон1, критерий1, [критерий_диапазон2, критерий2], …)

Используйте набор данных College Scorecard, чтобы узнать количество студентов в городе Хьюстон, штат Техас, зачисленных на двухгодичные программы обучения. Обратитесь к своему словарю данных, чтобы выбрать поля, которые необходимо включить в расчет. Перечислите их на листе бумаги или в простом файле на своем компьютере, который вы можете поместить рядом с файлом данных вашей оценочной карты колледжа.

Если вы застряли, вы можете обратиться к этому снимку экрана. (Номера таблиц [Таблица 1, Таблица 2] будут отражены, если вы преобразуете обратно в диапазон, чтобы свести данные, а затем заново создадите таблицу…) Когда закончите, используйте таблицу Excel или сводную таблицу, чтобы получить тот же ответ.

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

=СЧЁТЕСЛИ(диапазон, критерии)

Синтаксис COUNTIF начинается со знака равенства, за которым следует наша функция, а затем в круглых скобках мы должны указать Excel, какой диапазон значений мы хотим, чтобы он подсчитывал, на основе каких критериев . Как и в случае СУММЕСЛИ, наши критерии могут быть одним значением, например, числом 42 или «> 42», ссылкой на ячейку, где находится наш критерий. Если вы используете текст или операторы =, >, >= и т. д., убедитесь, что они заключены между ” “, иначе Excel вернет ошибку. У нас нет необязательных аргументов, как в случае с СУММЕСЛИ.

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

1. Щелкните любую из ячеек над первыми несколькими столбцами и начните ввод формулы, введя знак =.
2. Теперь вам нужно указать Excel, что вы хотите, чтобы он СЧИТАЛ. Используйте диапазон сокращений штатов (STABBR) и TX в качестве критериев . Попробуйте сделать это самостоятельно или используйте рисунок ниже. Обратите внимание, как структура таблицы Excel позволяет легко увидеть, на какой столбец вы ссылаетесь, в отличие от использования ссылок на диапазоны.
   Рис. 2. Функция СЧЁТЕСЛИ в работе.
3. Обратите внимание на цветные границы вокруг каждого активного диапазона в формуле.
4. Еще раз проверьте ответ, используя другие способы получения правильного ответа. Таким образом, вы можете подтвердить, что ваша формула выполнила свою работу. Вы можете отфильтровать свою таблицу Excel для Техаса, а затем использовать итоговую строку, чтобы СЧИТАТЬ названия учреждений или аббревиатуры штатов. Вы также можете создать сводную таблицу, используя поля «Сокращение штата» и «Названия учреждений», чтобы получить результат. Совпадают ли ваши ответы?
5. Сделайте еще несколько вариантов ответа на этот вопрос в зависимости от городов, штатов, регионов, типов МЕСТНОСТИ и многого другого!

Функция СЧЁТЕСЛИМН применяет критерии к ячейкам в нескольких диапазонах и подсчитывает количество совпадений всех критериев (support. office.com).

=СЧЕТЧИСЛ(диапазон_критериев1, диапазон_критериев1, [диапазон_критериев2, критерии2]…)

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

Если вы застряли, вы можете обратиться к этому снимку экрана. (Номера таблиц [Таблица 1, Таблица 2] будут отражены, если вы преобразуете обратно в диапазон, чтобы свести данные, а затем заново создадите таблицу…) Когда закончите, используйте таблицу Excel или сводную таблицу, чтобы получить тот же ответ.

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

Еще упражнения СЧЁТЕСЛИМН:

• Сколько в Аризоне онлайновых учебных заведений?
• Сколько учебных заведений, в которых более 50% студентов принимают федеральный кредит и более 50% получают грант PELL в штате Калифорния?
• Сколько учебных заведений находится в субрегионе восток-север-центр в крупных городах?

=СРЗНАЧЕСЛИ(диапазон, критерии, [средний_диапазон])

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

1. Щелкните любую из ячеек над первыми несколькими столбцами и начните ввод формулы, введя знак =.
2. Теперь вам нужно указать Excel, что вы хотите использовать СРЕДНЕЕ значение. Используйте диапазон сокращений штатов (STABBR) и TX в качестве критериев . Попробуйте сделать это самостоятельно или воспользуйтесь рисунком ниже. Обратите внимание, как структура таблицы Excel позволяет легко увидеть, на какой столбец вы ссылаетесь, в отличие от использования ссылок на диапазоны.
   Рисунок 3. AVERAGEIF в использовании.
3. Обратите внимание на цветные границы вокруг каждого активного диапазона в формуле.
4. Еще раз проверьте ответ, используя другие способы получения правильного ответа. Таким образом, вы можете подтвердить, что ваша формула выполнила свою работу. Вы можете отфильтровать свою таблицу Excel для Техаса, а затем использовать итоговую строку, чтобы СЧИТАТЬ названия учреждений или аббревиатуры штатов. Вы также можете создать сводную таблицу, используя поля «Сокращение штата» и «Названия учреждений», чтобы получить результат. Совпадают ли ваши ответы?
5. Сделайте еще несколько вариантов ответа на этот вопрос в зависимости от городов, штатов, регионов, типов МЕСТНОСТИ и многого другого!

Функция СРЗНАЧЕСЛИМН возвращает среднее (среднее арифметическое) всех ячеек, соответствующих нескольким критериям (источник: support. office.com).

=СРЗНАЧЕСЛИМН(средний_диапазон, критерий_диапазон1, критерий1, [критерий_диапазон2, критерий2], …)

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

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

Больше упражнений AVERAGEIFS:

• Каков средний процент студентов, изучающих психологию в штате Калифорния?
• Какой средний балл ACT(SAT) в учебных заведениях с 4-летним обучением в штате Нью-Йорк?
• Каков средний процент студентов, которые зарабатывают 25 тысяч или более через 6 лет после выпуска в Аризоне в онлайн-университетах?

Глава 8. 3 от Emese Felvegi лицензируется в соответствии с CC BY 4.0

Атрибуты мультимедиа

• СУММЕСЛИ
• СЧЕТЕСЛИ
• СРЗНАЧЕСЛИ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Совет. Чтобы ваша формула условного форматирования Excel работала правильно, всегда следуйте этим простым правилам.

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

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

На снимке экрана ниже показан пример Больше, чем формула , которая выделяет названия продуктов в столбце 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.

На снимке экрана ниже мы используем формулу =И($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 для пустых ячеек.

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

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

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

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

=ПОИСК("По всему миру", $D2)>0

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

=ПОИСК("Worldwide", $D2)>1

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

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

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

Выделить повторяющиеся значения, включая 1

^st вхождений

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

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

Выделение дубликатов без 1

^st вхождений

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

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

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

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

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

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

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

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

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

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

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

Одной из наиболее частых задач в Excel является проверка двух столбцов на наличие повторяющихся значений. то есть найти и выделить значения, которые существуют в обоих столбцах. Для этого вам потребуется создать правило условного форматирования Excel для каждого столбца с комбинацией =ЕОШИБКА() и =ПОИСКПОЗ() функции:

Для столбца A: =ЕОШИБКА(ПОИСКПОЗ(A1,$B$1:$B$10000,0))=ЛОЖЬ

Для столбца B: =ЕОШИБКА(ПОИСКПОЗ(B1,$A$1:$A$10000,0))=ЛОЖЬ

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

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

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

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

При работе с несколькими наборами числовых данных функция СРЗНАЧ() может пригодиться для форматирования ячеек, значения которых ниже или выше среднего в столбце.

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

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

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

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

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

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

=MIN(ABS(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, формула изменится на: =OR(B3=5-$C$2,B3=5+$C$2)

Пример 2. Выделение значения ближайший к заданному значению, но НЕ точное совпадение 90*(B3:C13=0)))

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

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

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

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

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

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

• Как изменить цвет строки на основе значения ячейки
• Условное форматирование Excel для дат
• Альтернативные цвета строк и столбцов в Excel
• Два способа изменить цвет фона на основе значения ячейки
• Подсчет и суммирование цветных ячеек в Excel

Почему условное форматирование в 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, не забудьте настроить все ссылки на ячейки в формуле.