Вложенный связанный список в EXCEL

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

Часто при вводе данных в таблицу пользуются Выпадающим (раскрывающимся) списком . Предположим, что в таблицу необходимо вводить фамилии, выбирая их из длинного списка. Если длина списка более 30 элементов, то процесс поиска может быть достаточно утомительным (несколько облегчает ситуацию предварительная сортировка этого списка).

В этом случае имеет смысл создать два выпадающих списка , один для букв алфавита, с которых начинаются фамилии, другой – Связанный список , для перечня фамилий, начинающихся на выбранную букву. Создадим в столбце A отсортированный по алфавиту перечень фамилий, см. файл примера .

Через меню Формулы/ Определенные имена/ Присвоить имя создадим Именованный диапазон Фамилии =Лист1!$A$2:$A$153 .

Для создания Выпадающего списка , содержащего все буквы алфавита, с которых начинаются фамилии в столбце А , создадим алфавит. Алфавит сформируем с помощью формулы

=СИМВОЛ(192+СТРОКА(Z1)-1) где 192 – код русской буквы А.

С помощью формулы массива

=ЕСЛИОШИБКА(ИНДЕКС($C$4:$C$35; НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(Фамилии;$C$4:$C$35&"*")>0; СТРОКА($C$4:$C$35);"");СТРОКА(Z1))-СТРОКА($C$3));"")

выведем в отдельный столбец только те буквы алфавита, с которых начинаются фамилии в перечне.

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

=СМЕЩ($D$4;;;СЧЁТЕСЛИ($D$4:$D$32;"*?"))

Ячейке С1 присвоим имя Буква .

Для создания выпадающего списка , содержащего перечень фамилий, начинающихся на выбранную букву, создадим Именованный диапазон Вложенный_диапазон со следующей формулой:

=СМЕЩ($A$1;ПОИСКПОЗ(Буква&"*"; Фамилии;0);; СЧЁТЕСЛИ(Фамилии;Буква&"*"))

Функция СЧЁТЕСЛИ() подсчитывает фамилии, начинающиеся с выбранной буквы. Результат соответствует высоте диапазона, возвращаемой функцией СМЕЩ() . Функция ПОИСКПОЗ() , принимающая в качестве аргумента [искомое_значение] подстановочные знаки , возвращает позицию первой встретившейся фамилии, начинающуюся с выбранной буквы. Результат соответствует смещению левой верхней ячейки диапазона относительно ячейки A1 .

В Диспетчере имен должны отображать 3 имени:

Наконец, с помощью Проверки данных создадим в ячейке D1 связанный список , содержащий фамилии, начинающиеся с буквы из ячейки С1 . Формула проста: =Вложенный_диапазон .

В итоге, мы должны получить работающий выпадающий список в ячейке D 1 . Выбрав в ячейке С1 , например, букву « В» , выпадающий список в ячейке D 1 будет содержать фамилии из столбца А , но только те, которые начинаются с буквы « В» .