Автоматическое заполнение ячеек при выборе значения из раскрывающегося списка в excel vba
Если вы еще не знаете про такой прием в Excel, как автозаполнение ячеек путем протягивания мышью крестика - то самое время это про него узнать. Эта возможность очень полезная. Что дает автозаполнение: допустим вы хотите заполнить строку или столбец днями недели(Понедельник, Вторник и т.д.). Человек, который про автозаполнение не знает последовательно вводит в каждую ячейку вручную все эти дни. Но в Excel для выполнения подобной операции вам потребуется заполнить лишь первую ячейку. Запишем в неё Понедельник. Теперь выделяем эту ячейку и ведем курсор мыши к нижнему правому углу ячейки. Курсор приобретет вид черного крестика:
Как только курсор стал крестиком, жмем левую кнопку мыши и удерживая её тянем вниз(если надо заполнить строки) или вправо(если надо заполнить столбцы) на необходимое количество ячеек. Теперь все захваченные нами ячейки заполнены днями недели. И не одним Понедельником, а по порядку следования:
для заполнения вниз подобным методом большого количества строк можно не тянуть за крестик, а быстро дважды нажать левую кнопку мыши на ячейке, как только курсор приобретет вид крестика
Напрашивается вопрос: так можно сделать только с днями недели или есть еще какие-то возможности? Ответ - есть, и немалые.
Если вместо левой кнопки мыши, зажать правую и протянуть, то по завершении Excel выдаст меню, в котором будет предложено выбрать метод заполнения: Копировать ячейки, Заполнить, Заполнить только форматы, Заполнить только значения, Заполнить по дням, Заполнить по рабочим дням, Заполнить по месяцам, Заполнить по годам, Линейное приближение, Экспоненциальное приближение, Прогрессия:
Выбираете необходимый пункт и вуаля!
Серым шрифтом выделены неактивные пункты меню - те, которые нельзя применить к данным в выделенных ячейках
Подобное автозаполнение доступно для числовых данных, для дат и некоторых распространенных данных - дней недели и месяцев.
Однако помимо использования встроенных в Excel списков автозаполнения, можно создать и свои списки. Например, Вы часто заполняете шапку таблицы словами: Дата, Артикул, Цена, Сумма. Можно их вписывать каждый раз или копировать откуда-то, но можно сделать и по-другому. Если Вы используете:
Выбираете пункт НОВЫЙ СПИСОК - ставите курсор в поле Элементы списка и заносите туда через запятую наименования столбцов, как показано на рисунке выше. Нажимаем Добавить.
Так же можно воспользоваться полем Импорт списка из ячеек. Активируем поле выбора, щелкнув в нем мышкой. Выбираем диапазон ячеек со значениями, из которых хотим создать список. Жмем Импорт. В поле Списки появиться новый список из значений указанных ячеек.
Теперь остается проверить в действии. Пишем в любую ячейку слово Дата и протягиваем, как описано выше. Excel заполнил нам остальные столбцы значениями из того списка, который мы сами только что создали. Вы можете изменять и удалять, созданные Вами списки, добавлять новые.
Так же эти списки можно использовать и для сортировки значений. Для этого выделяем нужные для сортировки ячейки -переходим на вкладку Данные -Сортировка. Раскрываем поле порядок -Настраиваемый список. Выбираем созданный список.
Созданные пользователем списки доступны из любой книги на том ПК, на котором эти списки были созданы.
Excel обладает очень неплохим инструментом для проверки введенных данных. В их число входит создание выпадающего списка. В этом случае в одной ячейке может содержаться несколько значений, организованных в виде списка
Предназначен такой список не только для удобства ввода, но и для контроля вводимых в ячейку данных. Создав такой список можно настроить его так, чтобы пользователь имел возможность вводить в ячейку только значения из списка (такие настройки используются по умолчанию) или использовал список, но мог так же ввести любое значение не из списка.
Создание списков
Необходимо выбрать ячейку(или сразу несколько ячеек), в которую необходимо поместить этот список. В меню выбираем Данные (Data) -Проверка данных (Data Validation) . Переходим на вкладку Параметры (Settings) и в выпадающем списке Тип данных (Allow) выбираем Список (List) .
Поле Источник (Source) : предназначено как раз для ввода значений, из которых будет состоять выпадающий список. Варианты указания значений:
- Вариант 1
Ввести список значений вручную (значения в этом случае необходимо заносить через "точку-с-запятой" для русской локализации и через запятую для английской). - Вариант 2
Указание в качестве источника обычной ссылки на диапазон ячеек
При использовании обычного диапазона в поле Источник (Source) просто указываем диапазон ячеек со значениями (A1:A10). Для этого ставим курсор мыши в это поле и затем выделяем необходимый диапазон со значениями. Перед адресом диапазона Excel сам поставит знак равно: =$A$1:$A$10
Недостаток данного метода: нельзя применять ссылки на другие листы и книги. Список будет работать только в случае, если ячейки расположены на том же листе, что и сам список. Что порой не очень удобно, поэтому чаще используется Вариант 3. - Вариант 3
Указание в качестве источника именованного диапазона
При использовании именованного диапазона необходимо сначала его создать: Создание именованных диапазонов. Предположим, что мы создали именованный диапазон с именем Список1.
После создания именованного диапазона в поле Источник (Source) вписываем имя этого диапазона, не забыв поставить перед именем знак равно:
Параметры списков
Распространить изменения на другие ячейки с тем же условием (Apply this chages to all other cells with the same settings) - данный пункт понадобится уже после создания списка в ячейках: если на листе есть много разных проверок данных необходимо изменить тип или условия проверки. Выделяете одну ячейку на листе, меняете необходимые параметры проверки данных и ставите данную галку. Нажимаете ОК. Внесенные изменения будут применены для всех ячеек, в которых были такие же условия.
Подробнее с остальными возможностями проверки данных(помимо выпадающих списков) можно ознакомиться в этой статье: Проверка данных
Как выполнить автозаполнение при вводе в раскрывающемся списке Excel?
Для раскрывающегося списка проверки данных с большим количеством элементов вам необходимо прокрутить список вверх и вниз, чтобы найти тот, который вам нужен, или правильно ввести все слово в поле списка. Есть ли какие-либо методы для создания раскрывающегося списка с автозаполнением при вводе первой буквы или любых относительных символов? Это поможет людям более эффективно работать с листами с раскрывающимися списками в ячейках. В этом руководстве представлены два метода, которые помогут вам в этом.
Автозаполнение при вводе раскрывающегося списка с кодом VBA
Чтобы раскрывающийся список автоматически заполнялся после ввода соответствующих букв в ячейке, сделайте следующее.
Во-первых, вам нужно вставить поле со списком в рабочий лист и изменить его свойства.
1. Откройте рабочий лист, содержащий ячейку раскрывающегося списка, для которой требуется выполнить автозаполнение.
2. Перед вставкой поля со списком вам необходимо добавить вкладку «Разработчик» на ленту Excel. Если на ленте отображается вкладка Разработчик, перейти к шагу 3. В противном случае сделайте следующее: Нажмите Файл > Доступные опции для открытия Доступные опции окно. В этом Параметры Excel окна, нажмите Настроить ленту на левой панели проверьте разработчик поле, а затем щелкните OK кнопка. Смотрите скриншот:
3. Нажмите разработчик > Вставить > Поле со списком (элемент управления ActiveX).
4. Нарисуйте поле со списком на текущем листе. Щелкните его правой кнопкой мыши и выберите Свойства из контекстного меню.
5. в Свойства диалоговое окно, замените исходный текст в (Имя) поле с ТемпКомбо.
6. Выключите Режим проектирования кликнув разработчик > Режим дизайна.
Затем примените приведенный ниже код VBA
7. Щелкните правой кнопкой мыши вкладку текущего листа и выберите Просмотреть код из контекстного меню. Смотрите скриншот:
8. В дебюте Microsoft Visual Basic для приложений окна, скопируйте и вставьте приведенный ниже код VBA в окно кода рабочего листа.
Код VBA: автозаполнение при вводе в раскрывающемся списке
9. Нажмите другой + Q клавиши одновременно, чтобы закрыть Приложения Microsoft Visual Basic окно.
С этого момента, когда щелкают ячейку раскрывающегося списка, раскрывающийся список будет запрашивать автоматически. Вы можете начать вводить букву, чтобы соответствующий элемент автоматически заполнялся в выбранной ячейке. Смотрите скриншот:
Примечание: Этот код не работает для объединенных ячеек.
Автозаполнение при вводе раскрывающегося списка с помощью замечательного инструмента
Поскольку многим пользователям Excel сложно работать с VBA, здесь рекомендуется Раскрывающийся список с возможностью поиска особенность Kutools for Excel чтобы помочь вам легко заполнить раскрывающийся список автоматически при вводе первого символа.
Внимание: Перед применением функции убедитесь, что вы создали раскрывающиеся списки на листе. В этом случае выпадающие списки помещаются в D3: D9.
1. Нажмите Кутулс > Раскрывающийся список > Раскрывающийся список с возможностью поиска > Настройки.
2. Во всплывающем Раскрывающийся список с возможностью поиска В диалоговом окне необходимо выполнить следующие настройки.
2.1). Обращаться к раздел, укажите диапазон или рабочий лист, где вы хотите применить функцию раскрывающегося списка с возможностью поиска;
Советы: После выбора Указанный объем переключатель, вы можете выбрать Текущий рабочий лист, Текущая рабочая тетрадь or Все книги из раскрывающегося списка в соответствии с вашими потребностями.
2.2). Доступные опции раздел, выберите Соответствует только началу слова или Деликатный случай вариант или оба;
Соответствует только началу слова: Отметьте эту опцию, будут отображаться только те элементы, которые начинаются с введенного символа, а первый совпавший элемент будет автоматически добавлен в поле списка. Снимите этот флажок, чтобы отображать элементы, содержащие введенный символ.
Деликатный случай: Установите этот флажок, чтобы выполнять сопоставление вводимых символов с учетом регистра.
3. После завершения настроек вам необходимо включить функцию, нажав Кутулс > Раскрывающийся список > Раскрывающийся список с возможностью поиска > Включить раскрывающийся список с возможностью поиска.
Теперь, когда вы щелкаете ячейку раскрывающегося списка, отображается список со всеми элементами. Вам нужно только ввести символ в текстовое поле, чтобы отобразить все соответствующие элементы, и использовать Up or вниз стрелку, чтобы выбрать нужный элемент, или просто позвольте первому совпадающему элементу автоматически заполнить поле списка, а затем нажмите Enter Клавиша для автозаполнения ячейки раскрывающегося списка соответствующим элементом. См. Демонстрацию ниже.
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Статьи по теме:
Как создать раскрывающийся список с несколькими флажками в Excel?
Многие пользователи Excel, как правило, создают раскрывающийся список с несколькими флажками, чтобы выбирать несколько элементов из списка за раз. На самом деле вы не можете создать список с несколькими флажками с проверкой данных. В этом руководстве мы покажем вам два метода создания раскрывающегося списка с несколькими флажками в Excel. В этом руководстве представлен метод решения проблемы.
Создать раскрывающийся список из другой книги в Excel
Создать раскрывающийся список проверки данных среди листов в книге довольно просто. Но если данные списка, необходимые для проверки данных, находятся в другой книге, что вы будете делать? В этом руководстве вы узнаете, как подробно создать раскрывающийся список из другой книги в Excel.
Создайте раскрывающийся список с возможностью поиска в Excel
Для раскрывающегося списка с многочисленными значениями найти подходящий - непростая задача. Ранее мы ввели метод автоматического заполнения раскрывающегося списка при вводе первой буквы в раскрывающемся списке. Помимо функции автозаполнения, вы также можете сделать раскрывающийся список доступным для поиска для повышения эффективности работы при поиске правильных значений в раскрывающемся списке. Чтобы сделать раскрывающийся список доступным для поиска, попробуйте метод, описанный в этом руководстве.
Автоматическое заполнение других ячеек при выборе значений в раскрывающемся списке Excel
Допустим, вы создали раскрывающийся список на основе значений в диапазоне ячеек B8: B14. При выборе любого значения в раскрывающемся списке необходимо, чтобы соответствующие значения в диапазоне ячеек C8: C14 автоматически заполнялись в выбранной ячейке. Для решения проблемы методы, описанные в этом руководстве, окажут вам услугу.
Как автоматически заполнять другие ячейки при выборе значений в раскрывающемся списке Excel?
Допустим, вы создали раскрывающийся список на основе значений в диапазоне A2: A8. При выборе значения в раскрывающемся списке необходимо, чтобы соответствующие значения в диапазоне B2: B8 автоматически подставлялись в определенную ячейку. Например, когда вы выбираете Наталию из раскрывающегося списка, соответствующий балл 40 будет заполнен в E2, как показано на скриншоте ниже. В этом руководстве представлены два метода, которые помогут вам решить проблему.
Выпадающий список автоматически заполняется функцией ВПР.
Пожалуйста, сделайте следующее, чтобы автоматически заполнить другие ячейки при выборе в раскрывающемся списке.
1. Выберите пустую ячейку, в которую вы хотите автоматически подставить соответствующее значение.
2. Скопируйте и вставьте в нее приведенную ниже формулу, а затем нажмите Enter ключ.
=VLOOKUP(D2,A2:B8,2,FALSE)
Внимание: В формуле D2 это выпадающий список ЯЧЕЙКА, A2: B8 диапазон таблицы включает значение поиска и результаты, а также число 2 указывает номер столбца, в котором находятся результаты. Например, если результаты находятся в третьем столбце диапазона таблицы, измените 2 на 3. Вы можете изменить значения переменных в формуле в зависимости от ваших потребностей.
3. С этого момента, когда вы выбираете имя в раскрывающемся списке, E2 будет автоматически заполняться определенной оценкой.
Легко выбирайте несколько элементов из раскрывающегося списка в Excel:
Вы когда-нибудь пробовали выбрать несколько элементов из раскрывающегося списка в Excel? Здесь Выпадающий список с множественным выбором полезности Kutools for Excel может помочь вам легко выбрать несколько элементов из раскрывающегося списка в диапазоне, на текущем листе, в текущей книге или во всех книгах. См. Демонстрацию ниже:
Загрузите Kutools для Excel прямо сейчас! (30-дневная бесплатная трасса)
Выпадающий список автоматически заполняется с помощью Kutools for Excel
Y вы можете легко заполнить другие значения на основе выбора из раскрывающегося списка, не запоминая формулы с Найдите значение в списке формула Kutools for Excel.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите ячейку для поиска значения автозаполнения (говорит ячейка C10), а затем щелкните Кутулс > Формула Помощник > Формула Помощник, см. снимок экрана:
3. В Помощник по формулам диалоговом окне укажите следующие аргументы:
- В Выберите формулу коробка, найдите и выберите Найдите значение в списке;
Советы: Вы можете проверить Фильтр введите определенное слово в текстовое поле, чтобы быстро отфильтровать формулу. - В Таблица_массив поле, щелкните кнопка для выбора диапазона таблицы, который содержит значение поиска и значение результата;
- В Look_value поле, щелкните кнопку, чтобы выбрать ячейку, содержащую искомое значение. Или вы можете напрямую ввести значение в это поле;
- В Колонка поле, щелкните кнопку, чтобы указать столбец, из которого вы вернете совпадающее значение. Или вы можете ввести номер столбца в текстовое поле, если вам нужно.
- Нажмите OK.
Теперь соответствующее значение ячейки будет автоматически заполнено в ячейке C10 на основе выбора раскрывающегося списка.
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Демо: раскрывающийся список автоматически заполняется без запоминания формул
Статьи по теме:
Автозаполнение при вводе текста в раскрывающемся списке Excel
Если у вас есть раскрывающийся список проверки данных с большими значениями, вам нужно прокрутить список вниз только для того, чтобы найти нужное, или введите все слово напрямую в поле списка. Если есть способ разрешить автозаполнение при вводе первой буквы в выпадающем списке, все станет проще. В этом руководстве представлен метод решения проблемы.
Создать раскрывающийся список из другой книги в Excel
Создать раскрывающийся список проверки данных среди листов в книге довольно просто. Но если данные списка, необходимые для проверки данных, находятся в другой книге, что вы будете делать? В этом руководстве вы узнаете, как подробно создать раскрывающийся список из другой книги в Excel.
Создайте раскрывающийся список с возможностью поиска в Excel
Для раскрывающегося списка с многочисленными значениями найти подходящий - непростая задача. Ранее мы ввели метод автоматического заполнения раскрывающегося списка при вводе первой буквы в раскрывающемся списке. Помимо функции автозаполнения, вы также можете сделать раскрывающийся список доступным для поиска для повышения эффективности работы при поиске правильных значений в раскрывающемся списке. Чтобы сделать раскрывающийся список доступным для поиска, попробуйте метод, описанный в этом руководстве.
Как создать раскрывающийся список с несколькими флажками в Excel?
Многие пользователи Excel, как правило, создают раскрывающийся список с несколькими флажками, чтобы выбирать несколько элементов из списка за раз. На самом деле вы не можете создать список с несколькими флажками с проверкой данных. В этом руководстве мы покажем вам два метода создания раскрывающегося списка с несколькими флажками в Excel. В этом руководстве представлен метод решения проблемы.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Еще. Меньше
Чтобы упростить работу пользователей с листом, добавьте в ячейки раскрывающиеся списки. Раскрывающиеся списки позволяют пользователям выбирать элементы из созданного вами списка.
На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel. Если это не так, список можно быстро преобразовать в таблицу, выделив любую ячейку диапазона и нажав клавиши CTRL+T.
Почему данные следует поместить в таблицу? Потому что в этом случае при добавлении и удалении элементов все раскрывающиеся списки, созданные на основе этой таблицы, будут обновляться автоматически. Дополнительные действия не требуются.
Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.
На ленте откройте вкладку Данные и нажмите кнопку Проверка данных.
Примечание: Если кнопка Проверка данных недоступна, возможно, лист защищен или является общим. Разблокируйте определенные области защищенной книги или отмените общий доступ к листу, а затем повторите шаг 3.
На вкладке Параметры в поле Тип данных выберите пункт Список.
Щелкните поле Источник и выделите диапазон списка. В примере данные находятся на листе "Города" в диапазоне A2:A9. Обратите внимание на то, что строка заголовков отсутствует в диапазоне, так как она не является одним из вариантов, доступных для выбора.
Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки.
Установите флажок Список допустимых значений
Не знаете, какой параметр выбрать в поле Вид?
Чтобы заблокировать пользователям ввод данных, которые не содержатся в раскрывающемся списке, выберите вариант Остановка.
После создания раскрывающегося списка убедитесь, что он работает правильно. Например, рекомендуется проверить, изменяется ли ширина столбцов и высота строк при отображении всех ваших записей.
Если список элементов для раскрывающегося списка находится на другом листе и вы хотите запретить пользователям его просмотр и изменение, скройте и защитите этот лист. Подробнее о защите листов см. в статье Блокировка ячеек.
Если вы решили изменить элементы раскрывающегося списка, см. статью Добавление и удаление элементов раскрывающегося списка.
Чтобы удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.
Ввод данных станет быстрее и точнее, если ограничить значения в ячейке вариантами из раскрывающегося списка.
Сначала создайте на листе список допустимых элементов, а затем выполните сортировку или расположите их в нужном порядке. В дальнейшем эти элементы могут служить источником для раскрывающегося списка данных. Если список небольшой, на него можно легко ссылаться и вводить элементы прямо в средстве проверки данных.
Создайте список допустимых элементов для раскрывающегося списка. Для этого введите элементы на листе в одном столбце или строке без пустых ячеек.
Выделите ячейки, для которых нужно ограничить ввод данных.
На вкладке Данные в группе Инструменты нажмите кнопку Проверка данных или Проверить.
Примечание: Если команда проверки недоступна, возможно, лист защищен или книга является общей. Если книга является общей или лист защищен, изменить параметры проверки данных невозможно. Дополнительные сведения о защите книги см. в статье Защита книги.
Откройте вкладку Параметры и во всплывающем меню Разрешить выберите пункт Список.
Щелкните поле Источник и выделите на листе список допустимых элементов.
Диалоговое окно свернется, чтобы было видно весь лист.
Нажмите клавишу ВВОД или кнопку Развернуть , чтобы развернуть диалоговое окно, а затем нажмите кнопку ОК.
Значения также можно ввести непосредственно в поле Источник через запятую.
Чтобы изменить список допустимых элементов, просто измените значения в списке-источнике или диапазон в поле Источник.
См. также
На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel.
Почему данные следует поместить в таблицу? Потому что в этом случае при добавлении и удалении элементов все раскрывающиеся списки, созданные на основе этой таблицы, будут обновляться автоматически. Дополнительные действия не требуются.
Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.
На ленте откройте вкладку Данные и нажмите кнопку Проверка данных.
На вкладке Параметры в поле Разрешить выберите пункт Список.
Если вы уже создали таблицу с элементами раскрывающегося списка, щелкните поле Источник и выделите ячейки, содержащие эти элементы. Однако не включайте в него ячейку заголовка. Добавьте только ячейки, которые должны отображаться в раскрывающемся списке. Список элементов также можно ввести непосредственно в поле Источник через запятую. Например:
Фрукты;Овощи;Зерновые культуры;Молочные продукты;Перекусы
Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки.
Установите флажок Список допустимых значений
После создания раскрывающегося списка убедитесь, что он работает правильно. Например, рекомендуется проверить, изменяется ли ширина столбцов и высота строк при отображении всех ваших записей. Если вы решили изменить элементы раскрывающегося списка, см. статью Добавление и удаление элементов раскрывающегося списка. Чтобы удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Читайте также: