Как подставить данные из одной таблицы в другую в excel

Перенос данных таблицы через функцию ВПР

ВПР в Excel – очень полезная функция, позволяющая подтягивать данные из одной таблицы в другую по заданным критериям. Это достаточно «умная» команда, потому что принцип ее работы складывается из нескольких действий: сканирование выбранного массива, выбор нужной ячейки и перенос данных из нее.

Весь процесс просмотра и выборки данных происходит за доли секунды, поэтому результат мы получаем моментально.

Синтаксис ВПР

ВПР расшифровывается как вертикальный просмотр. То есть команда переносит данные из одного столбца в другой. Для работы со строками существует горизонтальный просмотр – ГПР.

Аргументы функции следующие:

искомое значение – то, что предполагается искать в таблице;
таблица – массив данных, из которых подтягиваются данные. Примечательно, таблица с данными должна находиться правее исходной таблицы, иначе функций ВПР не будет работать;
номер столбца таблицы, из которой будут подтягиваться значения;
интервальный просмотр – логический аргумент, определяющий точность или приблизительность значения (0 или 1).

Как перемещать данные с помощью ВПР?

Рассмотрим пример на практике. Имеем таблицу, в которой прописываются партии заказанных товаров (она подсвечена зеленым). Справа прайс, где значатся цены каждого товара (подсвечен голубым). Нам нужно перенести данные по ценам из правой таблицы в левую, чтобы подсчитать стоимость каждой партии. Вручную это делать долго, поэтому воспользуемся функцией Вертикального Просмотра.

В ячейку D3 нужно подтянуть цену гречки из правой таблицы. Пишем =ВПР и заполняем аргументы.

Искомым значением будет гречка из ячейки B3. Важно проставить именно номер ячейки, а не слово «гречка», чтобы потом можно было протянуть формулу вниз и автоматически получить остальные значения.

Таблица – выделяем прайс без шапки. Т.е. только сами наименования товаров и их цены. Этот массив мы зафиксируем клавишей F4, чтобы он не изменялся при протягивании формулы.

Номер столбца – в нашем случае это цифра 2, потому что необходимые нам данные (цена) стоят во втором столбце выделенной таблицы (прайса).

Интервальный просмотр – ставим 0, т.к. нам нужны точные значения, а не приблизительные.

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

ВПР.

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

Пример.

Вот так, с помощью элементарных действий можно подставлять значения из одной таблицы в другую. Важно помнить, что функция Вертикального Просмотра работает только если таблица, из которой подтягиваются данные, находится справа. В противном случае, нужно переместить ее или воспользоваться командой ИНДЕКС и ПОИСКПОЗ. Овладев этими двумя функциями вы сможете намного больше реализовать сложных решений чем те возможности, которые предоставляет функция ВПР или ГПР.

Excel Formula Examples
Создать таблицу
Форматирование
Функции Excel
Формулы и диапазоны
Фильтр и сортировка
Диаграммы и графики
Сводные таблицы
Печать документов
Базы данных и XML
Возможности Excel
Настройки параметры
Уроки Excel
Макросы VBA
Скачать примеры

Автоматический перенос данных из одной таблицы в другую в Excel

Вот три способа автоматического переноса данных с одного листа Excel на другой.

Первый, самый простой и примитивный способ связи двух таблиц на разных листах документа — вставка данных при помощи опции специальной вставки.

Давайте посмотрим шаг за шагом, как соединить две таблицы.

Первый шаг.

вам необходимо выбрать таблицу, данные из которой будут переведены во вторую таблицу.

Второй шаг.

Скопируйте информацию, нажав ctrl + C или вызвав контекстное меню правой кнопкой мыши и нажав на пункт меню «Копировать»

Третий шаг.

Перейдите на лист документа «Excel», в который вы планируете переводить информацию из первой таблицы.

Четвертый шаг.

Установите курсор в первую ячейку (вверху слева) таблицы и выберите пункт «Вставить ссылку» в меню «Вставить». В некоторых версиях Excel этот элемент находится в меню Специальная вставка

После вставки ссылки нужно отформатировать внешний вид ячеек — привести их в правильный вид.

Результат вставки ссылки

Второй способ переноса данных из одной таблицы в другую — это использование сводных таблиц в программе «Excel».

При использовании этого метода сводная таблица сама играет роль второй таблицы («получатель.

Как обновить сводную таблицу

Когда вы щелкаете правой кнопкой мыши сводную таблицу и выбираете элемент «Обновить», сводная таблица автоматически переносит все данные из связанного информационного массива («Донорские таблицы»).

Как создавать сводные таблицы в Excel подробно описано в статье:

Как делать сводные таблицы в программе «Excel» и для чего они нужны.

Третий способ самый эффективный и наиболее автоматизированный — это использование меню надстройки «Power Query».

Однако следует отметить, что этот метод подходит только для пользователей Excel 2016 и пользователей Excel 2013 и более поздних версий с установленной надстройкой Power Query».

Смысл способа в следующем:

вам нужно открыть вкладку Power Query. В разделе «Данные Excel» нажмите кнопку «Из таблицы» (значок».

Из таблицы — Power Query

Далее вам нужно выбрать диапазон ячеек, из которого вы хотите «извлечь» информацию, и нажать «ОК».

Источник данных для Power Query

После выбора области данных появится окно для настройки внешнего вида новой таблицы. В этом окне вы можете настроить порядок столбцов и удалить ненужные столбцы.

После настройки вида таблицы нажмите кнопку «Закрыть и загрузить»

Результирующая таблица обновляется щелчком правой кнопкой мыши по названию нужного запроса в правой части листа (список «Запросы на книги»). После щелчка правой кнопкой мыши в контекстном меню выберите пункт «Обновить»

Программа подстановки данных из одного файла в другой (замена функции ВПР)

Программа предназначена для сравнения и подстановки значений в таблицах Excel.

Если вам надо сравнить 2 таблицы (по одному столбцу, или по нескольким),
и для совпадающих строк скопировать значения выбранных столбцов из одной таблицы в другую,
надстройка «Lookup» поможет сделать это нажатием одной кнопки.

То же самое можно сделать при помощи формулы =ВПР(), но:

формулы могут тормозить работу с файлом при пересчёте, если объём данных большой (много строк или столбцов)
если источник данных или файл, в который подставляются данные, каждый раз новый, — требуется время на прописывание или редактирование формул
если с файлами работают люди, «далёкие» от Excel, — их проще обучить нажимать одну кнопку, чем объяснять им, как прописывать эти формулы
иногда нужны дополнительные возможности (не учитывать заданные слова и символы при сравнении, выделять цветом изменения, копировать недостающие строки, и т.д.)

В настройках программы можно задать:

где искать сравниваемые файлы (использовать уже открытый файл, загружать файл по заданному пути, или же выводить диалоговое окно выбора файла)
с каких листов брать данные (варианты: активный лист, лист с заданным номером или названием)
какие столбцы сравнивать (можно задать несколько столбцов)
значения каких столбцов надо копировать в найденные строки (также можно указать несколько столбцов)
каким цветом подсвечивать совпавшие и ненайденные строки (для каждого из 2 файлов)
исключаемые при сравнении символы и фразы

Как скачать и протестировать программу

Для загрузки надстройки Lookup воспользуйтесь кнопкой Скачать программу

Если не удаётся скачать надстройку, читайте инструкцию про антивирус

Если скачали файл, но он не запускается, читайте почему не появляется панель инструментов

Это полнофункциональная пробная (TRIAL) версия, у вас есть 15 дней ,
в течение которых вы можете протестировать работу программы.

Этого вполне достаточно, чтобы всё настроить и проверить, используя раздел Справка по программе

Если вам понравится, как работает программа, вы можете Купить лицензию

Лицензия (для постоянного использования) стоит 1600 рублей .

В эту стоимость входит активация на 2 компьютера (вы сможете пользоваться программой и на работе, и дома).
Если нужны будут дополнительные активации, их можно будет в любой момент приобрести по 500 рублей за каждый дополнительный компьютер.

315138 просмотров

Здравствуйте, Виктор
Код программы закрыт.
Для вашего случая программа не подойдёт (она сравнивает только по полному совпадению)
Переделать (доработать) программу можно, но доработка будет стоить недешево (около 1500 руб дополнительно к стоимости программы)

Виктор, 25 Окт 2018 — 13:49. #4

Здравствуйте, подскажите после покупки, код программы будет виден, или можно ли как то переделать что бы например при нахождении двух данных в 1 книге ячейке A1 «1000,2000» B1 «Ок» и сопоставлении их во 2 книге A1, A2 проставлялись так же B1, B2 значением из 1 книги

что то вроде
1 книга
A1 1000,2000 B1 OK

2 книга
A1 1000 B1 OK
A2 2000 B2 OK

Игорь (администратор сайта), 8 Окт 2018 — 20:00. #5

Поиск в надстройке Lookup идет по полному совпадению ячеек (искомое значение равно найденному)
А поиск, выполняемый вами вручную в Excel, идет по частичному совпадению (вхождению искомого текста в ячейку)

В вашем случае, поиск по частичному совпадению выполнять нельзя, — будете искать APV3, а будет также найдена строка с APV31 (и потом кучу времени потратите на поиск ошибок, угадывая, что с чем могло еще так совпасть)

Гость, 8 Окт 2018 — 18:48. #6

После настройки и запуска надстройки оказалось, что он не может найти артикул в тексте и срабатывает только если удалить лишний текст в ячейке. На фото правая таблица содержит 35 000 строк и редактировать каждую ячейку займет колоссальное кол-во времени. При этом видно, что обычный поиск по документу всё находит. Возможно всё дело в неправильной настройке? Или лучшим решением будет заказать у вас макрос который справится с поставленной задачей? Спасибо! Очень жду ответа.

Михаил, 5 Окт 2018 — 21:34. #7

Спасибо за подсказку. Покупаю надстройку. ))

Игорь (администратор сайта), 5 Окт 2018 — 21:26. #8

Михаил, надстройку можно запускать и из VBS
Код будет из 2 строк:

XLapp.workbooks.Open "полный путь к файлу надстройки Lookup.xla" XLapp.Run "LookupData"

Если сами не разберетесь, могу помочь (платно)

Читать:

Как в html написать знак меньше

Сергей, перенос активаций с компа на комп не предусмотрен.

Михаил, 5 Окт 2018 — 19:38. #9

У меня в 9 утра появляются на сети два файла, которые нужно сопоставить и слить воедино, а потом отправить электронной почтой. Вот именно для отправки электронной почтой по расписанию нужно и сверку запускать по расписанию.
Как программу я её в планировщик засунуть не могу, может надстройку можно вызвать через vbs, чтобы планировщиком скрипт запускать?

Сергей, 5 Окт 2018 — 19:36. #10

Добрый день, такой вопрос. Если я купил программу активировал её, потом допустим не хочу использовать её на этом ПК.
Могу я деактивироватьи перенести на другой ПК? Или один раз установленная программа навечно залипает на этой машине без
возможности переноса?

Игорь (администратор сайта), 5 Окт 2018 — 19:07. #11

А зачем расписание?
Надо сравнить данные — нажали кнопку, подставилось тут же всё.
Какой смысл это делать по расписанию за час или несколько часов до просмотра данных?

Михаил, 5 Окт 2018 — 18:39. #12

Добрый день. Программа очень полезная. Но уточните, как можно настроить её запуск по расписанию?

Игорь (администратор сайта), 5 Окт 2018 — 17:19. #13

Александр, это читали?
Инструкция, почему может не запускаться программа:
(почему не появляется панель инструментов)
https://excelvba.ru/faq/addin_toolbar

Александр, 5 Окт 2018 — 17:13. #14

Добрый день.
Скачал вашу программу, отлично работала и облегчила мне жизнь )
Но после обновления ноутбука, перестала работать (
На панели исчезла Надстройка, если открыть программу Lookup, надстройка появляется, но не активная, не появляется окно (где производится настройка откуда брать данные из какого файла и куда подставить).
Помогите пожалуйста!
Заранее благодарен.

Игорь (администратор сайта), 20 Сен 2018 — 05:42. #15

Здравствуйте, Александр.
Ответил вам на почту.
Можно активировать и без интернета: https://excelvba.ru/faq/activation

Александр, 20 Сен 2018 — 05:40. #16

Здравствуйте, хочу приобрести Вашу программу, но к сожалению у меня на рабочем компьютере нету подключения к внешнему интернету, и при попытке обновить или при нажатии кнопки «Активация» выдает ошибку: «Нет подключения к серверу обновления/активации». Есть ли возможность как то активировать Вашу программу без доступа к интернету?
p.s. Программу еще не покупал, т.к. волнует вышеуказанная проблема.

Игорь (администратор сайта), 19 Сен 2018 — 12:44. #17

Инструкция, почему может не запускаться программа:
(почему не появляется панель инструментов)
https://excelvba.ru/faq/addin_toolbar

Скорее всего, проблема вызвана антивирусом (файл надстройки повреждён или удалён)
Инструкция, как решить эту проблему: https://excelvba.ru/faq/antivirus

Евгений, 19 Сен 2018 — 12:13. #18

Добрый день,
Вкладка надстройки с функциями подстановщика исчезла из ексель. Как восстановить?! Платная версия, все оплачено, обновления устанавливал.

Игорь (администратор сайта), 10 Сен 2018 — 05:19. #19

Предлагаю в настройках добавить еще поле, в котором можно поменять название кнопки запуска надстройки «Подставить данные» на свою индивидуальную фразу

Насчёт изменения названия кнопки, — такая опция раньше была в настройках (пару лет назад), но убрал её, когда сделал программу многоязычной.
Сейчас написал макрос, меняющий название кнопки в надстройке, — как купите надстройку, скину вам файл с макросом, за пару секунд сможете поменять надпись на кнопке:

Т.е. многопрофильность поддерживается?

Надстройка никак не мешает работать другим надстройкам, так что никаких конфликтов быть не должно.

Если ту же надстройку Lookup надо использовать с другими настройками, — то можно добавить переключатель режимов

зачем этой надстройке установка в какую-то папку? Как правильно эту папку выбрать?

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

Лиля, 10 Сен 2018 — 02:42. #20

На базе данной надстройки удачно реализовали робота «Джо», который контролирует оборот почтовых отправлений и поступление денег на счет от покупателей. Теперь посылки не теряются, а денежки не пропадают. Автор — красопед (крассава)! Но вот вопрос: а еще 3-4 таких надстройки для выполнения других (третьих) задач в том-же Excele не будут конфликтовать между собой? Ну, например, когда мы еще что-либо автоматизируем? Т.е. многопрофильность поддерживается? И еще вопрос: зачем этой надстройке установка в какую-то папку? Как правильно эту папку выбрать? Спасибо.

Лиля, 9 Сен 2018 — 17:26. #21

Человек спрашивал: «Здравствуйте! При запуске обработки выдает следующее http://prntscr.com/knw1jt. Как быть? Спасибо»

Такое бывает, если Вы скачали файл из сети Интернет, но не нажали клавишу «Разрешить редактирование». Тогда надстройка не получает доступа к файлу. ИМХО

Лиля, 9 Сен 2018 — 17:23. #22

Здравствуйте. Предлагаю в настройках добавить еще поле, в котором можно поменять название кнопки запуска надстройки «Подставить данные» на свою индивидуальную фразу. Спасибо.

Игорь (администратор сайта), 29 Авг 2018 — 03:53. #23

Не сталкивался с такой ошибкой ранее. Проверьте настройки программы, и попробуйте на других файлах

Евгений, 28 Авг 2018 — 20:53. #24

Здравствуйте! При запуске обработки выдает следующее http://prntscr.com/knw1jt. Как быть? Спасибо

Игорь (администратор сайта), 22 Авг 2018 — 20:58. #25

Иван, я же вам отвечал на почту
Сброс активаций (и перенос активаций на другой комп) не предусмотрен

Ivan, 22 Авг 2018 — 20:51. #26

Здравствуйте.
Подскажите можно как сбросить старые активации.

Игорь (администратор сайта), 22 Авг 2018 — 04:50. #27

Иван, попробуйте еще раз активировать, должно получиться

Ivan, 21 Авг 2018 — 23:21. #28

Здравствуете.
Переустановил windows, не могу активировать надстройку пишет что лимит активации исчерпан, нужно новую докупать.

Игорь (администратор сайта), 10 Июл 2018 — 20:05. #29

Добавьте мой сайт ExcelVBA.ru и расширение .xla в список исключений антивируса.
И скачайте заново

Гость, 10 Июл 2018 — 18:01. #30

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

ВПР на максималках

Думаю многие, если не большинство, в курсе, что такое ВПР и его неоспоримая сила при поиске и объединении данных из разных таблиц. Те же, кто достиг просветления, используют не менее полезную функцию ИНДЕКС, чтобы не париться, где там идентификатор: слева или справа.

Ниже будет пост о том, каким еще извращенным способом можно надругаться над excel и вытащить данные из другой таблицы по нестандартным условиям без ~~регистрации и смс~~ дополнительных фишек типа VBA и т.п., только штатный функционал excel.

Итак, что мы имеем:

Таблица раз — со списком значений, который надо обогатить данными

Таблица, которую будем обогащать

В другой таблице имеем данные по статусам, которые могут повторяться, и которые связаны с первой таблицей по идентификатору обращения

Фрагмент таблицы, откуда будем вытаскивать данные

Обычная задача для ВПР или ИНДЕКС звучит, как «мне надо добавить данные из одной таблицы в другую по какому-то критерию или критериям», после чего берем критерий первой таблицы и начинаем искать по нему первое совпадающее значение из другой таблицы и возвращаем значение из искомого столбца:

=ВПР(A2;Лист2!$C$1:$C$170;2;0), где A2 - критерий, Лист2!$C$1:$I$13 - диапазон в котором ищем, 2 - номер столбца, из которого возвращаем значение, 0 - тип сопоставлениятип

=ИНДЕКС(Лист2!$B$1:$I$170;ПОИСКПОЗ(Лист1!A3;Лист2!$C$1:$C$170;0);3), где - Лист2!$B$1:$I$170 - диапазон, в кокотором ищем, ПОИСКПОЗ(Лист1!A3;Лист2!$C$1:$C$170;0) - строка, которую ищем, 3 - столбец, откуда возвращаем данные,

В целом двух этих формул хватит для 90% ситуаций при работе с excel, чтобы подтянуть данные из другой таблицы. Хватит ровно до того момента, пока к нам не присоединится условие с датой, при этом дата будет неизвестной переменной.

Собственно сама задача:

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

То есть мы оперируем идентификатором и условием, что дата должна быть самая поздняя из возможных записей с совпадающими идентификаторами. Когда меня попросили изобразить это в excel, я сначала начал думать в сторону VBA, так как Googlение проблемы приводило в основном к примерам, как искать записи по нескольким критериям, то есть из исходной таблицы мы берем эти несколько критериев и пытаемся по ним найти соответствующие записи в другой таблицы. Эти варианты не подходили, так как определение последней даты записи должно было происходить также в формуле, и заранее ее значение было неизвестно, ну и плюс она должна быть определена для записей с конкретным идентификатором. VBA тоже не походил, так как человеку надо было отправить просто формулу, чтобы можно было вставить в ячейку.

В итоге с помощью того Googlа и небольшой доработки напильником была рождена формула следующего типа:

Вот он — ВПР на максималках) тут использовано все, чтобы вернуть ту искомую запись в таблице: и массивы, и индекс, и несколько критериев, и условие по дате.
Как это работает:

Используем в формуле ИНДЕКС массивы. При вводе формулы используем сочетание клавиш ctrl+shift+enter
Выделяем просматриваемый диапазон, в моем случае от B2 до I170
Искомую строку определяем по формуле ПОИСКПОЗ, при этом поиск осуществляем по двум условиям A4&МАКС (идентификатор A4 исходной таблицы и максимальное значение даты при равном идентификаторе, значение даты берется из функции ЕСЛИ ). Тут важно не забыть, что поиск по нескольким критериям можно задать через & перечислив критерии, а также надо через & перечислить диапазоны, в которых excel будет искать эти критерии, в той же последовательности, что и критерии
Искомый столбец оформил тоже через формулу ПОИСКПОЗ, где формула ищет позицию искомого значения «Статус» в просматриваемом диапазоне заголовков второй таблицы и возвращает таким образом номер столбца, из которого нам надо вытащить значение

Таким способом я смог вытянуть необходимо значения строк для обогащения исходной таблицы

Итоговый результат

Зачем этот пост?

Хотел поделиться в сети информацией для будущих искателей решения схожей проблемы, так как мой ТОП операций и функций excel ctrl+c и ctrl+v 🙂 Но мне не подвернулось готового решения, когда я искал. Может кому-то повезет больше с моей помощью.