Наскоро получих въпрос от читател относно комбинирането на няколко работни листа в една и съща работна книга в един работен лист.
Помолих го да използва 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 Table).
За да работи тази техника най -добре, е по -добре да имате имена за вашите Excel таблици (работете и без нея, но е по -лесно да използвате, когато таблиците са кръстени).
Дадох на таблиците следните имена: East_Data, West_Data, North_Data и South_Data.
Ето стъпките за комбиниране на няколко работни листа с таблици на Excel с помощта на Power Query:
- Отидете в раздела Данни.
- В групата Получаване и преобразуване на данни кликнете върху опцията „Получаване на данни“.
- Отидете на опцията „От други източници“.
- Щракнете върху опцията „Празна заявка“. Това ще отвори редактора на Power Query.
- В редактора на заявки въведете следната формула в лентата с формули: = Excel.CurrentWorkbook(). Имайте предвид, че формулите на Power Query са чувствителни към регистъра, така че трябва да използвате точната формула, както е споменато (иначе ще получите грешка).
- Натиснете клавиша Enter. Това ще ви покаже всички имена на таблици в цялата работна книга (ще ви покаже и наименуваните диапазони и/или връзки, в случай че съществува в работната книга).
- [Незадължителна стъпка] В този пример искам да комбинирам всички таблици. Ако искате да комбинирате само конкретни таблици на Excel, можете да щракнете върху падащата икона в заглавката на името и да изберете тези, които искате да комбинирате. По същия начин, ако сте посочили диапазони или връзки и искате да комбинирате само таблици, можете също да премахнете тези именовани диапазони.
- В клетката на заглавната част на съдържанието щракнете върху стрелката с двойна посока.
- Изберете колоните, които искате да комбинирате. Ако искате да комбинирате всички колони, уверете се, че (Select All Columns) е поставено отметка.
- Премахнете отметката от опцията „Използване на оригиналното име на колона като префикс“.
- Щракнете върху OK.
Горните стъпки ще обединят данните от всички работни листове в една единствена таблица.
Ако се вгледате внимателно, ще откриете, че последната колона (най -дясната) има името на таблиците на Excel (East_Data, West_Data, North_Data и South_Data). Това е идентификатор, който ни казва кой запис идва от коя таблица на Excel. Това е и причината да кажа, че е по -добре да има описателни имена за таблиците на Excel.
Ето няколко модификации, които можете да направите на комбинираните данни в самата Power Query:
- Плъзнете и поставете колоната Име в началото.
- Премахнете „_Data“ от колоната с имена (така че оставате с Изток, Запад, Север и Юг в колоната с имена). За да направите това, щракнете с десния бутон върху заглавката Name и щракнете върху Replace Values. В диалоговия прозорец Замяна на стойности заменете _Data с празно място.
- Променете колоната Данни, за да се показват само дати (а не час). За да направите това, щракнете върху заглавката на колоната Дата, отидете в раздела „Трансформиране“ и променете типа данни на Дата.
- Преименувайте заявката на ConsolidatedData.
Сега, когато имате комбинираните данни от всички работни листове в Power Query, можете да ги заредите в Excel - като нова таблица в нов работен лист.
Да го направя. следвайте стъпките по -долу:
- Щракнете върху раздела „Файл“.
- Щракнете върху Затвори и заредете в.
- В диалоговия прозорец Импортиране на данни изберете опции за таблица и нов работен лист.
- Щракнете върху OK.
Горните стъпки ще комбинират данни от всички работни листове и ще ви дадат тези комбинирани данни в нов работен лист.
Един проблем, който трябва да разрешите, когато използвате този метод
В случай, че сте използвали горния метод за комбиниране на всички таблици в работната книга, вероятно ще се сблъскате с проблем.
Вижте броя редове на комбинираните данни - 1304 (което е правилно).
Сега, ако опресня заявката, броят на редовете се променя на 2607. Опреснете отново и тя ще се промени на 3910.
Тук е проблемът.
Всеки път, когато опреснявате заявката, тя добавя всички записи в оригиналните данни към комбинираните данни.
Забележка: Ще се сблъскате с този проблем само ако сте използвали Power Query за комбиниране ВСИЧКИ ТАБЛИЦИ EXCEL в работната тетрадка. В случай, че сте избрали конкретни таблици за комбиниране, няма да се сблъскате с този проблем.Нека разберем причината за този проблем и как да го отстраним.
Когато опреснявате заявка, тя се връща назад и следва всички стъпки, които предприехме, за да комбинираме данните.
В стъпката, където използвахме формулата = Excel.CurrentWorkbook (), той ни даде списък с всички таблици. Това работи добре за първи път, тъй като имаше само четири маси.
Но когато опреснявате, има пет таблици в работната книга - включително новата таблица, която Power Query вмъкна, където имаме комбинираните данни.
Така всеки път, когато опреснявате заявката, освен четирите таблици на Excel, които искаме да комбинираме, тя добавя и съществуващата таблица с заявки към получените данни.
Това се нарича рекурсия.
Ето как да решите този проблем.
След като вмъкнете = Excel.CurrentWorkbook () в лентата с формули на Power Query и натиснете enter, получавате списък с таблици на Excel. За да сте сигурни, че можете да комбинирате само таблиците от работния лист, трябва по някакъв начин да филтрирате само тези таблици, които искате да комбинирате, и да премахнете всичко останало.
Ето стъпките, за да сте сигурни, че имате само необходимите таблици:
- Щракнете върху падащото меню и задръжте курсора на мишката върху Текстови филтри.
- Щракнете върху опцията Съдържа.
- В диалоговия прозорец Filter Rows въведете _Data в полето до опцията ‘contains’.
- Щракнете върху OK.
Възможно е да не видите никаква промяна в данните, но това ще предотврати повторното добавяне на получената таблица при опресняване на заявката.
Обърнете внимание, че в горните стъпки сме използвали „_Данни”, За да филтрирате, тъй като по този начин назовахме таблици. Но какво ще стане, ако вашите таблици не са именувани последователно. Ами ако всички имена на таблици са произволни и нямат нищо общо.
Ето начина да разрешите това - използвайте филтъра „не е равно“ и въведете името на заявката (което би било ConsolidatedData в нашия пример). Това ще гарантира, че всичко остава същото и получената таблица с заявки, която е създадена, се филтрира.
Освен факта, че Power Query прави целия процес на комбиниране на данни от различни листове (или дори от един и същ лист) доста лесен, още едно предимство от използването му го прави динамичен. Ако добавите още записи към някоя от таблиците и опресните Power Query, тя автоматично ще ви даде комбинираните данни.Важна забележка: В примера, използван в този урок, заглавките бяха еднакви. В случай, че заглавките са различни, Power Query ще комбинира и създаде всички колони в новата таблица. Ако данните са налични за тази колона, те ще бъдат показани, в противен случай ще покажат нула.
Може да харесате и следните уроци по Power Query:
- Комбинирайте данни от множество работни книги в Excel (използвайки Power Query).
- Как да разгънете данни в Excel с помощта на Power Query (известен още като Get & Transform)
- Вземете списък с имена на файлове от папки и подпапки (използвайки Power Query)
- Обединяване на таблици в Excel с помощта на Power Query.
- Как да сравним два листа/файлове на Excel
