Мгновенно отмечаем данные через условное форматирование в excel

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

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

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

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

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

1. Выделяем диапазон ячеек, где нужно применить правило.
2. Далее находим в верхней части экрана пункт «Условное форматирование» и наводим курсором на подраздел «Правила выделения ячеек». После этого появится большой список правил. В нашем случае, требуется нажать на «Больше…», благодаря чему будут выделены только те ячейки, значение которых будет выше того, что укажет пользователь. Остальные правила работают потому же принципу, только выполняют другие действия.
3. Дальше появится окно, в котором юзер может указать значение, больше которого должны быть данные для выделения. В нашем случае ставим 499 (делается для того, чтобы товары с суммой равной 500 тоже были выделены).
4. Кроме того, во второй строчке с правой части, пользователь может выбрать цвет, которым будут окрашены все подходящие ячейки. В нашем случае мы ищем товары, количество продаж которых составляет больше 500, поэтому выбираем зеленый цвет.
5. Иногда бывает такое, что программа не предлагает подходящий формат выделения. Чтобы это исправить, кликаем в открывшемся списке «Пользовательский формат». Откроется окошко «Формат ячеек», где у пользователя есть возможность настроить все по своим вкусовым предпочтениям (выбрать шрифт текста, границы и заливку).
6. Определившись со значением и заливкой, нажимаем на «ОК». Смотрим на получившийся результат.

Как видно, функция отлично справилась со своей задачей!

ВАЖНО! Интересно, что условное форматирование подстраивается под любые изменения в таблице. Допустим, если мы в нашей таблице изменим данные, например колонки, будут проданы не 426 раз, а 526, то таблица автоматически выделит ячейку зеленым цветом.

Затронем еще один интересные момент. В самом начале, когда мы с вами выбирали тип правила, можно было кликнуть на вариант «Другие правила». Если так сделать, то откроется окно под названием «Создание правила форматирования».

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

Кроме того, пользователь на один диапазон может накладывать сразу 2 и больше правил, если они не мешают друг другу. Допустим, в нашей таблице мы выделили зеленым все значения выше 500. Тем нам хочется обозначит красным те, которые ниже 500. Повторяем описанные выше шаги и получаем следующий результат:

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

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

Теперь поговорим о следующем пункте в условном форматировании – это правило отбора первых и последних значений. Как работает это правило выделения ячеек? Отметим, что абсолютно у каждого числа в диапазоне есть собственная последовательность по возрастанию. Именно благодаря этому программа легко может отобрать первые 10 или последние 5 значений со всего списка. Основываясь на этом, пользователь легко может подсветить нужные значения, не тратя время на поиск.

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

• Снова выделяем нужный диапазон.
• Переходим в раздел «Условное форматирование», но в этот раз наводим на вариант «Правила отбора первых и последних значений». Появится список с правилами.

ВАЖНО! Стоит уточнить, что варианты «первые 10 элементов» или «последние 10 элементов», будут выделять не по порядку в таблице. А по самому результату, который записан в значении. Например, если выбрать «первые 3 элемента», то программа выделит 3 самых больших значения.

• Вернемся к инструкции. Выбрав нужное правило, появится окно с названием варианта. Где пользователь может указать нужное количество элементов ( в нашем случае это 3), а также цвет выделения (в примере выбираем 3 цвет, так как мы ищем самые часто покупаемые товары).
• Нажимаем на «ОК» и смотрим на итоговый результат.
• Как видно красным выделилось только 3 самых больших значения со всего диапазона. Количество ячеек для форматирования вы можете регулировать самостоятельно, исходя из потребностей.

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

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

Мы воспользовались перечисленными выше правилами и получили вот такой результат:

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

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

Гистограммы

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

Рассмотрим, как установить гистограмму. Для этого вернемся к нашей старой таблице с товарами и продажами. Нам хочется наглядно увидеть различие между продажами. Чтобы это сделать, следуем следующей инструкции:

1. Выделяем диапазон, где нужно выполнить форматирование ячеек.
2. Теперь кликаем на уже знакомый нам раздел «Условное форматирование» и находим пункт «Гистограммы». Наводим курс на него и появится меню с разными вариантами заливки. В верхней части будут расположены варианты с градиентов (6 цветов), внизу же будет сплошная заливка (также 6 цветов). Выбираем понравившийся вариант. Или же можно создать собственный вариант, нажав на «Другие правила…».
3. На этом все! Excel автоматически проведет расчет и выстроит гистограмму на весь диапазон. Ячейка с самым высоким значением будет заполнена полностью. Остальные будут заполнены частично, наглядно отображая разницу с остальными  значениями.

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

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

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

