Дэвид из Флориды задает сегодняшний вопрос:
У меня две тетради. Оба имеют одинаковые данные в столбце A, но остальные столбцы разные. Как я могу объединить эти две книги?
Я спросил Дэвида, возможно ли, что в одной книге больше записей, чем в другой. И ответ - да. Я спросил Дэвида, появляется ли ключевое поле только один раз в каждом файле. Ответ тоже положительный. Сегодня я решу эту проблему с помощью Power Query. Инструменты Power Query находятся в версиях Excel 2016+ для Windows в разделе «Получить и преобразовать» на вкладке «Данные». Если у вас есть версии Excel 2010 или Excel 2013 для Windows, вы можете загрузить надстройку Power Query для этих версий.
Вот рабочая тетрадь Дэвида 1. В ней есть продукт, а затем три столбца данных.
Вот рабочая тетрадь Дэвида 2. В ней есть код продукта, а затем другие столбцы. В этом примере в книге2 есть дополнительные продукты, но решения будут работать, если в любой книге есть дополнительные столбцы.
Вот шаги:
-
Выберите данные, получить данные, из файла, из книги:
Загрузить данные из файла - Перейдите к первой книге и нажмите ОК.
- В диалоговом окне "Навигатор" выберите лист слева. (Даже если есть только один рабочий лист, вы должны его выбрать.) Вы увидите данные справа.
- В диалоговом окне "Навигатор" откройте раскрывающийся список "Загрузить" и выберите "Загрузить в…".
- Выберите Only Create a Connection и нажмите OK.
-
Повторите шаги 1–5 для второй книги.
Создайте подключение к книге Если вы выполнили обе книги, вы должны увидеть два соединения на панели запросов и подключений в правой части экрана Excel.
Подключения к обеим книгам Продолжайте выполнять шаги по объединению книг:
-
Данные, получение данных, объединение запросов, объединение.
Объединить два запроса с разными столбцами - В верхнем раскрывающемся списке диалогового окна «Слияние» выберите первый запрос.
- Во втором раскрывающемся списке диалогового окна «Слияние» выберите второй запрос.
- Щелкните заголовок продукта в верхнем превью (это ключевое поле. Обратите внимание, что вы можете выбрать несколько ключевых полей, нажав Ctrl + щелчок).
- Щелкните заголовок Код продукта во втором предварительном просмотре.
-
Откройте тип соединения и выберите полный внешний (все строки из обоих).
Шаги 8–12 показаны здесь -
Щелкните ОК. В предварительном просмотре данных не отображаются лишние строки, а только несколько раз отображается «Таблица» в последнем столбце.
Это не выглядит многообещающим - Обратите внимание, что в заголовке DavidTwo есть значок «Развернуть». Щелкните этот значок.
-
Необязательно, но я всегда снимаю флажок «Использовать исходное имя столбца как префикс». Щелкните ОК.
Разверните поля из книги 2 Результаты показаны в этом превью:
Все записи из любой книги - В Power Query используйте Home, Close & Load.
Вот прекрасная функция: если базовые данные в любой книге изменяются, вы можете щелкнуть значок «Обновить», чтобы добавить новые данные в книгу результатов.
Заметка
Значок «Обновить» обычно скрыт. Перетащите левый край панели «Запросы и подключения» влево, чтобы появился значок.
Смотреть видео
Стенограмма видео
Изучите Excel из подкаста, эпизод 2216: объедините две книги на основе общего столбца.
Привет, добро пожаловать обратно в netcast, я Билл Джелен. Сегодняшний вопрос от Дэвида, который был на моем семинаре в Мельбурне, Флорида, для отделения космического побережья IIA.
У Дэвида есть две разные книги, в которых столбец А является общим для них обоих. Итак, вот Рабочая тетрадь 1, вот Рабочая тетрадь 2 - у обеих есть код продукта. У этого есть элементы, которых нет в первом, или наоборот, и Дэвид хочет объединить все столбцы. Итак, у нас есть три столбца здесь и четыре столбца здесь. Я поместил их в одну книгу, на случай, если вы загружаете книгу для работы. Возьмите каждый из них, переместите в отдельную книгу и сохраните.
Хорошо, чтобы объединить эти файлы, мы будем использовать Power Query. Power Query встроен в Excel 2016. Если вы используете Windows версии 10 или 13, вы можете зайти в Microsoft и загрузить Power Query. Вы можете начать с новой пустой книги с пустым листом. Вы собираетесь сохранить этот файл - Сохранить как, может быть, Рабочую книгу, чтобы показать результаты объединения файлов .xlsx. Хорошо? И что мы собираемся сделать, так это сделать два запроса. Мы собираемся перейти к данным, получить данные, из файла, из книги, а затем выберем первый файл. В предварительном просмотре выберите лист с вашими данными, и нам не нужно ничего делать с этими данными. Поэтому просто откройте поле загрузки и выберите «Загрузить в», «Только создать соединение», нажмите «ОК». Отлично. Теперь мы повторим это для второго элемента - данных, из файла,В книге выберите DavidTwo, выберите имя листа, а затем откройте загрузку, «Загрузить в, только создать соединение». Вы увидите здесь, на этой панели, у нас есть оба соединения. Хорошо.
Теперь реальная работа - данные, получение данных, объединение запросов, слияние, а затем в диалоговом окне слияния выберите DavidOne, DavidTwo, и этот следующий шаг совершенно не интуитивно понятен. Вы должны это сделать. Выберите общий столбец или столбцы - так Product и Product. Хорошо. И затем будьте очень осторожны с типом соединения. Мне нужны все строки из обоих, потому что в одной может быть дополнительная строка, и мне нужно это увидеть, а затем мы нажимаем ОК. Хорошо. И вот первоначальный результат. Не похоже, что это сработало; не похоже, что он добавил дополнительные элементы, которые были в файле 2. И у нас есть этот столбец 5 - теперь он пустой. Я собираюсь щелкнуть правой кнопкой мыши столбец 5 и сказать: «Удалить этот столбец». Итак, откройте этот значок расширения и снимите этот флажок для Использовать исходное имя столбца в качестве префикса и БАМ! оно работает. Таким образом, дополнительные элементы, которые были в файле 2, которых нет в файле 1,действительно появляются.
Хорошо. Теперь в сегодняшнем файле, похоже, этот столбец кода продукта лучше, чем этот столбец продукта, потому что в нем есть дополнительные строки. Но в будущем может быть день, когда в Рабочей книге 1 будут вещи, которых нет в Рабочей книге 2. Итак, я собираюсь оставить их там обоих, и я не собираюсь избавляться от каких-либо нулей, потому что, например, даже если эта строка внизу кажется полностью нулевой, в будущем может возникнуть ситуация, когда у нас здесь несколько нулей, потому что чего-то не хватает. Хорошо? Итак, наконец, Close & Load, и у нас есть шестнадцать строк.
Теперь, в будущем, допустим, что-то изменится. Хорошо, поэтому мы вернемся к одному из этих двух файлов, и я изменю класс для Apple на 99, и давайте даже вставим что-нибудь новое и сохраним эту книгу. Хорошо. А затем, если мы хотим, чтобы наш файл слияния обновился, подойдите сюда - теперь, будьте осторожны, когда вы делаете это в первый раз, вы не видите значок «Обновить» - вам нужно взять эту панель и перетащить ее . И мы сделаем Refresh, и загрузятся 17 строк, появится арбуз, Apple изменится на 99 - это прекрасно. А теперь, хочешь узнать о Power Query? Купите эту книгу Кена Пульса и Мигеля Эскобара, M для (ДАННЫЕ) ОБЕЗЬЯНА. Я тебя в курсе.
Подводя итоги сегодня: у Дэвида из Флориды есть две рабочие тетради, которые он хочет объединить; у них обоих одинаковые поля в столбце A, но все остальные столбцы разные; в одной книге могут быть дополнительные элементы, которых нет в другой, и Дэвид хочет их; ни в одном файле нет дубликатов; мы собираемся использовать запрос мощности, чтобы решить эту проблему, поэтому начните с новой пустой книги на пустом листе; вы собираетесь выполнить три запроса: первый - данные, из файла, книга, а затем загрузить только в созданное соединение; то же самое для второй книги, а затем Data, Get Data, Merge, выберите два соединения, выберите столбец, который является общим для обоих - в моем случае, Product - а затем из типа соединения вы хотите полное соединение все из файла 1, все из файла 2. И тогда замечательно, если основные данные изменяются,вы можете просто обновить запрос.
Чтобы загрузить книгу из сегодняшнего видео, перейдите по URL-адресу в описании YouTube.
Что ж, эй, я хочу, как Дэвид, за то, что пришел на мой семинар, я хочу поблагодарить тебя за то, что зашли. Увидимся в следующий раз для другого сетевого вещания от.
Скачать файл Excel
Чтобы загрузить файл Excel: comb-based-on-common-column.xlsx
Power Query - отличный инструмент в Excel.
Идея дня в Excel
Я попросил совета у моих друзей-мастеров Excel по поводу Excel. Сегодняшняя мысль задуматься:
«Всегда нажимайте F4 при чтении диапазона или матрицы в функции»
Таня Кун
