Excel-konsolidering (indholdsfortegnelse)
- Introduktion til konsolidering i Excel
- Sådan konsolideres data i flere regneark?
Konsolider data i Excel
Konsolidering betyder generelt samling eller indsamling af nogle almindelige ting ét sted. Det kan være enhver form for data eller noget som helst information eller nummer. Konsolidering i Excel henviser til en funktion, der kombinerer information eller data fra forskellige projektmapper / regneark til et regneark / projektmappe. Med andre ord tager denne funktion data fra forskellige regneark for at kombinere dem i et enkelt regneark, der let kan opdateres.
For eksempel, hvis du har fire regneark. Hvert ark har udgifter i et kvartal. Du har muligvis brug for et enkelt ark med udgifterne fra alle fire kvartaler. Dette er, når konsolideringsfunktionen kommer i spil. Når dataene i regneark er i samme position, hvilket betyder under de samme etiketter og i samme rækkefølge. Vi bruger “Konsolidering efter position”. Og når dataene i regneark ikke er under de samme etiketter og ikke i samme rækkefølge. Derefter bruger vi “Konsolidering efter kategori”. Det er ligesom at oprette en Pivot-tabel.
Sådan konsolideres data i flere regneark?
Lad os forstå, hvordan man konsoliderer data i flere regneark med et par eksempler.
Eksempel 1 - Konsolider data i den samme projektmappe
Lad os sige, at vi har et regneark, hvor vi har salgsdata med fire regneark, der er markeret efter deres region som nord, syd, øst og vest. Nu vil vi gerne konsolidere dataene ét sted snarere i et ark inden for den samme projektmappe. Der er et femte ark med navnet konsolideret fil.
Dette eksempel viser det samlede salg for alle regioner. Her er eksempler på datafiler. Her kan du se de forskellige arbejdsarknavne og den sidste konsoliderede fil, som vi skal arbejde på.
Dette er vores skabelon i "konsolideret fil" -ark, og nu vil vi begynde at konsolidere data fra regnearkene.
Vi vil nu klikke på celle B3.
Hvor vi ønsker, at “Konsolider” -funktionen skal indsætte dataene fra andre ark. Som vi kan se ovenfor, er celle B3 valgt, og nu går vi op til fanen Data i Excel-båndet og går til Konsolidering.
Når vi klikker på Konsolidering, vises nedenunder vindue:
Den første ting, vi ser på, er dropdown-funktionen "Funktion", der viser mange aritmetiske funktioner som sum, antal, maks., Gennemsnit osv. Da vi ønsker en sum af salget, vælger vi "Sum" i dropdown. Nu går vi til referencefanen for at tilføje en henvisning til vores data fra forskellige regneark. Vi går derefter til vores første ark, der indeholder salgsdata for North. Vi skal kun vælge salgsdata og ikke overskrift og rækker. Dette vises nedenfor.
Henvisningen vises i denne rubrik “Reference”.
Klik på "Tilføj", og referencen tilføjes i boksen "All Reference".
På samme måde tilføjer vi nu en henvisning fra alle andre ark som nord, syd, øst og vest. Når vi er færdige med at tilføje referencerne, skal du klikke på "OK".
Nu har vi konsolideret data for salg for direktørerne månedligt på nationalt niveau.
Eksempel 2 - Konsolider årligt salgsprodukt
I dette har vi kvartalsvis salg af produkterne A, B, C og D, og vi ønsker et konsolideret årligt salg produktmæssigt.
Her har vi data i fire ark, første ark med salgsdata for Q1, derefter har data for Q2 efterfulgt af data for Q3 og Q4. Vi går nu til referencefanen for at tilføje referencer til vores data fra forskellige regneark. Vi går til vores første ark, der indeholder salgsdata for 1. kvartal. Vi vælger dataene som vist nedenfor.
Nu går vi til fanen Data og klikker på Konsolider, og nedenstående vindue vises.
Vi klikker på "Tilføj", og referencen tilføjes i feltet "Alle henvisninger".
Vi klikker på "Tilføj", og referencen tilføjes i feltet "Alle henvisninger".
Vi klikker på "Tilføj", og referencen tilføjes i feltet "Alle henvisninger".
Nedenfor er vores skabelon til det konsoliderede datablad. Vi vil nu vælge celle B2 for at hente de samlede salgsdata fra andre ark.
Vi vælger “Sum” -funktion fra rullemenuen. På samme måde tilføjer vi referencer fra ark Q2, Q3 og Q4. Det vil se sådan ud. Alle referencer fra alle arkene er nu tilføjet. Klik på "OK"
Vi har nu vores konsoliderede årlige salgsdata med summen for hvert produkt.
I tilfælde af at hvis vi ønsker at indsætte den konsoliderede tabel i et tomt regneark i stedet for at lave en skabelontabel. Det kan vi også gøre. Ved at tage vores tidligere eksempeldata foretager vi konsolideringen nedenfor.
Vi har ikke en skabelon til konsolideringstabel, og vi vil have konsoliderede data i et tomt regneark med række- og kolonnetiketter. Vi tilføjer et nyt regneark, i vores tilfælde er det ”Konsolideret fil”.
Som før går vi nu til fanen Data Vi vælger nu referencen fra vores datablad. Vi starter dette med "nord" ark og fortsætter derefter med "syd", "øst" og "vest" ark. Vi vælger dataene som vist nedenfor, som inkluderer række- og kolonnetiketter. Vi tilføjer derefter referencen i boksen "Alle referencer": Tilføj nu alle referencer på samme måde fra alle databladene. Vi tjekker nu både “Top Row” og “Left Column” og trykker derefter på OK. Vi ser nu en konsolideret salgsdata med række- og kolonnetiketter. Hvis du vil opdatere de konsoliderede data, når det enkelte ark bliver opdateret, skal du klikke på boksen "Opret links for at oprette data". Hvis du vil opdatere data manuelt, skal du ikke markere afkrydsningsfeltet og klikke på OK. Cellerne i den konsoliderede tabel indeholder nu sumformlen som vist nedenfor. Dette gør den dynamisk i naturen. Dette er en dynamisk konsolideringstabel. Hvis vi har data i forskellige projektmapper, og vi vil konsolidere dem til en enkelt projektmappe. Vi kan også gøre det. Dette giver os stor fleksibilitet og lethed. Vi har kvartalsvise salgsdata for forskellige produkter siger A, B, C og D i forskellige projektmapper som vist nedenfor. Når vi har åbnet alle arbejdsbøger, går vi til en ny projektmappe og klikker på celle B3. Tilføj referencerne fra alle regneark herunder. Vi har konsolideret data fra alle projektmapper til en ny projektmappe. Desuden vil ændringer i dataene i eventuelle projektmapper også blive opdateret i den nye konsoliderede data-projektmappe. Så vi har lært, hvordan man bruger konsolideringsfunktion i Excel ved hjælp af eksempler. Det er en meget nyttig funktion til at flette eller indsamle data til et ark fra forskellige regneark eller projektmapper. Dette er en guide til konsolidering i Excel. Her diskuterer vi Sådan konsolideres data i flere regneark sammen med praktiske eksempler og downloadbar excel-skabelon. Du kan også gennemgå vores andre relaterede artikler -
Eksempel # 3 - Konsolidering af data fra forskellige arbejdsbøger
Ting at huske på Konsolidering i Excel
Anbefalede artikler