Ref-sgain.ru

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

Как создать выпадающий список с возможностью поиска в Excel?

Как создать выпадающий список с возможностью поиска в Excel?

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

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

Создать раскрывающийся список с возможностью поиска в Excel

Например, исходные данные, необходимые для списка рулонов, находятся в диапазоне A2: A9.

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

Если вы не можете найти вкладку «Разработчик» на ленте, активируйте вкладку «Разработчик» следующим образом.

1). В Excel 2010 и 2013 нажмите Файл >Параметры . И в диалоговом окне Параметры нажмите Настроить ленту на правой панели, установите флажок Разработчик , затем нажмите Кнопка ОК . См. Снимок экрана:

2). В Outlook 2007 нажмите кнопку Office >Параметры Excel . В диалоговом окне Параметры Excel нажмите Популярные на правой панели, затем установите флажок Показать вкладку разработчика на ленте . и, наконец, нажмите кнопку OK .

р>

2. После отображения вкладки Разработчик нажмите Разработчик >Вставить >Поле со списком . См. Снимок экрана:

3. Нарисуйте на листе поле со списком и нажмите правую кнопку мыши. В контекстном меню выберите пункт Свойства .

р>

4. В диалоговом окне «Свойства» вы должны

1). Выберите False в поле AutoWordSelect ;

2). Заполните поле LinkedCell адресом ячейки. В данном случае мы вводим A12;

3). Выберите 2-fmMatchEntryNone в поле MatchEntry ;

4). Введите DropDownList в поле ListFillRange ;

5). Закройте диалоговое окно Свойства. См. скриншот:

5. Теперь закройте режим дизайна, нажав Developer >Design Mode .

6. Выберите пустую ячейку C2, а затем скопируйте и вставьте формулу = — ЕЧИСЛО (ЕСЛИОШИБКА (ПОИСК ($ A $ 12, A2,1), «»)) в панель формул и нажмите клавишу Enter. Они перетаскивают его в ячейку C9, чтобы автоматически заполнить выбранные ячейки той же формулой. См. Снимок экрана:

Примечания :

1. $ A $ 12 — это ячейка, которую вы указали в поле LinkedCell на шаге 4;

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

Читайте так же:
Как скопировать данные с защищенного листа?

7. Выберите ячейку D2, введите формулу = IF (C2 = 1, COUNTIF ($ C $ 2: C2,1), «») в панель формул и нажмите клавишу Enter. Затем перетащите маркер заливки в D2 вниз до D9, чтобы заполнить диапазон D3: D9.

8. Выберите ячейку E2, скопируйте и вставьте формулу = ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 9, MATCH (ROWS ($ D $ 2: D2), $ D $ 2: $ D $ 9,0)), «») в формулу Bar и нажмите клавишу Enter. Затем перетащите маркер заполнения в E2 вниз до E9, чтобы заполнить ячейки. Затем вы увидите, что ячейки заполнены, как показано на скриншоте ниже.

9. Теперь вам нужно создать диапазон имен. Нажмите Формула >Определить имя .

10. В диалоговом окне Новое имя введите DropDownList в поле Имя, введите формулу = $ E $ 2: INDEX ($ E $ 2: $ E $ 9, MAX ($ D $ 2: $ D $ 9), 1) в поле Используется, а затем нажмите OK.

11. Теперь включите режим дизайна, нажав Разработчик >Режим дизайна . Затем дважды щелкните поле со списком, созданное на шаге 3, чтобы открыть окно Microsoft Visual Basic для приложений .

12. Скопируйте и вставьте следующий код VBA в редактор кода.

Код V BA: Сделать выпадающий список доступным для поиска

13. Закройте окно Microsoft Visual Basic для приложений .

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

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

Простое создание выпадающего списка с флажками в Excel :

Выпадающий список с Утилита флажков из Kutools for Excel может помочь вам легко создать раскрывающийся список с флажками в указанном диапазоне, текущем листе, текущей книге или всех открытых книгах в зависимости от ваших потребностей.
Загрузите и попробуйте прямо сейчас! (30-дневная бесплатная пробная версия)

Статьи по теме:

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

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

Читайте так же:
Как создать диаграмму в порядке ранжирования в Excel?

Создание раскрывающегося списка с возможностью поиска в Excel
Для раскрывающегося списка с многочисленными значениями найти подходящий — непростая задача. Ранее мы ввели метод автоматического заполнения раскрывающегося списка при вводе первой буквы в раскрывающемся списке. Помимо функции автозаполнения, вы также можете сделать раскрывающийся список доступным для поиска для повышения эффективности работы при поиске правильных значений в раскрывающемся списке. Чтобы сделать раскрывающийся список доступным для поиска, попробуйте метод, описанный в этом руководстве.

Автоматическое заполнение других ячеек при выборе значений в раскрывающемся списке Excel
Давайте скажем, вы создали раскрывающийся список на основе значений в диапазоне ячеек B8: B14. Когда вы выбираете любое значение в раскрывающемся списке, вы хотите, чтобы соответствующие значения в диапазоне ячеек C8: C14 автоматически заполнялись в выбранной ячейке. Для решения проблемы методы, описанные в этом руководстве, окажут вам услугу.

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

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

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

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

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

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

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

Для записи макроса необходимо :

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

Разработчик

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

Макрос

  • Закройте окно, в котором находится макрос.

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

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

Горизонтальный список

Текст макроса:
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»)— это диапазон в пределах которого будет работать выпадающий список.
Аналогичным образом можно создать выпадающий список со смещением вниз и выпадающий список, записывающий в ячейку несколько значений через знак табуляции или пробел.

