Дейвид от Флорида задава днешния въпрос:
Имам две работни книги. И двете имат едни и същи данни в колона А, но останалите колони са различни. Как мога да обединя тези две работни книги?
Попитах Дейвид дали е възможно едната работна книга да има повече записи от другата. И отговорът е да. Попитах Дейвид дали ключовото поле се появява само веднъж във всеки файл. Отговорът също е да. Днес ще реша това с Power Query. Инструментите на Power Query се намират във версиите на Excel 2016+ за Windows в раздела Получаване и трансформиране на раздела Данни. Ако имате Windows версии на Excel 2010 или Excel 2013, можете да изтеглите добавката Power Query за тези версии.
Ето работната книга на Дейвид 1. В нея има Product и след това три колони с данни.
Ето работната книга на Дейвид 2. В нея има продуктов код, а след това и други колони. В този пример има допълнителни продукти в workbook2, но решенията ще работят, ако някоя от тях има допълнителни колони.
Ето стъпките:
-
Изберете Данни, Получаване на данни, От файл, От работна книга:
Заредете данни от файл - Прегледайте първата работна книга и щракнете върху OK
- В диалоговия прозорец Навигатор изберете работния лист вляво. (Дори ако има само един работен лист, трябва да го изберете.) Ще видите данните вдясно.
- В диалоговия прозорец Навигатор отворете падащото меню Зареждане и изберете Зареждане в …
- Изберете Само Създаване на връзка и натиснете OK.
-
Повторете стъпки 1-5 за втората работна книга.
Създайте връзка с работната книга Ако сте направили и двете работни книги, трябва да видите две връзки в панела Заявки и връзки вдясно на екрана на Excel.
Връзки с двете работни книги Продължете със стъпките за обединяване на работните книги:
-
Данни, Получаване на данни, Комбиниране на заявки, Обединяване.
Обединете две заявки с различни колони - От падащото меню отгоре в диалоговия прозорец Обединяване изберете първата заявка.
- От второто падащо меню в диалоговия прозорец Обединяване изберете втората заявка.
- Щракнете върху заглавието на продукта в горния преглед (това е ключовото поле. Забележете, че можете да изберете няколко или повече ключови полета чрез Ctrl + Clicking)
- Кликнете върху заглавието на кода на продукта във втория преглед.
-
Отворете Тип на присъединяване и изберете Пълна външна (Всички редове и от двете)
Стъпки 8 - 12 илюстрирани тук -
Щракнете върху OK. Прегледът на данните не показва излишните редове и показва „Таблица“ многократно в последната колона.
Това не изглежда обещаващо - Забележете, че в заглавието на DavidTwo има икона „Разгъване“. Щракнете върху тази икона.
-
По избор, но винаги премахвам отметката от „Използвай оригинално име на колона като префикс“. Щракнете върху OK.
Разширете полетата от работната книга 2 Резултатите са показани в този преглед:
Всички записи от която и да е работна книга - В Power Query използвайте Начало, затваряне и зареждане.
Тук е красивата функция: ако основните данни в която и да е работна книга се променят, можете да щракнете върху иконата Обновяване, за да изтеглите нови данни в работната книга с резултати.
Забележка
Иконата за Refresh обикновено е скрита. Плъзнете левия ръб на екрана Queries & Connections вляво, за да разкриете иконата.
Гледам видео
Видео транскрипция
Научете Excel от Podcast, епизод 2216: Комбинирайте две работни книги, базирани на обща колона.
Хей, добре дошли отново в netcast, аз съм Бил Джелен. Днешният въпрос е на Дейвид, който беше на семинара ми в Мелбърн, Флорида, за Главата за космическото крайбрежие на IIA.
Дейвид има две различни работни книги, където колона А е обща между двамата. И така, тук е Workbook 1, тук е Workbook 2 - и двата имат продуктов код. Този има елементи, които първият няма, или обратно, и Дейвид иска да комбинира всички колони. И така, имаме три колони тук и четири колони тук. Поставих и двете в една и съща работна книга, в случай че изтегляте работната книга, за да работите. Вземете всеки един от тях, преместете го в собствената си работна книга и го запазете.
Добре, за да комбинираме тези файлове, ще използваме Power Query. Power Query е вграден в Excel 2016. Ако сте в Windows версия 10 или 13, можете да отидете до Microsoft и да изтеглите Power Query. Можете да започнете от нова празна работна книга с празен работен лист. Ще запишете този файл - Запазете като, знаете ли, може би Работна книга, за да покажете резултатите от комбинираните файлове .xlsx. Добре? И това, което ще направим, е, че ще направим две заявки. Ще отидем на Данни, Получаване на данни, От файл, От работна книга и след това ще изберем първия файл. В предварителен преглед изберете листа с вашите данни и ние не трябва да правим нищо за тези данни. Така че просто отворете полето за зареждане и изберете Load To, Only Create Connection, щракнете върху OK. Перфектно. Сега ще повторим това за втория елемент - данни, от файл,От работна книга изберете DavidTwo, изберете името на листа и след това отворете товара, Load To, Only Create a Connection. Ще видите тук в този панел, имаме и двете връзки. Добре.
Сега действителната работа - Data, Get Data, Combine Queries, Merge и след това в диалоговия прозорец Merge изберете DavidOne, DavidTwo и тази следваща стъпка е напълно неинтуитивна. Трябва да направите това. Изберете общата колона или колони - така че продукт и продукт. Добре. И тогава, бъдете много внимателни тук с типа присъединяване. Искам всички редове и от двата, защото единият може да има допълнителен ред и трябва да го видя, след което щракваме върху OK. Добре. И ето първоначалния резултат. Не изглежда, че е работило; не изглежда, че е добавил допълнителните елементи, които са били във файл 2. И имаме тази колона 5 - сега е нула. Щраквам с десния бутон върху колона 5 и казвам: Премахване на тази колона. Така че отворете тази икона за разгъване и махнете отметката от това поле за Използване на оригинално име на колона като префикс и BAM! работи. Така че допълнителните елементи, които са били във файл 2, които не са във файл 1,се появяват.
Добре. Сега в днешния файл изглежда, че тази колона Product Code е по-добра от тази колона Product, тъй като има допълнителни редове. Но може да има ден в бъдещето, в който Workbook 1 да има неща, които Workbook 2 няма. Така че ще оставя и двамата там и няма да се отърва от всякакви нули, тъй като, макар че този ред в долната част изглежда напълно нулев, в бъдеще може да има ситуация, в която имаме няколко нули тук, защото нещо липсва. Добре? И така, накрая Close & Load и имаме нашите шестнадесет реда.
Сега, в бъдеще, да кажем, че нещо се променя. Добре, така че ще се върнем към един от тези два файла и ще сменя класа на Apple на 99 и нека дори да вмъкнем нещо ново и да запазим тази работна книга. Добре. И тогава, ако искаме файлът ни за сливане да се актуализира, елате тук - сега, внимавайте, когато правите това за първи път, не можете да видите иконата за опресняване - трябва да вземете тази лента и да я плъзнете . И ще направим Refresh, и 17 реда се зареждат, динята се появява, Apple се променя на 99 - това е красиво нещо. Сега, хей, искаш ли да научиш за Power Query? Купете тази книга от Кен Пулс и Мигел Ескобар, М е за (ДАННИ) МАЙМУНА. Ще ви ускоря.
Приключване днес: Дейвид от Флорида има две работни книги, които иска да комбинира; и двете имат едни и същи полета в колона А, но останалите колони са различни; едната работна книга може да има допълнителни елементи, които не са в другата и Дейвид ги иска; няма дубликати в нито един файл; ще използваме заявка за захранване, за да разрешим това, така че започнете в нова празна работна книга на празен работен лист; ще направите три запитвания, първо едно - Данни, От файл, Работна книга и след това Зареждане до само създадена връзка; същото нещо за втората работна книга и след това Data, Get Data, Merge, изберете двете връзки, изберете колоната, която е обща и в двете - в моя случай, Product - и след това от Type Type, искате да се присъедините напълно всички от Файл 1, всички от Файл 2. И тогава красивото е, ако основните данни се променят,можете просто да опресните заявката.
За да изтеглите работната книга от днешното видео, посетете URL адреса в описанието на YouTube.
Е, хей, искам като Дейвид да се яви на семинара ми, искам да ти благодаря, че се отби. Ще се видим следващия път за поредното излъчване от.
Изтеглете Excel файла
За да изтеглите файла на Excel: комбинирайте-базиран-на-обща-колона.xlsx
Power Query е невероятен инструмент в Excel.
Мисъл на деня в Excel
Помолих приятелите си в Excel Master за съвети относно Excel. Днешната мисъл за размисъл:
„Винаги натискайте F4, когато четете диапазон или матрица във функция“
Таня Кун
