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

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

Автор: admin | 19.02.2022

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

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

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

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

Первый шаг.

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

Второй шаг.

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

Третий шаг.

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

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

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

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

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

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

  

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

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

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

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

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

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

 

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

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

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

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

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

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

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

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

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

Функция ВПР (VLOOKUP) в Excel для чайников

Функция ВПР в Excel (на английском — VLOOKUP) по некоторому ключевому полю «подтягивает» данные из одного диапазона в другой. Ключевое поле должно присутствовать в обоих диапазонах данных (и там, куда «подтягиваем», и там, откуда берем данные).

Функция ВПР в Экселе: пошаговая инструкция

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

Во второй – цены:

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

Поэтому мы не можем прописать формулу умножения и «протянуть» вниз на все позиции.

Что делать? Надо как-то цены из второй таблицы подставить к соответствующему количеству в первой, т.е. цену товара А к количеству товара А, цену Б к количеству Б и т.д.

Вот так.

Функция ВПР в Эксель легко справится с задачей.

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

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

Кликаем по надписи «ВПР». Открывается следующее диалоговое окно.

Теперь нужно заполнить предлагаемые поля. В первом окошке «Искомое_значение» нужно указать критерий для ячейки, в которую мы вписываем формулу. В нашем случае это ячейка с наименованием товара «А».

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

Следующее поле «Номер_столбца» — это число, на которое столбец с искомыми данными (ценами) отстоит от столбца с критерием (наименованием товара) включительно. То есть отсчет идет, начиная с самого столбца с критерием. Если у нас во второй таблице оба столбца находятся рядом, то нужно указать число 2 (первый – критерий, второй — цены). Часто бывает, что данные отстоят от критерия на 10 или 20 столбцов. Это не важно, Excel все сосчитает.

Последнее поле «Интервальный_просмотр», где указывается тип поиска: точное (0) или приблизительное (1) совпадение критерия. Пока ставим 0 (или ЛОЖЬ). Второй вариант рассмотрен ниже.

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

Теперь легко рассчитать стоимость простым умножением количества на цену.

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

Особенности использования формулы ВПР в Excel

Функция ВПР имеет свои особенности, о которых следует знать.

1. Первую особенность можно считать общей для функций, которые используются для многих ячеек путем прописывания формулы в одной из них и дальнейшим копированием в остальные. Здесь нужно обращать внимание на относительность и абсолютность ссылок. Конкретно в ВПР критерий (первое поле) должно иметь относительную ссылку (без знаков $), так как у каждой ячейки свой собственный критерий. А вот поле «Таблица» должно иметь абсолютную ссылку (адрес диапазона прописывается через $). Если этого не сделать, то при копировании формулы диапазон «поедет» вниз и многие значения просто не найдутся, так как искать будет негде.

2. Номер столбца, указываемый в третьем поле «Номер_столбца» при использовании Мастера функций, должен отсчитываться, начиная с самого критерия.

3. Функция ВПР из диапазона с искомыми данными выдает первое сверху значение. Это значит, что, если во второй таблице, откуда мы пытаемся «подтянуть» некоторые данные, присутствуют несколько ячеек с одинаковым критерием, то в рамках выделенного диапазона ВПР захватит первое сверху значение. Об этом следует помнить. К примеру, если мы хотим к цене товара подтянуть количество из другой таблицы, а там этот товар встречается несколько раз (в нескольких строках), то к цене подтянется первое сверху количество.  

4. Последний параметр формулы, который 0 (нуль), ставить нужно обязательно. Иначе формула может криво работать.

5. После использования ВПР саму формулу лучше сразу удалить, оставив только полученные значения. Делается это очень просто. Выделяем диапазон с полученными значениями, нажимаем «копировать» и на это же место с помощью специальной вставки вставляем значения. Если таблицы находятся в разных книгах Excel, то очень удобно разорвать внешние связи (оставив вместо них только значения) с помощью специальной команды, которая находится по пути Данные → Изменить связи.

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

Это позволит удалить сразу все внешние ссылки. 

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

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

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

Вторая таблица меньше первой, т.е. некоторые коды в ней отсутствуют. Для отсутствующих позиций ВПР выдает ошибку #Н/Д.

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

Конструкция с функцией ЕСЛИОШИБКА

Вместе с функцией ВПР часто используют функцию ЕСЛИОШИБКА, которая «заглушает» ошибки #Н/Д и вместо них возвращает некоторое значение. Обычно это 0 или пусто. 

Как видно, ошибок больше нет, а вместо них пустые ячейки.

Разные форматы критерия в таблицах

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

Первый случай, когда критерии в первой таблице сохранены как числа, а критерии во второй таблице – как текст.

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

Такое решение используется довольно часто. Но оно не всегда подходит. Например, когда данные из второй таблицы регулярно выгружаются из какой-нибудь базы данных типа 1С. В подобных файлах вообще все сохранено как текст. И если мы планируем постоянно использовать такие данные, вставляя их в заранее подготовленный диапазон, то лучше, чтобы формулы работали без дополнительного вмешательства. 

Автоматически изменить формат критерия во второй таблице нельзя, т.к. ссылка ведет на целый диапазон. Придется вмешиваться в ссылку на критерий в первой таблице. Для этого потребуется дописать функцию ТЕКСТ, которая изменит числовой формат на текстовый. Синтаксис функции ТЕКСТ предполагает обязательное указание формата. Достаточно задать формат #. Ниже картинка с готовой формулой.

Две ошибки по-прежнему связаны с тем, что эти товары отсутствую во второй таблице. Чтобы их заглушить, можно вновь воспользоваться функцией ЕСЛИОШИБКА.

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

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

Бывает еще и третья, смешанная ситуация. Она встречается гораздо реже. Это когда в первой и второй таблице критерии сохранены и как число, и как текст, вперемешку. Здесь потребуется задействовать сразу все описанные выше функции: ЕСЛИОШИБКА, ТЕКСТ и +0. Вначале прописываем ЕСЛИОШИБКА и в качестве первого аргумента этой функции записываем ВПР с какой-либо конструкцией для изменения формата. Например, ВПР с формулой ТЕКСТ. В качестве второго аргумента (т.е. того, что должно быть в случае ошибки) записываем вторую конструкцию ВПР с +0. Таким образом, если ВПР с функцией ТЕКСТ не выдает ошибку, значит все ОК. Но если первая конструкция возвращает ошибку #Н/Д, то функция ЕСЛИОШИБКА подставляет вторую конструкцию – ВПР с +0. Другими словами, мы вначале принудительно делаем все критерии текстовыми, а затем, числовыми. Таким образом, ВПР проверяет оба формата. Один из них совпадет с форматом во второй таблице. Немного громоздко получается, но в целом все работает.

Отсутствующие критерии по-прежнему вызывают ошибку #Н/Д. В таком случае всю формулу можно еще раз «обернуть» в ЕСЛИОШИБКА.

Функция СЖПРОБЕЛЫ для чистки текстового критерия

В качестве критерия рекомендуется брать уникальный код, в котором опечатки, характерные для текста, маловероятны. Но иногда все-таки кода нет и критерием выступает текст (названия организаций, фамилии людей и т.д.). В этом случае возможны случайные ошибки в написании. Одна из распространенных ошибок – лишние пробелы. Проблема решается просто с помощью функции СЖПРОБЕЛЫ для всех критериев. Сделать это можно внутри формулы ВПР, а можно и предварительно пройтись по всем критериям в обеих таблицах. Кому как удобней.

Подсчет номера столбца в большой таблице

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

Здорово экономит время.

Интервальный просмотр в функции ВПР

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

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

Проще понять на примере. По результатам выполнения плана продаж каждому торговому агенту нужно выдать заслуженную премию (в процентах от оклада). Если план выполнен менее, чем на 100%, премия не положена, если план выполнен от 100% до 110% (110% не входит) – премия 20%, от 110% до 120% (120% не входит) – 40%, 120% и более – премия 60%. Данные находятся в следующем виде.

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

=ВПР(B2;$E$2:$F$5;2;1)

и скопируем вниз.

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

Джеки Чан выполнил план на 124%. Значит ВПР в качестве критерия ищет во второй таблице ближайшее меньшее значение. Это 120%. Затем отсчитывает 2 столбца и возвращает премию 60%. Брюс Ли план не выполнил, поэтому его ближайший меньший критерий – 0%.

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

Поделиться в социальных сетях:

Импорт из таблицы Excel

Знакомство с Power Query

• Импорт данных из Интернета
  Статья
• Импорт из таблицы Excel
  Статья
• Настройте строку заголовка
  Статья
• Преобразование типа данных
  Статья
• Отфильтровать ненужные строки
  Статья
• Разделить данные на несколько столбцов
  Статья
• Создайте столбец из примера
  Статья
• Создать условный столбец
  Статья
• Объединение запросов и объединение таблиц
  Статья
• Просмотрите примененные шаги
  Статья
• Добавьте данные, а затем обновите запрос
  Статья

Знакомство с Power Query

Справка по Excel и обучение

Знакомство с Power Query

Знакомство с Power Query

Импорт из таблицы Excel

• Импорт данных из Интернета
  Статья
• Импорт из таблицы Excel
  Статья
• Настройте строку заголовка
  Статья
• Преобразование типа данных
  Статья
• Отфильтровать ненужные строки
  Статья
• Разделить данные на несколько столбцов
  Статья
• Создайте столбец из примера
  Статья
• Создать условный столбец
  Статья
• Объединение запросов и объединение таблиц
  Статья
• Просмотрите примененные шаги
  Статья
• Добавьте данные, а затем обновите запрос
  Статья

Когда вы загружаете данные о продажах в Power Query и применяете некоторые преобразования, Power Query создает новый лист, но исходный лист данных о продажах остается прежним.

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

1. Обратите внимание на наличие категорий продуктов и листов данных о продажах. Откройте рабочий лист Данные о продажах .

2. Поместите курсор на таблицу Excel, выберите Данные > Получить и преобразовать данные > Из таблицы/диапазона .

   Excel открывает редактор Power Query, и ваши данные отображаются в области предварительного просмотра.

3. Чтобы отобразить все таблицы запросов в книге на панели запросов, щелкните стрелку слева от панели предварительного просмотра.

   Таблица Product Sales указана последней, затем 9Таблица 0089 World Cup Results , а затем таблицы, использованные ранее в учебнике. Когда вы добавляете новые таблицы, они автоматически отображаются здесь.

   1. На панели Параметры запроса показаны шаги преобразования, которые вы предприняли. Вы можете просматривать, изменять и удалять любые шаги. Power Query добавил для вас несколько шагов. В следующей таблице приведены важные функции:

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

4. Чтобы вернуть преобразованные данные в Excel, выберите Главная > Закрыть и загрузить .

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

Как импортировать и экспортировать данные в Excel

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

Экспорт данных

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

1. Перейдите на вкладку Файл .
2. Слева нажмите Экспорт .
3. Щелкните Изменить тип файла .
4. В разделе «Другие типы файлов» выберите тип файла.
   • Текст (с разделителями табуляции): Данные ячейки будут разделены табуляцией.
   • CSV (с разделителями-запятыми): Данные ячейки будут разделены запятой.
   • Форматированный текст (разделенный пробелом): Данные ячейки будут разделены пробелом.
   • Сохранить как другой тип файла: Выберите другой тип файла, когда появится диалоговое окно «Сохранить как».

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

5. Нажмите Сохранить как .
6. Укажите, где вы хотите сохранить файл.
7. Нажмите Сохранить .

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

8. Нажмите Да .

Импорт данных

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

1. Щелкните вкладку Данные на ленте.
2. Нажмите кнопку Получить данные
   .

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

3. Выберите из файла .
4. Выберите Из текста/CSV .

   Если у вас есть данные для импорта из Access, Интернета или другого источника, вместо этого выберите один из этих параметров в группе Получить внешние данные.

5. Выберите файл, который хотите импортировать.
6. Нажмите Импорт .

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

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

8. Щелкните Загрузить .