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

Содержание

примеры для чайников – WindowsTips.Ru. Новости и советы

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

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

Формула ВПР в Excel

Синтаксис ВПР в русифицированном Excel имеет вид:

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

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

Критерий поиска

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

Диапазон данных

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

Номер столбца для итогового значения

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

Условие для поиска

Логическое значение (истина/1 или ложь/0), которое указывает приблизительное совпадение искать (1) или точное (0).

ВПР в Excel: примеры функции

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

Найдем количество фактически выпущенной продукции по названию месяца.
Результат выведем справа от таблицы. В ячейке с адресом h4 будем вводить искомое значение. В примере здесь будет указываться название месяца.
В ячейке h5 введем саму функцию. Это можно делать вручную, а можно воспользоваться мастером. Для вызова поставьте указатель на ячейку h5 и нажмите значок Fx около строки формул.

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

Появится окно ВПР для таблицы Excel.

Чтобы указать первый аргумент (критерий), поставьте курсор в первую строку и щелкните по ячейке h4. Ее адрес появится в строке. Для выделения диапазона поставьте курсор во вторую строку и начните выделять мышью. Окно свернется до строки. Это делается для того, чтобы окно не мешало видеть Вам весь диапазон и не мешало выполнять действия.

Как только Вы закончите выделение и отпустите левую кнопку мыши, окно вернется в свое нормальное состояние, а во второй строке появится адрес диапазона. Он вычисляется от левой верхней ячейки до правой нижней. Их адреса разделены оператором «:» — берутся все адреса между первым и последним.

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

Последнюю строку оставьте пустой. По умолчанию значение будет равно 1, посмотрим, какое значение выведет наша функция. Нажмите «ОК».

Результат обескураживает. «Н/Д» означает некорректные данные для функции. Мы не указали значение в ячейке h4, и функция ищет пустое значение.

Введем название месяца и значение изменится.

Только оно не соответствует действительности, ведь настоящее фактическое количество выпущенной продукции в январе равно 2000.
Это влияние аргумента «Условие поиска». Изменим его на 0. Для этого поставьте указатель на ячейку с формулой и снова нажмите Fx. В открывшемся окне введите «0» в последнюю строку.

Нажимайте «ОК». Как видим, результат изменился.

Чтобы проверить второе условие из начала нашей статьи (среди формул функция не ищет) изменим условия для функции. Увеличим диапазон и попробуем вывести значение из столбца с вычисляемыми значениями. Укажите значения как на скриншоте.

Нажмите «Ок». Как видите, результат поиска оказался 0, хотя в таблице стоит значение 85%.

ВПР в Excel «понимает» только фиксированные значения.

Сравнение данных двух таблиц Excel

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

На двух листах мы имеем одинаковые таблицы с разными данными.

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

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

Растяните формулу на весь столбец.

Аналогично введите формулу в следующий столбец, только диапазон выделяйте на листе «Цех2».

После копирования Вы получите сводный отчет с двух листов.

Подстановка данных из одной таблицы Excel в другую

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

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

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

Вот и вся информация о незаметной, но полезной функции ВПР в Excel для чайников. Надеемся, она поможет Вам при решении задач.

Как использовать функцию ВПР (VLOOKUP) в Excel

Наверняка многим активным пользователям табличного редактора Excel периодически приходилось сталкиваться с ситуациями, в которых возникала необходимость подставить значения из одной таблицы в другую. Вот представьте, на ваш склад зашёл некий товар. В нашем распоряжении имеется два файла: один с перечнем наименований полученного товара, второй — прайс-лист этого самого товара. Открыв прайс-лист, мы обнаруживаем, что позиций в нём больше и расположены они не в той последовательности, что в файле с перечнем наименований. Вряд ли кому-то из нас понравится идея сверить оба файла и перенести цены из одного документа в другой вручную. Разумеется, в случае, когда речь идёт о 5–10 позициях, механическое внесение данных вполне возможно, но что делать, если число наименований переваливает за 1000? В таком случае справиться с монотонной работой нам поможет Excel и его волшебная функция ВПР (или vlookup, если речь идёт об англоязычной версии программы).

Что такое ВПР и как ею пользоваться?

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

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