Читайте так же:
Как создать / сделать общую книгу в Excel?

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

Вертикальный список

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

Похожее:

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

    Добрый день! Похоже, что макрос выпадающего списка с несколькими значениями в одной ячейке не работает. Не знаю почему, но строки ниже почему-то становятся красными. Значит ли это, что B2:B5 можно заменить на другой диапазон, например F2:F200? Или я ошибаюсь? Подскажите, пожалуйста.

    Выпадающий список в Гугл Таблицах — 2 лучших способа создания!

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

    Как создать выпадающий список и как с ним работать

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

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

    Самый простой вариант. Быстрый способ создать, например, простые формы опроса. Если вам не нужно обрабатывать большие объемы данных, вы можете использовать любую форму, которая этого не требует. Чтобы создать таблицу Google, сначала нужно научиться создавать выпадающий список в одной ячейке или в нескольких ячейках сразу:

    • Щелкните левой кнопкой мыши (LKM) на нужной ячейке или выберите сразу несколько ячеек внизу.
    • Щелкните правой кнопкой мыши на выделенной ячейке и выберите в меню пункт «Управление данными».
    • В окне «Управление данными» установите значения в соответствии со следующей таблицей ↓.
    Читайте так же:
    Как скопировать сумму выбранных ячеек только в Excel?

    • Жмем на сохранить.

    Готово. Теперь вы знаете, как создать выпадающий список в Google Spreadsheets.

    • Значения из диапазона. Переключитесь на «Значение из списка» → введите нужные значения → введите нужные значения.
    • Отображение списка прокрутки в ячейке. Если флажок не установлен, ячейка будет иметь значок. Если не выбран — и список будет отображаться при двойном щелчке левой кнопкой мыши.

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

    Еще о работе с выпадающим списком

    Мы выяснили, как создать выпадающий список в Google Spreadsheets. Есть еще несколько настроек, которые можно использовать. В окне проверки данных в строке «Правила» можно выбрать следующие параметры:

    • Число → В диапазоне (Вне диапазона, Больше чем, Больше или равно, Меньше чем, Меньше или равно и т.д.) → введите числа.
    • Текст → Содержит (Не содержит, Равен, Является действительным URL-адресом/адресом электронной почты) → введите нужный текст.
    • Дата → Является действительной датой (Равна, До, После, Указана или до и т.д.) → укажите дату.

    Ячейки могут быть выделены разными цветами (в том числе в зависимости от их содержимого). Затем выделите одну или несколько ячеек правой кнопкой мыши, выберите «Условное форматирование» и назначьте правило выбора цвета в форме справа.

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

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

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

    // Имя рабочего листа
    var tsheet = ‘Результат’; //заменить на имя листа, в котором должна проводиться проверка данных
    // диапазон проверки:
    var rownum = 100; //строка, на которой проверка закончится
    var vcol = 2; //номер колонки (не буква), которая проверяется
    // диапазон с условиями
    var ccols = 3; //номер колонки (не буква), в которой берет начало список условий
    var clen = 10; //сколько условий берется в расчет, максимум
    //……………………………………………………………………..
    for (var i = 2; i <= rownum; i++) <
    // Set the data validation
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getSheetByName(tsheet);
    var cell = sh.getRange(i, vcol, 1, 1);
    var range = sh.getRange(i, ccols, 1, clen);
    var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
    cell.setDataValidation(rule);
    >

    Примечание: между символами «//» находится пояснительная информация — ее не нужно удалять, она не влияет на функциональность скрипта. Вы можете сделать связанный выпадающий список в Google Таблице, изменив только цифры в пояснениях.

    Подготовка таблицы

    Сначала откройте новый документ в Google Электронных таблицах и создайте в нем новый лист («+» в левом нижнем углу окна). Переименуйте лист 1 в «Результат», а лист 2 — в «Данные».

    Читайте так же:
    Как совместить функцию транспонирования и функцию оператора If в Excel?

    Первый шаг — работа с техническим паспортом. Как создать выпадающий список в ячейке в Google Spreadsheets :

    1. В ячейке A1 указано имя/цель первого уровня в списке. Из A2 введите соответствующие значения. В каждом из них должно быть столько повторений, сколько значений во втором уровне.
    2. Ячейка D1 содержит имя/дерево первого уровня в списке. Соответствующие значения вводятся из D2 (по одному за раз).

    Это выглядит так:

    Второй шаг, работа с листом «Результаты»:

    • Перейдите на лист «Результат» и выделите интересующий вас диапазон ячеек. В данном случае с A2 по A13.
    • Щелкните по выделенной ячейке и выберите в меню пункт «Проверить данные». Не изменяйте первую строку в окне «Проверка данных». Поместите курсор в поле справа от «Значения в диапазоне» → переключитесь на лист «Данные» → выберите значения из столбца D, начиная с D2 + столько пустых ячеек вниз, сколько необходимо → Ok → Сохранить.

    Что мы видим перед тем, как нажать «Сохранить»:

    Подготовка завершена. Осталось только прикрепить скрипт к плате.

    Прикрепление скрипта

    • Нажмите на кнопку «Инструменты» и выберите в меню пункт «Редактор сценариев». Откроется окно редактора. Вставьте в него приведенный выше скрипт и отредактируйте его по своему усмотрению.
    • Нажмите «Запуск» в верхней панели. Затем система покажет вам несколько предупреждений и запросов на разрешение.
    • Подождите несколько секунд, пока сценарий не запустится, и на первой странице появится таблица (здесь это «Результат») и выпадающие списки.

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

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