Электронные таблицы: Excel, условное форматирование
Общие положения
Данный термин (Conditional formating) был не совсем корректно переведен с английского языка. Было бы правильнее говорить о форматировании по условию.
Это означает, что мы можем задать некое условие (или их набор), в случае соблюдения которого форматирование указанных ячеек будет изменено в соответствии с нашими правилами.
Функция крайне важна, так как позволяет автоматически выделить ячейки определенным образом для максимальной наглядности.
С большим огорчением я вынужден констатировать, что все приведенные ниже технологии относятся в версии 2003 и не могут быть перенесены на последующие разработки. В процессе развития было устранено большинство недостатков, но половина возможностей в принципе не будет работать, если пытаться использовать стандартные и привычные приемы.
Условное форматирование обладает комплексом достаточно непростых формальных закономерностей.
- Применяется к текущей ячейке (диапазону).
Доступ к этим материалам предоставляется только зарегистрированным пользователям!
Банальные рассуждения о зрительном выделении
Единственным назначением условного форматирования является принуждение к повышенному вниманию к значениям отдельных ячеек. Не так важно, что мы хотим увидеть: негативные или позитивные значения, их отсутствие или превышение пределов, важно только одно — не пропустить их.
При этом не стоит, конечно, забывать и о ранжировании, то есть возможности зрительно оценить общие тенденции.
Фактически, у нас есть только три свойства, которые имеет смысл рассматривать: шрифт, рамка ячейки и заливка. Но особенностей использования и противоречий хватает и здесь.
- Принцип «светофора», когда для «плохих» данных используется красный, «хороших» — зеленый, а «нейтральных» — желтый, востребован всегда и понятен любому зрителю.
Единственное, что стоит учесть, это сложность восприятия желтых символов, для которых лучше использовать производный, но более контрастный цвет: оранжевый или средне-коричневый.
Доступ к этим материалам предоставляется только зарегистрированным пользователям!
Версия Excel 2016
Доступ к условному форматированию осуществляется через вкладку ленты Главная и одноименную кнопку группы Стили, открывающую перечень возможных действий.
- Правила выделения ячеек
- Правила отбора
- Гистограммы
- Цветовые шкалы
- Наборы значков
- Создание, удаление и управление правилами
....
Разбор будет производиться не по порядку, а по значимости для большинства пользователей с моей личной точки зрения.
Создание правил
Сразу оговорюсь, что любую работу по созданию правил удобнее делать для единичной ячейки. Тем более, что расширение диапазона его действия прекрасно производится через управление.
Запуск соответствующего пункта меню приводит к появлению достаточно сложного диалога.
Исходя из моего позитивного опыта работы компании Microsoft, рискну предположить, что порядок пунктов определяется частотой их использования конечными пользователями. Также в пользу этого аргумента говорит и мой опыт взаимодействия с «офисным планктоном», для которых замена процесса мышления иллюзией получения результата нажатием кнопки крайне важна.
....
Форматировать только ячейки, которые содержат
Абсолютная важность данного способа заключается в том, что он соответствует подходу, использовавшемуся в предыдущих версиях. Подчеркиваю, всех предыдущих версий до 2007! Это безальтернативно говорит о востребованности теми, кто действительно нуждается в обдуманном результате.
Варианты «Пустые», «Не пустые», «Ошибки» и «Без ошибок» говорят сами за себя. Но, давайте для начала кратко обговорим их особенности.
Пустые ячейки относятся к нормальной ситуации, но только не для сведений, обязательных к вводу. Это — гениальный способ избежать проблем с пропущенным вводом. Единственное отклонение — «нулевые» данные: пустая строка и число, равное нулю. Для их обнаружения данный способ не подходит, потребуется указание вручную.
Ошибки, в большинстве своем, и так достаточно активно выделяются знаками (#) и хорошо знакомы всем пользователям. Только при сложном формульном формировании больших таблиц они могут оказаться критически важными. Кроме того, есть косвенные ошибки, которые программа определяет, но информирует пользователя гораздо более гуманно, то есть не так заметно.
Из перечня видов аналитических подходов, принципиально важны первые три (число, текст, дата), сущностно похожие на принципы, используемые при фильтрации и дающие следующие операторы:
Числовая ячейка должна сравниваться с числом. Но это получается далеко не всегда и противоречивость здесь максимальна.
Начнем с того, что значение ячейки может относиться к другому типу. Сразу, либо как следствие редактирования, предвидеть которое невозможно. Отсюда вытекает сложнопреодолимое противоречие: для решения подобных проблем в условии нужно прописывать анализ ячеек, что достаточно трудоемко.
Далее, числовой смысл ячейки равен:
- Числу для числовой ячейки.
- Числу для ячейки с датой или временем.
- Нулю для пустой ячейки или содержащей пустую строку.
- Больше нуля для символьной строки.
Достаточно отчетливо масштаб проблемы обозначен на рисунке, пользуясь которым, вы можете частично прогнозировать ситуацию и описывать условия.
Здесь зеленым цветом обозначены ячейки по условию >0, розовым — =0, A3 — пустая.
Текстовая ячейка примитивна, но попытки обработать по её правилам другие данные, также чреваты сюрпризами.
....
Ячейка с датами
....
Форматировать только уникальные или повторяющиеся значения
....
Форматировать все ячейки на основании их значений
Итак, первый пункт списка. Поскольку я им иногда пользуюсь, позволю себе предположить, что описание способа «для тупых» будет одним из самых больших: он очень прост только в варианте «ткнуть мышкой».
....
Использовать формулу для определения форматируемых ячеек
....
Форматировать только первые или последние значения
Достаточно простая ситуация, вполне логично вытекающая из содержания диалога.
Мы можем оформить иначе первые/последние значения, выраженные в штуках или процентах. Таким образом проще всего отслеживать негативные или позитивные элементы, оказывающие влияние на всю совокупность. Своего рода «отстающие» и «лидеры», но без необходимости сортировки данных и подсчетов.
Форматировать только значения, которые находятся выше или ниже среднего
Пункт содержит достаточно серьезную статистическую подоплеку. Можно произвести предварительную зрительную оценку данных без использования графических методов отображения совокупности.
Суть упирается в набор: >≥<≤ или ±1/2/3σ относительно средней арифметической диапазона.
Удаление правил
По умолчанию пункт имеет два активных подпункта.
Выделенный диапазон должен использоваться крайне сознательно. Мне сложно придумать, для чего это реально удобно, если не учитывать случай удаления ВСЕХ правил в части таблицы, без уничтожения данных. Того же эффекта можно (и проще) достигнуть Форматированием по образцу из незатронутой изменениями ячейки.
...Со всего листа означает довольно неприятный расклад, когда без какого-либо подтверждения, удалятся ВСЕ правила на листе, включая те области, которые не выводятся на экран. Будьте аккуратны!
Из этой таблицы пока остается загадкой. Даже сам термин непонятен.
Сводная таблица должна иметь место на листе.
....
Управление правилами
Хочется сказать, что диалог управления в целом удался программистам, так как он нагляден, удобен, функционален, но при этом оставляет достаточно много свободы опытному пользователю.
Вероятно именно здесь стоит проговорить порядок действия правил. Вроде всё просто: по списку от первого до последнего, что четко сформулировано в подсказке. Но правила добавляются вначало, а порядок их следования изменяется кнопкаами со стрелками (треугольниками) вверх/вниз для текущего (выделенного) правила. Не следует забывать о необходимости контроля.
Далее, логика может подчиняться стандартному правилу «вперед — самое частое» либо совершенно иным соображениям.
Первый пункт, определяющий область показа по умолчанию, содержит значение «Текущий фрагмент», наиболее приемлемое для работы. Кроме того, можно выбрать весь текущий лист или любой другой лист текущей книги. Надо полагать, что это своего рода «обзорная возможность» для поиска места расположения подходящего правила. Хотя возможности его копирования программными средствами нет.
Практически, крайне важно осуществлять этот выбор диапазона до входа в диалог, так как изменить его нельзя, а экран далеко не всегда может обеспечить полный обзор. Важность определяется и поставленной задачей: исправить правила или проверить их единообразие.
Двойной щелчок ЛКМ по области условия или формата возвращает нас к первичному диалогу создания правила, где вводятся/меняются соответствующие настройки. Действие эквивалентно нажатию на кнопку Изменить правило.
Применяется к может быть отредактировано вручную (не использовать клавиши перемещения курсора!) или через стандартный переход к выделению мышью.
Остановить, если истина принципиально влияет на результат. Установка галочки означает, что последующие правила не будут обрабатываться и применится именно данный вариант. Ещё один случай, когда подумать следует дополнительно.
....
Версия Excel 2003
Данный материал сохранен, чтобы стал более очевидным успех переработки возможности в последующих версиях.
Меню Формат→Условное форматирование... (Fomat→Conditional formating...).
Диалог выглядит как показано на рисунке.
Первый список позволяет указать, относительно чего будет формироваться условие: значений (Cell value is) или формул (Formula is).
В случае значений, нужно указать одно из условий сравнения
Для формулы —
Некоторые из них далее предполагают ввод двух (например, см. выше: между 5 и 10), а другие — одного значения (например, больше 5).
Кнопка справа от поля ввода (
) переводит программу в режим выбора координат ячеек при помощи мыши:
Помеченные ячейки выделяются пунктиром, а в поле вводятся правильные координаты:
Разумеется, что координаты можно и набрать, но при этом возрастает вероятность ошибки.
В большинстве случаев поля для ввода условий заполняются не координатами, а значениями (числовыми или текстовыми).
Если условие представляет собой текст, то его можно ввести не задумываясь. Excel сам превратит его в нужный вид. Примером может служить наш электронный журнал, где ячейка с пропусками помечается светло-коричневым фоном. В поле значения условия записано ="н".
После задания условия, необходимо разъяснить программе, каким образом отформатировать ячейку, когда это условие в ней выполняется. Нажимаем кнопку Формат...
и переходим к усеченному диалогу задания параметров ячейки:
Кнопка «А также >>» (Add >>) позволит добавить дополнительные условия. К сожалению, их количество ограничено только тремя.
Последующее развитие программы привело к устранению большинства описанных проблем, а базовый подход к условному форматированию перенесен в ячейки, которые содержат.
Ошибки в задании условий
-
Условие 1: от 10 до 1000.
Условие 2: равно 200. Оно не будет выполняться: уже сработало условие 1.
Условие 3: равно 500. Оно также не выполняется: уже сработало условие 1.
Выход: первое условие надо сделать третьим. Как? Удалить условие 1 и создать условие 3 с таким же содержанием. Вряд ли такая работа кому-либо понравится.
Вывод: прежде чем задавать условия, необходимо продумать, не перекроется ли последующее предыдущим. - Условие задается, а форматирование не указывается. Надо внимательно смотреть на диалог, оценивая будущий внешний вид ячеек в образце. Слова «Формат не задан» могут привлечь внимание :)
- Форматирование, указанное в диалоге, делает текст нечитабельным. Например, черный текст по коричневой заливке. См. предыдущий пункт.
- Условия, разбивающие значения на диапазоны, пересекаются или наоборот, оставляют «дыры».
Пример: надо выделить отрицательные и неотрицательные числа. Условия <0 и >0 оставляют ноль вне рассмотрения. Второе условие должно было быть «не меньше 0» (то есть ≥0).
Редактирование условий
Выделяем ячейки, в которых нужно изменить условие форматирования и меняем его. Казалось бы, всё очень просто, НО!
Типичная ошибка — выделение не всех ячеек (или редактирование только одной ячейки). В результате, в форматируемом диапазоне ячеек образуется сразу два набора условий. Исправить такие ошибки довольно трудно, так как ячейки могут содержать и другие признаки форматирования, внесенные вручную. Тогда кисть копирования формата нам не поможет. Единственное решение — клавиша F4, но и это может быть достаточно сложно.
Чтобы полностью удалить условное форматирование, можно (предварительно выделив нужные ячейки) зайти в основной диалог и нажать кнопку Удалить... (Delete...). Появится запрос:
После пометки необходимых условий и подтверждения в двух диалогах (кнопкой OK), условия будут удалены.
Примеры использования условного форматирования: Проверка ввода.
Copyright © 1993–2024 Мацкявичюс Д.А. Все права защищены.
Никакая часть сайта не может быть воспроизведена никаким способом без письменного разрешения правообладателя и явной ссылки на данный ресурс.
