David din Florida pune întrebarea de astăzi:
Am două cărți de lucru. Ambele au aceleași date în coloana A, dar coloanele rămase sunt diferite. Cum pot îmbina aceste două registre de lucru?
L-am întrebat pe David dacă este posibil ca un registru de lucru să aibă mai multe înregistrări decât celălalt. Și răspunsul este da. L-am întrebat pe David dacă câmpul cheie apare o singură dată în fiecare fișier. Răspunsul este și da. Astăzi, voi rezolva acest lucru cu Power Query. Instrumentele Power Query se găsesc în versiunile Windows ale Excel 2016+ în secțiunea Obțineți și transformați din fila Date. Dacă aveți versiuni Windows ale Excel 2010 sau Excel 2013, puteți descărca programul de completare Power Query pentru acele versiuni.
Iată cartea de lucru a lui David 1. Are produs și apoi trei coloane de date.
Iată cartea de lucru a lui David 2. Are Cod produs și apoi alte coloane. În acest exemplu, există produse suplimentare în registrul de lucru2, dar soluțiile vor funcționa dacă oricare dintre registrele de lucru are coloane suplimentare.
Iată pașii:
-
Selectați date, obțineți date, din fișier, din registrul de lucru:
Încărcați date dintr-un fișier - Navigați la primul registru de lucru și faceți clic pe OK
- În dialogul Navigator, alegeți foaia de lucru din stânga. (Chiar dacă există o singură foaie de lucru, trebuie să o selectați.) Veți vedea datele din dreapta.
- În dialogul Navigator, deschideți meniul derulant Încărcare și alegeți Încărcare în …
- Alegeți doar Creați o conexiune și apăsați OK.
-
Repetați pașii 1-5 pentru al doilea registru de lucru.
Creați o conexiune la registrul de lucru Dacă ați realizat ambele registre de lucru, ar trebui să vedeți două conexiuni pe panoul Interogări și conexiuni din dreapta ecranului Excel.
Conexiuni la ambele registre de lucru Continuați cu pașii de îmbinare a registrelor de lucru:
-
Date, Obțineți date, Combinați interogări, Îmbinați.
Îmbinați două interogări cu coloane diferite - Din meniul derulant din partea de sus a casetei de dialog Merge, alegeți prima interogare.
- Din a doua listă derulantă din dialogul Îmbinare, alegeți a doua interogare.
- Faceți clic pe titlul Produsului în previzualizarea de sus (acesta este câmpul cheie. Rețineți că puteți selecta mai multe două sau mai multe câmpuri cheie prin Ctrl + Clic)
- Faceți clic pe titlul Cod produs în a doua previzualizare.
-
Deschideți tipul de alăturare și alegeți complet exterior (toate rândurile din ambele)
Etapele 8-12 ilustrate aici -
Faceți clic pe OK. Previzualizarea datelor nu afișează rândurile suplimentare și afișează „Tabel” doar în mod repetat în ultima coloană.
Acest lucru nu pare promițător - Observați că există o pictogramă „Extindeți” în titlul pentru DavidTwo. Faceți clic pe pictograma respectivă.
-
Opțional, dar deselectez întotdeauna „Utilizați numele coloanei originale ca prefix”. Faceți clic pe OK.
Extindeți câmpurile din registrul de lucru 2 Rezultatele sunt afișate în această previzualizare:
Toate înregistrările din oricare dintre registrele de lucru - În Power Query, utilizați Acasă, Închideți și încărcați.
Iată frumoasa caracteristică: dacă datele care stau la baza fiecărui registru de lucru se modifică, puteți face clic pe pictograma Reîmprospătare pentru a trage date noi în registrul de lucru cu rezultate.
Notă
Pictograma pentru Reîmprospătare este de obicei ascunsă. Trageți marginea stângă a panoului Interogări și conexiuni spre stânga pentru a afișa pictograma.
Urmăriți videoclipul
Transcriere video
Aflați Excel din Podcast, Episodul 2216: Combinați două cărți de lucru pe baza unei coloane comune.
Hei, bine ai revenit pe netcast, eu sunt Bill Jelen. Întrebarea de astăzi este de la David, care a fost la seminarul meu din Melbourne, Florida, pentru capitolul Space Coast al IIA.
David are două registre de lucru diferite în care Coloana A este comună între amândoi. Deci, aici este Workbook 1, aici este Workbook 2 - ambele au codul produsului. Acesta are elemente pe care primul nu le are, sau invers, iar David vrea să combine toate coloanele. Deci, avem trei coloane aici și patru coloane aici. Le-am pus pe ambele în același registru de lucru, în cazul în care descărcați registrul de lucru pentru a lucra împreună. Luați fiecare dintre acestea, mutați-l în propriul registru de lucru și salvați-l.
În regulă, pentru a combina aceste fișiere, vom folosi Power Query. Power Query este încorporat în Excel 2016. Dacă vă aflați în versiunea Windows 10 sau 13, puteți accesa Microsoft și descărca Power Query. Puteți începe de la un nou registru de lucru gol cu o foaie de lucru goală. Veți salva acest fișier - Salvați ca, știți, poate Workbook, pentru a afișa rezultatele fișierelor combinate .xlsx. Bine? Și ceea ce vom face este, vom face două interogări. Vom merge la Date, Obțineți date, Din fișier, Din registru de lucru și apoi vom alege primul fișier. Într-o previzualizare, selectați foaia care conține datele dvs. și nu trebuie să facem nimic pentru aceste date. Deci, deschideți caseta de încărcare și alegeți Încărcare în, Creați doar conexiune, faceți clic pe OK. Perfect. Acum, vom repeta asta pentru al doilea element - Date, From File,Dintr-un registru de lucru, alegeți DavidTwo, alegeți numele foii, apoi deschideți încărcarea, Încărcați în, Creați doar o conexiune. Veți vedea aici în acest panou, avem ambele conexiuni prezente. Bine.
Acum lucrarea efectivă - Date, Obțineți date, Combinați interogări, Îmbinați și apoi în dialogul Merge, alegeți DavidOne, DavidTwo, iar acest pas următor este complet neintuitiv. Trebuie să faci asta. Alegeți coloana sau coloanele comune - deci Produs și Produs. Bine. Și apoi, fii foarte atent aici cu tipul de unire. Vreau toate rândurile de la ambele, deoarece s-ar putea ca unul să aibă un rând suplimentar și trebuie să văd asta, apoi facem clic pe OK. Bine. Și iată rezultatul inițial. Nu pare că a funcționat; nu se pare că a adăugat elementele suplimentare care erau în fișierul 2. Și avem această coloană 5 - este nulă acum. Voi face clic dreapta pe coloana 5 și voi spune, Eliminați coloana respectivă. Deci, deschideți această pictogramă de extindere și debifați această casetă pentru Utilizați numele coloanei originale ca prefix și BAM! funcționează. Deci, elementele suplimentare care erau în Fișierul 2, care nu sunt în Fișierul 1,apar.
Bine. Acum, în fișierul de astăzi, se pare că această coloană Cod produs este mai bună decât această coloană Produs, deoarece are rânduri suplimentare. Dar s-ar putea să existe o zi în viitor în care Workbook 1 să aibă lucruri pe care Workbook 2 nu le are. Deci, le voi lăsa pe amândouă acolo și nu voi scăpa de nule, deoarece, deși, chiar dacă acest rând din partea de jos pare a fi complet nul, ar putea exista în viitor o situație în care avem câteva nule aici pentru că lipsește ceva. Bine? Deci, în sfârșit, Închideți și încărcați și avem șaisprezece rânduri.
Acum, în viitor, să spunem că se schimbă ceva. Bine, așa că ne vom întoarce la unul dintre aceste două fișiere și voi schimba clasa pentru Apple la 99 și chiar să introducem ceva nou și să salvăm acest registru de lucru. Bine. Și apoi, dacă dorim ca fișierul nostru de îmbinare să se actualizeze, veniți aici - acum, atenție, când faceți acest lucru prima dată, nu puteți vedea pictograma Reîmprospătare - trebuie să apucați această bară și să o trageți peste . Și vom face Reîmprospătare și 17 rânduri încărcate, apare pepene verde, Apple se schimbă la 99 - este un lucru frumos. Acum, hei, vrei să afli despre Power Query? Cumpărați această carte de Ken Puls și Miguel Escobar, M este pentru (DATA) MAIMĂ. Te voi pune la curent.
Încheiați astăzi: David din Florida are două cărți de lucru pe care vrea să le combine; ambele au aceleași câmpuri în Coloana A, dar celelalte coloane sunt toate diferite; un registru de lucru ar putea avea elemente suplimentare care nu sunt în celălalt și David le dorește; nu există duplicate în niciun fișier; vom folosi interogarea de putere pentru a rezolva acest lucru, așa că începeți într-un nou registru de lucru gol pe o foaie de lucru goală; veți face trei interogări, mai întâi una - Date, From File, Workbook și apoi Load to only Created Connection; același lucru pentru al doilea registru de lucru și apoi Date, Obțineți date, Merge, selectați cele două conexiuni, selectați coloana care este comună în ambele - în cazul meu, Produs - și apoi din Tip de înscriere, doriți să vă înscrieți toate din Fișierul 1, toate din Fișierul 2. Și atunci frumosul este dacă datele subiacente se schimbă,puteți doar să reîmprospătați interogarea.
Pentru a descărca registrul de lucru din videoclipul de astăzi, accesați adresa URL din descrierea YouTube.
Ei bine, vreau ca David pentru că a apărut la seminarul meu, vreau să-ți mulțumesc că ai trecut pe aici. Ne vedem data viitoare pentru un alt netcast de la.
Descărcați fișierul Excel
Pentru a descărca fișierul Excel: combine-based-on-common-column.xlsx
Power Query este un instrument uimitor în Excel.
Gândul Excel al zilei
Le-am cerut prietenilor mei Excel Master sfatul lor despre Excel. Gândul de astăzi să medităm:
„Apăsați întotdeauna F4 când citiți intervalul sau matricea într-o funcție”
Tanja Kuhn
