Три простых способа автозаполнения функции ВПР в Excel?

Функция ВПР (VLOOKUP) незаменима в Excel, но при перетаскивании маркера автозаполнения для распространения формулы на диапазон часто возникают ошибки. В этом руководстве показан правильный способ автозаполнения функции ВПР в Excel.

Автозаполнение VLOOKUP в Excel с абсолютной ссылкой

Автозаполнение VLOOKUP в Excel с помощью Имя ячейки

Автозаполнение VLOOKUP в Excel с использованием расширенного инструмента из Kutools для Excel

Пример файла

Пример

У вас есть таблица с оценками и соответствующими им баллами. Теперь вы хотите найти оценки в диапазоне B2:B5 и получить соответствующие баллы из диапазона C2:C5, как показано на скриншоте ниже:

Обычно вы можете использовать формулу VLOOKUP следующего вида:=VLOOKUP(B2,F2:G8,2)затем перетащите маркер автозаполнения в нужный диапазон, и вы получите неверные результаты, как показано на скриншоте ниже:

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

Введите

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

Совет:

Синтаксис функции ВПР, приведённой выше: ВПР(искомое_значение; таблица; номер_столбца). Здесь B2 — искомое значение, диапазон $F$2:$G$8 — таблица, а 2 указывает, что возвращаемое значение находится во втором столбце этой таблицы.

Раскройте магию Excel с помощью KUTOOLS AI

• Интеллектуальное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это доступно через простые команды.
• Пользовательские формулы: создавайте индивидуальные формулы для оптимизации рабочих процессов.
• Программирование на VBA: Пишите и внедряйте код VBA легко и без усилий.
• Анализ формул: Легко разбирайтесь даже в самых сложных формулах.
• Перевод текста: Ломайте языковые барьеры прямо в ваших таблицах.

Расширьте возможности Excel с помощью инструментов на базе ИИ.Скачать сейчаси ощутите эффективность, как никогда раньше!

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

1. Выделите диапазон массива таблицы, перейдите в поле имени (рядом со строкой формул), введите Баллы (или любое другое имя по вашему выбору) и нажмите клавишу Enter. См. скриншот:

Диапазон массива таблицы — это диапазон, содержащий критерии, используемые в функции ВПР.

2. Введите в ячейку следующую формулу:

Затем перетащите маркер автозаполнения в диапазон, к которому следует применить формулу, — и вы получите корректные результаты.

Если работа с формулой вызывает затруднения, воспользуйтесь группой инструментов Супер ПОИСК из Kutools для Excel — она включает несколько расширенных утилит для поиска, все из которых поддерживают автозаполнение VLOOKUP. Выбирайте подходящий инструмент по своему усмотрению. В данном случае в качестве примера используется утилита Многолистовой поиск.

После бесплатной установкиKutools для Excel выполните следующие действия:

1. Нажмите Kutools > Супер ПОИСК > Многолистовой поиск.

2. В диалоговом окне Многолистовой поиск выполните следующие действия:

1) Выберите диапазон поиска и область размещения списка.

2) В разделе «Диапазон данных» нажмите кнопку Добавить, чтобы добавить используемый диапазон данных в список. При добавлении можно указать ключевой столбец и столбец для возврата.

3. После добавления диапазона данных нажмите ОК. Появится диалоговое окно с предложением сохранить сценарий: нажмите «Да», чтобы присвоить ему имя, или «Нет», чтобы закрыть окно. Теперь функция VLOOKUP автоматически заполнена в области размещения списка.

Нажмите, чтобы скачать пример файла

---

Лучшие инструменты для повышения продуктивности в офисе

Kutools для Excel решает большинство ваших задач и повышает продуктивность на 80 %

• Супер строка формул (удобное редактирование многострочного текста и формул); Режим чтения (удобный просмотр и редактирование большого количества ячеек); Вставка в диапазон фильтрации…
• Объединение ячеек, строк или столбцов с сохранением данных; разделение содержимого ячеек;Объединение дублирующихся строк с суммированием или усреднением… предотвращение дублирования записей в ячейках;Сравнение диапазонов…
• Выбор дубликатов или уникальных строк; выбор пустых строк (все ячейки пустые); улучшенный и нечёткий поиск по множеству книг; случайный выбор…
• Точная копия нескольких ячеек без изменения ссылок в формулах; автоматическое создание ссылок на несколько листов; вставка маркеров, флажков и многое другое…
• Избранные и быстрая вставка формул, диапазонов, диаграмм и изображений; Защита ячеек паролем; Создание списка рассылкии отправка электронных писем…
• Извлечение текста, добавление текста, удаление символов в определённой позиции, удаление пробелов; создание и печать статистики по странице данных; преобразование между содержимым ячеек и примечаниями…
• Суперфильтр (сохранение и применение схем фильтрации к другим листам); расширенная сортировка по месяцам, неделям, дням, частоте и другим параметрам; специальный фильтр по полужирному, курсиву…
• Объединение книг и листов; объединение таблиц на основе ключевого столбца; разделение данных на несколько листов; пакетное преобразование XLS, XLSX и PDF…
• Сводная таблица: группировка по номеру недели, дню недели и многому другому… Показ разблокированных и заблокированных ячеек разными цветами; Выделение ячеек, содержащих формулы или имена…

Подробнее…Полный функционал, бесплатная пробная версия на 30 дней…Купить… 

---

Office Tab — добавляет вкладки в Office и значительно упрощает вашу работу

• Включите вкладки для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
• Открывайте и создавайте несколько документов во вкладках одного окна — это удобнее, чем использовать отдельные окна.
• Повышает продуктивность на 50 % и экономит сотни кликов мышью каждый день!

Подробнее…Полный функционал, бесплатная пробная версия на 30 дней…Купить…