gototopgototop

Материалы Excel

Сейчас на сайте

Сейчас 205 гостей онлайн
Вставить в таблицу Excel. Связанный выпадающий список в Excel по алфавиту.

Связанный выпадающий список в Excel по алфавиту.

      Как сделать выпадающий список в Excel по алфавиту, чтобы фамилию выбрать по букве алфавита? Когда много фамилий в выпадающем списке, то удобно сделать в выпадающем списке алфавит, а затем, выбрав нужную букву  - выбрать фамилию. Для этого нужно сделать связанные выпадающие списки Excel.
      Например, на первом листе у нас основная таблица, в которую будем вставлять выпадающий список с фамилиями.
На втором листе книги Excel сделаем список фамилий. Сортируем этот список по алфавиту. Список такой.
Как сделать из большого списка с повторяющимися фамилиями список без повторов для выпадающего списка, смотрите в статье "Создание списка в Excel без повторов".
      Теперь присвоим имя этому списку.  Как присвоить имя диапазону, читайте в статье «Диапазон в Excel» тут.  Мы назвали диапазон – «Фамилии».
      Чтобы менялись автоматически границы диапазона при добавлении или убавлении фамилий, нужно сделать его динамичным. Смотрите в статье «Чтобы размер таблицы Excel менялся автоматически».
Теперь нам нужно создать список букв, на которые начинаются фамилии в нашем списке.
      Как написать алфавит в Excel формулой.
Сначала в столбце С пишем обычный алфавит. Это можно быстро сделать формулой. В ячейке С пишем формулу.
=СИМВОЛ(192+СТРОКА(Z1)-1)
192 – это код буквы А. Копируем формулу вниз по столбцу до буквы Я. Алфавит готов.
     Теперь в соседнем столбце нужно написать только те буквы, с которых начинаются фамилии в нашем списке. Здесь нам поможет  формула массива Excel. В ячейке D2 мы написали такую длинную формулу.
=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$33;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ
(Фамилии;$C$2:$C$33&"*")>0;СТРОКА($C$2:$C$33);"");
СТРОКА(Z1))-СТРОКА($C$1));"")
Это формула массива, п.э. нажимаем не просто «Enter», а сочетание клавиш «Shift» + «Ctrl» + «Enter». Формула будет заключена в фигурные скобки. Протягиваем формулу вниз по столбцу. У нас получилось так.

В формуле стоит диапазон С2:С33 – это диапазон букв всего алфавита в столбце С.
      Теперь в ячейку D1 установим выпадающий список для букв столбца D. В диалоговом окне «Создание имени» в строке «Диапазон» написали такую формулу.
=СМЕЩ(Лист2!$D$2;;;СЧЁТЕСЛИ(Лист2!$D$2:$D$33;"*?"))

Присвоим имя ячейки D1. Мы присвоили имя ячейки D1 – «Алфавит».
      Теперь выделяем диапазон ячеек с фамилиями в столбце А и создаем именованный диапазон. Имя диапазона напишем – «Фамилия».  В диалоговом окне «Создание имени» в строке «Диапазон» пишем такую формулу.
=СМЕЩ(Лист2!$A$1;ПОИСКПОЗ(Алфавит&"*";Фамилии;0);;СЧЁТЕСЛИ(Фамилии;Алфавит&"*"))
 Получилось так.

Теперь осталось создать выпадающий список. Тот, к которому мы шли.
В ячейку Е1 вставляем выпадающий список через функцию «Проверка данных». Как сделать выпадающий список в Excel, смотрите в статье "Выпадающий список в Excel". Диалоговое окно заполнили так.

Всё. Теперь в ячейке D1 выбираем букву, а в ячейке Е1 появится выпадающий список фамилий только на эту букву. Например. Свяханный выпадающий список в Excel по алфавиту.   или   Зависимые выпадающие списки в Excel по алфавиту.
Эти выпадающие списки с буквами и фамилиями можно установить на другом листе книги. Например, в бланке, т.д. 
      В Excel можно сделать очень быстро выпадающий список без дополнительного списка с данными. Об этом способе смотрите в статье «Как сделать выпадающий список в Excel без списка».