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

ВПР в Excel для облегчения работы с таблицами

Что это? Функция ВПР в Еxcel (или иначе VLOOKUP, то есть вертикальный просмотр) позволяет работать с двумя таблицами одновременно, оперируя их данными путем переноса значений из одной в другую.

Зачем нужна? К примеру, вам нужно оценить бюджет предстоящего маркетингового мероприятия, когда в одной таблице прайс-лист, а в другой – предполагаемое количество единиц разного мерча. Как же в этом случае использовать ВПР в Еxcel?

В статье рассказывается: 

1. Зачем нужна функция ВПР в Excel
2. Пошаговая инструкция по работе с ВПР в Excel
3. Поиск по нескольким критериям
4. Быстрое сравнение двух таблиц с помощью ВПР в Excel

5. Пройди тест и узнай, какая сфера тебе подходит:
   айти, дизайн или маркетинг.

   Бесплатно от Geekbrains

Зачем нужна функция ВПР в Excel

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

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

Зачем нужна функция ВПР в Excel

Однако сделать это не так просто. Нужного результата вы не достигнете, если просто скопируете и вставите столбец. А поиск и перенос цен вручную – долгая и кропотливая работа.

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

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

Пошаговая инструкция по работе с ВПР в Excel

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

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

Шаг 1. Построение функции

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

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

Приступаем к построению функции. Сделать это можно двумя способами: во вкладке «Формулы» выбрать пункт «Вставить функцию» либо в строке ссылок нажать на значок «fx».

После этого откроется окно «Построитель формул», в котором, используя поиск, нужно найти ВПР, а затем нажать кнопку «Вставить функцию».

Далее появится поле для ввода аргументов, которое необходимо заполнить. А как это сделать смотрим ниже.

Шаг 2. Заполнение значений функции

Перед тем, как объяснить выбор аргументов, рассмотрим понятие каждого.

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

Заполнение значений функции

Как установить данный параметр?

• Курсор устанавливаем в поле «Искомое значение» в окне «Построитель формул».
• Выбираем ячейку А2, в которой содержится первое значение из столбца моделей автомобилей.
• Значение, которое мы указали, помимо построителя формул, дублируется в формуле строки ссылок, которая выглядит так: fx = ВПР (А2).

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

У нас это стоимость автомобилей. Соответственно, в область работы функции мы включаем столбцы «Модель», которые является искомым значением и «Цена», то есть то, что будет перенесено.

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

Последовательность действий при выборе диапазона:

• Устанавливаем курсор в поле «Таблица» в окне построителя формул.
• Возвращаемся к таблице каталога автомобилей.
• Выделяем диапазон с колонками «Модель» и «Цена». У нас получается: А2:Е19
• Теперь выбранную область нужно закрепить. На Windows для этого требуется выбрать значение диапазона в строке ссылок, а затем нажать клавишу F4. На macOS подтверждаем выбранную в строке ссылок область сочетанием клавиш Cmd + T. Это действие необходимо, чтобы функцию можно было протянуть вниз для ее правильного срабатывания во всех строках.

Топ-30 самых востребованных и высокооплачиваемых профессий 2023

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

Подборка 50+ ресурсов об IT-сфере

Только лучшие телеграм-каналы, каналы Youtube, подкасты, форумы и многое другое для того, чтобы узнавать новое про IT

ТОП 50+ сервисов и приложений от Geekbrains

Безопасные и надежные программы для работы в наши дни

pdf 3,7mb

doc 1,7mb

Уже скачали 20689

Диапазон, который мы установили, появляется в построителе формул и в строке ссылок. Визуально это выглядит так: fx=ВПР(A2;’каталог авто’!$A$2:$E$19).

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

Если нумерация колонок не проставлена, необходимо сделать это вручную. У нас столбец «Цена» под номером пять.

Заполнение значений функции

Устанавливаем курсор в поле «Номер столбца» в окне «Построитель формул», вводим нужное значение. Функция ВПР теперь выглядит следующим образом: fx=ВПР(A2;’каталог авто’!$A$2:$E$19;5).

Интервальный просмотр – это параметр настройки работы формулы.

• Если требуется точное совпадение данных, нужно ввести 0.
• Если допускается приближенное значение, ставим 1.

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

Значение вводим в поле «Интервальный просмотр» и функция ВПР в Excel приобретает завершенный вид: fx=ВПР(A2;’каталог авто’!$A$2:$E$19;5;0)

Шаг 3.

Получение результатов

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

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

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

Поиск по нескольким критериям

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

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

Поиск по нескольким критериям

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

Порядок действий таков:

• В первую очередь нужно добавить в таблицу столбец слева, при этом произведя объединение «Материалов» и «Поставщиков».
• Аналогично совмещаем искомые значения.
• Задаем аргументы для функции ВПР: =ВПР(I6;$A$2:$D$15;4;ЛОЖЬ)
• Нажимаем «Готово». Поиск завершен, цена найдена.

Только до 8.05

Скачай подборку тестов, чтобы определить свои самые конкурентные скиллы

Список документов:

Тест на определение компетенций

Чек-лист «Как избежать обмана при трудоустройстве»

Инструкция по выходу из выгорания

Чтобы получить файл, укажите e-mail:

Подтвердите, что вы не робот,
указав номер телефона:

Уже скачали 7503

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

• Создаем раскрывающийся список: устанавливаем курсор в ячейку Е8.
• Переходим во вкладку «Данные» в меню «Проверка данных».
• Тип данных, нужный нам – «Список», для отбора указываем область с названием материалов.
• Подтверждаем свои действия нажатием клавиши «ОК». Раскрывающийся список создан.

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

Используем «Мастер функций» и устанавливаем аргументы. Искомое значение – ячейка с раскрывающимся списком; таблица – диапазон, включающий в себя «Материалы» и «Цены» (столбец 2). Наша формула должна выглядеть так: =ВПР(E8;A2:B16;2;ЛОЖЬ)

Поиск по нескольким критериям

Подтверждаем настройку нажатием соответствующей клавиши.

Быстрое сравнение двух таблиц с помощью ВПР в Excel

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

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

Для упрощения воспользуемся формулой ВПР в Excel для сравнения:

• Открываем старый прайс и добавляем колонку «Новая цена».
• Встаем на первую ячейку и через «Мастер функций» выбираем ВПР. Задаем параметры и получаем: =ВПР($A$2:$A$15;’новый прайс’!$A$2:$B$15;2;ЛОЖЬ).
• Таким образом мы сделали следующее: указали диапазон наименований А2:А15 в старом прайсе и сравнили его с новым. После чего значения новых цен подставили в ячейку С2 созданного столба прежнего прайса.

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

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

Продвижение блога — Генератор продаж

Рейтинг: 5

( голосов 2 )

Поделиться статьей

Перенос таблицы из Word в Excel

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

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

Ну что же, давайте разберемся в проблеме, которые препятствуют гладкому переносу.Все дело в том, что в таблице присутствует много “мусора”. Он мог быть туда набран при заполнении таблиц данными или же перенесен при копировании информации из других приложений. Как правило, деформацию таблицы вызывают знаки конца абзаца, которые забиваются по нескольку в одну ячейку. Вроде бы и на печатных машинках уже давно никто не работает, но все равно народ любит переходить с одной строки на другую кнопкой Enter. 🙂

Например, такая ситуация. В таблице присутствует столбец «Фамилия, Имя, Отчество». Чтобы эти данные красиво размещались в столбце, не сильно раздвигая его, стараются при наборе расположить их одно под другим, используя при этом кнопку Enter:

Копируем эту таблицу в Excel

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

Таблица, скопированная в Excel

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

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

Так как нам мешал символ конца абзаца, то и удалить следует именно его. Делается это так:

1. Выделяем таблицу в блок, чтобы обратиться только к ее данным.
2. На вкладке Главная ленты инструментов ищем область Редактирование.
3. В этой области активируем инструмент Заменить. Это можно сделать и нажатием комбинации клавиш Ctrl+H.
4. В открывшемся окне устанавливаем курсор в поле Найти.
5. Затем нажимаем на кнопку Больше>> и в области Заменить на кнопку Специальный. p».
6. В поле Заменит на указываем символ пробела. Если абзацы стоят в пустой ячейке, то это поле оставляем пустым, что приведет к замене указанного символа на пустоту, то есть его удаление.

   Очистка документа Word

7. Нажимаем на кнопку Заменить все.
8. Выполнится процесс удаления специальных знаков конца абзаца по всей таблице, о чем будет выведено сообщение в специальном окне и вопрос, следует ли продолжить эту операцию для остального документа. Отказываемся, нажатием на кнопку Нет.
9. Закрываем окно замены.

Очищенная таблица Word

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

Скопированная в Excel таблица

Как переместить таблицу Excel на другой лист?

спросил 10 лет, 9 месяцев назад

Изменено 6 лет, 10 месяцев назад

Просмотрено 36 тысяч раз

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

Итак, как мне переместить Worksheet1!Table1 в Worksheet2!Table1 и сохранить все мои ссылки?

РЕДАКТИРОВАТЬ: Что я пробовал до сих пор:

Очевидное копирование и вставка, но это требует удаления источников данных, переименования диапазонов и, как правило, возни с множеством незначительных (но важных) настроек, которые, кажется, должны управляться по Экселю. Ах да, и Google (безрезультатно).

• microsoft-excel-2010

Чуть менее очевидный ответ победил — Вырезать и вставить .

Копировать и вставить создал дубликат всего и не решил проблему. Однако Cut & Paste перенес все связанные ссылки на новое место. Получил короткую ошибку на странице со связанными диаграммами и формулами, но через несколько секунд она устранила ошибку и показала правильные данные.

РЕДАКТИРОВАТЬ: Похоже, существуют некоторые ограничения на размер таблицы, которую можно вырезать и вставлять. У меня есть две таблицы, которые нужно было вырезать и вставлять из одних и тех же рабочих листов. Таблица из 300 строк работала нормально, а таблица из 5000 строк — нет, она приводила к сбою Excel (5 раз, когда я пытался это исправить). Это не конкретная таблица, потому что, когда я изменил ее свойства запроса, чтобы возвращать меньше строк (около 300 строк), она переместилась нормально.

Существует новая надстройка Excel под названием Power Query, позволяющая копировать таблицы.

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

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

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

1

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

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

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

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

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

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

Требуется, но никогда не отображается

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

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

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

Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания, политикой конфиденциальности и политикой использования файлов cookie

.

excel — VBA Перемещение строк из одной таблицы в другую на основе условия

У меня есть книга с 4 вкладками: 1 основная, ежедневно обновляемая вкладка и 3 вкладки, в которых будут храниться исторические данные.

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

Теперь одна из таблиц главной ежедневно обновляемой панели управления называется «Проблемы». Шестой столбец в этой таблице — «Статус». Мне нужен макрос, который будет автоматически перемещать строку данных из таблицы «Проблемы» в другую таблицу с именем «Проблемы, решенные» на другом листе с именем «Проблемы», если статус проблемы установлен как «Решено» или «Отклонено»

Таблицы выглядят так:

Я уже написал код:

a = ActiveWorkbook.Worksheets("Текущий шаблон DMB").ListObjects("Проблемы").Range.Rows.Count
Для i = 2 К а
Если ActiveWorkbook.Worksheets("Текущий шаблон DMB").ListObjects("Проблемы"). Cells(i, 6).Value = "Отклонено" или "Решено" Тогда
ActiveWorkbook.Worksheets("Текущий шаблон DMB").ListObjects("Проблемы").Rows(i).Cut
ActiveWorkbook.Worksheets("Проблемы").Активировать
b = ActiveSheet.ListObjects("Решенные проблемы").Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.ListObjects("Решенные проблемы").Cells(b + 1, 1).Select
ActiveSheet.Paste
Рабочие листы («Текущий шаблон DMB»). Активировать
Конец, если
Следующий
Конец сабвуфера
 

Но мне выдает ошибку 438 о том, что объект не поддерживает этот метод.

Помогите пожалуйста, что я делаю не так.

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

Заранее большое спасибо

• excel
• vba

3

Было бы полезно использовать несколько переменных, чтобы сделать ваш код менее повторяющимся и более простым в обслуживании. Вот один подход:

 Sub MoveClosed()
 Dim wb As Workbook, loOpen As ListObject, loClosed As ListObject
 Dim lr As ListRow, i As Long 
 Установите wb = ThisWorkbook 'или ActiveWorkbook
 'получить ссылку на каждую из таблиц.