Как сделать выпадающий список в Excel

Как сделать выпадающий список в Excel

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

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

Сегодня мы с вами и научимся создавать выпадающие списки в Excel

Если вы уже ввели в столбце определенные значения и вам необходимо в следующую ячейку внести какое-либо значение из предыдущих, то достаточно нажать горячие клавиши Alt+стрелка_вниз – появится выпадающий список из значений которые вы вводили выше.

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

1. Выпадающий список состоит только из элементов, которые вы уже вносили

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

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

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

В Excel 2007/2010 Данные | Работа с данными | Проверка данных (смотрите рисунок ниже)

В Excel 2003 вам необходимо перейти в раздел Данные | Проверка (смотрите рисунок)

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

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

Аналогично поступим с городами, но теперь источник укажем в виде именованного диапазона. Для этого выделим исходный диапазон H5:H9, после этого перейдем в поле имени (смотрите рисунок) и присвоим данному диапазону имя: города (имя должно начинаться с буквы и не должно содержать пробелы), обязательно нажимаем после этого Enter.

В дальнейшем для редактирования именованного диапазона вы можете использовать диспетчер имен. Который находится в разделе Формулы | Определенные имена | Диспетчер имен

После этого становимся во вторую желтую ячейку C4, выбираем Данные | Работа с данными | Проверка данных, в раскрывающимся списке выбираем список, а в качестве источника указываем =города. Нажимаем Ок – получаем выпадающий список с использованием именованного диапазона.

Двухуровневый выпадающий список в excel

Выпадающий список с добавлением новых элементов

Итак, имеем следующую ситуацию:

Задача : сделать в ячейке D2 выпадающий список, чтобы пользователь мог выбирать имена из списка (столбец А). Если нужного имени нет в списке, то пользователь может ввести новое имя прямо в ячейку D2 — оно автоматически добавится к столбцу А и начнет отображаться в выпадающем списке в будущем.

Шаг 1. Создаем именованный диапазон

Сначала создадим именованный диапазон , указывающий на заполненные именами ячейки в столбце А — сколько бы имен в списке не находилось. Для этого идем в меню Вставка — Имя — Присвоить (Insert — Name — Define) , вводим имя диапазона (допустим People ) и в строку Ссылка (Reference) вводим следующую формулу:

в английской версии Excel это будет:

Эта формула ссылается на все заполненные ячейки в столбце А, начиная с А1 и вниз до конца — до последнего имени.

Шаг 2. Создаем выпадающий список в ячейке

Выделяем ячейку D2 и открываем меню Данные — Проверка (Data — Validation) . Далее выбираем из выпадающего списка Тип ( Allow) позицию Список (List) и вводим в строку Источник (Source) ссылку на созданный на шаге 1 именованный диапазон:

Чтобы Excel позволил нам в будущем ввести в список и новые имена, снимем галочки на вкладках Сообщение для ввода (Input Message) и Сообщение об ошибке (Error Alert) и нажмем ОК . Теперь у нас есть выпадающий список в ячейке D2. Причем, если, например, вручную дописать новое имя в столбце А, то оно автоматически появится в выпадающем списке в ячейке D2, поскольку имена берутся из динамического диапазона People, который автоматически отслеживает изменения в столбце А.

Шаг 3. Добавляем простой макрос

Щелкаем правой кнопкой мыши по ярлычку нашего листа и выбираем Исходный текст (View Source) . Откроется редактор Visual Basic, куда надо скопировать такой код:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lReply As Long

If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$D$2" Then
If IsEmpty(Target) Then Exit Sub
If WorksheetFunction.CountIf(Range("People"), Target) = 0 Then
lReply = MsgBox("Добавить введенное имя " & _
Target & " в выпадающий список?", vbYesNo + vbQuestion)
If lReply = vbYes Then
Range("People").Cells(Range("People").Rows.Count + 1, 1) = Target
End If
End If
End If
End Sub

Если Ваш выпадающий список находится не в ячейке D2 или Вы назвали диапазон с именами не People , а как-то еще, то подправьте эти параметры в макросе на свои.

Всё! Теперь при попытке ввести новое имя в ячейку D2 Excel будет спрашивать

. и при утвердительном ответе пользователя автоматически добавлять новое имя к списку в столбце А и в выпадающий список в ячейку D2.

Понятие же «физического вакуума» в релятивистской квантовой теории поля подразумевает, что во-первых, он не имеет физической природы, в нем лишь виртуальные частицы у которых нет физической системы отсчета, это «фантомы», во-вторых, «физический вакуум» — это наинизшее состояние поля, «нуль-точка», что противоречит реальным фактам, так как, на самом деле, вся энергия материи содержится в эфире и нет иной энергии и иного носителя полей и вещества кроме самого эфира.

В отличие от лукавого понятия «физический вакуум», как бы совместимого с релятивизмом, понятие «эфир» подразумевает наличие базового уровня всей физической материи, имеющего как собственную систему отсчета (обнаруживаемую экспериментально, например, через фоновое космичекое излучение, — тепловое излучение самого эфира), так и являющимся носителем 100% энергии вселенной, а не «нуль-точкой» или «остаточными», «нулевыми колебаниями пространства». Подробнее читайте в FAQ по эфирной физике.

Как в экселе сделать выпадающий список?

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

Использование комбинаций клавиш

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

То же самое можно осуществить, нажав комбинацию Alt+»Стрелочка вниз». Выпадет список, где отобразятся с ранее вводимые значения. Как видно из нижнего изображения, программой были предложены 4 возможных варианта для заполнения. Для нормальной функциональности этого инструмента между списком и областью, куда вводятся данные, не допускаем наличие пустых ячеек.
Применять список для заполнения можно как в верхней, так и в нижней ячейке. Верхняя ячейка заполнится нижними значениями. Пустоты между ячейками по-прежнему исключены.

Создание данных для списка

Применение этого способа может подразумевать нахождение данных там, где присутствует сама таблица или на ином листе эксель.

• Создаем данные, которые будут служить основой для появляющегося в дальнейшем списка. Выделив их, вызываем меню и нажимаем на «Присвоить имя…».
  
• Впишите имя в окне «Создание имени», которым список и будет обозначаться. Оно должно начинаться с буквы и не иметь пробелов.
  
• Сделайте выделение тех областей ячеек, где планируете организовать выпадающий список. В верхней области документа есть пункт «ДАННЫЕ». Зайдите в неё и выберете «Проверка данных».
  
• Выберите «Список», а как тип данных в окне «проверка вводимых значений». В виде источника впишите: = и имя списка, который был создан. При использовании этой формулы вводиться данные будут из списка. Будет произведена проверка с предложенными вариантами, которые и будут являться выпадающим списком.
  

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

Как в экселе сделать выпадающий список, используя элемент ActiveX

Теперь рассмотрим, как в экселе сделать выпадающий список с помощью ActiveX. Подобный вариант подразумевает рабочую вкладку «РАЗРАБОТЧИК», которая изначально не включена. Для её активации необходимо:

1. В приложении нажмите на «Файл».
2. Далее выбираем «Параметры».
3. В настройках программы заходим в «Настроить ленту» и возле «Разработчик» ставим отметку.

Теперь «Поле со списком (Элемент ActiveX)» становится доступным. Зайдите в раздел «РАЗРАБОТЧИК», кликните на «Вставить». В поле «элементы ActiveX» нажмите на «Поле со списком (Элемент ActiveX)».

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

Для его настройки, необходимо зайти в «Режим конструктора» и кликнуть на «Свойства». Далее вы увидите открывшееся окно Properties.

В открытом окне, жмите на «Поле со списком». В окне свойств в виде списка содержится множество параметров, которые поспособствуют индивидуальной настройке объекта. Рассмотрим 3 главные для нас:

1. ListFillRange – обозначает диапазон со значениями для выпадающего списка.
2. ListRows – данные в выпадающем списке. В ActiveX их может быть гораздо больше, чем в первом способе.
3. ColumnCount – показывает необходимое количество столбцов.
   В примере получилось:
   

Наглядно стало ясно, каким есть список с возможностью перенесения данных из столбца «Поставщик».

Минутку внимания, друзья! Совсем недавно я опубликовал пару статей на тему как удалить вирус SVCHOST.EXE netsvcs или как подключить автопродление скорости билайн, а так же что такое дискретная видеокарта. Ну и на последок не пропустите узнать про Catalyst control center или как проверить айфон на подлинность, а так же какой браузер самый лучший.

Выводы

Теперь вы знаете, как в экселе сделать выпадающий список. Я уверен, что эти знания сделают вашу работу в excel более удобной. Понравилась статья? Ставьте лайки, и нажимайте кнопки социальных сетей. Мира и добра вам!

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

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

Делаем пошагово

Как он работает — вы заранее формируете список, а затем в определенных полях выбираете нужное значение из списка, мышкой. Экономит время и нервы.

Реализуется это просто:

• Сначала формируете сам список данных.

• Выделяете те поля, которые планируете заполнять данными.

• Затем заходите во вкладку Данные, команда Проверка данных – Тип данных (Список), в поле Источник ставите диапазон.

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

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

• Отмечаете галочкой поле «Игнорировать пустые ячейки».

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

Нюанс – при таком методе в ячейку можно ввести значения, ТОЛЬКО содержащиеся в списке.

Нюансы

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

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

Второй способ

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

Для этого надо зайти на вкладку Разработчик (Excel 2007-2010). По умолчанию эта вкладка не показывается. Чтобы ее открыть надо зайти в Параметры (Офис/Параметры Excel/Основные/Показывать вкладку «Разработчик» на ленте).

Как сделать множественный выбор в раскрывающихся списках в Google Таблицах

Основная цель раскрывающихся списков в Google Таблицах — предлагать варианты, из которых пользователь может выбирать. Это дает пользователям четкое представление обо всех доступных параметрах, а также гарантирует, что пользователь выбирает только разрешенные элементы.

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

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

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

Часто вам может потребоваться выбрать более одного варианта в раскрывающемся списке . Например, если есть набор цветов, из которого вы можете выбрать, вам может понравиться более одного цвета.

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

Поэтому множественный выбор в раскрывающихся списках может быть весьма полезным. К сожалению, эта опция традиционно не разрешена в Google Таблицах. Вам разрешен только один вариант за раз.

Хорошая новость в том, что есть способ обойти это. Можно сделать ваш раскрывающийся список допускающим множественный выбор с помощью Google AppScript.

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

Но сначала начнем с нуля.

Начнем с создания нового раскрывающегося списка из списка вариантов цвета.

Разрешение множественного выбора в раскрывающемся списке (с повторением)

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

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

• Создайте раскрывающийся список, используя список элементов
• Добавьте в редактор скриптов функцию, которая позволит выбрать несколько вариантов в раскрывающемся списке.

Давайте подробно рассмотрим каждый из этих шагов.

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

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

Ниже приведены шаги для этого:

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

Теперь выпадающий список появится в выделенной ячейке (в данном примере C1). Когда вы нажмете на стрелку, вы увидите свой список опций.

Обратите внимание, что вам разрешено выбирать только один вариант за раз.

Теперь позвольте мне показать вам, как преобразовать этот раскрывающийся список (который позволяет отображать только один элемент в ячейке) в тот, который позволяет выбирать несколько элементов. А для этого вам нужно добавить скрипт функции в редактор скриптов Google Таблиц.

Добавление скрипта Google Apps для включения множественного выбора

Ниже приведен код сценария, который вам придется скопировать и вставить в редактор сценариев (шаги, указанные ниже в разделе после кода):

Ниже приведены шаги по добавлению этого кода сценария в бэкэнд Google Таблиц, чтобы раскрывающийся список, который мы создали в ячейке C1, мог позволить выбрать более одного варианта:

• Выберите в меню пункт Инструменты.
• Щелкните Редактор сценариев. Это откроет редактор сценариев в новом окне.
• В окне Code.gs удалите все, что уже есть, скопируйте и вставьте указанный выше код макроса.
• Нажмите кнопку Сохранить на панели инструментов (или используйте сочетание клавиш Control + S).
• Дайте проекту имя (это нужно сделать только один раз)
• Закройте окно сценария (если хотите)

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

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

Примечание. В правом верхнем углу ячейки вы увидите красный треугольник. Это может выглядеть как ошибка (поскольку значение в ячейке не соответствует ожидаемому). Вы можете спокойно игнорировать это.

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

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

Как работает код?

Попробуем разобраться в этом коде по частям.

Код начинается со строки

onEdit () — это специальная функция в Google Таблицах. Она также известна как обработчик событий. Эта функция запускается каждый раз при изменении вашей электронной таблицы.

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

Теперь AppScript передает эту функцию как объект события в качестве аргумента. Обычно объект события называется e. Этот объект события содержит информацию о инициированном событии.

Если вы знакомы с основами AppScript, вы обнаружите, что первые четыре строки довольно легко понять:

Я объявил две переменные — одну (oldValue), которая будет содержать старое значение ячейки, и другую (newValue), которая будет содержать новое значение ячейки.

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

Теперь мы не хотим, чтобы код запускался каждый раз при редактировании какой-либо ячейки. Мы хотим, чтобы он запускался только при редактировании ячейки CA1 Sheet1. Поэтому мы убеждаемся в этом, используя оператор if:

Приведенный выше код проверяет номер строки и столбца активной ячейки и имя листа. Поскольку раскрывающийся список находится в ячейке C1, он проверяет, равен ли номер строки 1 или нет, а также равен ли номер столбца 3 или нет.

Код в операторе IF выполняется только при соблюдении всех этих трех условий.

Ниже приведен код, который выполняется, когда мы находимся в правой ячейке (C1 в нашем примере).

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

Мы хотим присвоить это переменной oldValue.

e.value — это свойство объекта события, e. В нем хранится текущее значение активной ячейки. Мы хотим присвоить это переменной newValue.

Во-первых, давайте посмотрим, что произойдет, если не выбран ни один из вариантов. В этом случае e.value будет неопределенным. Когда это происходит, мы не хотим, чтобы в ячейке A1 отображалось что-либо. Поэтому мы помещаем в ячейку пустое значение.

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

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

Это означает, что e.oldValue не определено. Когда это происходит, мы хотим, чтобы в ячейке A1 отображался только выбранный параметр (newValue).

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

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

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

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

Однако здесь есть небольшая проблема. Обратите внимание, что если вы выберете элемент более одного раза, он снова будет внесен в ваш список выбора. Другими словами, повторение разрешено. Но обычно мы этого не хотим.

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

Разрешение множественного выбора в раскрывающемся списке (без повторения)

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

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

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

Здесь функция indexof () проверяет, содержит ли строка в oldValue строку в newValue.

Если это так, то он вернет индекс строки в oldValue. В противном случае он вернет значение меньше 0.

Если вновь выбранная опция действительно существует в нашем списке, мы хотим оставить список как есть (поэтому мы заполняем ячейку C1 предыдущим значением). Если нет, то мы хотим добавить вновь выбранный параметр в список с запятой (‘,’) и отобразить его в ячейке C1.

Множественный выбор в раскрывающемся списке (весь столбец или несколько ячеек)

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

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

со следующей строкой кода:

Когда вы это делаете, мы только проверяем, равен ли столбец 3 или нет. Любые ячейки, которые находятся на листе Sheet1 и в столбце 3, будут удовлетворять этому критерию IF, и любой раскрывающийся список в нем допускает множественный выбор.

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

Вышеупомянутая строка использует условие ИЛИ в операторе IF, где проверяется, равен ли номер столбца 3 или 6. Если ячейка с раскрывающимся списком находится в столбце C или F, будет разрешен выбор нескольких элементов.

Точно так же, если вы хотите, чтобы это было включено для нескольких ячеек, вы также можете сделать это, изменив код.

Вот как вы можете включить множественный выбор в раскрывающемся списке в Google Таблицах. Хотя это недоступно в качестве встроенной функции, вы можете легко сделать это с помощью магии Google Apps Script.