Newcomposers.ru

IT Мир
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как связать две таблицы в excel

Как связать две таблицы в excel

Если вы ещё не знакомы со сводными таблицами, то начните с этой статьи.

Проблема

Бывает так, что анализируемые данные попадают к нам в виде отдельных таблиц, которые, тем не менее, нужно связать. Это легко может сделать MS Access, а в Excel для этого приходилось всегда использовать формулы типа ВПР (VLOOKUP). Однако, начиная с Excel 2013, у нас появилась возможность при построении сводной таблицы в качестве источника использовать несколько таблиц, связанных между собой по ключевым полям.

Пример

В нашем примере мы располагаем 4-мя таблицами: Заказы , Строки заказов , Товары , Клиенты .

Таблица Строк заказов:

Исходные таблицы оформлены в виде умных таблиц: Orders , OrderLines , Goods и Clients .

Вполне очевидно, что таблицы Orders и OrderLines могут быть связаны по полю ID_Заказа , таблицы Orders и Clients — по полю ID_клиента , таблицы OrderLines и Goods — по полю ID_товара .

Скачать пример

Создание модели данных

Создадим сводную таблицу на основе любой из имеющихся таблиц.

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

В появившейся панеле Поля сводной таблицы вы видите надпись ДРУГИЕ ТАБЛИЦЫ.

Нажмём её. Появится такой вопрос:

Отвечаем Да и видим, что в список полей добавились все наши таблицы:

Если вы начнёте выбирать поля, то через некоторое время в списке полей появится кнопка СОЗДАТЬ.

Нажмём её и создадим связи между нашими таблицами. Так создаётся связь между таблицей Orders и OrderLines . Обратите внимание, что Excel умеет создавать связь типа » один к одному » или » один ко многим «. Причём первой надо указывать таблицу, где «много», в противном случае Excel ругается и предлагает поменять их местами.

Аналогично создаём другие связи.


В диалоговое окно Управление связями можно попасть через ленту АНАЛИЗ команда Отношения

Чтобы видеть больше полей на панеле Поля сводной таблицы , можно через кнопку Сервис (в виде шестерёнки) выбрать это представление:

Результат будет таким:

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

Как связать две таблицы в excel

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

Инструкция

Устанавливаем себе надстройку ЁXCEL . Читаем справку.

Приступаем к решению. Переходим во вкладку ЁXCEL главного меню, нажимаем кнопку «Таблицы», в выпавшем списке выбираем команду «Объединить таблицы»:

В открывшемся диалоговом окне выделяем листы с таблицами, которые необходимо объединить и нажимаем «ОК»:

Программа сформирует запрос — объединит таблицы и выведет информационное сообщение:

Переходим на итоговый лист (тот где хотим вывести общую таблицу), устанавливаем курсор в ячейку «A1«. Переходим в главном меню во вкладку «Данные» в разделе «Получение внешних данных» нажимаем кнопку «Существующие подключения»:

В открывшемся диалоговом окне выбираем «Подключения в этой книге» — «Запрос из Excel Files» и нажимаем «Открыть»:

В открывшемся диалоговом окне устанавливаем переключатели в положения «Таблица» и «Имеющийся лист», нажимаем «ОК»:

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

Теперь все таблицы связаны. Добавьте новые строчки в любую из объединенных таблиц. Перейдите во вкладку «Данные» и нажмите кнопку «Обновить все»:

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

Чтобы обновление итоговой таблицы происходило автоматически вставьте в модуль каждого листа (кроме Итогового) следующий код (Как вставлять макросы?):

Видео-пример

Важно:

  • Количество столбцов во всех таблицах должно быть одинаково;
  • Кроме таблиц на листах не должно быть никакой информации;
  • Если вы переместили файл в другую папку или отправили файл коллеге по электронной почте — необходимо заново связать таблицы (в запросе прописывается абсолютный путь к файлу).

Чтобы запрос работал не зависимо от того в какой папке лежит файл вставьте в модуль «ЭтаКнига» следующий код:

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

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

