Как автоматически заполнять другие ячейки после выбора значения из выпадающего списка в Excel: Полное руководство
При работе с Excel автоматизация ввода данных может значительно повысить производительность. Одной из распространенных задач является автозаполнение связанных данных после выбора значения из выпадающего списка. В этом руководстве рассматриваются четыре полных метода, начиная от встроенных функций Excel до VBA и сторонних инструментов, таких как Kutools.
Прежде всего: Создание выпадающего списка
Метод 1: Автозаполнение с использованием функции VLOOKUP
Метод 2: Автозаполнение с использованием функций INDEX и MATCH
Метод 3: Автозаполнение с использованием Kutools для Excel
Метод 4: Автозаполнение с использованием пользовательской функции
Метод 4: Автозаполнение с использованием пользовательской функции
Прежде всего: Создание выпадающего списка
Перед тем как применять любой метод автозаполнения, вам нужно создать выпадающий список. Этот выпадающий список будет выступать триггером для заполнения связанных ячеек.
Шаги:
Шаг 1. Подготовьте источник данных.
Шаг 2. Создайте выпадающий список.
Перейдите к ячейке, где вы хотите разместить выпадающий список (например, Sheet1!D2).
Перейдите к Данные > Проверка данных > Проверка данных.
В диалоговом окне проверки данных выберите Список из раздела Разрешить и укажите источник данных. Нажмите ОК.
Как только ваш выпадающий список будет готов, вы можете приступить к реализации любого из следующих методов автозаполнения.
Метод 1: Автозаполнение с использованием функции VLOOKUP
VLOOKUP — одна из самых часто используемых функций для получения данных в Excel. При совместном использовании с выпадающим списком она может быстро находить связанные данные из справочной таблицы.
Шаги:
В соседней ячейке выпадающего списка (например, E2) введите:
🔓 Объяснение формулы:
- Ищет значение из D2 в первом столбце диапазона A2:B5. Если значение найдено, возвращает соответствующее значение из второго столбца (столбец B). Если значение не найдено, возвращает ошибку (#N/A).
- FALSE означает, что должно быть точное совпадение.
Шаг 2. Нажмите клавишу Enter.
✨ Примечания
- Используйте IFERROR() для скрытия ошибок при отсутствии выбранного значения:
=VLOOKUP(D2,$A$2:$B$5,2,FALSE) - Не может выполнять поиск слева от ключевого столбца.
Метод 2: Автозаполнение с использованием функций INDEX и MATCH
INDEX и MATCH — это мощный дуэт, который превосходит VLOOKUP по гибкости. Он поддерживает поиски слева и остается стабильным даже если столбцы изменены местами.
Шаги:
В соседней ячейке выпадающего списка (например, E2) введите:
🔓 Объяснение формулы:
- MATCH(D2, $A$2:$A$5, 0)
Ищет значение D2 в диапазоне A2:A5. 0 означает точное совпадение (как FALSE в VLOOKUP).
Возвращает позицию (номер строки), где найдено значение D2. - INDEX($B$2:$B$5, ...)
Берет номер строки из MATCH.
Возвращает соответствующее значение из диапазона B2:B5.
Шаг 2. Нажмите клавишу Enter.
✨ Примечания
- Диапазон возвращаемых значений (INDEX) и диапазон поиска (MATCH) должны совпадать по строкам.
- Поддерживает поиск слева или справа.
- Более надежен, чем VLOOKUP.
Метод 3: Автозаполнение с использованием Kutools для Excel
Kutools предлагает графический подход, который исключает необходимость в формулах. Это особенно полезно для пользователей, которые хотят быстрых результатов без глубокого погружения в функции Excel.
Шаги:
Шаг 1. В соседней ячейке выпадающего списка (например, E2), перейдите к Kutools > Помощник формул > Поиск и ссылка > Искать значение в списке.
Шаг 2. Выберите Диапазон таблицы, Значение поиска и Номер столбца. Нажмите ОК.
✨ Примечания
- Kutools позволяет применять это ко всему диапазону одновременно.
- Инструмент очень удобен для новичков и снижает количество ручных ошибок.
- Легко использовать.
- Формулы не требуются.
Устали от повторяющихся задач и сложных формул в Excel? Kutools для Excel — ваш универсальный помощник для повышения продуктивности! Более 300 мощных функций — пакетное редактирование, умное заполнение, автофильтрация — вы будете работать в 10 раз быстрее. Скачайте прямо сейчас и поднимите свои навыки работы с Excel на новый уровень!
Метод 4: Автозаполнение с использованием пользовательской функции
Для пользователей, которым нужна динамическая и автоматизированная система за пределами возможностей формул, VBA предоставляет максимальный контроль и возможность настройки логики.
Шаги:
Шаг 1. Нажмите комбинацию клавиш Alt+F11, чтобы открыть редактор VBA.
Шаг 2. Нажмите Вставка > Модуль.
Шаг 3. Вставьте приведенный ниже код в Модуль.
'Update by Extendoffice
Function GetProductInfo(productName As String, colIndex As Integer) As Variant
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") 'the sheet that the data source in
Dim rng As Range
Set rng = ws.Range("A2:B5") 'the range of data source
Dim r As Range
For Each r In rng.Rows
If r.Cells(1, 1).Value = productName Then
GetProductInfo = r.Cells(1, colIndex).Value
Exit Function
End If
Next
GetProductInfo = "Not found"
End Function
Шаг 4. Вернитесь на лист и в соседней ячейке выпадающего списка (например, E2) введите:
Шаг 5. Нажмите клавишу Enter.
✨ Примечания
- Требуется книга с поддержкой макросов (.xlsm).
Часто задаваемые вопросы
Вопрос 1: Что делать, если мой диапазон данных часто меняется?
Используйте именованные диапазоны или динамические таблицы для поддержания ссылок.
Вопрос 2: Можно ли использовать VLOOKUP для поиска влево?
Нет, рассмотрите использование INDEX+MATCH или Kutools для этого случая.
Вопрос 3: Безопасен ли Kutools для использования?
Да, он широко используется и доверен многими, но всегда скачивайте его с официального сайта.
Вопрос 4: Будет ли VBA работать во всех версиях Excel?
Большинство настольных версий поддерживают его, но он отключен по умолчанию и не поддерживается в Excel Online.
Вопрос 5: Бесплатен ли Kutools для использования?
Kutools для Excel не является полностью бесплатным инструментом, но он предлагает бесплатную пробную версию, после которой можно приобрести постоянную лицензию:
- 30-дневная бесплатная пробная версия с полным функционалом — кредитная карта не требуется.
- Постоянная лицензия для одного пользователя: около 49 долларов США, включая 2 года бесплатных обновлений и поддержки.
- После окончания двухлетнего периода поддержки вы сможете продолжить использовать имеющуюся версию бессрочно — просто без дальнейших обновлений.
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
- Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!
Все надстройки Kutools. Один установщик
Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.
- Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек