Excel konsolidacija (vsebina)
- Uvod v konsolidacijo v Excelu
- Kako združiti podatke v več delovnih listih?
Konsolidirajte podatke v Excelu
Utrditev na splošno pomeni zbiranje ali zbiranje nekaterih skupnih stvari na enem mestu. To bi lahko bili kakršni koli podatki ali kaj podobnega, informacije ali številke. Konsolidacija v Excelu se nanaša na funkcijo, ki združuje informacije ali podatke iz različnih delovnih zvezkov / delovnih listov v en delovni list / delovni zvezek. Z drugimi besedami, ta funkcija sprejme podatke iz različnih delovnih listov, da jih združi v en sam delovni list, ki ga je mogoče enostavno posodobiti.
Na primer, če imate štiri delovne liste. Vsak list ima stroške za četrtino. Morda boste potrebovali en list s stroški iz vseh štirih četrtletij. Takrat se začne uporabljati funkcija konsolidacije. Kadar so podatki na delovnih listih v istem položaju, kar pomeni pod istimi nalepkami in v istem zaporedju. Uporabljamo konsolidacijo po položaju. In kadar podatki na delovnih listih niso pod istimi nalepkami in ne v istem vrstnem redu. Nato uporabimo konsolidacijo po kategorijah. To je tako kot ustvarjanje vrtilne tabele.
Kako združiti podatke v več delovnih listih?
Razjasnimo, kako združiti podatke v več delovnih listih z nekaj primeri.
Primer # 1 - Konsolidirajte podatke v istem delovnem zvezku
Recimo, da imamo delovni list, v katerem imamo podatke o prodaji s štirimi delovnimi listi, ki so glede na njihovo regijo označeni kot sever, jug, vzhod in zahod. Zdaj bi radi podatke združili na enem mestu in ne v listu v istem delovnem zvezku. Obstaja peti list z imenom prečiščena datoteka.
Ta primer bo pokazal konsolidirano prodajo za vse regije. Tu so primeri podatkovnih datotek. Tu lahko vidite različna imena delovnega lista in zadnjo prečiščeno datoteko, na kateri moramo delati.
To je naša predloga na listu "konsolidirana datoteka" in zdaj bomo začeli konsolidirati podatke iz delovnih listov.
Zdaj bomo kliknili na celico B3.
Kjer želimo, da funkcija »Konsolidacija« vstavi podatke z drugih listov. Kot lahko vidimo zgoraj, je celica B3 izbrana in zdaj se bomo premaknili do zavihka Podatki v Excelovem traku in prešli na Consolidate.
Ko kliknemo na Consolidate, se spodaj odpre okno:
Prva stvar, ki jo pogledamo, je spustni meni "Funkcija", ki prikazuje številne aritmetične funkcije, kot so vsota, štetje, max, povprečje itd. Ker želimo vsoto prodaje, bomo v spustnem meniju izbrali "Vsota". Zdaj bomo šli na zavihek sklicevanja, da bomo dodali sklicevanje na naše podatke z različnih delovnih listov. Nato bomo prešli na naš prvi list, ki vsebuje podatke o prodaji podjetja North. Izbrati moramo le podatke o prodaji in ne naslovov in vrstic. To je prikazano spodaj.
Sklic je prikazan v tem polju "Reference".
Kliknite »Dodaj« in referenca bo dodana v polje »Vse reference«.
Na enak način bomo zdaj dodali sklic vseh drugih listov, kot so sever, jug, vzhod in zahod. Ko končamo z dodajanjem referenc, kliknite »V redu«.
Zdaj smo mesečno konsolidirali podatke o prodaji vodstvenih delavcev na nacionalni ravni.
Primer # 2 - Konsolidirajte letne prodajne izdelke
V tem primeru imamo izdelke A, B, C in D četrtletno pametne in si želimo konsolidirane letne prodajne izdelke.
Tu imamo podatke v štirih listih, prvi list s prodajnimi podatki za prvo četrtletje, naslednji podatki za Q2, nato podatki za Q3 in Q4. Zdaj bomo šli na zavihek, da bomo dodali sklice na naše podatke iz različnih delovnih listov. Podali se bomo na naš prvi list, ki vsebuje podatke o prodaji za prvo četrtletje. Podatke bomo izbrali, kot je prikazano spodaj.
Zdaj bomo šli na zavihek Podatki in kliknite Konsolidiraj in spodaj se bo pojavilo okno.
Kliknite "Dodaj" in sklic bo dodan v polju "Vse reference".
Kliknite "Dodaj" in sklic bo dodan v polju "Vse reference".
Kliknite "Dodaj" in sklic bo dodan v polju "Vse reference".
Spodaj je naša predloga za prečiščen podatkovni list. Zdaj bomo izbrali celico B2, da bomo dobili skupne podatke o prodaji iz drugih listov.
Iz spustnega menija bomo izbrali funkcijo »Vsota«. Prav tako bomo dodali reference iz listov Q2, Q3 in Q4. Zdelo se bo tako. Vse reference z vseh listov so zdaj dodane. Kliknite »V redu«
Zdaj imamo konsolidirane letne podatke o prodaji s skupnimi vsotami za vsak izdelek.
V primeru, da želimo združiti tabelo vstaviti v prazen delovni list, namesto da bi naredili tabelo predloge. To lahko storimo tudi mi. Ob predhodnih vzorčnih podatkih bomo v nadaljevanju naredili konsolidacijo.
Nimamo predloge za konsolidacijsko tabelo in želimo dobiti konsolidirane podatke v praznem delovnem listu z nalepkami vrstic in stolpcev. Dodali bomo nov delovni list, v našem primeru je to »Konsolidirana datoteka«.
Tako kot prej bomo šli na zavihek Data Zdaj bomo izbrali sklic iz naših podatkovnih listov. Začeli bomo z listom „Sever“ in nato nadaljevali z listi „Jug“, „Vzhod“ in „Zahod“. Izbrali bomo podatke, kot so prikazani spodaj, ki vključujejo oznake vrstic in stolpcev. Nato bomo v polje »Vse reference« dodali sklic: Zdaj dodajte vse reference na enak način iz vseh podatkovnih listov. Zdaj bomo preverili tako "Vrh vrstice" kot "levi stolpec" in nato pritisnite OK. Zdaj vidimo konsolidirane podatke o prodaji z nalepkami vrstic in stolpcev. Če želite posodobiti konsolidirane podatke, ko se posamezen list posodobi, kliknite polje »Ustvari povezave za ustvarjanje podatkov«. Če želite ročno posodobiti podatke, ne potrdite polja in kliknite V redu. Celice v konsolidirani tabeli zdaj vsebujejo formulo vsote, kot je prikazano spodaj. Zaradi tega je v naravi dinamičen. To je dinamična konsolidacijska tabela. Če imamo podatke v različnih delovnih zvezkih in jih želimo združiti v en sam delovni zvezek. To zmoremo tudi mi. To nam omogoča veliko prožnost in enostavnost. Podatki o prodaji različnih izdelkov v četrtletju so na primer A, B, C in D v različnih delovnih zvezkih, kot je prikazano spodaj. Ko bomo odprli vse delovne zvezke, bomo šli v nov delovni zvezek in kliknili na celico B3. Spodaj dodajte reference z vseh delovnih listov. Podatke iz vseh delovnih zvezkov smo združili v nov delovni zvezek. Poleg tega se bodo morebitne spremembe podatkov v delovnih zvezkih posodobile tudi v novem konsolidiranem delovnem zvezku. Tako smo se naučili, kako s pomočjo primerov uporabljati konsolidacijsko funkcijo v Excelu. Zelo uporabna funkcija je pri združevanju ali zbiranju podatkov v enem listu iz različnih delovnih listov ali delovnih zvezkov. To je vodnik za konsolidacijo v Excelu. Tukaj razpravljamo o tem, kako konsolidirati podatke v več delovnih listih, skupaj s praktičnimi primeri in naloženo predlogo Excela. Ogledate si lahko tudi druge naše sorodne članke -
Primer # 3 - Konsolidacija podatkov iz različnih delovnih zvezkov
Spomniti se morate o konsolidaciji v Excelu
Priporočeni članki