Изменение представления данных в зависимости от их значения

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

Другой способ, позволяющий упростить работу с информацией - настроить Excel так, чтобы оформление данных изменялось в зависимости от их значения. Такие форматы называютсяусловными форматами, или форматами с условием, т.к. данные должны отвечать определенным условиям, чтобы к ним применился тот или иной формат. Например, если владелец компании Кэтрин Тернер хочет выделить все дни субботы, в которые объем продаж компании "Все для сада" составил более $4.000, ей потребуется создать условный формат, проверяющий значение в ячейке с данными по продажам и изменяющий формат содержимого ячейки при выполнении условия.

Чтобы создать условный формат, выделите ячейки, к которым вы хотите его применить, и откройте диалоговое окно Условное форматирование (Conditional Formatting). На рисунке ниже показаны настройки в диалоговом окне Условное форматирование (Conditional Formatting) по умолчанию.

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

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

Вторая строка со списком в диалоговом окне Условное форматирование (Conditional Formatting) позволяет вам выбрать нужный тип сравнения. В зависимости от выбранного типа сравнения, в диалоговом окне будет либо одна, либо две строки, в которые нужно ввести значения для сравнения. Сравнение по умолчанию, "между", требует два значения, тогда как сравнения "меньше, чем" требуют ввода только одного значения.

После того как вы создали условие, вам нужно установить формат, который будет применяться к данным, отвечающим данному условию. Формат создается в диалоговом окне Формат ячеек (Format Cells). С помощью этого диалогового окна можно настроить шрифт, которым отображается значение в ячейке. Определенный вами формат появится в диалоговом окне Условное форматирование (Conditional Formatting).

Если это необходимо, можно не ограничиваться присвоением лишь одного условия каждой ячейке. Можно создать дополнительные условия, нажав кнопку А также (Add) в диалоговом окне Условное форматирование (Conditional Formatting). После нажатия кнопки А также (Add) появится окно второго условия.

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

Excel выполняет условия в том порядке, в котором вы задали их в диалоговом окне Условное форматирование (Conditional Formatting) и, при нахождении условия, которому отвечают данные, прекращает сравнения. Например, предположим, что Кэтрин хочет визуально разделить лимиты кредитования покупателей компании "Все для сада" на две различные категории: лимиты меньше 1.500$, и лимиты в пределах от 1.500$ до 2.500$. Она может установить отображение лимитов кредитования покупателей с применением условного формата, использующего условия, показанные на рисунке ниже.

В этом случае, Excel будет сопоставлять значение "1250" с первым значением, "<2500", и применять форматирование к ячейке, содержащей это значение. Второе условие, "<1500", ближе по модулю к сравниваемому с ним значению; как только Excel найдет условие, отвечающее этому значению, сравнение прекратится.

Совет. В первую очередь всегда задавайте наиболее строгое условие. В рассматриваемом примере установка первым условия "<1500" и вторым "<2500" даст правильный результат форматирования.Conditional

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

В панели инструментов Стандартная нажмите кнопку Открыть (Open)
. Появится диалоговое окно Открытие документа (Open).
Дважды щелкните на файле Conditional.xls. Документ Conditional.xls откроется.
Щелкните на ячейке K4.
В меню Формат (Format) выберите Условное форматирование (Conditional Formatting). Откроется диалоговое окно Условное форматирование (Conditional Formatting).
Во второй строке со списком щелкните на стрелке и выберите из списка слово между. Во второй строке со списком появится слово "между".
В первой строке аргумента введите 1000.
Во второй строке аргумента введите 2000.
Нажмите кнопку Формат (Format). Откроется диалоговое окно Формат ячеек (Format Cells).
Перейдите во вкладку Шрифт (Font).
В строке Цвет (Color) щелкните на кнопке списка и выберите из появившейся палитры синий (Blue) цвет. Цветовая палитра исчезнет, и цвет текста в области просмотра станет синим.
Нажмите ОK. Диалоговое окно Формат ячеек (Format Cells) закроется.
Нажмите кнопку А также (Add). В диалоговом окне появится секция Условие 2 (Condition 2).
Во второй строке со списком щелкните на стрелке и выберите из списка пункт "между".
В строке первого аргумента наберите 2000.
В строке второго аргумента наберите 2500.
Нажмите кнопку Формат (Format). Откроется диалоговое окно Формат ячеек (Format Cells).
В строке Цвет (Color) щелкните на стрелке и выберите из появившейся палитры зеленый цвет. Цветовая палитра исчезнет, и цвет текста в области просмотра изменится на зеленый.
Нажмите ОK. Диалоговое окно Формат ячеек (Format Cells) закроется.
Нажмите ОK. Диалоговое окно Условное форматирование (Conditional Formatting) закроется.
В ячейке K4 щелкните на прямоугольной точке в правом нижнем углу и перетащите указатель на ячейку K6. Содержимое ячеек K5 и K6 сменится на "$2.400.00", и появится кнопка Параметры автозаполнения (Auto Fill Options)
.
Щелкните на кнопке Параметры автозаполнения (Auto Fill Options) и в появившемся меню выберите Заполнить только форматы (Fill Formatting Only). Содержимое ячеек K5 и K6 изменится на их прежние значения, и Excel применит к выделенным ячейкам условное форматирование.
На панели инструментов Стандартная нажмите кнопку Сохранить (Save), чтобы сохранить сделанные изменения.
Нажмите кнопку Закрыть (Close) для закрытия документа Conditional.xls.

В панели инструментов Стандартная нажмите кнопку Открыть (Open)
. Появится диалоговое окно Открытие документа (Open).
Дважды щелкните на файле Conditional.xls. Документ Conditional.xls откроется.
Щелкните на ячейке K4.
В меню Формат (Format) выберите Условное форматирование (Conditional Formatting). Откроется диалоговое окно Условное форматирование (Conditional Formatting).
Во второй строке со списком щелкните на стрелке и выберите из списка слово между. Во второй строке со списком появится слово "между".
В первой строке аргумента введите 1000.
Во второй строке аргумента введите 2000.
Нажмите кнопку Формат (Format). Откроется диалоговое окно Формат ячеек (Format Cells).
Перейдите во вкладку Шрифт (Font).
В строке Цвет (Color) щелкните на кнопке списка и выберите из появившейся палитры синий (Blue) цвет. Цветовая палитра исчезнет, и цвет текста в области просмотра станет синим.
Нажмите ОK. Диалоговое окно Формат ячеек (Format Cells) закроется.
Нажмите кнопку А также (Add). В диалоговом окне появится секция Условие 2 (Condition 2).
Во второй строке со списком щелкните на стрелке и выберите из списка пункт "между".
В строке первого аргумента наберите 2000.
В строке второго аргумента наберите 2500.
Нажмите кнопку Формат (Format). Откроется диалоговое окно Формат ячеек (Format Cells).
В строке Цвет (Color) щелкните на стрелке и выберите из появившейся палитры зеленый цвет. Цветовая палитра исчезнет, и цвет текста в области просмотра изменится на зеленый.
Нажмите ОK. Диалоговое окно Формат ячеек (Format Cells) закроется.
Нажмите ОK. Диалоговое окно Условное форматирование (Conditional Formatting) закроется.
В ячейке K4 щелкните на прямоугольной точке в правом нижнем углу и перетащите указатель на ячейку K6. Содержимое ячеек K5 и K6 сменится на "$2.400.00", и появится кнопка Параметры автозаполнения (Auto Fill Options)
.
Щелкните на кнопке Параметры автозаполнения (Auto Fill Options) и в появившемся меню выберите Заполнить только форматы (Fill Formatting Only). Содержимое ячеек K5 и K6 изменится на их прежние значения, и Excel применит к выделенным ячейкам условное форматирование.

Содержание раздела