Davidas iš Floridos užduoda šios dienos klausimą:
Turiu dvi darbo knygas. Abiejuose A stulpelyje yra tie patys duomenys, tačiau likę stulpeliai skiriasi. Kaip galiu sujungti tuos du darbaknygius?
Aš paklausiau Deivido, ar įmanoma, kad vienoje darbaknygėje būtų daugiau įrašų nei kitoje. Ir atsakymas yra „Taip“. Aš paklausiau Deivido, ar raktų laukas kiekviename faile rodomas tik vieną kartą. Atsakymas taip pat yra taip. Šiandien aš tai išspręsiu naudodamas „Power Query“. „Power Query“ įrankiai yra „Windows 2016+“ versijų versijose, skirtuko Duomenys skyriuje Gauti ir transformuoti. Jei turite „Windows 2010“ versijų „Excel 2010“ arba „Excel 2013“, galite atsisiųsti tų versijų „Power Query“ priedą.
Čia yra Dovydo darbaknygė 1. Joje yra produktas ir trys duomenų stulpeliai.
Čia yra Deivido 2 darbo knyga. Joje yra produkto kodas ir kiti stulpeliai. Šiame pavyzdyje darbaknygėje2 yra papildomų produktų, tačiau sprendimai veiks, jei bet kurioje darbaknygėje yra papildomų stulpelių.
Štai šie veiksmai:
-
Pasirinkite duomenis, gauti duomenis, iš failo, iš darbaknygės:
Įkelkite duomenis iš failo - Naršykite iki pirmosios darbaknygės ir spustelėkite Gerai
- Naršyklės dialogo lange pasirinkite darbalapį kairėje. (Net jei yra tik vienas darbalapis, turite jį pasirinkti.) Duomenis pamatysite dešinėje.
- Dialogo lange Naršyklė atidarykite išskleidžiamąjį meniu Įkelti ir pasirinkite Įkelti į…
- Pasirinkite Tik sukurti ryšį ir paspauskite Gerai.
-
Antrai darbaknygei pakartokite 1–5 veiksmus.
Sukurkite ryšį su darbaknyge Jei atlikote abu darbaknygius, „Excel“ ekrano dešinėje esančiame skydelyje „Užklausos ir ryšiai“ turėtumėte pamatyti du ryšius.
Ryšiai su abiem darbaknygėmis Toliau atlikite veiksmus, kad sujungtumėte darbaknyges:
-
Duomenys, Gauti duomenis, Sujungti užklausas, Sujungti.
Sujunkite dvi užklausas su skirtingais stulpeliais - Iš viršaus išskleidžiamajame dialogo langelyje „Sujungti“ pasirinkite pirmąją užklausą.
- Antrame išskleidžiamajame dialogo langelyje „Sujungti“ pasirinkite antrąją užklausą.
- Viršutinėje peržiūroje spustelėkite antraštę Produktas (tai yra raktų laukas. Atkreipkite dėmesį, kad galite pasirinkti kelis ar daugiau pagrindinių laukų paspausdami „Ctrl“ + spustelėdami)
- Antroje peržiūroje spustelėkite antraštę Prekės kodas.
-
Atidarykite „Join Type“ ir pasirinkite „Full Outer“ (visos eilutės iš abiejų)
Čia iliustruojami 8–12 žingsniai -
Spustelėkite Gerai. Duomenų peržiūra nerodo papildomų eilučių, o paskutiniame stulpelyje pakartotinai rodoma „Lentelė“.
Tai neatrodo perspektyvu - Atkreipkite dėmesį, kad „DavidTwo“ antraštėje yra piktograma „Išskleisti“. Spustelėkite tą piktogramą.
-
Pasirenkama, bet aš visada atžymiu parinktį „Naudoti pirminio stulpelio pavadinimą kaip priešdėlį“. Spustelėkite Gerai.
Išplėskite 2 darbaknygės laukus Rezultatai rodomi šioje peržiūroje:
Visi įrašai iš bet kurios darbaknygės - „Power Query“ naudokite „Pagrindinis“, „Uždaryti ir įkelti“.
Čia yra graži funkcija: jei pasikeičia pagrindiniai bet kurios darbaknygės duomenys, galite spustelėti piktogramą Atnaujinti, kad į rezultatų darbaknygę įtrauktumėte naujus duomenis.
Pastaba
Atnaujinimo piktograma paprastai yra paslėpta. Vilkite kairįjį lango Užklausos ir jungtys kraštą į kairę, kad atskleistumėte piktogramą.
Žiūrėti video
Vaizdo įrašo nuorašas
Sužinokite „Excel“ iš „Podcast“, 2216 serija: Sujunkite dvi darbaknyges pagal bendrą stulpelį.
Ei, sveiki sugrįžę į netcast'ą, aš Billas Jelenas. Šios dienos klausimas - Davido, kuris buvo mano seminare Melburne (Floridoje) TIS kosmoso pakrantės skyriuje.
Deividas turi du skirtingus darbaknygius, kur A stulpelis yra bendras tarp jų abiejų. Taigi, čia yra 1 darbaknygė, štai 2 darbaknygė - abu turi produkto kodą. Šis turi elementų, kurių pirmasis neturi, arba atvirkščiai, o Deividas nori sujungti visus stulpelius. Taigi, mes turime tris stulpelius čia ir keturis stulpelius čia. Aš įdėjau juos abu į tą pačią darbaknygę, jei atsisiųsite darbaknygę, kad ji veiktų kartu. Paimkite kiekvieną iš jų, perkelkite į savo darbo knygą ir išsaugokite.
Gerai, kad sujungtume šiuos failus, naudosime „Power Query“. „Power Query“ yra integruota į „Excel 2016.“. Jei naudojate „Windows“ versiją 10 arba 13, galite apsilankyti „Microsoft“ ir atsisiųsti „Power Query“. Galite pradėti nuo naujos tuščios darbaknygės su tuščiu darbalapiu. Jūs išsaugosite šį failą - išsaugokite kaip, žinote, galbūt darbaknygę, kad parodytumėte sujungtų failų .xlsx rezultatus. Gerai? Ką mes darysime, tai atliksime dvi užklausas. Eisime į Duomenys, Gauti duomenis, Iš failo, Iš darbaknygės ir tada mes pasirinksime pirmąjį failą. Peržiūroje pasirinkite lapą, kuriame yra jūsų duomenys, ir mes neturime nieko daryti su šiais duomenimis. Taigi tiesiog atidarykite apkrovos langelį ir pasirinkite Įkelti, tik sukurti ryšį, spustelėkite Gerai. Puikus. Dabar pakartosime tai antram elementui - duomenys, iš failo,Darbaknygėje pasirinkite „DavidTwo“, pasirinkite lapo pavadinimą ir atidarykite apkrovą, Įkelti, sukurti tik ryšį. Čia pamatysite šiame skydelyje, turime abu ryšius. Gerai.
Dabar tikrasis darbas - „Duomenys“, „Gauti duomenis“, „Sujungti užklausas“, „Sujungti“, tada dialogo langelyje „Sujungti“ pasirinkite „DavidOne“, „DavidTwo“, ir šis kitas žingsnis yra visiškai nenaudingas. Jūs turite tai padaryti. Pasirinkite bendrą stulpelį ar stulpelius - „Produktas“ ir „Produktas“. Gerai. Tada būkite labai atsargūs sujungimo tipu. Noriu visų eilučių iš abiejų, nes viena gali turėti papildomą eilutę, ir aš turiu tai pamatyti, o tada spustelėkite Gerai. Gerai. Ir štai pradinis rezultatas. Neatrodo, kad pavyko; neatrodo, kad jis pridėjo papildomų elementų, buvusių 2 faile. Dešiniuoju pelės mygtuku spustelėkite 5 stulpelį ir sakau: Pašalinti tą stulpelį. Taigi atidarykite šią išplėtimo piktogramą ir atžymėkite laukelį Naudoti originalų stulpelio pavadinimą kaip priešdėlį ir BAM! tai veikia. Taigi papildomi elementai, esantys 2 faile, kurių nėra 1 faile,tikrai pasirodo.
Gerai. Šiandienos faile atrodo, kad šis stulpelis Produkto kodas yra geresnis už šį stulpelį Produktas, nes jame yra papildomų eilučių. Bet ateityje gali būti diena, kai 1 darbaknygėje yra dalykų, kurių nėra 2 darbaknygėje. Taigi ketinu palikti juos abu ir neketinu atsikratyti niekų, nes, panašu, kad nors ši eilutė apačioje atrodo visiškai nulinė, ateityje gali būti situacijos, kai mes čia turime keletą niekų, nes kažko trūksta. Gerai? Taigi, pagaliau, „Uždaryti ir įkelti“, ir mes turime savo šešiolika eilučių.
Tarkime, kad ateityje kažkas pasikeis. Gerai, todėl grįšime prie vieno iš šių dviejų failų ir aš pakeisiu „Apple“ klasę į 99, netgi įdėkime ką nors naujo ir išsaugokime šią darbaknygę. Gerai. Ir tada, jei norime, kad sujungimo failas būtų atnaujintas, eikite čia - dabar, saugokitės, kai tai padarysite pirmą kartą, nematysite atnaujinimo piktogramos - turite patraukti šią juostą ir vilkti . Mes atliksime „Atnaujinti“ ir įkelta 17 eilučių, pasirodys arbūzas, „Apple“ pasikeis į 99 - tai gražus dalykas. Ei, ar nori sužinoti apie „Power Query“? Pirkite šią Ken Puls ir Miguel Escobar knygą, M skirta (DUOMENYS) PELKINIUI. Aš pagreitinsiu tave.
Šiandienos užbaigimas: Davidas iš Floridos turi du darbo knygas, kurias jis nori sujungti; jie abu turi tuos pačius laukus A stulpelyje, tačiau visi kiti stulpeliai yra skirtingi; vienoje darbaknygėje gali būti papildomų daiktų, kurių nėra kitoje, o Deividas to nori; nė viename faile nėra dublikatų; tam naudosime energijos užklausą, todėl pradėkite nuo naujos tuščios darbaknygės tuščiame darbalapyje; ketinate atlikti tris užklausas, iš pradžių vieną - duomenys, iš failo, darbaknygė ir tada įkelti tik į sukurtą ryšį; tas pats dalykas antrai darbaknygei, tada duomenys, gauti duomenis, sujungti, pasirinkite dvi jungtys, pasirinkite stulpelį, kuris yra įprastas abiejuose - mano atveju produktas - ir tada iš prisijungimo tipo norite visiškai prisijungti visi iš 1 failo, visi iš 2 failo. Ir tada gražus dalykas, jei pasikeis pagrindiniai duomenys,galite tiesiog atnaujinti užklausą.
Norėdami atsisiųsti darbaknygę iš šiandienos vaizdo įrašo, apsilankykite „YouTube“ apraše esančiame URL.
Na, aš, noriu, kad Deividas pasirodytų mano seminare, noriu padėkoti, kad užsukote. Pasimatysime kitą kartą kitam internetiniam transliacijai iš.
Atsisiųskite „Excel“ failą
Norėdami atsisiųsti „Excel“ failą: Combine-based-on-common-column.xlsx
„Power Query“ yra nuostabi „Excel“ priemonė.
„Excel“ dienos mintis
Aš paprašiau savo „Excel Master“ draugų patarimo apie „Excel“. Šiandienos mintis apmąstyti:
"Visada paspauskite F4, kai skaitote diapazoną ar matricą funkcijoje"
Tanja Kuhn
