Условное форматирование в MS Excel с примерами - statanaliz.info

Условное форматирование в MS Excel с примерами

Условное форматирование в Эксель – этот тот инструмент, который делит работу на до и после его изучения. Суть в том, что при наступлении некоторого условия ячейки форматируются автоматически. Например, если число превышает значение 100, шрифт становится красным полужирным курсивом; когда до наступления платежа остается 2 дня, ячейка с датой подсвечивается желтым цветом; перевыполнение плана продаж на 5% и более окрашивается в зеленый цвет и т.д. и т.п.

Вот упрощенный, но реальный пример. Есть отчет о товарных запасах.

Таблица без форматирования

Менеджер по закупкам отслеживает те позиции, которые требуют пополнения. Для этого он смотрит в последнюю колонку, где рассчитывается товарный запас (ТЗ) в неделях. Если ТЗ меньше, скажем, 3-х, то нужно готовить заказ. Если меньше 2-х, то возникает риск дефицита и заказ нужно размещать срочно. Если в таблице десятки позиций, то просмотр каждой строки займет довольно много времени. А теперь та же таблица, где после применения условного форматирования значения ниже пороговых подсвечиваются некоторым цветом.

Таблица с условным форматированием

Согласитесь, так гораздо нагляднее. В реальности условия сложнее, а данные постоянно меняются. Поэтому эффект от применения условного форматирования – это многочасовая экономия времени ежедневно! Теперь для оценки запасов достаточно взглянуть на таблицу, а не анализировать каждую ячейку. Много желтого – пора действовать, много красного – ситуация критическая!

Для настройки условного формата следует воспользоваться соответствующей командой на вкладке Главная.

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

При ее нажатии открывается меню.

Меню условного форматирования

Верхние 5 команд – это готовые сценарии для быстрого условного форматирования. Чтобы ими воспользоваться достаточно выбрать нужный вариант и сделать минимальные настройки. Эти сценарии мы рассмотрим ниже.

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

Все сценарии разбиты на категории:

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

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

– Гистограммы

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

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

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

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

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

Больше...

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

Меньше… Форматируются ячейки, у которых значение меньше заданного порога.

Меньше...

Между… Форматирование наступает, если содержимое ячейки находится внутри заданных границ.

Между...

Равно… если значение или текст в ячейке совпадает с условием.

Равно...

Текст содержит… Если совпадает только часть текста (слово, код, комбинация символов и т.д).

Текст содержит…

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

Дата...

Повторяющиеся значения… выделяются ячейки с одинаковым содержимым. Отличный способ найти дубликаты (повторы). В настройках можно выбрать и обратный вариант – выделить только уникальные значения.

Повторяющиеся значения…

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

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

Первые 10 элементов… Выделяются первые топ–10 ячеек. Количество регулируется в диалоговом окне (можно сделать топ-5, топ-20 и др.).

Первые 10 элементов…

Первые 10%… Выделяются 10% наибольших значений. Долю можно изменить.

Первые 10%...

Последние 10 элементов… Аналогично с первым пунктом, только форматируются наименьшие значения.

Последние 10 элементов…

Последние 10%… Наименьшие 10% или другая доля от всех элементов.

Последние 10%...

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

Выше среднего…

Ниже среднего… Ниже средней арифметической.

Ниже среднего…

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

Гистограммы

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

Гистограммы условного форматирования

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

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

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

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

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

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

В ячейках Excel выглядит так.

Набор значков в условном форматировании

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

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

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

Диалоговое окно управление правилами

После нажатия кнопки «Изменить правило…» откроется окно, вид которого зависит от редактируемого правила.

Редактирование правила условного форматирования

Здесь также есть куча настроек, но мы их пока опустим. В целом там все интуитивно понятно. Нужно только поэкспериментировать. Практика – лучший учитель.

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

Удаление условного форматирования

Условное форматирование – это три шага вперед на пути к профессиональному использованию Excel. Поэтому рекомендую незамедлительно внедрить в практику.

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

Неправильный формат ячеек

Старайтесь также, чтобы количество используемых цветов было не больше трех. Иначе внимание рассеивается и может стать еще хуже.

Поделиться в социальных сетях:
  • Аноним

    Нужна помощь с УФ: есть таблица (6 столбцов 30 строк) в которую вводятся значения.
    Необходимо чтоб ячейка с последним введенным значением окрашивалась красным, а ячейка заполненная перед последним — в желтый и оставалась желтым навсегда.
    Значения могут быть между 0 и 2000.

    • Аноним

      Это уже работа для макроса

  • Аноним

    Приветствую!…
    Подскажите, пожалуйста..
    34330284000000004
    34330284000000003
    34330284000000002
    34330282000000005
    34330282000000004
    34330284000000006
    34330282000000006

    Почему Excel 2010 раскрасил эти значения в УФ как «повторяющиеся»? хотя они разные…
    Формат ячеек: текстовый.

    Спасибо!

    • Езепов Дмитрий

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

  • Аноним

    здравствуйте, как можно сделать, например письмо поступил к нам 01.08.2018, в течении 15 (например) дней должны отетить на это письмо, мне надо чтоб в течении 15 ячейка с датой менял цвета, чтоб знать что приближается окончательный срок для ответа на письмо

  • Аноним

    Здравствуйте. Имеется первая колонка, в ней перечень характеристик в строках. Далее идут колонки — это дни месяца. В какой то день, вносим данные, например символ +. Можно ли сделать так, что бы ячейка напротив + подсветилась?

  • Аноним

    Добрый день. Вы случайно не на связи? Простите но есть вопрос по условному форматированию

    • Езепов Дмитрий

      Добрый день. Задавайте.

  • Аноним

    Добрый день! Как можно окрасить строки эксель в желтый цвет при выполнении двух условий:
    1. Если стоит знак + в столбце С
    2. Нет значения в столбце D

    Необходимо одновременно выполнение двух условий, так как задано условие, если в столбце D стоит знак + строка окрашивается в зеленый цвет

  • Аноним

    Добрый день!
    Есть ли возможность «залить» ячейки содержащие формулы (любую формулу)?

    • Езепов Дмитрий

      Добрый день. Да, можно. Нужно проверить выполнение условия (наличие формулы) по формуле ЕФОРМУЛА.

      • Аноним

        Большое спасибо!

  • http://vk.com/id15940806 Руслан Мустакаев

    Добрый день!

    Подскажите пожалуйста, как выделить один диапазон ячеек, когда другой не содержит ничего (null, #ПУСТО!)?
    Т.е. у меня есть таблица, в ней, допустим, в ячейках А1:А10 указаны фамилии, а в ячейках Б1:В10 некоторые данные. При отсутствии данных во втором диапазоне (Б1:В10), выделяются соответствующие строки в первом диапазоне А1:А10.

    Заранее спасибо!

  • http://vk.com/id233990852 Олег Берговин

    Возможно ли привязать высоту строки к условному форматированию?
    Например: если значение A1=0, то и высота строки 1:1 тоже равна 0.

    • Езепов Дмитрий

      Нет, насколько знаю.

      • http://vk.com/id233990852 Олег Берговин

        Спасибо.

  • http://vk.com/id1971135 Гульнара Абдуллаева

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

  • http://vk.com/id233990852 Олег Берговин

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

  • http://community.wrxatlanta.com/proxy.php?link=https://www.gate.io/ar/signup/612995 gate.io

    For my thesis, I consulted a lot of information, read your article made me feel a lot, benefited me a lot from it, thank you for your help. Thanks!

  • https://www.binance.com/ru-UA/register?ref=YU3Z0EHF binance nft

    Thanks for sharing. I read many of your blog posts, cool, your blog is very good.

  • https://www.gate.io/tr/signup/XlQVXVo gate.io türkiye

    After reading your article, it reminded me of some things about gate io that I studied before. The content is similar to yours, but your thinking is very special, which gave me a different idea. Thank you. But I still have some questions I want to ask you, I will always pay attention. Thanks.

  • https://www.gate.io/pt-br/signup/XlQVXVo gateio

    I may need your help. I tried many ways but couldn’t solve it, but after reading your article, I think you have a way to help me. I’m looking forward for your reply. Thanks.

  • https://elitepipeiraq.com/ Iraq factory expertise

    Simply wish to say your article is as amazing. The clearness in your post is just nice and i could assume you’re an expert on this subject. Well with your permission let me to grab your feed to keep updated with forthcoming post. Thanks a million and please carry on the gratifying work.

  • https://elitepipeiraq.com/ Pipe fittings solutions

    Fantastic site. Lots of helpful information here. I am sending it to some friends ans additionally sharing in delicious. And of course, thanks for your effort!

  • https://elitepipeiraq.com/ القدرة الإنتاجية للمصنع

    Its like you read my mind! You appear to know a lot about this, like you wrote the book in it or something. I think that you could do with some pics to drive the message home a little bit, but instead of that, this is fantastic blog. An excellent read. I will certainly be back.

  • https://bwerpipes.com/ bwer Factory Irrigation Iraq

    My brother suggested I might like this blog. He was totally right. This post actually made my day. You can not imagine simply how much time I had spent for this info! Thanks!

  • https://bwerpipes.com/ bwer Upvc Factory Iraq

    obviously like your web-site but you need to test the spelling on quite a few of your posts. Several of them are rife with spelling problems and I to find it very troublesome to inform the reality on the other hand I’ll certainly come back again.

  • https://bwerpipes.com/ bwer Agri Pipe Iraq

    Magnificent beat ! I would like to apprentice while you amend your site, how can i subscribe for a blog web site? The account helped me a acceptable deal. I had been a little bit acquainted of this your broadcast offered bright clear idea

  • https://bwerpipes.com/ bwer upvc iraq

    My brother suggested I might like this blog. He was totally right. This post actually made my day. You can not imagine simply how much time I had spent for this info! Thanks!

  • https://bwerpipes.com/ bwer Hdpe Factory Iraq

    I was recommended this website by my cousin. I am not sure whether this post is written by him as nobody else know such detailed about my difficulty. You are wonderful! Thanks!

  • https://bwerpipes.com/ bwer pipe iraq

    I was suggested this web site by my cousin. I’m not sure whether this post is written by him as no one else know such detailed about my trouble. You are incredible! Thanks!

  • https://bwerpipes.com/ bwer Hdpe Factory Iraq

    Its like you read my mind! You appear to know so much about this, like you wrote the book in it or something. I think that you can do with a few pics to drive the message home a little bit, but other than that, this is fantastic blog. A great read. I’ll certainly be back.

  • https://bwerpipes.com/ bwer Ductile Upvc Iraq

    What i do not realize is in fact how you are no longer actually much more well-favored than you might be right now. You’re very intelligent. You recognize thus considerably in relation to this topic, made me in my view believe it from numerous numerous angles. Its like men and women are not fascinated until it is one thing to do with Lady gaga! Your own stuffs excellent. All the time handle it up!

  • https://elitepipeiraq.com المورد أنابيب uPVC

    Hi, Neat post. There’s an issue together with your web site in internet explorer, may test this텶E still is the marketplace chief and a good component of people will pass over your fantastic writing due to this problem.

  • https://www.binance.info/zh-CN/join?ref=RQUR4BEO Binance推荐奖金

    Your point of view caught my eye and was very interesting. Thanks. I have a question for you. https://www.binance.info/zh-CN/join?ref=RQUR4BEO

  • https://afterwest.com/ Afterwest

    I loved as much as youll receive carried out right here The sketch is attractive your authored material stylish nonetheless you command get bought an nervousness over that you wish be delivering the following unwell unquestionably come more formerly again as exactly the same nearly a lot often inside case you shield this hike

  • https://www.binance.com/pl/join?ref=YY80CKRN Polecenie Binance

    Your point of view caught my eye and was very interesting. Thanks. I have a question for you. https://www.binance.com/pl/join?ref=YY80CKRN

  • https://accounts.binance.com/zh-TC/register-person?ref=DB40ITMB binance美國註冊

    Can you be more specific about the content of your article? After reading it, I still have some doubts. Hope you can help me. https://accounts.binance.com/zh-TC/register-person?ref=DB40ITMB

  • https://fitspresso-reviews.shop/ Fitspresso

    Wonderful web site Lots of useful info here Im sending it to a few friends ans additionally sharing in delicious And obviously thanks to your effort

Комментарии для сайта Cackle
Пролистать наверх