Как выбрать несколько элементов из раскрывающегося списка в ячейку в Excel?
Создайте список элементов, которые будут отображаться в данном списке, как показано на рисунке.
Затем на вкладке Разработчик нажмите Вставить.
Примечание: Если вкладка Разработчик не отображается, на вкладке Файл выберите Параметры > Настроить ленту. В списке основных вкладок установите флажок на вкладке Разработчик и нажмите OK.
В разделе Form Controls выберите элемент управления List (элемент управления формы).
Выделите ячейку со списком, который вы хотите создать.
На вкладке Элемент управления нажмите кнопку Свойства и установите нужные свойства:
В поле Сформировать список по диапазону введите диапазон ячеек, который содержит список значений.
Примечание: Если необходимо отобразить больше элементов в списке, можно изменить размер шрифта для текста.
В поле Link to cell введите ссылку на ячейку.
Эта ячейка содержит номер, связанный с элементом, выбранным в списке. Вы можете использовать его в формуле для получения фактического элемента из диапазона ввода.
В группе возможного выбора установите переключатель одного значения и нажмите кнопку OK.
Примечание: Если вы хотите выбрать параметр набора значений или списка значений, используйте элемент ActiveX «List».
Создание раскрывающегося списка в Excel: использование диапазона
Чтобы создать более гибкий список, в Excel можно выбрать элементы из нескольких ячеек.
- Сначала введите все элементы, которые вы хотите включить в список, в любой столбец ячеек. Вы можете записать их в текущую электронную таблицу или в любую другую таблицу.
- Повторите описанный выше процесс, чтобы выбрать ячейку и открыть окно Проверка данных. Установите для поля Разрешить значение Список. На этот раз вместо того, чтобы вводить что-то в поле Источник, нажмите на значок стрелки вверх справа от поля. Это опция выбора диапазона, которая позволяет выбрать поле, из которого вы хотите извлечь элементы списка.
- Вы увидите, как окно проверки данных уменьшится, и вы сможете видеть весь лист. Перетащите указатель мыши вниз по всему диапазону ячеек, который включает все элементы списка, которые вы хотите включить. В этом случае нажмите на значок стрелки вниз справа от поля выбора. Окно проверки данных снова развернется.
- Вы увидите, что выбранный диапазон теперь отображается в поле Источник. Просто нажмите OK, чтобы принять эти настройки.
- Теперь при выборе стрелки справа от ячейки, которую вы определили как выпадающий список, вы увидите все элементы, включенные в только что выбранный диапазон.
Прелесть этого подхода в том, что вы можете изменить любой элемент в этом списке, просто изменив любую ячейку в диапазоне. Любые внесенные вами изменения будут обновлять все созданные вами выпадающие списки, в которых вы выбрали этот диапазон в качестве источника.
Этот метод лучше всего подходит для форматирования многих ячеек в раскрывающемся списке с использованием одних и тех же элементов списка. Вы можете установить содержимое одного диапазона для управления элементами во всех таких списках, сколько бы их ни было.
Добавление элементов в ваш список
Помимо редактирования элементов в диапазоне для обновления списков, вы также можете добавлять новые элементы. Вы не сможете добавить элемент в конец диапазона, так как выбор диапазона ограничен первой и последней выделенными ячейками.
Вместо этого вам нужно вставить новую запись где-то в середине диапазона. Excel динамически обновит ваш выбор диапазона в настройках проверки данных, чтобы включить новый диапазон, который вы увеличили на одну ячейку.
- Щелкните правой кнопкой мыши на любой ячейке исходного диапазона и выберите во всплывающем меню пункт Вставить.
- Выберите «Переместить ячейки вниз» в маленьком поле и нажмите кнопку «OK». Это переместит все ячейки диапазона на одну ячейку вниз и вставит пустую ячейку в выбранное место.
В пустой ячейке, которую вы только что создали, введите новый элемент, который нужно добавить.
Теперь, когда вы выберете значок стрелки вниз справа от ячейки в созданном вами выпадающем списке, вы увидите новый элемент, который только что добавили в диапазон.
Это простой способ добавления новых элементов в выпадающий список, но он требует нескольких дополнительных действий. Конечно, это не так просто, как добавить новый элемент в конец диапазона.
Для этого достаточно изменить способ проверки диапазона. В следующем разделе мы расскажем вам, как это сделать.
Динамическое добавление элементов в ваш список
Если вы хотите добавить элементы в раскрывающийся список, просто введя новый элемент в конце диапазона, используйте функцию SHIFT.
Выберите «Данные» в меню, а затем «Проверка данных» на ленте.
Измените параметр Источник окна Проверка данных на следующий:
СМЕЩЕНИЕ ($ E $ 1,0,0, COUNTA ($ E: $ E), 1)
Измените $ E $ 1 и $ E: $ E в формуле, чтобы использовать букву столбца, в который вы ввели список товаров. Нажмите OK, чтобы подтвердить новую конфигурацию проверки данных.
Вот как работает формула:
- Аргументы 0 функции OFFSET указывают OFFSET не применять смещения к столбцам или строкам.
- Выход функции COUNTA сообщает OFFSET высоту диапазона.
- Функция COUNTA подсчитывает количество непустых ячеек в столбце, содержащем диапазон.
Теперь, когда вы добавите новое значение в этот столбец, параметр высоты функции OFFSET будет увеличен на единицу, и функция OFFSET вернет весь диапазон, обновленный новым элементом.
Добавьте новую позицию в ассортимент, чтобы увидеть это в действии. Вы увидите новую позицию в раскрывающемся списке, когда нажмете на стрелку справа от раскрывающегося поля.
Пожалуйста, имейте в виду, что вам может понадобиться использовать полосу прокрутки с правой стороны, если список очень длинный.
Параметры и функции поиска в списке
Дополнение для поиска по списку содержит функции, которые значительно упрощают ввод данных и быстрый доступ к спискам. В окне поиска по списку нажмите кнопку меню для просмотра опций.
- Select Next Cell— после нажатия клавиши «Ввод» или «Ввод значения» выбирается ячейка под активной ячейкой. Это поведение можно изменить в раскрывающемся меню направления.
- Down — выбирает ячейку под активной ячейкой.
- Right — выбор ячейки справа от активной ячейки.
- None — не меняет выбор.
- Close — закрывает окно поиска по списку.
- Paste — копирует входное значение в буфер обмена и вставляет его в активную ячейку с помощью метода VBA SendKeys. Окно поиска по списку закрывается. Это единственная опция, которая сохраняет историю отмен в Excel.
ВАЖНО. Примечание.
Использование опции «Вставить» в раскрывающемся списке «Выбрать следующую ячейку» — единственный способ сохранить историю отмены при вводе значений в активную ячейку. Поиск по списку использует макросы для ввода выбранного значения, которые обычно очищают историю отмены в Excel при изменении книги.
Параметр Вставить — это обходной путь, который использует метод SendKeys для копирования и вставки выбранного значения. Это имитирует то, что пользователь будет делать для копирования / вставки, и НЕ очищает историю отмен в Excel.
Как убрать выпадающий список в Excel
Чтобы удалить выпадающий список в ячейке в Excel, необходимо выделить эту ячейку на рабочем листе. Вы можете проверить достоверность данных, перейдя на вкладку Данные. Сначала нажмите кнопку Очистить все, а затем нажмите OK, когда откроется окно. Вот как можно удалить всплывающий список в Excel из любой ячейки.
Как убрать выпадающий список в эксель
Чтобы удалить выпадающий список в excel сразу из всех ячеек одного столбца, необходимо, удерживая клавишу Ctrl, выделить сразу все ячейки столбца. Затем перейдите на вкладку Данные и в ней нажмите на Проверить данные. В открывшемся окне нажмите кнопку Очистить все, а затем нажмите кнопку OK.
Способ 4. Элемент ActiveX
Этот способ частично напоминает предыдущий. Основное отличие в том, что на лист добавляется не элемент управления, а элемент ActiveX «Поле со списком» из раскрывающегося набора под кнопкой Вставить (Insert) с вкладки Разработчик (Developer):
Этот метод частично аналогичен предыдущему
На листе рисуется выбор из списка, а затем добавляется объект. Кроме того, этот метод сильно отличается от предыдущего.
Во-первых, выпадающий список ActiveX может находиться в двух принципиально разных состояниях: в режиме отладки, когда вы можете настраивать его параметры и свойства, перемещать его по листу и изменять его размер, и в режиме ввода, когда единственное, что вы можете делать, это выбирать данные из него. На вкладке Разработчик находится кнопка Режим проектирования, с помощью которой можно переключаться между следующими режимами:
Чтобы переключаться между этими режимами, используйте кнопку Designer Mode.
Если нажать на эту кнопку, параметры выпадающего списка можно настроить с помощью соседней кнопки Properties, которая откроет окно, содержащее список всех возможных параметров для выбранного объекта, то есть наш список:
Самые нужные и полезные функции, которые можно и нужно настраивать
- ListFillRange — диапазон ячеек, из которых берутся данные для списка. Это не позволит вам выбрать диапазон с помощью мыши, просто введите его с клавиатуры (например, Sheet2!A1:A5);
- LinkedCell — связанная ячейка, в которой будет отображаться выбранный элемент списка;
- ListRows — количество строк, отображаемых в выпадающем списке;
- Font — шрифт, размер, стиль шрифта (курсив, подчеркивание и т.д., кроме цвета);
- ForeColor и BackColor — цвет текста и фона соответственно.
При использовании этого метода есть большое преимущество — быстрый доступ к нужному элементу путем ввода первых букв с клавиатуры (!). По сравнению со всеми другими методами это не всегда так.
Кроме того, многомерные диапазоны также могут быть указаны как ListFillRange. Например, вы можете задать диапазон из двух столбцов и нескольких строк, указав при этом, что хотите создать два столбца (ColumnCount=2). Когда вы это сделаете, вы будете вознаграждены невероятно привлекательными результатами, которые окупят все усилия, затраченные на дополнительные настройки:
Можно достичь очень привлекательных результатов, которые вознаграждают все усилия, потраченные на дополнительные настройки.