Условное форматирование значками
Наглядность большой таблице с огромным количеством значений отлично придаст форматирование с использованием значков. С их помощью можно классифицировать все значения в таблице на несколько групп, каждая из которых будет представлять конкретный диапазон. В зависимости от выбранного набора значков таких групп будет три, четыре или пять.
Например, используя в работе набор из трех флажков, можно получить следующую картину: красные флажки будут стоять рядом с маленькими значениями (то есть менее 33%), желтые рядом со средними значениями (находящимися в диапазоне между 33 и 67%), а зеленые – с наибольшими значениями (более 67%).
Стоит отметить, что условное форматирование в Excel 2010 содержит гораздо больше вариантов значков по сравнению с предыдущей версией программы.
Если у пользователя есть желание использовать значки из разных наборов для отображения необходимых значений, то стоит выбрать пункт «Другие правила». В более поздних версиях программы здесь можно не только вручную задать необходимые условия, но также и выбрать понравившийся тип значка для каждого значения. Это дает возможность сделать представлены данные еще более наглядными и достаточно уникальными.
Работа с большими таблицами Excel
Допустим, что на одном из листов в рабочей книге Excel создана большая таблица с данными. Возможно, это сводка по прибыли и другим показателям эффективности предприятия в разбивке по годам или кварталам за несколько лет, динамика температуры и влажности воздуха для конкретного города по дням недели в течение месяца или любая другая информация, содержащая большое количество разнообразных показателей.
Чтобы данная таблица имела какое-то значение и помогала судить о каких-либо процессах, ее обязательно надо проанализировать. Но даже для профессионала этот массив данных на первый взгляд будет выглядеть простым набором цифр, в котором необходимо искать какие-то определенные моменты.
Пример использования формулы в условном форматировании
Предположим, создана таблица, в которой в двух столбцах представлены данные о выручке, полученной за реализацию большого количества разных товаров за март и апрель. Необходимо выделить суммы, которые в марте оказались больше, чем в апреле.
Для этого следует зайти в вышеуказанное меню и в строку формулы ввести следующее: = B5>C5. Здесь B – это столбец марта, С – столбец апреля. Записанное условие можно будет протянуть на весь диапазон, в котором собраны данные. Его можно также протянуть дальше по столбцам при добавлении новых месяцев.
Функция условного форматирования делает Excel еще более удобной и мощной программой для работы с таблицами. И пользователи, знающие о плюсах этого инструмента, несомненно, работают в программе с большей легкостью и удовольствием.
Условное форматирование с помощью гистограммы
Этот вид условного форматирования наглядно отображает значение в ячейке по сравнению с другими ячейками. Это условное форматирование можно запросто заменить созданием настоящей гистограммы, ведь по сути они выполняют одинаковые функции. Однако воспользоваться форматированием гораздо удобнее и проще.
В каждой ячейке длина гистограммы будет соответствовать числу, которое в ней находится. Чем выше это значение, тем длиннее будет гистограмма, и наоборот.
С помощью этого типа форматирования очень удобно определять главные значения в особенно больших массивах данных и наглядно представлять их прямо в таблице, а не в отдельном графике.
В меню гистограмм есть несколько предустановленных форматов, которые позволяют быстро отформатировать таблицу. Также есть возможность создать новое условие с выбором цвета столбца и произвольных значений. Стоит отметить, что палитра цветов во всех правилах условного форматирования такая же обширная, как и другие цветовые наборы в Excel. Это позволяет выделить различными способами множество данных и сделать наглядным каждое значение, требующее особого внимания.
Создать правило
Немного отвлечемся от изучения вариантов применения функций условного форматирования при нескольких условиях и заглянем во внутренние настройки данного сервиса. Вы уже заметили, при выборе пункта «создать правило» в выпавшем окне (в верхней части) «выберете тип правила», мы видим целый список предлагаемых правил.
Все очень просто. Условное форматирование 3.20
В нижней части «измените описание правила» мы можем выбрать стиль формата, значение (условие) и конечно палитру (цвет). Хочу сразу сказать, что содержание нижней части окна зависит от выбранного типа правил. Полистайте и по изучайте предлагаемые варианты. На изученных нами примерах или на каких-то своих наработках попробуйте применение стилей, поэкспериментируйте со значениями и вариантами отображения информации.
Условное форматирование даты
Ну а я тем временем, опишу еще одну замечательную функцию. Зачастую наши базы с данными содержат колонку «Дата». И как раз по дате нам необходимо к примеру найти какую то информацию. Для примера берем столбец с датами. Выделяем выбранный диапазон. в главном меню открываем функцию «условное форматирование» / «правило выделения ячеек» / «дата»
Все очень просто. Условное форматирование 3.4
В левой части выпавшего окна выбираем из предлагаемого списка условие, по которому необходимо выделить даты.
Все очень просто. Условное форматирование 3.5
К примеру (на текущей недели). Жмем «ok» и получаем вот такой результат
Все очень просто. Условное форматирование 3.21
Кстати мы можем задать второе условия для данной функции и выделить его другим цветом. Например, (на предыдущей недели) и цвет зеленый и далее задаем третье правило и выбираем цвет, отличный от выбранных.
Все очень просто. Условное форматирование 3.22
Виды условного форматирования
Во всплывающем меню данного инструмента показано, какие существуют основные правила условного форматирования в Excel.
Прежде всего, это правила выделения ячеек, которые позволяют отформатировать ячейки с помощью специально заданных условий, значений и диапазонов. Например, среди выбранных данных выделятся числа больше или меньше какого-то значения, находящиеся внутри установленного диапазона, равные заданному значению или одинаковые по значению или даже содержащие определенный текст.
В данном списке есть интересное правило, позволяющее сделать условное форматирование в Excel даты каких-либо событий. Условие в этом случае выставляется от настоящего дня, например, можно задать ячейки, содержащие даты в следующем месяце или на прошлой неделе.
Следующий набор правил – правила отбора первых и последних значений. Используя их, можно выделять значения, входящие в первый или последний десяток элементов или процентов, а также данные, которые больше или меньшего среднего значения.
Также условное форматирование в Excel позволяет создать пользователю собственные правила или удалить их, а с помощью пункта меню «Управление правилами» есть возможность просмотреть все правила, созданные на выбранном листе, и совершать с ними разные манипуляции.
Для чего нужно условное форматирование ячеек в Excel?
Например, чтобы найти наибольшую прибыль предприятия или его выручку за указанный период, придется пробежаться глазами по всем ячейкам с данными несколько раз, постоянно сравнивая показатели и держа в голове много различных цифр. Когда искомое значение будет найдено, необходимо будет как-то выделить его и приступить к дальнейшему анализу сведенных в таблицу данных. Показателей, которые предстоит выделить из этого массива цифр, может оказаться очень много, и для поиска каждого придется не один раз целиком просматривать весь массив, причем очень внимательно, чтобы не упустить из вида данные за какой-нибудь период.
Именно в таких непростых ситуациях работнику придет на помощь условное форматирование ячеек в Excel. Благодаря этому инструменту можно без труда выявить числа, подходящие под заданные условия, и сделать определенный формат для ячеек, в которых расположены данные, удовлетворяющие этим условиям. Совершив всего несколько манипуляций, человек, работающий с таблицей, может получить наглядный свод данных, в которых без труда будут видны необходимые цифры.
Условное форматирование цветовой шкалой
Благодаря этому типу условного форматирования есть возможность задать шкалу, состоящую из двух или трех цветов, которая будет выступать в роли фона ячейки. Яркость заливки будет зависеть от значения в конкретной ячейке относительно других в указанном диапазоне.
Например, если выбрать шкалу «зеленый-желтый-красный», то наибольшие значения будут окрашены зеленым цветом, а наименьшие – красным, а средние величины – желтым.
Выбрав пункт меню «Другие правила», пользователь может настроить стиль шкалы самостоятельно. Здесь есть возможность выбрать, из каких именно цветов будет состоять шкала и подобрать их количество.
Условное форматирование по значению другой ячейки
На примерах выше мы задавали формат ячейкам, на основе их собственных значений. В Excel возможно задавать формат, на основе значений из других ячеек. Например, в таблице с данными курса доллара мы можем выделить цветом ячейки по правилу. Если курс доллара ниже чем в предыдущем месяце, то значение курса в текущем месяце будет выделено цветом.
Для создания условия по значению другой ячейки выполним следующие шаги:
- Выделим первую ячейку для назначения правила. Кликнем на пункт “Условное форматирование” на панели инструментов. Выберем условие “Меньше”.
- Во всплывающем окне указываем ссылку на ячейку, с которой будет сравниваться данная ячейка. Выбираем формат. Нажимаем кнопку “ОК”.
Условное форматирование строки по значению ячейки
Условием для данной функции УФ является выделение строки по определенному значению в ячейке. Для примера возьмем какую ни будь таблицу с данными (в нашем случае я буду использовать сведения по ремонтам оборудования)
Все очень просто. Условное форматирование 3.23
Определимся с условиями выделения информации, к примеру, нам необходимо что бы, сведения где еще ремонт не закончился (Р), отображались красным цветом. А там, где работы завершены (З) – зеленым И так зададим условие для завершенных ремонтов. Выделяем диапазон со всеми данными ремонтной таблицы. Заходим в меню «условного форматирования», выбираем «создать правило»
Все очень просто. Условное форматирование 3.26
В выпавшем меню выбираем тип правила (кликаем 1 раз ЛКМ по названию правила) – «использовать формулу для определения форматируемых ячеек» и в строке для формул забиваем логическую функцию, как показано на примере.
Все очень просто. Условное форматирование 3.24
Далее задаем формат отображения и жмем «ok» И обратите внимание, что ссылка на строку у нас в формуле – абсолютная, а ссылка на ячейку – смешанная. Затем по тому-же принципу задаем правило для не завершенных ремонтов и цвет выбираем «красный»
И в «диспетчере правил условного форматирования» это выглядит так:
Все очень просто. Условное форматирование 3.27
А наша таблица будет иметь вот такой вид:
Все очень просто. Условное форматирование 3.25
