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

Функция ВПР в Excel — TutorExcel.Ru

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

---

Приветствую всех, дорогие читатели блога Tutorexcel.ru!

Мне кажется, что если подбирать ассоциацию к слову Excel, то с большим преимуществом победит вариант ВПР. Впрочем, уверен и в обратном, услышав слово ВПР сразу становится понятно, что речь идет про Excel.

Вот и мы сегодня поговорим про работу этой замечательной функции в Excel, которая позволяет быстро сопоставлять данные между несколькими таблицами, и на самом деле используется примерно в 100% всех файлов, наверное за исключением пустых книг 🙂

В общем давайте начинать и на примерах разберем пошаговую инструкцию по функции ВПР в Excel.

Функция ВПР в Excel. Синтаксис

Изучение любой функции начинаем с синтаксиса, функция ВПР (VLOOKUP в английской версии):

ВПР (искомое_значение; таблица; номер_столбца; [интервальный_просмотр])

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

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

Познакомимся с каждым из аргументов по отдельности:

• Искомое значение (обязательный аргумент) — значение, которое должно быть найдено в первом столбце массива;
  Обычно это числовое или текстовое значение.
• Таблица (обязательный аргумент) — таблица с текстом, числами или логическими значениями, в которой производится поиск данных;
• Номер столбца (обязательный аргумент) — номер столбца в таблице, из которого нужно вернуть значение;
• Интервальный просмотр (необязательный аргумент) — логическое значение, определяющее, точно (ЛОЖЬ) или приближенно (ИСТИНА) должен производиться поиск в первом столбце.

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

Со схемой познакомились, самое время перейти к практике.

Формула ВПР в Excel. Пример 1

Давайте рассмотрим пример, пусть у нас имеется таблица с прайс-листом товаров в магазине (артикул, наименование, цена в рублях, объем в литрах и вес в килограммах):

К нам в магазин приходит заказ и наша задача посчитать общую сумму заказа в деньгах:

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

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

Определимся с аргументами, в качестве искомого значения — артикул (G2), таблицы — исходная таблица с данными (A1:E11), номера столбца — третий (3), интервального просмотра — ЛОЖЬ (0, так как ищем точное соответствие), и записываем полученную формулу:

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

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

Ошибки при работе с функцией ВПР в Excel

Не закрепление диапазона в таблице

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

В нашем примере если мы забудем зафиксировать диапазон таблицы A1:E11, то при протягивании формулы он сначала превратится в A2:E12, затем в A3:E13 и т.д.

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

Поиск не по первому столбцу

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

В нашем случае, к примеру, если мы хотим искать по наименованию (которые находятся в столбце B), то в качестве таблицы нужно выбирать диапазон B1:E11 (не A1:E11), так как именно по столбцу B будет делаться поиск и именно он является первым столбцом в таблице B1:E11.

Пропуск интервального просмотра

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

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

В нашем случае пропуск параметра приводит к тому, что ВПР находит совершенно не то, что нужно, как раз из-за приближенного поиска:

Смена номера столбца

Аргумент номер столбца для функции очень часто прописывается как число в явном виде (например, 3, 4 или 7). Проблема может возникнуть тогда, когда в исходной таблице поменяется порядок столбцов. Например, мы решим добавить в таблицу новый столбец или наоборот, удалить лишний столбец, и то, что раньше было, к примеру, третьим столбцом может стать четвертым или вторым.

Если в наш пример добавить в середину таблицы новый столбец, то исходный третий столбец с ценой станет четвертым, а уже новый третий столбец станет пустым, поэтому формула ВПР вместо цены вернет пустые значения:

Идем дальше.

Функция ВПР в Excel. Пример 2

Для закрепления материала давайте рассмотрим еще один пример и сделаем сравнение двух таблиц посредством функции ВПР.

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

Так же как видим в правой таблице для усложнения перемешаны строчки с товарами (теперь они уже идут не по порядку), чтобы задача решалась не простым вычитанием, а все-таки с помощью более интересных инструментов 🙂

Давайте подтянем справа от новой цены старую, для этого прописываем функцию ВПР, в качестве аргумента искомого значения указываем название товара (D3), таблицы — левую часть исходной таблицы (A2:B12), номера столбца — второй (2), интервального просмотра — ЛОЖЬ (0) и не забываем закреплять диапазоны в случае необходимости, чтобы ссылки не сбились:

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

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

Скачать файл с примером.

Спасибо за внимание!
Если у вас остались вопросы, то обязательно делитесь ими в комментариях.

Удачи вам и до скорых встреч на страницах блога TutorExcel.Ru!

Поделиться с друзьями:

Поиск по сайту:

Формулы,Функции

Гид по функции ВПР (VLOOKUP) в Google Таблицах и Excel

Инструкции

Что это за функция и как с ней работать

Как работает ВПР Как пользоваться функцией ВПР Как сравнить таблицы с помощью ВПР Как работать с ВПР, если искомое значение — слева, а не справа Как использовать символьные шаблоны ВПР

Мы в Telegram

В канале «Маркетинговые щи» только самое полезное: подборки, инструкции, кейсы.
Не всегда на серьёзных щах — шуточки тоже шутим =)

Подписаться

Станьте email-рокером 🤘

Пройдите бесплатный курс и запустите свою первую рассылку

Подробнее

Если нужно объединить данные в таблицах, можно вручную перепроверять и переносить значения с одного места на другое. Но это сложно и долго, плюс легко ошибиться.

Чтобы было быстрее и проще работать, в Google Таблицах и Excel есть множество функций. Одна из таких — ВПР (VLOOKUP). Она мгновенно и точно находит нужные данные в указанном диапазоне, позволяет автоматически переносить их с одного листа на другой (или с одной таблицы на другую, если использовать вместе с функцией IMPORTRANGE).

Чтобы самостоятельно поработать с шаблоном и примерами из статьи, можно открыть эту таблицу, выбрать «Файл → Создать копию».

Как работает ВПР

ВПР (VLOOKUP) — функция поиска и извлечения данных, которая:

• принимает определенный набор символов в качестве запроса;
• ищет совпадение с этим запросом в крайнем левом столбце заданного диапазона;
• копирует значения из ячейки, которая находится в соседнем столбце, но на этой же строке.

Так, ВПР используют магазины, когда нужно объединить или сравнить две таблицы. К примеру, таблицу заказов (какой товар заказали) и прайс-лист (по какой цене заказали, сколько денег ушло на закупку партии и так далее). Или, допустим, ВПР можно использовать, чтобы вычислить скидку для клиента или размер прибыли работника в зависимости от количества продаж.

Функция принимает четыре параметра: запрос, диапазон, номер столбца и сортировки. Подробнее о каждом:

Запрос показывает, что мы ищем в таблице. Например, наименование товара.

Диапазон отражает, где мы ищем запрос. Например, в диапазоне B2:C20. И если будет совпадение с ячейкой B1, функция ничего не вернет, так как эта ячейка не входит в указанный диапазон.

Индекс — номер столбца, который определяет, из какого столбца возвращать значение. Например, если в качестве диапазона указать B2:D11, то столбец C будет вторым, а D — третьим.

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

Сортировка говорит, отсортированы значения в таблице или нет. 1 или ИСТИНА (TRUE) — да, 0 или ЛОЖЬ (FALSE) — нет.

Как правило, указывают 0 — в таком случае ВПР будет искать только точное совпадение с запросом. В противном случае функция выберет значения, которые примерно похожи на запрос — то есть меньшие или равные ему.

К тому же неточный поиск работает только в отношении чисел. Если ищем по словам, нужно обязательно передавать последним параметром 0 (ЛОЖЬ, FALSE).

А что если будет несколько ячеек, которые соответствуют искомому запросу? Тогда функция все равно выдаст только один результат — завершит работу, как только наткнется на первое совпадение.

Как пользоваться функцией ВПР

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

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

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

Шаг 1: Выбираем функцию и запрос

Для этого в ячейке «стоимость покупки» набираем равно «=» и пишем ВПР. После этого нажимаем на ячейку с запросом в столбце «название товара». Либо прописываем в скобках координаты ячейки. В нашем случае это «B4».

Шаг 2: Настраиваем диапазон запроса

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

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

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

Шаг 3: Выбираем номер столбца

Индекс (номер столбца) передаем следующим после диапазона.

Важно: столбцы считаются внутри выбранного диапазона.

Так, в нашем примере нужно взять и перенести информацию о стоимости электроники, которая находится в столбце D. Если смотреть на весь лист, то D — это четвертый по счету столбец. А вот в диапазоне C:D столбец D — это именно второй по счету.

Шаг 4: Выбираем параметр «отсортировано» или «не отсортировано»

На этом этапе функция определяет, что искать: точное или неточное (меньшее или равное) совпадение с запросом. Напомню, что здесь есть два варианта:

0 (ЛОЖЬ, FALSE). ВПР выбирает точь-в-точь подходящий вариант. Как правило, используют именно такой режим поиска.

1 (ИСТИНА, TRUE). Функция выбирает примерно подходящий вариант, меньший или равный, но не больший. Это нужно гораздо реже, и имеет смысл, только если значения в диапазоне отсортированы.

Допустим, нам нужно сопоставить размер скидки для клиента с количеством покупок. Для этого создаем отдельную таблицу с диапазоном скидок. Важно, чтобы диапазон был возрастающий. Например, 5, 7, 9, 12, 15. Иначе функция работать не будет.

Теперь прописываем функцию. Ячейка запроса — количество покупок, диапазон — вторая таблица с количеством покупок и размером скидки, номер столбца — второй, сортировка — «ИСТИНА» или «1».

В итоге получаем таблицу с расчетами скидок для клиентов.

Шаг 5: Настраиваем функцию под всю таблицу

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

Но перед этим важно зафиксировать значения диапазона. Для этого нужно поставить знаки доллара как минимум после названий столбцов («G» и «H» в нашем случае), а лучше и перед тоже. Это можно сделать вручную или выделить диапазон и нажать «F4» на клавиатуре. 

В противном случае при протягивании формула будет меняться, например, так: G4:h26 → G5:h27 → G6:h28 и так далее.

А нам важно искать все значения в определенном диапазоне.

Как сравнить таблицы с помощью ВПР

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

Для начала подтягиваем в таблицу старые цены. Формула будет выглядеть так: 

=ВПР(A4;’Таблица со старыми ценами’!$B$4:$C$16;2;0)

После этого добавляем данные из таблицы с новыми ценами. Формула будет такой:

=ВПР(A4;$G$4:$H$16;2;0)

Теперь можно сравнить цены. Чтобы не делать это вручную, прописывайте формулу: новая цена – старая цена / новая цена. А в формате чисел выберите процент.

Как работать с ВПР, если искомое значение — слева, а не справа

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

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

Поэтому самый простой способ — скопировать столбец «Цена» и перенести его в правый, после «Код товара». После этого внести новый столбец в диапазон и работать с ВПР как и прежде.

Если такой вариант не подходит, есть более сложный — через массивы.

Массив в Excel и Google Таблицах — это определенный набор данных, можно сказать, та же таблица, только «виртуальная». 

Массивы могут быть одномерными, то есть состоять только из строк вроде {1\2} или столбцов вида {1;2}. Или же многомерными — включать и столбцы, и строки.

Для поиска цены создадим отдельные поля «Код товара» и «Цена».

И прописываем ВПР для ячейки «Цена». Функция будет вида

=ВПР(H6;{‘Лист6’!D:D \ ‘Лист6’!C:C};2;0), где:

• H6 — номер ячейки с кодом товара.
• Лист6 — название листа, в котором находится наша таблица с ценами, кодами и количеством товаров.
• D:D — диапазон столбца с кодами товаров.
• C:C — диапазон столбца с ценами товаров.
• 2 — номер столбца из диапазона.
• 0 — точное соответствие.

Фигурными скобками как раз создаем массив, а обратной косой чертой показываем, что данные разделяются по столбцам. Тем самым меняем столбцы исходной таблицы местами — теперь сначала идет D:D, а только потом C:C. Такой лайфхак по обходу ограничения функции ВПР.

В итоге, когда введем в ячейку «Код товара» соответствующие данные, получим информацию о стоимости устройств.

Есть и другие способы работать с ВПР, если искомое значение слева. Например, с помощью функции СУММЕСЛИ, ВЫБОР, ИНДЕКС и ПОСКПОЗ — о таком варианте рассказывали на сайте «Планета Excel». Другую полезную инструкцию по работе с массивами выкладывали в телеграм-канале «Google Таблицы».

Как использовать символьные шаблоны ВПР

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

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

Для этого прописываем формулу:

=ВПР(«*Iphone*»;диапазон;1;0) 

Звездочки означают любое количество любых символов (в том числе их отсутствие). То есть условию будут соответствовать и «Apple Iphone», и «Iphone 12», и «Iphone».

А чтобы найти ячейку со словом Iphone с определенным количеством знаков после него, нужно составить формулу вида:

=ВПР(«*Iphone ?? ???»;диапазон;1;0)

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

Также ВПР можно настроить для нескольких условий одновременно, о таком способе рассказывал в своем блоге Евгений Намоконов. Пригодится, например, чтобы быстро найти стоимость битого Iphone 12 из таблицы.

Поделиться

СВЕЖИЕ СТАТЬИ

Другие материалы из этой рубрики

Не пропускайте новые статьи

Подписывайтесь на соцсети

Делимся новостями и свежими статьями, рассказываем о новинках сервиса

Статьи почтой

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

Оставляя свой email, я принимаю Политику конфиденциальности

Наш юрист будет ругаться, если вы не примете 🙁

Как запустить email-маркетинг с нуля?

В бесплатном курсе «Rock-email» мы за 15 писем расскажем, как настроить email-маркетинг в компании. В конце каждого письма даем отбитые татуировки об email ⚡️

*Вместе с курсом вы будете получать рассылку блога Unisender

Оставляя свой email, я принимаю Политику конфиденциальности

Наш юрист будет ругаться, если вы не примете 🙁

excel — скопировать одну таблицу данных листа в другую таблицу на другом листе

Задавать вопрос

