Как в excel создать условие: как сделать функцию – WindowsTips.Ru. Новости и советы

Содержание

Условное форматирование в Microsoft Excel

Виктор Бухтеев

24 янв в 2022

8.1K

Обсудить

Личный опыт
#Программы
#Microsoft

15 мин. чтения

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

Правила выделения ячеек

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

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

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

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

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

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

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

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

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

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

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

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

Я настроил подсветку первых шести элементов, а это значит, что теперь в моем диапазоне будут подсвечены первые 6 самых больших чисел.

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

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

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

Гистограммы

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

Наведите курсор на правило «Гистограммы» и выберите подходящий тип оформления. По умолчанию предлагается 12 вариантов.

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

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

Цветовые шкалы

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

Откройте список всех типов цветовых шкал и выберите среди них подходящий цветовой градиент.

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

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

Наборы значков

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

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

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

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

Создание своего правила

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

Разверните «Условное форматирование» и выберите «Создать правило».

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

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

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

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

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

Личный опыт

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

Ад Условного Форматирования

50238
05.04.2020
Скачать пример

Порядок необходим глупцам,
гений же властвует над хаосом.
(Альберт Эйнштейн)

Исходные данные

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

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

Для наглядности к таблице добавлены три правила условного форматирования:

Первое правило делает синие гистограммы на столбце с суммами сделок. Создается через Главная — Условное форматирование — Гистограммы (Home — Conditional formatting — Data bars).

Второе — подсвечивает желтым ячейки с именами менеджеров, которые не выполнили план, т.е. сумма их сделки меньше, чем зелёная ячейка h3.

Третье — делает нижнюю границу всей строки красной, если день меняется на следующий, т.е. дата в текущей строке не равна дате в следующей.

Второе и третье правила создаются через Главная — Условное форматирование — Создать правило — Использовать формулу для определения форматируемых ячеек (Home — Conditional formatting — Create rule — Use formula to determine which cells to format) с вводом соответствующей формулы (2) и настройкой формата ячеек (3):

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

Путь к катастрофе

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

Красная линия между 2 и 3-м марта почему-то исчезла, а наше правило условного форматирования для разделения дат развалилось на два, причем одно из них с ошибкой #ССЫЛКА (т.е. не работает), а другое применяется к двум несмежным диапазонам A2:E8 и A10:E29 (не ко всей таблице!).

Шикарно, правда?!

Теперь представим, что Кирилл Краснов повторил свою сделку в Тольятти с магазином «Лента» (строка 25) и вам нужно внести эти данные в таблицу.

Как вы поступите?

Скорее всего, как любой нормальный человек, вы скопируете 25-ю строчку и вставите её в конец таблицы, верно?

Ага, и получите в наследство вот такой бардак в правилах условного форматирования:

Excel зачем-то продублировал те же правила для добавленной строки вместо того, чтобы просто растянуть диапазон в поле Применяется к (Applied to).

Ну, и на десерт давайте попробуем ещё что-нибудь безобидное — например, вставить пустую строку в середину таблицы, между 4 и 5-й строчками:

В списке правил условного форматирования это приведёт к появлению еще одного дубликата и раздроблению диапазона уже существующего 5-го правила на кучу фрагментов:

Продолжать можно долго, но, думаю, вы уже уловили идею или вспомнили, как сталкивались с этой бедой ранее (эта проблема существует в Excel ещё с 2007 года). Выполнение совершенно безобидных и естественных операций с таблицей (вставка и удаление строк, копирование, вырезание и перенос) приводят к:

появлению бесчисленных дубликатов одних и тех же правил

фрагментации диапазонов применения этих правил

появлению неработающих правил с ошибками #ССЫЛКА!

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

На англоязычных Excel-форумах в интернете такую картину называют иногда «адом» или «кошмаром условного форматирования» («Conditional Formatting Nightmare» или «Conditional Formatting Hell»).

Причем весь этот быстро разрастающийся бардак очень скоро начнёт нещадно тормозить. Условное форматирование, само по себе, весьма ресурсоёмкая штука, т.к. Excel пересчитывает правила УФ гораздо чаще, чем те же формулы. А когда этих правил несколько десятков, то даже самый мощный ПК начнёт «тупить».

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

Как же всё исправить?

Способ 1. Вручную

Несмотря на кажущуюся запущенность, лечится весь этот адок достаточно легко. Идея в том, что правила УФ «ломаются», обычно, для строк ниже первой. Первая же строка, в большинстве случаев, остается в порядке. Поэтому, чтобы всё починить, нам нужно просто очистить все правила в таблице и заново распространить их с первой строки на все остальные.

Для этого делаем следующее:

Выделяем в нашей таблице все строки кроме первой.

Удаляем все правила условного форматирования с выделенных ячеек через Главная — Условное форматирование — Удалить правила — Удалить правила из выделенных ячеек (Home — Conditional formatting — Clear rules — Clear rules from selected cells).

