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

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

Шаги

1. 1 Введите ваши данные или скачайте практический пример здесь. Это будет очень полезно, потому что условное форматирование легче понять, проверив на уже имеющихся у вас данных. Хотя вы можете применить условное форматирование к пустым ячейкам, проще всего увидеть, работает ли оно, с использованием уже существующих данных.
2. 2 Кликните на ячейку, которую хотите отформатировать. Условное форматирование позволяет изменить стиль шрифта, добавить подчеркивание или изменить цвет. Используя условное форматирование, можно также применить зачеркивание к ячейке, а также поменять ее границы и заливку. Однако вы не сможете изменить шрифт или размер шрифта содержимого ячейки.
3. 3 Нажмите "Формат" > "Условное форматирование", чтобы начать процесс условного форматирования. В Excel 2007 это находится в "Домашняя страница" > "Стили" > "Условное форматирование".
4. 4 Нажмите "Добавить >>" чтобы использовать два условия. В этом примере используется два условия, чтобы видеть, как одно исключает другое. Excel позволяет добавлять до трех условий на ячейку. Если вам нужно только одно условие, пропустите следующий шаг.
5. 5 Нажмите “Добавить >>" еще раз для добавления другого условия или нажмите “Удалить..." и выберите, какое из условий удалить.
6. 6 Определите, основано ли ваше условие на значении в текущей ячейке или какой-то другой ячейки, или даже группы ячеек в другой части листа.
7. 7 Оставьте условие, как есть (другими словами, оставьте первое значение из выпадающего списка “Значение ячейки"), если условие основано на текущей ячейке. Если оно зависит от других ячеек, в выпадающем списке выберите “Формула". Для разъяснений по пункту “Формула" перейдите к следующему шагу. В случае варианта “Значение ячейки" сделайте следующее:
   • Используя второй выпадающий список, выберите аргумент, который лучше всего подходит. Для проверки условий нахождения между нижней и верхней границей выберите “Между" или “Не между". Для условий, использующих одно значение, используйте другие аргументы. В этом примере будет использоваться одно значение с аргументом “Больше чем".
      
   • Определите, какое значение (значения) нужно применить к аргументу. В этом примере мы используем аргумент “Больше чем" и ячейку B5 в качестве значения. Чтобы выбрать ячейку, нажмите кнопку на текстовом поле. Окно условного форматирования будет свернуто.
      
8. 8 В случае использования “Формулы" вы можете применить условное форматирование, основываясь на значении другой ячейки или ячеек. После выбора пункта “Формула" все выпадающие списки пропадут, и у вас будет только текстовое поле. Это значит, вы можете ввести любую формулу, используя стандартные формулы Excel. В большинстве случаев вам лучше придерживаться простых формул и избегать текста или текстовых строк. Учтите, что формулы привязаны к текущей ячейке. Например: C5 (текущая ячейка) = B5>=B6. Это означает, что C5 изменит форматирование, когда B5 станет больше или равно B6. Этот пример можно было использовать для условия “Значение ячейки", но он хорошо передает смысл. Чтобы выбрать ячейку на листе, нажмите кнопку в текстовом поле. Окно условного форматирования будет свернуто.
   • Например: Представьте, что у вас есть таблица со всеми днями текущего месяца, перечисленными в колонке А; вам каждый день нужно вводить данные на этом листе; и вы хотите, чтобы вся строка, связанная с сегодняшней датой, была каким-то образом выделена. Попробуйте так: (1) Выделите всю таблицу с данными, (2) Выберите условное форматирование, как это описывалось выше, (3) Выберите "Формула" и (4) Введите что-то вроде =$A3=TODAY(), где колонка A содержит даты, а строка 3 - это первая строка с данными (после заголовков). Заметьте, что вам нужен значок $ перед A, но не перед 3. (5) Выберите формат. [1]
9. 9 Нажмите на ячейку, содержащую значение. Вы заметите, что значки ($) будут автоматически добавлены перед обозначением строки и столбца. Это делает ссылку на ячейку абсолютной. Это означает, что если вы примените то же условное форматирование к другим ячейкам с помощью операции "копировать/вставить", они все будут ссылаться на первоначальную ячейку. Чтобы исключить такое поведение, просто щелкните на текстовое поле и удалите знаки ($). Если вы не хотите устанавливать условие, зависящее от какой-либо ячейки на вашем листе, просто введите значение в текстовое поле. Вы даже можете ввести текст, зависящий от аргументов. Например, не используйте аргумент “больше чем" и “John Smith" в текстовом поле. Вы не можете быть больше, чем John Smith...хотя, возможно и могли бы, но - ладно, не берите в голову. В этом примере полное условие, если бы вы произнесли его, звучит так: “Когда значение этой ячейки больше, чем значение в ячейке B5, то..."
10. 10 Примените тип форматирования. Имейте в виду, что вы хотите сделать ячейку отличающейся от остальных ячеек на листе, особенно если у вас много данных. Но вы хотите, чтобы это выглядело профессионально. В нашем примере мы хотим, чтобы шрифт стал толстым и белым, а заливка стала красной. Чтобы начать, нажмите “Формат..."
11. 11 Выберите, какие изменения в шрифте вы хотите сделать. Затем нажмите “Граница" и сделайте там какие-нибудь изменения. В нашем примере мы не изменяем границы. Затем нажмите “Шаблоны" и сделайте там изменения. В любой момент, когда вы закончили делать изменения форматирования, нажмите "OK."
12. 12 Под аргументами и значениями появится предварительный просмотр формата. Вносите необходимые изменения, пока формат не станет таким, как вам хотелось бы.
13. 13 Перейдите ко второму (и третьему, если вы его добавили) условию и следуйте шагам, описанным выше (начиная с шага 6). В примере вы заметите, что второе условие тоже включает небольшую формулу (=B5*.90). Она берет значение B5, умножает его на 0.9 (т.е. берет 90 процентов от него) и применяет форматирование, если значение меньше полученного.
14. 14 Нажмите "OK." Теперь, когда вы закончили с вашими условиями, случится одно из двух:
    1. ничего не изменится. Это означает, что условия не выполняются, и форматирование не применяется.
    2. Один из выбранных вами форматов появится, потому что выполнилось одно из условий.

Советы

• Вы можете попытаться сделать условное форматирование для данных, которые не имеют особого значения или не потеряются, если вы допустите ошибку.
• Одно из применений этой функции - это идентификация товарно-материальных ценностей, стоимость которых упала ниже желаемого уровня. Например: Сделайте шрифт жирным в строке или ячейке, когда стоимость ниже указанного значения.
• Эти шаги подходят для Excel 97 и более новых версий.
• Одной из особенностей Excel является возможность применять "копировать - специальная вставка - значения" для условного форматирования таким образом, что форматирование копируется, но пропадают уравнения условного форматирования. Это экономит память, занимаемую уравнениями. Ниже приведен (VBA) макрос, который делает это, копируя данные из Excel в Word (который использует формат HTML), а затем копирует обратно в Excel; Но это только для продвинутых пользователей, у которых есть опыт использования VBA макросов:

Вот быстрый и грязный макрос VBA, который может быть применен к небольшим наборам данных (jp_johnny)

• Условное форматирование можно применить для затенения каждой второй строки. Вы можете найти информацию об этом на сайте Microsoft http://support.microsoft.com/kb/268568/en-us?spid=2513&sid=280
• Вы можете применить одно и то же форматирование для всей строки или столбца. Нажмите на кнопку “Формат по образцу" (выглядит как желтая кисть) и затем выберите все ячейки, к которым хотите применить условное форматирование. Это будет работать только, если в условиях нет значков $. Учтите, что вам лучше будет перепроверить все ссылки на ячейки.
• Вы также можете применить форматирование к другим ячейкам, просто выделив ячейку, у которой уже есть форматирование, и скопировав ее. Затем выделите ячейки, к которым нужно применить форматирование, сделайте "Специальную вставку" и выберите "Форматы".

Предупреждения

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