10 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

Условное форматирование позволяет автоматически изменять форматирование ячеек в зависимости от их содержимого.

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

Условное форматирование можно использовать определения ячеек, которые содержат недопустимые значения, или ячеек определенного типа. Затем согласно примененному форматированию можно определить, какие ячейки содержат значения определенного типа. Например, к искомым ячейкам можно применить ярко-красный фон.

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

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

  1. Выделите диапазон или ячейку.
  2. Выберите команду ФорматУсловное форматирование. На экране появится одноименное диалоговое окно, показанное на рис. 3.3.

Рис. 3.3. Диалоговое окно Условное форматирование

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

Параметры форматирования

После нажатия на кнопке Формат диалогового окна Условное форматирование
откроется окно Формат ячеек (рис.3.4).

Рис. 3.4. Диалоговое окно Формат ячеек, используемое для условного форматирования

Оно несколько отличается от обычного окна Формат — в нем отсутствуют вкладки Число, Выравнивание и Защита, но появилась новая Очистить.

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

Определение условия

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

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

Простые условия

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

  • между. Указывается два значения.
  • вне. Указывается два значения.
  • равно. Указывается одно значение.
  • не равно. Указывается одно значение.
  • больше. Указывается одно значение.
  • меньше. Указывается одно значение.
  • больше или равно. Указывается одно значение.
  • меньше или равно. Указывается одно значение.

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

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

При выборе элемента Формула в поле, расположенном справа, нужно задать формулу. Это можно сделать, просто определив ячейку с формулой на рабочем листе, а затем указав ссылку на нее, или же введя ее непосредственно в диалоговом окне Условное форматирование. Как всегда, перед началом формулы должен стоять знак равенства (=).

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

Формула должна возвращать только логические значения ИСТИНА или ЛОЖЬ. Если результат соответствует значению ИСТИНА, условие выполняется и к ячейке или диапазону применяется условное форматирование. Иначе (если результат принимает значение ЛОЖЬ) условное форматирование не применяется.

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

Например, если нужно задать условное форматирование, которое будет менять цвет заливки пустых ячеек в диапазоне С2:С10, выполните ряд действий.

  1. Выделите диапазон С2:С10, начиная с ячейки С2, т.е. чтобы ячейка С2 была активной.
  2. Выберите команду ФорматУсловное форматирование. Откроется диалоговое окно Условное форматирование.
  3. Из левого раскрывающегося списка выберите формула.
  4. В поле справа от выпадающего списка введите формулу =С2="" .
  5. Щелкните на кнопке Формат, чтобы открыть диалоговое окно Формат ячеек.
  6. В этом диалоговом окне определите цвет заливки и щелкните на кнопке ОК.
  7. В диалоговом окне Условное форматирование щелкните на кнопке ОК, чтобы закрыть его.
    Обратите внимание на то, что введенная формула содержит ссылку на верхнюю левую ячейку выделенного диапазона. Чтобы убедиться в том, что ссылка является относительной, нужно выделить одну из ячеек диапазона (например, С5) и посмотреть на формулу условного форматирования для этой ячейки. Формула будет иметь следующий вид: =С5=""

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

Например, в диапазоне А2:В10 необходимо применить условное форматирование только к тем ячейкам, значения которых больше значения ячейки С1. Для этого следует ввести формулу =А2>$С$1. В этом случае ссылка на ячейку С1 будет абсолютной и одинаковой во всех ячейках выделенного диапазона. Другими словами, формула условного форматирования, например, для ячейки А3 будет выглядеть следующим образом: =А3>$С$1. Следовательно, для всех ячеек выделенного диапазона относительные ссылки будут изменяться в соответствии с позицией ячейки в диапазоне, а абсолютные всегда будут ссылаться на одну и ту же ячейку.

Множественные условия

Щелкнув на кнопке А также диалогового окна Условное форматирование, можно определить дополнительное условие (трех условий).

Например, для ячейки или диапазона можно определить следующие условия (а также параметры форматирования, которые будут применяться при выполнении тех или иных условий):

Значение ячейки меньше 0
Значение ячейки равно 0
Значение ячейки больше 0

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

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

Значение ячейки лежит между 1 и 12
Значение ячейки меньше 6

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

Копирование ячеек, содержащих условное форматирование

Информация об условном форматировании сохраняется в ячейке почти так же, как и стандартное форматирование. Это значит, что при копировании ячейки копируется и
примененное к ней условное форматирование (если оно есть).

Чтобы копировать только условное форматирование, в диалоговом окне Специальная вставка нужно выбрать опцию форматы.

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

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

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

При удалении содержимого ячейки с помощью нажатия клавиши <Delete> условное форматирование, которое ранее было применено к этой ячейке, не удаляется. Чтобы удалить условное форматирование как и прочее форматирование, выберите команду Правка />Очистить />Форматы.

Для удаления содержимого ячейки (включая форматирование) выберите команду Правка /> Очистить /> Все.

Чтобы удалить только условное форматирование (и оставить содержимое и другие параметры форматирования ячейки), выполните ряд действий:

  1. Выделите ячейки. Затем выберите команду ФорматУсловноеформатирование; откроется одноименное диалоговое окно.
  2. В диалоговом окне Условное форматирование нажмите кнопку Удалить. Откроется диалоговое окно Удаление условия форматирования (рис. 3.5).

Рис. 3.5. Диалоговое окно Удаление условия форматирования

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

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

  1. Выберите команду ПравкаПерейти (или нажмите F5 или <Ctrl>+<G> ). Откроется диалоговое окно Переход.
  2. В диалоговом окне Переход щелкните на кнопке Выделить, чтобы открыть диалоговое окно Выделение группы ячеек, которое показано на рис. 3.6.

Рис. 3.6. Диалоговое окно Выделение группы ячеек

Использование ссылок на другие рабочие листы

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

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

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

Например, если необходимо, чтобы формула условного форматирования ссылалась на ячейку А1 рабочего листа ЛистЗ, нужно вставить в ячейку активного листа следующую формулу.
=ЛистЗ!А1
После этого можно использовать ссылку на данную ячейку в формуле условного форматирования.

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

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

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

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

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

Пример для демонстрации условного форматирования в Excel 2003

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

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

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

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

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

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

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

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

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

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

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

Скриншот выпадающего окна Excel "Условное форматирование"

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

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

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

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

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

Фрагмент файла с примером условного форматирования №1

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

Фрагмент файла с примером условного форматирования №2

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

Фрагмент файла с примером условного форматирования №3

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

В программе есть определенный набор условий, по которому можно выделять числа:

  1. Выделение (больше, меньше, равно, между, повторяющееся).Выделение
  2. Отбор первых и последних.Установка отбора

Меню инструментов

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

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

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

Стиль формата

А также в этом окошке возможно задать и другие функции, как, например, стиль формата (знаки или цвет) и отбор.

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

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

Противоречие правил

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

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

Встроенные функции

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

Аргументами функций могут быть выражения, а также другие функции.

В состав Excel входит свыше 300 функций. Самый простой и удобный способ использования встроенных функций – использование ^ Мастера функций, в котором все функции разделены на несколько категорий (рис. 13, табл. 6).

Рис. 13. Диалоговое окно «Мастер функций – шаг 1 из 2»

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

КатегорияНазначение
ФинансовыеРасчет процентов, увеличения и уменьшения суммы капитала и т. д.
Дата и времяВычисления с датами и временем.
МатематическиеМатематические и тригонометрические операции вроде тех, которые выполняются на научных калькуляторах.
СтатистическиеВычисление средних значений, сумм, распределений и стандартных отклонений.
Ссылки и массивыВычисления с таблицами данных.
Работа с базой данныхРабота со списками и внешними базами данных.
ТекстовыеСравнение, преобразование и форматирование текста в ячейках.
ЛогическиеВычисления, результатом которых может быть значение ИСТИНА или ЛОЖЬ.
Проверка свойствОпределение ошибок, происходящих при вычислениях и значений.

Вызов ^ Мастера функций осуществляется командой Функция меню Вставка, либо кнопка Вставка функции на стандартной панели инструментов.

Одна из самых часто используемых математических функций – функция СУММ, которая автоматически вычисляет сумму значений из выделен­ного диапазона. Эта функция может иметь до 30 аргументов, причем каждый аргумент может быть числом, адресом ячейки или блока ячеек, содержащих число или формулу. Например,

^ СУММ(А2; В2:К2; 500; КОРЕНЬ(9))

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

Хотелось бы обратить внимание на другие математические функции:

^ ОКРУГЛ – округляет число до указанного количества десятичных знаков;

ОТБР – отбрасывает дробную часть числа;

ЦЕЛОЕ – округляет число до ближайшего меньшего целого.

Из логических функций следует отметить функцию ЕСЛИ (рис. 14), которая определяет действие в случае выполнения какого-либо условия, и имеющая следующий синтаксис:

ЕСЛИ (<условие>; <результат 1>; <результат 2>)

Результат 1 – результат, который должен возвращаться функцией, если условие выполняется.

Результат 2 – результат, который должен возвращаться функцией, если условие не выполняется.

= ЕСЛИ (СУММ(В1:В6)>0; СУММ(В1:В6); 0)

Иногда после проверки одного какого-то условия требуется проверка дополнительных условий. В этом случае используют вложенные функции ЕСЛИ (можно использовать до 7 уровней вложения функции ЕСЛИ).

Рис. 14. Диалоговое окно «Мастер функций – шаг 2 из 2»

Например, вычислить значение функции Y (в ячейке В2) в зависимости от значения аргумента X (ячейка (А2):

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

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

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

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

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

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

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

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

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

4. Использование сводных таблиц для определения повторяющихся значений

Воспользуемся уже знакомой нам таблицей с тремя столбцами и добавим четвертый, под названием Счетчик, и заполним его единицами (1). Выделяем всю таблицу и переходим по вкладке Вставка в группу Таблицы, щелкаем по кнопке Сводная таблица.

сводная таблица excel

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

голоса
Рейтинг статьи
Читайте так же:
Как пользоваться Snapchat на Андроиде
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector