Девід із Флориди задає сьогоднішнє запитання:
У мене є дві робочі книги. В обох є однакові дані у стовпці A, але решта стовпців різні. Як я можу об’єднати ці дві книги?
Я запитав Девіда, чи можливо, що в одній книзі більше записів, ніж в іншій. І відповідь - так. Я запитав Девіда, чи ключове поле з’являється лише один раз у кожному файлі. Відповідь також так. Сьогодні я вирішу це за допомогою Power Query. Інструменти Power Query містяться у версіях Windows Excel 2016+ у розділі Отримати та перетворити на вкладці Дані. Якщо у вас версії Excel 2010 або Excel 2013 для Windows, ви можете завантажити надбудову Power Query для цих версій.
Ось робоча книга Давида 1. У ній є Product, а потім три стовпці даних.
Ось робоча книга Давида 2. У ній є код товару, а потім інші стовпці. У цьому прикладі в книзі2 є додаткові продукти, але рішення будуть працювати, якщо кожна книга має додаткові стовпці.
Ось кроки:
-
Виберіть Дані, Отримати дані, З файлу, З робочої книги:
Завантажте дані з файлу - Перейдіть до першої книги та натисніть OK
- У діалоговому вікні Навігатора виберіть аркуш ліворуч. (Навіть якщо є лише один аркуш, його потрібно вибрати.) Ви побачите дані праворуч.
- У діалоговому вікні Навігатора відкрийте спадне меню Завантажити і виберіть Завантажити в…
- Виберіть Тільки створити підключення та натисніть OK.
-
Повторіть кроки 1-5 для другої книги.
Створіть підключення до книги Якщо ви створили обидві книги, ви побачите два з'єднання на панелі "Запити та підключення" праворуч на екрані Excel.
Підключення до обох робочих книжок Виконайте кроки для об’єднання книг:
-
Дані, Отримати дані, Об’єднати запити, Об’єднати.
Об’єднайте два запити з різними стовпцями - У верхньому спадному меню діалогового вікна «Об’єднання» виберіть перший запит.
- З другого спадного меню у діалоговому вікні «Об’єднання» виберіть другий запит.
- Клацніть на заголовок Product у верхньому попередньому перегляді (це ключове поле. Зверніть увагу, що ви можете багаторазово вибрати два або більше ключових полів, натиснувши Ctrl + клацання)
- Натисніть на заголовок Код товару у другому попередньому перегляді.
-
Відкрийте Тип приєднання та виберіть Full Outer (Всі рядки з обох)
Крок 8 - 12 проілюстровано тут -
Клацніть OK. Попередній перегляд даних не відображає зайвих рядків, а лише останній раз відображає "Таблиця".
Це не виглядає багатообіцяючим - Зверніть увагу, що у заголовку DavidTwo є піктограма "Розгорнути". Клацніть на цю піктограму.
-
Необов’язково, але я завжди знімаю прапорець «Використовувати оригінальну назву стовпця як префікс». Клацніть OK.
Розгорніть поля з книги 2 Результати показані в цьому попередньому перегляді:
Усі записи з будь-якої книги - У Power Query використовуйте Домашня сторінка, Закрити та завантажити.
Ось прекрасна особливість: якщо основні дані в будь-якій книзі змінюються, ви можете натиснути піктограму Оновити, щоб ввести нові дані до книги результатів.
Примітка
Піктограма для оновлення зазвичай прихована. Перетягніть лівий край області Запити та підключення ліворуч, щоб відкрити піктограму.
Переглянути відео
Стенограма відео
Дізнайтеся Excel з подкасту, епізод 2216: Об’єднайте дві робочі книги на основі загальної колонки
Привіт, ласкаво просимо до мережі, я Білл Джелен. Сьогоднішнє запитання від Девіда, який був на моєму семінарі в Мельбурні, штат Флорида, для Розділу про космічне узбережжя МІА.
У Девіда є дві різні книги, де колонка A є спільною між ними обома. Отже, ось Workbook 1, ось Workbook 2 - обидва мають код товару. У цьому є елементи, яких у першого немає, або навпаки, і Девід хоче об’єднати всі стовпці. Отже, у нас тут є три стовпці, а тут - чотири. Я поклав обидва в одну книгу, на випадок, якщо ви завантажуєте книгу для роботи. Візьміть кожен із них, перенесіть його до власної книги та збережіть.
Добре, для об’єднання цих файлів ми будемо використовувати Power Query. Power Query вбудовано в Excel 2016. Якщо ви користуєтесь версією Windows 10 або 13, ви можете звернутися до Microsoft і завантажити Power Query. Ви можете почати з нової порожньої книги з порожнього аркуша. Ви збираєтеся зберегти цей файл - Збережіть як, знаєте, можливо, Робочу книгу, щоб показати результати комбінованих файлів .xlsx. Добре? І що ми будемо робити, це робитимемо два запити. Ми перейдемо до Дані, Отримати дані, З файлу, З робочої книги, а потім виберемо перший файл. У попередньому перегляді виберіть аркуш із вашими даними, і нам не потрібно нічого робити з цими даними. Тож просто відкрийте вікно завантаження та виберіть Завантажити до, Тільки створити підключення, натисніть кнопку ОК. Ідеально Тепер ми повторимо це для другого пункту - Дані з файлу,У робочій книзі виберіть DavidTwo, виберіть назву аркуша, а потім відкрийте завантаження «Завантажити в», «Тільки створити підключення». Ви побачите тут, на цій панелі, у нас є обидва зв’язки. Гаразд
Тепер фактична робота - Дані, Отримати дані, Об’єднати запити, Об’єднати, а потім у діалоговому вікні Об’єднання вибрати DavidOne, DavidTwo, і цей наступний крок абсолютно не інтуїтивний. Ви повинні це зробити. Виберіть спільний стовпець або стовпці - так Продукт і Продукт. Гаразд І тоді, будьте дуже обережні тут із типом з’єднання. Я хочу всі рядки з обох, тому що в одному може бути зайвий рядок, і мені це потрібно побачити, а потім ми натискаємо OK. Гаразд І ось початковий результат. Схоже, це не спрацювало; схоже, він не додав зайві елементи, які були у файлі 2. І у нас є цей стовпець 5 - зараз він нульовий. Я збираюся клацнути правою кнопкою миші стовпець 5 і сказати: Видалити цей стовпець. Тож відкрийте цю піктограму розгортання та зніміть прапорець біля пункту Використовувати оригінальну назву стовпця як префікс та BAM! це працює. Отже, зайві елементи, які були у Файлі 2, а не у Файлі 1,справді з'являються.
Гаразд Тепер у сьогоднішньому файлі схоже, що цей стовпець Коду продукту кращий за цей стовпець Продукту, оскільки він має додаткові рядки. Але в майбутньому може бути день, коли в Workbook 1 є речі, яких у Workbook 2 немає. Тому я збираюся залишити їх обох там, і я не збираюся позбавлятися від будь-яких нулів, оскільки, наприклад, хоча цей рядок внизу здається абсолютно нульовим, у майбутньому може бути ситуація, коли ми маємо тут кілька нулів, бо чогось не вистачає. Добре? Отже, нарешті, Close & Load, і ми маємо свої шістнадцять рядків.
Тепер, у майбутньому, припустимо, що щось змінюється. Гаразд, тому ми повернемось до одного з цих двох файлів, і я зміню клас для Apple на 99, і давайте навіть вставимо щось нове та збережемо цю книгу. Гаразд А потім, якщо ми хочемо, щоб наш файл злиття оновився, заходьте сюди - тепер, будьте уважні, коли ви робите це вперше, ви не бачите піктограму Оновити - вам потрібно взяти цю панель і перетягнути її . І ми зробимо Refresh, і завантажиться 17 рядків, з’явиться кавун, Apple зміниться на 99 - це прекрасна річ. Тепер, привіт, ти хочеш дізнатись про Power Query? Придбайте цю книгу Кена Пулса та Мігеля Ескобара, "М" для (ДАНИХ) МАВПИ. Я підведу вас до швидкості.
Підведення підсумків сьогодні: Девід із Флориди має дві книги, які він хоче об’єднати; вони обидва мають однакові поля в стовпці A, але всі інші стовпці різні; в одній книзі можуть бути зайві предмети, яких немає в іншій, і Девід хоче їх; в жодному файлі немає дублікатів; ми будемо використовувати енергетичний запит, щоб вирішити цю проблему, тому почніть із нової порожньої книги на порожньому аркуші; ви збираєтеся виконати три запити, спочатку один - Дані, З файлу, Книги, а потім Завантажити лише у створене підключення; те саме для другої книги, а потім Дані, Отримати дані, Об’єднати, вибрати два з’єднання, вибрати стовпчик, який є загальним для обох - у моєму випадку, Продукт, - а потім із Типу об’єднання ви хочете повністю приєднатися все з файлу 1, все з файлу 2. І тоді найкраще, якщо основні дані змінюються,Ви можете просто оновити запит.
Щоб завантажити книгу із сьогоднішнього відео, відвідайте URL-адресу в описі YouTube.
Ну, привіт, я хочу, як Девід за те, що я з’явився на мій семінар, я хочу подякувати вам за те, що ви завітали. Побачимось наступного разу для чергової трансляції від.
Завантажте файл Excel
Щоб завантажити файл excel: об'єднайте-на-основі-загальний-column.xlsx
Power Query - це чудовий інструмент у програмі Excel.
Думка дня в Excel
Я попросив своїх друзів Excel Master порадити їх щодо Excel. Сьогоднішня думка поміркувати:
"Завжди натискати F4, коли читаєш діапазон або матрицю у функції"
Таня Кун
