Deivids no Floridas uzdod šodienas jautājumu:
Man ir divas darba burtnīcas. Abiem ir vienādi dati A slejā, bet pārējās kolonnas ir atšķirīgas. Kā es varu apvienot šīs divas darbgrāmatas?
Es jautāju Deividam, vai ir iespējams, ka vienā darbgrāmatā ir vairāk ierakstu nekā otrā. Un atbilde ir Jā. Es jautāju Deividam, vai atslēgas lauks katrā failā parādās tikai vienu reizi. Atbilde ir arī jā. Šodien es to atrisināšu ar Power Query. Power Query rīki ir atrodami Windows 2016+ versijās Windows cilnes Dati sadaļā Iegūt un pārveidot. Ja jums ir Excel 2010 vai Excel 2013 Windows versijas, varat lejupielādēt Power Query pievienojumprogrammu šīm versijām.
Šeit ir Dāvida 1. darba grāmata. Tajā ir produkts un pēc tam trīs datu kolonnas.
Šeit ir Dāvida 2. darba grāmata. Tajā ir produkta kods un pēc tam citas slejas. Šajā piemērā darbgrāmatā2 ir papildu produkti, taču risinājumi darbosies, ja kādā no darbgrāmatām būs papildu slejas.
Tālāk ir norādītas darbības.
-
Atlasiet Dati, Iegūt datus, no faila, no darbgrāmatas:
Ielādējiet datus no faila - Pārlūkojiet līdz pirmajai darbgrāmatai un noklikšķiniet uz Labi
- Dialoglodziņā Navigator izvēlieties darblapu kreisajā pusē. (Pat ja ir tikai viena darblapa, tā ir jāizvēlas.) Datus redzēsiet labajā pusē.
- Dialoglodziņā Navigator atveriet nolaižamo izvēlni Load un izvēlieties Load To…
- Izvēlieties Tikai izveidot savienojumu un nospiediet Labi.
-
Atkārtojiet 1.-5. Darbību otrajai darbgrāmatai.
Izveidojiet savienojumu ar darbgrāmatu Ja esat veicis abas darbgrāmatas, Excel ekrāna labajā pusē panelī Vaicājumi un savienojumi vajadzētu redzēt divus savienojumus.
Savienojumi ar abām darbgrāmatām Turpiniet veikt darbgrāmatu apvienošanas darbības:
-
Dati, Iegūt datus, Apvienot vaicājumus, Apvienot.
Apvienojiet divus vaicājumus ar dažādām kolonnām - Dialogloga Apvienot augšējā nolaižamajā izvēlnē izvēlieties pirmo vaicājumu.
- Dialogloga Apvienot otrajā nolaižamajā izvēlnē izvēlieties otro vaicājumu.
- Augšējā priekšskatījumā noklikšķiniet uz virsraksta Produkts (tas ir atslēgas lauks. Ņemiet vērā, ka varat atlasīt divus vai vairākus taustiņu laukus, nospiežot Ctrl + noklikšķinot)
- Otrajā priekšskatījumā noklikšķiniet uz virsraksta Produkta kods.
-
Atveriet Join Type un izvēlieties Full Outer (visas rindas no abām)
Šeit ilustrēti 8. līdz 12. solis -
Noklikšķiniet uz Labi. Datu priekšskatījumā papildu rindas netiek rādītas, un tikai pēdējā tabulā atkārtoti tiek rādīta tabula.
Tas nešķiet daudzsološi - Ievērojiet, ka DavidTwo virsrakstā ir ikona "Izvērst". Noklikšķiniet uz šīs ikonas.
-
Neobligāti, bet es vienmēr noņemu izvēli "Izmantot sākotnējo kolonnas nosaukumu kā prefiksu". Noklikšķiniet uz Labi.
Izvērsiet 2. darbgrāmatas laukus Rezultāti tiek parādīti šajā priekšskatījumā:
Visi ieraksti no jebkuras darbgrāmatas - Programmā Power Query izmantojiet Sākums, Aizvērt un ielādēt.
Šeit ir skaista funkcija: ja mainās pamatdati abās darbgrāmatās, varat noklikšķināt uz ikonas Atsvaidzināt, lai rezultātu darbgrāmatā ievilktu jaunus datus.
Piezīme
Ikona Atjaunot parasti tiek paslēpta. Velciet rūts Vaicājumi un savienojumi kreiso malu pa kreisi, lai atklātu ikonu.
Skatīties video
Video atšifrējums
Uzziniet programmu Excel no Podcast, 2216. sērija: apvienojiet divas darbgrāmatas, pamatojoties uz kopīgu kolonnu.
Sveiki, laipni lūgti atgriezties netcast ēterā, es esmu Bils Jelens. Šodienas jautājums ir no Dāvida, kurš piedalījās manā seminārā MII kosmosa krasta nodaļā Melburnā, Floridā.
Deividam ir divas dažādas darba burtnīcas, kur A sleja ir kopīga starp abām. Tātad, šeit ir 1. darbgrāmata, šeit ir 2. darbgrāmata - abiem ir produkta kods. Šajā ir vienumi, kuru pirmajā nav, vai otrādi, un Deivids vēlas apvienot visas kolonnas. Tātad mums ir trīs kolonnas šeit un četras kolonnas šeit. Es ievietoju abus vienā darbgrāmatā, ja jūs lejupielādējat darbgrāmatu, lai tā darbotos. Paņemiet katru no šiem, pārvietojiet to uz savu darbgrāmatu un saglabājiet to.
Labi, lai apvienotu šos failus, mēs izmantosim Power Query. Power Query ir iebūvēts programmā Excel 2016. Ja izmantojat Windows versiju 10 vai 13, varat doties uz Microsoft un lejupielādēt Power Query. Jūs varat sākt no jaunas tukšas darbgrāmatas ar tukšu darblapu. Jūs saglabāsit šo failu - saglabājiet kā, jūs zināt, varbūt darbgrāmatu, lai parādītu apvienoto failu .xlsx rezultātus. Labi? Un tas, ko mēs darīsim, ir divi jautājumi. Mēs ejam uz Dati, Iegūt datus, No faila, No darbgrāmatas un pēc tam izvēlēsimies pirmo failu. Priekšskatījumā atlasiet lapu, kurā ir jūsu dati, un mums šiem datiem nekas nav jādara. Tāpēc vienkārši atveriet ielādes lodziņu un izvēlieties Ielādēt, izveidot tikai savienojumu, noklikšķiniet uz Labi. Lieliski. Tagad mēs atkārtosim to otrajam vienumam - dati, no faila,Darbgrāmatā izvēlieties DavidTwo, izvēlieties lapas nosaukumu un pēc tam atveriet ielādi, ielādēt, izveidot tikai savienojumu. Jūs redzēsiet šeit, šajā panelī, mums ir abi savienojumi. Labi.
Tagad faktiskais darbs - Dati, Iegūt datus, Apvienot vaicājumus, Apvienot un pēc tam dialoglodziņā Apvienot izvēlieties DavidOne, DavidTwo, un šis nākamais solis nav pilnīgi saprotams. Jums tas jādara. Izvēlieties kopīgo kolonnu vai kolonnas - tātad Produkts un Produkts. Labi. Un tad esiet šeit ļoti piesardzīgs ar savienojuma veidu. Es gribu visas rindas no abiem, jo vienā varētu būt papildu rinda, un man tas ir jāredz, un tad mēs noklikšķiniet uz Labi. Labi. Un šeit ir sākotnējais rezultāts. Neizskatās, ka tas darbojās; neizskatās, ka tas būtu pievienojis papildu vienumus, kas bija failā 2. Un mums ir šī 5. sleja - tagad tā ir nulle. Es ar peles labo pogu noklikšķiniet uz 5. kolonnas un saku: Noņemt šo kolonnu. Tāpēc atveriet šo izvēršanas ikonu un noņemiet atzīmi no izvēles rūtiņas Izmantot sākotnējo kolonnas nosaukumu kā prefiksu un BAM! tas strādā. Tātad papildu vienumi, kas atradās 2. failā un kas nav 1. failā,tiešām parādās.
Labi. Tagad šodienas failā izskatās, ka šī sleja Produkta kods ir labāka par šo sleju Produkts, jo tai ir papildu rindas. Bet nākotnē varētu būt diena, kad 1. darbgrāmatā ir lietas, kuru nav 2. darbgrāmatā. Tāpēc es atstāšu abus tur, un es netaisos atbrīvoties no jebkādiem nulles punktiem, jo, piemēram, lai arī šī rinda apakšā šķiet pilnīgi nulle, nākotnē varētu būt situācija, kad mums šeit ir dažas nulles, jo kaut kā trūkst. Labi? Tātad, visbeidzot, Aizvērt un ielādēt, un mums ir sešpadsmit rindas.
Tagad, pieņemsim, ka kaut kas mainās. Labi, tāpēc mēs atgriezīsimies pie viena no šiem diviem failiem, un es mainīšu Apple klasi uz 99, un pat ievietosim kaut ko jaunu un saglabāsim šo darbgrāmatu. Labi. Un tad, ja mēs vēlamies, lai mūsu apvienošanas fails tiktu atjaunināts, nāciet šeit - tagad, uzmanieties, kad jūs to darāt pirmo reizi, jūs nevarat redzēt ikonu Atsvaidzināt - jums ir jāķer šī josla un jāvelk tā . Un mēs darīsim atsvaidzināšanu, un, ielādējot 17 rindas, parādās arbūzs, Apple mainās uz 99 - tā ir skaista lieta. Tagad, hei, vai vēlaties uzzināt par Power Query? Pērciet šo Ken Puls un Miguel Escobar grāmatu, M ir domāta (DATA) MONKEY. Es jūs paātrināšu.
Šodien ietīšana: Deividam no Floridas ir divas darba burtnīcas, kuras viņš vēlas apvienot; viņiem abiem ir vienādi lauki A slejā, bet visas pārējās kolonnas ir atšķirīgas; vienā darbgrāmatā varētu būt papildu priekšmeti, kuru nav otrā, un Dāvids tos vēlas; nevienā failā nav dublikātu; mēs to izmantosim enerģijas vaicājumam, lai to atrisinātu, tāpēc sāciet ar jaunu tukšu darbgrāmatu tukšā darblapā; jūs gatavojaties veikt trīs vaicājumus, vispirms vienu - dati, no faila, darbgrāmata un pēc tam ielādēt tikai izveidotajā savienojumā; tas pats par otro darbgrāmatu un pēc tam Dati, Iegūt datus, Apvienot, atlasiet abus savienojumus, atlasiet kolonnu, kas ir izplatīta abos - manā gadījumā Produkts - un pēc tam no Pievienošanās veida vēlaties pilnībā pievienoties viss no 1. faila, viss no 2. faila. Un jauki, ja mainās pamatā esošie dati,jūs varat vienkārši atsvaidzināt vaicājumu.
Lai lejupielādētu darbgrāmatu no šodienas videoklipa, apmeklējiet vietni YouTube YouTube aprakstā.
Nu, hei, es gribu, piemēram, Deivids, ka viņš parādījās manā seminārā, es gribu pateikties, ka apstājāties. Tiksimies nākamreiz, lai skatītu citu netcast no.
Lejupielādējiet Excel failu
Lai lejupielādētu Excel failu: combin-based-on-common-column.xlsx
Power Query ir pārsteidzošs rīks programmā Excel.
Excel dienas doma
Esmu lūdzis saviem Excel Master draugiem padomu par Excel. Šodienas doma apdomāt:
"Vienmēr nospiediet taustiņu F4, kad funkcijā lasāt diapazonu vai matricu"
Tanja Kuhn
