Создание раскрывающегося. Как сделать сложный выпадающий список в excel
При заполнении ячеек данными, часто необходимо ограничить возможность ввода определенным списком значений. Например, имеется ячейка, куда пользователь должен внести название департамента, указав где он работает. Логично, предварительно создать список департаментов организации и позволить пользователю лишь выбирать значения из этого списка. Этот подход поможет ускорить процесс ввода и уменьшить количество опечаток.
Выпадающий список можно создать с помощью
В этой статье создадим Выпадающий список с помощью () с типом данных Список .
Выпадающий список можно сформировать по разному.
А. Простейший выпадающий список - ввод элементов списка непосредственно в поле Источник
Предположим, в ячейке B 1 необходимо создать выпадающий список для ввода единиц измерений. Выделим ячейку B 1 и вызовем Проверку данных.
Если в поле Источник указать через точку с запятой единицы измерения шт;кг;кв.м;куб.м , то выбор будет ограничен этими четырьмя значениями.
Теперь смотрим, что получилось. Выделим ячейку B 1 . При выделении ячейки справа от ячейки появляется квадратная кнопка со стрелкой для выбора элементов из выпадающего списка .
Недостатки
этого подхода: элементы списка легко потерять (например, удалив строку или столбец, содержащие ячейку B
1
); не удобно вводить большое количество элементов. Подход годится для маленьких (3-5 значений) неизменных списков.
Преимущество
: быстрота создания списка.
Б. Ввод элементов списка в диапазон (на том же листе, что и выпадающий список)
Элементы для выпадающего списка можно разместить в диапазоне на листе EXCEL, а затем в поле Источник инструмента указать ссылку на этот диапазон.
Предположим, что элементы списка шт;кг;кв.м;куб.м введены в ячейки диапазона A 1: A 4 , тогда поле Источник будет содержать =лист1!$A$1:$A$4
Преимущество
: наглядность перечня элементов и простота его модификации. Подход годится для редко изменяющихся списков.
Недостатки
: если добавляются новые элементы, то приходится вручную изменять ссылку на диапазон. Правда, в качестве источника можно определить сразу более широкий диапазон, например, A
1:
A
100
. Но, тогда выпадающий список может содержать пустые строки (если, например, часть элементов была удалена или список только что был создан). Чтобы пустые строки исчезли необходимо сохранить файл.
Второй недостаток: диапазон источника должен располагаться на том же листе, что и выпадающий список , т.к. для правил нельзя использовать ссылки на другие листы или книги (это справедливо для EXCEL 2007 и более ранних).
Избавимся сначала от второго недостатка – разместим перечень элементов выпадающего списка на другом листе.
B. Ввод элементов списка в диапазон (на любом листе)
Ввод элементов списка в диапазон ячеек, находящегося в другой книге
Если необходимо перенести диапазон с элементами выпадающего списка в другую книгу (например, в книгу Источник. xlsx ), то нужно сделать следующее:
- в книге Источник. xlsx создайте необходимый перечень элементов;
- в книге Источник.xlsx диапазону ячеек содержащему перечень элементов присвойте , например СписокВнеш;
- откройте книгу, в которой предполагается разместить ячейки с выпадающим списком;
- выделите нужный диапазон ячеек, вызовите инструмент , в поле Источник укажите =ДВССЫЛ("[Источник.xlsx]лист1!СписокВнеш") ;
При работе с перечнем элементов, расположенным в другой книге, файл Источник .xlsx должен быть открыт и находиться в той же папке, иначе необходимо указывать полный путь к файлу. Вообще ссылок на другие листы лучше избегать или использовать Личную книгу макросов Personal.xlsx или Надстройки .
Если нет желания присваивать имя диапазону в файле Источник.xlsx , то формулу нужно изменить на =ДВССЫЛ("[Источник.xlsx]лист1!$A$1:$A$4")
СОВЕТ:
Если на листе много ячеек с правилами Проверки данных
, то можно использовать инструмент (Главная/ Найти и выделить/ Выделение группы ячеек
). Опция Проверка данных
этого инструмента позволяет выделить ячейки, для которых проводится проверка допустимости данных (заданная с помощью команды Данные/ Работа с данными/ Проверка данных
). При выборе переключателя Всех
будут выделены все такие ячейки. При выборе опции Этих
же
выделяются только те ячейки, для которых установлены те же правила проверки данных, что и для активной ячейки.
Примечание
:
Если выпадающий список
содержит более 25-30 значений, то работать с ним становится неудобно. Выпадающий список
одновременно отображает только 8 элементов, а чтобы увидеть остальные, нужно пользоваться полосой прокрутки, что не всегда удобно.
В EXCEL не предусмотрена регулировка размера шрифта Выпадающего списка . При большом количестве элементов имеет смысл список элементов и использовать дополнительную классификацию элементов (т.е. один выпадающий список разбить на 2 и более).
Например, чтобы эффективно работать со списком сотрудников насчитывающем более 300 сотрудников, то его следует сначала отсортировать в алфавитном порядке. Затем создать выпадающий список , содержащий буквы алфавита. Второй выпадающий список должен содержать только те фамилии, которые начинаются с буквы, выбранной первым списком. Для решения такой задачи может быть использована структура или .
Microsoft Excel – отличный инструмент для работы с табличными данными. С его помощью можно быстро создать подходящую таблицу и заполнить ее данными. При этом Excel упрощает не только работу с данными в таблице, но и сам процесс заполнения таблицы.
В этом материале речь пойдет именно о заполнении таблицы. Здесь вы сможете узнать, как в Эксель сделать выпадающий список, который позволит значительно быстрее заполнять таблицу данными. Инструкция будет актуальна для Excel 2007, 2010, 2013 и 2016.
Самый простой способ сделать выпадающий список в Экселе, это воспользоваться функцией для проверки данных. Для создания данного выпадающего списка вам сначала нужно сделать обычный список с данными и разместить его в документе Эксель. Такой список можно разместить на том же листе документа, где будет расположен выпадающий список, либо на любом другом листе.
Итак, сначала создаем список данных, которые должны находится в выпадающем списке, и размещаем его в любом удобном месте документа Эксель. Например, вы можете разместить такой список за областью печати либо на другом листе документа Эксель.
После этого выбираем ячейку, в которой нужно сделать выпадающий список. Выделяем эту ячейку мышкой и нажимаем на кнопку «Проверка данных», на вкладке «Данные».
После этого откроется окно «Проверка вводимых значений». В этом окне сначала нужно открыть выпадающий список «Тип данных» и выбрать там вариант «Список».
После чего нужно заполнить строку «Источник».
Для этого устанавливаем курсор в строку «Источник» после чего выделяем мышкой список данных, который должен находится в созданном выпадающем списке.
После указания источника закрываем окно «Проверка вводимых значений» нажатием на кнопку «Ok». Выпадающий список в Экселе сделан и его можно проверять.
Но, теперь в данную ячейку можно ввести только те данные, которые присутствуют в выпадающем списке. А при попытке ввести неверное значение вы будете получать сообщение об ошибке.
Если вы хотите оставить возможность вводить в ячейку данные не содержащиеся в выпадающем списке, то вам нужно выделить ячейку с впадающим списком и нажать на кнопку «Проверка данных». Дальше в открывшемся окне нужно перейти на вкладку «Сообщение об ошибке» и снять отметку напротив функции «Выводить сообщение об ошибке».
После сохранения этих настроек вы можете вводить в ячейку не только те данные, которые доступны в выпадающем списке, но и любые другие, которые вам понадобятся.
Как сделать выпадающий список с добавлением данных
Описанный выше вариант выпадающего списка достаточно удобен. Но, если вы хотите регулярно добавлять в него новые данные, то этот вариант не подойдет, поскольку после каждого добавления данных вам придется изменять диапазон, который указывается в поле «Источник». Решить эту проблему можно с помощью функции «Умные таблицы», которая появилась в Microsoft Excel 2007.
Для этого вам нужно сделать список с данными, точно также как описано выше. Единственно отличие, теперь список должен иметь заголовок.
После создания списка, его нужно выделить и с помощью кнопки «Форматировать как таблицу» на вкладке «Главная» применить к списку любой стиль.
После выбора стиля, появится окно «Форматирование таблицы». Здесь нужно установить отметку напротив функции «Таблица с заголовком» и нажать «Ok».
В результате у вас должна получится таблица с данными, примерно такая, как на скриншоте внизу.
Теперь нужно выделить ячейку, в которой вы хотите сделать выпадающий список, и нажать на кнопку «Проверка данных» на вкладке «Данные». В открывшемся окне нужно выбрать «Тип данных – Список», а потом установить курсор в строку «Источник» и выделить мышкой список с данными, которые должны использоваться для выпадающего списка (выделять заголовок списка не нужно).
Данный способ создания выпадающих списков с возможностью добавления новых строк был проверен на Excel 2010, но он также должен работать и в Excel 2007. Как и в более современных версиях Excel, например, Excel 2013 и Excel 2016.
Если Вы заполняете таблицу в Excel, и данные в столбце могут иногда повторяться, например, название товара, или имя сотрудника, то, чтобы не вводить нужный параметр каждый раз, проще и легче один раз создать выпадающий список и выбирать из него значение.
В статье мы рассмотрим, как сделать выпадающие списки различного вида в таблице Эксель.
Создаем простой выпадающий список
Для этого, в ячейки А1:А7 вписываем данные, которые будут отображаться в списке. Теперь выделим ячейку, в которой создадим выпадающий список – В2 .
Переходим на вкладку «Данные» и кликаем по кнопочке «Проверка данных» .
На вкладке «Параметры» в поле «Тип данных» выбираем «Список» . В поле «Источник» можно ввести значения различными способами:
1 – вводим значения для списка вручную, через точку с запятой;
2 – указываем диапазон ячеек, в которые введены данные для выпадающего списка;
3 – выделяем ячейки с именами, кликаем по ним правой кнопкой мыши и выбираем из меню «Присвоить имя» .
Выделяем ячейку В2 и в поле «Источник» ставим «=» , затем пишем созданное имя.
Таким образом, мы создали простой выпадающий список в Excel.
Если у Вас есть заголовок для столбца, и значениями нужно заполнять каждую строку, то выделите не одну ячейку, а диапазон ячеек – В2:В9 . Тогда можно будет выбирать из выпадающего списка нужное значение в каждой ячейке.
Добавляем значения в выпадающий список – динамический список
При этом мы будем дописывать значения в нужный диапазон, а они будут автоматически добавляться в выпадающий список.
Выделяем диапазон ячеек – D1:D8 , затем на вкладке «Главная» нажимаем «Форматировать как таблицу» и выбираем любой стиль.
Подтверждаем расположение данных и ставим галочку в поле «Таблица с заголовками» .
Вверху пишем заголовок таблицы – «Сотрудники» , и заполняем ее данными.
Выделяем ячейку, в которой будет выпадающий список и кликаем по кнопочке «Проверка данных» . В следующем окне, в поле «Источник» , пишем следующее: =ДВССЫЛ(«Таблица1[Сотрудники]») . У меня одна таблица на листе, поэтому пишу «Таблица1» , если будет вторая – «Таблица2» , и так далее.
Теперь добавим новое имя сотрудника в наш список: Ира. В выпадающем списке оно появилось. Если мы удалим любое имя из таблицы, из списка оно тоже удалится.
Выпадающий список со значениями с другого листа
Если таблица с выпадающими списками находится на одном листе, а данные для этих списков – на другом, то данная функция нам очень поможет.
На Листе 2, выделяем одну ячейку или диапазон ячеек, затем кликаем по кнопочке «Проверка данных» .
Переходим на Лист 1, ставим курсор в поле «Источник» и выделяем нужный диапазон ячеек.
Теперь можно дописывать имена на Листе 1, они будут добавляться в выпадающие списки на Листе 2.
Создаем зависимые выпадающие списки
Предположим, у нас есть три диапазона: имена, фамилии и отчества сотрудников. Для каждого, нужно присвоить имя. Выделяем ячейки оного диапазона, можно и пустые – в них со временем можно будет добавлять данные, которые будут появляться в выпадающем списке. Кликаем по ним правой кнопкой мыши и выбираем из списка «Присвоить имя» .
Первый называем «Имя» , второй – «Фамилия» , третий – «Отч» .
Сделаем еще один диапазон, в котором будут прописаны присвоенные имена. Назовем его «Сотрудники» .
Делаем первый выпадающий список, который будет состоять из названия диапазонов. Выделяем ячейку Е1 и на вкладке «Данные» выбираем «Проверка данных» .
В поле «Тип данных» выберите «Список» , в поле источник – или введите «=Сотрудники» , или выделите диапазон ячеек, которому присвоено имя.
Первый выпадающий список создан. Теперь в ячейке F2 создадим второй список, который должен зависеть от первого. Если в первом выберем «Имя» , во втором отобразится список имен, если выберем «Фамилия» – список фамилий.
Выделяем ячейку и кликаем по кнопочке «Проверка данных» . В поле «Тип данных» выбираем «Список» , в поле источник прописываем следующее: =ДВССЫЛ($Е$1) . Здесь Е1 – это ячейка с первым выпадающим списком.
По такому принципу можно делать зависимые выпадающие списки.
Если в дальнейшем, нужно будет вписать значения в диапазон, которому задано имя, например, «Фамилия» . Перейдите на вкладку «Формулы» и кликните «Диспетчер имен» . Теперь в имени диапазона выбираем «Фамилия» , и внизу, вместо последней ячейки С3 , напишите С10 . Нажмите галочку. После этого диапазон увеличится, и в него можно будет дописывать данные, которые автоматически будут появляться в выпадающем списке.
Теперь Вы знаете, как сделать раскрывающийся список в Excel.
Оценить статью: (1
оценок, среднее: 5,00
из 5)
Вебмастер. Высшее образование по специальности "Защита информации".. Автор большинства статей и уроков компьютерной грамотности
Выберите для задания выпадающего списка. В меню откройте пункты «Данные» - «Проверка». После чего перейдите в новом окне во вкладку «Параметры» и в открывшемся поле «Тип данных» установите строку «Список». При этом в том же окне появится поле «Источник». Введите в него «=» и имя выделенного диапазона, которое было задано с данными. Для применения параметров нажмите «Enter» или «Ок». Это вариант самого простого выпадающего списка.
При этом в том же окне появится поле «Источник». Введите в него символ «=» и имя выделенного диапазона, которое было задано ячейкам с данными. Для применения установленных параметров нажмите «Enter» или «Ок». Это вариант самого простого выпадающего списка.
В Excel существует возможности для задания выпадающего списка, обладающего более сложной конструкцией. Для этого применяют элемент управления под названием «поле со списком», вставляемый на рабочий лист Excel. Чтобы , откройте в меню пункты «Вид», затем «Панели инструментов» и подпункт «Формы».
Выберите на открывшейся панели элементов управления значок «поле со списком» - это и есть выпадающий список. Нарисуйте мышкой прямоугольник в форме поля. Выделите правой кнопкой мыши нарисованный список и укажите команду «Формат объекта…».
В появившемся диалоговом окне в поле «Формировать список по диапазону» задайте нужный . Для этого мышкой выделите ячейки, которые должны попасть в данный выпадающий список Excel. В поле «Связь с ячейкой» установите номер ячейки для вывода порядкового номера элемента, выбранного в списке. Задайте требуемое количество создаваемого списка. Кнопка «Ок» применит все установленные параметры, и список готов к использованию.
Итак, для создания выпадающего списка необходимо:
1. Создать список значений, которые будут предоставляться на выбор пользователю (в нашем примере это диапазон M1:M3 ), далее выбрать ячейку в которой будет выпадающий список (в нашем примере это ячейка К1 ), потом зайти во вкладку "Данные ", группа "Работа с данными ", кнопка "Проверка данных "
2.
Выбираем "Тип данных
" -"Список
" и указываем диапазон списка
3.
Если есть желание подсказать пользователю о его действиях, то переходим во вкладку "Сообщение для ввода
" и заполняем заголовок и текст сообщения
которое будет появляться при выборе ячейки с выпадающим списком
4.
Так же необязательно можно создать и сообщение, которое будет появляться при попытке ввести неправильные данные
Если Вы не сделаете пункты 3 и 4, то проверка данных работать будет, но при активации ячейки не будет появляться сообщение пользователю о его предполагаемых действиях, а вместо сообщения об ошибке с вашим текстом будет появляться стандартное сообщение.
5.
Если список значений находится на другом листе, то вышеописанным образом создать выпадающий список не получится (до версии Excel 2010). Для этого необходимо будет присвоить имя списку. Это можно сделать несколько способами. Первый
: выделите список и кликните правой кнопкой мыши, в контекстном меню выберите "Присвоить имя
"
Для Excel версий ниже 2007 те же действия выглядят так:
Второй
: воспользуйтесь Диспетчером имён
(Excel версий выше 2003 - вкладка "Формулы
" - группа "Определённые имена
"), который в любой версии Excel вызывается сочетанием клавиш Ctrl+F3
.
Какой бы способ Вы не выбрали в итоге Вы должны будете ввести имя (я назвал диапазон со списком list
) и адрес самого диапазона (в нашем примере это"2"!$A$1:$A$3
)
6.
Теперь в ячейке с выпадающим списком укажите в поле "Источник" имя диапазона
7.
Готово!
Для полноты картины добавлю, что список значений можно ввести и непосредственно в проверку данных, не прибегая к вынесению значений на лист (это так же позволит работать со списком на любом листе). Делается это так:
То есть вручную, через ; (точка с запятой) вводим список в поле "Источник ", в том порядке в котором мы хотим его видеть (значения введённые слева-направо будут отображаться в ячейке сверху вниз).
При всех своих плюсах выпадающий список, созданный вышеописанным образом, имеет один, но очень "жирный" минус: проверка данных работает только при непосредственном вводе значений с клавиатуры. Если Вы попытаетесь вставить в ячейку с проверкой данных значения из буфера обмена, т.е скопированные предварительно любым способом, то Вам это удастся. Более того, вставленное значение из буфера УДАЛИТ ПРОВЕРКУ ДАННЫХ И ВЫПАДАЮЩИЙ СПИСОК ИЗ ЯЧЕЙКИ, в которую вставили предварительно скопированное значение. Избежать этого штатными средствами Excel нельзя.
Статьи по теме