Возможные ошибки при использовании этого метода:

  • В таблицах одинаковое количество столбцов, но при попытке использовать запрос получаем сообщение: «В таблицах или запросах, выбранных в запросе на объединение, не совпадает число столбцов.» Причина: в одном или нескольких листах с таблицами есть пустые столбцы (в них раньше были данные) и MS Excel считает их столбцами таблиц. Решение: Выделить и удалить все пустые столбцы на листах с таблицами.
  • Все столбцы удалили, но ошибка осталась. Причина: файл сохранен на сетевом диске или открыт из почтовой программы. Решение: сохраните файл у себя на локальном компьютере, выполните запрос, вставьте в него макрос меняющий путь в запросе и выложите обратно в сеть (если нужно).
  • В полученном запросе в некоторых ячейках пропадают данные. Причина: в ваших таблицах встречаются столбцы, которые одновременно содержатся и числовые и текстовые значения. MS Excel считает, что эти столбцы должны содержать только числа и не выводит текст. Решение:преобразуйте все числовые значения в этих столбцах во всех таблицах в текстовые. Обновите запрос — текстовые данные появятся.
Читать еще:  Итерации в excel

Как объединить две или несколько таблиц Excel

Как объединить две или несколько таблиц Excel

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

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

Как объединить две или несколько таблиц Excel

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

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

Варианты копирования и вставки в Excel не имеют единственного вкуса! Когда вы копируете свои данные и переходите к их вставке, щелкните правой кнопкой мыши в том месте, куда вы хотите вставить данные, и выберите опцию Специальная вставка. Вот несколько наиболее полезных:

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

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

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

Используйте функцию «Переместить или Копировать» для объединения рабочих книг

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

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

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

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

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

Используйте функцию консолидации для объединения рабочих листов

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

1. Создайте новую рабочую книгу или рабочий лист для ваших консолидированных данных, затем откройте исходные рабочие листы.

2. Откройте новую электронную таблицу и перейдите к кнопке «Консолидация» на вкладке «Данные».

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

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

Как объединить две или несколько таблиц Excel

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

6. Нажатие на кнопку «Обзор» открывает ваш файловый менеджер. Выберите электронную таблицу, которую вы хотите объединить.

7. Нажмите кнопку «Ссылка» и откройте электронную таблицу, которую вы только что выбрали. Здесь вы можете выделить данные, которые вы хотите объединить.

8. Нажмите клавишу Enter и затем кнопку «Добавить». Это должно поместить выбранные данные в список слияния.

9. Повторите вышеуказанные шаги для любого количества рабочих листов / книг, которые вы хотите объединить.

10. Нажмите «ОК», чтобы объединить выбранные данные в новую электронную таблицу и убедиться, что они работают правильно.

Заключение

Эти методы объединяют удобный интерфейс с приличное количество энергии. Существует не так много заданий, которые эти три инструмента, в некоторой комбинации, не смогут выполнить, и они не требуют каких-либо экспериментов с кодом VB или макросами. Однако, как и во всех вещах в Excel, это очень помогает, если ваши данные хорошо организованы, прежде чем вы начнете логически называть свои книги и рабочие таблицы, убедитесь, что строки и столбцы выстроены так, как вы хотите, и убедитесь, что ваши ссылки line up!

Читать еще:  Разница значений в excel

Связанные таблицы в Excel, как их сделать? Простые советы

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

Связанные таблицы в Excel, что это такое и зачем с ними работать

Здравствуйте друзья! Во время работы с таблицами Excel приходится их связывать. Что такое связанные таблицы в Эксель? Их называют Эксель таблицами, с помощью которых переносится информация с одной таблицы на другую таблицу.

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

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

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

Разберём два эффективных варианта создания связанных Эксель таблиц. В начале работы, создайте на своём компьютере две таблицы. Например, первая таблица будет с таким названием, как «Даты», а вторая таблица пустая.

Вместе с тем, запускаете на компьютере ещё одну таблицу – «Даты» и записываете в неё значения. Например, «0.1.11.2019» Январь и так далее. Выделяете столбцы в таблице с любой информацией и нажимаете правой кнопкой мыши – «Копировать» (Скрин 1).

Запускаем второй лист таблицы Excel. Затем, нужно кликнуть в любую ячейку таблицы компьютерной мышкой и кликните кнопки – «Вставить» и «Вставить связь» (Скрин 2).

После их нажатия, в Вашей таблице появятся данные из другой таблицы (Скрин 3).

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

Запустите на своём компьютере пустую Excel таблицу. Далее, в ней нажмите кнопку – «Вставка» затем, «Объект» (Скрин 4).

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

Заключение

В статье мы научились создавать связанные таблицы в Excel. Мы разобрали два способа, которые наиболее эффективны для новичков. Конечно, есть и другие варианты. Например, вставить таблицу в Эксель, через кнопки «Файл» и «Открыть» или с помощью формул. Эти практические советы Вам должны помочь в соединении двух таблиц Excel. Спасибо за внимание, удачи Вам!

С уважением, Иван Кунпан.

P.S. Практические статьи по работе с Excel-таблицами:

Как объединить две таблицы Excel по частичному совпадению ячеек

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

Итак, есть два листа Excel, которые нужно объединить для дальнейшего анализа данных. Предположим, в одной таблице содержатся цены (столбец Price) и описания товаров (столбец Beer), которые Вы продаёте, а во второй отражены данные о наличии товаров на складе (столбец In stock). Если Вы или Ваши коллеги составляли обе таблицы по каталогу, то в обеих должен присутствовать как минимум один ключевой столбец с уникальными идентификаторами товаров. Описание товара или цена могут изменяться, но уникальный идентификатор всегда остаётся неизменным.

Трудности начинаются, когда Вы получаете некоторые таблицы от производителя или из других отделов компании. Дело может ещё усложниться, если вдруг вводится новый формат уникальных идентификаторов или самую малость изменятся складские номенклатурные обозначения (SKU). И перед Вами стоит задача объединить в Excel новую и старую таблицы с данными. Так или иначе, возникает ситуация, когда в ключевых столбцах имеет место только частичное совпадение записей, например, “12345” и “12345-новый_суффикс“. Вам-то понятно, что это тот же SKU, но компьютер не так догадлив! Это не точное совпадение делает невозможным использование обычных формул Excel для объединения данных из двух таблиц.

И что совсем плохо – соответствия могут быть вовсе нечёткими, и “Некоторая компания” в одной таблице может превратиться в “ЗАО «Некоторая Компания»” в другой таблице, а “Новая Компания (бывшая Некоторая Компания)” и “Старая Компания” тоже окажутся записью об одной и той же фирме. Это известно Вам, но как это объяснить Excel?

Выход есть всегда, читайте далее и Вы узнаете решение!

Замечание: Решения, описанные в этой статье, универсальны. Вы можете адаптировать их для дальнейшего использования с любыми стандартными формулами, такими как ВПР (VLOOKUP), ПОИСКПОЗ (MATCH), ГПР (HLOOKUP) и так далее.

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

Ключевой столбец в одной из таблиц содержит дополнительные символы

Рассмотрим две таблицы. Столбцы первой таблицы содержат номенклатурный номер (SKU), наименование пива (Beer) и его цену (Price). Во второй таблице записан SKU и количество бутылок на складе (In stock). Вместо пива может быть любой товар, а количество столбцов в реальной жизни может быть гораздо больше.

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

Читать еще:  Удалить дубли в excel

Ключевым в таблице в нашем примере является столбец A с данными SKU, и нужно извлечь из него первые 5 символов. Добавим вспомогательный столбец и назовём его SKU helper:

  • Наводим указатель мыши на заголовок столбца B, при этом он должен принять вид стрелки, направленной вниз:
  • Кликаем по заголовку правой кнопкой мыши и в контекстном меню выбираем Вставить (Insert):
  • Даём столбцу имя SKU helper.
  • Чтобы извлечь первые 5 символов из столбца SKU, в ячейку B2 вводим такую формулу:

Здесь A2 – это адрес ячейки, из которой мы будем извлекать символы, а 5 – количество символов, которое будет извлечено.

  • Копируем эту формулу во все ячейки нового столбца.
  • Готово! Теперь у нас есть ключевые столбцы с точным совпадением значений – столбец SKU helper в основной таблице и столбец SKU в таблице, где будет выполняться поиск.

    Теперь при помощи функции ВПР (VLOOKUP) мы получим нужный результат:

    Другие формулы

    • Извлечь первые Х символов справа: например, 6 символов справа из записи “DSFH-164900”. Формула будет выглядеть так:

    =ПРАВСИМВ(A2;6)
    =RIGHT(A2,6)
    Пропустить первые Х символов, извлечь следующие Y символов: например, нужно извлечь “0123” из записи “PREFIX_0123_SUFF”. Здесь нам нужно пропустить первые 8 символов и извлечь следующие 4 символа. Формула будет выглядеть так:

    =ПСТР(A2;8;4)
    =MID(A2,8,4)
    Извлечь все символы до разделителя, длина получившейся последовательности может быть разной. Например, нужно извлечь “123456” и “0123” из записей “123456-суффикс” и “0123-суффикс” соответственно. Формула будет выглядеть так:

    Одним словом, Вы можете использовать такие функции Excel, как ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT), ПСТР (MID), НАЙТИ (FIND), чтобы извлекать любые части составного индекса. Если с этим возникли трудности – свяжитесь с нами, мы сделаем всё возможное, чтобы помочь Вам.

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

    Предположим, таблица, в которой производится поиск, содержит столбец с идентификаторами. В ячейках этого столбца содержатся записи вида XXXX-YYYY, где XXXX – это кодовое обозначение группы товаров (мобильные телефоны, телевизоры, видеокамеры, фотокамеры), а YYYY – это код товара внутри группы. Главная таблица состоит из двух столбцов: в одном содержатся коды товарных групп (Group), во втором записаны коды товаров (ID). Мы не можем просто отбросить коды групп товаров, так как один и тот же код товара может повторяться в разных группах.

    Добавляем в главной таблице вспомогательный столбец и называем его Full ID (столбец C), подробнее о том, как это делается рассказано ранее в этой статье.

    В ячейке C2 запишем такую формулу:

    Здесь A2 – это адрес ячейки, содержащей код группы; символ “” – это разделитель; B2 – это адрес ячейки, содержащей код товара. Скопируем формулу в остальные строки.

    Теперь объединить данные из наших двух таблиц не составит труда. Мы будем сопоставлять столбец Full ID первой таблицы со столбцом ID второй таблицы. При обнаружении совпадения, записи из столбцов Description и Price второй таблицы будут добавлены в первую таблицу.

    Данные в ключевых столбцах не совпадают

    Вот пример: Вы владелец небольшого магазина, получаете товар от одного или нескольких поставщиков. У каждого из них принята собственная номенклатура, отличающаяся от Вашей. В результате возникают ситуации, когда Ваша запись “Case-Ip4S-01” соответствует записи “SPK-A1403” в файле Excel, полученном от поставщика. Такие расхождения возникают случайным образом и нет никакого общего правила, чтобы автоматически преобразовать “SPK-A1403” в “Case-Ip4S-01”.

    Плохая новость: Данные, содержащиеся в этих двух таблицах Excel, придётся обрабатывать вручную, чтобы в дальнейшем было возможно объединить их.

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

    1. Создаём вспомогательную таблицу для поиска.

    Создаём новый лист Excel и называем его SKU converter. Копируем весь столбец Our.SKU из листа Store на новый лист, удаляем дубликаты и оставляем в нём только уникальные значения.

    Рядом добавляем столбец Supp.SKU и вручную ищем соответствия между значениями столбцов Our.SKU и Supp.SKU (в этом нам помогут описания из столбца Description). Это скучная работёнка, пусть Вас радует мысль о том, что её придётся выполнить только один раз :-).

    В результате мы имеем вот такую таблицу:

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

    В главную таблицу (лист Store) вставляем новый столбец Supp.SKU.

    Далее при помощи функции ВПР (VLOOKUP) сравниваем листы Store и SKU converter, используя для поиска соответствий столбец Our.SKU, а для обновлённых данных – столбец Supp.SKU.

    Столбец Supp.SKU заполняется оригинальными кодами производителя.

    Замечание: Если в столбце Supp.SKU появились пустые ячейки, то необходимо взять все коды SKU, соответствующие этим пустым ячейкам, добавить их в таблицу SKU converter и найти соответствующий код из таблицы поставщика. После этого повторяем шаг 2.

    3. Переносим данные из таблицы поиска в главную таблицу

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

    При помощи функции ВПР (VLOOKUP) объединяем данные листа Store с данными листа Wholesale Supplier 1, используя для поиска соответствий столбец Supp.SKU.

    Вот пример обновлённых данных в столбце Wholesale Price:

    Всё просто, не так ли? Задавайте свои вопросы в комментариях к статье, я постараюсь ответить, как можно скорее.

    Ссылка на основную публикацию
    Adblock
    detector