Cum se combină datele din două foi de calcul Excel

Într-o luni, primești două fișiere în căsuța de e-mail. Unul conține comenzile din luna trecută, celălalt pe cele din luna aceasta, iar cineva de la conducere vrea un singur raport până la prânz. Așa că le deschizi pe amândouă, începi să copiezi și să lipești, iar la jumătatea drumului îți dai seama că coloanele nici măcar nu sunt aliniate la fel. Îți sună familiar?

Iată aspectul pe care aproape nimeni nu îl menționează de la început. În expresia„combinarea datelor din două foi de calcul în Excel” se ascund două sarcini complet diferite, iar alegerea celei greșite te face să-ți pierzi o după-amiază. Una dintre sarcini suprapune rândurile unul peste altul. Cealaltă potrivește coloanele una lângă alta folosind un ID comun. Dacă identifici corect problema, munca propriu-zisă durează doar câteva minute.

Ideea este că acest ghid clasifică mai întâi în funcție de scop, apoi îți indică instrumentul potrivit pentru fiecare. Nu mai trebuie să ghicești.

Cum să combini datele din două foi de calcul în Excel: greșeala pe care o face toată lumea la început

Majoritatea ghidurilor aleg o metodă și se concentrează exclusiv pe aceasta. Ele explică funcția VLOOKUP pe parcursul a șase paragrafe sau prezintă Power Query fără a menționa deloc funcțiile de căutare. Jumătate dintre cititori ajung pe pagina greșită și pleacă mai confuzi decât erau la început.

Adevărata problemă nu este instrumentul. Problema este că oamenii fac copiere-lipire manual, pentru că nu s-au oprit niciodată să se întrebe în ce formă se află datele. Iar lipirea manuală generează erori ușor de trecut cu vederea — un rând gol în plus pe aici, o coloană deplasată cu una mai departe pe acolo — și, brusc, totalul tău are o abatere de câteva mii și nimeni nu știe de ce.

Așadar, înainte de orice altceva, efectuează această verificare rapidă.

• Două foi de calcul, aceleași coloane, înregistrări diferite (comenzi din ianuarie + comenzi din februarie)? Trebuie să adăugați rânduri. Aceasta este o sarcină pentru Power Query.
• Două foi de calcul, aceeași coloană de ID-uri, detalii diferite (ID-urile comenzilor într-una, adresele de e-mail ale clienților în cealaltă)? Trebuie să potriviți coloanele. Aceasta este o sarcină pentru XLOOKUP sau INDEX/MATCH.

Această singură întrebare decide tot ce urmează. Dacă greșești, nici măcar cele mai bune cunoștințe de formule nu te vor salva.

Sarcina nr. 1: Adăugarea de rânduri cu Power Query

Când vrei să îmbini două foi de calcul Excel prin suprapunerea uneia peste cealaltă, Power Query este cea mai simplă soluție. Este integrat în Excel (fila „Date”, începând cu Excel 2016) și se actualizează automat, astfel încât luna viitoare poți introduce date noi și să dai un singur clic, în loc să refaci totul de la zero.

Documentația oficială a Microsoft acoperă noțiunile de bază privind combinarea datelor din mai multe foi de calcul, dar procesul de adăugare se desfășoară mai exact astfel.

Mai întâi, transformați fiecare foaie de calcul într-un tabel corespunzător. Faceți clic oriunde în datele dvs., apăsați Ctrl+T, bifați caseta de selectare „Antete”. Faceți acest lucru pentru ambele foi de calcul. Denumiți-le cu un nume pe care îl veți recunoaște, cum ar fi „ tblJan ” și „ tblFeb ”, în secțiunea „Proiectare tabel”.

Apoi încărcați fiecare tabel în Power Query. Cu cursorul în primul tabel, accesați „Date” > Din „Tabel/Interval”. Se deschide editorul. Nu este nevoie să modificați nimic aici – închideți-l pur și simplu și încărcați-l doar ca conexiune (Fișier > Închide și încarcă în > (Doar „Creați conexiune”). Repetați procedura pentru al doilea tabel.

Acum, adăugarea propriu-zisă. Date > Obținere date > Combinarea interogărilor > Adăugare. Alegeți cele două interogări, apoi faceți clic pe OK. Power Query așează rândurile celui de-al doilea tabel chiar sub cele ale primului, potrivind coloanele în funcție de numele anteturilor.

Încărcați rezultatul într-o foaie nouă. Gata. Iar frumusețea abordării Power Query pentru adăugarea de date în Excel constă în actualizare. Data viitoare când primiți fișiere actualizate, lipiți rândurile noi în tabelele sursă și accesați Data > Refresh All. Tabelul combinat se actualizează automat.

„Adăugarea nu are absolut nimic de-a face cu formulele. Este vorba despre a-i spune programului Excel că aceste două seturi sunt de același tip, apoi a-l lăsa să le cumuleze pentru tine în fiecare lună, la cerere.”

Există un ghid detaliat pentru versiunea cu mai multe foi de calcul în ghidul Trump Excel privind combinarea foilor de calcul, dacă ai mai mult de două fișiere de integrat. Logica este aceeași, doar că lista de adăugare conține mai multe interogări.

Sarcina nr. 2: Potrivirea coloanelor cu XLOOKUP sau INDEX/MATCH

Această sarcină este de altă natură. Cele două foi de calcul descriu aceleași înregistrări, dar conțin coloane diferite referitoare la acestea. Foaia de calcul nr. 1 conține ID-urile comenzilor și sumele. Foaia de calcul nr. 2 conține aceleași ID-uri ale comenzilor, plus adresa de e-mail și regiunea clientului. Doriți să obțineți un singur tabel în care toate aceste informații să fie afișate una lângă alta.

Este vorba despre o situație în care se utilizează funcția VLOOKUP între foi de calcul, cu excepția faptului că VLOOKUP este metoda veche și instabilă. Funcția XLOOKUP a înlocuit-o pentru utilizatorii de Microsoft 365 sau Excel 2021 și merită să o folosiți în locul acesteia.

Să presupunem că tabelul principal se află în Foaia1, cu ID-urile comenzilor în coloana A. Adresele de e-mail pe care le cauți se află în Foaia2, cu ID-urile în coloana A și adresele de e-mail în coloana B. În Foaia1, celula D2:

=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B, "Not found")

Adică: ia ID-ul din A2, caută-l în coloana „ID” din Foaia2 și returnează adresa de e-mail corespunzătoare. Ultimul argument, „ "Not found" ”, este cel care apare atunci când nu există nicio potrivire – ceea ce este mult mai plăcut decât urâtul mesaj „ #N/A ” din VLOOKUP, care te privește fix.

Trage-l în jos pe coloană și fiecare rând va afișa elementul corespunzător. Simplu.

Folosiți o versiune mai veche de Excel care nu are funcția XLOOKUP? Combinația INDEX/MATCH îndeplinește aceeași funcție:

=INDEX(Sheet2!B:B, MATCH(A2, Sheet2!A:A, 0))

Faptul că se folosește operatorul „ 0 ” în funcția MATCH impune o potrivire exactă, ceea ce este aproape întotdeauna ceea ce doriți în cazul ID-urilor. Dacă omiteți acest operator, Excel efectuează o potrivire aproximativă, care returnează în mod silențios valori eronate în cazul datelor nesortate. O eroare silențioasă clasică.

De știut: Power Query poate realiza și această operațiune de potrivire, folosind funcția „Merge” în loc de „Append”. Pentru un raport punctual, este mai rapid să scrii o formulă. Pentru un raport pe care îl vei genera lunar și care conține mii de rânduri, funcția „Merge” din Power Query se adaptează mai bine la volum și nu va încetini fișierul la fel de mult cum ar face-o zece mii de formule XLOOKUP.

Când doriți cu adevărat să consolidați datele

Există o a treia opțiune, mai puțin cunoscută, pe care oamenii o uită. Dacă obiectivul dvs. este să consolidați datele în stilul Excel – adică doriți ca sumele sau mediile să fie calculate la nivel de foi de calcul, în loc să se păstreze fiecare rând brut – Excel dispune de un instrument dedicat în acest sens. Date > Consolidare.

Selectează ambele intervale, alege funcția dorită (Sumă, Medie, Număr) și datele vor fi agregate pe baza etichetelor corespunzătoare din rânduri și coloane. Ghidul pas cu pas al Spreadsheeto privind funcția „Consolidare” prezintă în detaliu această fereastră de dialog. Este alegerea potrivită atunci când nu aveți nevoie de detaliile de bază, ci doar de totaluri – cum ar fi combinarea cifrelor de vânzări regionale într-un singur rezumat, de exemplu.

