Repeater-zone.ru

ПК Репитер
6 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

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

Располагается эта полезная возможность на вкладке «Главная» в области «Стили» под одноименной пиктограммой:

Пиктограмма условного форматирования

1. Выделение цветом всей строки

Необходимо выделить цветом те строки таблицы, в которых в столбце «В» значения больше 5000.

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

2. Выберите в меню Формат — Условное форматирование

3. В открывшемся Окне задайте условие:

  • Выберите — «формула»
  • введите формулу =$B2>5000

4. Выберите необходимый формат для выделения строки

Формат

Формат

Теперь зеленым цветом будут выделены все строки, в которых в столбце «В» стоит значение большее 5000.

Формат

Главный нюанс заключается в знаке доллара ($) перед буквой столбца в адресе — он фиксирует столбец, оставляянезафиксированной ссылку на строку- проверяемые значения берутся из столбца указанного в формуле, по очереди из каждой последующей строки.

Условное форматирование в EXCEL (ч.1)

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

Функция условного форматирования находиться на вкладке Главная:

условный формат, форматирование ячеек, цветной формат в ексель

Рассмотрим самые популярные виды условного форматирования: больше/меньше, между/равно, между, дата, первые/последние значения/%

условный формат, форматирование ячеек, цветной формат в ексель

Необходимо выделить значения дохода больше 300:

  • выделяем диапазон который форматируем С2:С9
  • переходим Главная → Условное форматирование → Правила выделения ячеек → Больше.
  • вписываем необходимо нам значение 300
  • и в выпадающем списке выбираем цвет форматируемых ячеек

условный формат, форматирование ячеек, цветной формат в ексель

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

условный формат, форматирование ячеек, цветной формат в ексель

Выделим все продажи прошлого месяца:

  • выделяем диапазон который форматируем В2:В9
  • переходим Главная → Условное форматирование → Правила выделения ячеек → Дата.
  • выбираем в выпадающем списке нужный нам вид отбора (В прошлом месяце) форматирование наименьшее, форматирование ячеек, форматирование ексель, формат дат, дата в ексель
  • и в выпадающем списке выбираем цвет форматируемых ячеексписок цвета, форматирование наименьшее, форматирование ячеек, форматирование ексель

Наименьшее количество продаж:

  • выделяем диапазон который форматируем С2:С9
  • переходим Главная → Условное форматирование → Правила отбора первых и последних значений → последние 10 элементов

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

  • вписываем необходимо нам значение.

форматирование наименьшее, форматирование ячеек, форматирование ексель

  • и в выпадающем списке выбираем цвет форматируемых ячеек

Во второй части про заливку с помощью гистограммы, цветовые шкалы и набор значков — Условное форматирование в EXCEL (заливка ячеек) ч.2

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

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

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

Исходная таблица

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

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

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

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

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

Второе и третье правила создаются через Главная — Условное форматирование — Создать правило — Использовать формулу для определения форматируемых ячеек (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. Вручную

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

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

  1. Выделяем в нашей таблице все строки кроме первой.
  2. Удаляем все правила условного форматирования с выделенных ячеек через Главная — Условное форматирование — Удалить правила — Удалить правила из выделенных ячеек (Home — Conditional formatting — Clear rules — Clear rules from selected cells) .
  3. Выделяем первую строку, жмём кнопку-кисточку Формат по образцу на Главной (Home — Format Painter) и выделяем все остальные строки, копируя на них формат с первой.

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

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

  1. Жмём сочетание клавиш Alt + F11 или на вкладке Разработчик кнопку Visual Basic (Developer — Visual Basic) .
  2. В открывшемся окне редактора макросов добавляем в нашу книгу новый модуль через меню Insert — Module.
  3. Вставляем в созданный пустой модуль наш макрос:

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

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

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

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

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

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

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

Допустим в ячейке A1 находиться числовое значение 50:

Правила для A1.

Определим следующие условия для формата отображения значений в A1:

  1. Если число больше чем 15, то шрифт будет отображаться зеленым цветом.
  2. Если число больше чем 30, то шрифт будет отображаться желтым цветом.
  3. Если число больше чем 40, то шрифт будет отображаться красным.

Определенно вы заметили, что значение 50 в ячейке A1 соответствует всем условиям (A1>15, A1>30 и A1>40 = ИСТИНА). Каким же цветом шрифта Excel отобразит числовое значение 50?

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

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

Форматирование части строки в Excel

Пример условного форматирования части строки на листе Excel в зависимости от содержимого одной или двух ячеек в этой строке.

Условие примера

  1. Заливка строки желтым фоном, если в третьей ячейке (столбец «C») этой строки содержится значение «Да».
  2. Заливка строки серым фоном, если в четвертой ячейке (столбец «D») этой строки содержится значение «Нет».
  3. Заливка строки красным фоном, если в третьей ячейке (столбец «C») этой строки содержится значение «Да», а в четвертой ячейке (столбец «D») – значение «Нет».
  4. Заливка применяется к 5 первым ячейкам любой строки.

Решение примера

1. Выделяем первые 5 столбцов, чтобы задать диапазон, к которому будут применяться создаваемые правила условного форматирования:

Выделение первых пяти столбцов на листе Excel

2. Создаем первое правило: условие – =$C1=»Да» , цвет заливки – желтый:

Пример создания правил форматирования №3

3. Создаем второе правило: условие – =$D1=»Нет» , цвет заливки – серый:

Пример создания правил форматирования №4

4. Создаем третье правило: условие – =И($C1=»Да»;$D1=»Нет») , цвет заливки – красный:

Пример создания правил форматирования №5

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

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

6. Заполняем ячейки столбцов «C» и «D» словами «Да» и «Нет» и смотрим результаты условного форматирования части строки:

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

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

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

Скачать файл Excel с примерами. На первом листе реализовано условное форматирование всей строки, на втором – ее части.

Правила условного форматирования в think-cell

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

В think-cell поддерживается lдо трех типов правил условного форматирования. В правиле форматирования для Значения ячейки можно применить один из следующих вариантов:

  • в интервале / не в интервале
  • равно / не равно
  • меньше чем / больше чем
  • меньше чем или равно / больше чем или равно

Для указания диапазона значения используются следующие операторы:

  • +, -, *, /, ^, &, %
  • =, <, >, ≤ и ≥
  • Обратите внимание, что условное форматирование не применяется для правил, содержащих ссылки на другие ячейки.

ссылки: правила поддерживаются

ссылки: правила не поддерживаются

Изменение числового формата условным форматированием не поддерживается think-cell. Однако think-cell может использовать числовой формат из Excel: Сперва выберите желаемый числовой формат в Excel с помощью диалогового окна Форматировать ячейки. , затем выберите команду Использовать формат Excel в нижней части элемента управления числовым форматом think-cell. Числовой формат не может изменить цвет шрифта в метках think-cell.

голоса
Рейтинг статьи
Читать еще:  Роутер снижает скорость интернета, как исправить
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector