Svinkovod.ru

Бытовая техника
7 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

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

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

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

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

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

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

  1. Вручную через «точку-с-запятой» в поле «Источник». Ввод значений.
  2. Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком. Проверка вводимых значений.
  3. Назначить имя для диапазона значений и в поле источник вписать это имя.

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

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

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

  1. Выделяем диапазон для выпадающего списка. В главном меню находим инструмент «Форматировать как таблицу». Форматировать как таблицу.
  2. Откроются стили. Выбираем любой. Для решения нашей задачи дизайн не имеет значения. Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья». То есть нужно выбрать стиль таблицы со строкой заголовка. Получаем следующий вид диапазона: Выпадающий список.
  3. Ставим курсор в ячейку, где будет находиться выпадающий список. Открываем параметры инструмента «Проверка данных» (выше описан путь). В поле «Источник» прописываем такую функцию:

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

Список и таблица.

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

Добавлено значение елка.

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

Удалено значение береза.

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

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

  1. Сформируем именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Вводим уникальное название диапазона – ОК. Создание имени.
  2. Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
  3. Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения. Сообщение об ошибке.
  4. Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
  5. Сохраняем, установив тип файла «с поддержкой макросов». Сообщение об ошибке.
  6. Переходим на лист со списком. Вкладка «Разработчик» — «Код» — «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

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

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

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

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

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

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

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

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

Читайте так же:
Гистограмма с группировкой в excel как построить

Три именованных диапазона.

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

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

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

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

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

Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.

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

  1. На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки). Вставить ActiveX.
  2. Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка. Элемент ActiveX.
  3. Жмем «Свойства» – открывается перечень настроек. Свойства ActiveX.
  4. Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.

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

Выпадающий список в Excel — Инструкция по созданию

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

vypadajushhij-spisok-v-excel-№9

Содержание:

Несколько наиболее распространенных типов выпадающих списков, которые можно создать в программе Excel:

  1. С функцией мультивыбора;
  2. С наполнением;
  3. С добавлением новых элементов;
  4. С выпадающими фото;
  5. Другие типы.

Сделать список в Эксель с мультивыбором

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

Рассмотрим подробнее все основные и самые распространенные типы, и процесс их создание на практике.

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

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

Чтобы создать такой, следуйте инструкции:

  • Выделите ячейки. Если посмотреть на рисунок, то выделять нужно начиная с C2 и заканчивая C5;
  • Найдите вкладку «Данные», которая расположена на главной панели инструментов в окне программы. Затем нажмите на клавишу проверки данных, как показано на рисунке ниже;

vypadajushhij-spisok-v-excel-№2-650x333

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

vypadajushhij-spisok-v-excel-№3

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

Пример заполнения:

Таким образом вы создадите классический горизонтальный список с функцией мультивыбора.

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

vypadajushhij-spisok-v-excel-№10-650x365

Программный код для создания макроса

Еще рекомендуем прочитать эти статьи:

Создать список в Экселе с наполнением

Стандартный перечень с наполнением позволит вам автоматизировать процесс ввода информации. При нажатии на ее, появится выбор ее возможных значений.

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

Пользовательский список с наполнением

Самый простой способ создать такой перечень – использовать «умные таблицы программы».

С их помощью можно легко и быстро форматировать необходимые вам виды списков с наполнением:

  • Выделите необходимые ячейки и нажмите в главной вкладке на клавишу «Форматировать как таблицу»;

Пример форматирования и расположение клавиш:

vypadajushhij-spisok-v-excel-№5

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

vypadajushhij-spisok-v-excel-№6

Форматирование перечня с наполнением с помощью «умных таблиц»

Создать раскрывающийся список в ячейке (версия программы 2010)

Также можно создавать перечень в ячейке листа.

Пример указан на рисунке ниже:

Пример в ячейке листа

Чтобы создать такой, следуйте инструкции:

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

Заполнение и проверка вводимых значений в процессе создания списка с заполнением ячейки листа

Также вам может быть интересно:

Итоги

В статье были рассмотрены основные типы выпадающих списков и способы их создания. Помните, что процесс их создания идентичен в таких версиях программы: 2007, 2010, 2013.

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

Тематические видеоролики к статье:

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

4 способа создать выпадающий список на листе Excel.

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

Как в ексель сделать выпадающий список Как добавить всплывающий список в ексель Как создать в ексел раскрывающийся список

Богдан Вязовский

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

Excel функциональный макрос: создание выпадающего списка

Я хочу создать функцию, которая превращает ячейку в выпадающий список со значениями в ней.

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

А в камере это будет записано и перенесено в нижние камеры

3 ответа

  • Считывание данных Excel из выпадающего списка в массив объектов C#

Изнутри C# я пытаюсь прочитать данные с листа Excel в объект C#. Все работает нормально, за исключением одной маленькой детали-Excel данных из выпадающих списков. Каким-то образом результат в массиве равен null для каждой соответствующей записи выпадающего списка Excel. Мой код до сих пор ниже.

Я совершенно новичок в мире macros, но уже делал некоторые VBScript на QTP раньше. Я хочу создать макрос, который скрывал бы определенные столбцы в зависимости от выбора пользователей из выпадающего списка. Я не уверен в синтаксисе и в том, как идентифицировать столбцы, которые я хочу скрыть, и.

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

если вам нужен этот код, вы можете попробовать этот код:

или вы можете использовать массив un с динамическим размером и получать элементы из ячеек

Надеюсь, это поможет вам

Существует способ обойти этот тип требований, но он может не соответствовать вашим потребностям.

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

Шаг 2 . Вы не можете ссылаться на эту функцию непосредственно из настройки списка проверки, поэтому вам необходимо использовать имя книги для «route» вызова проверки функции:

enter image description here

Шаг 3 . Создайте раскрывающийся список в ячейке, ссылаясь на только что созданное имя (которое, в свою очередь, ссылается на функцию VBA). Теперь вы получите список, когда в ячейке слева есть «Yes», но в противном случае списка нет. Или вместо этого вы можете использовать один и тот же подход для возврата двух разных списков, в зависимости от содержимого в других ячейках.

enter image description here

  • функциональный макрос против macros

gcc (GCC) 4.7.2 c89 Привет, Я смотрел на набор тестов и заметил этот функциональный макрос, объявленный следующим образом: #define MU_SUITE_START() char *msg = NULL Однако есть ли какая-то реальная разница в том, чтобы делать именно это вместо этого: #define MU_SUITE_START char *msg = NULL Macros.

Мне интересно, можно ли скрыть столбцы на основе выбора выпадающего списка с помощью excel. Например,предположим,что у меня есть 3 столбца( C1, C2, C3) и у меня есть выпадающий список с 2 значениями(drop1 и drop2). Когда drop1 выбран из выпадающего списка,то покажите C1, C2 и скройте C3. Если.

Итак, изучив все ответы, которые мне предоставили, я нашел эту ссылку, https://www.excel-easy.com/vba/events.html

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

Похожие вопросы:

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

Есть ли в Excel 2013 функция, которая позволила бы мне выбрать значение выпадающего списка на основе значения другого поля? Например: я хочу иметь 2 варианта в моем выпадающем списке (True и False).

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

Изнутри C# я пытаюсь прочитать данные с листа Excel в объект C#. Все работает нормально, за исключением одной маленькой детали-Excel данных из выпадающих списков. Каким-то образом результат в.

Я совершенно новичок в мире macros, но уже делал некоторые VBScript на QTP раньше. Я хочу создать макрос, который скрывал бы определенные столбцы в зависимости от выбора пользователей из выпадающего.

gcc (GCC) 4.7.2 c89 Привет, Я смотрел на набор тестов и заметил этот функциональный макрос, объявленный следующим образом: #define MU_SUITE_START() char *msg = NULL Однако есть ли какая-то реальная.

Мне интересно, можно ли скрыть столбцы на основе выбора выпадающего списка с помощью excel. Например,предположим,что у меня есть 3 столбца( C1, C2, C3) и у меня есть выпадающий список с 2.

Нужно создать checkbox внутри выпадающего списка в excel 2010. Мы уже пробовали создать список и выбрали опцию multiselectExtended, но это не служит нашей цели. Образец необходимого функционала.

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

Я добавил выпадающие списки в некоторых столбцах листа excel, используя Apache poi. Когда одно из выпадающих значений содержит символ дефиса ( — ), то при открытии листа excel он выдает ошибку — мы.

if not intersect target range

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

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

Для начала следует создать обыкновенный выпадающий список.

Для этого необходимо:

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

После этой процедуры следует записать макрос в документ.

Для записи макроса следует:

  • Открыть вкладку «Разработчик» ( Если вкладка отключена, включите ее в разделе Файл=> Параметры=> Настройка Ленты);

  • Во вкладке «Разработчик» выбрать кнопку «Просмотр кода»;
  • В открывшееся окно записать макрос;

  • Закрыть окно с макросом.

Давайте рассмотрим несколько макросов с выпадающими списками.

Первый макрос со смещением списка в сторону (горизонтально).


Текст макроса:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range(«B2:B10»)) 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
Необходимо обратить внимание, что в строке :
If Not Intersect(Target, Range(«B1:B10»)) Is Nothing And Target.Cells.Count = 1 Then
Значения («B1:B10»)— это диапазон в пределах которого будет работать выпадающий список.
Аналогичным образом можно создать выпадающий список со смещением вниз и выпадающий список, записывающий в ячейку несколько значений через знак табуляции или пробел.

Макрос выпадающего списка со смещением вниз:


Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range(«C2:F2»)) 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

Макрос выпадающего списка с внесением нескольких значений в одну ячейку:


Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range(«B2:B5»)) 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

В строке If Not Intersect(Target, Range(«B2:B5»)) Is Nothing And Target.Cells.Count = 1 Then
указывается диапазон действия макроса.
В строке
Target = Target & «//» & newVal
указывается разделитель «//». Его можно заменить на любой знак препинания, текст или поставить пробел.

Похожее:

  1. Макрос определяющий пустая ли ячейка или заполненная в VBA ExcelМакрос проверки заполнения ячеек. Периодически при создании.
  2. Функция VAL в VBA Excel или как преобразовать TextBox в число (цифру).Использования функции преобразования текста в число в.
  3. Макрос для быстрой замены формул на значения (числа) в выделенных ячейках документа Excel.Когда удобно менять формулы на значения нажатием.

Макрос выпадающего списка с несколькими значениями в Excel: 2 комментария

Добрый день! Макрос выпадающего списка с внесением нескольких значений в одну ячейку почему то не работает. Нижеприведенные строки почему то становятся красным. Я так понимаю В2:В5 это диапазон который можно изменять на другую область например на F2:F200 допучстим? Или я не прав? Подскажите пожалуйста.

I am relatively new to VBA and I need help with this please.

I have a private sub within a sheet and I want it to autofill formulas adjacent to a dynamic named range, if the size of the range changes.

(edit) I am pasting data from another worksheet into this one columns A-M. My dynamic range is defined as =OFFSET($A$1,1,0,COUNTA($A:$A)-1,13). The first If statement should exit the sub if there is no data in column M and I had the destination calculating the last row of column M because I want to fill the formulas in N:O so that they cover the same number of rows as column M.

This is my code and it works if the size of the range gets smaller (i.e. if I delete rows from the bottom), but not if it gets bigger and I can’t work out why!

I put the last bit into a separate macro to test if it works on its own and for some reason, when I run it, the autofill goes all the way up to row 1 and overwrites the formulas, which is weird because I use that code a lot and it’s never done that before. What have I done.

Also, if there is a better way to do the autofill I’d appreciate if someone could let me know what it is because I just cobbled that together from bits I found on forums

Возвращает объект Range , представляющий прямоугольное пересечение двух или более диапазонов. Returns a Range object that represents the rectangular intersection of two or more ranges. Если указаны один или несколько диапазонов из другого листа, возвращается ошибка. If one or more ranges from a different worksheet are specified, an error is returned.

Синтаксис Syntax

выражение: переменная, представляющая объект Application. expression A variable that represents an Application object.

Параметры Parameters

Имя NameОбязательный или необязательный Required/OptionalТип данных Data typeОписание Description
Arg1 Arg1Обязательный RequiredRange RangeПересекающиеся диапазоны. The intersecting ranges. Необходимо указать по крайней мере два объекта Range . At least two Range objects must be specified.
Arg2 Arg2Обязательный RequiredRange RangeПересекающиеся диапазоны. The intersecting ranges. Необходимо указать по крайней мере два объекта Range . At least two Range objects must be specified.
Arg3Arg30 Arg3Arg30Необязательный OptionalVariant VariantПересекающийся диапазон. An intersecting range.

Возвращаемое значение Return value

Пример Example

В примере ниже показано, как выбрать пересечение двух именованных диапазонов, Rg1 и RG2, на листе Sheet1. The following example selects the intersection of two named ranges, rg1 and rg2, on Sheet1. Если диапазоны не пересекаются, в примере отображается сообщение. If the ranges don’t intersect, the example displays a message.

В следующем примере сравнивается свойство листа. Range , метод Application. Union и метод Intersect . The following example compares the Worksheet.Range property, the Application.Union method, and the Intersect method.

Поддержка и обратная связь Support and feedback

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Have questions or feedback about Office VBA or this documentation? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь. Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector