Часто приходится использовать Microsoft Excel, чтобы перенести данные из одной таблицы в другую. Например, у вас есть таблица с артикулами и ценами на каждый из этих товаров. Вручную поиск и копирование данных для десятков или сотен позиций — трудоемкий процесс, в котором легко ошибиться.
К сожалению, для этого в Microsoft Excel есть удобные инструменты. Они позволяют автоматически «подтянуть» необходимые данные, такие как цена или фамилия сотрудника, на основе общего критерия, такого как артикул или идентификация. Вам не придется искать все самостоятельно — формула сделает это за вас.
В этой статье мы рассмотрим самый популярный метод решения таких задач — функцию VLOOKUP. С ее помощью вы узнаете, как быстро соединить две таблицы друг с другом, сэкономив время и избежав неприятных ошибок. Это не так сложно, как кажется, и значительно облегчает работу с любыми списками.
Способ 1: Использование функции ВПР
Поскольку она проста в использовании и эффективно работает с большими и малыми объемами данных, функция ВПР остается одним из наиболее популярных инструментов для поиска и подтягивания данных между таблицами. Эта функция находит значение в первом столбце определенного диапазона и возвращает его из любого столбца, расположенного справа от найденного значения. Microsoft Excel значительно ускоряет процесс заполнения связанных таблиц, автоматически сопоставляя данные по ключевому полю.
- Откройте рабочую книгу с двумя таблицами, между которыми необходимо установить связь. Предположим, у вас есть основная таблица с товарами в столбцах A:C и справочная таблица с дополнительной информацией в столбцах E:G.
- Выделите ячейку в основной таблице, куда требуется подтянуть данные из справочной таблицы. Введите формулу
=ВПР(A2;$E$2:$G$10;3;ЛОЖЬ), где A2 — ячейка с ключевым значением для поиска. 
- В аргументах функции укажите диапазон поиска с использованием абсолютных ссылок ($E$2:$G$10), чтобы при копировании формулы область поиска оставалась неизменной. Цифра 3 означает номер столбца в диапазоне поиска, из которого нужно вернуть значение.
- Четвертый аргумент ЛОЖЬ обеспечивает точное совпадение значений, что критически важно при работе с текстовыми данными или уникальными идентификаторами. При использовании значения ИСТИНА функция будет искать приблизительное совпадение.
- Нажмите клавишу Enter для применения формулы. Если поиск успешен, в ячейке отобразится соответствующее значение из справочной таблицы. При отсутствии совпадений функция вернет ошибку #Н/Д.
- Скопируйте формулу на остальные строки основной таблицы, используя маркер заполнения или комбинацию клавиш Ctrl + C и Ctrl + V. Абсолютные ссылки обеспечат корректную работу формулы во всех ячейках.
Обратите внимание, что ВПР может искать данные только справа от ключевого столбца. Если нужная информация находится слева от столбца поиска, структуру таблицы можно изменить или использовать другие функции.
Способ 2: Комбинация функций ИНДЕКС и ПОИСКПОЗ
Соединение функций ИНДЕКС и ПОИСКПОЗ работает быстрее с большими объемами данных и обеспечивает большую устойчивость к изменениям структуры таблиц. Соединение этих функций позволяет искать значения в любом направлении, не ограничиваясь расположением столбцов, а ИНДЕКС возвращает значение из указанной позиции в другом массиве. Это обеспечивает более гибкие возможности поиска данных по сравнению с ВПР.
- Выберите ячейку для размещения результата поиска в основной таблице. Введите формулу
=ИНДЕКС($F$2:$F$10;ПОИСКПОЗ(A2;$E$2:$E$10;0)), где $F$2:$F$10 — столбец с возвращаемыми значениями. - Функция ПОИСКПОЗ ищет точное совпадение значения из ячейки A2 в диапазоне $E$2:$E$10. Третий аргумент со значением 0 указывает на необходимость точного соответствия.
- ИНДЕКС использует номер позиции, возвращенный функцией ПОИСКПОЗ, для извлечения соответствующего значения из столбца результатов. Данный подход позволяет искать значения слева от ключевого столбца.
- Примените формулу нажатием клавиши Enter. В случае успешного поиска отобразится нужное значение, при отсутствии совпадений — ошибка #Н/Д.
- Для обработки ошибок оберните формулу в функцию ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(ИНДЕКС($F$2:$F$10;ПОИСКПОЗ(A2;$E$2:$E$10;0));"Не найдено"). Это позволит отображать понятное сообщение вместо стандартной ошибки. 
- Скопируйте готовую формулу на другие строки таблицы. Относительные ссылки на ключевые ячейки будут автоматически изменяться, а абсолютные ссылки на диапазоны поиска останутся фиксированными.
Способ 3: Применение функции ПРОСМОТРX
ПРОСМОТРX — это современная альтернатива ВПР, которая предлагает расширенные возможности поиска и обработки данных, которые доступны в новых версиях Excel. Функция может поддерживать точные и приблизительные совпадения, обрабатывать ошибки поиска и возвращать массивы значений. ПРОСМОТРX отличается от ВПР тем, что может искать данные в любом направлении и работает с динамическими массивами.
- Убедитесь, что используете версию Excel, поддерживающую функцию ПРОСМОТРX (Microsoft 365 или Excel 2021). В противном случае функция будет недоступна и вернет ошибку имени.
- Выделите целевую ячейку и введите базовую формулу
=ПРОСМОТРX(A2;$E$2:$E$10;$F$2:$F$10). Первый аргумент — искомое значение, второй — массив поиска, третий — массив возвращаемых значений. 
- Добавьте четвертый аргумент для обработки случаев отсутствия совпадений:
=ПРОСМОТРX(A2;$E$2:$E$10;$F$2:$F$10;"Товар не найден"). Это исключит появление стандартных ошибок в таблице. 
- При необходимости точного поиска укажите пятый аргумент со значением 0:
=ПРОСМОТРX(A2;$E$2:$E$10;$F$2:$F$10;"Товар не найден";0). По умолчанию функция уже выполняет точный поиск. - Примените формулу клавишей Enter. ПРОСМОТРX автоматически обработает все возможные сценарии поиска и отобразит либо найденное значение, либо заданное сообщение об ошибке.
- Скопируйте формулу на остальные ячейки столбца. Функция будет работать стабильно даже при изменении структуры исходных таблиц, поскольку не зависит от номеров столбцов.
Если ПРОСМОТРX не доступен в вашей версии Microsoft Excel, лучше использовать комбинацию ИНДЕКС и ПОИСКПОЗ, которая обеспечивает аналогичные функции.
Способ 4: Создание формул массива для множественного поиска
Формулы массива значительно ускоряют заполнение больших объемов связанных данных, позволяя одновременно искать и возвращать несколько значений из справочной таблицы. Динамические массивы, поддерживаемые в современных версиях Microsoft Excel, автоматически расширяются при изменении исходных данных. При необходимости подтягивать сразу несколько столбцов данных или при обработке данных с повторяющимися ключевыми значениями этот метод эффективен.
- Выделите диапазон ячеек, соответствующий количеству строк и столбцов результирующих данных. Если планируется подтянуть два столбца для десяти строк, выберите область размером 10×2 ячейки.
- Введите формулу массива для поиска нескольких значений:
=ПРОСМОТРX(A2:A11;$E$2:$E$10;$F$2:$G$10). Данная формула вернет массив значений из столбцов F и G для каждого ключа в диапазоне A2:A11. 
- Если функция ПРОСМОТРX недоступна, используйте альтернативную формулу с ИНДЕКС:
=ИНДЕКС($F$2:$G$10;ПОИСКПОЗ(A2:A11;$E$2:$E$10;0);{1;2}). Массив {1;2} указывает номера столбцов для возврата. - Примените формулу нажатием Ctrl + Shift + Enter в старых версиях Excel или просто Enter в Excel 365. Функция автоматически заполнит выбранный диапазон соответствующими значениями.
- При использовании динамических массивов формула автоматически расширится при добавлении новых строк в исходную таблицу. Область результатов будет выделена синей рамкой, указывающей на принадлежность к одному массиву.
- Для обработки ошибок в формулах массива оберните основную функцию в ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(ПРОСМОТРX(A2:A11;$E$2:$E$10;$F$2:$G$10);"Данные отсутствуют"). 
Как вы можете видеть, Microsoft Excel не слишком сложен для переноса данных из одной таблицы в другую. Главное — понять основные принципы и выбрать инструмент, подходящий для вашей задачи. ВПР, XПР или СМЕЩ могут быть хорошими помощниками.
Со временем эти действия станут автоматическими, если вы начнете с простых примеров и не боитесь экспериментировать. Вы всегда можете проверить аргументы в формуле или посмотреть справку, если что-то пошло не так.
Вы сможете забыть о ручном копировании и бесконечных поисках взглядом, используя эти приeмы. Ваши таблицы станут связанными и «умными», что экономит много времени и усилий на повседневной работе.