спросил 2 года 9 месяцев назад

Изменено 2 года, 9 месяцев назад

Просмотрено 468 раз

отказ от ответственности Я никогда раньше не использовал VBA. ..

У меня есть следующее: Лист1 содержит Таблицу1 Sheet2 содержит Table2

Я хочу скопировать содержимое Table1 и вставить его в Table2. Вы бы использовали метод копирования/вставки или использовали ListRows.add?

Вот как я могу выбрать строки из таблицы 1:

 'Копировать из таблицы src
Установите srcRows = srcSh.ListObjects ("Таблица1")
srcRows.Parent.Activate
srcRows.AutoFilter.ShowAllData
srcRows.DataBodyRange.SpecialCells(xlCellTypeVisible).Копировать
 

Но тогда я не уверен, как мне скопировать его в конец другой таблицы?

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

 from = srcRows.DataBodyRange.Value
 

Я видел функцию добавления https://learn.microsoft.com/en-us/office/vba/api/excel.listrows.add#example, но не уверен, как применить к ней этот выбор.

Наконец, я попробовал еще одну упрощенную версию с демонстрацией, начинающейся со следующего:

Моя цель — скопировать строки из второй таблицы, чтобы добавить их в первую.

Итак, я попробовал следующий макрос:

 Set tbl1 = ActiveSheet.ListObjects("Table3")
    Установите tbl2 = ActiveSheet.ListObjects("Table2")
  Для x = 2 Для tbl2.Range.Rows.Count
        tbl1.ListRows.Add.Range() = tbl2.Range.Rows(x).Value
  Следующий х
 

, что приведет к следующему результату:

Почти готово, но как заставить его «вставлять» значения, начиная со столбца foo?

Будем очень признательны за любую помощь 🙂

Спасибо

• excel
• vba

2

На случай, если у кого-то возникнет такая же проблема, вот как я решил ее с помощью VBA:

 Sub MacroYeah()
Dim ws As рабочий лист
Dim Source, Destination As ListObject
Dim r As Диапазон
Тусклый индекс As Long
    Установите ws = ActiveSheet
    Установить источник = ActiveSheet.ListObjects ("Таблица2")
    Установить пункт назначения = ActiveSheet.ListObjects("Table3")
    Источник.Родитель. Активировать
    Источник.AutoFilter.ShowAllData
    Source.DataBodyRange.SpecialCells(xlCellTypeVisible).Копировать
    Установите r = Destination.Range()
    р.Активировать
    индекс = r.Rows.Count
    ActiveCell.Offset(индекс, 1).Выбрать
    Вставка ActiveCell.PasteSpecial:=xlPasteValues
    Application.CutCopyMode = Ложь
Конец сабвуфера
 

Содержимое второй таблицы будет скопировано в конец первой таблицы со смещением на 1 строку.

Ранее:

После:

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя электронную почту и пароль

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Копировать строки на другой лист

Применимо к

Smartsheet

• Про
• Бизнес
• Предприятие

Возможности

Пользователи с разрешениями Владелец, Администратор, и Редактор   на исходном и целевом листах могут копировать строки на новый лист.
 

Не дублируйте свою работу вручную, копируя строки с одного листа на другой.

Вы можете копировать строки автоматически с помощью автоматизированного рабочего процесса копирования строк.

Копирование строк с одного листа на другой 

1. Нажмите и удерживайте клавишу Shift (выберите диапазон строк) или клавишу Ctrl (выберите разрозненные строки) и выберите номера строк в левой части сетки листа.
   Дочерние строки включаются при выборе родительских строк. После копирования строк удалите дочерние строки из целевого листа, если они вам не нужны. Дополнительные сведения о родительских и дочерних строках см. в статье Иерархия: отступы и отступы строк.
2. Щелкните правой кнопкой мыши выделенный фрагмент.
3. В меню строки выберите Копировать на другой лист .
4. В диалоговом окне Копировать на другой лист выберите целевой лист.
   • Необязательно: выберите Включить вложения или Включить комментарии .
5. Выберите  ОК .

Данные ячейки из строки (или нескольких строк) из исходного листа будут скопированы в новую строку (или строки) в нижней части листа назначения. Этот процесс не повлияет на исходный лист.

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

Устранение неполадок

Вот некоторые моменты, о которых следует помнить при использовании этого метода для копирования строк на новый лист:

• Предшественники и формулы не сохраняются, но их можно копировать и вставлять между двумя листами.
• Исходный и конечный столбцы должны иметь одинаковые имена столбцов. Если исходная и целевая таблицы имеют разные имена или типы столбцов, данные из скопированной строки отображаются в новых столбцах справа от существующих столбцов на целевом листе. В этом случае прокрутите вправо, чтобы найти данные, скопируйте их и вставьте в нужные столбцы.