✅ Об'єднайте дані з кількох робочих аркушів в єдиний робочий аркуш у Excel

Зміст

Об’єднайте дані з кількох робочих аркушів за допомогою Power Query

Нещодавно я отримав запитання від читача щодо об’єднання кількох аркушів в одній книзі в один аркуш.

Я попросив його використовувати Power Query для об’єднання різних аркушів, але потім я зрозумів, що для когось нового в Power Query зробити це може бути важко.

Тому я вирішив написати цей підручник і показати точні кроки об’єднання кількох аркушів в одну таблицю за допомогою Power Query.

Нижче відео, де я показую, як об’єднати дані з кількох аркушів/таблиць за допомогою Power Query:

Нижче наведені письмові вказівки щодо того, як об’єднати кілька аркушів (якщо ви віддаєте перевагу письмовому тексту над відео).

Примітка: Power Query можна використовувати як надбудову в Excel 2010 та 2013, і це вбудована функція з Excel 2016 і далі. Виходячи з вашої версії, деякі зображення можуть виглядати інакше (знімки зображень, використані в цьому посібнику, є з Excel 2016).

Об’єднайте дані з кількох робочих аркушів за допомогою Power Query

При об'єднанні даних з різних аркушів за допомогою Power Query дані потрібно мати у таблиці Excel (або принаймні в іменованих діапазонах). Якщо дані відсутні в таблиці Excel, метод, показаний тут, не працюватиме.

Припустимо, у вас є чотири різні аркуші - східний, західний, північний та південний.

Кожен з цих аркушів містить дані в таблиці Excel, а структура таблиці є послідовною (тобто заголовки однакові).

Натисніть тут, щоб завантажити дані та продовжити.

Такі дані надзвичайно легко об’єднати за допомогою Power Query (який дуже добре працює з даними в таблиці Excel).

Щоб ця техніка працювала найкраще, краще мати назви для таблиць Excel (працювати і без неї, але її легше використовувати, коли таблиці називаються).

Я дав таблицям такі назви: East_Data, West_Data, North_Data та South_Data.

Ось кроки для об’єднання кількох аркушів із таблицями Excel за допомогою Power Query:

Перейдіть на вкладку Дані.
У групі Отримати та перетворити дані натисніть опцію "Отримати дані".
Перейдіть до опції «З інших джерел».
Натисніть на опцію "Пустий запит". Відкриється редактор Power Query.
У редакторі запитів введіть таку формулу у рядку формул: = Excel.Поточна робоча книга(). Зверніть увагу, що формули Power Query чутливі до регістру, тому вам потрібно використовувати точну формулу, як зазначено вище (інакше ви отримаєте помилку).
Натисніть клавішу Enter. Це покаже вам усі назви таблиць у всій книзі (він також покаже вам іменовані діапазони та/або зв’язки, якщо вони є у книзі).
[Необов’язковий крок] У цьому прикладі я хочу об’єднати всі таблиці. Якщо ви хочете об’єднати лише певні таблиці Excel, ви можете натиснути спадне меню у заголовку імені та вибрати ті, які потрібно об’єднати. Аналогічно, якщо у вас є іменовані діапазони або з'єднання, і ви хочете лише об'єднати таблиці, ви також можете видалити ці іменовані діапазони.
У клітинці заголовка вмісту натисніть на подвійну стрілку.
Виберіть стовпці, які потрібно об’єднати. Якщо ви хочете об’єднати всі стовпці, переконайтеся, що (Вибрати всі стовпці) встановлено прапорець.
Зніміть прапорець "Використовувати оригінальну назву стовпця як префікс".
Натисніть OK.

Наведені вище кроки об’єднали б дані з усіх аркушів в одну єдину таблицю.

Якщо ви уважно подивіться, ви побачите, що останній стовпець (крайній правий) містить назву таблиць Excel (East_Data, West_Data, North_Data та South_Data). Це ідентифікатор, який вказує, який запис надходив з якої таблиці Excel. Це також причина, чому я сказав, що краще мати описові назви для таблиць Excel.

Нижче наведено кілька змін, які ви можете внести до об’єднаних даних у самому Power Query:

Перетягніть стовпець Ім'я до початку.
Видаліть "_Data" зі стовпця імені (так що у стовпці з іменами залишиться Схід, Захід, Північ та Південь). Для цього клацніть правою кнопкою миші на заголовку Ім'я та клацніть Замінити значення. У діалоговому вікні «Замінити значення» замініть _Data порожнім.
Змініть стовпець Дані, щоб відображати лише дати (а не час). Для цього натисніть заголовок стовпця Дата, перейдіть на вкладку «Перетворення» та змініть тип даних на Дата.
Перейменуйте запит на ConsolidatedData.

Тепер, коли у вас є об’єднані дані з усіх аркушів у Power Query, ви можете завантажити їх у Excel - як нову таблицю на новому аркуші.

Зробити це. виконайте наступні кроки:

Перейдіть на вкладку «Файл».
Натисніть кнопку Закрити та завантажити.
У діалоговому вікні Імпортувати дані виберіть Параметри таблиці та Нового аркуша.
Натисніть ОК.

Наведені вище кроки об’єднали б дані з усіх аркушів і дали б ці об’єднані дані в новому аркуші.

При використанні цього методу потрібно вирішити одну проблему

Якщо ви використали вищезгаданий метод для об’єднання всіх таблиць у книзі, ймовірно, ви зіткнетеся з проблемою.

Подивіться кількість рядків об’єднаних даних - 1304 (це правильно).

Тепер, якщо я оновлюю запит, кількість рядків змінюється на 2607. Оновіть знову, і він зміниться на 3910.

Ось у чому проблема.

Щоразу, коли ви оновлюєте запит, він додає всі записи в вихідних даних до об’єднаних даних.

Примітка: Ви зіткнетеся з цією проблемою, лише якщо для комбінування ви використовували Power Query ВСІ ТАБЛИЦІ EXCEL у трудовій книжці. Якщо ви вибрали конкретні таблиці для об’єднання, ви не зіткнетеся з цією проблемою.

Давайте розберемося в причині цієї проблеми і як це виправити.

Коли ви оновлюєте запит, він повертається назад і виконує всі кроки, які ми зробили для об’єднання даних.

На кроці, де ми використовували формулу = Excel.CurrentWorkbook (), він дав нам список усіх таблиць. Перший раз це спрацювало чудово, оскільки було всього чотири столи.

Але коли ви оновлюєте, у книзі є п’ять таблиць, включаючи нову таблицю, яку Power Query вставляв туди, де ми маємо об’єднані дані.

Тож кожного разу, коли ви оновлюєте запит, окрім чотирьох таблиць Excel, які ми хочемо об’єднати, він також додає наявну таблицю запитів до отриманих даних.

Це називається рекурсією.

Ось як вирішити це питання.

Після того, як ви вставите = Excel.CurrentWorkbook () у рядок формул Power Query і натиснете Enter, ви отримаєте список таблиць Excel. Щоб переконатися, що вам вдасться лише об’єднати таблиці з аркуша, вам потрібно якимось чином відфільтрувати лише ці таблиці, які потрібно об’єднати, і видалити все інше.

Ось кроки, щоб переконатися, що у вас є лише необхідні таблиці:

Натисніть спадне меню і наведіть курсор на Текстові фільтри.
Натисніть на опцію Містить.
У діалоговому вікні Фільтрування рядків введіть _Data у поле поруч із опцією "містить".
Натисніть OK.

Можливо, ви не побачите жодних змін у даних, але це запобіжить повторному додаванню отриманої таблиці під час оновлення запиту.

Зауважте, що у вищезгаданих кроках ми використовували "_Дані”, Щоб відфільтрувати так, як ми називали таблиці таким чином. Але що робити, якщо ваші таблиці не називаються послідовно. Що робити, якщо всі назви таблиць випадкові і не мають нічого спільного.

Ось спосіб вирішити це - скористайтеся фільтром "не дорівнює" та введіть ім'я запиту (це буде ConsolidatedData у нашому прикладі). Це гарантуватиме, що все залишиться незмінним, а отримана таблиця запитів, яка буде створена, відфільтрована.

Крім того, що Power Query робить весь процес об’єднання даних з різних аркушів (або навіть з одного аркуша) досить простим, ще одна перевага його використання - це робить його динамічним. Якщо ви додасте більше записів до будь -якої таблиці та оновите Power Query, вона автоматично надасть вам об’єднані дані.

Важлива примітка: У прикладі, використаному в цьому посібнику, заголовки були однаковими. Якщо заголовки різні, Power Query об’єднає та створить усі стовпці в новій таблиці. Якщо дані доступні для цього стовпця, вони відображатимуться, інакше - нульовими.

Вам також можуть сподобатися такі підручники Power Query: