Вложенный связанный список в MS 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. Формула проста: =Вложенный_диапазон.

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

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (3 оценок)