Условное форматирование ячеек в зависимости от результата вычисления

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

Условное форматирование осуществляется через выпадающее меню кнопки « Условное форматирование» в группе Стили Главной вкладки ленты инструментов.

Управление условным форматированием в Microsoft Excel 2010 осуществляется посредством правил. Каждое правило характеризуется своим условием отбора ячеек и своим способом их форматирования. К ячейкам можно применить одновременно несколько правил. Например, одно правило позволяет оформить светло-красной заливкой все положительные значения, а второе – светло-синей все отрицательные. Третье правило выделит ярко-красным полужирным шрифтом 5 ячеек с самыми большими значениями, а четвертое – ярко синим курсивным шрифтом 10 ячеек с самыми малыми значениями.

В выпадающем меню кнопки « Условное форматирование» содержатся наборы шаблонов, каждый из которых позволяет быстро создать правило и применить его к выделенным ячейкам. Эти шаблоны студентам следует изучить самостоятельно, мы же рассмотрим создание правил вручную. Для этого используется команда Создать правило… из выпадающего списка кнопки « Условное форматирование». Команда открывает диалоговое окно, варианты которого показаны на рис. 11.12.

Работа разных правил условного форматирования иллюстрируется рис. 11.13.

В верхней части окна выбирается один из шести возможных типов правил:

Ø Форматировать все ячейки на основе их значений. Правила данного типа сортируют все выделенные ячейки в соответствии с их значениями и присваивают каждой из них определенный формат. В Microsoft Excel 2010 доступны несколько видов правил данного типа:

o Двухцветная шкала (рис. 11.12, а) – Microsoft Excel формирует плавный градиентный переход от одного цвета к другому. Пользователь может задать эти цвета и то, какому значению они будут соответствовать. Это может быть как самое меньшее или самое большее из чисел, встречающихся в ячейках, так и конкретное число, заданное пользователем. Пример настроек, заданных в окне рис. 11.12, а, проиллюстрирован на рис. 11.13 (столбец а);

o Трехцветная шкала (рис. 11.12, б). То же самое, но можно задать не два, а три цвета, причем средний цвет не обязан располагаться строго по середине градиентного перехода. Пример настроек, заданных в окне рис. 11.12, б, проиллюстрирован на рис. 11.13 (столбец б). Видно, что 75% цветовой шкалы занимает переход от синего к голубому и 25% – от голубого к розовому;

o Гистограмма (рис. 11.12, в). В этом режиме поверх значения в ячейке отрисовывается полоса гистограммы, позволяющая наглядно оценить величину в ячейке. Для полосы можно задать значения, соответствующие ее нулевой и максимальной длине, цвет заливки (сплошной или в виде градиентного перехода от выбранного цвета к белому), цвет границы и направление столбца (влево или вправо). Флажок «Показать только столбец» заставляет Microsoft Excel скрыть числовое значение из ячейки. Пример настроек, заданных в окне рис. 11.12, в, проиллюстрирован на рис. 11.13 (столбец в).

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

o Набор значков (рис. 11.12, г). Диапазон значений ячеек разбивается заданными пользователем числами на три-пять частей, и рядом со значением отображается соответствующий значок. Пользователь может либо выбрать готовый набор значков, либо сформировать свой. Пример настроек, заданных в окне рис. 11.12, г, проиллюстрирован на рис. 11.13 (столбец г). Флажок «Показать только значок» также заставляет Microsoft Excel скрыть числовое значение из ячейки.

Как видно на рис. 11.12, а-в, в диалоговом окне присутствует поле «Тип:». Это поле позволяет по-разному задавать числовые значения для цветов, значков, начала и конца диаграммы. Доступны следующие значения поля «Тип:»:

o Авто (для гистограмм) – Microsoft Excel назначает тип и значения по своему усмотрению;

                                  а)                                                                        б)

                             в)                                                                         г)

                                       д)                                                                             е)

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

                                      ж)                                                                            з)

Продолжение рис. 11.12

Рис. 11.13 Диалоговое окно настройки отрицательной части гистограммы условного форматирования

