Трюк №22. Как в Excel включить и выключить условное форматирование и проверку данных при помощи флажков

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

Обычно, чтобы разрешить пользователям вводить данные, которые могли бы быть расценены как неправильные, из ячеек удаляется условное форматирование или проверка данных. Однако есть более простой путь: можно совместить простой флажок с панели инструментов Формы (Forms) с проверкой данных.

Для этого примера мы применим условное форматирование к диапазону ячеек, чтобы любые данные, встречающиеся более одного раза, выделялись для упрощения идентификации. Предположим, что в таблице данные расположены в диапазоне $А$1:$Н$100. Условное форматирование этого диапазона данных для упрощения идентификации повторяющихся данных требует выполнить несколько шагов.

Выделите ячейку К1 и присвойте ей имя CheckBoxLink, введя его в поле имени слева от строки формул и нажав клавишу Enter. Если панель инструментов Формы (Forms) не видна, правой кнопкой мыши щелкните любую панель инструментов и выберите команду Формы (Forms), а затем щелкните значок флажка. Теперь щелкните лист где-либо за пределами диапазона $А$1:$Н$100, чтобы добавить флажок.

Правой кнопкой мыши щелкните флажок и в контекстном меню выберите команду Формат объекта > Элемент управления (Format Control > Control). В поле Связь с ячейкой (Cell Link) введите имя CheckBoxLink и щелкните кнопку ОК. Выберите ячейку А1, а затем перетащите указатель, чтобы выделить диапазон ячеек до Н100.

Важно, чтобы ячейка А1 была при выделении активной. Выберите команду Формат > Условное форматирование (Format → Conditional Formatting) и в поле с параметром Значение (Value Is) выберите вариант Формула (Formula Is). В поле справа введите следующую формулу (рис. 2.7): =AND(COUNTIF($A$l:$H$100;Al)>1;CheckboxL1nk), в русской версии Excel: =И(СЧЕТЕСЛИ($A$l:$H$100;Al)>1;CheckboxLink). Щелкните кнопку Формат (Format), перейдите на вкладку Вид (Patterns) и выберите цвет, которым будут выделяться дублирующиеся данные. Щелкните кнопку ОК, затем еще раз щелкните кнопку ОК.

Рис. 2.7. Диалоговое окно с формулой условного форматирования диапазона и выделения повторяющихся данных

Когда флажок, который вы добавили на лист, будет установлен, связь в ячейке К1 (CheckBoxLink) возвратит значение ИСТИНА (TRUE), и все дублирующиеся значения в диапазоне $А$1:$А$100 будут подсвечены. Когда вы сбросите флажок, связь в ячейке возвратит значение ЛОЖЬ (FALSE) и дубликаты подсвечены не будут.

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

Этот способ работает благодаря функции И (AND). Она требует, чтобы произошло два события: функция СЧЁТЕСЛИ($А$1:$Н$100;А1)>1 (COUNTIF($A$1:$H$100;A1)>1) должна вернуть значение ИСТИНА (TRUE), и связь с флажком в ячейке (CheckBoxLink) также должна вернуть значение ИСТИНА (TRUE). Другими словами, чтобы функция И (AND) вернула значение ИСТИНА (TRUE), оба условия должны быть истинными.