Cum să îmbini mai multe foi Excel într-una singură: toate opțiunile

Dacă lucrezi des cu multe foi de calcul, probabil te-ai trezit copiend și lipind ca și cum nu ar exista un mâine pentru a aduna informații împrăștiate pe mai multe file. Îmbinați mai multe foi Excel într-una singură Nu numai că economisește timp: reduce și erorile, simplifică raportarea și vă permite să analizați datele mult mai ușor.

Vestea bună este că nu ești limitat la copierea și lipirea tipică. Excel și unele instrumente externe oferă mai multe metode Pentru a combina foi din același fișier, din mai multe registre de lucru diferite, cu sau fără duplicate și chiar pe baza unor coloane comune, cum ar fi un identificator de produs. Vom analiza, pas cu pas, toate alternativele practice și ce este cel mai bine de utilizat în fiecare caz.

Ce opțiuni aveți pentru combinarea mai multor foi Excel?

Înainte de a aprofunda pașii, este util să avem o imagine de ansamblu clară. Există abordări foarte diferite pentru unificarea foilor de calculDe la funcții interne Excel (Consolidate, Power Query) la macrocomenzi VBA sau servicii web externe și soluții de automatizare precum Coupler.io sau ASPOSE.

Cea mai bună metodă va depinde de dacă tabelele au aceeași structură, dacă se află în același fișier sau în foldere diferite și de cât de bine știți să utilizați Excel. Nu este același lucru să aduni totaluri numerice pentru un raport de management pentru a lega tabelele de vânzări și financiare printr-o coloană comună pentru a efectua o analiză detaliată.

Combinați datele din mai multe foi de calcul folosind funcția Consolidare din Excel

Una dintre cele mai clasice și mai puțin cunoscute metode de a aduna informații este utilizarea comenzii Consolidate din Excel. Consolidarea vă permite să rezumați date numerice împrăștiate pe mai multe foi de lucru sau registre de lucru. și grupați-le pe o foaie „principală” cu totaluri, medii, numărări etc.

Imaginează-ți că ții evidența cheltuielilor pe sucursale: o foaie per birou, toate cu un tabel de cheltuieli similar. Cu consolidarea, puteți construi o fișă corporativă în doar câteva clicuri. care colectează date din toate locațiile și calculează sume, medii, stocuri sau orice altă metrică de care aveți nevoie.

Excel oferă două moduri de consolidare: după poziție y pe categoriiAlegerea depinde de modul în care sunt organizate intervalele pe diferitele foi: dacă celulele sunt aliniate la aceleași coordonate sau dacă vă bazați pe etichete de rând și coloană.

Consolidare după poziție: când toate foile sunt „așezate” la fel

Consolidarea pozițională funcționează foarte bine atunci când Toate foile au același design de tabelaceleași coloane în aceeași ordine, aceleași rânduri în aceeași secvență și fără spații libere în lista de date.

Pentru a vă asigura că acest sistem funcționează fără probleme, examinați fiecare fișă sursă și Asigurați-vă că intervalele de date ocupă exact aceleași celule În toate, fără rânduri sau coloane goale între ele. Este ca și cum ai suprapune mai multe folii transparente: Excel va însuma sau va procesa datele care se încadrează în aceeași „celulă”.

El proces general este aceasta:

• Deschideți toate foile sau cărțile sursă cu datele pe care doriți să le adăugați sau să le rezumați și verificați dacă tabelul este aliniat în fiecare dintre ele.
• În registrul de lucru în care doriți să afișați totalul, accesați foaia de destinație și selectați celula din stânga sus unde vrei să ajung cizmă tabelul consolidat.
• În fila de date, introduceți instrumentul corespunzător (în versiunile în spaniolă este de obicei Date > Consolidare).
• În fereastra Consolidare, alegeți în caseta Funcție Dacă vrei să aduni, să faci o medie, să numeri etc.
• Pentru fiecare foaie sursă, selectați intervalul care conține datele care urmează să fie consolidate; Excel va adăuga fiecare referință în listă. de intervale care alcătuiesc consolidarea.
• După ce ați inclus toate zonele, apăsați OK pentru ca Excel să creeze tabelul combinat cu rezultatele.

Este o metodă foarte convenabilă dacă aveți nevoie, de exemplu, obțineți un rezumat numeric al mai multor foi periodice (luni, delegații, proiecte) care au o structură identică.

Consolidați pe categorii: când etichetele contează, nu poziția

Celălalt mod de consolidare funcționează cel mai bine atunci când Nu toate foile au rânduri și coloane în aceeași ordine.Dar au aceleași etichete. Aici, Excel folosește anteturile de rând și de coloană pentru a determina ce să adauge la ce.

Această abordare este ideală dacă tabelele sunt liste de date fără spații goale, dar cu mici diferențe în ordine, cu condiția ca numele categoriilor sunt scrise în mod consecventDacă puneți „Prom.” într-o foaie și „Medie” în alta, Excel le va trata ca și câmpuri separate și nu le va combina.

Pentru a utiliza Consolidare pe categorii:

• Deschideți toate foile sursă care conțin datele ce urmează a fi grupate.
• Pe foaia în care doriți să vedeți rezumatul, selectați din nou celula din stânga sus a intervalului de ieșire.
• Accesați din nou Date > Consolidare și alegeți funcția dorită (sumă, medie etc.).
• Bifați căsuțele care indică locul în care se află etichetele: Rândul de sus, Coloana din stânga sau ambele, în funcție de titlurile dvs.
• Pentru fiecare foaie sursă, selectați intervalul de date, inclusiv rândurile sau coloanele unde se află etichetele, astfel încât Excel poate recunoaște categoriile.
• După ce toate intervalele au fost adăugate, confirmați cu OK pentru a genera tabelul consolidat.

În acest fel poți îmbină date numerice care au categorii comune, chiar dacă nu sunt perfect aliniate pe toate foile, atâta timp cât numele coloanelor și rândurilor sunt consecvente.

Combinați manual mai multe foi Excel din programul în sine.

Dacă ceea ce vrei nu este atât de mult să consolidezi valorile, ci a avea într-o singură carte mai multe foi care se află în prezent în fișiere separateExcel facilitează, de asemenea, munca relativ ușoară cu opțiunile de mutare sau copiere a foilor de calcul.

Cea mai simplă metodă implică deschiderea fișierelor implicate și, din foaia pe care doriți să o duplicați, Folosește opțiunea de formatare pentru a muta sau copiaEste o abordare simplă care nu necesită formule sau automatizare, perfectă atunci când există doar câteva foi.

PașiiÎn termeni generali, acestea sunt:

• Deschideți toate foile Excel pe care doriți să le combinați într-un singur registru de lucru.
• Plasați cursorul pe fila foii pe care doriți să o copiați. spre o altă carte.
• Accesați fila Acasă și, în grupul Celule, introduceți opțiunea Format, unde apare comanda pentru mutați sau copiați foaia.
• În caseta care va apărea veți putea alege Cărei cărți ar trebui trimisă foaia? (una deja deschisă sau o carte nouă care va fi creată) și în ce poziție să fie plasată.
• Asigurați-vă că bifați caseta Creați o copie dacă doriți ca foaia originală să rămână în fișierul sursă.

Când paginile pe care doriți să le puneți împreună sunt deja toate în aceeași carte, puteți, de asemenea, să selectați mai multe file simultan Folosind Ctrl (pentru a selecta foi individuale) sau Shift (pentru intervale consecutive) și repetând procesul de mutare sau copiere, puteți compila foi diferite din mai multe registre de lucru într-un singur fișier fără a pierde originalele.

Îmbinarea mai multor foi din fișiere diferite folosind cod VBA

Când datele sunt dispersate pe mai multe fișiere și doriți să automatizați procesul, VBA (Visual Basic for Applications) este un briceag elvețian foarte puternicCu o macrocomandă, puteți deschide un folder plin de registre de lucru Excel și puteți copia toate foile acestora într-un fișier principal în câteva secunde.

