Условное форматирование в excel для чего нужно
Условное форматирование в Excel — подробно с примерами
Если необходимо наглядно представить информацию, то это очень удобно сделать с помощью условного форматирования. Пользователь, который пользуется этим сервисом, экономит огромное количество энергии и времени. Чтобы получить необходимые сведения, хватает быстрого просмотра файла.
Как осуществить условное форматирование в Эксель
Найти «Условное форматирование» можно на самой первой вкладке ленты, перейдя в раздел «Стили».
Далее нужно найти глазом значок стрелочки немного правее, передвинуть курсор на нее. Затем откроются настройки, где можно гибко настроить все необходимые параметры.
2
Далее требуется выбрать соответствующий оператор, чтобы сравнить нужную переменную с числом. Есть четыре оператора сравнения – больше, меньше, равно и между. Они перечислены в меню с правилами.
Далее мы указали ряд чисел в диапазоне A1:A11.
3
Осуществим форматирование подходящего набора данных. Затем открываем меню настройки «Условного форматирования» и выставляем требуемые критерии выделения данных. В случае с нами мы для примера выберем критерий «Больше».
4
Откроется окно с набором параметров. В левой части окна есть поле, в котором нужно указать число 15. В правой же части указывается способ выделения информации при условии, что она соответствует заданному ранее критерию. Результат будет отображен сразу.
5
Далее завершаем настройку с помощью кнопки ОК.
Для чего нужно условное форматирование
В определенной степени можно сказать, что оно является инструментом, после изучения которого работа с Excel никогда не будет прежней. Все потому, что он помогает значительно упростить жизнь. Вместо того, чтобы каждый раз вручную задавать форматирование ячейки, подходящей под определенное условие, так еще и самому пытаться проверить ее на предмет соответствия этому критерию, достаточно просто один раз задать настройки, а дальше Excel все сделает сам.
Например, можно сделать так, чтобы все ячейки, которые содержат число, выше 100, окрашивались красный цвет. Или же определить, сколько дней осталось до следующего платежа, после чего окрасить в зеленый цвет те ячейки, в которых срок истекает еще достаточно нескоро.
Давайте приведем такой пример.
6
В этой таблице указываются товарные запасы, которые имеются на складе. В ней есть такие колонки. как товар, средние продажи (измеряются в штуках за неделю), остаток и на сколько недель этого товара осталось.
Далее задача менеджера по закупкам – определить те позиции, пополнение которых необходимо. Чтобы это сделать, необходимо посмотреть на четвертую слева колонку, где записывается запас товаров по неделям.
Предположим, критерием для определения повода для паники служит товарный запас в размере меньшем, чем 3 недели. Это говорит о том, что нам нужно готовить заказ. Если же запас товаров составляет меньше двух недель, то это говорит о необходимости срочно размещать заказ. Если в таблице приводится огромное количество позиций, то вручную проверять, сколько недель осталось, довольно проблематично. Даже если воспользоваться поиском. А теперь давайте посмотрим, как выглядит таблица, которая подсвечивает красным цветом дефицитные товары.
7
И ведь действительно, так значительно проще сориентироваться.
Правда, данный пример учебный, который несколько упрощен по сравнению с реальной картиной. И сэкономленные секунды и минуты при регулярном использовании подобных таблиц превращаются в часы. Теперь просто достаточно посмотреть на таблицу, чтобы понять, какие товары дефицитные, а не тратить часы на то, чтобы анализировать каждую ячейку (если подобных товарных позиций тысячи).
Если есть «желтые» товары, то необходимо начать их закупку. Если же соответствующая позиция красная, то нужно это делать немедленно.
По значению другой ячейки
Теперь давайте разберем следующий практический пример.
Предположим, у нас есть такая таблица, и перед нами появилась задача выделить строки, содержащие определенные значения.
8
Итак, если проект пока что исполняется (то есть, еще не был доведен до конца, поэтому отмечен буквой «Р»), то нам необходимо его фон сделать красным. Завершенные же проекты помечаются зеленым.
Наша последовательность действий в этой ситуации будет следующей:
Далее заполняем строки так, как показано на этой картинке.
9
Важно! На строки обязательно давать абсолютную ссылку. Если же мы ссылаемся на ячейку, то в таком случае она является смешанной (с закреплением колонки).
Точно так же создается правило для тех рабочих проектов, которые не были завершены к текущему моменту.
Вот так выглядят наши критерии.
10
Ну и наконец, такая база данных у нас получается в итоге.
11
Несколько условий
Возьмем для наглядности диапазон А1:А11 из таблицы, чтобы продемонстрировать, как можно на реальном практическом примере использовать несколько условий для определения форматирования ячейки.
Сами условия такие: если число в ячейке превышает значение 6, то оно выделяется красным цветом. Если оно зеленое, то цвет тогда зеленый. Ну и наконец, наибольшие числа, больше 20, будут выделяться желтым.
Есть несколько способов, как осуществляется условное форматирование по нескольким правилам.
После этого Далее выбираем пункт «Использовать формулу…» (выделен на рисунке красной рамочкой) и задаем первое условие. После этого кликаем на ОК. А потом цикл повторяется для последующих условий аналогично описанному выше, но при этом используется формула.
14
Важно учесть такой момент. В нашем примере некоторые ячейки соответствуют сразу нескольким критериям одновременно. Данный конфликт Эксель разрешает следующим образом: то правило, которое выше, применяется в первую очередь.
И вот скриншот для лучшего понимания.
15
Например, у нас есть число 24. Оно одновременно соответствует всем трем условиям. В таком случае будет заливка, соответствующая первому условию, несмотря на то, что оно больше подпадает под третье. Поэтому если важно, чтобы числа, выше 20 были залиты именно желтым цветом, нужно третье условие ставить на первое место.
Условное форматирование даты
А теперь посмотрим, как условное форматирование работает с датами. У нас есть такой классный диапазон.
16
В этом случае нужно задавать такое правило условного форматирования, как «Дата».
17
После этого открывается диалоговое окно с целым набором условий. Ознакомиться с ними подробно вы можете на этом скриншоте (они перечислены в левой части экрана в виде списка).
18
Нам осталось только выбрать подходящее и нажать на клавишу «ОК».
19
Видим, что у нас красным цветом были выделены те даты, которые относятся к последней неделе на момент отрисовки этого диапазона.
С использованием формул
Набор стандартных правил условного форматирования довольно большой. Но ситуации бывают разные, и стандартного перечня может не хватать. В таком случае можно создать собственное правило форматирование, используя формулу.
Для этого необходимо нажать кнопку «Создать правило» в меню условного форматирования, а потом выбрать пункт, отображенный на скриншоте.
20
Строки (по значению ячейки)
Предположим, нам нужно выделить ту строку, которая содержит ячейку с определенным значением. В этом случае нам нужно выполнить ту же последовательность действий, что и выше, но применительно к строкам. То есть, можно самому выбирать, на какой диапазон должно распространяться условное форматирование.
Как создать правило
Чтобы создать правило, необходимо воспользоваться соответствующей опцией в разделе «Условное форматирование». Давайте рассмотрим конкретную последовательность действий.
Сначала необходимо найти на ленте на вкладке «Главная» найти пункт «Условное форматирование». Его можно узнать по характерным разноцветным ячейкам разного размера со стоящей рядом картинкой с перечеркнутым знаком равно.
21
Там есть кнопка «Создать правило». Также можно нажать кнопку «Управление правилами», после чего откроется диалоговое окно, через которое также можно создать правило, нажав соответствующую кнопку в левом верхнем углу.
22
Также можно ознакомиться в этом окне с теми правилами, которые уже применены.
23
Правила выделения ячеек
Хорошо, мы столько говорим о том, как создавать правила, но что это такое? Под правилом выделения ячеек подразумеваются признаки, которые берутся в учет программой для принятия решения, каким образом осуществлять форматирование ячейки, которые подходят под них. Например, условия могут быть больше определенного числа, меньше, формула и так далее. Их целый набор. С ними можно ознакомиться самостоятельно и потренироваться в их применении «в песочнице».
Форматирование всех ячеек на основании их значений
Человек может самостоятельно задавать диапазон: целая строчка, весь документ или отдельная ячейка. Ориентироваться программа может только на значение одной ячейки. Это добавляет гибкости в процесс.
Форматирование только уникальных или повторяющихся ячеек
Существует большое количество правил, непосредственно зашитых в программу. Ознакомиться с ними вы можете в меню «Условное форматирование». В частности, чтобы форматирование распространялось исключительно на повторяющиеся значения или только на уникальные, существует специальная опция – «Повторяющиеся значения».
Если выбрать этот пункт, то открывается окно с настройками, где возможен выбор и обратного варианта – выделять лишь уникальные значения.
Форматирование значений в диапазоне ниже и выше среднего
Чтобы форматировать значения ниже или выше среднего, также существует специальная опция в том же меню. Но подменю нужно выбрать другое – «Правила отбора первых и последних значений».
Форматирование только первых и последних значений
В том же подменю есть возможность выделять особым цветом, шрифтом и другими способами форматирования лишь первые и последние значения. По стандарту есть вариант выделять первые и последние десять элементов, а также 10% от общего количества ячеек, входящих в этот диапазон. Но пользователь может самостоятельно определять, сколько ячеек ему необходимо выделить.
Форматирование только ячеек с определенным содержимым
Чтобы форматировать только ячейки с определенным содержимым, необходимо выбрать правило форматирование «Равно» или «Текст содержит». Отличие между ними в том, что в первом случае строка должна совпадать с критерием полностью, а во втором – лишь частично.
Как видим, условное форматирование – это многофункциональная возможность программы Excel, дающая огромное количество преимуществ человеку, который ее освоил. Знаем, что на первый взгляд все это может показаться сложным. Но на самом деле, руки сами тянутся к ней, как только появляется выделить определенный кусок текста красным цветом (или отформатировать его любым другим способом), основываясь на его содержимом или в соответствии с другим критерием. Эта функция входит в базовый комплект знаний Excel, без нее невозможна даже любительская работа с электронными таблицами и базами данных.
Условное форматирование: инструмент Microsoft Excel для визуализации данных
Смотря на сухие цифры таблиц, трудно с первого взгляда уловить общую картину, которую они представляют. Но, в программе Microsoft Excel имеется инструмент графической визуализации, с помощью которого можно наглядно представить данные, содержащиеся в таблицах. Это позволяет более легко и быстро усвоить информацию. Данный инструмент называется условным форматированием. Давайте разберемся, как использовать условное форматирование в программе Microsoft Excel.
Простейшие варианты условного форматирования
Для того, чтобы произвести форматирование определенной области ячеек, нужно выделить эту область (чаще всего столбец), и находясь во вкладке «Главная», кликнуть по кнопке «Условное форматирование», которая расположена на ленте в блоке инструментов «Стили».
После этого, открывается меню условного форматирования. Тут представляется три основных вида форматирования:
Для того, чтобы произвести условное форматирование в виде гистограммы, выделяем столбец с данными, и кликаем по соответствующему пункту меню. Как видим, представляется на выбор несколько видов гистограмм с градиентной и сплошной заливкой. Выберете ту, которая, на ваш взгляд, больше всего соответствует стилю и содержанию таблицы.
Как видим, гистограммы появились в выделенных ячейках столбца. Чем большее числовое значение в ячейках, тем гистограмма длиннее. Кроме того, в версиях Excel 2010, 2013 и 2016 годов, имеется возможность корректного отображения отрицательных значений в гистограмме. А вот, у версии 2007 года такой возможности нет.
При использовании вместо гистограммы цветовой шкалы, также существует возможность выбрать различные варианты данного инструмента. При этом, как правило, чем большее значение расположено в ячейке, тем насыщеннее цвет шкалы.
Наиболее интересным и сложным инструментом среди данного набора функций форматирования являются значки. Существует четыре основные группы значков: направления, фигуры, индикаторы и оценки. Каждый выбранный пользователем вариант предполагает использование разных значков при оценке содержимого ячейки. Вся выделенная область сканируется Excel, и все значения ячеек разделяются на части, согласно величинам, указанным в них. К самым большим величинам применяются значки зеленого цвета, к величинам среднего диапазона – желтого, и величины, располагающиеся в самой меньшей трети – помечаются значками красного цвета.
При выборе стрелок, в качестве значков, кроме цветового оформления, используется ещё сигнализирование в виде направлений. Так, стрелка, повернутая указателем вверх, применяется к большим величинам, влево – к средним, вниз – к малым. При использовании фигур, кругом помечаются самые большие величины, треугольником – средние, ромбом – малые.
Правила выделения ячеек
По умолчанию, используется правило, при котором все ячейки выделенного фрагмента обозначаются определенным цветом или значком, согласно расположенным в них величинам. Но, используя меню, о котором мы уже говорили выше, можно применять и другие правила обозначения.
Кликаем по пункту меню «Правила выделения ячеек». Как видим, существует семь основных правил:
Рассмотрим применение этих действий на примерах. Выделим диапазон ячеек, и кликнем по пункту «Больше…».
Открывается окно, в котором нужно установить, значения больше какого числа будут выделяться. Делается это в поле «Форматировать ячейки, которые больше». По умолчанию, сюда автоматически вписывается среднее значение диапазона, но можно установить любое другое, либо же указать адрес ячейки, в которой содержится это число. Последний вариант подойдёт для динамических таблиц, данные в которых постоянно изменяются, или для ячейки, где применяется формула. Мы для примера установили значение в 20000.
В следующем поле, нужно определиться, как будут выделяться ячейки: светло-красная заливка и темно-красный цвет (по умолчанию); желтая заливка и темно-желтый текст; красный текст, и т.д. Кроме того, существует пользовательский формат.
При переходе на этот пункт, открывается окно, в котором можно редактировать выделения, практически, как угодно, применяя различные варианты шрифта, заливки, и границы.
После того, как мы определились, со значениями в окне настройки правил выделения, жмём на кнопку «OK».
Как видим, ячейки выделены, согласно установленному правилу.
По такому же принципу выделяются значения при применении правил «Меньше», «Между» и «Равно». Только в первом случае, выделяются ячейки меньше значения, установленного вами; во втором случае, устанавливается интервал чисел, ячейки с которыми будут выделяться; в третьем случае задаётся конкретное число, а выделяться будут ячейки только содержащие его.
Правило выделения «Текст содержит», главным образом, применяется к ячейкам текстового формата. В окне установки правила следует указать слово, часть слова, или последовательный набор слов, при нахождении которых, соответствующие ячейки будут выделяться, установленным вами способом.
Правило «Дата» применяется к ячейкам, которые содержат значения в формате даты. При этом, в настройках можно установить выделение ячеек по тому, когда произошло или произойдёт событие: сегодня, вчера, завтра, за последние 7 дней, и т.д.
Применив правило «Повторяющиеся значения» можно настроить выделение ячеек, согласно соответствию размещенных в них данных одному из критериев: повторяющиеся это данные или уникальные.
Правила отбора первых и последних значений
Кроме того, в меню условного форматирования имеется ещё один интересный пункт – «Правила отбора первых и последних значений». Тут можно установить выделение только самых больших или самых маленьких значений в диапазоне ячеек. При этом, можно использовать отбор, как по порядковым величинам, так и по процентным. Существуют следующие критерии отбора, которые указаны в соответствующих пунктах меню:
Но, после того, как вы кликнули по соответствующему пункту, можно немного изменить правила. Открывается окно, в котором производится выбор типа выделения, а также, при желании, можно установить другую границу отбора. Например, мы, перейдя по пункту «Первые 10 элементов», в открывшемся окне, в поле «Форматировать первые ячейки» заменили число 10 на 7. Таким образом, после нажатия на кнопку «OK», будут выделяться не 10 самых больших значений, а только 7.
Создание правил
Выше мы говорили о правилах, которые уже установлены в программе Excel, и пользователь может просто выбрать любое из них. Но, кроме того, при желании, пользователь может создавать свои правила.
Для этого, нужно нажать в любом подразделе меню условного форматирования на пункт «Другие правила…», расположенный в самом низу списка». Или же кликнуть по пункту «Создать правило…», который расположен в нижней части основного меню условного форматирования.
Открывается окно, где нужно выбрать один из шести типов правил:
Согласно выбранному типу правил, в нижней части окна нужно настроить изменение описания правил, установив величины, интервалы и другие значения, о которых мы уже говорили ниже. Только в данном случае, установка этих значений будет более гибкая. Тут же задаётся, при помощи изменения шрифта, границ и заливки, как именно будет выглядеть выделение. После того, как все настройки выполнены, нужно нажать на кнопку «OK», для сохранения проведенных изменений.
Управление правилами
В программе Excel можно применять сразу несколько правил к одному и тому же диапазону ячеек, но отображаться на экране будет только последнее введенное правило. Для того, чтобы регламентировать выполнение различных правил относительно определенного диапазона ячеек, нужно выделить этот диапазон, и в основном меню условного форматирования перейти по пункту управление правилами.
Открывается окно, где представлены все правила, которые относятся к выделенному диапазону ячеек. Правила применяются сверху вниз, так как они размещены в списке. Таким образом, если правила противоречат друг другу, то по факту на экране отображается выполнение только самого последнего из них.
Чтобы поменять правила местами, существуют кнопки в виде стрелок направленных вверх и вниз. Для того, чтобы правило отображалось на экране, нужно его выделить, и нажать на кнопку в виде стрелки направленной вниз, пока правило не займет самую последнюю строчу в списке.
Есть и другой вариант. Нужно установить галочку в колонке с наименованием «Остановить, если истина» напротив нужного нам правила. Таким образом, перебирая правила сверху вниз, программа остановится именно на правиле, около которого стоит данная пометка, и не будет опускаться ниже, а значит, именно это правило будет фактически выполнятся.
В этом же окне имеются кнопки создания и изменения выделенного правила. После нажатия на эти кнопки, запускаются окна создания и изменения правил, о которых мы уже вели речь выше.
Для того, чтобы удалить правило, нужно его выделить, и нажать на кнопку «Удалить правило».
Кроме того, можно удалить правила и через основное меню условного форматирования. Для этого, кликаем по пункту «Удалить правила». Открывается подменю, где можно выбрать один из вариантов удаления: либо удалить правила только на выделенном диапазоне ячеек, либо удалить абсолютно все правила, которые имеются на открытом листе Excel.
Как видим, условное форматирование является очень мощным инструментом для визуализации данных в таблице. С его помощью, можно настроить таблицу таким образом, что общая информация на ней будет усваиваться пользователем с первого взгляда. Кроме того, условное форматирование придаёт большую эстетическую привлекательность документу.
Помимо этой статьи, на сайте еще 12533 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.
Отблагодарите автора, поделитесь статьей в социальных сетях.