1. Как и в прошлые разы, выделяем нужный нам диапазон ячеек.
2. Возвращаемся к разделу «Условное форматирование» в Excel, наводим на «Цветовые шкалы» и выбираем любой понравившийся вариант. В нашем случае это будет цветовая палитра с красным, желтым и зеленым цветом.
3. После выбора цвета, сразу будет виден результат. В нашем случае был выбран вариант «Красный-желтый-зеленый». Где красным оформлены максимальные значения, а зеленым наоборот – минимальные. Интересно, что оттенок цвета также показывает разницу чисел.

Стоит отметить, что если вам не подходит ни один из предложенных вариантов, то тогда можно воспользоваться пунктом «Другие правила». Откроется окно «Создание правила форматирования». Здесь пользователь может самостоятельно подобрать желаемые цвета и настроить все по своему усмотрению.

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

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

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

Чтобы воспользоваться этим правилом следуем следующим шагам:

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

ВАЖНО! Если открыть окно настроек этих правил, то получиться выбрать вид значков и условия, при соблюдении которых они должны будут появиться. Благодаря этому у пользователя получиться указать моменты, в которых не нужно подсвечивать ячейки в диапазоне.

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

Вот и мы разобрали, как можно сделать условное форматирование в Excel самыми разными способами. Каждый из правил отлично подходит для отдельных случаев. Снова хочется подчеркнуть, что форматирование динамично! Это значит, что сделал любые изменения, таблица тут же подстроиться под них. Произойдет корректировка каждой ячейки и все данные будут соответствовать желаемую результату!

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

1. Если снова открыть раздел «Условное форматирование», внизу можно заметить пункт «Создать правило». Кликаем сюда. Перед этим не забываем выделить желаемый диапазон.
2. Появится рамка «Создание правила форматирования», где нам нужно определиться с типом правила, который больше всего подойдет в нашем случае. Например, в нашем примере нам хочется отформатировать ячейки в зависимости от их значений. Выбираем подходящий пункт и идем дальше.
3. Осталось самое простое – определиться со стилем форматирования, цветом окраса и типом выделения. Здесь уже подстраивайте все под свой вкус или требования. Мы решили воспользоваться двухцветной шкалой, где минимальные значения выделятся зеленым, а максимальные – красным. Убеждаемся, что все правильно настроено и нажимаем на «ОК».
4. После применения правила, таблица измениться. Вот так быстро и просто можно сделать условное форматирование в экселе. Кроме того, вы всегда можете перейти в настройки и изменить параметры.

Приоритет и порядок выполнения правил форматирования

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

Посмотреть его можно, открыв Диспетчер правил. Чем выше расположено правило, тем выше его приоритет. Также работает и в обратном порядке, чем ниже находится правило, тем менее важное оно по сравнению с другими. Еще стоит уточнить, что когда пользователь создает новое правило, то она всегда будет вначале списка, то есть выше и приоритетнее всех остальных.

Где открыть этот диспетчер?

1. Выделяем диапазон ячеек, где установлены правила. Заходим в раздел «Условное форматирование» и открываем «Управление правилами».
2. Откроется окошко «Диспетчер правил условного форматирования», где мы и можем наблюдать все текущие правила и их приоритет, касательно остальных.
3. Как видно, пользователь может в этом окошке создавать, изменять, удалять и дублировать правила. Кроме того, если выделить определенное правило и воспользоваться стрелочками в верхней правой части, то можно изменять его позиции в списке (опускать ниже или поднимать наверх).

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

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

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

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

Как это можно сделать? Все довольно просто! Следуем данной инструкции и все получиться:

1. Снова открываем пункт «Управления правилами…», предварительно выделив наш диапазон.
2. Теперь нужно найти правило, которое мы хотим изменить. Кликаем на него и находим вариант «Изменить правило».
3. Откроется уже знакомое нам окно «Изменения правила форматирования», где у нас есть возможность настроить все под свои предпочтения. Соглашаемся с изменениями и нажимаем на «Применить», после на «ОК».

Вот и все! Все изменения были успешно приняты. А итоговый результат условного форматирования соответствует нашим ожиданиям!

А если забыл где какие правила форматирования создавал

Теперь давайте обсудим другую проблему. Что если по какой-то причине пользователь забыл какие правила он создавал до этого? Допустим таблица была создана год тому назад и он попросту не помнит, что именно и где было создано… Не стоит отчаиваться или переделывать всю таблицу с самого начала. Чтобы узнать, какое условное форматирование достаточно следовать следующей инструкции:

1. Кликаем на ячейку A1 и нажимаем сочетание клавиш «Ctrl + A». Это действие выделит полностью всю нашу таблицу.
2. Теперь заходим в раздел «Условное форматирование» и кликаем на «Управление правилами…»
3. Откроется окно в котором пользователь сможет увидеть все текущие правила, примененные к таблице. Кроме того, если обратить внимание на столбец «Применяется к:», то получиться увидеть какое правило к какому диапазону применяется. Например, «Набор значков» используется на диапазоне ячеек С2:С8.

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

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

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

И вот у нас появился еще один магазин – продуктовый. Теперь мы нуждаемся в еще одной таблице. Делать все с нуля не вариант, а стандартный вид выглядит не очень красиво.

Как же скопировать форматирование с прошлой таблицы в новую? Сейчас мы это и разберем! Сразу стоит отметить, что просто скопировать и вставить не получится. Ведь вместе со всеми правилами, также будет скопирован и сам текст. А это значит, что все придется переписывать по новой… Вот вариант, как это можно сделать.

1. Выделяем прошлую таблицу и копируем ее.
2. Переходим на новую таблицу. Выделяем ее и нажимаем правой кнопкой мышки на выделенной области. Находим раздел «Параметры вставки» и выбираем пункт «Форматирование». Кликаем на него.
3. В данном случае Excel вставит только правила форматирования в нашу таблицу, без текста и прочих ненужных деталей.
4. Но тут же мы сразу видим и небольшую проблему. Правило «Наборы значков» работает не совсем корректно. Это уже легко исправить, зайдя в параметры и изменив нужные настройки. Как это делать, разбиралось немного выше.

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

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

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

Вариант 1

1. Выделяем всю таблицу или диапазон, где требуется снять условное форматирование.
2. Далее кликаем на изображение ластика и выбираем «Очистить форматы». Важно выбирать именно этот пункт, так как другие просто удалят содержимое ячеек. Если же пользователь все-таки стер нужные данные, то можно сделать шаг назад, нажав на «Ctrl + Z» или воспользовавшись стрелкой назад на верхнем левом углу программы.
3. Готово! Excel убрал все форматирование, которое было установлено на выделенных ячейках.

Вариант 2

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

1. Выделяем всю таблицу.
2. Переходим в раздел «Условное форматирование», далее нажимаем на «Управление правилами».
3. Теперь находим нужное правило. Выделяем его и нажимаем на «Удалить правило». После этого оно будет стерто.

Вариант 3

Еще есть и третий вариант, как избавить от условного форматирования, которое когда-то было применено к определенному диапазону или всей странице.

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

После этого мы получим желаемый результат.

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

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

• Проблема: На некоторых ячейках содержатся правила, которые ссылаются на другие листы таблицы. И по каким-то причинам ничего не работает…
• Решение: Скорее всего этот пользователь использует старую версию Excel из-за чего и возникают такие проблемы. Решение простое. Достаточно провести проверку совмести на каждом листе. После обновляем условное форматирование.
• Проблема: На ячейку наложено сразу несколько диапазонов, где используется условное форматирование. Все отображается совершенно не так, как это было задумано. Как исправить?
• Решение: Решить проблему намного легче, чем кажется. Открываем раздел «Условное форматирование» и «Управление правилами…». В открывшемся окне видим, что правило «Значение ячейки > 100» было наложено поверх «Значения ячейки < 499». Как мы рассматривали выше приоритет правил, одно будет приоритетнее другого, если оно находиться выше. А значит, нам нужно выделить это правило и опустит его ниже.

После этого нажимаем на «Применить» и «ОК». Проверяем результат. Видим, что все теперь выглядит именно так, как это и должно быть.

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

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

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

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

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

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

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

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