Трюк №37. Как в Excel автоматически добавлять данные в список проверки

Возможность проверки в Excel — это замечательная вещь, но она не умеет выполнять одно важное действие (без следующего трюка): автоматически добавлять новую запись в список, который используется в качестве источника для списка проверки.

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

Пусть есть список имен в диапазоне А1:А10 (рис. 2.24).

Рис. 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

Рис. 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. Превращение списка в динамический список

Рис. 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

Рис. 2.27. Список с проверкой, добавленный в ячейку D1

Правой кнопкой мыши щелкните ярлычок листа и в контекстном меню выберите команду Исходный текст (View Code). Введите код, приведенный в листинге 2.5.

1
2
3
4
5
6
7
8
// Листинг 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

Рис. 2.28. Список после добавления новой записи в ячейку D1

Если вы хотите добавить больше 10 новых имен в список, скопируйте формулу ниже строки 20.

Top