Выделяем первую строку, жмём кнопку-кисточку Формат по образцу на Главной (Home — Format Painter) и выделяем все остальные строки, копируя на них формат с первой.

Способ 2. Макросом

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

Жмём сочетание клавиш Alt+F11 или на вкладке Разработчик кнопку Visual Basic (Developer — Visual Basic).

В открывшемся окне редактора макросов добавляем в нашу книгу новый модуль через меню Insert — Module.

Вставляем в созданный пустой модуль наш макрос:

Sub Fix_СF_Hell()

    'создаем ссылки на диапазоны
    Set rngAll = Selection
    Set rngRow1 = Selection.Rows(1)
    Set rngRow2 = Selection.Rows(2)
    Set rngRowLast = Selection.Rows(rngAll.Rows.Count)

    'удаляем все правила форматирования со всех строк кроме первой
    Range(rngRow2, rngRowLast).FormatConditions.Delete
    
    'копируем форматы с первой строки на все остальные
    rngRow1.Copy
    Range(rngRow1, rngRowLast).PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
    
End Sub

Теперь можно будет просто выделить все строки в таблице (кроме шапки) и запустить макрос через Разрабочик — Макросы (Developer — Macros) или сочетанием клавиш Alt+F8.

И всё будет хорошо :)

P.S.

И не забудьте сохранить файл в формате с поддержкой макросов (xlsm).

Если нужно применять этот макрос в других файлах, то имеет смысл поместить его в Личную Книгу Макросов (Personal Macro Workbook).

Немного улучшенная версия этого макроса уже встроена в последнюю версию моей надстройки PLEX ;)

Ссылки по теме

Как работает условное форматирование в Excel

Подсветка наборов строк с помощью условного форматирования

Разделительная линия между наборами строк

Видео: Использование формул для применения условного форматирования

Промежуточное условное форматирование

Обучение Эксель 2013.

Промежуточное условное форматирование

Используйте формулы

Промежуточное условное форматирование
видео
Используйте формулы
видео
Управление условным форматированием
видео

Используйте условное форматирование

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

Хотите больше?

Используйте условное форматирование

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

В этом примере я собираюсь отформатировать ячейки в столбце «Продукт», если соответствующая ячейка в столбце «На складе» больше 300.

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

При выборе диапазона ячеек первая выбранная ячейка является активной ячейкой.

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

Создайте новое правило, выберите Используйте формулу, чтобы определить, какие ячейки форматировать . Поскольку B2 является активной ячейкой, я набираю =E2>300.

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

Я нажимаю кнопку Формат и выбираю способ форматирования ячеек. Я собираюсь использовать синюю заливку.

Нажмите OK , чтобы принять цвет; щелкните OK еще раз, чтобы применить формат.

И ячейки в столбце Продукт, где соответствующая ячейка в столбце Е больше 300, форматируются условно.

Когда я изменяю значение в столбце E на значение больше 300, автоматически применяется условное форматирование в столбце Product.

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

В этом примере мне нужны разные цвета заливки для разных диапазонов баллов.

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

Я хочу отформатировать ячейку, если ее значение больше или равно 90.

Активной ячейкой является B2, поэтому я ввожу формулу =B2>=90.

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

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

Формула =И(B2>=80,B2 <90).

И я выбираю другой цвет заливки.

Я создаю аналогичные правила для 70 и 60. Последнее правило предназначено для значений меньше 60.

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

Далее, Управление условным форматированием .

Создать условный столбец — служба поддержки Майкрософт

Знакомство с Power Query

Справка по Excel и обучение

Знакомство с Power Query

Создать условный столбец

Импорт данных из Интернета
Статья
Импорт из таблицы Excel
Статья
Настройте строку заголовка
Статья
Преобразование типа данных
Статья
Отфильтровать ненужные строки
Статья
Разделить данные на несколько столбцов
Статья
Создайте столбец из примера
Статья
Создать условный столбец
Статья
Объединение запросов и объединение таблиц
Статья
Просмотрите примененные шаги
Статья
Добавьте данные, а затем обновите запрос
Статья

Условный столбец вычисляет значение по формуле. В Power Query используется термин «настраиваемый столбец». В этом разделе давайте создадим расчет бонуса в размере 2,5% для всех продаж свыше 25 000 долларов США. Для этого напишите формулу в построителе формул Power Query. Формулы Power Query аналогичны формулам Excel.

Выберите Добавить столбец > Пользовательский столбец .
Введите «Бонус» в Новое имя столбца текстовое поле.
Чтобы ввести пользовательскую формулу, в разделе Пользовательская формула столбца справа от знака равенства введите:
Если общий объем продаж > 25 000, тогда общий объем продаж * 0,025, иначе 0,
Совет Чтобы не вводить имена таблиц, дважды щелкните нужный столбец в списке Доступные столбцы .

Как в excel создать условие: как сделать функцию – WindowsTips.Ru. Новости и советы