Перевірка даних істотно зменшує ймовірність того, що користувач випадково введе неправильні дані. Іноді, однак, виникає бажання по-іншому спростити введення даних, які б позначалися як неправильні за допомогою умовного форматування і були б заблоковані засобами перевірки.
Зазвичай, щоб дозволити користувачам вводити дані, які могли б бути розцінені як неправильні, з осередків видаляється умовне форматування або перевірка даних. Однак є більш простий шлях: можна поєднати простий прапорець з панелі інструментів Форми (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), обидва умови повинні бути істинними.