o Число – пользователь вводит число вручную;

o Процент – интервал от минимального до максимального значения берется за 100%; пользователь задает процент в виде числа от 0 до 100 (знак «%» не пишется), а Microsoft Excel автоматически пересчитывает его в число;

o Формула – пользователь может самостоятельно ввести в ячейку формулу; результат ее вычисления должен быть числом, которое и будет сопоставлено с цветом;

o Процентиль – если, например, пользователь введет значение 10 (10-я процентиль), то Microsoft Excel автоматически подберет такое число, чтобы 10% ячеек в диапазоне было меньше этого числа, а 90% ячеек – больше. Именно с этим числом будет сопоставлен цвет. Отметим, что 50-ю процентиль в математической статистике принято называть медианой.

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

o Значение ячейки, которое может быть больше, меньше, больше или равно, меньше или равно, равно, не равно некоторому числу, заданному пользователем, а также лежать внутри или вне интервала чисел. Пример настроек, заданных в окне рис. 11.12, д, проиллюстрирован на рис. 11.13 (столбец д);

o Текст ячейки содержит или не содержит некоторые символы, начинается с них или заканчивается ими;

o Дата в ячейке – вчерашняя, сегодняшняя, завтрашняя, соответствует прошедшей, текущей или следующей неделе, прошедшему, текущему или следующему месяцу;

o Пустые / непустые ячейки, ячейки с ошибками / без ошибок.

Ø Форматировать только первые или последние значения (рис. 11.12, е). Пользователь вводит количество (или процент) ячеек с самыми большими значениями, к которым и применяется форматирование. Например, если имеется 250 ячеек с числами, и пользователь задает «Первым», «10» и снимает флажок, форматирование будет применено к 10 ячейкам с самыми большими значениями. Если флажок «% от выделенного диапазона» установлен, то будут выделены форматированием 25 (10% от 250) ячеек. Пример настроек, заданных в окне рис. 11.12, е, проиллюстрирован на рис. 11.13 (столбец е).

Ø Форматировать только значения, которые находятся выше или ниже среднего (рис. 11.12, ж). В этом случае Microsoft Excel вычисляет среднее арифметическое () значений в ячейках и выделяет форматированием те из них, которые больше, меньше, больше или равны, меньше или равны этому среднему. Также есть возможность выделить ячейки, которые больше ,  и  либо меньше ,  и , где  – стандартное (среднеквадратическое) отклонение значения ячейки от его среднего. Пример настроек, заданных в окне рис. 11.12, ж, проиллюстрирован на рис. 11.13 (столбец ж)

Ø Форматировать только уникальные или повторяющиеся значения – Microsoft Excel на выбор пользователя находит ячейки с повторяющимися значениями (т.е. значениями, которые содержатся больше чем в одной ячейке) или, наоборот, не повторяющимися значениями, и выделяет их форматированием.

Ø Использовать формулу для определения форматируемых ячеек (рис. 11.12, з) – в этом случае пользователь вводит математическую формулу, значение которой зависит от значений величин в ячейках. Формула составляется для первой ячейки выделенного диапазона; в формуле применяются относительные ссылки. Это позволяет Microsoft Excel «мысленно» размножить формулу и рассчитать ее для каждой ячейки диапазона. Форматирование применяется к тем ячейкам, для которых рассчитанное формулой значение – «ИСТИНА». Пример настроек, заданных в окне рис. 11.12, з, проиллюстрирован на рис. 11.13 (столбец з). Здесь формула при помощи функции «ОСТАТ» (функциям будет посвящена Лабораторная работа №12) сравнивает остаток от деления содержимого ячейки на 3 с числом «0». Закрашены будут, соответственно, только те ячейки, значения которых делятся на 3 нацело.

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

Рис. 11.15 Диспетчер правил условного форматирования

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

Для всех типов правил, кроме «Форматировать все ячейки на основе их значений» Microsoft Excel принимает решение о применении или не применении форматирования, заданного при помощи кнопки «Формат…» в окне рис. 11.12. Для таких типов в правой части таблицы в окне рис. 11.15 доступен флажок «Остановить, если истина». Если флажок установлен, то для ячеек, к которым в рамках данного правила форматирование было применено, последующие правила применяться не будут. Например, в списке учащихся можно залить красным цветом студентов, которые имеют пропуски без уважительной причины (не могут претендовать  на экзамен «автоматом»), затем выделить тех из оставшихся, кто имеетсредний балл по контрольным работам выше 8,5. Для студентов, имеющих пропуски, автомат не предусмотрен, и выделять среди них успешно написавших контрольные не нужно; для правила, связанного с пропусками, имеет смысл поставить флажок «Остановить, если истина».

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

Специальная вставка

При копировании ячеек через буфер обмена Microsoft Excel полностью копирует ячейку, включая формулы и возвращаемые ими значения, примечания и настроенное для ячейки форматирование. Часто это оказывается не нужно. Например, может понадобиться скопировать только возвращаемое формулой значение, скопировать форматирование ячейки без ее содержимого либо наоборот, содержимое ячейки без какого-либо форматирования. Достичь этого помогает средство специальной вставки, активируемое командой Специальная вставка… из выпадающего меню кнопки « Вставить» в группе Буфер обмена Главной вкладки ленты инструментов. Команда открывает одноименное диалоговое окно (рис. 11.16).

Рис. 11.16 Диалоговое окно специальной вставки

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

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

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

Флажок «Транспонировать» при вставке позволяет сделать строки диапазона столбцами, а столбцы – строками, т.е. перевернуть таблицу «на бок».

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

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

Более удобной альтернативой диалоговому окну специальной вставки в Microsoft Excel являются кнопки из выпадающего списка кнопки « Вставить». Значения кнопок приведены в табл. 11.5.

Табл. 11.5

	Выбор режима вставки на усмотрение программы		Формулы не сохраняются, сохраняется формат числа
	Формулы сохраняются, форматирование – нет		Формулы не сохраняются, форматирование сохраняется
	Сохраняются формулы и формат числа (числовой, денежный, дата/время)		Копируется только форматирование, без формул и значений
	Сохранение формул и всего форматирования		Вставка в виде ссылки на ячейку
	Сохраняются формулы и все форматирование, кроме рамок		Вставка значения в виде нередакти­руемого рисунка
	Сохранение формул и всего форматирования, а также применение ширины столбца оригинальной ячейки;		Вставка в виде рисунка, который автоматически обновляется при изменении исходной ячейки
	Вставка с транспонированием		Вставка текста из другого приложения без сохранения форматирования
	Ни формулы, ни форматирование не сохраняются

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

Еще одним способом скопировать ячейку в виде нередактируемого рисунка является использование команды Копировать как рисунок… из выпадающего меню кнопки « Копировать». В этом случае в выпадающем меню кнопки « Вставить» будет доступно только одно действие – вставка по умолчанию (кнопка ).

Также после вставки ячеек из буфера обмена рядом с ними появляется символ , щелчок по которому (или нажатие клавиши Ctrl) открывает меню, дублирующее выпадающее меню кнопки « Вставить».

ЛАБОРАТОРНАЯ РАБОТА №12
Использование встроенных функций в электронных таблицах Microsoft Excel

ЗАДАНИЕ

1) Открыть книгу Excel, созданную на Лабораторной работе №11. При необходимости добавить в нее чистый лист;

2) Решить предложенные преподавателем задачи, соблюдая следующие требования:

а) Ячейки с константами, если таковые имеются, выделять форматированием;

б) Если задача требует ответа на вопрос в виде «Да» или «Нет», воспользоваться функцией ЕСЛИ. По требованию преподавателя выделить ответ условным форматированием;

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

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

д) Если в задаче предполагается одновременное использование нескольких условий, следует объединять их функциями И или ИЛИ;

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

ж) Если задача предполагает ответ в виде текстового предложения, следует сформировать его в ячейке при помощи функции СЦЕПИТЬ;

з) Ответ на задачу следует выделить форматированием;

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