С Power Query работата с данни, разпръснати в работни листове или дори работни книги, стана по -лесна.
Едно от нещата, при които Power Query може да ви спести много време, е, когато трябва да обедините таблици с различни размери и колони въз основа на съответстваща колона.
По -долу е видео, където показвам как точно да обединявате таблици в Excel с помощта на Power Query.
В случай, че предпочитате да четете текста, вместо да гледате видео, по -долу са дадени писмените инструкции.
Да предположим, че имате таблица, както е показано по -долу:
Тази таблица съдържа данните, които искам да използвам, но все още липсват две важни колони - „Идент. № на продукта“ и „Регион“, където работи представителят по продажбите.
Тази информация се предоставя като отделни таблици, както е показано по -долу:
За да получите цялата тази информация в една таблица, ще трябва да обедините тези три таблици, за да можете след това да създадете обобщена таблица и да я анализирате, или да я използвате за други цели за отчитане/табло.
И като обединявам, нямам предвид проста копирна паста.
Ще трябва да съпоставите съответните записи от Таблица 1 с данни от Таблици 2 и 3.
Сега можете да разчитате на VLOOKUP или INDEX/MATCH, за да направите това.
Или ако сте привърженик на VBA, можете да напишете код, за да направите това.
Но тези опции отнемат много време и са сложни в сравнение с Power Query.
В този урок ще ви покажа как да обедините тези три таблици на Excel в една.
За да работи тази техника, трябва да имате свързващи колони. Например в таблица 1 и таблица 2 общата колона е „артикул“, а в таблица 1 и таблица 3 общата колона е „търговски представител“. Също така имайте предвид, че не трябва да има повторение в тези свързващи колони.Забележка: Power Query може да се използва като добавка в Excel 2010 и 2013 и е вградена функция от Excel 2016 нататък. Въз основа на вашата версия някои изображения могат да изглеждат различно (заснемането на изображения, използвано в този урок, е от Excel 2016).
Обединяване на таблици с помощта на Power Query
Нарекох тези таблици, както е показано по -долу:
- Таблица 1 - Sales_Data
- Таблица 2 - Pdt_Id
- Таблица 3 - Регион
Не е задължително да преименувате тези таблици, но е по -добре да дадете имена, които описват какво представлява таблицата.
В един момент можете да обедините само две таблици в Power Query.
Така че първо ще трябва да обединим таблица 1 и таблица 2 и след това да слеем таблица 3 в нея в следващата стъпка.
Обединяване на Таблица 1 и Таблица 2
За да обедините таблици, първо трябва да конвертирате тези таблици в връзки в Power Query. След като имате връзките, можете лесно да ги обедините.
Ето стъпките за запазване на таблица в Excel като връзка в Power Query:
- Изберете всяка клетка в таблицата Sales_Data.
- Щракнете върху раздела Данни.
- В групата Get & Transform кликнете върху „От таблица/диапазон“. Това ще отвори редактора на заявки.
- В редактора на заявки щракнете върху раздела „Файл“.
- Кликнете върху опцията „Затвори и зареди в“.
- В диалоговия прозорец „Импортиране на данни“ изберете „Само създаване на връзка“.
- Щракнете върху OK.
Горните стъпки биха създали връзка с името Sales_Data (или всяко име, което сте дали на таблицата на Excel).
Повторете горните стъпки за Таблица 2 и Таблица 3.
Така че, когато приключите, ще имате три връзки (с името Sales_Data, Pdt_Id и Region).
Сега нека видим как да обединим таблицата Sales_Data и Pdt_Id.
- Щракнете върху раздела Данни.
- В групата Получаване и преобразуване на данни щракнете върху Получаване на данни.
- В падащото меню щракнете върху Комбиниране на заявки.
- Щракнете върху Обединяване. Това ще отвори диалоговия прозорец Обединяване.
- В диалоговия прозорец Обединяване изберете „Sales_Data“ от първото падащо меню.
- Изберете „Pdt_Id“ от второто падащо меню.
- В визуализацията „Sales_Data“ кликнете върху колоната „Item“. Това ще избере цялата колона.
- В визуализацията „Pdt_Id“ кликнете върху колоната „Елемент“. Това ще избере цялата колона.
- В падащото меню „Join Kind“ изберете „Left Outer (all from first, matching from second)“.
- Щракнете върху OK.
Горните стъпки ще отворят редактора на заявки и ще ви покажат данните от Sales_Data с една допълнителна колона (на Pdt_Id).
Обединяване на таблиците на Excel (таблици 1 и 2)
Сега процесът на обединяване на таблиците ще се случи в редактора на заявки със следните стъпки:
- В допълнителната колона (Pdt_Id) щракнете върху двойната стрелка в заглавката.
- От отвореното поле с опции махнете отметката от всички имена на колони и изберете само Item. Това е така, защото вече имаме колоната с името на продукта в съществуващата таблица и искаме само идентификатора на продукта за всеки продукт.
- Премахнете отметката от опцията „Използване на оригиналното име на колона като префикс“.
- Щракнете върху OK.
Това ще ви даде получената таблица, която съдържа всеки запис от таблицата Sales_Data и допълнителна колона, която също има идентификатори на продукти (от таблицата Pdt_Id).
Сега, ако искате да комбинирате само две таблици, можете да заредите този Excel, сте готови.
Но имаме три таблици за обединяване, така че има още работа за вършене.
Трябва да запазите тази получена таблица като връзка (за да можем да я използваме, за да я обединим с Таблица 3).
Ето стъпките за запазване на тази обединена таблица (с данни от таблиците Sales_Data и Pdt_Id) като връзка:
- Щракнете върху раздела Файл
- Кликнете върху опцията „Затвори и зареди в“.
- В диалоговия прозорец „Импортиране на данни“ изберете „Само създаване на връзка“.
- Щракнете върху OK.
Това ще запази новосъединените данни като връзка. Можете да преименувате тази връзка, ако искате.
Обединяване на таблица 3 с получената таблица
Процесът на обединяване на третата таблица с получената таблица (който получихме чрез обединяване на таблица 1 и таблица 2) е абсолютно същият.
Ето стъпките за обединяване на тези таблици:
- Щракнете върху раздела Данни.
- В групата Получаване и преобразуване на данни кликнете върху „Получаване на данни“.
- В падащото меню щракнете върху „Комбиниране на заявки.
- Кликнете върху „Обединяване“. Това ще отвори диалоговия прозорец Обединяване.
- В диалоговия прозорец Обединяване изберете „Обединяване1“ от първото падащо меню.
- Изберете „Регион“ от второто падащо меню.
- В визуализацията „Merge1“ кликнете върху колоната „Sales Rep“. Това ще избере цялата колона.
- В визуализацията на региона щракнете върху колоната „Представител на продажбите“. Това ще избере цялата колона.
- В падащото меню „Join Kind“ изберете Left Outer (всичко от първо, съвпадение от второ).
- Щракнете върху OK.
Горните стъпки ще отворят редактора на заявки и ще ви покажат данните от Merge1 с една допълнителна колона (регион).
Сега процесът на обединяване на таблиците ще се случи в редактора на заявки със следните стъпки:
- В допълнителната колона (Регион) щракнете върху двойната стрелка в заглавката.
- От отвореното поле с опции премахнете отметката от всички имена на колони и изберете само Регион.
- Премахнете отметката от опцията „Използване на оригиналното име на колона като префикс“.
- Щракнете върху OK.
Горните стъпки ще ви дадат таблица, в която са обединени и трите таблици (таблица Sales_Data с една колона за Pdt_Id и една за регион).
Ето стъпките за зареждане на тази таблица в Excel:
- Щракнете върху раздела Файл.
- Кликнете върху „Затвори и зареди в“.
- В диалоговия прозорец „Импортиране на данни“ изберете опциите за таблица и нови работни листове.
- Щракнете върху OK.
Това ще ви даде получената обединена таблица в нов работен лист.
Едно от най -добрите неща за Power Query е, че можете лесно да приспособите всякакви промени в основните данни (Таблица 1, 2 и 3), като просто ги опресните.
Да предположим например, че Лора ще бъде прехвърлена в Азия и ще получите нови данни за следващия месец. Сега не е нужно да повтаряте горните стъпки отново. Всичко, което трябва да направите, е да опресните таблицата и тя ще направи всичко отначало вместо вас.
В рамките на секунди ще имате новата обединена таблица.
Може да харесате и следните уроци по Power Query:
- Комбинирайте данни от множество работни книги в Excel (използвайки Power Query).
- Комбинирайте данни от множество работни листове в един работен лист в Excel.
- Как да разгънете данни в Excel с помощта на Power Query (известен още като Get & Transform)
- Вземете списък с имена на файлове от папки и подпапки (използвайки Power Query)
