Сортировка данных с помощью настраиваемых списков
С помощью встроенных настраиваемых списков вы можете сортировать данные по дням недели или месяцам года. При желании вы можете создавать собственные настраиваемые списки для сортировки по любым другим параметрам, которые трудно сортировать по алфавиту (например, высокий, средний и низкий или S, M, L, XL).
Настраиваемые списки позволяют сортировать данные листа по месяцам доставки или по приоритету.
Сортировка по дням недели или месяцам года с помощью встроенного настраиваемого списка
Выполните указанные ниже действия:
Выделите столбцы для сортировки.
Примечание: Для получения наилучшего результата у каждого столбца должен быть заголовок.
На ленте выберите Данные > Сортировка.
Во всплывающем окне Сортировка в раскрывающемся списке Сортировать по выберите столбец, по которому нужно выполнить сортировку.
Например, чтобы отсортировать данные в предыдущем примере по дате доставки, в поле Сортировать по выберите доставка.
В раскрывающемся списке Порядок выберите Настраиваемый список.
В диалоговом окне Списки выберите нужный список и нажмите кнопку OK для сортировки листа.
Создание собственного настраиваемого списка
Чтобы создать собственный настраиваемый список для сортировки, выполните указанные ниже действия.
В столбце листа введите значения, по которым нужно выполнять сортировку. Расположите их нужным образом, определяющим порядок сортировки (от верхней ячейки к нижней). Например:
Выделите все ячейки в этом списке и выберите Файл > Параметры > Дополнительно.
Прокрутите страницу вниз до раздела Общие и нажмите кнопку Изменить списки...
В окне Списки нажмите кнопку Импорт.
Совет: Короткий список, который содержит значения "Высокий", "Средний" и "Низкий", возможно, проще ввести непосредственно в поле Элементы списка окна Списки.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
Создание или удаление настраиваемого списка для сортировки и подстановки данных
Как добавить в список в эксель
Создание раскрывающегося списка
Смотрите также" база данных, когда Target.Cells.Count = 1 сформирует правильную ссылкуТеперь удалим значение «береза».
пунктИмпорт Настраиваемый список может содержать тех, кто хочет с ним дома, некоторыми ограничениями. Поле ExcelИгнорировать пустые ячейки см. статью Удаление.Чтобы упростить работу пользователейДля Excel версий ввод несоответствующего значения Then Application.EnableEvents = на внешний источник
Осуществить задуманное нам помоглаНастраиваемый список
. только текст или избежать многократного копирования то придётся ещеList entriesСортировка по пользовательскому списку.Значения также можно ввести раскрывающегося списка.Если вы хотите, чтобы
с листом, добавьте ниже 2007 те в поле может False If Len(Target.Offset(1, информации.
«умная таблица», которая, чтобы отобразить всплывающееНа панели
текст с числами. и вставки данных. раз создать такой(Элементы списка) позволяетЯ продемонстрирую это на
Установите флажок непосредственно в полеПредлагаем скачать образец книги при выборе ячейки в ячейки раскрывающиеся же действия выглядят привести к нежелаемым 0)) = 0Делаем активной ячейку, куда легка «расширяется», меняется. окно
Списки Чтобы создать настраиваемый Если Вы знаете, же пользовательский список ввести не более примере списка школьныхСписок допустимых значений
Источник с несколькими примерами появлялось всплывающее сообщение, списки, в которых так: результатам. Then Target.Offset(1, 0) хотим поместить раскрывающийсяТеперь сделаем так, чтобыСпискипоявятся выбранные вами список, содержащий только что есть набор на домашнем компьютере.
255 символов. Будьте клубов. Мне нужноОткройте вкладкучерез запятую.
проверки данных, аналогичными установите флажок
можно выбрать нужныйВторойИтак, для создания
= Target Else список. можно было вводить, а затем выделите элементы. числа, например от данных, который в Если Вы использовали внимательны к тому, вести учёт денегСообщение для вводаЧтобы изменить список допустимых примеру в этой
Отображать подсказку, если ячейка элемент.: воспользуйтесь
выпадающего списка необходимо: Target.End(xlDown).Offset(1, 0) =Открываем параметры проверки данных. новые значения прямо настраиваемый список и"Параметры" > "Дополнительно" > "Общие" > 0 до 100, любой момент может настраиваемый список, чтобы из скольких символов на балансе каждого. элементов, просто измените статье. Вы можете
является текущейWindows macOS OnlineДиспетчером имён1.
Target End If В поле «Источник» в ячейку с нажмите кнопку "Изменить списки". Если нужно сначала создать понадобиться, почему бы сделать сортировку, то состоит каждая Ваша из них, а
Если вы хотите, чтобы значения в списке-источнике
воспользоваться ими или
и введите заголовок (Excel версий вышеСоздать список значений, Target.ClearContents Application.EnableEvents = вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).
этим списком. ИДобавить вы используете Excel список чисел в не сделать из его элементы останутся запись! также наметить ежедневный при выборе ячейки или диапазон в
Работа с раскрывающимся списком
создать собственные сценарии и сообщение вНа новом листе введите 2003 - вкладка которые будут предоставляться True End IfИмя файла, из которого
данные автоматически добавлялись. 2007, нажмите кнопку текстовом формате. него настраиваемый список? в ячейках Excel,Подсказка: бюджет. Я начал появлялось всплывающее сообщение, поле проверки данных. Скачать
соответствующие поля (до данные, которые должны " на выбор пользователю End Sub
берется информация для в диапазон.Создание списка последовательных дат
Скачивание примеров
Office и выберитеСоздать настраиваемый список можно Кто знает, возможно но среди списковЕсли вы планируете с того, что установите флажокИсточник примеры проверки данных 225 символов). Если
отображаться в раскрывающемсяФормулы (в нашем примереЧтобы выбираемые значения отображались списка, заключено в
Сформируем именованный диапазон. Путь:Под выпадающим списком понимается "Параметры Excel" > "Популярные" > двумя способами. Если это сохранит Вам он показан не вводить список в ввёл названия клубов,Показывать сообщения. Excel вы не хотите, списке. Желательно, чтобы" - группа " это диапазон
в одной ячейке, квадратные скобки. Этот «Формулы» - «Диспетчер содержание в одной "Изменить списки"." /> список короткий, можно гораздо больше времени,
будет. поле – это та
и введите заголовокМожно указать собственное сообщениеВвод данных станет быстрее чтобы сообщение отображалось, элементы списка содержалисьОпределённые именаM1:M3 разделенные любым знаком файл должен быть
имен» - «Создать». ячейке нескольких значений.Два раза нажмите кнопку ввести его значения чем Вы можетеИтак, наш настраиваемый списокList entries информация, которая мне и сообщение в об ошибке, которое и точнее, если снимите этот флажок.
в таблице Excel."), который в любой), далее выбрать ячейку препинания, применим такой открыт. Если книга Вводим уникальное название Когда пользователь щелкаетОК
прямо во всплывающем предположить… и оставит готов к использованию.(Элементы списка) вручную, будет нужна постоянно.
соответствующие поля (до будет отображаться при ограничить значения в
Откройте вкладку Если это не версии Excel вызывается
в которой будет модуль. с нужными значениями диапазона – ОК. по стрелочке справа,
. окне. Если список
шефа с мыслью, Выделите ячейку и не ставьте лишниеВ этот момент я
225 символов). Если вводе недопустимых данных. ячейке вариантами изСообщение об ошибке так, список можно сочетанием клавиш выпадающий список (в
Private Sub Worksheet_Change(ByVal находится в другойСоздаем раскрывающийся список в появляется определенный перечень.Примечание: длинный, можно импортировать что Вам пришлось введите с клавиатуры пробелы между элементами. бы также запустил вы не хотите, На вкладке раскрывающегося списка..
См. также
быстро преобразовать вCtrl+F3
нашем примере это Target As Range)
папке, нужно указывать любой ячейке. Как Можно выбрать конкретное. Настраиваемый список можно создать значения из диапазона изрядно потрудиться. Пусть
любой элемент этого Если пробелы будут
проверку орфографии, это чтобы сообщение отображалось,ДанныеСначала создайте на листеЕсли вы хотите, чтобы таблицу, выделив любую. ячейкаOn Error Resume путь полностью.
это сделать, ужеОчень удобный инструмент Excel только на основе ячеек. он так думает.
списка. Нажмите на стоять до или делается простым нажатием
снимите этот флажок.нажмите кнопку список допустимых элементов, при вводе значения, ячейку диапазона и
Какой бы способК1 NextВозьмем три именованных диапазона: известно. Источник – для проверки введенных значений, таких как
Чтобы создать настраиваемый списокНаслаждайтесь! маркер автозаполнения (небольшой после элемента, тоF7Откройте вкладкуПроверка данных а затем выполните которого нет в нажав клавиши Вы не выбрали), потом зайти воIf Not Intersect(Target,Это обязательное условие. Выше имя диапазона: =деревья. данных. Повысить комфорт текст, числа, даты
этим способом, выполните
Урок подготовлен для Вас квадратик в правом Microsoft их просто.
Сообщение об ошибкеили
сортировку или расположите списке, появлялось всплывающееCTRL+T
в итоге Вы вкладку " Range("C2:C5")) Is Nothing описано, как сделатьСнимаем галочки на вкладках работы с данными и время. На указанные ниже действия. командой сайта office-guru.ru нижнем углу ячейки), не станет учитывать,Затем я выделил эту
.Проверить их в нужном
сообщение, установите флажок. должны будете ввестиДанные And Target.Cells.Count = обычный список именованным «Сообщение для ввода», позволяют возможности выпадающих основе формата, напримерВИсточник: https://www.ablebits.com/office-addins-blog/2011/11/02/create-excel-custom-lists/ и потяните за а если между область, нажал на
Если вы хотите, чтобы, а затем откройте порядке. В дальнейшем
Выводить сообщение об ошибкеПримечания: имя (я назвал", группа " 1 Then диапазоном (с помощью «Сообщение об ошибке». списков: подстановка данных, значков, цвета ячейкиExcel 2010Перевел: Антон Андронов него, чтобы продолжить словами одного элемента, кнопку при вводе значения,
См. также
вкладку эти элементы могут
, выберите параметр в
support.office.com
Создание и использование настраиваемых списков в Excel 2007 и 2010
диапазон со спискомРабота с даннымиApplication.EnableEvents = False «Диспетчера имен»). Помним, Если этого не отображение данных другого или цвета шрифта,и более позднихАвтор: Антон Андронов последовательность. Чуть правее то все ониOffice которого нет вСообщение об ошибке служить источником для полеПочему данные следует поместитьlist", кнопка "newVal = Target что имя не сделать, Excel не листа или файла, создать настраиваемый список
- версиях выберите пунктыДля сортировки или заполнения
- я вписал «Monday»,
- будут сохранены.и в нижней
- списке, появлялось всплывающее.
- раскрывающегося списка данных.
Создаем пользовательский список в Excel
Вид в таблицу? Потому) и адрес самогоПроверка данныхApplication.Undo может содержать пробелов позволит нам вводить наличие функции поиска нельзя.Файл значений в пользовательском затем нажал наДругой способ добавить элементы
части открывшегося меню сообщение, установите флажокПрименение проверки данных к Если список небольшой,и введите заголовок что в этом
диапазона (в нашем"oldval = Target и знаков препинания. новые значения. и зависимости.Выполните указанные ниже действия. > порядке можно применять
маркер и потянул в настраиваемый список щелкнул поПоказывать оповещения ячейкам на него можно и сообщение. Если случае при добавлении примере этоДля Excel версийIf Len(oldval) <>Создадим первый выпадающий список,
Вызываем редактор Visual Basic.Путь: меню «Данные» -По приведенным выше инструкциямПараметры настраиваемые списки. В вправо, Excel заполнил – импортировать данные.Excel Options, выберите параметр вУпрощение ввода данных с легко ссылаться и
вы не хотите, и удалении элементов'2'!$A$1:$A$3 ниже 2007 те 0 And oldval куда войдут названия Для этого щелкаем инструмент «Проверка данных» откройте диалоговое окно > Excel есть встроенные ячейки автоматически. Если Вы выделите(Параметры Excel). поле помощью формы данных вводить элементы прямо чтобы сообщение отображалось, все раскрывающиеся списки,) же действия выглядят <> newVal Then
Создаем список с нуля
диапазонов. правой кнопкой мыши - вкладка «Параметры». "Списки".Дополнительно списки дней неделиЗамечательное свойство этой функции их прежде чемВ разделеТипНа новом листе введите в средстве проверки снимите этот флажок. созданные на основе6. так:Target = TargetКогда поставили курсор в по названию листа Тип данных –Выделите список, который нужно >
и месяцев года, состоит в том, откроете окно меню,Popularи введите заголовок данные, которые должны данных.Не знаете, какой параметр этой таблицы, будутТеперь в ячейке2. & "," & поле «Источник», переходим и переходим по «Список». удалить, в полеОбщие
Создание списка из существующего диапазона данных
но вы можете что Excel одинаково выбранный диапазон будет(Общие) Вы найдёте и сообщение. Если отображаться в раскрывающемсяСоздайте список допустимых элементов выбрать в поле обновляться автоматически. Дополнительные с выпадающим спискомВыбираем " newVal на лист и вкладке «Исходный текст».Ввести значения, из которыхСписки > создавать и свои заполнит как столбец, автоматически вставлен в пункт вы не хотите, списке. Желательно, чтобы
для раскрывающегося списка.Вид действия не требуются. укажите в полеТип данныхElse выделяем попеременно нужные Либо одновременно нажимаем будет складываться выпадающийи нажмите кнопкуИзменить списки настраиваемые списки. так и строку, соответствующее поле. ВамCreate lists for use чтобы сообщение отображалось, элементы списка содержались
Для этого введите?Теперь следует отсортировать данные "Источник" имя диапазона" -"Target = newVal ячейки. клавиши Alt + список, можно разнымиУдалить
.Чтобы понять, что представляют как в прямом останется только нажать in sorts and снимите этот флажок. в таблице Excel. элементы на листеЧтобы отобразить сообщение, не в том порядке,7.СписокEnd IfТеперь создадим второй раскрывающийся F11. Копируем код способами:.В собой настраиваемые списки, направлении, так иImport fills sequencesНажмите кнопкуПримечания: в одном столбце
Использование пользовательских списков в Excel
препятствующее вводу данных, в котором ониГотово!" и указываем диапазонIf Len(newVal) = список. В нем (только вставьте своиВручную через «точку-с-запятой» вНастраиваемые списки добавляются вExcel 2007 полезно ознакомиться с в обратном, начнёте(Импорт) и Excel(Создавать списки дляОК или строке без
которые не содержатся должны отображаться вДля полноты картины списка 0 Then Target.ClearContents должны отражаться те параметры).Private Sub Worksheet_Change(ByVal поле «Источник». реестр компьютера, чтобынажмите принципами их работы Вы с первого создаст список из сортировки и заполнения).Почему данные следует поместить пустых ячеек. в раскрывающемся списке,
Сортировка по пользовательскому списку
раскрывающемся списке. добавлю, что список3.Application.EnableEvents = True слова, которые соответствуют Target As Range)Ввести значения заранее. А их можно былокнопку Microsoft Office и хранения на элемента списка, с содержащегося в ячейках – кликните поПосле создания раскрывающегося списка в таблицу? ПотомуВыделите ячейки, для которых выберите вариантВыделите на листе ячейку, значений можно ввестиЕсли есть желание
End If выбранному в первом Dim lReply As в качестве источника использовать в другихи выберите пункты компьютере. середины или с текста. Если ВыEdit Custom Lists убедитесь, что он что в этом нужно ограничить вводСообщение в которую требуется и непосредственно в подсказать пользователю о
End Sub списке названию. Если Long If Target.Cells.Count указать диапазон ячеек книгах. Если выПараметры ExcelВ Excel есть указанные последнего элемента… Как не выделили текст(Изменить списки). работает правильно. Например, случае при добавлении данных.или "Предупреждение". Если поместить раскрывающийся список. проверку данных, не его действиях, тоНе забываем менять диапазоны «Деревья», то «граб», > 1 Then со списком. используете настраиваемый список
>
ниже встроенные списки только Вы щелкните
заранее, поставьте курсор
Если Вы работаете в
можно проверить, достаточно
office-guru.ru
Создание или удаление настраиваемого списка для сортировки и подстановки данных
и удалении элементовНа вкладке выбрать вариант "Сообщение",На ленте откройте вкладку прибегая к вынесению переходим во вкладку на «свои». Списки «дуб» и т.д. Exit Sub IfНазначить имя для диапазона
Сведения о настраиваемых списках
при сортировке данных,Популярные дней недели и и начнете протаскивать в поле рядом Excel 2010, то
Сравнение встроенных и настраиваемых списков
ли ширины ячеек все раскрывающиеся списки,Данные сообщение будет отображаться
Данные |
значений на лист " |
создаем классическим способом. Вводим в поле |
Target.Address = "$C$2" значений и в он также сохраняется> |
месяцев года. мышь, Excel определит, с кнопкой Вам нужен другой |
для отображения всех созданные на основев группе
со значкоми нажмите кнопку (это так жеСообщение для ввода А всю остальную «Источник» функцию вида Then If IsEmpty(Target) поле источник вписать вместе с книгой,Основные параметры работы сВстроенные списки что Вы хотите
Import |
маршрут. Откройте вкладку |
элементов. Если вы |
этой таблицы, будут |
Инструменты, а еслиПроверка данных позволит работать со |
" и заполняем заголовок работу будут делать =ДВССЫЛ(E3). E3 – Then Exit Sub это имя. поэтому его можно
ExcelПн, Вт, Ср, Чт, сделать, и вставит(Импорт) и выделитеFile решили изменить элементы обновляться автоматически. Дополнительныенажмите кнопку "Предупреждение" — со значком. списком на любом
Создание настраиваемого списка
и текст сообщения макросы. ячейка с именем If WorksheetFunction.CountIf(Range("Деревья"), Target)Любой из вариантов даст использовать на других > Пт, Сб, Вс соответствующие данные.
Введение значений напрямую
ячейки с данными(Файл) и нажмите раскрывающегося списка, см.
действия не требуются.Проверка данных.Примечание: листе). Делается этокоторое будет появлятьсяНа вкладке «Разработчик» находим первого диапазона. = 0 Then такой результат. компьютерах, в томИзменить спискиПонедельник, Вторник, Среда, Четверг,Одна из приятных возможностей,
для нового списка.Options статью Добавление иТеперь следует отсортировать данные
илиЧтобы заблокировать пользователям ввод Если кнопка так: при выборе ячейки инструмент «Вставить» –Бывает, когда из раскрывающегося lReply = MsgBox("Добавить числе на серверах
. Пятница, Суббота, Воскресенье которую дает работаПомните, у нас было(Параметры). Затем пролистайте удаление элементов раскрывающегося в том порядке,Проверить данных, которые не
Проверка данныхТо есть вручную,
с выпадающим списком «ActiveX». Здесь нам списка необходимо выбрать введенное имя "
Необходимо сделать раскрывающийся список с службы Excel,Выберите в полеянв, фев, мар, апр,
с настраиваемыми списками, ограничение на количество вниз, найдите кнопку
Создание настраиваемого списка на основе диапазона ячеек
списка. Чтобы удалить
в котором они. содержатся в раскрывающемсянедоступна, возможно, лист через4. нужна кнопка «Поле сразу несколько элементов.
& _ Target со значениями из для которых можетСписки май, июн, июл, – это возможность символов, которое можноEdit Custom Lists раскрывающийся список, см.
должны отображаться вПримечание: списке, выберите вариант защищен или является
;Так же необязательно со списком» (ориентируемся Рассмотрим пути реализации & " в динамического диапазона. Если быть опубликована ваша
пункт авг, сен, окт, упорядочить данные по
ввести в поле(Изменить списки). статью Удаление раскрывающегося раскрывающемся списке. Если команда проверки недоступна,Остановка общим. Разблокируйте определенные(точка с запятой) вводим можно создать и на всплывающие подсказки). задачи.
Удаление настраиваемого списка
выпадающий список?", vbYesNo
вносятся изменения в книга.НОВЫЙ СПИСОК
ноя, дек любому из списков,List entriesДалее откроется диалоговое окно, списка.Выделите на листе ячейку,
Хранение настраиваемых списков
возможно, лист защищен. области защищенной книги список в поле сообщение, которое будетЩелкаем по значку –Создаем стандартный список с + vbQuestion) If имеющийся диапазон (добавляютсяОднако, открыв книгу наи введите данныеЯнварь, Февраль, Март, Апрель, сохранённых на Вашем(Элементы списка)? Только в котором можноПрименение проверки данных к в которую требуется
или книга являетсяПримечание: или отмените общий " появляться при попытке становится активным «Режим помощью инструмента «Проверка lReply = vbYes или удаляются данные), другом компьютере или в поле Май, Июнь, Июль, компьютере. Кликните по не при импорте! добавить информацию, которую ячейкам поместить раскрывающийся список. общей. Если книга Если вы не добавили доступ к листу,Источник
ввести неправильные данные конструктора». Рисуем курсором данных». Добавляем в Then Range("Деревья").Cells(Range("Деревья").Rows.Count + они автоматически отражаются сервере, вы неЭлементы списка Август, Сентябрь, Октябрь, одному или нескольким Теперь максимальный размер планируется использовать многократно.Функции Excel (по категориям)На ленте откройте вкладку является общей или заголовок и текст, а затем повторите", в том порядкеЕсли Вы не (он становится «крестиком») исходный код листа 1, 1) = в раскрывающемся списке. найдете настраиваемый список,
См. также
support.office.com
Выпадающий список в Excel с помощью инструментов или макросов
Ноябрь, Декабрь столбцам, затем нажмите списка где-то 2000 Если Вы посмотритеЕсли Вам приходится работатьДанные лист защищен, изменить
по умолчанию выводится шаг 3. в котором мы сделаете пункты 3 небольшой прямоугольник – готовый макрос. Как Target End IfВыделяем диапазон для выпадающего сохраненный в файле элемента.
Создание раскрывающегося списка
Примечание:Sort & Filter символов! Нажмите на списки, уже с книгой Excel,

и нажмите кнопку параметры проверки данных заголовок "Microsoft Excel"На вкладке
- хотим его видеть и 4, то
- место будущего списка. это делать, описано End If End списка. В главном
- книги, вПосле ввода каждого элемента Изменить или удалить встроенный(Сортировка и фильтр),


ОК подготовленные Microsoft, то
в которой постоянноВыпадающий список в Excel с подстановкой данных
Проверка данных невозможно. Дополнительные сведения и сообщение "ВведенноеПараметры (значения введённые слева-направопроверка данныхЖмем «Свойства» – открывается выше. С его
- If End Sub меню находим инструментПараметрах Excel нажимайте клавишу ВВОД.
- список невозможно. раскройте выпадающее меню, чтобы закрыть окно увидите среди них фигурирует один и. о защите книги значение неверно. Наборв поле будут отображаться вработать будет, но перечень настроек. помощью справа от
- Сохраняем, установив тип файла «Форматировать как таблицу».во всплывающем окнеЗавершив создание списка, нажмитеВы также можете создатьOrder с параметрами списка, те, которыми все

тот же списокНа вкладке см. в статье

значений, которые могутТип данных

ячейке сверху вниз).

при активации ячейкиВписываем диапазон в строку выпадающего списка будут
«с поддержкой макросов».Откроются стили. Выбираем любой.Списки кнопку свой настраиваемый список(Порядок), кликните и ещё раз

- так часто пользуются. данных, или можетПараметры Защита книги. быть введены в
- выберите пунктПри всех своих не будет появляться ListFillRange (руками). Ячейку, добавляться выбранные значения.Private
- Переходим на лист со Для решения нашей. Настраиваемые списки будутДобавить и использовать егоCustom ListОК
- Они не могут быть Вы простов полеОткройте вкладку ячейку, ограничен".Список плюсах выпадающий список, сообщение пользователю о куда будет выводиться Sub Worksheet_Change(ByVal Target списком. Вкладка «Разработчик» задачи дизайн не отображаться только в. для сортировки или(Настраиваемый список) и, чтобы закрыть окно быть изменены или не хотите множествоРазрешитьПараметрыПосле создания раскрывающегося списка. созданный вышеописанным образом, его предполагаемых действиях, выбранное значение – As Range) On - «Код» - имеет значения. Наличие диалоговом окнеНа панели заполнения. Например, чтобы выберите список, по
- параметров Excel. удалены. Однако, если
- раз использовать действиевыберите пункти во всплывающем убедитесь, что онЩелкните поле имеет один, но а вместо сообщения в строку LinkedCell.

Error Resume Next «Макросы». Сочетание клавиш заголовка (шапки) важно.СортировкаСписки отсортировать или заполнить
которому желаете выполнитьДля пользовательского списка можно Вы захотите удалить
Выпадающий список в Excel с данными с другого листа/файла
КопироватьСписок меню работает правильно. Например,Источник очень "жирный" минус: об ошибке с Для изменения шрифта If Not Intersect(Target, для быстрого вызова В нашем примере
- в столбцепоявятся введенные вами значения по приведенным
- сортировку. импортировать только текстовые или изменить созданный
>.Разрешить можно проверить, достаточнои выделите диапазон проверка данных работает вашим текстом будет и размера – Range("Е2:Е9")) Is Nothing – Alt +
Как сделать зависимые выпадающие списки
это ячейка А1

Порядок элементы. ниже спискам, нуженСортировка может включать более значения. Если Вам Вами список, этоВставитьЕсли вы уже создали
- выберите пункт ли ширины ячеек списка. В примере
- только при непосредственном появляться стандартное сообщение. Font. And Target.Cells.Count = F8. Выбираем нужное
- со словом «Деревья».. Настраиваемый список, хранимыйНажмите два раза кнопку настраиваемый список, так одного столбца. Если нужно создать настраиваемый можно сделать в, то было бы таблицу с элементамиСписок для отображения всех данные находятся на вводе значений с
Выбор нескольких значений из выпадающего списка Excel
5.Скачать пример выпадающего списка 1 Then Application.EnableEvents имя. Нажимаем «Выполнить». То есть нужно
- в файле книги,ОК как соответствующего естественного Вы хотите добавить список с календарными любой момент. Для очень удобно иметь раскрывающегося списка, щелкните. элементов. листе "Города" в клавиатуры. Если ВыЕсли список значенийПри вводе первых букв = False IfКогда мы введем в выбрать стиль таблицы также недоступен непосредственно. порядка значений не ещё один уровень датами или числами, этого кликните по заранее настроенный и полеЩелкните поле
- Если список элементов для диапазоне A2:A9. Обратите попытаетесь вставить в находится на другом с клавиатуры высвечиваются Len(Target.Offset(0, 1)) = пустую ячейку выпадающего со строкой заголовка. для командыВыполните указанные ниже действия. существует. и упорядочить сначала то придётся использовать своему списку и сохранённый список, чтобыИсточникИсточник раскрывающегося списка находится внимание на то,
- ячейку с листе, то вышеописанным подходящие элементы. И 0 Then Target.Offset(0, списка новое наименование,
листе список допустимых и вы хотите отсутствует в диапазоне,значения из буфера список не получится все приятные моменты
Выпадающий список с поиском
- Else Target.End(xlToRight).Offset(0, 1) введенное имя баобабСтавим курсор в ячейку,При необходимости можно добавить по которым нужноБольшое, Среднее, Малое
- счёта, то можете(Элементы списка).У меня есть два Использование настраиваемых списков Однако не включайте элементов.
- запретить пользователям его так как она
- обмена, т.е скопированные (до версии Excel данного инструмента. Здесь = Target End в выпадающий список?». где будет находиться такой список в выполнить сортировку или
Север, Юг, Восток, Запад
кликнутьВот некоторые моменты, которые способа, как создать – это хорошее в него ячейкуДиалоговое окно свернется, чтобы просмотр и изменение, не является одним предварительно любым способом, 2010). Для этого
exceltable.com
Создание выпадающего списка в ячейке
можно настраивать визуальное If Target.ClearContents Application.EnableEventsНажмем «Да» и добавиться выпадающий список. Открываем реестр компьютера или заполнение. Выделите этотСтарший менеджер по продажам,Add Level Вы должны знать настраиваемый список. Я решение для данной заголовка. Добавьте только было видно весь скройте и защитите
из вариантов, доступных то Вам это необходимо будет присвоить представление информации, указывать = True End еще одна строка параметры инструмента «Проверка сервера, чтобы он диапазон и, следуя Региональный менеджер по(Добавить уровень) и о настраиваемый списках… могу создать его ситуации. Далее я ячейки, которые должны лист. этот лист. Подробнее для выбора. удастся. Более того, имя списку. Это в качестве источника

If End Sub со значением «баобаб». данных» (выше описан был доступен в

инструкциям выше, откройте продажам, Руководитель отдела определить, каким образом Все списки привязываются вручную, введя каждое покажу, как создать отображаться в раскрывающемся

Нажмите клавишу ВВОД или о защите листовЕсли можно оставить ячейку вставленное значение из можно сделать несколько сразу два столбца.Чтобы выбранные значения показывалисьКогда значения для выпадающего путь). В поле

Параметрах Excel всплывающее окно "Списки". продаж, Торговый представитель

должны отображаться данные. к компьютеру. Их значение в поле настраиваемый список в списке. Список элементов кнопку


см. в статье пустой, установите флажок буфера УДАЛИТ ПРОВЕРКУ способами.Выпадающий список в снизу, вставляем другой списка расположены на «Источник» прописываем такуюво всплывающем окнеУбедитесь, что ссылка наНастраиваемый список может соответствовать Когда закончите, нажмите настройки сохраняются на
List entries Excel. также можно ввестиРазвернуть Блокировка ячеек.Игнорировать пустые ячейки ДАННЫХ И ВЫПАДАЮЩИЙПервый ячейке позволяет пользователю код обработчика.Private Sub другом листе или функцию:Списки выделенные значения отображается диапазону ячеек, илиОК том компьютере, на(Элементы списка) иСоздаем пользовательский список в

непосредственно в поле, чтобы развернутьЕсли вы решили изменить.

СПИСОК ИЗ ЯЧЕЙКИ,: выделите список и выбирать для ввода Worksheet_Change(ByVal Target As в другой книге,Протестируем. Вот наша таблица. Для этого выберите в окне его можно ввести. Теперь информация упорядочена котором Вы в нажав ExcelИсточник
диалоговое окно, а элементы раскрывающегося списка,Установите флажок в которую вставили кликните правой кнопкой только заданные значения. Range) On Error стандартный способ не со списком на во всплывающем окнеСписки в диалоговом окне

по выбранному списку! данный момент работаете.AddСоздаем список с нулячерез запятую. Например:

затем нажмите кнопку см. статью Добавление

Список допустимых значений предварительно скопированное значение. мыши, в контекстном Это особенно удобно Resume Next If работает. Решить задачу одном листе:Сортировкав полеСпискиВсе достаточно просто! Применение Если Вы возьмёте

(Добавить). Если ВыСоздание списка из существующегоФрукты;Овощи;Зерновые культуры;Молочные продукты;ПерекусыОК и удаление элементовОткройте вкладку Избежать этого штатными меню выберите " при работе с Not Intersect(Target, Range("Н2:К2")) можно с помощьюДобавим в таблицу новоев столбце
Импорт списка из ячеек. пользовательских списков очень файл с рабочего выберете этот путь, диапазона данныхЕсли можно оставить ячейку. раскрывающегося списка.Подсказка по вводу средствами Excel нельзя.Присвоить имя файлами структурированными как Is Nothing And функции ДВССЫЛ: она значение «елка».Порядок, и нажмите кнопкуПримечание: удобно особенно для компьютера, чтобы поработать то столкнетесь сИспользование пользовательских списков в пустой, установите флажокСоветы:

excelworld.ru
Чтобы удалить раскрывающийся список,
Создание или удаление настраиваемого списка для сортировки и подстановки данных
Для сортировки или заполнения значений в пользовательском порядке можно применять настраиваемые списки. В Excel есть встроенные списки дней недели и месяцев года, но вы можете создавать и свои настраиваемые списки.
Чтобы понять, что представляют собой настраиваемые списки, полезно ознакомиться с принципами их работы и хранения на компьютере.
Сравнение встроенных и настраиваемых списков
В Excel есть указанные ниже встроенные списки дней недели и месяцев года.
Встроенные списки |
---|
Пн, Вт, Ср, Чт, Пт, Сб, Вс |
Понедельник, Вторник, Среда, Четверг, Пятница, Суббота, Воскресенье |
янв, фев, мар, апр, май, июн, июл, авг, сен, окт, ноя, дек |
Январь, Февраль, Март, Апрель, Май, Июнь, Июль, Август, Сентябрь, Октябрь, Ноябрь, Декабрь |
Примечание: Изменить или удалить встроенный список невозможно.
Вы также можете создать свой настраиваемый список и использовать его для сортировки или заполнения. Например, чтобы отсортировать или заполнить значения по приведенным ниже спискам, нужен настраиваемый список, так как соответствующего естественного порядка значений не существует.
Настраиваемые списки |
---|
Высокое, Среднее, Низкое |
Большое, Среднее, Малое |
Север, Юг, Восток, Запад |
Старший менеджер по продажам, Региональный менеджер по продажам, Руководитель отдела продаж, Торговый представитель |
Настраиваемый список может соответствовать диапазону ячеек, или его можно ввести в диалоговом окне Списки.
Примечание: Настраиваемый список может содержать только текст или текст с числами. Чтобы создать настраиваемый список, содержащий только числа, например от 0 до 100, нужно сначала создать список чисел в текстовом формате.
Создать настраиваемый список можно двумя способами. Если список короткий, можно ввести его значения прямо во всплывающем окне. Если список длинный, можно импортировать значения из диапазона ячеек.
Введение значений напрямую
Чтобы создать настраиваемый список этим способом, выполните указанные ниже действия.
В Excel 2010 и более поздних версиях выберите пункты Файл > Параметры > Дополнительно > Общие > Изменить списки.
В Excel 2007 нажмите кнопку Microsoft Office
и выберите пункты Параметры Excel > Популярные > Основные параметры работы с Excel > Изменить списки.
Выберите в поле Списки пункт НОВЫЙ СПИСОК и введите данные в поле Элементы списка, начиная с первого элемента.
После ввода каждого элемента нажимайте клавишу ВВОД.
Завершив создание списка, нажмите кнопку Добавить.
На панели Списки появятся введенные вами элементы.
Нажмите два раза кнопку ОК.
Создание настраиваемого списка на основе диапазона ячеек
Выполните указанные ниже действия.
В диапазоне ячеек введите сверху вниз значения, по которым нужно выполнить сортировку или заполнение. Выделите этот диапазон и, следуя инструкциям выше, откройте всплывающее окно "Списки".
Убедитесь, что ссылка на выделенные значения отображается в окне Списки в поле Импорт списка из ячеек, и нажмите кнопку Импорт.
На панели Списки появятся выбранные вами элементы.
Два раза нажмите кнопку ОК.
Примечание: Настраиваемый список можно создать только на основе значений, таких как текст, числа, даты и время. На основе формата, например значков, цвета ячейки или цвета шрифта, создать настраиваемый список нельзя.
Выполните указанные ниже действия.
По приведенным выше инструкциям откройте диалоговое окно "Списки".
Выделите список, который нужно удалить, в поле Списки и нажмите кнопку Удалить.
Настраиваемые списки добавляются в реестр компьютера, чтобы их можно было использовать в других книгах. Если вы используете настраиваемый список при сортировке данных, он также сохраняется вместе с книгой, поэтому его можно использовать на других компьютерах, в том числе на серверах с Службы Excel, для которых может быть опубликована ваша книга.
Однако при открытии книги на другом компьютере или сервере такой список, сохраненный в файле книги, не отображается во всплывающем окне Списки в параметрах Excel: его можно выбрать только в столбце Порядок диалогового окна Сортировка. Настраиваемый список, сохраненный в файле книги, также недоступен непосредственно для команды Заполнить.
При необходимости можно добавить такой список в реестр компьютера или сервера, чтобы он был доступен в Параметрах Excel во всплывающем окне Списки. Для этого выберите во всплывающем окне Сортировка в столбце Порядок пункт Настраиваемый список, чтобы отобразить всплывающее окно Списки, а затем выделите настраиваемый список и нажмите кнопку Добавить.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
Создание списка последовательных дат
Эксель сделать выбор из списка. Создание выпадающего списка в ячейке
Если Вам приходится работать с книгой Excel, в которой постоянно фигурирует один и тот же список данных, или может быть Вы просто не хотите множество раз использовать действие Копировать > Вставить , то было бы очень удобно иметь заранее настроенный и сохранённый список, чтобы Excel мог помочь Вам в работе. Использование настраиваемых списков – это хорошее решение для данной ситуации. Далее я покажу, как создать настраиваемый список в Excel.
Создаем пользовательский список в Excel
Я продемонстрирую это на примере списка школьных клубов. Мне нужно вести учёт денег на балансе каждого из них, а также наметить ежедневный бюджет. Я начал с того, что ввёл названия клубов, – это та информация, которая мне будет нужна постоянно.
В этот момент я бы также запустил проверку орфографии, это делается простым нажатием F7 .
Затем я выделил эту область, нажал на кнопку Office и в нижней части открывшегося меню щелкнул по Excel Options (Параметры Excel).
В разделе Popular (Общие) Вы найдёте пункт Create lists for use in sorts and fills sequences (Создавать списки для сортировки и заполнения) – кликните по Edit Custom Lists (Изменить списки).
Если Вы работаете в Excel 2010, то Вам нужен другой маршрут. Откройте вкладку File (Файл) и нажмите Options (Параметры). Затем пролистайте вниз, найдите кнопку Edit Custom Lists (Изменить списки).
Далее откроется диалоговое окно, в котором можно добавить информацию, которую планируется использовать многократно. Если Вы посмотрите на списки, уже подготовленные Microsoft, то увидите среди них те, которыми все так часто пользуются. Они не могут быть изменены или удалены. Однако, если Вы захотите удалить или изменить созданный Вами список, это можно сделать в любой момент. Для этого кликните по своему списку и делайте с ним что захотите.
Создаем список с нуля
У меня есть два способа, как создать настраиваемый список. Я могу создать его вручную, введя каждое значение в поле List entries (Элементы списка) и нажав Add (Добавить). Если Вы выберете этот путь, то столкнетесь с некоторыми ограничениями. Поле List entries (Элементы списка) позволяет ввести не более 255 символов. Будьте внимательны к тому, из скольких символов состоит каждая Ваша запись!
Подсказка: Если вы планируете вводить список в поле List entries (Элементы списка) вручную, не ставьте лишние пробелы между элементами. Если пробелы будут стоять до или после элемента, то Microsoft их просто не станет учитывать, а если между словами одного элемента, то все они будут сохранены.
Создание списка из существующего диапазона данных
Другой способ добавить элементы в настраиваемый список – импортировать данные. Если Вы выделите их прежде чем откроете окно меню, выбранный диапазон будет автоматически вставлен в соответствующее поле. Вам останется только нажать Import (Импорт) и Excel создаст список из содержащегося в ячейках текста. Если Вы не выделили текст заранее, поставьте курсор в поле рядом с кнопкой Import (Импорт) и выделите ячейки с данными для нового списка.
Помните, у нас было ограничение на количество символов, которое можно ввести в поле List entries (Элементы списка)? Только не при импорте! Теперь максимальный размер списка где-то 2000 символов! Нажмите ОК , чтобы закрыть окно с параметрами списка, и ещё раз ОК , чтобы закрыть окно параметров Excel.
Для пользовательского списка можно импортировать только текстовые значения. Если Вам нужно создать настраиваемый список с календарными датами или числами, то придётся использовать поле List entries (Элементы списка).
Вот некоторые моменты, которые Вы должны знать о настраиваемый списках… Все списки привязываются к компьютеру. Их настройки сохраняются на том компьютере, на котором Вы в данный момент работаете. Если Вы возьмёте файл с рабочего компьютера, чтобы поработать с ним дома, то придётся еще раз создать такой же пользовательский список на домашнем компьютере. Если Вы использовали настраиваемый список, чтобы сделать сортировку, то его элементы останутся в ячейках Excel, но среди списков он показан не будет.
Использование пользовательских списков в Excel
Итак, наш настраиваемый список готов к использованию. Выделите ячейку и введите с клавиатуры любой элемент этого списка. Нажмите на маркер автозаполнения (небольшой квадратик в правом нижнем углу ячейки), и потяните за него, чтобы продолжить последовательность. Чуть правее я вписал “Monday”, затем нажал на маркер и потянул вправо, Excel заполнил ячейки автоматически.
Замечательное свойство этой функции состоит в том, что Excel одинаково заполнит как столбец, так и строку, как в прямом направлении, так и в обратном, начнёте Вы с первого элемента списка, с середины или с последнего элемента… Как только Вы щелкните и начнете протаскивать мышь, Excel определит, что Вы хотите сделать, и вставит соответствующие данные.
Сортировка по пользовательскому списку
Одна из приятных возможностей, которую дает работа с настраиваемыми списками, – это возможность упорядочить данные по любому из списков, сохранённых на Вашем компьютере. Кликните по одному или нескольким столбцам, затем нажмите Sort & Filter (Сортировка и фильтр), раскройте выпадающее меню Order (Порядок), кликните Custom List (Настраиваемый список) и выберите список, по которому желаете выполнить сортировку.
Сортировка может включать более одного столбца. Если Вы хотите добавить ещё один уровень и упорядочить сначала по месяцам, а затем по номеру счёта, то можете кликнуть Add Level (Добавить уровень) и определить, каким образом должны отображаться данные. Когда закончите, нажмите ОК . Теперь информация упорядочена по выбранному списку!
Все достаточно просто! Применение пользовательских списков очень удобно особенно для тех, кто хочет избежать многократного копирования и вставки данных. Если Вы знаете, что есть набор данных, который в любой момент может понадобиться, почему бы не сделать из него настраиваемый список? Кто знает, возможно это сохранит Вам гораздо больше времени, чем Вы можете предположить… и оставит шефа с мыслью, что Вам пришлось изрядно потрудиться. Пусть он так думает.
В данной статье рассмотрим, как создать раскрывающийся список в excel 2007. Возьмем пример, когда нам нужно в ячейке выбрать заданные значения от 1 до 5 из выпадающего списка. Создаем сам список и выделяем его левой кнопкой мыши. Кликаем правой кнопкой мыши в выделенной области и выбираем пункт Имя диапазона .
В открывшемся окошке в поле Имя вводим название нашего списка, назовем Значение . В поле Область из выпадающего списка выберем Книга (либо номер Листа к которому желаете применить список). Жмем Ок.
Так же, чтоб задать название списка, следует выделить имеющийся список и в поле имя ячейки внести, присвоить имя списка. Смотрим картинку ниже.
Список создан. Теперь применим этот список к ячейке.
Выделяем ячейку, к которой будет привязан список. В ленте переходим на вкладку Данные и в группе Работа с данными кликаем по кнопке Проверка данных . В следующем окошке во вкладке Параметры в поле Тип данных выбираем из списка пункт Список.
В поле Источник ставим знак «равно» и пишем название, которое присвоили списку. Список называется «Значение». Соответственно запись должна быть как показано на рисунке ниже.
Жмем Ок, и теперь получили ячейку с выпадающим списком. Как это выглядит, смотрим ниже на картинке. Когда щелкаем по ячейке, видим справа от нее квадратик с треугольничком вниз. Кликаем по квадратику и тем самым раскрываем список.
Можно создать , минуя присвоение названия списка. То есть:
- создаем сам список;
- переходим в Ленте на вкладку Данные , жмем кнопку Проверка данных;
- в открывшемся окошке, во вкладке Параметры в поле Тип данных выбираем Список;
- в поле Источник кликаем левой кнопкой мыши для активации данного поля. Далее выделяем ячейки образующие список;
- жмем Ок.
Все, раскрывающийся список в excel 2007 готов.
Под выпадающим списком понимается содержание в одной ячейке нескольких значений. Когда пользователь щелкает по стрелочке справа, появляется определенный перечень. Можно выбрать конкретное.
Очень удобный инструмент Excel для проверки введенных данных. Повысить комфорт работы с данными позволяют возможности выпадающих списков: подстановка данных, отображение данных другого листа или файла, наличие функции поиска и зависимости.
Создание раскрывающегося списка
Путь: меню «Данные» - инструмент «Проверка данных» - вкладка «Параметры». Тип данных – «Список».
Ввести значения, из которых будет складываться выпадающий список, можно разными способами:

Любой из вариантов даст такой результат.
Выпадающий список в Excel с подстановкой данных
Необходимо сделать раскрывающийся список со значениями из динамического диапазона. Если вносятся изменения в имеющийся диапазон (добавляются или удаляются данные), они автоматически отражаются в раскрывающемся списке.

Протестируем. Вот наша таблица со списком на одном листе:

Добавим в таблицу новое значение «елка».

Теперь удалим значение «береза».

Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.
Теперь сделаем так, чтобы можно было вводить новые значения прямо в ячейку с этим списком. И данные автоматически добавлялись в диапазон.



Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».
Нажмем «Да» и добавиться еще одна строка со значением «баобаб».
Выпадающий список в Excel с данными с другого листа/файла
Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.
- Делаем активной ячейку, куда хотим поместить раскрывающийся список.
- Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).
Имя файла, из которого берется информация для списка, заключено в квадратные скобки. Этот файл должен быть открыт. Если книга с нужными значениями находится в другой папке, нужно указывать путь полностью.
Как сделать зависимые выпадающие списки
Возьмем три именованных диапазона:

Это обязательное условие. Выше описано, как сделать обычный список именованным диапазоном (с помощью «Диспетчера имен»). Помним, что имя не может содержать пробелов и знаков препинания.
- Создадим первый выпадающий список, куда войдут названия диапазонов.
- Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.
- Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.
- Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("Е2:Е9" )) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(0, 1)) = 0 Then Target.Offset(0, 1) = Target Else Target.End (xlToRight).Offset(0, 1) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub
- Чтобы выбранные значения показывались снизу, вставляем другой код обработчика. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("Н2:К2" )) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(1, 0)) = 0 Then Target.Offset(1, 0) = Target Else Target.End (xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub
- Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препинания, применим такой модуль.



Выбор нескольких значений из выпадающего списка Excel
Бывает, когда из раскрывающегося списка необходимо выбрать сразу несколько элементов. Рассмотрим пути реализации задачи.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("C2:C5" )) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
newVal = Target
Application.Undo
oldval = Target
If Len(oldval) <> 0 And oldval <> newVal Then
Target = Target & "," & newVal
Else
Target = newVal
End If
If Len(newVal) = 0 Then Target.ClearContents
Application.EnableEvents = True
End If
End Sub
Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.
Выпадающий список с поиском

При вводе первых букв с клавиатуры высвечиваются подходящие элементы. И это далеко не все приятные моменты данного инструмента. Здесь можно настраивать визуальное представление информации, указывать в качестве источника сразу два столбца.
В этом уроке расскажу как сделать выпадающий список в Excel. В основном он создается с одной целью - ограничить возможность ввода данных определенным списком. Например, когда в таблице есть специальная ячейка, в которой вы отмечаете к какому отделу относиться тот или иной сотрудник. Этот список всегда один и тот же. Намного удобнее выбирать отдел из списка, чем вводить его каждый раз вручную или копировать из других ячеек.
Чтобы создать выпадающий список в Эксель мы воспользуемся опцией Проверка данных . Находится она во вкладке Данные .
После клика по иконке откроется новое окно Проверка вводимых значений . Здесь необходимо в выпадающем списке Тип данных выбрать Список , а что вводить в появившемся поле Источник мы сейчас и будем разбираться.
Создать список можно как минимум 3-я способами.
Указание элементов напрямую в источнике
Этот способ очень простой и подходит для маленьких списков.
- Становимся на ячейку, где нужно создать список;
- Входим в Проверить данные ;
- В поле Источник перечисляем элементы списка, которые разделяем точкой с запятой.
После этого нажимаем клавишу Ок и получаем готовый выпадающий список.
Эту ячейку можно спокойно использовать по всей таблице. Просто копируем ее и вставляем в нужном месте.
Элементы списка на том же листе
Этот способ позволяет использовать уже готовый список отделов, который есть на листе.
- Становимся на ячейку;
- Входим в Проверить данные ;
- Становимся на поле Источник и мышкой выбираем диапазон, который должен быть списком. Диапазон при это должен располагаться на этом же листе!.
Теперь эту ячейку можно просто скопировать и вставить во все строки таблицы.
Используем Именованный диапазон
Данный способ подойдет в том случае, если элементы списка размещаются на другом листе.
- Создаем перечень отделов на другом листе;
- Создаем Именованный диапазон. Выбираем диапазон с элементами списка. Слева от строки формул сейчас указана ячейка, с который вы начинали выделение. В моем случае - А2;
- Вместо А2 даем Имя нашему диапазону. Например, называем его Отделы . После этого нажимаем клавишу Enter , Поздравляю, мы создали Именованный диапазон .
Возвращаемся обратно на исходный лист. Становимся на ячейку, где будем создавать список. Заходим в "Данные -> Проверить данные". В поле Источник , через знак = вводим название созданного на предыдущем этапе диапазона Отделы .
В результате получаем список, элементы которого находятся на другом листе.
Поздравляю, теперь вы знаете как сделать выпадающий список в Эксель. Если будут вопросы, то с радостью постараюсь на них ответить.
В этом уроке расскажу о том, что такое специальная вставка в Excel и как ей пользоваться.
В этом уроке расскажу как поставить в начале ячейки знак плюс или ноль перед числом в Excel. Давайте представим ситацию, что вам необходимо ввести в ячейку номер телефона в формате "+7 987...". В обычном состоянии программа Эксель просто удалит этот знак плюс.
Добрый день, уважаемый читатель!
В этой статье, я хотел бы поговорить о том, что такое выпадающий список в ячейке, как его сделать, ну и соответственно для чего же он нужен?
Это список закреплённых значений, которые доступны только с указанного диапазона значений. Это означает, что в указанную вами ячейку могут попасть данные только соответствующие значениям заданного диапазона, данные, которые не соответствуют – вводиться, не будут. В ячейке появляется возможность выбора значений, которые предлагает фиксированный список в ячейке.
Ну что же, рассмотрим создание выпадающих списков и для чего же это нужно:

Я лично постоянно использую выпадающий список по всем 3 причинам. И она значительно упрощает мне работу с данными, я сознательно сокращаю к 0% возможность при введении первичных данных.
Ну вот 2 вопроса, что и для чего, я рассказал, а вот о том, как это сделать ниже и поговорим.
А делать выпадающий список в ячейке будем в несколько этапов:
1. Определяем диапазон ячеек, в которых мы будем создавать фиксированный список.
2. Выделяем нужный нам диапазон и в меню выбираем пункт “Данные” — “Проверка данных” , в появившемся контекстном окне выбираем из указанного выбора пункт “Список” .
3. В разблокированной ниже строке указываем диапазон данных, которые должны быть у нас в выпадающем списке. Нажимаем “Ок” и работа сделана.
В более старых версиях Excel, нет возможности формировать выпадающий список в ячейкеиспользуя данные других листов, поэтому имеет смысл создавать списки в том же листе и прятать их . Также при необходимости вы можете сформировать с вертикального списка – горизонтальный с помощью возможности .
А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!
Прогресс человечества основывается на желании каждого человека жить не по средствам
Сэмюэль Батлер, философ
Excel создать список
Создание и использование настраиваемых списков в Excel 2007 и 2010
Смотрите такжеListFillRange горизонтальный прямоугольник -СоздатьElse куда войдут названия Если этого не для проверки введенных указать имя диапазона городов линии между одинаковымиво всплывающем окне заполнение. Выделите этотНастраиваемые списки одного столбца. Если параметров Excel. те, которыми всеЕсли Вам приходится работать- диапазон ячеек, будущий список.. Введите имя (можноTarget = newVal диапазонов. сделать, Excel не данных. Повысить комфорт
- со странами. Введите2
- словами, например, отделяться
- Списки диапазон и, следуя
- Высокое, Среднее, Низкое Вы хотите добавить
- Для пользовательского списка можно
Создаем пользовательский список в Excel
так часто пользуются. с книгой Excel, откуда берутся данныеЩелкните по нарисованному списку любое, но обязательноEnd IfКогда поставили курсор в позволит нам вводить работы с данными в этом поле. Позже Вы увидите, строки по датам.. Для этого выберите
инструкциям выше, откройтеБольшое, Среднее, Малое ещё один уровень импортировать только текстовые Они не могут в которой постоянно
для списка. Выделить правой кнопкой мыши без пробелов иIf Len(newVal) = поле «Источник», переходим новые значения. позволяют возможности выпадающих «=Country» и жмите как этот индекс
Подробнее о таком во всплывающем окне всплывающее окно "Списки".Север, Юг, Восток, Запад и упорядочить сначала значения. Если Вам быть изменены или фигурирует один и мышью диапазон он и выберите команду начать с буквы!) 0 Then Target.ClearContents
на лист иВызываем редактор Visual Basic. списков: подстановка данных,ОК будет использован. способе визуализировать данные,СортировкаУбедитесь, что ссылка наСтарший менеджер по продажам, по месяцам, а нужно создать настраиваемый
удалены. Однако, если тот же список не даст, надоФормат объекта (Format control) для выделенного диапазонаApplication.EnableEvents = True выделяем попеременно нужные Для этого щелкаем отображение данных другого. Теперь нам нужноЕсли Вы работаете в смотрите в статьев столбце выделенные значения отображается Региональный менеджер по затем по номеру список с календарными Вы захотите удалить данных, или может просто вписать его. В появившемся диалоговом (например
Создаем список с нуля
End If ячейки. правой кнопкой мыши листа или файла, сделать второй раскрывающийся Excel 2010, то "Разделительная линия строкПорядок в окне продажам, Руководитель отдела счёта, то можете датами или числами, или изменить созданный быть Вы просто руками с клавиатуры окне задайтеТоварыEnd SubТеперь создадим второй раскрывающийся по названию листа наличие функции поиска список, чтобы пользователи
можете создать лист-источник в таблице Excel"пунктСписки продаж, Торговый представитель кликнуть то придётся использовать Вами список, это не хотите множество (например, Лист2!A1:A5)Формировать список по диапазону). НажмитеНе забываем менять диапазоны список. В нем и переходим по и зависимости. могли выбрать город.
Создание списка из существующего диапазона данных
в отдельной рабочей тут.Настраиваемый списокв полеНастраиваемый список может соответствоватьAdd Level поле можно сделать в раз использовать действиеLinkedCell- выделите ячейкиОК на «свои». Списки должны отражаться те вкладке «Исходный текст».Путь: меню «Данные» - Мы поместим этот книге. Если жеЭтот список можно, чтобы отобразить всплывающееИмпорт списка из ячеек диапазону ячеек, или(Добавить уровень) и
List entries любой момент. ДляКопировать- связанная ячейка, с наименованиями товаров,. создаем классическим способом. слова, которые соответствуют Либо одновременно нажимаем инструмент «Проверка данных» раскрывающийся список в у Вас версия пронумеровать. Смотрите о окно, и нажмите кнопку его можно ввести определить, каким образом
(Элементы списка). этого кликните по> куда будет выводиться которые должны попастьВыделите ячейки (можно сразу А всю остальную выбранному в первом клавиши Alt + - вкладка «Параметры».
ячейку Excel 2003 года, нескольких способах нумерацииСпискиИмпорт в диалоговом окне должны отображаться данные.Вот некоторые моменты, которые своему списку иВставить выбранный из списка в список несколько), в которых работу будут делать списке названию. Если F11. Копируем код Тип данных –B2 и Вы планируете списков в статье, а затем выделите.Списки Когда закончите, нажмите Вы должны знать
Использование пользовательских списков в Excel
делайте с ним, то было бы элементСвязь с ячейкой хотите получить выпадающий макросы. «Деревья», то «граб», (только вставьте свои «Список».. А теперь внимание использовать именованный диапазон, "Автонумерация в Excel" настраиваемый список иНа панели.ОК о настраиваемый списках…
что захотите. очень удобно иметьListRows- укажите ячейку список и выберитеНа вкладке «Разработчик» находим «дуб» и т.д. параметры).Private Sub Worksheet_Change(ByValВвести значения, из которых – фокус! Нам то значения должны здесь. нажмите кнопкуСпискиПримечание:. Теперь информация упорядочена Все списки привязываютсяУ меня есть два
Сортировка по пользовательскому списку
заранее настроенный и- количество отображаемых куда нужно выводить в меню (на инструмент «Вставить» – Вводим в поле Target As Range) будет складываться выпадающий нужно проверить содержимое находиться в тойЕщё в ExcelДобавитьпоявятся выбранные вами Настраиваемый список может содержать по выбранному списку! к компьютеру. Их способа, как создать сохранённый список, чтобы строк порядковый номер выбранного
вкладке) «ActiveX». Здесь нам «Источник» функцию вида Dim lReply As список, можно разными ячейки с названием же книге, можно можно создать выпадающий. элементы. только текст илиВсе достаточно просто! Применение настройки сохраняются на настраиваемый список. Я Excel мог помочьFont пользователем элемента.
Данные - Проверка (Data нужна кнопка «Поле =ДВССЫЛ(E3). E3 – Long If Target.Cells.Count способами: страны (ячейка B1), на другом листе. список. Он будетСоздание списка последовательных дат"Параметры" > "Дополнительно" > "Общие" > текст с числами. пользовательских списков очень том компьютере, на могу создать его Вам в работе.- шрифт, размер,Количество строк списка - Validation) со списком» (ориентируемся ячейка с именем > 1 ThenВручную через «точку-с-запятой» в
чтобы получить индекс
Мы будем использовать именованные появляться, гогда нажмем
Рассмотрим,
"Изменить списки". Если
Чтобы создать настраиваемый
office-guru.ru
Создание или удаление настраиваемого списка для сортировки и подстановки данных
удобно особенно для котором Вы в вручную, введя каждое Использование настраиваемых списков начертание (курсив, подчеркивание- сколько строк. Из выпадающего списка на всплывающие подсказки). первого диапазона. Exit Sub If
Сведения о настраиваемых списках
поле «Источник». соответствующий базе данных диапазоны и сделаем на ячейку. Подробнеекак создать список из вы используете Excel
Сравнение встроенных и настраиваемых списков
список, содержащий только тех, кто хочет данный момент работаете. значение в поле
– это хорошее |
и т.д. кроме показывать в выпадающем |
Тип данных (Allow)Щелкаем по значку – |
Бывает, когда из раскрывающегося Target.Address = "$C$2"Ввести значения заранее. А с городами. Если |
так, чтобы эти смотрите в статье таблицы Excel 2007, нажмите кнопку |
числа, например от избежать многократного копирования Если Вы возьмёте
List entries решение для данной цвета) списке. По умолчаниювыберите вариант становится активным «Режим списка необходимо выбрать Then If IsEmpty(Target) в качестве источника пользователь выберет связанные выпадающие списки "Выпадающий список в
. В Excel можно |
Office и выберите |
0 до 100, |
и вставки данных. |
файл с рабочего(Элементы списка) и ситуации. Далее яForeColor |
- 8, ноСписок (List) конструктора». Рисуем курсором сразу несколько элементов. Then Exit Sub указать диапазон ячеек
Portugal работали во всех Excel". сделать список из "Параметры Excel" > "Популярные" > нужно сначала создать Если Вы знаете, компьютера, чтобы поработать нажав покажу, как создатьи
Создание настраиваемого списка
можно больше, чегои введите в (он становится «крестиком») Рассмотрим пути реализации If WorksheetFunction.CountIf(Range("Деревья"), Target) со списком., то мы должны версиях Excel. СледующийВ Excel есть
Введение значений напрямую
таблицы или сделать "Изменить списки"." /> список чисел в
что есть набор с ним дома,Add настраиваемый список вBackColor не позволяет предыдущий строчку небольшой прямоугольник – задачи. = 0 ThenНазначить имя для диапазона обратиться к базе шаг – создать функции, которые помогут
в ячейке выпадающийДва раза нажмите кнопку текстовом формате. данных, который в
то придётся еще(Добавить). Если Вы Excel.- цвет текста способ.Источник (Source) место будущего списка.Создаем стандартный список с lReply = MsgBox("Добавить значений и в
с индексом именованные диапазоны для быстро заполнить большой список. Рассмотрим варианты.ОКСоздать настраиваемый список можно любой момент может раз создать такой выберете этот путь,
Создаем пользовательский список в и фона, соответственно
После нажатия назнак равенства иЖмем «Свойства» – открывается помощью инструмента «Проверка
введенное имя " поле источник вписать3 наших списков. На
диапазон таблицы данными, Вот наша таблица..
Создание настраиваемого списка на основе диапазона ячеек
двумя способами. Если
понадобиться, почему бы же пользовательский список то столкнетесь с ExcelБольшим и жирным плюсомОК имя диапазона (т.е. перечень настроек.
данных». Добавляем в & _ Target это имя., в которой хранятся вкладке не используя копирование.Сначала мы создаемПримечание: список короткий, можно
не сделать из на домашнем компьютере. некоторыми ограничениями. ПолеСоздаем список с нуля
этого способа являетсясписком можно пользоваться.=ТоварыВписываем диапазон в строку исходный код листа & " вЛюбой из вариантов даст
названия городов Португалии.Formulas Подробнее об этих
сводную таблицу с Настраиваемый список можно создать ввести его значения него настраиваемый список? Если Вы использовалиList entriesСоздание списка из существующего возможность быстрого переходаЧтобы вместо порядкового номера). ListFillRange (руками). Ячейку,
Удаление настраиваемого списка
готовый макрос. Как
выпадающий список?", vbYesNo такой результат. Мы воспользуемся функцией
(Формулы) есть команда функциях смотрите в помощью функции «Мастер только на основе прямо во всплывающем Кто знает, возможно
Хранение настраиваемых списков
настраиваемый список, чтобы(Элементы списка) позволяет диапазона данных к нужному элементу элемента выводилось егоНажмите куда будет выводиться это делать, описано + vbQuestion) IfВПРName Manager статье "Заполнить быстро сводных таблиц и значений, таких как окне. Если список это сохранит Вам
сделать сортировку, то ввести не болееИспользование пользовательских списков в в списке при название можно дополнительноОК выбранное значение – выше. С его lReply = vbYesНеобходимо сделать раскрывающийся список(VLOOKUP) для поиска(Диспетчер имён). Нажав диапазон, массив в диаграмм». текст, числа, даты длинный, можно импортировать гораздо больше времени, его элементы останутся 255 символов. Будьте Excel вводе первых букв
использовать функцию. в строку LinkedCell. помощью справа от Then Range("Деревья").Cells(Range("Деревья").Rows.Count + со значениями из значения из ячейки на нее, откроется Excel".Внимание! и время. На значения из диапазона чем Вы можете в ячейках Excel, внимательны к тому,Сортировка по пользовательскому списку с клавиатуры(!), чегоИНДЕКС (INDEX)Все! Наслаждайтесь! Для изменения шрифта выпадающего списка будут 1, 1) = динамического диапазона. Если
См. также
support.office.com
Как создать список в Excel из таблицы.
диалоговое окноПРЕДСТАВЬТЕ СИТУАЦИЮ:Это не функция основе формата, например ячеек. предположить… и оставит но среди списков из скольких символовЯ продемонстрирую это на Target End If
вносятся изменения вв таблице сName ManagerМы хотим создать «Сводная таблица» на значков, цвета ячейкиЧтобы создать настраиваемый список шефа с мыслью, он показан не состоит каждая Ваша
примере списка школьных остальных способов. Приятным содержимое нужной по источника данных для
Font. Sub Worksheet_Change(ByVal Target End If End имеющийся диапазон (добавляются
названиями стран. После(Диспетчер имён). в Excel небольшую
закладке «Вставка». Это или цвета шрифта, этим способом, выполните что Вам пришлось будет.
запись! клубов. Мне нужно моментом, также, является
счету ячейки из
списка может выступатьСкачать пример выпадающего списка As Range) On If End Sub или удаляются данные), того как индексНажмите кнопку табличку, где можно


в раскрывающемся списке. выберем список, который(Создать), чтобы добавить соответствующий ей город. установить эту кнопкуВыполните указанные ниже действия.
Excel 2010Наслаждайтесь! Выделите ячейку и вводить список в из них, а и т.д.) предыдущий. Основное отличие Тогда при дописывании
подходящие элементы. И Range("Е2:Е9")) Is NothingПереходим на лист соВыделяем диапазон для выпадающего станет источником данных новый именованный диапазон. При этом с функции, смотрите вПо приведенным выше инструкциями более поздних
excel-office.ru
Создаем связанные выпадающие списки в Excel – самый простой способ!
Урок подготовлен для Вас введите с клавиатуры поле также наметить ежедневныйПри использовании этого способа, в том, что новых товаров к это далеко не And Target.Cells.Count = списком. Вкладка «Разработчик» списка. В главном для нашего второго Откроется диалоговое окно помощью выпадающих списков, статье «Сводная таблица откройте диалоговое окно версиях выберите пункты командой сайта office-guru.ru любой элемент этого
List entries бюджет. Я начал также возможно указывать на лист добавляется прайсу, они будут все приятные моменты 1 Then Application.EnableEvents - «Код» - меню находим инструмент выпадающего списка. ДляNew Name необходимо ограничить доступные в Excel из "Списки".Файл
Источник: https://www.ablebits.com/office-addins-blog/2011/11/02/create-excel-custom-lists/ списка. Нажмите на(Элементы списка) вручную, с того, что в качестве не элемент управления, автоматически добавляться к данного инструмента. Здесь = False If «Макросы». Сочетание клавиш «Форматировать как таблицу». этого напишем такую(Создание имени). пользователям варианты стран нескольких таблиц».Выделите список, который нужно >Перевел: Антон Андронов маркер автозаполнения (небольшой не ставьте лишние ввёл названия клубов,ListFillRange а элемент ActiveX выпадающему списку. Еще можно настраивать визуальное Len(Target.Offset(0, 1)) = для быстрого вызоваОткроются стили. Выбираем любой. формулу:В поле
и городов, изСоздаем сводную таблицу, удалить, в полеПараметрыАвтор: Антон Андронов квадратик в правом пробелы между элементами. – это тане только одномерные"Поле со списком" одним часто используемым представление информации, указывать 0 Then Target.Offset(0,
– Alt + Для решения нашей=CHOOSE(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),England,France,Portugal)Name которых они могут указывая в диалоговыхСписки >Для сортировки или заполнения нижнем углу ячейки), Если пробелы будут информация, которая мне диапазоны. Можно, напримериз раскрывающегося набора трюком для таких в качестве источника 1) = Target F8. Выбираем нужное
задачи дизайн не=ВЫБОР(ВПР(B1;Sheet3!$A$3:$B$5;2;ЛОЖЬ);England;France;Portugal)(Имя) введите имя выбирать. В первой окнах «Мастера сводныхи нажмите кнопкуДополнительно
значений в пользовательском и потяните за стоять до или будет нужна постоянно. задать диапазон из под кнопкой списков является создание сразу два столбца. Else Target.End(xlToRight).Offset(0, 1) имя. Нажимаем «Выполнить». имеет значения. Наличие
Country ячейке мы сделаем таблиц и диаграмм»:Удалить
> порядке можно применять него, чтобы продолжить
после элемента, тоВ этот момент я двух столбцов иВставить (Insert) связанных выпадающих списковУ кого мало времени = Target EndКогда мы введем в заголовка (шапки) важно. формула? Она ищетдля нашего первого выбор страны, аНа первом шаге.Общие настраиваемые списки. В последовательность. Чуть правее
Microsoft их просто бы также запустил нескольких строк, указав
с вкладки (когда содержимое одного и нужно быстро If Target.ClearContents Application.EnableEvents пустую ячейку выпадающего В нашем примере значение из ячейки именованного диапазона, а во второй будут – «В несколькихНастраиваемые списки добавляются в > Excel есть встроенные я вписал «Monday», не станет учитывать, проверку орфографии, это дополнительно, что выводитьРазработчик (Developer) списка меняется в ухватить суть - = True End списка новое наименование, это ячейка А1B1 в поле доступны только принадлежащие диапазонах консолидации», «Сводная реестр компьютера, чтобыИзменить списки списки дней недели затем нажал на а если между делается простым нажатием нужно два столбца: зависимости от выбора смотрим обучающее видео: If End Sub появится сообщение: «Добавить со словом «Деревья».в списке странRefers to выбранной стране города. таблица». их можно было. и месяцев года, маркер и потянул словами одного элемента,F7 (свойствоМеханизм добавления тот же в другом).Кому интересны подробности иЧтобы выбранные значения показывались
и возвращает соответствующий(Диапазон) выберите тот, Думаю, это понятно?На шаге 2а использовать в другихВ но вы можете вправо, Excel заполнил то все они.ColumnCount - выбираем объект
Этот способ представляет собой нюансы всех описанных снизу, вставляем другой
в выпадающий список?». выбрать стиль таблицы индекс, который затем в котором хранитсяИтак, давайте начнём наш – «создать поля книгах. Если выExcel 2007 создавать и свои ячейки автоматически.
будут сохранены.Затем я выделил эту=2). Тогда можно получить из списка и вставку на лист способов - дальше код обработчика.Private SubНажмем «Да» и добавиться со строкой заголовка.
использует функция список стран:
простой пример с
страницы».
используете настраиваемый список
office-guru.ru
Выпадающий список в Excel с помощью инструментов или макросов
нажмите настраиваемые списки.Замечательное свойство этой функцииДругой способ добавить элементы область, нажал на весьма привлекательные результаты, рисуем его на
нового объекта - по тексту. Worksheet_Change(ByVal Target As еще одна строка Получаем следующий видCHOOSE=Sheet3!$A$3:$A$5 того, как можноНа шаге 2б при сортировке данных,
Создание раскрывающегося списка
кнопку Microsoft OfficeЧтобы понять, что представляют состоит в том, в настраиваемый список кнопку

окупающие все потраченные листе. А вот элемента управления "полеОдин щелчок правой кнопкой
- Range) On Error со значением «баобаб».
- диапазона:(ВЫБОР), чтобы выбратьНажмите создать связанный (или
- указываем только диапазон он также сохраняетсяи выберите пункты собой настраиваемые списки,


что Excel одинаково – импортировать данные.
OfficeВыпадающий список в Excel с подстановкой данных
на дополнительные настройки дальше начинаются серьезные со списком" с мыши по пустой Resume Next IfКогда значения для выпадающегоСтавим курсор в ячейку, 1-й, 2-й или
- ОК зависимый) выпадающий список нашей таблицы и вместе с книгой,
- Параметры Excel полезно ознакомиться с заполнит как столбец, Если Вы выделитеи в нижней усилия: отличия от предыдущего последующей привязкой его ячейке под столбцом Not Intersect(Target, Range("Н2:К2")) списка расположены на где будет находиться 3-й именованный диапазон.
- , чтобы сохранить и в Excel? В все. Поля строки поэтому его можно > принципами их работы так и строку, их прежде чем

части открывшегося менюСпособ 1. способа.

к диапазонам на с данными, команда

Is Nothing And

другом листе или выпадающий список. ОткрываемВот так будет выглядеть
закрыть диалоговое окно. ячейке не указываем. использовать на другихПопулярные и хранения на как в прямом

- откроете окно меню, щелкнул поПримитивныйВо-первых, созданный выпадающий ActiveX листе. Для этого:
- контекстного меню Target.Cells.Count = 1 в другой книге, параметры инструмента «Проверка наш второй раскрывающийся
- Имена диапазонам, содержащим города,B1На третьем шаге компьютерах, в том> компьютере. направлении, так и
- выбранный диапазон будетExcel OptionsСпособ 2. список может находитсяВ Excel 2007/2010 откройтеВыбрать из раскрывающегося списка Then Application.EnableEvents = стандартный способ не данных» (выше описан список: можно присвоить точномы будем выбирать указываем - куда числе на серверахОсновные параметры работы сВ Excel есть указанные в обратном, начнёте автоматически вставлен в(Параметры Excel).Стандартный в двух принципиально вкладку(Choose from drop-down list) False If Len(Target.Offset(1, работает. Решить задачу путь). В полеВ результате мы получим таким же образом. страну, а в поместить сводную таблицу. с службы Excel, Excel ниже встроенные списки
- Вы с первого соответствующее поле. Вам
- В разделеСпособ 3. разных состояниях -Разработчик (Developer)или нажать сочетание 0)) = 0 можно с помощью «Источник» прописываем такую

два связанных (илиТеперь мы можем создать ячейкеВсё, таблица составлена. для которых может >
дней недели и элемента списка, с останется только нажать
Выпадающий список в Excel с данными с другого листа/файла
PopularЭлемент управления режиме отладки, когда. В более ранних клавиш Then Target.Offset(1, 0) функции ДВССЫЛ: она функцию: зависимых) выпадающих списка. выпадающие списки вB2
- Справа от сводной быть опубликована вашаИзменить списки
- месяцев года. середины или сImport
(Общие) Вы найдётеСпособ 4. можно настраивать его версиях - панельALT+стрелка вниз = Target Else сформирует правильную ссылкуПротестируем. Вот наша таблица Если мы выбираем тех ячейках, где
Как сделать зависимые выпадающие списки
– принадлежащий ей

таблицы появилось окно книга..Встроенные списки последнего элемента… Как(Импорт) и Excel пунктЭлемент ActiveX
- параметры и свойства, инструментов. Способ не работает,
- Target.End(xlDown).Offset(1, 0) = на внешний источник со списком на страну планировали выбирать данные.
- город, как на «Список полей своднойОднако, открыв книгу наВыберите в полеПн, Вт, Ср, Чт, только Вы щелкните создаст список изCreate lists for useСложность двигать его поФормы (Forms) если ячейку и Target End If
Выбор нескольких значений из выпадающего списка Excel
информации. одном листе:France Выделите ячейку примере:
- таблицы». Убираем галочки другом компьютере илиСписки Пт, Сб, Вс и начнете протаскивать содержащегося в ячейках in sorts andнизкая листу и менятьчерез меню столбец с данными Target.ClearContents Application.EnableEvents =Делаем активной ячейку, кудаДобавим в таблицу новое, в связанном спискеB1Для начала нужно создать у слов «Строка», сервере, вы непунктПонедельник, Вторник, Среда, Четверг, мышь, Excel определит, текста. Если Вы fills sequencesсредняя размеры и -
- Вид - Панели инструментов отделяет хотя бы True End If хотим поместить раскрывающийся значение «елка». у нас будут(в ней мы базу данных. На «Столбец». Остается галочка найдете настраиваемый список,НОВЫЙ СПИСОК Пятница, Суббота, Воскресенье что Вы хотите не выделили текст(Создавать списки длявысокая режиме ввода, когда - Формы (View одна пустая строка
- End Sub список.Теперь удалим значение «береза». города только из будем выбирать страну),
- выбирать из Forms) товар, который еще в одной ячейке, В поле «Источник» «умная таблица», которая
Выпадающий список с поиском
- Из этой статьи ВыData которые хочу датьИ, от нашей своднойПараметрах ExcelЭлементы списка
- авг, сен, окт,Одна из приятных возможностей, с кнопкойEdit Custom Listsнет него данные. Переключение
- . Если этой вкладки ни разу не
- разделенные любым знаком вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”). легка «расширяется», меняется. узнали, как можно(Данные), нажмите пользователям на выбор таблицы осталась тольково всплывающем окне
, начиная с первого
ноя, дек которую дает работаImport(Изменить списки).нет между этими режимами не видно, то вводился выше: препинания, применим такойИмя файла, из которого
exceltable.com
Выпадающий список в ячейке листа
Видео
Теперь сделаем так, чтобы сделать простейшие связанныеData Validation в первом раскрывающемся
итоговая сумма. НажимаемСписки элемента.Январь, Февраль, Март, Апрель,
Способ 1. Примитивный
с настраиваемыми списками,(Импорт) и выделитеЕсли Вы работаете вда происходит с помощью нажмите кнопкуВыделите ячейки с данными, модуль. берется информация для можно было вводить выпадающие списки в(Проверка данных), а списке, а в двойным щелчком мыши. Настраиваемые списки будутПосле ввода каждого элемента Май, Июнь, Июль, – это возможность ячейки с данными
Способ 2. Стандартный
- Excel 2010, тоКоличество отображаемых строк кнопкиОфис - Параметры Excel
- которые должны попастьPrivate Sub Worksheet_Change(ByVal списка, заключено в новые значения прямо Microsoft Excel. Вы затем в выпадающем соседнем столбце указал на ячейку с отображаться только в нажимайте клавишу ВВОД. Август, Сентябрь, Октябрь, упорядочить данные по для нового списка. Вам нужен другойвсегда 8Режим Конструктора (Design Mode)- в выпадающий список Target As Range) квадратные скобки. Этот в ячейку с можете взять этот меню выберите числовой индекс, который итоговой суммой (у диалоговом окне
- Завершив создание списка, нажмите Ноябрь, Декабрь любому из списков,Помните, у нас было маршрут. Откройте вкладкулюбоена вкладкефлажок (например, наименованиями товаров).On Error Resume файл должен быть этим списком. И простой пример иData Validation соответствует одному из нас, в примереСортировка кнопкуПримечание:
сохранённых на Вашем ограничение на количествоFile
Быстрый поиск элемента по
Разработчик (Developer)Отображать вкладку Разработчик наЕсли у вас Excel Next открыт. Если книга данные автоматически добавлялись использовать его для(Проверка данных). списков городов. Списки – это 4476).в столбцеДобавить Изменить или удалить встроенный компьютере. Кликните по символов, которое можно(Файл) и нажмите первым буквам:
Способ 3. Элемент управления
ленте (Office Button 2003 или старшеIf Not Intersect(Target, с нужными значениями в диапазон. решения реальных задач.Откроется диалоговое окно городов располагаются правее
- На новой страницеПорядок. список невозможно. одному или нескольким ввести в полеOptionsнетЕсли эта кнопка нажата, - Excel Options - выберите в Range("C2:C5")) Is Nothing находится в другойСформируем именованный диапазон. Путь:Урок подготовлен для ВасData Validation в столбцах появился такой список.. Настраиваемый список, хранимыйНа панелиВы также можете создать столбцам, затем нажмитеList entries(Параметры). Затем пролистайте
- нет то мы можем - Show Developer меню And Target.Cells.Count = папке, нужно указывать «Формулы» - «Диспетчер
командой сайта office-guru.ru(Проверка вводимых значений).DТеперь можно убрать - в файле книги,Списки свой настраиваемый списокSort & Filter(Элементы списка)? Только вниз, найдите кнопку
- да настраивать параметры выпадающего Tab in theВставка - Имя - 1 Then
- путь полностью. имен» - «Создать».Источник: http://www.excel-user.com/2011/02/cascading-validation-lists.htmlМы хотим дать пользователю,
- фильтры, цвет ячеек, также недоступен непосредственнопоявятся введенные вами и использовать его(Сортировка и фильтр), не при импорте!Edit Custom ListsНеобходимость использования дополнительной функции
списка, нажав соседнюю Ribbon) Присвоить
Application.EnableEvents = FalseВозьмем три именованных диапазона: Вводим уникальное названиеПеревел: Антон Андронов на выбор списокF т.д. Цвет ячеек для команды элементы.
Способ 4. Элемент ActiveX
для сортировки или раскройте выпадающее меню Теперь максимальный размер(Изменить списки).ИНДЕКС кнопкуНайдите значок выпадающего списка(Insert - Name -newVal = TargetЭто обязательное условие. Выше диапазона – ОК.Автор: Антон Андронов вариантов, поэтому в
и здесь убирается изЗаполнитьНажмите два раза кнопку заполнения. Например, чтобыOrder списка где-то 2000Далее откроется диалоговое окно,
нетСвойства (Properties) среди элементов управления Define),Application.Undo описано, как сделатьСоздаем раскрывающийся список вПод выпадающим списком понимается полеH закладки «Работа с.ОК отсортировать или заполнить(Порядок), кликните символов! Нажмите в котором можнода, которая откроет окно форм (не ActiveX!).если Excel 2007
oldval = Target обычный список именованным любой ячейке. Как содержание в однойAllow. Так, например, рядом таблицами» -> «Конструктор»При необходимости можно добавить. значения по приведенным
Custom ListОК добавить информацию, которую
- нет со списком всех Ориентируйтесь по всплывающим или новее -If Len(oldval) <> диапазоном (с помощью это сделать, уже ячейке нескольких значений.(Тип данных) выберите
- с -> «Стили таблиц». такой список вВыполните указанные ниже действия. ниже спискам, нужен
- (Настраиваемый список) и, чтобы закрыть окно планируется использовать многократно.
- Возможность создания связанных выпадающих возможных настроек для подсказкам - откройте вкладку 0 And oldval
- «Диспетчера имен»). Помним, известно. Источник – Когда пользователь щелкаетListFrance
Получился такой список. реестр компьютера илиВ диапазоне ячеек введите настраиваемый список, так выберите список, по с параметрами списка, Если Вы посмотрите списков выделенного объекта:Поле со спискомФормулы (Formulas) <> newVal Then что имя не
имя диапазона: =деревья. по стрелочке справа,(Список). Это активируетстоит индексМожно настроить таблицу сервера, чтобы он сверху вниз значения, как соответствующего естественного которому желаете выполнить и ещё раз на списки, уженетСамые нужные и полезные:и воспользуйтесь кнопкойTarget = Target может содержать пробеловСнимаем галочки на вкладках
Итоговая сравнительная таблица всех способов
появляется определенный перечень. поле | 2 так, что автоматически | был доступен в по которым нужно | порядка значений не сортировку. |
ОК | подготовленные Microsoft, то | да | свойства, которые можно |
Щелкните по значкуДиспетчер имен (Name Manager) | & "," & | и знаков препинания. | «Сообщение для ввода», |
Можно выбрать конкретное. | Source | , который соответствует списку | |
будут ставиться разделительныеПараметрах Excel | выполнить сортировку или | существует. | Сортировка может включать более |
, чтобы закрыть окно увидите среди них | нет | и нужно настроить: | и нарисуйте небольшой |
, затем newVal | Создадим первый выпадающий список, | «Сообщение об ошибке». | Очень удобный инструмент Excel |
planetaexcel.ru
(Источник), где необходимо
Создание взаимосвязанных раскрывающихся списков. Связанные выпадающие списки
Если Вам приходится работать с книгой Excel, в которой постоянно фигурирует один и тот же список данных, или может быть Вы просто не хотите множество раз использовать действие Копировать > Вставить , то было бы очень удобно иметь заранее настроенный и сохранённый список, чтобы Excel мог помочь Вам в работе. Использование настраиваемых списков – это хорошее решение для данной ситуации. Далее я покажу, как создать настраиваемый список в Excel.
Создаем пользовательский список в Excel
Я продемонстрирую это на примере списка школьных клубов. Мне нужно вести учёт денег на балансе каждого из них, а также наметить ежедневный бюджет. Я начал с того, что ввёл названия клубов, – это та информация, которая мне будет нужна постоянно.
В этот момент я бы также запустил проверку орфографии, это делается простым нажатием F7 .
Затем я выделил эту область, нажал на кнопку Office и в нижней части открывшегося меню щелкнул по Excel Options (Параметры Excel).
В разделе Popular (Общие) Вы найдёте пункт Create lists for use in sorts and fills sequences (Создавать списки для сортировки и заполнения) – кликните по Edit Custom Lists (Изменить списки).
Если Вы работаете в Excel 2010, то Вам нужен другой маршрут. Откройте вкладку File (Файл) и нажмите Options (Параметры). Затем пролистайте вниз, найдите кнопку Edit Custom Lists (Изменить списки).
Далее откроется диалоговое окно, в котором можно добавить информацию, которую планируется использовать многократно. Если Вы посмотрите на списки, уже подготовленные Microsoft, то увидите среди них те, которыми все так часто пользуются. Они не могут быть изменены или удалены. Однако, если Вы захотите удалить или изменить созданный Вами список, это можно сделать в любой момент. Для этого кликните по своему списку и делайте с ним что захотите.
Создаем список с нуля
У меня есть два способа, как создать настраиваемый список. Я могу создать его вручную, введя каждое значение в поле List entries (Элементы списка) и нажав Add (Добавить). Если Вы выберете этот путь, то столкнетесь с некоторыми ограничениями. Поле List entries (Элементы списка) позволяет ввести не более 255 символов. Будьте внимательны к тому, из скольких символов состоит каждая Ваша запись!
Подсказка: Если вы планируете вводить список в поле List entries (Элементы списка) вручную, не ставьте лишние пробелы между элементами. Если пробелы будут стоять до или после элемента, то Microsoft их просто не станет учитывать, а если между словами одного элемента, то все они будут сохранены.
Создание списка из существующего диапазона данных
Другой способ добавить элементы в настраиваемый список – импортировать данные. Если Вы выделите их прежде чем откроете окно меню, выбранный диапазон будет автоматически вставлен в соответствующее поле. Вам останется только нажать Import (Импорт) и Excel создаст список из содержащегося в ячейках текста. Если Вы не выделили текст заранее, поставьте курсор в поле рядом с кнопкой Import (Импорт) и выделите ячейки с данными для нового списка.
Помните, у нас было ограничение на количество символов, которое можно ввести в поле List entries (Элементы списка)? Только не при импорте! Теперь максимальный размер списка где-то 2000 символов! Нажмите ОК , чтобы закрыть окно с параметрами списка, и ещё раз ОК , чтобы закрыть окно параметров Excel.
Для пользовательского списка можно импортировать только текстовые значения. Если Вам нужно создать настраиваемый список с календарными датами или числами, то придётся использовать поле List entries (Элементы списка).
Вот некоторые моменты, которые Вы должны знать о настраиваемый списках… Все списки привязываются к компьютеру. Их настройки сохраняются на том компьютере, на котором Вы в данный момент работаете. Если Вы возьмёте файл с рабочего компьютера, чтобы поработать с ним дома, то придётся еще раз создать такой же пользовательский список на домашнем компьютере. Если Вы использовали настраиваемый список, чтобы сделать сортировку, то его элементы останутся в ячейках Excel, но среди списков он показан не будет.
Использование пользовательских списков в Excel
Итак, наш настраиваемый список готов к использованию. Выделите ячейку и введите с клавиатуры любой элемент этого списка. Нажмите на маркер автозаполнения (небольшой квадратик в правом нижнем углу ячейки), и потяните за него, чтобы продолжить последовательность. Чуть правее я вписал “Monday”, затем нажал на маркер и потянул вправо, Excel заполнил ячейки автоматически.
Замечательное свойство этой функции состоит в том, что Excel одинаково заполнит как столбец, так и строку, как в прямом направлении, так и в обратном, начнёте Вы с первого элемента списка, с середины или с последнего элемента… Как только Вы щелкните и начнете протаскивать мышь, Excel определит, что Вы хотите сделать, и вставит соответствующие данные.
Сортировка по пользовательскому списку
Одна из приятных возможностей, которую дает работа с настраиваемыми списками, – это возможность упорядочить данные по любому из списков, сохранённых на Вашем компьютере. Кликните по одному или нескольким столбцам, затем нажмите Sort & Filter (Сортировка и фильтр), раскройте выпадающее меню Order (Порядок), кликните Custom List (Настраиваемый список) и выберите список, по которому желаете выполнить сортировку.
Сортировка может включать более одного столбца. Если Вы хотите добавить ещё один уровень и упорядочить сначала по месяцам, а затем по номеру счёта, то можете кликнуть Add Level (Добавить уровень) и определить, каким образом должны отображаться данные. Когда закончите, нажмите ОК . Теперь информация упорядочена по выбранному списку!
Все достаточно просто! Применение пользовательских списков очень удобно особенно для тех, кто хочет избежать многократного копирования и вставки данных. Если Вы знаете, что есть набор данных, который в любой момент может понадобиться, почему бы не сделать из него настраиваемый список? Кто знает, возможно это сохранит Вам гораздо больше времени, чем Вы можете предположить… и оставит шефа с мыслью, что Вам пришлось изрядно потрудиться. Пусть он так думает.
Для работы с большим количеством данных, проведения различных расчетов и оформления информации в виде таблиц пользователи в основном используют программу Excel. Для проведения расчетов и оформления все необходимые данные вводятся вручную в соответствующую ячейку на листе и для некоторого упрощения этого процесса можно сделать выпадающий список в Excel. Но его настройка также связана с первоначальным ручным вводом всех данных и далее мы более подробно рассмотрим, как сделать выпадающий список в Excel, причем, чтобы сам список допустимых значений находился на другом листе, не загромождая основной лист с данными.
Раскрывающийся список Excel создается в первую очередь для предотвращения ввода неверной информации, предоставляя для ввода только значения из созданного списка. Сделать выпадающий список в Экселе довольно просто. Самое сложное это подготовить полный список допустимых значений для ввода, оформив его в виде одного столбца или строки без пустых ячеек.
Рассмотрим для начала простой пример, как сделать выпадающий список в Excel 2010, создав список допустимых значений на том же листе. Для примера создадим список, в котором обозначен определенный диапазон чисел, ограничивающий устанавливаемые числа в необходимой нам ячейке. После этого устанавливаем курсор в том месте, где должен быть раскрывающийся список Excel, и переходим на вкладку «Данные» , где в секторе «Работа с данными» находим пункт «Проверка данных» . Если вы нажмете на стрелочку, появится дополнительное меню, в котором есть такой же пункт «Проверка данных» .
После выбора этого пункта появляется окошко, в котором необходимо настроить проверку вводимых данных в закладке «Параметры» . Для этого в графе «Тип данных:» выбираем «Список» , и нажав на соответствующий значок в графе «Источник:» , выделяем диапазон ячеек с нашим подготовленным списком допустимых значений. После этого при активации выбранной нами ячейки будет появляться стрелка, при нажатии на которую появляется список допустимых значений, которые можно выбрать.
При желании можно также настроить различные подсказки, что именно указывается в ячейке с выпадающим списком. При настройке выпадающего списка в Экселе доступно два пункта «Заголовок:» и «Сообщение:» , которые можно ввести в закладке «Сообщение для ввода» после выбора в меню пункта «Проверка данных» .
Теперь рассмотрим, как сделать выпадающий список в Excel 2010, если список допустимых значений для ввода расположен на другом листе этого же документа Эксель. На самом деле все очень просто. Проделываем все то же самое, как описано выше, и когда доходим до выбора источника данных, просто переходим на необходимый лист и выделяем диапазон ячеек.
Есть и более замороченный способ. Диапазону ячеек на другом листе можно присвоить имя. Для этого их выделяем, переходим на вкладку «Формулы» и выбираем «Присвоить имя» . Теперь для выбора диапазона допустимых значений достаточно указать имя диапазона ячеек после знака равенства.
При создании таблиц порой очень удобно использовать выпадающие (иначе говоря — раскрывающиеся) списки. Excel 2010 позволяет это делать несколькими способами. Рассмотрим их.
Способ 1. Создаем раскрывающийся список в Excel 2010, используя инструмент «Проверка данных»
Этот способ считается стандартным, поскольку он прост и удобен.
1. На свободном месте листа запишите все элементы раскрывающегося списка в столбик, каждый элемент — в своей ячейке.
2. Задайте имя диапазону ячеек. Для этого:
- кликните верхнюю ячейку списка и, зажав левую кнопку мыши, тяните курсор вниз, пока весь список не будет выделен.
- установите курсор в поле «Имя», слева от строки формул;
- введите имя списка и нажмите Enter.
Обратите внимание, имя списка всегда должно начинаться с буквы и не содержать пробелов.
3. Выделите ячейку в таблице, где должен будет размещаться выпадающий список.
4. Откройте вкладку «Данные» и нажмите кнопку «Проверка данных». В открывшемся окне перейдите на вкладку «Параметры». В выпадающем списке «Тип данных» выберите пункт «Список».
5. В строке «Источник» нужно указать адрес, откуда будет взяты элементы создаваемого списка. Адресом будет имя, которое вы присвоили диапазону ячеек. Задать адрес можно несколькими способами.
- Вписать вручную, поставив перед ним знак «равно», например, «=месяца». Регистр букв не важен.
- Щелкнув мышью в строке «Источник» (для активации), выделить курсором все элементы списка в таблице.
6. Если нужно создать сообщение для ввода, откройте одноименную вкладку. Напишите текст, который будет появляться рядом с ячейкой с раскрывающимся списком при ее выделении. На соседней вкладке — «Сообщение об ошибке», таким же образом можно написать текст, уведомляющий об ошибках.
7. Подтвердите ввод нажатием «ОК», и раскрывающийся список готов. Для его открытия нажмите на кнопку со стрелкой вниз, которая появится рядом с ячейкой, содержащей список.
Способ 2. Быстрое создание выпадающего списка
Выпадающий список в Excel 2010 можно создавать одним нажатием сочетания клавиш, но он может быть расположен только в одном месте — в ячейке под элементами списка.
1. Перечислите в столбик все элементы будущего выпадающего списка.
2. Выделите ячейку, находящуюся под последним элементом и нажмите сочетание клавиш «Alt» + «стрелка вниз» — список будет создан. Этот метод позволит задать ячейке значение одного из элементов.
Способ 3. Создание выпадающего списка как элемента управления
Для применения этого метода включите отображение вкладки «Разработчик»: откройте меню «Файл» — «Параметры» — «Настройка ленты». В столбце «Основные вкладки» отметьте галочкой пункт «Разработчик». Подтвердите действие нажатием «ОК» — вкладка будет создана.
1. Перечислите элементы будущего списка в столбик.
2. Из меню «Вставить» вкладки «Разработчик» выберите пункт «Элементы управления формы» — «Поле со списком».
3. Нарисуйте на листе курсором ваш будущий раскрывающийся список. Кликните по нему правой кнопкой мыши и выберите из меню пункт «Формат объекта».
4. Значением поля «Формировать список по диапазону» должен быть список элементов — выделите его курсором, и поле будет заполнено автоматически. В поле «Связь с ячейкой» указывается адрес ячейки, где будет показан порядковый номер выделенного элемента. Выберите ячейку и кликните по ней. Поле «Количество строк списка» позволяет настроить, сколько элементов будет отображено при раскрытии списка.
5. Подтвердите ввод и нажмите «ОК». Список будет создан.
Способ 4. Создание выпадающего списка как элемента ActiveX
Самый сложный метод, но обладающий максимально гибкими настройками.
1. Создайте список вышеописанным способом.
2. Из меню «Вставить» вкладки «Разработчик» выберите пункт «Элементы ActiveX» — «Поле со списком».
3. Нарисуйте на листе будущий раскрывающийся список.
4. Опция, которая позволяет редактировать выпадающий список называется «Режим конструктора». Если данный режим активен — будет выделена одноименная кнопка в разделе «Элементы управления», рядом с кнопкой «Вставить». Если кнопка не выделена — режим редактирования отключен.
5. Для задания параметров списка нажмите кнопку «Свойства» того же раздела. Откроется окно настроек «Properties». Обе вкладки этого окна содержат одни и те же настройки, рассортированные в первом случае — по алфавиту, во втором — по категориям.
6. Большинство настроек можно оставить по умолчанию, а самое необходимое перечислено ниже.
- ListRows — аналог значения «Количество строк в списке», покажет, сколько строк будет отображаться.
- Font — настройки шрифта. Позволяет выбрать шрифт и его начертание.
- ForeColor — выбор из таблицы цвета шрифта.
- BackColor — цвет заднего фона.
- ListFillRange — расположение списка элементов в формате: лист(«!» — разделитель) и диапазон ячеек. Например: Лист2!D2:D6. Прописывается вручную.
- LinkedCell — связь с ячейкой. Вручную указывается адрес ячейки, где будет показываться порядковый номер выделенного элемента списка.
7. Сохраните настройки и деактивируйте режим конструктора нажатием на одноименную кнопку. Выпадающий список будет создан, а вы сможете проверить, как он работает.
Как создать в Экселе выпадающий список? Все давно знают о том, как хорошо Excel работает с таблицами и разного рода формулами, но мало кто знает, что здесь можно делать выпадающие списки. И сегодня речь пойдет о них.
И так есть несколько вариантов, как сделать выпадающие списки для работы в Microsoft Office Excel.
Вариант первый очень прост. Если вы вводите схожие данные в одном столбце сверху в низ, то вам достаточно всего лишь встать на ячейку под данными и нажать комбинацию клавиш «Alt + стрелка вниз». Перед вами появится выпадающий список, с которого одним кликом можно выбрать нужные Вам данные.
Недостатком данного метода является то, что он рассчитан на последовательный способ ввода данных и если вы станете на любую другую ячейку в столбце то выпадающий список будет пустым.
Вариант второй дает больше возможностей, его еще считают стандартным. Сделать можно это через проверку данных. В первую очередь нам нужно выделить диапазон данных, которые попадут в наш список, и присвоить ему имя.
Отредактировать данный диапазон можно через вкладку меню «Формулы» выбрав пиктограмму «Диспетчер имен». В нем вы можете создать новый выпадающий список, редактировать действующий или просто удалить не нужный.
Следующим шагом выбираем ячейку, где будет размещен наш выпадающий список и переходим на вкладку меню «Данные», нажимаем на иконку «Проверка данных». В открывшемся окне нам нужно выбрать тип данных, которые будут вводиться в нашей ячейке. В нашем случае мы выбираем «Списки» и ниже через знак равенства прописываем имя нашего диапазона, и нажать ОК. Для того чтобы применить список ко всем ячейкам достаточно перед включением проверки данных просто выделить весь столбец или нужную вам область.
Есть и другие варианты более сложные для создания выпадающего списка, такие как: вставка через вкладку меню «Разработчик», где можно вставить выпадающие списки как часть элемента формы или часть элемента ActiveX. Или написать соответствующие макросы для создания и работы выпадающих списков.
Введите данные в ячейки A1:A10, которые будут выступать в качестве источника для списка. В нашем примере мы ввели цифры, они появятся в выпадающем списке. Выделите ячейку (Например, E5), которая будет содержать в раскрывающийся список. Выберите меню «Данные» -> «Проверка данных», чтобы открыть диалоговое окно «Проверка вводимых значений».
3. На вкладке «Параметры» выберите параметр «Список» в раскрывающемся меню. Убедитесь, что установлены нужные флажки.
4. Затем, нажмите на кнопку. Появится следующее диалоговое окно.
5. Выберите элементы, которые появятся в выпадающем списке на листе с помощью мыши, кликните на кнопку и вернитесь обратно к окну «Проверка вводимых значений», затем нажмите кнопку «ОК».
6. Выпадающий список в Excel будет создан.
Если ваш список короткий, вы можете ввести элементы непосредственно в «Источник» на вкладке «Настройка» в диалоговом окне «Проверка вводимых значений». Отделите каждый пункт списка разделителями, указанными в региональных настройках.
Если список должен располагаться на другом листе, вы можете воспользоваться параметром «= List» перед указанием диапазона данных.
Как создать выпадающий список в Экселе на основе данных из перечня
Представим, что у нас есть перечень фруктов:
Как сделать выпадающий список в Excel
Для создания выпадающего списка нам потребуется сделать следующие шаги:
Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов => выбираем пункт “Проверка данных”.
В поле “Источник” ввести диапазон названий фруктов =$A$2:$A$6 или просто поставить курсор мыши в поле ввода значений “Источник” и затем мышкой выбрать диапазон данных:
Если вы хотите создать выпадающие списки в нескольких ячейках за раз, то выберите все ячейки, в которых вы хотите их создать, а затем выполните указанные выше действия. Важно убедиться, что ссылки на ячейки являются абсолютными (например, $A$2), а не относительными (например, A2 или A$2 или $A2).
Как сделать выпадающий список в Excel используя ручной ввод данных
На примере выше, мы вводили список данных для выпадающего списка путем выделения диапазона ячеек. Помимо этого способа, вы можете вводить данные для создания выпадающего списка вручную (необязательно их хранить в каких-либо ячейках).
Например, представим что в выпадающем меню мы хотим отразить два слова “Да” и “Нет”.
Для этого нам потребуется:
Выбрать ячейку, в которой мы хотим создать выпадающий список;
Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов =>
Проверка данных в Excel
Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выбрать “Список”:
Проверка вводимых значений в Excel
В поле “Источник” ввести значение “Да; Нет”.
Нажимаем “ОК”
Да - Нет
После этого система создаст раскрывающийся список в выбранной ячейке. Все элементы, перечисленные в поле “Источник”, разделенные точкой с запятой будут отражены в разных строчках выпадающего меню.
Если вы хотите одновременно создать выпадающий список в нескольких ячейках – выделите нужные ячейки и следуйте инструкциям выше.
Как создать раскрывающийся список в Эксель с помощью функции СМЕЩ
Наряду со способами описанными выше, вы также можете использовать формулу СМЕЩ для создания выпадающих списков.
Например, у нас есть список с перечнем фруктов:
Для того чтобы сделать выпадающий список с помощью формулы СМЕЩ необходимо сделать следующее:
Выбрать ячейку, в которой мы хотим создать выпадающий список;
Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов => выбрать пункт “Проверка данных”:
Проверка данных в Excel
Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выбрать “Список”:
Проверка вводимых значений в Excel
В поле “Источник” ввести формулу: =СМЕЩ(A$2$;0;0;5)
Нажать “ОК”
Система создаст выпадающий список с перечнем фруктов.
Как эта формула работает?
На примере выше мы использовали формулу =СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина]).
Эта функция содержит в себе пять аргументов. В аргументе “ссылка” (в примере $A$2) указывается с какой ячейки начинать смещение. В аргументах “смещ_по_строкам” и “смещ_по_столбцам” (в примере указано значение “0”) – на какое количество строк/столбцов нужно смещаться для отображения данных.
В аргументе “[высота]” указано значение “5”, которое обозначает высоту диапазона ячеек. Аргумент “[ширина]” мы не указываем, так как в нашем примере диапазон состоит из одной колонки.
Используя эту формулу, система возвращает вам в качестве данных для выпадающего списка диапазон ячеек, начинающийся с ячейки $A$2, состоящий из 5 ячеек.
Как сделать выпадающий список в Excel с подстановкой данных (с использованием функции СМЕЩ)
Если вы используете для создания списка формулу СМЕЩ на примере выше, то вы создаете список данных, зафиксированный в определенном диапазоне ячеек. Если вы захотите добавить какое-либо значение в качестве элемента списка, вам придется корректировать формулу вручную.
Ниже вы узнаете, как делать динамический выпадающий список, в который будут автоматически загружаться новые данные для отображения.
Для создания списка потребуется:
Выбрать ячейку, в которой мы хотим создать выпадающий список;
Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов => выбрать пункт “Проверка данных”;
Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выбрать “Список”;
В поле “Источник” ввести формулу: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
Нажать “ОК”
В этой формуле, в аргументе “[высота]” мы указываем в качестве аргумента, обозначающего высоту списка с данными – формулу СЧЕТЕСЛИ, которая рассчитывает в заданном диапазоне A2:A100 количество не пустых ячеек.
Примечание: для корректной работы формулы, важно, чтобы в списке данных для отображения в выпадающем меню не было пустых строк.
Как создать выпадающий список в Excel с автоматической подстановкой данных
Для того чтобы в созданный вами выпадающий список автоматически подгружались новые данные, нужно проделать следующие действия:
Создаем список данных для отображения в выпадающем списке. В нашем случае это список цветов. Выделяем перечень левой кнопкой мыши:
выпадающий список с автоматиеской подстановкой в эксель
На панели инструментов нажимаем пункт “Форматировать как таблицу”:
Из раскрывающегося меню выбираем стиль оформления таблицы
Нажав клавишу “ОК” во всплывающем окне, подтверждаем выбранный диапазон ячеек:
Присваиваем имя таблице в правой верхней ячейке над колонкой “А”:
Таблица с данными готова, теперь можем создавать выпадающий список. Для этого необходимо:
Выбрать ячейку, в которой мы хотим создать список;
Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов => выбрать пункт “Проверка данных”:
Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выбрать “Список”:
В поле источник указываем =”название вашей таблицы”. В нашем случае мы ее назвали “Список”:
Поле источник автоматическая подстановка данных в выпадающий список Эксель
Готово! Выпадающий список создан, в нем отображаются все данные из указанной таблицы:
Для того чтобы добавить новое значение в выпадающий список – просто добавьте в следующую после таблицы с данными ячейку информацию:
Таблица автоматически расширит свой диапазон данных. Выпадающий список соответственно пополнится новым значением из таблицы:
Автоматическая подстановка данных в выпадающий список эксель
Как скопировать выпадающий список в Excel
В Excel есть возможность копировать созданные выпадающие списки. Например, в ячейке А1 у нас есть выпадающий список, который мы хотим скопировать в диапазон ячеек А2:А6.
Для того чтобы скопировать выпадающий список с текущим форматированием:
нажмите левой клавишей мыши на ячейку с выпадающим списком, которую вы хотите скопировать;
выделите ячейки в диапазоне А2:А6, в которые вы хотите вставить выпадающий список;
нажмите сочетание клавиш на клавиатуре CTRL+V.
Так, вы скопируете выпадающий список, сохранив исходный формат списка (цвет, шрифт и.т.д). Если вы хотите скопировать/вставить выпадающий список без сохранения формата, то:
нажмите левой клавишей мыши на ячейку с выпадающим списком, который вы хотите скопировать;
нажмите сочетание клавиш на клавиатуре CTRL+C;
выберите ячейку, в которую вы хотите вставить выпадающий список;
нажмите правую кнопку мыши => вызовите выпадающее меню и нажмите “Специальная вставка”;
выпадающий список в excel
В появившемся окне в разделе “Вставить” выберите пункт “условия на значения”:
Нажмите “ОК”
После этого, Эксель скопирует только данные выпадающего списка, не сохраняя форматирование исходной ячейки.
Как выделить все ячейки, содержащие выпадающий список в Экселе
Иногда, сложно понять, какое количество ячеек в файле Excel содержат выпадающие списки. Есть простой способ отобразить их. Для этого:
Нажмите на вкладку “Главная” на Панели инструментов;
Нажмите “Найти и выделить” и выберите пункт “Выделить группу ячеек”:
В диалоговом окне выберите пункт “Проверка данных”. В этом поле есть возможность выбрать пункты “Всех” и “Этих же”. “Всех” позволит выделить все выпадающие списки на листе. Пункт “этих же” покажет выпадающие списки схожие по содержанию данных в выпадающем меню. В нашем случае мы выбираем “всех”:
Выпадающий список в Excel. Как найти все списки
Нажмите “ОК”
Нажав “ОК”, Excel выделит на листе все ячейки с выпадающим списком. Так вы сможете привести за раз все списки к общему формату, выделить границы и.т.д.
Как сделать зависимые выпадающие списки в Excel
Иногда нам требуется создать несколько выпадающих списков, причем, таким образом, чтобы, выбирая значения из первого списка, Excel определял какие данные отобразить во втором выпадающем списке.
Предположим, что у нас есть списки городов двух стран Россия и США:
Для создания зависимого выпадающего списка нам потребуется:
Создать два именованных диапазона для ячеек “A2:A5” с именем “Россия” и для ячеек “B2:B5” с названием “США”. Для этого нам нужно выделить весь диапазон данных для выпадающих списков:
зависимый выпадающий список в Excel
Перейти на вкладку “Формулы” => кликнуть в разделе “Определенные имена” на пункт “Создать из выделенного”:
Зависимые выпадающие списки в Excel
Во всплывающем окне “Создание имен из выделенного диапазона” поставьте галочку в пункт “в строке выше”. Сделав это, Excel создаст два именованных диапазона “Россия” и “США” со списками городов:
зависимый-выпадающий-список-в-excel
Нажмите “ОК”
В ячейке “D2” создайте выпадающий список для выбора стран “Россия” или “США”. Так, мы создадим первый выпадающий список, в котором пользователь сможет выбрать одну из двух стран.
Теперь, для создания зависимого выпадающего списка:
Выделите ячейку E2 (или любую другую ячейку, в которой вы хотите сделать зависимый выпадающий список);
Кликните по вкладке “Данные” => “Проверка данных”;
Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выберите “Список”:
Проверка вводимых значений в Excel
Нажмите “ОК”
Теперь, если вы выберите в первом выпадающем списке страну “Россия”, то во втором выпадающем списке появятся только те города, которые относятся к этой стране. Также и в случае, когда выбираете “США” из первого выпадающего списка.
Нужен для того, чтобы при заполнении легко и быстро выбирать нужное значение. Мы рассмотрим два способа создания раскрывающегося списка в Excel 2007 .
Первый способ самый простой, но и наименее пригодный к применению. Под столбцом с данными нужно нажать правой клавишей на ячейку правой клавишей мыши, и нажать на строку Выбрать из раскрывающегося списка . Затем выбираете нужное значение, нажимаете на него, и это значение появляется в ячейке. Данный способ действует только для ячейки непосредственно под списком, и список не должен содержать пустых ячеек.
Второй способ создания выпадающего списка в Excel более изящный и универсальный. Выделяете диапазон данных для выпадающего списка, затем нажимаете на пункт меню Формула — Диспетчер имен — Создать . Заполняете поле Имя , и копируете его (оно Вам понадобится позже). Имя должно начинаться с буквы или символа подчеркивания, и не должно содержать пробелов. Нажимаете ОК . Закрываете окно.
Затем выбираете ячейку, в которой будет выпадающий список Excel (можно сразу выделить несколько ячеек, если в них будут одинаковые выпадающие списки). После этого выбираете пункт меню Данные — Проверка данных , затем в окошке Тип данных выберите строку Список , в поле Источник поставьте знак равно, и без пробела вставьте то, что Вы копировали (значение поля Имя ). Не забудьте про знак = , иначе ничего не получится. Выглядит надпись в поле Источник примерно так: =Имя_диапазона . Нажимаете ОК .
Справа от ячейки появится значок выпадающего списка, в котором можно выбрать одно из значений. После выбора это значение появится в ячейке. Ячейку с выпадающим списком Excel можно сделать на другом листе, так, чтобы на одном листе были данные для списков, а на другом — ячейка с выпадающим списком, или несколько таких ячеек.
Это может быть опрос, тест, или анкета с заранее прописанными вариантами ответов. Можно, например, раздать файл Excel с анкетой группе людей, которых Вы хотите опросить, и получить назад уже файлы с ответами, или распечатанные ответы.
Для надежности можно лист с данными для выпадающих списков скрыть или защитить. Чтобы скрыть лист Excel , нажимаете на его название правой клавишей мыши, и выбираете Скрыть . Чтобы отобразить скрытые листы, нажимаете на название любого открытого листа Excel правой клавишей мыши, и выбираете Отобразить .
Чтобы защитить лист Excel , выбираете пункт меню Редактировать — Защитить лист , и прописываете пароль и действия, которые разрешены для пользователей.
Чтобы удалить из ячейки выпадающий список, выделяете ячейку, выбираете в меню Данные — Проверка данных , и нажимаете на кнопку Очистить все .
Похожие вопросы из справочника EXCEL: Динамическая сортировка таблицы в MS EXCEL Проблемы совместимости сортировки и фильтрации Как в Excel настраивать фильтр и сортировать ячейки по цвету заливки, цвету шрифта и значку