În acest tutorial, vom analiza modul în care vă puteți alătura tabelelor în Excel pe baza uneia sau mai multor coloane obișnuite utilizând Expertul de interogare Power și Merge Tables.

Combinarea datelor din mai multe tabele este una dintre cele mai descurajante sarcini din Excel. Dacă decideți să o faceți manual, puteți petrece ore întregi doar pentru a afla că ați deranjat informații importante. Dacă sunteți un profesionist Excel cu experiență, atunci vă puteți baza pe formulele VLOOKUP și INDEX MATCH. O macro, credeți, ar putea face treaba în cel mai scurt timp, dacă ați ști cum. Veștile bune pentru toți utilizatorii Excel - Power Query sau Merge Tables Wizard pot economisi timp. Alegerea este a ta.

Cum să uniți tabele cu Excel Power Query

In termeni simpli, Interogare de alimentare (de asemenea cunoscut ca si Obțineți și transformați în Excel 2016 și Excel 2019) este un instrument pentru a combina, curăța și transforma datele din mai multe surse în formatul de care aveți nevoie, cum ar fi un tabel, un tabel pivot sau o diagramă pivot.

Printre altele, Power Query poate alăturați 2 mese în 1 sau combinați date din tabele multiple prin potrivirea datelor în coloane, care este centrul acestui tutorial.

Pentru ca rezultatele să corespundă așteptărilor dvs., vă rugăm să țineți cont de următoarele lucruri:

  • Power Query este o funcție încorporată în Excel 2016 și Excel 2019, dar poate fi descărcată și în Excel 2010 și Excel 2013 și utilizată ca supliment. În versiunile anterioare, unele ferestre pot arăta diferit de imaginile din acest tutorial care au fost capturate în Excel 2016.
  • Pentru ca tabelele să fie combinate corect, acestea ar trebui să aibă cel puțin o coloană comună (denumită și un cod comun sau o coloană cheie sau un identificator unic). De asemenea, coloanele comune ar trebui să conțină numai valori unice, fără repetări.
  • Tabelele sursă pot fi localizate pe aceeași foaie sau în foi de lucru diferite.
  • Spre deosebire de formule, Power Query nu trage date dintr-un tabel în altul. Se creează un nou tabel care combină datele din tabelele originale.
  • Tabelul rezultat nu se actualizează automat. Ar trebui să îi spuneți în mod explicit Excelului să facă acest lucru. Vedeți cum să reîmprospătați un tabel combinat.

Date sursă

De exemplu, să alăturăm 3 tabele bazate pe coloanele comune ID-ul comenzii și vânzătorul. Vă rugăm să rețineți că tabelele noastre au un număr diferit de rânduri și, deși tabelul 1 are duplicate în coloana Vânzător, tabelul 3 conține doar intrări unice.

multe

Sarcina noastră este să mapăm datele din tabelul 1 cu înregistrările relevante din celelalte două tabele și să combinăm toate datele într-un tabel nou ca acesta:

Înainte de a începe să vă alăturați, vă sfătuiesc să dați câteva nume descriptive tabelelor dvs., astfel încât vă va fi mai ușor să le recunoașteți și să le gestionați ulterior. De asemenea, deși spunem „tabele”, nu este necesar să creați un tabel Excel. „Tabelele” dvs. pot fi intervale obișnuite sau intervale denumite ca în acest exemplu:

  • Tabelul 1 se numește Comenzi
  • Tabelul 2 este denumit Produse
  • Tabelul 3 este denumit Comisioane

Creați conexiuni Power Query

Pentru a nu vă aglomera registrul de lucru cu copii ale tabelelor dvs. originale, le vom converti în conexiuni, vom face îmbinarea în Power Query Editor și apoi vom încărca doar tabelul rezultat.

Pentru a salva un tabel ca conexiune în Power Query, iată ce faceți:

  1. Selectați primul dvs. tabel (Comenzi) sau orice celulă din acel tabel.
  2. Accesați fila Date> Obțineți și transformați grupul și faceți clic Din tabel/interval.
  3. În Editorul de interogări Power care se deschide, faceți clic pe Închidere și încărcaresăgeată derulantă (nu butonul în sine!) și selectați Închideți și încărcați în ... opțiune.
  4. În caseta de dialog Import date, selectați Creați numai conexiune și faceți clic pe OK.

Aceasta va crea o conexiune cu numele tabelului/intervalului dvs. și va afișa conexiunea respectivă în panoul Interogări și conexiuni care apare în partea dreaptă a registrului de lucru.

  • Repetați pașii de mai sus pentru toate celelalte tabele pe care doriți să le îmbinați (încă două tabele, Produse și Comisioane, în cazul nostru).

    • Când ați terminat, veți vedea toate conexiunile pe panou:

    Îmbinați două conexiuni într-un singur tabel

    Cu conexiunile la locul lor, să vedem cum puteți uni două tabele într-una:

    1. În fila Date, în grupul Obțineți și transformați date, faceți clic pe butonul Obțineți date, alegeți Combinați interogări în lista derulantă și faceți clic pe Combina:
    2. În caseta de dialog Merge, efectuați următoarele:
      • Selectați primul tabel (Comenzi) din primul meniu derulant.
      • Selectați al doilea tabel (Produse) din al doilea meniu derulant.
      • În ambele previzualizări, faceți clic pe coloană potrivită (ID comandă) pentru ao selecta. Coloana selectată va fi evidențiată în verde.
      • În lista derulantă Alăturați-vă tipului, lăsați opțiunea implicită: Stânga exterioară (toate din primul, potrivite din al doilea).
      • Faceți clic pe OK.

    La finalizarea pașilor de mai sus, Editorul de interogări Power va afișa primul tabel (Comenzi) cu o coloană suplimentară denumită ca al doilea tabel (Produse) adăugată la final. Această coloană suplimentară nu are încă valori, doar cuvântul „Tabel” în toate celulele. Dar nu vă simțiți descurajați, ați făcut totul bine și vom remedia asta într-o clipă!

    Selectați coloanele de adăugat din al doilea tabel

    În acest moment, aveți un tabel asemănător celui din captura de ecran de mai jos. Pentru a finaliza procesul de fuzionare, efectuați următorii pași în Editorul de interogări Power:

    1. În coloana adăugată (Produse), faceți clic pe săgeata față-verso din antet.
    2. În caseta care se deschide, faceți acest lucru:
      • Păstrează Extinde butonul radio selectat.
      • Deselectați toate coloanele, apoi selectați numai coloana (coloanele) pe care doriți să le copiați din al doilea tabel. În acest exemplu, selectăm doar coloana Produs, deoarece primul nostru tabel are deja codul vânzătorului și al comenzii.
      • Debifați Folosiți numele coloanei originale ca prefix casetă (cu excepția cazului în care doriți ca numele coloanei să fie prefixat cu numele tabelului din care este preluată această coloană).
      • Faceți clic pe OK.

    Ca rezultat, veți obține un nou tabel care conține fiecare înregistrare din primul dvs. tabel și coloanele suplimentare din cel de-al doilea tabel:

    Dacă trebuie să îmbinați doar două tabele, puteți considera lucrarea aproape terminată și puteți încărca tabelul rezultat în Excel.

    Îmbinați mai multe tabele (opțional)

    În cazul în care aveți trei sau mai multe tabele de alăturat, mai aveți de lucru. Voi descrie pașii pe scurt aici, pentru că ați făcut deja toate acestea atunci când vă alăturați primelor două tabele:

    1. Salvați tabelul pe care îl aveți la pasul anterior (prezentat în captura de ecran de mai sus) ca conexiune:
      • În Power Query Editor, faceți clic pe Închidere și încărcare săgeată derulantă și selectați Închideți și încărcați în ....
      • În caseta de dialog Import date, selectați Creați doar conexiune și faceți clic pe OK.

    Aceasta va adăuga încă o conexiune, numită Merge1, la panoul Interogări și conexiuni. Puteți redenumi această conexiune dacă doriți (faceți clic dreapta și selectați Redenumiți în meniul pop-up).
    Combinați Merge1 cu al treilea tabel (Comisioane) efectuând acești pași (fila Date> Obțineți date> Combinați interogări> Merge).

    Captura de ecran de mai jos arată setările mele:

  • Dacă faceți clic pe OK în caseta de dialog Merge, se deschide Editorul de interogări Power, unde selectați coloanele care vor fi adăugate din tabelul 3.

    • În acest exemplu, adăugăm doar coloana Comisiei:

    Ca rezultat, veți obține un tabel combinat care constă din primul tabel, plus coloanele suplimentare copiate din celelalte două tabele.

    Importați tabelul combinat în Excel

    Cu tabelul rezultat în Power Query Editor, rămâne doar un lucru de făcut - încărcați-l în registrul de lucru Excel. Și este partea cea mai ușoară!

    1. În Power Query Editor, faceți clic pe săgeata drop-down Închidere și încărcare și alegeți Închideți și încărcați în ....
    2. În caseta de dialog Import date, selectați Masa și Foaie de lucru nouă Opțiuni.
    3. Faceți clic pe OK.

    Un nou tabel care combină datele din două sau mai multe surse apare într-o nouă foaie de lucru. Felicitări, ai făcut-o!

    Ca o ultimă atingere, poate doriți să aplicați formatul corect al numărului la unele coloane și poate schimbați stilul implicit al tabelului la cel preferat. După aceste îmbunătățiri, tabelul meu combinat arată foarte frumos:

    Cum să uniți tabele bazate pe mai multe coloane cu Power Query

    În exemplul anterior, combinam tabele prin potrivirea datelor într-o singură coloană cheie. Dar nu există nimic care să vă împiedice să selectați două sau mai multe perechi de coloane. Iată cum:

    În caseta de dialog Merge, țineți apăsată tasta Ctrl și faceți clic pe coloanele cheie una câte una pentru a le selecta. Este important să faceți clic pe coloane în aceeași ordine în ambele previzualizări, astfel încât coloanele potrivite să aibă aceleași numere. De exemplu, Vânzătorul este coloana cheie 1 și Produsul este coloana cheie 2. Celulele sau rândurile goale pe care Power Query nu le poate egala arată nul:

    După aceea, efectuați exact aceiași pași descriși mai sus, iar tabelele dvs. vor fi îmbinate prin potrivirea valorilor din toate coloanele cheie.

    Cum se actualizează/reîmprospătează tabelul combinat

    Cel mai bun lucru despre Power Query este că este o configurare unică. Când efectuați unele modificări la un tabel sursă, nu trebuie să repetați întregul proces din nou. Pur și simplu, faceți clic pe butonul Reîmprospătare din panoul Interogări și conexiuni, iar tabelul combinat se va actualiza simultan:

    Dacă panoul a dispărut din Excel, faceți clic pe butonul Interogări și conexiuni din fila Date pentru al recupera.

    Alternativ, puteți face clic pe Reîmprospătați-le pe toate butonul din fila fila Date sau Reîmprospăta butonul din interogare (această filă se activează după ce selectați orice celulă dintr-un tabel fuzionat).

    Expert Merge Tables - modalitate rapidă de a uni 2 tabele în Excel

    Acum, că sunteți familiarizat cu instrumentul încorporat, permiteți-mi să vă arăt abordarea noastră privind fuzionarea tabelelor în Excel.

    În acest exemplu, vom combina aceleași tabele pe care le-am alăturat cu Power Query acum un moment. Tocmai am adăugat câteva rânduri la al doilea tabel pentru a vă arăta mai multe funcții ale programului nostru de completare:

    Cu Expertul Merge Tables instalat în Excel, iată ce trebuie să faceți:

    1. Selectați primul tabel sau orice celulă din acesta și faceți clic pe Combinați două mese din fila Ablebits Data:
    2. Aruncați o privire rapidă asupra gamei selectate pentru a vă asigura că programul de completare a fost corect și faceți clic pe Următorul.
    3. Selectați al doilea tabel și faceți clic pe Următorul. Vă rugăm să rețineți că al doilea tabel conține 26 de rânduri comparativ cu doar 10 rânduri din primul tabel:
    4. Alege unu sau mai multe coloane potrivite și faceți clic pe Următorul. Deoarece alăturăm două tabele printr-o coloană comună, ID-ul comenzii, selectăm doar acea coloană:

      Vă rugăm să observați caseta de potrivire diferențiată de majuscule și minuscule din partea de sus. Selectați-l dacă doriți să tratați textul cu majuscule și minuscule în coloanele cheie ca caractere diferite. Pentru acest exemplu, nu avem nevoie de asta, așa că lăsăm caseta neselectată.
    5. Selectați coloanele către Actualizați în primul tabel. Acest pas este opțional și, dacă nu doriți nicio actualizare, puteți face clic pe Următorul fără a selecta nimic aici.

    Selectăm coloana Vânzător deoarece avem mai multe rânduri în al doilea tabel și dorim ca noile nume ale vânzătorului să apară în coloana Vânzător existentă:

  • Alegeți coloanele care vor fi adăugate la primul tabel, Produs în cazul nostru, și faceți clic pe Următorul:
  • Acest pas este foarte important, deoarece determină modul în care tabelele dvs. vor fi îmbinate.
    În acest exemplu, mergem cu opțiunile implicite afișate în captura de ecran de mai jos. Dar aș dori să vă atrag atenția asupra următoarelor 2 casete care pot împiedica suprascrierea datelor existente în cazul în care ați ales Actualizați câteva coloane:

    • Numai celule goale
    • Numai dacă celulele din tabelul de căutare conțin date

    Faceți alegerile, faceți clic pe Finalizare, permiteți expertului câteva secunde pentru procesare și examinați rezultatele.

    Cu opțiunile implicite, expertul evidențiază rândurile nou adăugate și adaugă coloana Stare. Dacă nu doriți nimic din toate acestea, debifați casetele corespunzătoare din ultimul pas.

    Pentru a alătura trei și mai multe tabele, repetați pur și simplu pașii de mai sus. Nu uitați să selectați rezultatul unei îmbinări anterioare ca tabel principal.

    Spre deosebire de Power Query, Expertul Merge Tables nu păstrează o conexiune între tabelele rezultate și cele sursă. În unele situații, acest lucru poate fi un dezavantaj. În plus, indiferent de ceea ce faceți cu tabelul sursă - editați, mutați sau chiar ștergeți - tabelul combinat rămâne intact.

    Acest exemplu a arătat doar un singur scenariu pe care vrăjitorul nostru îl poate gestiona, dar este mult mai mult! Dacă sunteți curioși să cunoașteți alte cazuri de utilizare, vă rugăm să consultați aceste exemple.

    De asemenea, puteți descărca o versiune de încercare a Ultimate Suite pentru Excel care include Expert Merge Tables, precum și peste 60 de alte instrumente utile. Dacă vă place suplimentul și decideți să obțineți o licență, suntem bucuroși să vă facem această ofertă exclusivă:

    În cazul în care doriți să vă alăturați tabelelor în alt mod, este posibil să găsiți următoarele resurse utile.

    Alte modalități de a combina datele în Excel:

    Îmbinați tabelele după anteturile coloanei - uniți două sau mai multe tabele pe baza numelor coloanelor. Puteți alege să combinați toate coloanele sau doar cele pe care le selectați.

    Combinați mai multe foi de lucru într-o singură - copiați mai multe foi într-o singură foaie de lucru sumară. Desigur, nu este copiere/lipire manuală! Indicați numai foile de lucru de îmbinat, iar instrumentul nostru Copiere foi face restul.

    Comparați două fișiere Excel - cum să comparați două tabele (foi de lucru) pentru diferențe și combinați-le într-o singură foaie.