Floridast pärit David esitab tänase küsimuse:
Mul on kaks töövihikut. Mõlemal on veerus A samad andmed, kuid ülejäänud veerud on erinevad. Kuidas saan need kaks töövihikut ühendada?
Küsisin Davidilt, kas on võimalik, et ühel töövihikul on rohkem kirjeid kui teisel. Ja vastus on jah. Küsisin Davidilt, kas võtmeväli ilmub igas failis ainult üks kord. Vastus on ka jah. Täna lahendan selle Power Query abil. Power Query tööriistad leiate Exceli 2016+ Windowsi versioonidest vahekaardi Andmed jaotises Hangi ja muuda. Kui teil on Exceli 2010 või Excel 2013 Windowsi versioonid, saate nende versioonide jaoks Power Query lisandmooduli alla laadida.
Siin on Davidi töövihik 1. Selles on toode ja seejärel kolm veergu andmeid.
Siin on Davidi töövihik 2. Selles on tootekood ja seejärel muud veerud. Selles näites on töövihikus2 lisatooteid, kuid lahendused toimivad, kui kummaski töövihikus on lisaveerge.
Siin on sammud:
-
Valige Andmed, Hangi andmed, failist, töövihikust:
Laadige andmed failist - Sirvige esimese töövihikuni ja klõpsake nuppu OK
- Valige navigaatori dialoogis vasakul asuv tööleht. (Isegi kui töölehte on ainult üks, peate selle valima.) Näete andmeid paremal.
- Avage navigaatori dialoogis rippmenüü Laadi ja valige Laadi …
- Valige Ainult ühenduse loomine ja vajutage nuppu OK.
-
Korrake teise töövihiku samme 1-5.
Looge ühendus töövihikuga Kui olete mõlemad töövihikud teinud, peaksite Exceli ekraani paremal küljel kuvama päringute ja ühenduste paneelil kaks ühendust.
Ühendused mõlema töövihikuga Jätkake töövihikute ühendamiseks järgmiste toimingutega.
-
Andmed, andmete hankimine, päringute ühendamine, ühendamine.
Ühendage kaks erineva veeruga päringut - Valige dialoogi Ühendamine ülaosas olevast rippmenüüst esimene päring.
- Valige dialoogi Ühendamine teisest rippmenüüst teine päring.
- Klõpsake ülemise eelvaate pealkirjal Toote (see on võtmeväli. Pange tähele, et saate mitu või rohkem võtmevälja valida, vajutades Ctrl + klõpsamine)
- Klõpsake teises eelvaates pealkirja Tootekood.
-
Avage liitumistüüp ja valige Täielik välimine (kõik read mõlemalt)
Siin on illustreeritud etapid 8–12 -
Klõpsake nuppu OK. Andmete eelvaade ei näita lisaridu ja viimases veerus kuvatakse korduvalt ainult tabel.
See ei tundu paljulubav - Pange tähele, et DavidTwo pealkirjas on ikoon "Laienda". Klõpsake sellel ikoonil.
-
Valikuline, kuid valin alati valiku „Kasuta algse veeru nime prefiksina”. Klõpsake nuppu OK.
Laiendage töövihiku 2 välju Tulemused kuvatakse selles eelvaates:
Kõik kirjed mõlemast töövihikust - Rakenduses Power Query kasutage nuppu Kodu, Sule ja laadige.
Siin on ilus funktsioon: kui kummagi töövihiku alusandmed muutuvad, võite klõpsata ikoonil Värskenda, et tõmmata uued andmed tulemuste töövihikusse.
Märge
Värskendamise ikoon on tavaliselt peidetud. Ikooni paljastamiseks lohistage paani Päringud ja ühendused vasakut serva vasakule.
Vaata videot
Video ärakiri
Exceli õppimine Podcastist, episood 2216: ühendage kaks töövihikut ühise veeru põhjal.
Tere, tere tulemast tagasi netiülekandesse, ma olen Bill Jelen. Tänane küsimus on Davidilt, kes oli mu seminaril Floridas Melbourne'is IIA kosmoseranniku peatükis.
Davidil on kaks erinevat töövihikut, kus veerg A on mõlema vahel ühine. Nii, siin on töövihik 1, siin töövihik 2 - mõlemal on tootekood. Sellel on üksusi, mida esimesel pole, või vastupidi, ja David soovib kõik veerud ühendada. Nii et meil on siin kolm veergu ja neli veergu siin. Panin mõlemad need samasse töövihikusse, juhul kui laadite töövihiku toimimiseks alla. Võtke igaüks neist, teisaldage see oma töövihikusse ja salvestage.
Hästi, nende failide kombineerimiseks kasutame Power Queryd. Power Query on sisse ehitatud rakendusse Excel 2016. Kui kasutate Windowsi versiooni 10 või 13, võite minna Microsofti ja alla laadida Power Query. Võite alustada uuest tühjast töövihikust koos tühja töölehega. Salvestate selle faili. Kombineeritud failide .xlsx tulemuste kuvamiseks salvestage teadmine, võib-olla töövihik. Hästi? Ja mida me teeme, on see, et teeme kaks päringut. Läheme menüüsse Data, Get Data, From File, From Workbook ja valime siis esimese faili. Valige eelvaates leht, millel on teie andmed, ja me ei pea nende andmetega midagi tegema. Nii et lihtsalt avage laadimiskast ja valige Laadi, loo ainult ühendus, klõpsake nuppu OK. Täiuslik. Kordame seda teise üksuse puhul - andmed, failist,Valige töövihikust DavidTwo, valige lehe nimi ja seejärel avage koormus, Laadi ainult, looge ainult ühendus. Näete siin paneelil, meil on mõlemad ühendused olemas. Hästi.
Nüüd tegelik töö - andmed, andmete hankimine, päringute ühendamine, ühendamine ja seejärel ühendamise dialoogis valige DavidOne, DavidTwo ja see järgmine samm on täiesti mõistmatu. Sa pead seda tegema. Valige veerg või veerud ühiselt - nii toode kui ka toode. Hästi. Ja siis olge siin liitumisviisiga väga ettevaatlik. Soovin kõiki ridu mõlemast, kuna ühel võib olla lisarida ja ma pean seda nägema ning siis klõpsame nuppu OK. Hästi. Ja siin on esialgne tulemus. Tundub, et see ei töötanud; ei tundu, nagu oleks see lisanud täiendavaid üksusi, mis olid failis 2. Ja meil on see veerg 5 - see on nüüd null. Paremklõpsan 5. veerus ja ütlen: Eemalda see veerg. Nii avage see laiendamise ikoon ja tühjendage see ruut Kasuta algset veeru nime prefiksina ja BAM! see töötab. Nii et täiendavad üksused, mis olid failis 2 ja mida pole failis 1,ilmuvad küll.
Hästi. Nüüd tänases failis näib, et see veerg Tootekood on parem kui see veerg Tooted, kuna sellel on lisaridu. Kuid tulevikus võib juhtuda, et 1. töövihikus on asju, mida 2. töövihikus pole. Nii et jätan mõlemad sinna ja ma ei vabane ühestki nullist, sest nagu näib, et kuigi see rida allosas näib olevat täiesti null, võib tulevikus olla olukord, kus meil on siin mõned nullid, sest midagi on puudu. Hästi? Nii et lõpuks sulgege ja laadige ning meil on oma kuusteist rida.
Oletame nüüd, et tulevikus midagi muutub. Hästi, nii et läheme tagasi ühe neist kahest failist ja ma muudan Apple'i klassi 99-ks ning sisestame isegi midagi uut ja salvestame selle töövihiku. Hästi. Ja siis, kui tahame, et meie liitmisfaili värskendataks, tulge siia - nüüd, olge ettevaatlik, kui teete seda esimest korda, ei näe te värskendusikooni - peate selle riba haarama ja lohistama . Ja me teeme värskenduse ja 17 rida on laaditud, ilmub arbuus, Apple muutub 99-ks - see on ilus asi. Kuule, kas sa tahad Power Query kohta rohkem teada saada? Osta see Ken Pulsi ja Miguel Escobari raamat, M on (DATA) MONKEY jaoks. Ma annan sulle hoogu juurde.
Täna kokku: Floridast pärit Davidil on kaks töövihikut, mida ta soovib ühendada; mõlemal on veerus A samad väljad, kuid ülejäänud veerud on kõik erinevad; ühes töövihikus võib olla täiendavaid esemeid, mida teises pole, ja David soovib neid; kummaski failis pole duplikaate; selle lahendamiseks kasutame võimsuspäringut, nii et alustage tühja töölehe uues tühjas töövihikus; kavatsete teha kolm päringut, kõigepealt ühe - andmed, failist, töövihik ja seejärel laadida ainult loodud ühendusse; sama asi teise töövihiku jaoks ja seejärel andmed, andmete hankimine, ühendamine, valige kaks ühendust, valige veerg, mis on mõlemas tavaline - minu puhul toode - ja seejärel liidetüübist soovite täielikult liituda kõik failist 1, kõik failist 2. Ja siis on ilus see, kui alusandmed muutuvad,saate lihtsalt päringut värskendada.
Töövihiku allalaadimiseks tänasest videost külastage YouTube'i kirjelduses olevat URL-i.
Noh, hei, ma tahan nagu David, et ta minu seminarile ilmus, tahan tänada teid, et peatusite. Järgmisel korral näeme järgmise Netcast'i jaoks.
Laadige alla Exceli fail
Exceli faili allalaadimiseks: combine-based-on-common-column.xlsx
Power Query on Exceli hämmastav tööriist.
Exceli päeva mõte
Olen küsinud oma Exceli meistri sõpradelt Exceli kohta nõu. Tänane mõte mõelda:
"Funktsioonis vahemiku või maatriksi lugemisel vajutage alati klahvi F4"
Tanja Kuhn
