Lumpics lumpics.ru

Условное форматирование: инструмент Microsoft Excel для визуализации данных

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

Простейшие варианты условного форматирования

Для того, чтобы произвести форматирование определенной области ячеек, нужно выделить эту область (чаще всего столбец), и находясь во вкладке «Главная», кликнуть по кнопке «Условное форматирование», которая расположена на ленте в блоке инструментов «Стили».

После этого, открывается меню условного форматирования. Тут представляется три основных вида форматирования:

  • Гистограммы;
  • Цифровые шкалы;
  • Значки.
Типы условного форматирования в Microsoft Excel

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

Выбор гистограммы в Microsoft Excel

Как видим, гистограммы появились в выделенных ячейках столбца. Чем большее числовое значение в ячейках, тем гистограмма длиннее. Кроме того, в версиях Excel 2010, 2013 и 2016 годов, имеется возможность корректного отображения отрицательных значений в гистограмме. А вот, у версии 2007 года такой возможности нет.

Гистограмма применена в Microsoft Excel

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

Использование цветовой шкалы в Microsoft Excel

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

Значки при условном форматировании в Microsoft Excel

При выборе стрелок, в качестве значков, кроме цветового оформления, используется ещё сигнализирование в виде направлений. Так, стрелка, повернутая указателем вверх, применяется к большим величинам, влево – к средним, вниз – к малым. При использовании фигур, кругом помечаются самые большие величины, треугольником – средние, ромбом – малые.

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

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

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

Кликаем по пункту меню «Правила выделения ячеек». Как видим, существует семь основных правил:

  • Больше;
  • Меньше;
  • Равно;
  • Между;
  • Дата;
  • Повторяющиеся значения.
Правила выделения ячеек в Microsoft Excel

Рассмотрим применение этих действий на примерах.

  1. Выделим диапазон ячеек, и кликнем по пункту «Больше…».
  2. Переход к правилу выделения ячеек в Microsoft Excel
  3. Открывается окно, в котором нужно установить, значения больше какого числа будут выделяться. Делается это в поле «Форматировать ячейки, которые больше». По умолчанию, сюда автоматически вписывается среднее значение диапазона, но можно установить любое другое, либо же указать адрес ячейки, в которой содержится это число. Последний вариант подойдёт для динамических таблиц, данные в которых постоянно изменяются, или для ячейки, где применяется формула. Мы для примера установили значение в 20000.
  4. Установка границы для выделения ячеек в Microsoft Excel
  5. В следующем поле, нужно определиться, как будут выделяться ячейки: светло-красная заливка и темно-красный цвет (по умолчанию); желтая заливка и темно-желтый текст; красный текст, и т.д. Кроме того, существует пользовательский формат.
  6. Выбор цвета выделения в Microsoft Excel
  7. При переходе на этот пункт, открывается окно, в котором можно редактировать выделения, практически, как угодно, применяя различные варианты шрифта, заливки, и границы.
  8. Пользоательский формат в Microsoft Excel
  9. После того, как мы определились, со значениями в окне настройки правил выделения, жмём на кнопку «OK».
  10. Сохранение результатов в Microsoft Excel

Как видим, ячейки выделены, согласно установленному правилу.

Ячейки выделены согласно правилу в Microsoft Excel

По такому же принципу выделяются значения при применении правил «Меньше», «Между» и «Равно». Только в первом случае, выделяются ячейки меньше значения, установленного вами; во втором случае, устанавливается интервал чисел, ячейки с которыми будут выделяться; в третьем случае задаётся конкретное число, а выделяться будут ячейки только содержащие его.

Другие варианты выделения в Microsoft Excel

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

Выделение текст содержит в Microsoft Excel

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

Выделение ячеек по дате в Microsoft Excel

Применив правило «Повторяющиеся значения» можно настроить выделение ячеек, согласно соответствию размещенных в них данных одному из критериев: повторяющиеся это данные или уникальные.

Выделение повторяющихся значений в Microsoft Excel

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

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

  • Первые 10 элементов;
  • Первые 10%;
  • Последние 10 элементов;
  • Последние 10%;
  • Выше среднего;
  • Ниже среднего.
Правила отбора первых и последних ячеек в Microsoft Excel

Но, после того, как вы кликнули по соответствующему пункту, можно немного изменить правила. Открывается окно, в котором производится выбор типа выделения, а также, при желании, можно установить другую границу отбора. Например, мы, перейдя по пункту «Первые 10 элементов», в открывшемся окне, в поле «Форматировать первые ячейки» заменили число 10 на 7. Таким образом, после нажатия на кнопку «OK», будут выделяться не 10 самых больших значений, а только 7.

Установка правила отбора первых и последних ячеек в Microsoft Excel

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

Выше мы говорили о правилах, которые уже установлены в программе Excel, и пользователь может просто выбрать любое из них. Но, кроме того, при желании, пользователь может создавать свои правила.

Для этого, нужно нажать в любом подразделе меню условного форматирования на пункт «Другие правила…», расположенный в самом низу списка». Или же кликнуть по пункту «Создать правило…», который расположен в нижней части основного меню условного форматирования.

Переход к созданию правила в Microsoft Excel

Открывается окно, где нужно выбрать один из шести типов правил:

  1. Форматировать все ячейки на основании их значений;
  2. Форматировать только ячейки, которые содержат;
  3. Форматировать только первые и последние значения;
  4. Форматировать только значения, которые находятся выше или ниже среднего;
  5. Форматировать только уникальные или повторяющиеся значения;
  6. Использовать формулу для определения форматируемых ячеек.
Типы правил в Microsoft Excel

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

Опивание правила в Microsoft Excel

Управление правилами

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

Переход к управлению правидлами в Microsoft Excel

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

Окно управления праилами в Microsoft Excel

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

Изменение порядка правил в Microsoft Excel

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

Оставить если истина в Microsoft Excel

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

Создание и изменение правила в Microsoft Excel

Для того, чтобы удалить правило, нужно его выделить, и нажать на кнопку «Удалить правило».

Удаление правила в Microsoft Excel

Кроме того, можно удалить правила и через основное меню условного форматирования. Для этого, кликаем по пункту «Удалить правила». Открывается подменю, где можно выбрать один из вариантов удаления: либо удалить правила только на выделенном диапазоне ячеек, либо удалить абсолютно все правила, которые имеются на открытом листе Excel.

Удаление правил вторым способом в Microsoft Excel

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

Обсудить в TelegramНаш Telegram каналТолько полезная информация
Автор статьи Вы на сайте: Статья обновлена: . Автор: Максим Тютюшев

Вам помогли мои советы?

Получить ответ на Email
Уведомить о

22 ответов
По рейтингу
Новые Старые
Межтекстовые Отзывы
Посмотреть все комментарии
Константин
14 декабря 2017 08:34

Как создать условное форматирование по дате? не ячеек с датой, а именно по дате, т.е. наступает заданная дата цвет ячеек меняется.
Спасибо

Олег
15 февраля 2018 14:31

Здравствуйте, Максим. Подскажите пожалуйста, Как записать правило форматирования, чтобы Сидоров, Иванов и Петров имели при вводе в ячейки один и тот же формат? спасибо

Олег
16 февраля 2018 11:43

Максим, спасибо за ответ. В общем то я и пользовался этим способом. Только выбирал вариант не «значение «, а «текст». Для трех-четырех слов это приемлемо. А если слов 30-100… К сожалению, Этот вариант долгий и нудный, т.к. нельзя ввести по очереди для выбранного формата разные слова. Каждый раз после кнопки ОК окно закрывается и начинай сначала с выбора формата. Ведь должен быть вариант где это делается более красиво? Спасибо

Артем
12 июля 2018 10:35

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

Артём
29 октября 2018 13:11

Добрый день.
Как установить правило, что бы цвета были с переходом, как на картинке.

123
Татьяна
8 ноября 2018 19:00

Добрый день. на листе 1 имеется сводная таблица которая считает остатки. Как мне сделать условное форматирование на листе 2 (на нем записываются продажи), чтобы при остатках продукции меньше 0 с листа 1, на листе 2 выделялось значение каким либо цветом. заранее спасибо

Макс
2 декабря 2018 16:43

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

Эдя
20 декабря 2018 17:46

Какое назночение ус.ф.?

vagizx@yandex.ru
2 апреля 2019 08:32

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

Ксения
26 января 2020 15:46

Чем отличается условное форматирование от просто форматирования?

Юрий
10 апреля 2020 20:04

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

Кирилл
12 мая 2020 15:57

Условное форматирование можно применить к таблице Excel, уже имеющей форматирование??

Олег
20 мая 2020 07:23

Добрый день. Вопрос на Вашем примере. Как отформатировать (красной заливкой) ячейки (столбец) с фамилиями сотрудников, у которых размер заработной платы меньше 10.000 рублей? Спасибо.

Аноним
31 мая 2020 10:41

Добрый день! В какой версии Excel, в каком году впервые появилась функция «Условное форматирование»(правила выделения ячеек, непосредственно)?

Александр
27 марта 2021 11:32

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

Аноним
22 сентября 2021 01:21

Добрый день! подскажите пожалуйста как создать правило в одном столбце: так чтобы при сравнении сумма строк (1+2)не равно (3+4), то строки 1+2 загорались красным.

строка 1
строка 2
строка 3
строка 4

Аноним
2 февраля 2022 09:59

Правила удаляются очень медленно, иногда зависает и перескакивает на правило которое не надо удалять. После обновления до Windows 10 приходится тратить времени на операцию от 4 до 11 раз больше

Задать вопрос