Закрыть ... [X]

Как в excel сделать список с выбором

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

dynamic-dropdown.gif 

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

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

Выделите диапазон вариантов для выпадающего списка (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) вот такую формулу:

=Люди

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

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

 



Поделись с друзьями



Рекомендуем посмотреть ещё:




Выпадающий список в ячейке листа Белокрылка как с ней бороться в домашних условиях

Как в excel сделать список с выбором Как в excel сделать список с выбором Как в excel сделать список с выбором Как в excel сделать список с выбором Как в excel сделать список с выбором Как в excel сделать список с выбором Как в excel сделать список с выбором

ШОКИРУЮЩИЕ НОВОСТИ