Как сделать условное форматирование в excel 2007?

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

Условное форматирование уже рассматривалось ранее для предыдущих версий Excel.

Excel 2007 предоставляет еще более мощные и удобные инструменты условного форматирования.

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

Для применения условного форматирования служит кнопка «Условное форматирование» на панели «Стили» ленты «Главная».

Чтобы лучше понять, как работает условное форматирование, выделите группу ячеек с уже введенными данными, нажмите кнопку «Условное форматирование» и посмотрите различные варианты форматирования.

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

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

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

В появившемся окне задайте конкретные условия форматирования. Если не подходят стандартные операторы для форматирования, воспользуйтесь пунктом «Другие правила..».

В нашем случае выделенная пустая ячейка окрасилась, т.к. по умолчанию Excel считает, что пустые ячейки эквиваленты нулевому значению. Если в ячейку ввести число большее 4, то ячейка обесцветится. Чтобы распространить условное форматирование на другие ячейки, необходимо правой кнопкой мыши потянуть за правый нижний угол только что отформатированной ячейки и в контекстном меню выбрать пункт «Заполнить только форматы».

В Excel 2007 имеется возможность условного форматирования верхних и нижних значений в диапазоне ячеек.

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

В появившемся окне можно подстроить условие форматирования.

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

Правило, находящееся вверху списка является самым приоритетным.

Для прекращения действия какого-либо правила форматирования необходимо установить флажок «Остановить, если истина».

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

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

В начало страницы

В начало страницы

Обучение условному форматированию в Excel с примерами

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

Как сделать условное форматирование в Excel

Инструмент «Условное форматирование» находится на главной странице в разделе «Стили».

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

Сравним числовые значения в диапазоне Excel с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».

Введем в диапазон А1:А11 ряд чисел:

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

Введем в левое поле число 15. В правое – способ выделения значений, соответствующих заданному условию: «больше 15». Сразу виден результат:

Выходим из меню нажатием кнопки ОК.

Условное форматирование по значению другой ячейки

Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.

Выделяем исходный диапазон и открываем окно инструмента «Условное форматирование» (ниже сокращенно упоминается «УФ»). Для данного примера применим условие «меньше» («Правила выделения ячеек» — «Меньше»).

В левое поле вводим ссылку на ячейку В2 (щелкаем мышью по этой ячейке – ее имя появится автоматически). По умолчанию – абсолютную.

Результат форматирования сразу виден на листе Excel.

Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.

Зададим условие форматирования: сравнить значения ячеек в разных диапазонах и показать одинаковые. Сравнивать будем столбец А1:А11 со столбцом В1:В11.

Читать еще:  Как сделать чтобы были видны все формулы в excel?

Выделим исходный диапазон (А1:А11). Нажмем «УФ» — «Правила выделения ячеек» — «Равно». В левом поле – ссылка на ячейку В1. Ссылка должна быть СМЕШАННАЯ или ОТНОСИТЕЛЬНАЯ! , а не абсолютная.

Каждое значение в столбце А программа сравнила с соответствующим значением в столбце В. Одинаковые значения выделены цветом.

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

В нашем примере в момент вызова инструмента была активна ячейка А1. Ссылка $B1. Следовательно, Excel сравнивает значение ячейки А1 со значением В1. Если бы мы выделяли столбец не сверху вниз, а снизу вверх, то активной была бы ячейка А11. И программа сравнивала бы В1 с А11.

Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.

Проверить правильность заданного условия можно следующим образом:

  1. Выделите первую ячейку диапазона с условным форматированим.
  2. Откройте меню инструмента, нажмите «Управление правилами».

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

Условное форматирование – несколько условий

Исходный диапазон – А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.

  • 1 способ. Выделяем диапазон А1:А11. Применяем к нему «Условное форматирование». «Правила выделения ячеек» — «Больше». В левое поле вводим число 6. В правом – «красная заливка». ОК. Снова выделяем диапазон А1:А11. Задаем условие форматирования «больше 10», способ – «заливка зеленым». По такому же принципу «заливаем» желтым числа больше 20.
  • 2 способ. В меню инструмента «Условное форматирование выбираем «Создать правило».

Заполняем параметры форматирования по первому условию:

Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.

Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».

То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).

Условное форматирование даты в Excel

Выделяем диапазон с датами.

Применим к нему «УФ» — «Дата».

В открывшемся окне появляется перечень доступных условий (правил):

Выбираем нужное (например, за последние 7 дней) и жмем ОК.

Красным цветом выделены ячейки с датами последней недели (дата написания статьи – 02.02.2016).

Условное форматирование в Excel с использованием формул

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

Есть столбец с числами. Необходимо выделить цветом ячейки с четными. Используем формулу: =ОСТАТ($А1;2)=0.

Выделяем диапазон с числами – открываем меню «Условного форматирования». Выбираем «Создать правило». Нажимаем «Использовать формулу для определения форматируемых ячеек». Заполняем следующим образом:

Для закрытия окна и отображения результата – ОК.

Условное форматирование строки по значению ячейки

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

Таблица для примера:

Необходимо выделить красным цветом информацию по проекту, который находится еще в работе («Р»). Зеленым – завершен («З»).

Выделяем диапазон со значениями таблицы. Нажимаем «УФ» — «Создать правило». Тип правила – формула. Применим функцию ЕСЛИ.

Порядок заполнения условий для форматирования «завершенных проектов»:

Обратите внимание: ссылки на строку – абсолютные, на ячейку – смешанная («закрепили» только столбец).

Аналогично задаем правила форматирования для незавершенных проектов.

В «Диспетчере» условия выглядят так:

Когда заданы параметры форматирования для всего диапазона, условие будет выполняться одновременно с заполнением ячеек. К примеру, «завершим» проект Димитровой за 28.01 – поставим вместо «Р» «З».

«Раскраска» автоматически поменялась. Стандартными средствами Excel к таким результатам пришлось бы долго идти.

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

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

Откройте свою таблицу продаж. Посмотрите на столбец Продано . Все ячейки в столбце имеют одинаковый дизайн. Представьте себе, что Вы хотите поощрить менеджеров, которые продают товара больше, чем на 30 долларов за одну сделку. И Вам нужно соответствующие ячейки как-то выделить. Как это сделать?

Выделяете весь столбец, выбираете в меню Главная — Условное форматирование — Правила выделения ячеек — Больше , и пишете в поле цифру 30. Выбираете цвет выделения и нажимаете ОК. Затем выделяете ячейку с заглавием, заходите в Условное форматирование , и выбираете Удалить правило — Удалить правило из выделенных ячеек .

Читать еще:  При вводе первых букв подбирается слово в excel как сделать

Ячейка с заглавием станет обычной, без выделения, а внизу те ячейки в столбце, значение которых больше 30, станут выделенными тем цветом, который Вы выбрали.

Условное форматирование в Excel можно наложить сверху на уже существующее. Выделите опять весь столбец, зайдите в Условное форматирование — Больше , и напишите в поле цифру 50. Затем примените другой цвет для выделения. Вы увидите, что цвет ячеек от 30 до 50 остался прежним, а цвет ячеек больше 50 изменился на новый. Опять уберите условное форматирование в ячейке с заголовком.

И теперь, сколько бы Вы не заполняли строчек с новыми данными, это условное форматирование в столбце будет действовать и выделять ячейки с числами больше 30 и 50.

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

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

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

Если Вам все эти варианты условного форматирования Excel не подходят, Вы можете создать и свои правила.

Выделите столбец Продано , и зайдите в Условное форматирование — Создать правило . Выделите строку Форматировать только ячейки, которые содержат , а внизу в окошке вместо слова Между поставьте Больше , и в следующем окошке напишите 30. Нажмите на кнопку Формат , и выберите, например, цвет заливки красный. Нажмите ОК, затем еще в одном окошке ОК. Удалите условное форматирование с ячейки с названием столбца. Все ячейки в столбце Продано, которые содержат цифры больше 30, после этого будут окрашены в красный цвет.

Условное форматирование в Excel можно изменить. Для изменения заходите в Управление правилами , и нажимаете на кнопку Изменить правило . Если правил несколько, соответствующее правило нужно выделить.

Видео о том, как сделать условное форматирование в Excel

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

Также Вы можете подписаться на блог, и узнавать о всех новых статьях.
Это не займет много времени. Просто нажмите на ссылку ниже:
Подписаться на блог: Дорога к Бизнесу за Компьютером

Вам понравилась статья? Поделитесь, буду весьма признателен:

Также приглашаю добавиться в друзья в социальных сетях:

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

Условное форматирование ячеек листа MS Excel или OOo Calc позволяет автору электронной таблицы существенно улучшить визуальное представление информации. Пользователи кроме повышенного эстетического восприятия получают инструмент контроля.

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

Термин «условное» не означает, что форматирование «как бы есть, и как бы его нет». «Условное» – это форматирование по условиям, которые задал автор таблицы для определенных ячеек рабочего листа. Этим инструментом почему-то не очень часто пользуются, хотя он очень и эффективен, и эффектен! (Почти как «масло — масляное»!)

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

Предлагаю перейти к рассмотрению примера, который более наглядно, нежели пространные определения, поможет раскрыть нашу тему.

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

В качестве примера будем использовать файл с расчетной программой из статьи «Расчет усилия листогиба».

Работать с файлом-примером будем в программе MS Excel 2003. Аналогичного результата можно достичь, работая в программе OOo Calc из пакета Open Office. Условное форматирование в MS Excel 2007 имеет гораздо больше интересных и разнообразных возможностей. Мы их немного коснемся в конце статьи.

Наша основная задача – разобраться с понятием «условное форматирование» и усвоить, что дает пользователю применение этого инструмента.

В файле примера выполняется расчет усилия развиваемого листогибочным прессом при свободной гибке деталей из листового металлопроката в «V»-образной матрице. Расчет ведется по двум различным методикам, и результаты сравниваются в конце. В качестве результатов расчета представлена таблица, показывающая зависимость усилия гибки от угла.

Читать еще:  Как сделать чтобы строки автоматически добавлялись в excel?

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

Применять условное форматирование будем только к результатам, полученным по формуле №1 для визуального сравнения с результатами формулы №2, которые форматировать не будем.

Формулировка условий:

1. Максимальное значение усилия гибки должно быть выделено жирным шрифтом белого цвета на оранжевом фоне.

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

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

Назначение условий форматирования:

1. Становимся курсором мыши на ячейку G12 (активируем ячейку).

2. В строке меню нажимаем «Формат» > «Условное форматирование…».

3. В выпавшем окне «Условное форматирование» назначаем условия, которые мы сформулировали чуть выше. На скриншоте ниже показан результат, который необходимо достичь! Я уверен, что затруднений ни у кого не должно возникнуть. Все интуитивно достаточно понятно!

Функция «НАИБОЛЬШИЙ($G$12:$P$12;1)» находит в указанном диапазоне G12:P12 максимальное значение. (Если в конце выражения в скобках поставить 2 вместо 1, функция найдет второе по величине значение в заданном массиве.)

4. Закрываем окно «Условное форматирование» нажатием на кнопку «ОК».

5. Для распространения форматирования на другие ячейки диапазона, копируем содержимое вместе с форматированием ячейки G12 в ячейки H12…P12. (Условное форматирование можно назначать так же, как и обычное, выделив необходимый диапазон ячеек или при помощи специальной вставки, выбрав для копирования только форматы.)

Результаты работы условного форматирования:

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

Изменим длину сгибаемого листа в ячейке D3 с 1000 мм на 1700 мм. Заливка ячеек I12 и K12 стала розовой, усилие пресса превзошло 80 тонн! Программа цветом ячеек предупреждает: «Внимание. Осторожно. »

Увеличим еще длину сгибаемого листа в ячейке D3 с 1700 мм до 2140 мм. Заливка ячеек I12 и K12 автоматически тут же превратилась в красную, усилие пресса превысило 100 тонн! Программа, как бы, кричит пользователю: «Внимание. Недопустимая операция. »

В Excel 2003 возможности условного форматирования многими считаются весьма скудными по сегодняшним меркам, даже размер шрифта нельзя поменять. К ячейке можно применить всего три условия, причем приоритет первого будет выше второго и третьего. Однако основную идею условного форматирования этот простой набор возможностей успешно реализует. Абсолютно аналогичные возможности предоставляет программа OOo Calc при почти полной идентичности интерфейса.

В Excel 2007 все выглядит красивее, изящнее, разнообразнее, но суть остается той же! Кроме возможности создания своих правил, Excel 2007 предлагает пользователю целый ряд встроенных правил форматирования. В ячейках вместо заливки можно поместить маленькие гистограммы, оформленные цветовыми градиентами или применить цветовые шкалы с плавным переходом от одного цвета к другому, где конечные цвета – это соответственно максимальное и минимальное значения форматируемого диапазона. В ячейки с числовыми значениями могут быть добавлены различные значки – стрелки, «огни светофора», разноцветные флажки, столбчатые маленькие диаграммы и другие дополнительные визуальные эффекты. Правил-условий форматирования может быть не три, а сколь угодно много. Приоритет «верхних» правил над «нижними» сохранен так, как и в Excel 2003.

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

Иногда, разбираясь в возможностях различных программ, начинаешь понимать, как сделать то или иное действие, но не понимаешь, для чего этот результат может быть с пользой применен. В этой статье рассмотрен лишь один из десятков или даже сотен вариантов использования условного форматирования при работе с файлами Excel. Жизненные ситуации и опыт помогут вам постепенно лучше освоить и использовать эту одну из массы возможностей Excel! Уверен, что инструмент «Условное форматирование» станет вашим повседневным удобным и мощным помощником при анализе табличных данных!

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

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

Оставляйте Ваши комментарии и отзывы, уважаемые читатели.

Ссылка на скачивание файла с примером: uslovnoe-formatirovanie (xls 56,5KB).

Ссылка на основную публикацию
Adblock
detector