Как автоматически заполнить другие ячейки после выбора значения из Раскрывающийся список в Excel: подробное руководство
При работе в Excel автоматизация ввода данных значительно повышает продуктивность. Одна из распространённых задач — автоматическое заполнение связанных данных после выбора значения из раскрывающегося списка. В этом руководстве рассматриваются четыре подробных метода: от встроенных функций Excel до VBA и сторонних инструментов, таких как Kutools.
Прежде всего: создайте Раскрывающийся список
Метод 1: автоматическое заполнение с помощью функции ВПР
Метод 2: автоматическое заполнение с помощью функций ИНДЕКС и ПОИСКПОЗ
Метод 3: автоматическое заполнение с помощью Kutools для Excel
Метод 4: автоматическое заполнение с помощью пользовательской функции
Метод 4: автоматическое заполнение с помощью пользовательской функции
Прежде всего: создайте Раскрывающийся список
Перед использованием любого метода автоматического заполнения сначала создайте раскрывающийся список — именно он станет триггером для заполнения связанных ячеек.
Шаги:
Шаг 1. Подготовьте исходный диапазон.
Шаг 2. Создайте раскрывающийся список.
Перейдите в ячейку, где вы хотите разместить раскрывающийся список (например, Лист1!D2)
Перейдите к Данные > Проверка данных > Проверка данных.
В диалоговом окне «Проверка данных» выберите Список в разделе «Тип данных» и укажите исходный диапазон. Нажмите «ОК».
После создания раскрывающегося списка вы можете сразу приступить к реализации любого из следующих методов автоматического заполнения.
Метод 1: автоматическое заполнение с помощью функции ВПР
ВПР — одна из самых популярных функций для извлечения данных в Excel. В сочетании с раскрывающимся списком она позволяет мгновенно получать связанные данные из справочной таблицы.
Шаги:
В ячейке, соседней с Раскрывающийся список (например, E2), введите:
🔓 Пояснение формулы:
- Ищет значение из ячейки D2 в первом столбце диапазона A2:B5 и, если находит, возвращает соответствующее значение из второго столбца (столбца B); если не находит — выдаёт ошибку #Н/Д.
- Значение FALSE указывает на необходимость точного совпадения.
Шаг 2. Нажмите клавишу Enter.
✨ Примечания
- Use IFERROR() для скрытия ошибок, если значение не выбрано:
=VLOOKUP(D2,$A$2:$B$5,2,FALSE) - Не может выполнять поиск слева от ключевого столбца.
Метод 2: автоматическое заполнение с помощью функций ИНДЕКС и ПОИСКПОЗ
ИНДЕКС и ПОИСКПОЗ — мощная комбинация, превосходящая ВПР по гибкости: она позволяет искать данные влево и сохраняет стабильность даже при изменении порядка столбцов.
Шаги:
В ячейке, соседней с Раскрывающийся список (например, E2), введите:
🔓 Пояснение формулы:
- MATCH(D2, $A$2:$A$5, 0)
Ищет значение из ячейки D2 в диапазоне A2:A5. Параметр 0 означает точное совпадение (аналогично FALSE в функции VLOOKUP).
Возвращает номер строки, на которой найдено значение из D2. - INDEX($B$2:$B$5, …)
Принимает номер строки, полученный с помощью функции ПОИСКПОЗ,
и возвращает соответствующее значение из диапазона B2:B5.
Шаг 2. Нажмите клавишу Enter
✨ Примечания
- Диапазон возврата (ИНДЕКС) и диапазон поиска (ПОИСКПОЗ) должны совпадать по количеству строк.
- Может выполнять поиск как влево, так и вправо.
- Надежнее, чем ВПР.
Метод 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: Можно ли с помощью ВПР выполнять поиск влево?
Нет, в этом случае используйте функцию ИНДЕКС+ПОИСКПОЗ или надстройку 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек