С помощью условного форматирования в электронных таблицах MS Excel можно сделать ячейки более выразительными, что достигается изменением цвета, рамки, заливки и других параметров при осуществлении определенных действий.
Понятие о рассматриваемом инструменте
Его нужно использовать там, где нет доступа к «1С», но при этом необходимо отображать актуальную информацию за минуты или даже секунды. С этой целью можно использовать инструмент «Условное форматирование» табличного процессора компании Microsoft.
Данные по различной продукции могут быть занесены в эту программу. Их может быть очень большое количество, при этом может возникать необходимость их отслеживания по различным параметрам.
Условное форматирование в Excel по тем условиям, которые заданы, осуществляется с помощью выделения ячеек или определенного диапазона цветом, набором значков или гистограммами.
Отслеживание поступающей и отгружаемой продукции может осуществляться с помощью фильтра. Но удобнее производить визуализацию. Просроченной продукции может быть присвоен красный цвет, тем товарам, у которых в скором времени наступает окончание срока годности — желтый и т. д. Эти данные вводятся один раз при заполнении таблицы. После этого при открытии данных будет видно, что краснеет, что желтеет.
Возможности использования в разных версиях
Условное форматирование в «Эксель» 2003 года и более поздних версиях отличается кардинально. Это заключается в том, что в первой программе возможны только три варианта задания данного условия — на основе значений или формул.
С версии 2007 года число условий форматирования резко увеличилось и стало больше визуальных эффектов.
Рассмотрим примеры при использовании Excel 2016. Более ранние версии, начиная с 2007, имеют незначительные отличия, с которыми пользователь может разобраться самостоятельно.
Простой пример
Создаем новую книгу. На табе «Главная» ищем раздел «Стили» и в нем видим кнопку «Условное форматирование». Стрелка, расположенная на ней, позволяет выбрать условия и задать определенные правила.
Перед этим необходимо ввести определенные данные в этой книге. После чего нужно осуществить их выбор. После этого кликаем на названную ранее кнопку и выбираем «Правила выделения ячеек». Здесь задаем условие по стрелке, направляющей в выпадающее справа меню.
Открывается диалоговое окно, слева задаем конкретный параметр, который нам необходим. Справа указываем условие отображения значений, которые будут совпадать с заданным ранее условием.
Подтверждаем сделанные изменения нажатием Enter или кликом по ОК.
Использование инструмента по значению иной ячейки
Предположим, что в некоторой базе данных нужно определить числа, превышающие некие нормативы. Это говорит о том, что в одной какой-либо колонке могут быть заданы определенные значения, а в другой — конкретный норматив.
Правила условного форматирования в этом случае задаются по тому диапазону, в котором введены определенные значения, составляющие базу данных. После его выделения кликаем на вышеназванную кнопку и через первый пункт выбираем необходимое условие (больше, между, равно и пр.). Затем указываем на ячейку, в которую внесен норматив. Эта ссылка будет автоматически вставлена в диалоговое окно с одноименным названием с тем условием, которое было задано ранее. Справа в этом окне выбираем правило отображения информации, подвергающейся форматированию. Внизу списка имеется возможность создать свой вариант, если мы не согласны с предложенными.
Сравниваем разные базы данных
Условное форматирование ячеек в Excel может осуществляться по некоторым диапазонам. Например, в колонке А введены одни значения, а в колонке В — другие. Выделяем первый из них сверху вниз. Кликаем по знакомой кнопке, выбираем те же пункты. После появления диалогового окна, имеющего одноименное с выбранным условием название, в его левой части вводим относительную ссылку (={адрес_ячейки}) из второго диапазона. Если просто щелкнуть на нужную ячейку данного диапазона, то появится ссылка со знаками американского доллара, их нужно будет убрать.
Под относительной ссылкой понимают ту из них, которая меняется в формуле при динамике адреса по отношению к новому месту. Если же и перед буквенным, и перед цифровым значением адреса ячейки стоит знак американского доллара, то говорят об абсолютной ссылке. Здесь в формуле значения этой ячейки при любом движении не будут изменяться. Также выделяют смешанные ссылки, при которых знак доллара ставится перед одной составляющей адреса ячейки. В этом случае перемещение будет осуществляться или по столбцу, или по строке при соответствующем копировании формулы.
В случае неправильного выделения одного диапазона и такого же выбора ссылки во втором, анализ не получится. Правильный алгоритм предполагает выделение ячеек в первой колонке снизу-вверх, а во второй — первой значащей ячейки.
Первый вариант формирования правил для нескольких условий
Выделяем диапазон ячеек (кстати говоря, в Excel условное форматирование строки может свободно осуществляться, а не только колонок). Проходим все пути для создания определенного правила. В диалоговом окне задаем слева необходимый параметр, а справа - способ отображения данных, который будет применен к сведениям, которые соответствуют выбранному ранее условию. Подтверждаем введенные изменения.
Повторяем проведенную процедуру для другого параметра, но уже с иным способом отображения данных. Это действие может повторяться такое количество раз, сколько необходимо. В результате получим разноцветную таблицу.
Можно использовать и другой вариант, о котором речь пойдет ниже.
Второй вариант формирования правил для нескольких условий
Выделяем необходимый диапазон и при клике на кнопку в группе «Стили» выбираем «Создать правило». В появившемся диалоговом окне в верхней его части кликаем на «Использовать формулу… форматируемых ячеек». В нижней части этого окна прописываем относительную или смешанную ссылку в ручном или автоматическом режиме путем указания на определенную ячейку. После приведенной ссылки ставим арифметический знак применяемого условия. В самой нижней части данного окна кликаем на кнопку «Формат» и выбираем условие выделения.
Такие же действия осуществляются для иных значений условного форматирования.
Рассматриваемый процесс по отношению к дате
Многие базы данных содержат колонки или строки с наименованием «Дата». Для этого выделяется указанный диапазон, кликаем на одноименную с применяемым инструментом кнопку, выбираем правило выделения ячеек и там находим вкладку «Дата».
В левой части появившегося диалогового окна выбираем условие их отбора (например, на следующей неделе), подтверждаем и получаем в результате выделение тех ячеек условным форматированием, которые по датам соответствуют следующей неделе. Аналогичным образом можно задать подобные правила и для других параметров.
Применение инструмента к строке по значению, заданному в ячейке
В этом случае выделяется строка, а условием осуществления рассматриваемого форматирования является некоторое значение в определенной ячейке. Здесь чаще всего применяются логические функции, которые начинаются со слова «ЕСЛИ», ссылка на строку приводится абсолютная, а на ячейку — смешанная.
При клике на «Формат» выбираем цвет для заданного условия. В результате получим разноцветную таблицу, в которой по окраске будут отличаться строки, а не столбцы.
Применение формул при использовании данного инструмента
Допустим, из имеющегося диапазона нам необходимо вычленить те числа, которые относятся к нечетным. Для этого кликаем по одноименной с рассматриваемым инструментом кнопке на панели инструментов, выбираем «Создать правило», а там «Использовать формулу… форматируемых ячеек». В нижней части вводим следующую формулу: =ОСТАТ($A1:2)=1 - и задаем тот формат, который нас устраивает. Подтверждаем введенные изменения, в результате в таблице те числа, которые являются нечетными, в колонке А будут выделены заданным цветом.
Создание информационного сообщения
Выделяем определенный диапазон, например, в колонке А. В ячейке В1 вводим формулу (=ЕСЛИ(А1>{определенное_число};»сверхнорма»;ЕСЛИ{A1>{следующее_определенное_число};»следует обратить внимание»)). Эта формула может включать множество значений в зависимости от того, сколько информационных сообщений необходимо.
Кликаем на Enter, в итоге в В1 появится одно из тех значений формулы, которое в ней было введено в кавычках. Протягиваем эту формулу до конца колонки в соответствии с количеством строк со значениями в выделенном диапазоне.
После этого для каждого полученного значения в столбце В создаем правило условного форматирования. В диалоговом окне выбираем «Форматировать только ячейки, которые содержат», внизу выбираем «Текст», «содержит» и то, что он должен содержать. Задаем формат. Повторяем эту операцию для каждого информационного сообщения.
В заключение
Условное форматирование позволяет пользователю выделять некоторые данные по заданным условиям для их визуального отображения по тем из них, которые важны для конкретного человека в определенный временной промежуток. В статье рассмотрено, как данный инструмент может использоваться в простых случаях, а также в более сложных — с применением формул, информационных сообщений. Данными примерами использование рассматриваемого инструмента не исчерпывается. Здесь показаны лишь наиболее распространенные случаи его применения.
