Выпадающий список с наполнением
Задача: создать в ячейке выпадающий список для удобного ввода информации. Варианты для списка должны браться из заданного динамического диапазона, т.е. если завтра в него внесут изменения - например, удалят ненужные элементы или допишут еще несколько новых - они должны автоматически отразиться в выпадающем списке:
Способ 1. Если у вас Excel 2007 или новее
Простой и удобный способ почти без формул. Использует новую возможность последних версий Microsoft Excel начиная с 2007 версии - "Умные Таблицы". Суть его в том, что любой диапазон можно выделить и отформатировать как Таблицу. Тогда он превращается, упрощенно говоря, в "резиновый", то есть сам начинает отслеживать изменения своих размеров, автоматически растягиваясь-сжимаясь при добавлении-удалении в него данных.
Выделите диапазон вариантов для выпадающего списка (A1:A5 в нашем примере выше) и на Главной (Home) вкладке нажмите кнопку Форматировать как таблицу (Home - Format as Table). Дизайн можно выбрать любой - это роли не играет:
Обратите внимание на то, что таблица должна иметь строку заголовка (в нашем случае это А1 со словом Сотрудники). Первая ячейка играет роль "шапки" и содержит название столбца. На появившейся после превращения в Таблицу вкладке Конструктор (Design) можно изменить стандартное имя таблицы на свое (без пробелов!). По этому имени мы сможем потом адресоваться к таблице на любом листе этой книги:
Теперь выделите ячейки где вы хотите создать выпадающие списки (в нашем примере выше - это D2) и выберите в старых версиях Excel в меню Данные - Проверка (Data - Validation), а в новых нажмите кнопку Проверка данных (Data Validation) на вкладке Данные (Data). В открывшемся окне на вкладке Параметры (Settings) выберите вариант Список (List) и введите в поле Источник (Source) вот такую формулу:
=ДВССЫЛ("Таблица1[Сотрудники]")
=INDIRECT("Таблица1[Сотрудники]")
Смысл этой формулы прост. Выражение Таблица1[Сотрудники] - это ссылка на столбец с данными для списка из нашей умной таблицы. Но проблема в том, что Excel почему-то не хочет понимать прямых ссылок в поле Источник (Source), т.е. нельзя написать в поле Источник выражение вида =Таблица1[Сотрудники]. Поэтому мы идем на тактическую хитрость - вводим ссылку как текст (в кавычках) и используем функцию ДВССЫЛ (INDIRECT), которая преобразовывает текстовую ссылку в настоящую, живую.
Осталось только нажать на ОК. Если теперь дописать к нашей таблице новые элементы, то они будут автоматически в нее включены, а значит - добавятся к нашему выпадающему списку. С удалением - то же самое.
Если вам лень возиться с вводом формулы ДВССЫЛ, то можно чуть упростить процесс. После создания умной таблицы просто выделите мышью диапазон с элементами для выпадающего списка (A2:A5) и введите в поле адреса имя для этого диапазона (без пробелов), например Стажеры, и нажмите на Enter:
Фактически, этим мы создаем именованный динамический диапазон, который ссылается на данные из нашей умной таблицы. Теперь имя этого диапазона можно ввести в окне создания выпадающего списка в поле Источник (Source):
Способ 2. Если у вас Excel 2003 или старше
В старых версиях Excel до 2007 года не было замечательных "умных таблиц", поэтому придется их имитировать своими силами. Это можно сделать с помощью именованного диапазона и функции СМЕЩ (OFFSET), которая умеет выдавать ссылку на динамический диапазон заданного размера.
Откройте меню Вставка - Имя - Присвоить (Insert - Name - Define) или нажмите Ctrl+F3. В открывшемся окне нажмите кнопку Добавить (New), введите имя диапазона (любое, но без пробелов и начинающееся с буквы, например - Люди) и в поле Ссылка (Reference) введите вот такую формулу:
=СМЕЩ(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) вот такую формулу:
=Люди
После нажатия на ОК ваш динамический список в выделенных ячейках готов к работе.
Ссылки по теме
- 4 способа создания выпадающих списков в ячейках листа Excel
- Связанные выпадающие списки
- Быстрое создание выпадающих списков с помощью надстройки PLEX
А я все никак не мог понять как победить проблему с невозможностью ссылаться списку на "умную" таблицу
а вот если список Сотрудников очень большой, можно ли в выподающем списке прозводить поиск сотрудников?
А вот если есть два одинаковых имени, то они оба будут в списке.
Простого способа выводить только уникальные, без повторений, имена - нет?
Использовал извлечение уникальных значений, но динамический список не хочет формироваться - то есть, список конечных значений, отформатированный как таблица, не расширяется самостоятельно. Подскажите, пожалуйста, я что-то не так делаю?
PS: Это если Вы выбор уникальных значений делали третьим способом (через формулы ВПР)
Делаю по второму методу... ничего не получается...
в итоге после многих экспериментов понял, что почему-то выпадающий список можно создать только в столбце "D"! в других столбцах вылетает ошибка "При вычислении "Источник" возникает ошибка. Продолжить?" причем если создавать в ячейке D3 то все отображается корректно, а если сместиться например вниз, то уже не полный список отображается.
Как быть.
Мне бы надо так - список из которого ведется выбор на одном листе, а выпадающий список на другом листе одной книги.
у меня есть таблица которая содержит:
1. Дату
2. Документ (поступление, списание, перемещение)
3. наименование материала
4. Склад на котором числится материал
5. Наименование материала (то же что и в п.3)
6. склад с которого списывается материал
7. количество
Мне необходимо создать выпадающий список материалов (это я уже сделала), и чтоб при выборе материала в таблице формировалась дата поставки, от кого поставлено, дата списание с какого склада списано, помогите пожалуйста.
Создайте тему на форуме и приложите вашу "не очень умную таблицу" - посмотрим, поможем
Не перестаю восхищаться данным ресурсом))))
Подскажите, пожалуйста, а возможно ли объединить данный приём с одной опцией способа №3 из , а именно чтобы можно было выводить порядковый номера элемента, либо выводилось его название???
В качестве решения в способе №3 сказано, что можно дополнительно использовать функцию ИНДЕКС (INDEX), которая умеет выводить содержимое нужной по счету ячейки из диапазона. Но что-то как-то не получилось применить такой способ к выпадающему списоку с наполнением
Подскажите, пожалуйста: в первом столбике список объектов (рэндомный, с повторами), во втором — числа (положительные и отрицательные). Есть ли какой-то способ в отдельной ячейке создать выпадающий список имеющихся объектов (без повторов), чтобы в соседней отображалась сумма чисел, стоящих в соседнем от объекта столбике?
Например:
Вот ссылка на открытую тему в форуме:
А вот ссылка на сам файл
первый столбец ( "А" ) скрыт, при добавлении новых объектов нужно будет скопировать формулу ниже, а также скопировать формулы вниз по столбцу "D", плюс ко всему при добавлении новых объектов, нужно будет корректировать размер "таблицы1" (протягивать вниз за правый нижний угол). И еще нужно будет поправить нижний диапазон (столбец с объектами) и диапазон_суммирования в последней формуле (можно поправить в первой и скопировать ее вниз)
Подскажите, есть ли возможность делать выпадающий список на основе "умной таблицы" состоящей из двух столбцов? Например, Наименование и Тип, в которых перечислены, ну скажем наименования контрагентов, а в столбце Тип напротив каждого из них обозначено кем этот контрагент является поставщик либо покупатель. Контрагенты не повторяются. В выборку в выпадающий список должны попадать наименования контрагентов с условием, что он является скажем поставщиком.
Скажите а возможно чтобы раскрывающийся список автоматический (по мере наполнения) сортировался от минимума к максимуму или в алфавитном порядке?
Ну, или делать все (добавление и сортировку) макросом.
список можно настроить так, чтобы пользователь имел возможность вводить в ячейку только значения из списка (такие настройки используются по умолчанию) или использовал список, но мог так же ввести любое значение не из списка.
:!:А как, собственно, настроить последнее - "использовал список, но мог так же ввести любое значение не из списка" ???
Формулу взял такую: =ДВССЫЛ("Регионы["&$T31&"]";).( Регионы - это название таблицы).
В каждой колонке разное количество наименований, и в выпадающем списке отображается "КУЧА" пустых строк.
Как сделать, чтобы отображались только заполненные?
Если можно, то просьба подкорректировать мою формулу.
У меня задача, чтобы 2 поля завили от 3его...
то есть, в 1ом выбрал страну, от этого должны показываться только регионы этой страны(2столбец) и в 3ем показываются магазины в этой стране.
Подскажите, как можно сделать следующий пример: Есть таблица из двух столбцов. В выпадающем списке должны быть показаны оба столбца, а после выбора нужной строчки из списка, отображалось только значение первого столбца. Спасибо.
При создании выпадающего списка на основе "умной таблицы" нет никаких проблем, вот только при переименовании (изменении) данных "умной таблицы" в списке данные меняются (что логично), а вот в ячейках, где они были уже введены по-старому изменений не вносится. Я понимаю, почему так происходит, но вот решения данной проблемы пока не нашел, Прошу Вашей компетентной помощи...
Не знаю что прямо произошло.
Скопировать ячейки с работающими выпадающими списками из другой таблицы, тоже перестает работать. ((
Подскажите пожалуйста как в выпадающем списке увеличить размер окна прокрутки.
Благодарю.
в итоге список не работает,что делать?
Есть Умная таблица, с тремя столбцами: КодР; ВидР и Стоимость.
Нужно чтобы выпадал список из ВидР, а при выборе отображалось значение КодР.
cloud.mail.ru/public/4A8u/gutKiWgaq
Делал выпадающий список с помощью элемента ActiveX.
cloud.mail.ru/public/4SqY/j7bovankx
Я сделал 3 строки, а мне нужно примерно 500 таких строк. Как можно сделать это быстро и просто, а не копировать по одной строке и перепривязывать выпадающие списки к нужным ячейкам через свойства?
Достаточно в качестве источника в окне проверка вводимых значений вписать формулу
=ДВССЫЛ("$A$"&2&":$A$"&ПОИСКПОЗ(1E+306;A:A;1))