Выпадающий список с наполнением

220277 20.01.2013 Скачать пример

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

dynamic-dropdown.gif 

Способ 1. Если у вас Excel 2007 или новее

Простой и удобный способ почти без формул. Использует новую возможность последних версий Microsoft Excel начиная с 2007 версии - "Умные Таблицы". Суть его в том, что любой диапазон можно выделить и отформатировать как Таблицу. Тогда он превращается, упрощенно говоря, в "резиновый", то есть сам начинает отслеживать изменения своих размеров, автоматически растягиваясь-сжимаясь при добавлении-удалении в него данных.

Выделите диапазон вариантов для выпадающего списка (A1:A5 в нашем примере выше) и на Главной (Home) вкладке нажмите кнопку Форматировать как таблицу (Home - Format as Table). Дизайн можно выбрать любой - это роли не играет:

zebra2.png

Обратите внимание на то, что таблица должна иметь строку заголовка (в нашем случае это А1 со словом Сотрудники). Первая ячейка играет роль "шапки" и содержит название столбца. На появившейся после превращения в Таблицу вкладке Конструктор (Design) можно изменить стандартное имя таблицы на свое (без пробелов!). По этому имени мы сможем потом адресоваться к таблице на любом листе этой книги:

dynamic-dropdown1.png

Теперь выделите ячейки где вы хотите создать выпадающие списки (в нашем примере выше - это D2) и выберите в старых версиях Excel в меню Данные - Проверка (Data - Validation), а в новых нажмите кнопку Проверка данных (Data Validation) на вкладке Данные (Data). В открывшемся окне на вкладке Параметры (Settings) выберите вариант Список (List) и введите в поле Источник (Source) вот такую формулу:

dynamic-dropdown2.png

=ДВССЫЛ("Таблица1[Сотрудники]")

=INDIRECT("Таблица1[Сотрудники]")

Смысл этой формулы прост. Выражение Таблица1[Сотрудники] - это ссылка на столбец с данными для списка из нашей умной таблицы. Но проблема в том, что Excel почему-то не хочет понимать прямых ссылок в поле Источник (Source), т.е. нельзя написать в поле Источник выражение вида =Таблица1[Сотрудники]. Поэтому мы идем на тактическую хитрость - вводим ссылку как текст (в кавычках) и используем функцию ДВССЫЛ (INDIRECT), которая преобразовывает текстовую ссылку в настоящую, живую.

Осталось только нажать на ОК. Если теперь дописать к нашей таблице новые элементы, то они будут автоматически в нее включены, а значит - добавятся к нашему выпадающему списку. С удалением - то же самое.

Если вам лень возиться с вводом формулы ДВССЫЛ, то можно чуть упростить процесс. После создания умной таблицы просто выделите мышью диапазон с элементами для выпадающего списка (A2:A5) и введите в поле адреса имя для этого диапазона (без пробелов), например Стажеры, и нажмите на Enter:

dynamic-dropdown3.png

Фактически, этим мы создаем именованный динамический диапазон, который ссылается на данные из нашей умной таблицы. Теперь имя этого диапазона можно ввести в окне создания выпадающего списка в поле Источник (Source):

dynamic-dropdown4.png

Способ 2. Если у вас Excel 2003 или старше

В старых версиях Excel до 2007 года не было замечательных "умных таблиц", поэтому придется их имитировать своими силами. Это можно сделать с помощью именованного диапазона и функции СМЕЩ (OFFSET), которая умеет выдавать ссылку на динамический диапазон заданного размера.

Откройте меню Вставка - Имя - Присвоить (Insert - Name - Define) или нажмите Ctrl+F3. В открывшемся окне нажмите кнопку Добавить (New), введите имя диапазона (любое, но без пробелов и начинающееся с буквы, например - Люди) и в поле Ссылка (Reference) введите вот такую формулу:

dynamic-dropdown5.png

=СМЕЩ(A2;0;0;СЧЁТЗ(A2:A100);1)

=OFFSET(A2;0;0;COUNTA(A2:A100);1)

Функция СЧЁТЗ (COUNTA) подсчитывает количество непустых ячеек в столбце с фамилиями, т.е. количество строк в диапазоне для выпадающего списка. Функция СМЕЩ (OFFSET) формирует ссылку на диапазон с нужными нам именами и использует следующие аргументы:

  • A2 - начальная ячейка
  • 0 - сдвиг начальной ячейки по вертикали вниз на заданное количество строк
  • 0 - сдвиг начальной ячейки по горизонтали вправо на заданное количество столбцов
  • СЧЁТЗ(A2:A100) - размер получаемого на выходе диапазона по вертикали, т.е. столько строк, сколько у нас занятых ячеек в списке
  • 1 - размер получаемого на выходе диапазона по горизонтали, т.е. один столбец

Теперь выделите ячейки, где вы хотите создать выпадающие списки, и выберите в старых версиях Excel в меню Данные - Проверка (Data - Validation). В открывшемся окне на вкладке Параметры (Settings) выберите вариант Список (List) и введите в поле Источник (Source) вот такую формулу:

=Люди

После нажатия на ОК ваш динамический список в выделенных ячейках готов к работе.

Ссылки по теме

 


08.02.2013 03:48:48
Классно!
А я все никак не мог понять как победить проблему с невозможностью ссылаться списку на "умную" таблицу
08.02.2013 11:44:45
Класс! Возьму себе на вооружение :)
08.02.2013 11:49:00
Николай, а вы не подскажите с помощью какой программы вы создаете анимации ваших передвижений по таблице? Пробовал  некоторые программы, но там заметны потери качества.
08.02.2013 13:03:11
Пишу в Camtasia Studio, затем конвертирую в анимированный gif. Чтобы не было сильной потери качества можно с настройками пошаманить.
08.02.2013 13:04:20
Спасибо, будем шаманить!
08.02.2013 13:24:26
Мне больше нравится последний способ с формулой СМЕЩ, причем вписывать ее прямо в источник данных для списка, минуя этап присвоения имени диапазону. Спасибо за статью.
08.02.2013 13:49:06
Да, так тоже неплохо, спасибо за уточнение :)
14.02.2013 05:34:58
Николай, подскажите, а можно сотрудникам присвоить категорию (например, стажеры, специалисты, гл.специалисты и т.д.), а затем чтобы при выборе из выпадающего списка категории сотрудников выпадал список сотрудников данной конкретной категории?
14.02.2013 10:13:33
Если у вас категория - это столбец в списке сотрудников, то для этого можно просто использовать обычный фильтр (Данные - Фильтр). Если хотите это выбирать именно не в фильтре, а в выпадающем списке в ячейке, то придется шаманить формулами - см. http://www.planetaexcel.ru/techniques/2/81/
14.02.2013 11:22:35
фильтр немного не то... конструкция у меня немного сложнее... спасибо за ссылку, попробую разобраться.
17.02.2013 13:12:07
Очень классно, спасибо!
Спасибо!
а вот если список Сотрудников очень большой, можно ли в выподающем списке прозводить поиск сотрудников?
20.02.2013 20:03:14
Только если делать список на ActiveX - см. http://planetaexcel.ru/techniques/1/40/
02.07.2013 14:18:41
А можно сделать, чтобы выпадающий список фильтровался по возрастанию?
03.07.2013 09:12:11
Наталия, вы имели ввиду "сортировался"? Только макросом, который будет отслеживать изменение длины списка и запускать сортировку программно.
OBR
11.07.2013 10:11:37
Полезная статья.
А вот если есть два одинаковых имени, то они оба будут в списке.
Простого способа выводить только уникальные, без повторений, имена - нет?  
14.07.2014 18:24:31
Николай, добрый день!

Использовал извлечение уникальных значений, но динамический список не хочет формироваться - то есть, список конечных значений, отформатированный как таблица, не расширяется самостоятельно. Подскажите, пожалуйста, я что-то не так делаю?
15.07.2014 17:04:40
Андрей, трудно сказать не видя файла. Вопрос еще в том, как именно вы извлекли уникальные. Киньте файлик на почту или создайте тему на форуме - поможем ;)
23.07.2014 13:10:17
Николай, приветствую! 16 июля отправил Вам по почте файл, к сожалению, ответа не получил - посмотрите, пожалуйста, возможно оно попало в спам.
05.09.2014 08:41:37
Доброго времени суток Андрей! Я кажется понял в чем Ваша проблема, причина скорее всего в том, что в столбце с выбранными уникальными данными во всех ячейках стоят формулы, а таблица расширяется автоматически (автозаменой) только если вы в ПУСТУЮ ячейку ниже добавляете данные, можно конечно сразу прописать диапазон таблицы вниз, но тогда в выпадающем списке будут пустые строчки, пока ничего лучше ручной правки таблицы я не нашел, т.е. при добавлении автоматически нового(ых) уникального(ых) значения(й), цеплять нижний правый угол таблицы и тянуть вниз до пустых строк.
PS: Это если Вы выбор уникальных значений делали третьим способом (через формулы ВПР)
20.09.2013 13:20:14
У меня 2003 эксель.
Делаю по второму методу... ничего не получается...
в итоге после многих экспериментов понял, что почему-то выпадающий список можно создать только в столбце "D"!  в других столбцах вылетает ошибка "При вычислении "Источник" возникает ошибка. Продолжить?" причем если создавать в ячейке D3 то все отображается корректно, а если сместиться например вниз, то уже не полный список отображается.
Как быть.
Мне бы надо так - список из которого ведется выбор на одном листе, а выпадающий список на другом листе одной книги.
25.09.2013 11:47:21
Помогите пожалуйста.
у меня есть таблица которая содержит:
1. Дату
2. Документ (поступление, списание, перемещение)
3. наименование материала
4. Склад на котором числится материал
5. Наименование материала (то же что и в п.3)
6. склад с которого списывается материал
7. количество
Мне необходимо создать выпадающий список материалов (это я уже сделала), и чтоб при выборе материала в таблице формировалась дата поставки, от кого поставлено, дата списание с какого склада списано, помогите пожалуйста.
07.12.2013 20:18:46
Подскажите пожалуйста, как можно убрать(скрыть) в данном примере список А1:A5 если на выходе мне нужен только выпадающий список D2? Спасибо :)
18.12.2013 07:52:09
Можно просто скрыть столбец А (правой кнопкой мыши по букве столбца - Скрыть).
19.12.2013 17:25:09
А что делать если таблица не очень умная и не хочет изменяться в размерах при добавлении в нее новых данных? делала как написано, но на этом моменте заминка вышла..
25.12.2013 19:37:16
Ольга, не видя вашего файла - как ответить?
Создайте тему на форуме и приложите вашу "не очень умную таблицу" - посмотрим, поможем ;)
05.09.2014 09:09:34
Возможно отключены параметры автозамены, в окне настроек вкладка "автоформат при вводе" нужно чтобы стояла галочка на "включать в таблицу новые строки и столбцы"
01.02.2014 14:55:25
Николай, подскажите, пожалуйста, на данном примере, если в именах сотрудников придётся несколько раз написать одно и то же имя, например 2 раза подряд ввести "Данила",  как сделать так, чтобы в выпадающем списке они не дублировались?  
01.02.2014 15:13:01
Извините, не сразу увидел, в комментариях уже есть подобный вопрос..
Добрый день.
Не перестаю восхищаться данным ресурсом))))
Подскажите, пожалуйста, а возможно ли объединить данный приём с одной опцией способа №3 из http://www.planetaexcel.ru/techniques/1/40/, а именно чтобы можно было выводить порядковый номера элемента, либо выводилось его название???
В качестве решения в способе №3 сказано, что можно дополнительно использовать функцию ИНДЕКС (INDEX), которая умеет выводить содержимое нужной по счету ячейки из диапазона. Но что-то как-то не получилось применить такой способ к выпадающему списоку с наполнением
23.03.2014 18:33:15
Николай, подскажите пожалуйста как сделать что б автоматически изменялось значение ячейки D2 (уже выбранное имя Иван) если в самом списке я его затем переименую в Ваня?. т.е. что то аналогичное если применить для ячейки D2 формулу =А2 .  Спасибо.
04.04.2014 15:11:55
Добрый день. А подскажите возможно ли сделать, чтобы именно в выпадающем списке были картинки?
19.04.2014 10:49:15
Не думаю :)
17.05.2014 19:54:52
Добрый день, Николай, подскажите пожалуйста, как сделать чтоб при вводе первых букв появлялись названия в выпадающем списке, список большой и найти в нем нужное сложно.Спасибо!
15.08.2014 10:06:18
Здравствуйте, Николай.
Подскажите, пожалуйста: в первом столбике список объектов (рэндомный, с повторами), во втором — числа (положительные и отрицательные). Есть ли какой-то способ в отдельной ячейке создать выпадающий список имеющихся объектов (без повторов), чтобы в соседней отображалась сумма чисел, стоящих в соседнем от объекта столбике?

Например:
Объект 110 000,00р.
Объект 220 000,00р.
Объект 330 000,00р.
Объект 2-10 000,00р.
Объект 1-5 000,00р.
Объект 2-5 000,00р.
Объект 3-15 000,00р.
Объект 2-2 000,00р.
выпадающий списоксумма
То есть, выбирая в выпадающем списке "Объект 1", мы увидим автосумму "0", "Объект 2" — "3000", и "Объект 3" — "15000"
05.09.2014 09:45:28
Вообще-то по объекту 1 сумма 5000,00 р, но не суть важно.
Вот ссылка на открытую тему в форуме:

Тема на форуме

А вот ссылка на сам файл

Вариант решения

первый столбец ( "А" ) скрыт, при добавлении новых объектов нужно будет скопировать формулу ниже, а также скопировать формулы вниз по столбцу "D", плюс ко всему при добавлении новых объектов, нужно будет корректировать размер "таблицы1" (протягивать вниз за правый нижний угол). И еще нужно будет поправить нижний диапазон (столбец с объектами) и диапазон_суммирования в последней формуле (можно поправить в первой и скопировать ее вниз)
23.09.2014 18:29:02
Здравствуйте, Николай.
Подскажите, есть ли возможность делать выпадающий список на основе "умной таблицы" состоящей из двух столбцов? Например, Наименование и Тип, в которых перечислены, ну скажем наименования контрагентов, а в столбце Тип напротив каждого из них обозначено кем этот контрагент является поставщик либо покупатель. Контрагенты не повторяются. В выборку в выпадающий список должны попадать наименования контрагентов с условием, что он является скажем поставщиком.
15.10.2014 13:42:16
Добрый день, Николай.
Скажите а возможно чтобы раскрывающийся список автоматический (по мере наполнения) сортировался от минимума к максимуму или в алфавитном порядке?
14.02.2015 11:38:13
Можно использовать для создания списка динамический диапазон, созданный с помощью СМЕЩ или ИНДЕКС. А внутри диапазона применить сортировку формулой.
Ну, или делать все (добавление и сортировку) макросом.
11.02.2015 08:55:22
Доброе утро, Николай. Подскажите, пожалуйста, возможно ли увеличить шрифт отображения в выпадающем списке (воспользовалась 1 способом)?Заранее спасибо.
14.02.2015 11:36:25
Точно нет :(
22.05.2015 15:47:54
День добрый.
список можно настроить так, чтобы пользователь имел возможность вводить в ячейку только значения из списка (такие настройки используются по умолчанию) или использовал список, но мог так же ввести любое значение не из списка.

:!:А как, собственно, настроить последнее - "использовал список, но мог так же ввести любое значение не из списка" ???:?:
16.10.2015 12:44:29
Сделал выпадающий список, всё работает. спасибо!
Формулу взял такую: =ДВССЫЛ("Регионы["&$T31&"]";).( Регионы - это название таблицы).
В каждой колонке разное количество наименований, и в выпадающем списке отображается "КУЧА" пустых строк.
Как сделать, чтобы отображались только заполненные?
Если можно, то просьба подкорректировать мою формулу.
У меня задача, чтобы 2 поля завили от 3его...
то есть, в 1ом выбрал страну, от этого должны показываться только регионы этой страны(2столбец) и в 3ем показываются магазины в этой стране.
30.10.2015 01:07:26
Николай, спасибо за пример.
Подскажите, как можно сделать следующий пример: Есть таблица из двух столбцов. В выпадающем списке должны быть показаны оба столбца, а после выбора нужной строчки из списка, отображалось только значение первого столбца. Спасибо.
07.12.2015 14:22:44
Спасибо за ваш сайт!!!
28.01.2016 19:33:41
Николай, все очень классно, но есть одна проблема и не хочется решать ее макросами и программированием.
При создании выпадающего списка на основе "умной таблицы" нет никаких проблем, вот только при переименовании (изменении) данных "умной таблицы" в списке данные меняются (что логично), а вот в ячейках, где они были уже введены по-старому изменений не вносится. Я понимаю, почему так происходит, но вот решения данной проблемы пока не нашел, Прошу Вашей компетентной помощи...
03.03.2016 13:06:40
А у меня в одной Книге упорно не появляется кнопка выпадающего списке.
Не знаю что прямо произошло.
Скопировать ячейки с работающими выпадающими списками из другой таблицы, тоже перестает работать. ((
13.07.2016 13:41:45
Николай, Здравствуйте.

Подскажите  пожалуйста  как в выпадающем списке увеличить размер окна прокрутки.

Благодарю.
26.07.2016 11:20:08
у меня при создании выпадающего списка формулу =СМЕЩ(A2;0;0;СЧЁТЗ(A2:A100);1) программа сама изменяет
в итоге список не работает,что делать?
17.11.2016 19:53:50
Подскажите пожалуйста.
Есть Умная таблица, с тремя столбцами: КодР; ВидР и Стоимость.
Нужно чтобы выпадал список из ВидР, а при выборе отображалось значение КодР.