Для начала приведите таблицу Excel в необходимый вам вид. Добавьте к заготовленной матрице данных два столбца с названиями «Цена» и «Стоимость». Выберите для ячеек, находящихся в диапазоне новообразовавшихся столбцов, денежный формат.
Теперь активируйте первую ячейку в блоке «Цена» и вызовите «Мастер функций». Сделать это можно, нажав на кнопку «fx», расположенную перед строкой формул, или зажав комбинацию клавиш «Shift+F3». В открывшемся диалоговом окне отыщите категорию «Ссылки и массивы». Здесь нас не интересует ничего кроме функции ВПР. Выберите её и нажмите «ОК». Кстати, следует сказать, что функция VLOOKUP может быть вызвана через вкладку «Формулы», в выпадающем списке которой также находится категория «Ссылки и массивы».
После активации ВПР перед вами откроется окно с перечнем аргументов выбранной вами функции. В поле «Искомое значение» вам потребуется внести диапазон данных, содержащийся в первом столбце таблицы с перечнем поступивших товаров и их количеством. То есть вам нужно сказать Excel, что именно ему следует найти во второй таблице и перенести в первую.
После того как первый аргумент обозначен, можно переходить ко второму. В нашем случае в роли второго аргумента выступает таблица с прайсом. Установите курсор мыши в поле аргумента и переместитесь в лист с перечнем цен. Вручную выделите диапазон с ячейками, находящимися в области столбцов с наименованиями товарной продукции и их ценой. Укажите Excel, какие именно значения необходимо сопоставить функции VLOOKUP.
Для того чтобы Excel не путался и ссылался на нужные вам данные, важно зафиксировать заданную ему ссылку. Чтобы сделать это, выделите в поле «Таблица» требуемые значения и нажмите клавишу F4. Если всё выполнено верно, на экране должен появиться знак $.
Теперь мы переходим к полю аргумента «Номер страницы» и задаём ему значения «2». В этом блоке находятся все данные, которые требуется отправить в нашу рабочую таблицу, а потому важно присвоить «Интервальному просмотру» ложное значение (устанавливаем позицию «ЛОЖЬ»). Это необходимо для того, чтобы функция ВПР работала только с точными значениями и не округляла их.

Теперь, когда все необходимые действия выполнены, нам остаётся лишь подтвердить их нажатием кнопки «ОК». Как только в первой ячейке изменятся данные, нам нужно будет применить функцию ВПР ко всему Excel документу. Для этого достаточно размножить VLOOKUP по всему столбцу «Цена». Сделать это можно при помощи перетягивания правого нижнего уголка ячейки с изменённым значением до самого низа столбца. Если все получилось, и данные изменились так, как нам было необходимо, мы можем приступить к расчёту общей стоимости наших товаров. Для выполнения этого действия нам необходимо найти произведение двух столбцов — «Количества» и «Цены». Поскольку в Excel заложены все математические формулы, расчёт можно предоставить «Строке формул», воспользовавшись уже знакомым нам значком «fx».

Важный момент

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

Подобной ситуации лучше избежать посредством разделения двух таблиц. Чтобы это сделать, нам необходимо выделить ячейки, находящиеся в диапазоне столбца «Цена», и щёлкнуть по нему правой кнопкой мыши. В открывшемся окошке выберите и активируйте опцию «Копировать». После этого, не снимая выделения с выбранной области ячеек, вновь нажмите правую кнопку мыши и выберите опцию «Специальная вставка».

Активация этой опции приведёт к открытию на вашем экране диалогового окна, в котором вам нужно будет поставить флажок рядом с категорией «Значение». Подтвердите совершённое вами действия, кликнув на кнопку «ОК».

Возвращаемся к нашей строке формул и проверяем наличие в столбце «Цена» активной функции VLOOKUP. Если на месте формулы вы видите просто числовые значения, значит, всё получилось, и функция ВПР отключена. То есть связь между двумя файлами Excel разорвана, а угроза незапланированного изменения или удаления прикреплённых из таблицы с прайсом данных нет. Теперь вы можете смело пользоваться табличным документом и не волноваться, что будет, если «Прайс-лист» окажется закрыт или перемещён в другое место.

Как сравнить две таблицы в Excel?

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

Возможность работы с несколькими условиями

Ещё одним несомненным достоинством функции VLOOKUP является его способность работать с несколькими параметрами, присущими вашему товару. Чтобы найти товар по двум или более характеристикам, необходимо:

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

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

Код VBA для передачи данных из одной таблицы в другую на основе значения ячейки

Falko26

Обычная доска

16 декабря 2021 г.

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

 Sub MoveBasedOnValue (YsNo As String)