Dar să nu ne amăgim: Utilizarea macrocomenzilor necesită un anumit nivel de cunoștințe despre cod.Nu trebuie să fii programator profesionist, dar trebuie să știi cum să deschizi editorul Visual Basic, să lipești o macrocomandă și să o rulezi fără teamă.

Un exemplu tipic de macrocomandă pentru îmbinarea foilor din mai multe fișiere face următoarele: deschide o casetă de selectare a fișierelor, vă permite să alegeți mai multe registre de lucru, Deschideți fiecare fișier, derulați prin toate paginile sale și copiați-le. la sfârșitul cărții principale și, după terminare, închideți cărțile sursă.

Fluxul de lucru Este similar cu următorul:

• Creați o carte nouă care va acționa ca fișier „master”.
• presa ALT + F11 pentru a deschide Editorul Visual Basic.
• Introduceți un modul Nou și lipiți codul macro care va gestiona parcurgerea cărților și copierea foilor.
• Salvați fișierul ca registru de lucru activat pentru macrocomenzi și Executați macrocomanda (F5) de la editor.
• Când apare exploratorul de fișiere, selectați fișierele Excel care conțin foile de calcul care urmează să fie îmbinate și acceptați.

După câteva momente, veți vedea asta în cartea dvs. principală Au apărut toate foile din fișierele selectate., plasate unul după altul. De acolo puteți rearanja, redenumi sau curăța orice aveți nevoie.

O altă variantă utilizată pe scară largă este scenariu VBA care Scanează automat toate fișierele .xlsx dintr-un anumit folderLe deschide una câte una și le copiază foile în registrul de lucru principal. Aceasta este o abordare fantastică atunci când veți repeta operațiunea frecvent și veți salva întotdeauna rapoartele în același folder.

Afișarea datelor din mai multe foi într-o foaie principală utilizând instrumentul Consolidare

Pe lângă copierea unor pagini întregi, adesea ceea ce căutați este să aveți o singură foaie principală cu date sumarizate din mai multe foifără a fi nevoie să manipulați manual fiecare filă. Aici intervine din nou funcția de consolidare a Excel.

Ideea este să aveți fișele detaliate separat (de exemplu, una pe lună sau una pe domeniu de activitate) și construiți un tabel pe foaia principală care să reflecte suma, media sau numărul tuturor acestoraAceastă fișă principală poate fi baza pentru diagrame, tablouri de bord sau rapoarte periodice.

În funcție de modul în care sunt organizate datele dvs., puteți alege consolidarea. după poziție sau prin categorieașa cum am văzut anterior. Important este ca intervalele sursă să fie definite ca liste curate (fără rânduri sau coloane goale între ele) și ca etichetele și structurile sunt consistente între diferitele frunze.

Această abordare este utilă în special atunci când gestionați volume mari de informații și aveți nevoie doar de Foaia principală ar trebui să conțină un rezumat, nu toate detaliile rând cu rând.Totuși, puteți păstra oricând foile originale cu detaliile în cazul în care va trebui să aprofundați informațiile mai târziu.

Îmbinarea mai multor foi fără duplicate: combinarea și curățarea

O altă situație foarte frecventă este atunci când îmbinați mai multe foi cu înregistrări similare (de exemplu, liste de clienți, produse sau tranzacții) și Vedeți rânduri repetate După ce pui totul cap la cap. Excel nu are un buton magic care combină foile și elimină duplicatele în același timp, dar poți conecta două instrumente pentru a realiza acest lucru.

Ideea este clară: Mai întâi combini frunzele (folosind Copiere și Lipire, Power Query, Coupler.io sau metoda preferată) pentru a avea toate datele împreună într-un singur tabel; odată ce acest lucru este făcut, treceți la Curățați tabelul folosind opțiunea Eliminare duplicate din Excel-ul în sine.

Să o faci de la Opțiuni:

• Selectați tabelul în care aveți datele îmbinate.
• Accesați fila De date și în grupul Instrumente de date, faceți clic pe eliminați duplicatele.
• În fereastra care apare, bifați sau debifați. Datele mele au antete după caz, astfel încât Excel să poată distinge corect anteturile.
• Alegeți coloanele pe care doriți să le utilizați pentru a detecta înregistrările duplicate; dacă selectați toate coloaneleDoar rândurile care corespund tuturor câmpurilor vor fi șterse.
• Acceptă și Excel îți va arăta câte duplicate a eliminat și câte valori unice au rămas.

