Consolidare Excel (cuprins)

  • Introducere în consolidarea în Excel
  • Cum să consolidezi datele în mai multe foi de lucru?

Consolidarea datelor în Excel

Consolidarea în general înseamnă colectarea sau adunarea unor lucruri comune într-un singur loc. Ar putea fi orice fel de date sau ceva de genul informații sau număr. Consolidarea în Excel se referă la o funcție care combină informații sau date din diferite cărți de lucru / fișe de lucru într-un singur fișă de lucru / carte de lucru. Cu alte cuvinte, această funcție preia date din diferite foi de lucru pentru a le combina într-o singură foaie de lucru, care poate fi actualizată cu ușurință.

De exemplu, dacă aveți patru foi de lucru. Fiecare foaie are cheltuieli pentru un sfert. Este posibil să aveți nevoie de o singură foaie cu cheltuielile din toate cele patru sferturi. Acest lucru este atunci când funcția de consolidare intră în joc. Când datele din foile de lucru sunt în aceeași poziție, ceea ce înseamnă sub aceleași etichete și în aceeași ordine. Folosim „Consolidare după poziție”. Și când datele din foile de lucru nu se află sub aceleași etichete și nu sunt în aceeași ordine. Apoi, folosim „Consolidare după categorie”. Este la fel ca și crearea unei tabele Pivot.

Cum să consolidezi datele în mai multe foi de lucru?

Să înțelegem cum să consolidăm datele în mai multe foi de lucru cu câteva exemple.

Exemplul # 1 - Consolidarea datelor din același registru de lucru

Să presupunem că avem o foaie de lucru în care avem date de vânzări cu patru foi de lucru marcate în funcție de regiunea lor ca Nord, Sud, Est și Vest. Acum am dori să consolidăm datele într-un singur loc, mai degrabă într-o foaie din același registru de lucru. Există oa cincea foaie numită fișier consolidat.

Acest exemplu va arăta vânzările consolidate pentru toate regiunile. Iată exemple de fișiere de date. Aici puteți vedea diferite denumiri ale foii de lucru și ultimul fișier consolidat la care trebuie să lucrăm.

Acesta este șablonul nostru din „fișier consolidat” și acum vom începe consolidarea datelor din foile de lucru.

Vom face clic acum pe celula B3.

Unde dorim ca funcția „Consolidare” să insereze datele din alte foi. După cum putem vedea mai sus, celula B3 este selectată și acum vom trece la fila Date din Panglica Excel și vom merge la Consolidare.

După ce facem clic pe Consolidare, va apărea fereastra de mai jos:

Primul lucru pe care îl analizăm este funcția derulantă „Funcție”, care arată multe funcții aritmetice, cum ar fi suma, numărul, maximul, media, etc. De vreme ce dorim o sumă de vânzări, vom selecta „Suma” în lista verticală. Acum, vom merge la fila de referință pentru a adăuga o referință la datele noastre din diferite foi de lucru. Vom merge apoi la prima noastră foaie care conține datele de vânzări pentru North. Trebuie doar să selectăm datele de vânzări și nu rubricile și rândurile. Acest lucru este arătat mai jos.

Referința este afișată în caseta „Referință” astfel.

Faceți clic pe „Adăugare” și referința va fi adăugată în caseta „Toate referințele”.

În același mod, acum vom adăuga o referință din toate celelalte foi, cum ar fi Nord, Sud, Est și Vest. După ce am terminat de adăugat referințele, faceți clic pe „OK”.

Acum avem date consolidate pentru vânzări pentru executivi, lunar, la nivel național.

Exemplul # 2 - Consolidă înțelepciune anuală de produse de vânzări

În acest sens, avem vânzări trimestriale pentru produsele A, B, C și D și dorim un vânzări consolidat anual pentru produse.

Aici avem date în patru foi, prima foaie cu date de vânzări pentru primul trimestru, urmează date pentru T2 urmată de date pentru Q3 și Q4. Vom merge acum la fila de referință pentru a adăuga referințe la datele noastre din diferite foi de lucru. Vom merge la prima noastră foaie care conține datele de vânzări pentru primul trimestru. Vom selecta datele așa cum se arată mai jos.

Acum, vom merge la fila Date și vom face clic pe Consolidare și va apărea fereastra de mai jos.

Vom face clic pe „Adăugare” și referința va fi adăugată în caseta „Toate referința”.

Vom face clic pe „Adăugare” și referința va fi adăugată în caseta „Toate referința”.

Vom face clic pe „Adăugare” și referința va fi adăugată în caseta „Toate referința”.

Mai jos este șablonul nostru pentru fișa de date consolidată. Acum vom selecta celula B2 pentru a obține datele totale de vânzări din alte foi.

Vom selecta funcția „Suma” din meniul derulant. De asemenea, vom adăuga referințe din fișele Q2, Q3 și Q4. Va apărea astfel. Toate referințele din toate foile sunt acum adăugate. Faceți clic pe „OK”

Avem acum datele noastre consolidate de vânzări cu suma totală pentru fiecare produs.

În caz, dacă dorim să introducem tabelul consolidat într-o foaie de lucru goală în loc să facem un tabel de șabloane. Putem face și asta. Luând datele anterioare ale eșantionului, vom face consolidarea de mai jos.

Nu avem un șablon pentru tabelul de consolidare și dorim să obținem date consolidate într-o foaie de lucru goală cu etichete de rând și coloană. Vom adăuga o nouă foaie de lucru, în cazul nostru este „Fișier consolidat”.

Acum ca înainte vom merge la fila Date

Vom selecta acum referința din fișele noastre de date. Vom începe acest lucru cu foaia „Nord” și apoi vom continua cu foile „Sud”, „Est” și „Vest”. Vom selecta datele așa cum se arată mai jos, care includ etichete de rând și coloană.

Vom adăuga apoi referința în caseta „Toate referințele”:

Acum adăugați toate referințele în același mod din toate fișele tehnice.

Acum vom verifica atât „Rândul de sus”, cât și „Coloana din stânga”, apoi vom apăsa OK.

Acum vedem o informație consolidată de vânzări cu etichete pe rând și pe coloane.

Dacă doriți să actualizați datele consolidate atunci când foaia individuală este actualizată, faceți clic pe caseta „Creare legături pentru a crea date”. Dacă doriți să actualizați manual datele, atunci nu bifați caseta și faceți clic pe OK.

Celulele din tabelul consolidat conțin acum formula sumei așa cum se arată mai jos. Acest lucru îl face dinamic în natură.

Acesta este un tabel de consolidare dinamic.

Exemplul # 3 - Consolidarea datelor din diferite cărți de lucru

Dacă avem date în diferite cărți de lucru și dorim să le consolidăm într-un singur manual de lucru. Putem face și asta. Acest lucru ne oferă o mare flexibilitate și ușurință.

Avem date de vânzări trimestriale ale diferitelor produse, spun A, B, C și D în cărți de lucru diferite, așa cum se arată mai jos.

Odată ce toate cărțile de lucru sunt deschise, vom merge la un nou manual și vom face clic pe celula B3. Adăugați referințele din toate foile de lucru de mai jos.

Am consolidat datele din toate manualele într-un nou manual.

Mai mult, orice modificări ale datelor din orice cărți de lucru vor fi, de asemenea, actualizate în noul registru de date consolidat.

Astfel, am învățat cum să utilizăm funcția de consolidare în Excel cu ajutorul unor exemple. Este o funcție foarte utilă în contopirea sau colectarea datelor într-o singură foaie din fișele de lucru sau cărțile de lucru diferite.

Lucruri de reținut despre consolidare în Excel

  • Aveți grijă să selectați datele de referință când bifați casetele pentru „Rândul de sus” și „Coloana din stânga”. Apoi, va trebui să selectați datele complete, inclusiv etichetele de rând și coloană.
  • Când consolidați date de natură dinamică din fișe de lucru și cărți de lucru diferite. Verificați „creați link-uri către datele sursă” și acest lucru va actualiza automat modificările datelor.

Articole recomandate

Acesta este un ghid pentru Consolidarea în Excel. Aici vom discuta Cum să consolidezi datele în mai multe foi de lucru împreună cu exemple practice și șablon excel descărcabil. De asemenea, puteți parcurge și alte articole conexe -

  1. Formula de stânga în Excel
  2. Urmăriți fereastra în Excel
  3. Cum să adăugați și să folosiți Slicer în Excel?
  4. Exemple VLOOKUP în Excel

Categorie:

Sfaturi Excel