Создание или удаление настраиваемого списка для сортировки и подстановки данных

С помощью встроенных настраиваемых списков вы можете сортировать данные по дням недели или месяцам года. При желании вы можете создавать собственные настраиваемые списки для сортировки по любым другим параметрам, которые трудно сортировать по алфавиту (например, высокий, средний и низкий или S, M, L, XL).

Настраиваемые списки позволяют сортировать данные листа по месяцам доставки или по приоритету.

Сортировка по дням недели или месяцам года с помощью встроенного настраиваемого списка

Выполните указанные ниже действия:

1. Выделите столбцы для сортировки.

   Примечание: Для получения наилучшего результата у каждого столбца должен быть заголовок.

2. На ленте выберите Данные > Сортировка.

   

3. Во всплывающем окне Сортировка в раскрывающемся списке Сортировать по выберите столбец, по которому нужно выполнить сортировку.

   Например, чтобы отсортировать данные в предыдущем примере по дате доставки, в поле Сортировать по выберите доставка.

   

4. В раскрывающемся списке Порядок выберите Настраиваемый список.

   

5. В диалоговом окне Списки выберите нужный список и нажмите кнопку OK для сортировки листа.

   

Создание собственного настраиваемого списка

Чтобы создать собственный настраиваемый список для сортировки, выполните указанные ниже действия.

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

   

2. Выделите все ячейки в этом списке и выберите Файл > Параметры > Дополнительно.

3. Прокрутите страницу вниз до раздела Общие и нажмите кнопку Изменить списки...
   
   

4. В окне Списки нажмите кнопку Импорт.

   

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Создание или удаление настраиваемого списка для сортировки и подстановки данных

Создание раскрывающегося списка

​Смотрите также​"​ база данных, когда​ Target.Cells.Count = 1​ сформирует правильную ссылку​Теперь удалим значение «береза».​

1. ​ Настраиваемый список может содержать​ тех, кто хочет​ с ним дома,​ некоторыми ограничениями. Поле​ Excel​Игнорировать пустые ячейки​ ​ см. статью Удаление​.​Чтобы упростить работу пользователей​Для Excel версий​ ввод несоответствующего значения​​ Then Application.EnableEvents =​​ на внешний источник​

   

   ​Осуществить задуманное нам помогла​​Настраиваемый список​

   • ​.​ только текст или​ избежать многократного копирования​ то придётся еще​List entries​Сортировка по пользовательскому списку​.​Значения также можно ввести​ раскрывающегося списка.​Если вы хотите, чтобы​

   • ​ с листом, добавьте​ ниже 2007 те​ в поле может​ False If Len(Target.Offset(1,​ информации.​

2. ​ «умная таблица», которая​, чтобы отобразить всплывающее​На панели​

3. ​ текст с числами.​​ и вставки данных.​​ раз создать такой​​(Элементы списка) позволяет​​Я продемонстрирую это на​

   ​Установите флажок​​ непосредственно в поле​​Предлагаем скачать образец книги​​ при выборе ячейки​ в ячейки раскрывающиеся​ же действия выглядят​ привести к нежелаемым​ 0)) = 0​Делаем активной ячейку, куда​ легка «расширяется», меняется.​ окно​

4. ​Списки​​ Чтобы создать настраиваемый​​ Если Вы знаете,​​ же пользовательский список​​ ввести не более​​ примере списка школьных​​Список допустимых значений​

5. ​Источник​​ с несколькими примерами​​ появлялось всплывающее сообщение,​ списки, в которых​ так:​ результатам.​ Then Target.Offset(1, 0)​ хотим поместить раскрывающийся​Теперь сделаем так, чтобы​Списки​появятся выбранные вами​ список, содержащий только​ что есть набор​ на домашнем компьютере.​

   

6. ​ 255 символов. Будьте​ клубов. Мне нужно​​Откройте вкладку​​через запятую.​

7. ​ проверки данных, аналогичными​​ установите флажок​

8. ​ можно выбрать нужный​​Второй​​Итак, для создания​

   • ​ = Target Else​ список.​ можно было вводить​, а затем выделите​​ элементы.​ числа, например от​​ данных, который в​ Если Вы использовали​ внимательны к тому,​ вести учёт денег​Сообщение для ввода​Чтобы изменить список допустимых​ примеру в этой​

     

9. ​Отображать подсказку, если ячейка​​ элемент.​​: воспользуйтесь​

   • ​ выпадающего списка необходимо:​ Target.End(xlDown).Offset(1, 0) =​Открываем параметры проверки данных.​ новые значения прямо​ настраиваемый список и​​"Параметры" > "Дополнительно" > "Общие" >​​ 0 до 100,​ любой момент может​​ настраиваемый список, чтобы​​ из скольких символов​ на балансе каждого​.​ элементов, просто измените​ статье. Вы можете​

     

10. ​ является текущей​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​ вы не хотите,​ списке. Желательно, чтобы​" - группа "​ это диапазон​

1. ​ в одной ячейке,​ квадратные скобки. Этот​ «Формулы» - «Диспетчер​ содержание в одной​ "Изменить списки"." />​ список короткий, можно​ гораздо больше времени,​

2. ​ будет.​ поле​ – это та​

3. ​и введите заголовок​​Можно указать собственное сообщение​​Ввод данных станет быстрее​​ чтобы сообщение отображалось,​​ элементы списка содержались​​Определённые имена​​M1:M3​​ разделенные любым знаком​​ файл должен быть​

   

   ​ имен» - «Создать».​​ ячейке нескольких значений.​Два раза нажмите кнопку​ ввести его значения​ чем Вы можете​Итак, наш настраиваемый список​List entries​ информация, которая мне​ и сообщение в​ об ошибке, которое​ и точнее, если​ снимите этот флажок.​

4. ​ в таблице Excel.​​"), который в любой​​), далее выбрать ячейку​ препинания, применим такой​​ открыт. Если книга​​ Вводим уникальное название​​ Когда пользователь щелкает​​ОК​

5. ​ прямо во всплывающем​​ предположить… и оставит​​ готов к использованию.​(Элементы списка) вручную,​ будет нужна постоянно.​

   ​ соответствующие поля (до​ будет отображаться при​ ограничить значения в​

6. ​Откройте вкладку​ Если это не​​ версии Excel вызывается​​ в которой будет​ модуль.​ с нужными значениями​​ диапазона – ОК.​​ по стрелочке справа,​

   ​.​​ окне. Если список​

   • ​ шефа с мыслью,​ Выделите ячейку и​​ не ставьте лишние​​В этот момент я​

   • ​ 225 символов). Если​ вводе недопустимых данных.​ ячейке вариантами из​Сообщение об ошибке​ так, список можно​​ сочетанием клавиш​​ выпадающий список (в​

   • ​Private Sub Worksheet_Change(ByVal​ находится в другой​Создаем раскрывающийся список в​ появляется определенный перечень.​Примечание:​​ длинный, можно импортировать​​ что Вам пришлось​​ введите с клавиатуры​​ пробелы между элементами.​​ бы также запустил​​ вы не хотите,​ На вкладке​​ раскрывающегося списка.​​.​

См. также

​ быстро преобразовать в​Ctrl+F3​

​ нашем примере это​ Target As Range)​

1. ​ папке, нужно указывать​ любой ячейке. Как​ Можно выбрать конкретное.​ Настраиваемый список можно создать​ значения из диапазона​ изрядно потрудиться. Пусть​

   ​ любой элемент этого​​ Если пробелы будут​

   • ​ проверку орфографии, это​ чтобы сообщение отображалось,​Данные​Сначала создайте на листе​Если вы хотите, чтобы​ таблицу, выделив любую​.​ ячейка​On Error Resume​ путь полностью.​

   • ​ это сделать, уже​Очень удобный инструмент Excel​ только на основе​ ячеек.​ он так думает.​

2. ​ списка. Нажмите на​ стоять до или​ делается простым нажатием​

3. ​ снимите этот флажок.​​нажмите кнопку​​ список допустимых элементов,​​ при вводе значения,​​ ячейку диапазона и​

4. ​Какой бы способ​​К1​​ Next​​Возьмем три именованных диапазона:​​ известно. Источник –​​ для проверки введенных​​ значений, таких как​

5. ​Чтобы создать настраиваемый список​Наслаждайтесь!​ маркер автозаполнения (небольшой​ после элемента, то​​F7​​Откройте вкладку​Проверка данных​ а затем выполните​ которого нет в​ нажав клавиши​ Вы не выбрали​), потом зайти во​If Not Intersect(Target,​Это обязательное условие. Выше​ имя диапазона: =деревья.​​ данных. Повысить комфорт​​ текст, числа, даты​

   ​ этим способом, выполните​

6. ​Урок подготовлен для Вас​ квадратик в правом​​ Microsoft их просто​​.​

7. ​Сообщение об ошибке​​или​

8. ​ сортировку или расположите​​ списке, появлялось всплывающее​​CTRL+T​

   • ​ в итоге Вы​ вкладку "​ Range("C2:C5")) Is Nothing​ описано, как сделать​​Снимаем галочки на вкладках​​ работы с данными​ и время. На​ указанные ниже действия.​ командой сайта office-guru.ru​ нижнем углу ячейки),​ не станет учитывать,​Затем я выделил эту​

9. ​.​​Проверить​​ их в нужном​

   • ​ сообщение, установите флажок​.​ должны будете ввести​Данные​ And Target.Cells.Count =​​ обычный список именованным​​ «Сообщение для ввода»,​ позволяют возможности выпадающих​​ основе формата, например​​В​Источник: https://www.ablebits.com/office-addins-blog/2011/11/02/create-excel-custom-lists/​ и потяните за​ а если между​ область, нажал на​

10. ​Если вы хотите, чтобы​​, а затем откройте​​ порядке. В дальнейшем​

​Выводить сообщение об ошибке​Примечания:​ имя (я назвал​", группа "​ 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, то​

Сравнение встроенных и настраиваемых списков

​ ли ширины ячеек​ все раскрывающиеся списки,​Данные​ сообщение будет отображаться​

​ для отображения всех​​ созданные на основе​в группе​

​ со значком​и нажмите кнопку​ (это так же​Сообщение для ввода​ А всю остальную​ «Источник» функцию вида​ Then If IsEmpty(Target)​ поле источник вписать​ вместе с книгой,​Основные параметры работы с​Встроенные списки​ что Вы хотите​

​" и заполняем заголовок​ работу будут делать​ =ДВССЫЛ(E3). E3 –​ Then Exit Sub​​ это имя.​​ поэтому его можно​

​ Excel​​Пн, Вт, Ср, Чт,​ сделать, и вставит​(Импорт) и выделите​File​ решили изменить элементы​ обновляться автоматически. Дополнительные​нажмите кнопку​ "Предупреждение" — со значком​.​ списком на любом​

Создание настраиваемого списка

​ и текст сообщения​ макросы.​ ячейка с именем​ If WorksheetFunction.CountIf(Range("Деревья"), Target)​Любой из вариантов даст​ использовать на других​ >​ Пт, Сб, Вс​ соответствующие данные.​

Введение значений напрямую

​ ячейки с данными​(Файл) и нажмите​ раскрывающегося списка, см.​

1. ​ действия не требуются.​​Проверка данных​​.​Примечание:​​ листе). Делается это​​которое будет появляться​​На вкладке «Разработчик» находим​​ первого диапазона.​​ = 0 Then​​ такой результат.​​ компьютерах, в том​​Изменить списки​​Понедельник, Вторник, Среда, Четверг,​​Одна из приятных возможностей,​

2. ​ для нового списка.​​Options​​ статью Добавление и​​Теперь следует отсортировать данные​​или​​Чтобы заблокировать пользователям ввод​​ Если кнопка​​ так:​​ при выборе ячейки​​ инструмент «Вставить» –​Бывает, когда из раскрывающегося​​ lReply = MsgBox("Добавить​​​​ числе на серверах​

3. ​.​​ Пятница, Суббота, Воскресенье​​ которую дает работа​​Помните, у нас было​​(Параметры). Затем пролистайте​ удаление элементов раскрывающегося​​ в том порядке,​​Проверить​ данных, которые не​

   ​Проверка данных​То есть вручную,​

4. ​ с выпадающим списком​ «ActiveX». Здесь нам​​ списка необходимо выбрать​​ введенное имя "​

   

   ​Необходимо сделать раскрывающийся список​​ с службы Excel,​​Выберите в поле​янв, фев, мар, апр,​

5. ​ с настраиваемыми списками,​​ ограничение на количество​​ вниз, найдите кнопку​

Создание настраиваемого списка на основе диапазона ячеек

​ списка. Чтобы удалить​

1. ​ в котором они​.​ содержатся в раскрывающемся​недоступна, возможно, лист​ через​4.​ нужна кнопка «Поле​ сразу несколько элементов.​

2. ​ & _ Target​ со значениями из​ для которых может​​Списки​​ май, июн, июл,​​ – это возможность​​ символов, которое можно​​Edit Custom Lists​​ раскрывающийся список, см.​

3. ​ должны отображаться в​​Примечание:​​ списке, выберите вариант​ защищен или является​

   ​;​Так же необязательно​ со списком» (ориентируемся​ Рассмотрим пути реализации​ & " в​ динамического диапазона. Если​ быть опубликована ваша​

4. ​пункт​​ авг, сен, окт,​​ упорядочить данные по​

​ ввести в поле​​(Изменить списки).​ статью Удаление раскрывающегося​ раскрывающемся списке.​ Если команда проверки недоступна,​Остановка​ общим. Разблокируйте определенные​(точка с запятой) вводим​ можно создать и​ на всплывающие подсказки).​ задачи.​

Удаление настраиваемого списка

​ выпадающий список?", vbYesNo​

1. ​ вносятся изменения в​ книга.​НОВЫЙ СПИСОК​

2. ​ ноя, дек​ любому из списков,​​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"​На вкладке​

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

​ОК​ подготовленные Microsoft, то​

Выпадающий список в Excel с подстановкой данных

​Проверка данных​ невозможно. Дополнительные сведения​ и сообщение "Введенное​Параметры​ (значения введённые слева-направо​проверка данных​Жмем «Свойства» – открывается​ выше. С его​

1. ​ If End Sub​ меню находим инструмент​Параметрах Excel​ нажимайте клавишу ВВОД.​
2. ​ список невозможно.​ раскройте выпадающее меню​, чтобы закрыть окно​ увидите среди них​ фигурирует один и​.​ о защите книги​ значение неверно. Набор​в поле​ будут отображаться в​работать будет, но​ перечень настроек.​ помощью справа от​
3. ​Сохраняем, установив тип файла​ «Форматировать как таблицу».​во всплывающем окне​Завершив создание списка, нажмите​Вы также можете создать​Order​ с параметрами списка,​ те, которыми все​

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

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

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

​ при активации ячейки​Вписываем диапазон в строку​ выпадающего списка будут​

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

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

​ Error Resume Next​ «Макросы». Сочетание клавиш​ заголовка (шапки) важно.​Сортировка​Списки​ отсортировать или заполнить​

​ которому желаете выполнить​Для пользовательского списка можно​ Вы захотите удалить​

Выпадающий список в Excel с данными с другого листа/файла

​Копировать​Список​ меню​ работает правильно. Например,​Источник​ очень "жирный" минус:​ об ошибке с​ Для изменения шрифта​ If Not Intersect(Target,​ для быстрого вызова​ В нашем примере​

1. ​в столбце​появятся введенные вами​ значения по приведенным​
2. ​ сортировку.​ импортировать только текстовые​ или изменить созданный​

​>​.​Разрешить​ можно проверить, достаточно​и выделите диапазон​ проверка данных работает​ вашим текстом будет​ и размера –​ Range("Е2:Е9")) Is Nothing​ – Alt +​

Как сделать зависимые выпадающие списки

​ это ячейка А1​

​Порядок​ элементы.​ ниже спискам, нужен​Сортировка может включать более​ значения. Если Вам​ Вами список, это​Вставить​Если вы уже создали​

1. ​выберите пункт​ ли ширины ячеек​ списка. В примере​
2. ​ только при непосредственном​ появляться стандартное сообщение.​ Font.​ And Target.Cells.Count =​ F8. Выбираем нужное​
3. ​ со словом «Деревья».​. Настраиваемый список, хранимый​Нажмите два раза кнопку​ настраиваемый список, так​ одного столбца. Если​ нужно создать настраиваемый​ можно сделать в​, то было бы​ таблицу с элементами​Список​ для отображения всех​ данные находятся на​ вводе значений с​

   Выбор нескольких значений из выпадающего списка Excel

   ​5.​Скачать пример выпадающего списка​ 1 Then Application.EnableEvents​ имя. Нажимаем «Выполнить».​ То есть нужно​

   1. ​ в файле книги,​ОК​ как соответствующего естественного​ Вы хотите добавить​ список с календарными​ любой момент. Для​ очень удобно иметь​ раскрывающегося списка, щелкните​.​ элементов.​ листе "Города" в​ клавиатуры. Если Вы​Если список значений​При вводе первых букв​ = False If​Когда мы введем в​ выбрать стиль таблицы​ также недоступен непосредственно​.​ порядка значений не​ ещё один уровень​ датами или числами,​ этого кликните по​ заранее настроенный и​ поле​Щелкните поле​
   2. ​Если список элементов для​ диапазоне A2:A9. Обратите​ попытаетесь вставить в​ находится на другом​ с клавиатуры высвечиваются​ Len(Target.Offset(0, 1)) =​ пустую ячейку выпадающего​ со строкой заголовка.​ для команды​Выполните указанные ниже действия.​ существует.​ и упорядочить сначала​ то придётся использовать​ своему списку и​ сохранённый список, чтобы​Источник​Источник​ раскрывающегося списка находится​ внимание на то,​
   3. ​ ячейку с​ листе, то вышеописанным​ подходящие элементы. И​ 0 Then Target.Offset(0,​ списка новое наименование,​

   ​ листе список допустимых​ и вы хотите​ отсутствует в диапазоне,​значения из буфера​ список не получится​ все приятные моменты​

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

   1. ​ Else Target.End(xlToRight).Offset(0, 1)​ введенное имя баобаб​Ставим курсор в ячейку,​При необходимости можно добавить​ по которым нужно​Большое, Среднее, Малое​
   2. ​ счёта, то можете​(Элементы списка).​У меня есть два​ Использование настраиваемых списков​ Однако не включайте​ элементов.​
   3. ​ запретить пользователям его​ так как она​
   4. ​ обмена, т.е скопированные​ (до версии Excel​ данного инструмента. Здесь​ = Target End​ в выпадающий список?».​ где будет находиться​ такой список в​ выполнить сортировку или​

   ​Север, Юг, Восток, Запад​

   ​ кликнуть​Вот некоторые моменты, которые​ способа, как создать​ – это хорошее​ в него ячейку​Диалоговое окно свернется, чтобы​ просмотр и изменение,​ не является одним​ предварительно любым способом,​ 2010). Для этого​

   exceltable.com

Создание выпадающего списка в ячейке

excelworld.ru

​Чтобы удалить раскрывающийся список,​

Для сортировки или заполнения значений в пользовательском порядке можно применять настраиваемые списки. В 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 из выпадающего списка. Создаем сам список и выделяем его левой кнопкой мыши. Кликаем правой кнопкой мыши в выделенной области и выбираем пункт Имя диапазона .

В открывшемся окошке в поле Имя вводим название нашего списка, назовем Значение . В поле Область из выпадающего списка выберем Книга (либо номер Листа к которому желаете применить список). Жмем Ок.

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

Список создан. Теперь применим этот список к ячейке.

Выделяем ячейку, к которой будет привязан список. В ленте переходим на вкладку Данные и в группе Работа с данными кликаем по кнопке Проверка данных . В следующем окошке во вкладке Параметры в поле Тип данных выбираем из списка пункт Список.

В поле Источник ставим знак «равно» и пишем название, которое присвоили списку. Список называется «Значение». Соответственно запись должна быть как показано на рисунке ниже.

Жмем Ок, и теперь получили ячейку с выпадающим списком. Как это выглядит, смотрим ниже на картинке. Когда щелкаем по ячейке, видим справа от нее квадратик с треугольничком вниз. Кликаем по квадратику и тем самым раскрываем список.

Можно создать , минуя присвоение названия списка. То есть:

1. создаем сам список;
2. переходим в Ленте на вкладку Данные , жмем кнопку Проверка данных;
3. в открывшемся окошке, во вкладке Параметры в поле Тип данных выбираем Список;
4. в поле Источник кликаем левой кнопкой мыши для активации данного поля. Далее выделяем ячейки образующие список;
5. жмем Ок.

Все, раскрывающийся список в excel 2007 готов.

Под выпадающим списком понимается содержание в одной ячейке нескольких значений. Когда пользователь щелкает по стрелочке справа, появляется определенный перечень. Можно выбрать конкретное.

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

Создание раскрывающегося списка

Путь: меню «Данные» - инструмент «Проверка данных» - вкладка «Параметры». Тип данных – «Список».

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

Любой из вариантов даст такой результат.

Выпадающий список в Excel с подстановкой данных

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

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

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

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

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

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

Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».

Нажмем «Да» и добавиться еще одна строка со значением «баобаб».

Выпадающий список в Excel с данными с другого листа/файла

Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.

1. Делаем активной ячейку, куда хотим поместить раскрывающийся список.
2. Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).

Имя файла, из которого берется информация для списка, заключено в квадратные скобки. Этот файл должен быть открыт. Если книга с нужными значениями находится в другой папке, нужно указывать путь полностью.

Как сделать зависимые выпадающие списки

Возьмем три именованных диапазона:

Это обязательное условие. Выше описано, как сделать обычный список именованным диапазоном (с помощью «Диспетчера имен»). Помним, что имя не может содержать пробелов и знаков препинания.

1. Создадим первый выпадающий список, куда войдут названия диапазонов.

2. Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.


3. Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.


Выбор нескольких значений из выпадающего списка Excel

Бывает, когда из раскрывающегося списка необходимо выбрать сразу несколько элементов. Рассмотрим пути реализации задачи.

1. Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.
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
2. Чтобы выбранные значения показывались снизу, вставляем другой код обработчика.
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
3. Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препинания, применим такой модуль.


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 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​

Сравнение встроенных и настраиваемых списков

​ список, содержащий только​ тех, кто хочет​ данный момент работаете.​ значение в поле​

​ числа, например от​​ избежать многократного копирования​ Если Вы возьмёте​

​List entries​ решение для данной​ цвета)​ списке. По умолчанию​выберите вариант​ становится активным «Режим​ списка необходимо выбрать​ Then If IsEmpty(Target)​ в качестве источника​ пользователь выберет​ связанные выпадающие списки​ "Выпадающий список в​

​ - 8, но​Список (List)​ конструктора». Рисуем курсором​ сразу несколько элементов.​​ Then Exit Sub​​ указать диапазон ячеек​

​Portugal​​ работали во всех​ Excel".​ сделать список из​ "Параметры Excel" > "Популярные" >​ нужно сначала создать​ Если Вы знаете,​ компьютера, чтобы поработать​ нажав​ покажу, как создать​и​

Создание настраиваемого списка

​ можно больше, чего​и введите в​ (он становится «крестиком»)​ Рассмотрим пути реализации​ If WorksheetFunction.CountIf(Range("Деревья"), Target)​ со списком.​, то мы должны​ версиях Excel. Следующий​В Excel есть​

Введение значений напрямую

​ таблицы или сделать​ "Изменить списки"." />​ список чисел в​

1. ​ что есть набор​​ с ним дома,​​Add​ настраиваемый список в​​BackColor​​ не позволяет предыдущий​​ строчку​​ небольшой прямоугольник –​​ задачи.​​ = 0 Then​​Назначить имя для диапазона​​ обратиться к базе​​ шаг – создать​​ функции, которые помогут​

2. ​ в ячейке выпадающий​​Два раза нажмите кнопку​​ текстовом формате.​​ данных, который в​​ то придётся еще​​(Добавить). Если Вы​​ Excel.​​- цвет текста​​ способ.​​Источник (Source)​ место будущего списка.​​Создаем стандартный список с​​ lReply = MsgBox("Добавить​​ значений и в​

3. ​ с индексом​​ именованные диапазоны для​​ быстро заполнить большой​​ список. Рассмотрим варианты.​​ОК​Создать настраиваемый список можно​​ любой момент может​​ раз создать такой​ выберете этот путь,​

   ​Создаем пользовательский список в​ и фона, соответственно​

4. ​После нажатия на​знак равенства и​​Жмем «Свойства» – открывается​​ помощью инструмента «Проверка​

   

   ​ введенное имя "​​ поле источник вписать​​3​ наших списков. На​

5. ​ диапазон таблицы данными,​​ Вот наша таблица.​​.​

Создание настраиваемого списка на основе диапазона ячеек

​ двумя способами. Если​

1. ​ понадобиться, почему бы​ же пользовательский список​ то столкнетесь с​ Excel​Большим и жирным плюсом​ОК​ имя диапазона (т.е.​ перечень настроек.​

2. ​ данных». Добавляем в​ & _ Target​ это имя.​​, в которой хранятся​​ вкладке​​ не используя копирование.​​Сначала мы создаем​​Примечание:​​ список короткий, можно​

3. ​ не сделать из​​ на домашнем компьютере.​​ некоторыми ограничениями. Поле​Создаем список с нуля​

   ​ этого способа является​списком можно пользоваться.​=Товары​Вписываем диапазон в строку​ исходный код листа​ & " в​Любой из вариантов даст​

4. ​ названия городов Португалии.​​Formulas​​ Подробнее об этих​

​ сводную таблицу с​​ Настраиваемый список можно создать​ ввести его значения​ него настраиваемый список?​ Если Вы использовали​List entries​Создание списка из существующего​ возможность быстрого перехода​Чтобы вместо порядкового номера​).​ ListFillRange (руками). Ячейку,​

Удаление настраиваемого списка

​ готовый макрос. Как​

1. ​ выпадающий список?", vbYesNo​ такой результат.​ Мы воспользуемся функцией​

2. ​(Формулы) есть команда​ функциях смотрите в​​ помощью функции «Мастер​​ только на основе​​ прямо во всплывающем​​ Кто знает, возможно​

Хранение настраиваемых списков

​ настраиваемый список, чтобы​(Элементы списка) позволяет​ диапазона данных​ к нужному элементу​ элемента выводилось его​Нажмите​ куда будет выводиться​ это делать, описано​ + 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 из таблицы.

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​ списка новое наименование,​ это ячейка А1​​B1​​ в поле​ доступны только принадлежащие​ диапазонах консолидации», «Сводная​ реестр компьютера, чтобы​Изменить списки​ списки дней недели​ затем нажал на​ а если между​ делается простым нажатием​​ нужно два столбца​​:​ зависимости от выбора​ смотрим обучающее видео:​​ 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​Чтобы понять, что представляют​ состоит в том,​ в настраиваемый список​ кнопку​

​ окупающие все потраченные​ листе. А вот​ элемента управления "поле​Один щелчок правой кнопкой​

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

​ что Excel одинаково​ – импортировать данные.​

Выпадающий список в Excel с подстановкой данных

​ на дополнительные настройки​ дальше начинаются серьезные​ со списком" с​ мыши по пустой​ Resume Next If​Когда значения для выпадающего​Ставим курсор в ячейку,​ 1-й, 2-й или​

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

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

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

​ Is Nothing And​

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

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

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

​ два связанных (или​Теперь мы можем создать​ ячейке​Всё, таблица составлена.​ для которых может​ >​

​ дней недели и​ элемента списка, с​ останется только нажать​

Выпадающий список в Excel с данными с другого листа/файла

​Popular​Элемент управления​ режиме отладки, когда​. В более ранних​ клавиш​ Then Target.Offset(1, 0)​ функции ДВССЫЛ: она​ функцию:​ зависимых) выпадающих списка.​ выпадающие списки в​B2​

1. ​Справа от сводной​ быть опубликована ваша​Изменить списки​
2. ​ месяцев года.​ середины или с​Import​

​(Общие) Вы найдёте​Способ 4.​ можно настраивать его​ версиях - панель​ALT+стрелка вниз​ = Target Else​ сформирует правильную ссылку​Протестируем. Вот наша таблица​ Если мы выбираем​ тех ячейках, где​

Как сделать зависимые выпадающие списки

​– принадлежащий ей​

​ таблицы появилось окно​ книга.​.​Встроенные списки​ последнего элемента… Как​(Импорт) и Excel​ пункт​Элемент ActiveX​

1. ​ параметры и свойства,​ инструментов​. Способ не работает,​
2. ​ Target.End(xlDown).Offset(1, 0) =​ на внешний источник​ со списком на​ страну​ планировали выбирать данные.​
3. ​ город, как на​ «Список полей сводной​Однако, открыв книгу на​Выберите в поле​Пн, Вт, Ср, Чт,​ только Вы щелкните​ создаст список из​Create lists for use​Сложность​ двигать его по​Формы (Forms)​ если ячейку и​ Target End If​

   Выбор нескольких значений из выпадающего списка Excel

   ​ информации.​ одном листе:​France​ Выделите ячейку​ примере:​

   1. ​ таблицы». Убираем галочки​ другом компьютере или​Списки​ Пт, Сб, Вс​ и начнете протаскивать​ содержащегося в ячейках​ in sorts and​низкая​ листу и менять​через меню​ столбец с данными​ Target.ClearContents Application.EnableEvents =​Делаем активной ячейку, куда​Добавим в таблицу новое​, в связанном списке​B1​Для начала нужно создать​ у слов «Строка»,​ сервере, вы не​пункт​Понедельник, Вторник, Среда, Четверг,​ мышь, Excel определит,​ текста. Если Вы​ fills sequences​средняя​ размеры и -​
   2. ​Вид - Панели инструментов​ отделяет хотя бы​ True End If​ хотим поместить раскрывающийся​ значение «елка».​ у нас будут​(в ней мы​ базу данных. На​ «Столбец». Остается галочка​ найдете настраиваемый список,​НОВЫЙ СПИСОК​ Пятница, Суббота, Воскресенье​ что Вы хотите​ не выделили текст​(Создавать списки для​высокая​ режиме ввода, когда​ - Формы (View​ одна пустая строка​
   3. ​ End Sub​ список.​Теперь удалим значение «береза».​ города только из​ будем выбирать страну),​

   ​ - выбирать из​ Forms)​ товар, который еще​ в одной ячейке,​ В поле «Источник»​ «умная таблица», которая​

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

   1. ​Из этой статьи Вы​Data​ которые хочу дать​И, от нашей сводной​Параметрах Excel​Элементы списка​
   2. ​ авг, сен, окт,​Одна из приятных возможностей,​ с кнопкой​Edit Custom Lists​нет​ него данные. Переключение​
   3. ​. Если этой вкладки​ ни разу не​
   4. ​ разделенные любым знаком​ вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).​ легка «расширяется», меняется.​ узнали, как можно​(Данные), нажмите​ пользователям на выбор​ таблицы осталась только​во всплывающем окне​

   ​, начиная с первого​

   ​ ноя, дек​ которую дает работа​Import​(Изменить списки).​нет​ между этими режимами​ не видно, то​ вводился выше:​ препинания, применим такой​Имя файла, из которого​

   exceltable.com

Выпадающий список в ячейке листа

Видео

​Теперь сделаем так, чтобы​ сделать простейшие связанные​Data Validation​ в первом раскрывающемся​

​ итоговая сумма. Нажимаем​Списки​ элемента.​Январь, Февраль, Март, Апрель,​

Способ 1. Примитивный

​ с настраиваемыми списками,​(Импорт) и выделите​Если Вы работаете в​да​ происходит с помощью​​ нажмите кнопку​​Выделите ячейки с данными,​​ модуль.​ берется информация для​​ можно было вводить​​ выпадающие списки в​(Проверка данных), а​ списке, а в​ двойным щелчком мыши​. Настраиваемые списки будут​После ввода каждого элемента​ Май, Июнь, Июль,​ – это возможность​ ячейки с данными​

Способ 2. Стандартный

1. ​ Excel 2010, то​Количество отображаемых строк​ кнопки​Офис - Параметры Excel​
2. ​ которые должны попасть​Private Sub Worksheet_Change(ByVal​ списка, заключено в​ новые значения прямо​​ Microsoft Excel. Вы​ затем в выпадающем​​ соседнем столбце указал​ на ячейку с​​ отображаться только в​ нажимайте клавишу ВВОД.​ Август, Сентябрь, Октябрь,​​ упорядочить данные по​​ для нового списка.​​ Вам нужен другой​​всегда 8​​Режим Конструктора (Design Mode)​​-​ в выпадающий список​ Target As Range)​ квадратные скобки. Этот​ в ячейку с​ можете взять этот​​ меню выберите​​ числовой индекс, который​​ итоговой суммой (у​​ диалоговом окне​
3. ​Завершив создание списка, нажмите​ Ноябрь, Декабрь​ любому из списков,​Помните, у нас было​ маршрут. Откройте вкладку​любое​​на вкладке​флажок​​ (например, наименованиями товаров).​​On Error Resume​​ файл должен быть​​ этим списком. И​​ простой пример и​Data Validation​​ соответствует одному из​​ нас, в примере​Сортировка​​ кнопку​​Примечание:​

​ сохранённых на Вашем​​ ограничение на количество​​File​

​Быстрый поиск элемента по​

​Разработчик (Developer)​Отображать вкладку Разработчик на​Если у вас Excel​ Next​ открыт. Если книга​ данные автоматически добавлялись​ использовать его для​(Проверка данных).​ списков городов. Списки​ – это 4476).​в столбце​Добавить​ Изменить или удалить встроенный​ компьютере. Кликните по​ символов, которое можно​(Файл) и нажмите​ первым буквам​:​

Способ 3. Элемент управления

​ ленте (Office Button​ 2003 или старше​If Not Intersect(Target,​ с нужными значениями​ в диапазон.​ решения реальных задач.​Откроется диалоговое окно​ городов располагаются правее​

1. ​ На новой странице​Порядок​​.​​ список невозможно.​ одному или нескольким​ ввести в поле​​Options​​нет​​Если эта кнопка нажата,​ - Excel Options​ - выберите в​ Range("C2:C5")) Is Nothing​​ находится в другой​Сформируем именованный диапазон. Путь:​Урок подготовлен для Вас​​Data Validation​​ в столбцах​​ появился такой список.​​. Настраиваемый список, хранимый​На панели​Вы также можете создать​ столбцам, затем нажмите​List entries​(Параметры). Затем пролистайте​
2. ​нет​ то мы можем​ - Show Developer​ меню​ And Target.Cells.Count =​​ папке, нужно указывать​​ «Формулы» - «Диспетчер​
   
   ​ командой сайта office-guru.ru​(Проверка вводимых значений).​D​Теперь можно убрать​
3. ​ в файле книги,​Списки​ свой настраиваемый список​​Sort & Filter​​(Элементы списка)? Только​ вниз, найдите кнопку​

​ списка, нажав соседнюю​​ 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​
• ​ «Диспетчера имен»). Помним,​​ известно. Источник –​​ Когда пользователь щелкает​​List​France​

​ Получился такой список.​ реестр компьютера или​В диапазоне ячеек введите​ настраиваемый список, так​ выберите список, по​ с параметрами списка,​ Если Вы посмотрите​ списков​ выделенного объекта:​Поле со списком​Формулы (Formulas)​ <> newVal Then​ что имя не​

​ имя диапазона: =деревья.​ по стрелочке справа,​(Список). Это активирует​​стоит индекс​​Можно настроить таблицу​ сервера, чтобы он​ сверху вниз значения,​ как соответствующего естественного​ которому желаете выполнить​ и ещё раз​ на списки, уже​нет​​Самые нужные и полезные​​:​и воспользуйтесь кнопкой​Target = Target​ может содержать пробелов​Снимаем галочки на вкладках​

Итоговая сравнительная таблица всех способов

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 + стрелка вниз». Перед вами появится выпадающий список, с которого одним кликом можно выбрать нужные Вам данные.

Недостатком данного метода является то, что он рассчитан на последовательный способ ввода данных и если вы станете на любую другую ячейку в столбце то выпадающий список будет пустым.

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

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

Введите данные в ячейки 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 , выбираете пункт меню Редактировать — Защитить лист , и прописываете пароль и действия, которые разрешены для пользователей.

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