Cu această combinație de fuziune și curățare, Ai ajuns la o singură masă rafinată, fără repetiții., perfect pentru rapoarte, tabele pivot sau exporturi către alte sisteme.

Îmbinarea foilor Excel pe baza unei coloane comune

În multe scenarii din lumea reală nu doriți doar să stivuiți înregistrări, ci legarea informațiilor suplimentare răspândite în diferite tabeleDe exemplu, un tabel financiar cu sume asociate unui ID de produs și un alt tabel de vânzări cu detalii despre același produs; lucrul logic este să doriți să le combinați folosind acea cheie comună.

Există două modalități principale de a face acest lucru într-un mediu de utilizator: folosind un instrument extern precum Coupler.io, care automatizează îmbinarea sau folosind Power Querycare este integrat în Excel-ul modern și permite, de asemenea, joncțiuni între tabele de tip bază de date.

Metodă care folosește Coupler.io pentru a uni foile de calcul printr-o coloană

Coupler.io este o soluție bazată pe cloud concepută pentru automatizați rapoartele și mutați datele între diferite sursePrintre funcțiile sale, vă permite să importați mai multe foi Excel, să le combinați și să încărcați rezultatul într-o altă foaie sau chiar în instrumente de analiză precum BigQuery sau Looker Studio.

Dacă doriți să îmbinați, de exemplu, o foaie numită „Tabel financiar” și o alta numită „Tabel vânzări” pe baza coloanei ID-ul de produsFluxul tipic ar fi acesta:

• La Coupler.io alegi ambele origine, precum și destinație Microsoft Excel în forma inițială și continuați.
• Îți conectezi contul Microsoft, selectezi fișierul Excel și alegi foaia ca primă sursă. Tabelul financiar.
• Folosești opțiunea de conectați o altă sursă pentru a adăuga a doua foaie, în acest caz Tabel de vânzări, din același fișier sau din altul.
• În etapa de configurare a combinației, alegeți tipul de operațiune: Adăuga (pentru a stivui tabele cu aceeași structură) sau Alatura-te (pentru a potrivi datele folosind o coloană cu identificator unic).
• Selectați Alatura-te și definiți „ID produs” ca și coloană cheie în ambele foi.
• Revizuiți previzualizarea datelor unite și ajustați coloanele pe care doriți să le păstrați.

Apoi, în secțiunea destinație, alegeți fișierul și foaia Excel în care va fi salvat tabelul combinat. Puteți programa actualizări automate astfel încât Coupler.io să actualizeze datele periodic, fără a fi nevoie să repetați întregul proces manual.

Unirea foilor cu Power Query pe baza unei coloane comune

Dacă preferați să rămâneți în ecosistemul Excel, Power Query este instrumentul perfect pentru combinarea tabelelor bazat pe coloane corespondente. Este un add-in gratuit în Excel 2010 și 2013 și o funcție încorporată începând cu Excel 2016.

Să presupunem din nou că aveți două tabele: unul în foaia „Tabel financiar” și altul în foaia „Tabel vânzări”, ambele cu coloana „ID produs”. Scopul este de a crea un singur tabel care să combine datele din ambele pe baza acelui identificator.

Procesul de Poate fi împărțit în mai multe etape:

Pasul 1: Creați conexiunile Power Query

• În registrul de lucru Excel, localizați primul tabel (de exemplu, într-o celulă din foaia „Tabel vânzări”).
• În fila Date, în secțiunea Obține și transformă, alegeți opțiunea Din tabel sau interval pentru a încărca tabelul respectiv în Power Query.
• În caseta care se deschide, bifați sau debifați Tabelul meu are antete după caz; în majoritatea cazurilor, veți lăsa această căsuță bifată.
• Se va deschide editorul Power Query. Dacă nu doriți încă să încărcați tabelul în registrul de lucru, selectați Închideți și încărcați… și verificați opțiunea Doar creează o conexiune.
• Repetați procesul cu cealaltă foaie (de exemplu, „Tabel financiar”) pentru a crea conexiunea corespunzătoare. În panoul de interogări al cărții, veți vedea acum ambele conexiuni..

Pasul 2: Îmbinarea interogărilor cu o coloană comună

• În Excel, selectați oricare dintre interogări și accesați Date > Interogare nouă (sau direct din panoul de interogări) > Combinați interogările > combină.
• În fereastra de îmbinare, alegeți „Tabel financiar” ca prim tabel și „Tabel vânzări” ca al doilea.
• În fiecare tabel, Faceți clic pe coloana „ID produs” pentru a-l marca ca un câmp corespondent; veți vedea că este evidențiat atunci când selecția este reușită.
• Lăsați tipul de îmbinare implicit sau ajustați-l după nevoile dvs. (de exemplu, îmbinare internă, îmbinare stângă etc.).
• Acceptați, astfel încât Power Query să genereze o nouă interogare cu o coloană suplimentară care conține, pentru fiecare rând, „Tabelul de vânzări” aferent.

Pasul 3: Extindeți coloanele tabelului combinat

• În editorul Power Query, veți vedea o coloană cu numele celui de-al doilea tabel, care conține cuvântul „Tabel” pe fiecare rând.
• Faceți clic pe pictograma cu cele două săgeți lângă numele coloanei respective pentru a o extinde.
• În caseta pop-up, selectați coloanele din „Tabelul de vânzări” pe care doriți să le adăugați la combinație (de exemplu, „Articole” și „Sumă ($)”).
• Dacă nu doriți ca numele original al tabelului să apară ca prefix în câmpuri, Debifați caseta pentru a utiliza numele original al coloanei ca prefix și acceptă.

Pasul 4: Încărcați tabelul îmbinat în Excel

• Acum, cu tabelul combinat și curățat în Editorul Power Query, accesați Închideți și încărcați….
• Alegeți dacă doriți să încărcați datele ca tabel într-o foaie nouă sau pe o foaie existentă, în funcție de modul în care doriți să vă organizați cartea.

Când ați terminat, veți avea un tabel gata în Excel cu datele din ambele foi legate prin coloana comună, cu toate câmpurile selectate în pasul de extindere.

Utilizați interogările și conexiunile pentru a uni foi din același fișier

Mulți utilizatori descoperă aproape din întâmplare că Funcția Interogări și conexiuni din Excel vă permite să combinați cu ușurință mai multe foi de calcul. din același fișier, atâta timp cât au aceeași structură. Această funcție se bazează pe Power Query, dar interfața este destul de ușor de utilizat.

Dacă, de exemplu, aveți trei foi cu aceleași coloane (Data – Articol – Valoare) și doriți Listați toate înregistrările dintr-un singur tabel, sortate după datăAceastă abordare este foarte practică și flexibilă.

Pașii tipici ar:

• Pe fila Date, alegeți Interogare nouă (sau „Obțineți date”, în funcție de versiunea dvs.) > Din fișier > Din carte.
• Selectați fișierul Excel care conține foile pe care doriți să le combinați; Ar putea fi chiar cartea la care lucrezi..
• În browser, bifați caseta pentru Selectați mai multe articole și marcați toate foile pe care doriți să le includeți.
• Faceți clic pe Transformarea datelor pentru a deschide tabelele în editorul Power Query.
• În fiecare interogare, eliminați coloanele de care nu aveți nevoie și Asigurați-vă că toate tabelele au exact aceeași structură (aceleași coloane, același tip de date).
• Utilizați opțiunile meniului principal din Power Query pentru a adăugați interogărileastfel încât în ​​final să aveți o singură interogare care conține rândurile din toate foile de calcul.
• Când interogarea finală este gata, apăsați Închideți și încărcați astfel încât să fie adăugat în carte ca tabel pe o foaie nouă.

De acolo puteți sorta după dată, aplica culori în funcție de sursa de date sau chiar Adăugați o coloană care identifică din ce foaie provine fiecare înregistrare.ceea ce este foarte util pentru analize mai avansate.