'Общая подпрограмма для модуля кода
Dim xRg как диапазон
Dim xCell как диапазон
Дим А до тех пор
Dim B до тех пор, пока
Dim C до тех пор, пока
Dim ToSheet как рабочий лист
Dim FroSheet как рабочий лист
Выберите случай YsNo
 Случай «да»
    Установить ToSheet = Листы («Возвращено»)
    Установите FroSheet = Листы («Выход»)
Дело №"
    Установить ToSheet = Листы ("Out")
    Установить FroSheet = Листы ("Возвращено")
Конец выбора
A = FroSheet.UsedRange.Rows.Count
B = ToSheet.UsedRange.Rows.Count
Если В = 1 Тогда
Если Application.WorksheetFunction.CountA(ToSheet.UsedRange) = 0, тогда B = 0
Конец, если
Установите xRg = FroSheet. Range("h2:H" & A)
При ошибке Возобновить Далее
Application.ScreenUpdating = Ложь
Для C = 1 To xRg.Count
Если CStr(xRg(C).Value) = YsNo Тогда
xRg(C).EntireRow.Copy Destination:=ToSheet.Range("A" & B + 1)
xRg(C).EntireRow.Delete
Если CStr(xRg(C).Value) = YsNo Тогда
С = С - 1
Конец, если
В = В + 1
Конец, если
Следующий
Application.ScreenUpdating = Истина
Конец суб

Лист 1
Код:
Private Sub Worksheet_Change (ByVal Target As Range) 'Подписка на возвращенный лист Дим ТоШт Как Строка Dim FroSht As String Dim YsNo As String Dim Z как долго Dim xVal как строка При ошибке Возобновить Далее Если Intersect(Target, Range("H:H")) ничего не значит, тогда выйдите из Sub Приложение.EnableEvents = Ложь Для Z = 1 до Target.Count Если Цель(Z).Значение > 0 Тогда '************************************ YsNo = "нет" '*************************************** Вызов MoveBasedOnValue (YsNo) Конец, если Следующий Приложение. EnableEvents = Истина Конец суб

Лист 2
Код:
Private Sub Worksheet_Change (ByVal Target As Range) 'Подписка на возвращенный лист Дим ТоШт Как Строка Dim FroSht As String Dim YsNo As String Dim Z как долго Dim xVal как строка При ошибке Возобновить Далее Если Intersect(Target, Range("H:H")) ничего не значит, тогда выйдите из Sub Приложение.EnableEvents = Ложь Для Z = 1 до Target.Count Если Цель(Z).Значение > 0 Тогда '************************************ YsNo = "да" '*************************************** Вызов MoveBasedOnValue (YsNo) Конец, если Следующий Приложение.EnableEvents = Истина Конец суб

Возврат данных о населении города
Щелкните здесь, чтобы открыть ответ
Если у вас есть список городов в формате A2:A100, используйте Данные, География. Затем =A2.Population и скопируйте вниз.

тоний
Известный член
18 декабря 2021 г.

Что касается VBA, работа с таблицами (также известными как ListObjects) отличается от работы с диапазонами. Крис Ньюман представляет хороший обзор в The VBA Guide to ListObject Excel Tables. В какой-то момент вы, возможно, захотите переписать свой код с использованием ListObjects.
Адаптация кода VBA
Maverick_NL
7 сентября 2022 г.
Вопросы Excel
Ответы
0
просмотров
113
7 сентября 2022 г.
Maverick_NL
Справка по коду VBA. Необходимо вырезать/вставлять строки на разные листы по разным критериям и сортировать по дате
VBAN0oB
31 октября 2022 г.

Вопросы Excel
2
Ответы
10
просмотров
200
8 ноября 2022 г.
псгольдберг
Делиться:

Если вам нужно выполнить очень мало действий по копированию и вставке, вы можете сделать это. Поместите формулу в режим редактирования, выберите ее всю в строке редактирования, скопируйте ее, нажмите клавишу Escape, перейдите в ячейку в новой таблице, поместите ее в режим редактирования, вставьте ее, нажмите клавишу ввода. [Это действие в основном выполняет то, что Excel должен иметь в качестве одного из параметров вставки, в частности, вставить дословно. ]
Если вы работаете не только с несколькими формулами, скопируйте и вставьте их, как вы делали это в своем сообщении. Затем выберите строки и столбцы всей таблицы и используйте функцию «Найти и заменить», чтобы изменить имя таблицы (например, resource10111220) на имя новой таблицы.