Можливість перевірки в Excel - це чудова річ, але вона не вміє виконувати одну важливу дію (без наступного трюку): автоматично додавати новий запис до списку, який використовується в якості джерела для списку перевірки.
Якщо ви вже працювали з перевіркою, то знаєте, що це дуже майстерна можливість. Ймовірно, найбільш вражаючим є її вміння додавати в будь-яку клітинку список, в якому користувач може вибирати значення. Було б здорово, якби при введенні нового імені в перевірену осередок Excel автоматично додавав її в список. Це можливо завдяки наступному трюку.
Нехай є список імен в діапазоні А1: А10 (рис. 2.24).
Мал. 2.24. Робоча книга, налаштована для списку перевірки
Ці імена представляють співробітників компанії. Не так вже рідко в подібний список додаються імена нових співробітників, але зараз єдиний спосіб зробити це - додати нові імена в кінці списку, а потім вибирати їх в списку в перевіреної осередку. Щоб обійти це обмеження, зробіть наступне. В осередку А11 введіть наступну формулу і скопіюйте її вниз до рядка 20 (рис. 2.25) - зверніть увагу на відносну посилання на осередок А10: = IF (OR ($ D $ 1 = ""; COUNTIF ($ A $ 1: A10; $ D $ l)); "x"; $ D $ l). в російській версії Excel: = ЕСЛИ (ИЛИ ($ D $ 1 = ""; СЧЁТЕСЛІ ($ А $ 1: А10; $ D $ 1)); "х"; $ D $ 1).
Мал. 2.25. Список з формулою, доданої в рядки А11: А20
Тепер виберіть команду Формули> Присвоїти Ім'я (Formulas> Define Name) і в поле Ім'я (Names in workbook) введіть MyNames. В поле Формула (Refers to) введіть наступну формулу (рис. 2.26), а потім натисніть кнопку Додати (Add), потім клацніть кнопку ОК: = OFFSET (Sheet1! $ А $ 1; 0; 0; COUNTIF (Sheet1! $ А: $ А ; "<>х "); 1). в російській версії Excel: = CMEЩ (Sheet1! $ А $ 1; 0; 0; СЧЕТЕСЛІ (Sheet1! $ А: $ А;"<>х "); 1).
Мал. 2.26. Перетворення списку в динамічний список
Виділіть клітинку D1 і виберіть команду Дані> Перевірка (Data> Validation). В поле Тип даних (Allow) виберіть пункт Список (List), а в полі Джерело (Source) введіть = MyNames; упевніться, що увімкнено Архів допустимих значень (In-Cell). Перейдіть на вкладку Повідомлення про помилку (Error Alert) і скиньте прапорець Виводити повідомлення про помилку (Show error alert after invalid data is entered). Натисніть кнопку ОК. Ви побачите результат, як на рис. 2.27.
Мал. 2.27. Список з перевіркою, для якого створено осередок D1
Правою кнопкою миші ярличок аркуша і в контекстному меню виберіть команду Оригінальний текст (View Code). Введіть код, наведений у лістингу 2.5.
// Лістинг 2.5 Private Sub Worksheet_Calculate () On Error Resume Next Application.EnableEvents = False Range ( "MyNames") = Range ( "MyNames"). Value Application.EnableEvents = True On Error GoTo 0 End Sub
Щоб повернутися назад в Excel і зберегти робочу книгу, закрийте вікно. Тепер виділіть осередок D1, введіть будь-яке ім'я, що не входить в список, і натисніть клавішу Enter. Знову виділіть осередок D1 і погляньте на список. Тепер в списку має бути присутнім нове ім'я (рис. 2.28).
Мал. 2.28. Список після додавання нового запису в комірку D1
Якщо ви хочете додати більше 10 нових імен в список, скопіюйте формулу нижче рядка 20.