Totuși, nu apela la „Consolidate” atunci când, de fapt, ai nevoie ca fiecare rând să rămână intact. Această funcție grupează datele, iar tu pierzi granularitatea. Este o operațiune de adăugare folosită în mod greșit.

Depanarea combinei

Lucrurile merg prost în moduri previzibile. Iată ce se strică de obicei și cum se remediază.

Apariția unor antete duplicate după o adăugare înseamnă aproape întotdeauna că numele coloanelor din cele două tabele sursă nu se potrivesc. Reveniți, redenumiți-le astfel încât să se potrivească exact – inclusiv spațiile și majusculele – apoi reîmprospătați pagina.

Căutările care returnează „ #N/A ” pentru rânduri despre care știi că există? În nouă cazuri din zece, este vorba de o neconcordanță între tipurile de date. O foaie de calcul stochează ID-ul ca text, iar cealaltă ca număr. Pe ecran arată identic, dar Excel tratează „1024” și 1024 ca fiind lucruri diferite. Convertește una dintre ele, astfel încât ambele să corespundă.

Rândurile goale suplimentare care se strecoară în tabelul combinat provin, de obicei, din rândurile goale din intervalul de date inițial, care au fost incluse din greșeală. Filtrează-le la sursă înainte de combinare.

Iar dacă fișierul tău începe să funcționeze foarte lent după o operațiune mare de căutare, acesta este semnalul tău să treci la Power Query. Mii de formule volatile care se recalculează la fiecare editare vor pune în genunchi chiar și un computer performant. Ablebits oferă o prezentare generală bună a scenariilor de îmbinare mai complexe, dacă lucrezi cu mai multe registre de lucru de dimensiuni mari.

Întrebări frecvente

Ar trebui să folosesc Power Query sau o formulă pentru a combina două foi de calcul?
Depinde dacă este vorba de o operațiune punctuală sau recurentă. Pentru un raport unic, pe care nu-l vei mai folosi niciodată, o formulă se scrie mai repede. Pentru orice raport pe care îl refaci lunar, Power Query este mai avantajos, deoarece se actualizează cu un singur clic, în loc să fii nevoit să refaci formulele de fiecare dată.

Care este diferența dintre „appending” și „merging”?
„Appending” înseamnă alinierea rândurilor unul sub altul atunci când ambele foi de calcul conțin același tip de înregistrări. „Merging” (sau „looking up”) înseamnă potrivirea coloanelor una lângă alta folosind un ID comun, atunci când foile de calcul conțin detalii diferite despre aceleași înregistrări. Sunt operațiuni complet diferite.

Este XLOOKUP mai bun decât VLOOKUP pentru combinarea foilor de calcul?
Da, dacă îl aveți. Funcția XLOOKUP gestionează un mesaj încorporat de „nu s-a găsit”, caută la stânga sau la dreapta și nu se va defecta atunci când cineva inserează o coloană. Funcția VLOOKUP este potrivită pentru fișierele vechi, dar nu există niciun motiv să începeți o lucrare nouă cu ea în Excel 2021 sau 365.

De ce căutarea mea returnează erori când datele se potrivesc în mod evident?
Aproape întotdeauna este vorba de o neconcordanță între tipurile de date. O foaie de calcul stochează cheia ca text, iar cealaltă ca număr. Ele arată la fel, dar Excel nu le tratează ca fiind identice. Unificați formatul în ambele cazuri și problemele de potrivire se vor rezolva.

Pot combina foi de calcul din două registre de lucru diferite?
Da. Power Query se conectează la fișiere externe prin intermediul opțiunilor „Date” > „Obține date” > „Din fișier” > „Din registru de lucru”. Pașii de adăugare și îmbinare funcționează exact la fel odată ce ambele interogări sunt încărcate.

Concluzii privind

funcția „
” Combinarea a două foi de calcul nu este dificilă odată ce nu o mai tratezi ca pe o singură problemă. Pune-ți mai întâi o singură întrebare: sunt înregistrări identice suprapuse sau ID-uri identice care se potrivesc? Pentru primul caz, folosește funcția „Append” din Power Query; pentru al doilea, folosește XLOOKUP sau INDEX/MATCH; iar pentru a obține doar totalurile, folosește funcția „Consolidate”. Când combini date din două foi de calcul în Excel, analizează situația, apoi creează formula. Această ordine îți va economisi o după-amiază întreagă.