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

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

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

Приведу три способа Автоматического переноса данных с одного листа программы «Эксель» в другой.

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

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

Первый шаг.

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

Второй шаг.

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

Третий шаг.

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

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

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

После вставки связи следует отформатировать вид ячеек – привести их к надлежащему виду.

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

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

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

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

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

О том, как в «Эксель» создавать сводные таблицы подробно написано в статье:

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

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

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

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

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

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

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

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

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

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

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

Обновление запроса в PowerQuery

Источник: ruexcel.ru

Несколько простых полезных функций Excel

Продолжаю делиться приемами работы в Excel и Google Таблицах.

Как автоматически подобрать ширину столбцов/строк?

Выделяем столбцы (строки) (для этого нужно навести курсор именно на названия столбцов(строк)):

Двойной щелчок по стыку любых столбцов из этого диапазона автоматически подбирает их ширину в соответствии с содержанием:

Как повернуть таблицу на 90°?

Выделяем таблицу и копируем ее (Ctrl+C):

Щелкаем правой кнопкой мыши на ту ячейку, в которую хотим вставить перевернутую таблицу, в появившемся контекстном меню нажимаем на «Транспонировать»:

Как перенести данные из одной таблицы в другую? (Функция ВПР-VLOOKUP)

Функция ВПР (VLOOKUP) позволяет подтянуть данные из таблицы по названию показателя, например:

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

У функции ВПР есть следующие аргументы:

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

Существует аналогичная функция ГПР (HLOOKUP) – в ней искомое значение ищется в столбцах и указывается номер строки, а не столбца.

Как узнать порядковый номер элемента в списке? (Функция ПОИСКПОЗ-MATCH)

Допустим, есть список книг, и нам нужно узнать, какой по счёту в этом списке идет книга «12 времён года». Используем для этого функцию ПОИСКПОЗ, в качестве последнего аргумента указываем «точное совпадение» (ноль):

Результат: книга восьмая по счёту в заданном массиве.

Как просуммировать ячейки с нескольких листов?

Если у вас есть несколько однотипных листов с данными, которые вы хотите сложить, посчитать или обработать как-то иначе:

Для этого в ячейку, в которой вы хотите видеть результат, введите стандартную формулу (например, СУММ (SUM),) но укажите в аргументе через двоеточие название первого и последнего листа из списка тех листов, что вам нужно обработать:

Вы получите сумму ячеек с адресом B3 с листов «Данные1», «Данные2», «Данные3»:

Такая адресация работает для листов, расположенных последовательно. Синтаксис следующий:

Читайте также:  В эксель найти

=ФУНКЦИЯ(первый_лист:последний_лист!ссылка на диапазон)

Как разделить фразу, записанную в одну ячейку, на отдельные слова в отдельных ячейках?

Выделяем ячейки и нажинаем на кнопку «Текст по столбцам» (на ленте в разделе «Данные»):

В появившемся окне выбираем «С разделителями» (если нужно разбить текст на блоки – например, номера кредитной карты по 4 цифры – то нужно было бы выбрать “фиксированной ширины”) и нажимаем далее:

В следующем окне выбираем разделитель – в данном случае пробел – и нажимаем «Далее»:

В последнем окне выбираем, куда хотим разместить данные, и нажимаем «Готово»:

Источник: shagabutdinov.ru

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

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

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

Специальная вставка

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

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

Попробуем воспользоваться командой СПЕЦИАЛЬНАЯ ВСТАВКА и скопировать все данные.

Сначала мы выделяем имеющуюся таблицу, правой кнопкой вызываем меню и жмем КОПИРОВАТЬ.

В свободной ячейке снова вызываем меню правой кнопкой и нажимаем СПЕЦИАЛЬНАЯ ВСТАВКА.

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

Попробуем поэкспериментировать. В СПЕЦИАЛЬНОЙ ВСТАВКЕ выберем другой пункт, например, ФОРМУЛЫ. Получили уже неформатированную таблицу, но с работающими формулами.

Теперь вставим не формулы, а только ЗНАЧЕНИЯ результатов их вычислений.

Чтобы новая таблица со значениями получила внешний вид аналогичный образцу выделим ее и вставим ФОРМАТЫ с помощью специальной вставки.

Теперь попробуем выбрать пункт БЕЗ РАМКИ. Получили полную таблицу, но только без выделенных границ.

Полезный совет! Чтобы перенести формат вместе с размерами столбцов нужно перед копированием выделить не диапазон исходной таблицы, а столбцы целиком (в данном случаи это диапазон A:F).

Аналогично можно поэкспериментировать с каждым пунктом СПЕЦИАЛЬНОЙ ВСТАВКИ, чтобы наглядно увидеть, как она работает.

Перенос данных на другой лист

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

Рассмотрим, как это работает. Для начала переименуем листы Excel в месяцы. Затем с помощью уже знакомой нам СПЕЦИАЛЬНОЙ ВСТАВКИ перенесем таблицу на февраль и удалим значения из трех столбов:

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

Перенесем данные по остатку макарон каждой марки с января на февраль. Это делается буквально в пару нажатий.

  1. На листе ФЕВРАЛЬ ставим курсор в ячейку, показывающую количество макарон марки А на начало месяца. Можно посмотреть на рисунок выше – это будет ячейка D3.
  2. Ставим в этой ячейке знак РАВНО.
  3. Переходим на лист ЯНВАРЬ и кликаем на ячейку, показывающую количество макарон марки А на конец месяца (в нашем случае это ячейка F2 на листе “январь”).

Получаем следующее: в ячейке C2 образовалась формула, которая отсылает нас на ячейку F2 листа ЯНВАРЬ. Протянем формулу вниз, чтобы знать количество макарон каждой марки на начало февраля.

Аналогично можно перенести данные на все месяцы и получить наглядный годовой отчет.

Перенос данных в другой файл

Аналогично можно переносить данные из одного файла в другой. Данная книга в нашем примере так и называется EXCEL. Создадим еще одну и назовем ее ПРИМЕР.

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

Скопируем в книгу ПРИМЕР таблицу с помощью все той же СПЕЦИАЛЬНОЙ ВСТАВКИ. И опять удалим значения из трех столбцов. Проведем те же действия, что и в предыдущем параграфе, но переходить уже будем не на другой лист, а на другую книгу.

Получили новую формулу, которая показывает, что ячейка ссылается на книгу EXCEL. Причем видим, что ячейка F2 выглядит как $F$2, т.е. она зафиксирована. И если мы хотим протянуть формулу на остальные марки макарон, сначала нужно удалить значки доллара, чтобы снять фиксацию.

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

Источник: exceltable.com

САМОУЧИТЕЛЬ ПО EXCEL 2007-2010-2013
Копирование данных

Как перенести данные из одной таблицы в другую в Excel 2007-2010-2013

Есть два способа переноса данных:

Копирование-вставка

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

Например, у нас есть две таблицы в разных файлах:

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

Читайте также:  В excel денежный формат

Дальше (никуда не нажимая) зайти во вкладку “январь”, выбрать первую левую ячейку после таблицы и нажать “Вставить”:

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

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

Мы хотим перенести данные по объемам из нижней таблицы в верхнюю.

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

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

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

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

Если мы скопируем эти ячейки и вставим в наш второй файл, то получим такую картину:

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

Ссылка

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

1. Документ находится в том же файле

2. Документ находится в другом файле

Вариант 1. Документ находится в том же файле

Допустим, что в нашем файле есть два листа – «сумма» и «объем»:

Мы хотим перенести информацию с листа «объем» в лист «сумма». Для этого выделяем левую верхнюю ячейку диапазона, в который хотим перенести информацию, на листе «сумма» и ставим в ней знак «=»:

Далее открываем лист «объем» и выделяем левую верхнюю ячейку копируемого диапазона. В строке формул появляется ссылка на лист “объем”:

Нажимаем ENTER и автоматически попадаем на лист «сумма».

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

Вариант 2. Документ находится в другом файле

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

Ссылка в этом случае будет выглядеть следующим образом:

Ссылка всегда получается абсолютной (фиксированной) при сопоставлении разных файлов. Для копирования и протягивания формулы в данном случае следует использовать «полуабсолютную ссылку».

Источник: excel7.ru

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

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

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

Копирование таблицы с сохранением структуры

Если у вас есть одна или несколько таблиц, форматирование которых необходимо сохранять при переносе, то обычный метод Ctrl+CCtrl+V не даст нужного результата.

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

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

Способ 1: Специальная вставка

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

  1. Выделите исходные таблицы и проведите обычный перенос комбинацией клавиш Ctrl+CCtrl+V.
  2. Как мы помним из предыдущего примера, ячейки получаются стандартного размера. Чтобы исправить это, выделите скопированный массив данных и кликните правой кнопкой по нему. В контекстном меню выберите пункт «Специальная вставка».

Нам потребуется «Ширины столбцов», поэтому выбираем соответствующий пункт и принимаем изменения.

В результате у вас получится таблица идентичная той, что была в первом файле. Это удобно в том случае, если у вас десятки столбцов и выравнивать каждый, стандартными инструментами, нет времени/желания. Однако в этом методе есть недостаток — вам все равно придётся потратить немного времени, ведь изначально скопированная таблица не отвечает нашим запросам. Если это для вас неприемлемо, существует другой способ, при котором форматирование сохранится сразу при переносе.

Способ 2: Выделение столбцов перед копированием

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

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

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

Способ 3: Вставка формул с сохранением формата

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

Чтобы выполнить операцию, сделайте следующее:

  1. Выделите и скопируйте исходник.
  2. В контекстном меню вставки просто выберите «Значения» и подтвердите действие.

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

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

    Источник: compdude.ru

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

    У меня есть одна задачка, и уже третий день ломаю голову – не знаю, как ее выполнить. Есть 2 таблицы (примерно 500-600 строк в каждой), нужно взять столбец с названием товара из одной таблицы и сравнить его с названием товара из другой, и, если товары совпадут – скопировать и подставить значение из таблицы 2 в таблицу 1. Запутанно объяснил, но думаю, по фотке задачу поймете ( прим. : фотка вырезана цензурой, все-таки личная информация) .

    Заранее благодарю. Андрей, Москва.

    Доброго дня всем!

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

    Пример работы с функцией ВПР

    В качестве примера я взял две небольших таблички, представлены они на скриншоте ниже. В первой таблице (столбцы A, B — товар и цена) нет данных по столбцу B; во второй — заполнены оба столбца (товар и цена). Теперь нужно проверить первые столбцы в обоих таблицах и автоматически, при найденном совпадении, скопировать цену в первую табличку. Вроде, задачка простая.

    Две таблицы в Excel – сравниваем первые столбцы

    Как это сделать.

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

    A2 — значение из первого столбца первой таблицы (то, что мы будем искать в первом столбце второй таблицы);

    $E$1:$F$7 — полностью выделенная вторая таблица (в которой хотим что-то найти и скопировать). Обратите внимание на значок “$” — он необходим, чтобы при копировании формулы не менялись ячейки выделенной второй таблицы;

    2 — номер столбца, из которого буем копировать значение (обратите внимание, что у нас выделенная вторая таблица имеет всего 2 столбца. Если бы у нее было 3 столбца – то значение можно было бы копировать из 2-го или 3-го столбца);

    ЛОЖЬ — ищем точное совпадение (иначе будет подставлено первое похожее, что явно нам не подходит).

    Какая должна быть формула

    Собственно, можете готовую формулу подогнать под свои нужды, слегка изменив ее. Результат работы формулы представлен на картинке ниже: цена была найдена во второй таблице и подставлена в авто-режиме. Все работает!

    Значение было найдено и подставлено автоматически

    Чтобы цена была проставлена и для других наименований товара — просто растяните (скопируйте) формулу на другие ячейки. Пример ниже.

    Растягиваем формулу (копируем формулу в другие ячейки)

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

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

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

    Ну а у меня на этом пока всё, удачи!

    Источник: ocomp.info