Кто-то из запаса армии США спросил:
У меня есть простая таблица со сроками выполнения. Есть ли способ сделать так, чтобы сроки выполнения стали зелеными, когда до них осталось 10 дней, и красными, когда до них осталось 5 дней?
Это простая задача с использованием условного форматирования. Эта функция была введена в Excel 95. Она позволяет указать до трех условий и автоматически изменять форматирование ячеек на основе этих условий. Вы можете изменить шрифт, сделать шрифт жирным, изменить цвет, цвет фона. Если у вас есть ячейки, которые вы хотите отслеживать, чтобы увидеть, оставляют ли они определенный допуск, условное форматирование - отличный инструмент.
Лучший способ - установить условный формат для первой ячейки в столбце дат, а затем скопировать этот формат во все остальные ячейки диапазона.
Допустим, ваш диапазон дат находится в E2: 200. Вы должны выбрать ячейку E2 и выполнить следующие действия: (прокрутите немного вниз, чтобы увидеть изображение при чтении…)
- В меню выберите Формат - Условное форматирование…
- Слева от диалогового окна «Условное форматирование» есть раскрывающийся список с надписью «Значение ячейки». Щелкните этот DropDown и измените значение на «Formula Is». Это изменит правую часть диалогового окна на одно длинное поле.
- В этом поле введите эту формулу:
=(E2-TODAY())<6(Обратите внимание, если ваша первая дата не указана в E2, измените E2 на ячейку, в которой вы находитесь) - Нажмите кнопку «Формат» в нижней части диалогового окна условного форматирования.
- В середине вкладки «Шрифт» находится раскрывающийся список цветов. Выберите красный. Или - выберите желтый и на вкладке узоров выберите красный. Это сделает их действительно выделяющимися. Щелкните OK, чтобы вернуться в диалоговое окно «Условное форматирование».
При выполнении описанных выше действий даты выполнения в течение 5 дней после сегодняшнего дня станут красными. Пока вы все еще находитесь в диалоговом окне условного форматирования, выполните следующие действия:
- Нажмите кнопку «Добавить >>» в нижней части диалогового окна, и появится новое условие 2.
- Измените «Значение ячейки» на «Формула»
- Введите формулу
=(E2-TODAY())<11 - Нажмите кнопку «Формат» и выберите синий шрифт и зеленый узор. Нажмите ОК, чтобы закрыть диалоговое окно Формат ячеек.
- Ваш диалог условного форматирования должен выглядеть так. Щелкните ОК.
Мы почти на месте. После того, как вы выполните все эти шаги, чтобы настроить условный формат для первой ячейки, вы легко сможете использовать функцию «Специальная вставка», чтобы скопировать форматы для всего диапазона дат. Когда вы вставляете Special - Formats, условное форматирование также будет скопировано.
- Выберите ячейку, в которую вы добавили условное форматирование. В этом примере это E2.
- В меню выберите Правка - Копировать.
- Выделите все остальные даты в ваших данных - E3: E200. ЕСЛИ вы собираетесь добавить больше данных, не стесняйтесь выделить несколько дополнительных строк.
- В меню выберите Правка - Специальная вставка…
- В диалоговом окне «Специальная вставка» выберите четвертую запись в левом столбце - «Форматы».
- Щелкните ОК.
Это скопирует условное форматирование во все выбранные вами ячейки. Обратите внимание, что он также скопирует границы и числовые форматы, поэтому вам, возможно, придется перенастроить границы после выполнения этой операции.
Заметки:
- Существует ограничение в 3 условных формата, которые могут быть применены к любой ячейке. Включая форматирование по умолчанию, которое применяется к ячейкам, которые не соответствуют ни одному из условий, это означает, что вы можете автоматически применить до 4 форматов к ячейке.
- Если вам нужно расширить это до более чем 4 форматов, вам придется написать макрос на VBA. Макрос будет работать относительно медленно, так как ему придется обновить все ячейки после того, как вы внесете какие-либо изменения в электронную таблицу. Для этого вы также можете нанять консультанта по Excel.
- Самая сложная часть настройки условного форматирования - это ввод формулы в поле формулы. Не забывайте всегда начинать формулу со знака равенства. В книге может быть целая глава, посвященная различным формулам, которые можно ввести в это поле. Можно вводить формулы, полностью полагающиеся на другие ячейки.
- Если вы выйдите из ввода формулы и вернетесь позже, чтобы изменить формулу, Excel имеет неприятную привычку интерпретировать символ возврата как попытку указать на ячейки. Это всегда изменит вашу формулу на неправильную. Нажмите Escape, чтобы вернуться к исходной формуле. Полезно выделить ошибочный текст с помощью мыши, а затем ввести еще.
