Excel Consolidation (inhoudsopgave)
- Inleiding tot consolidatie in Excel
- Hoe gegevens in meerdere werkbladen te consolideren?
Gegevens consolideren in Excel
Consolidatie betekent in het algemeen het verzamelen of verzamelen van enkele veelvoorkomende dingen op één plek. Het kan elke soort data zijn of iets zoals informatie of nummer. Consolidatie in Excel verwijst naar een functie die informatie of gegevens uit verschillende werkmappen / werkbladen combineert in één werkblad / werkmap. Met andere woorden, deze functie neemt gegevens uit verschillende werkbladen om deze te combineren in een enkel werkblad dat gemakkelijk kan worden bijgewerkt.
Als u bijvoorbeeld vier werkbladen heeft. Elk blad heeft uitgaven voor een kwart. Mogelijk hebt u één vel nodig met de kosten van alle vier de kwartalen. Dit is wanneer de consolidatiefunctie een rol speelt. Wanneer de gegevens in werkbladen zich op dezelfde positie bevinden, wat betekent onder dezelfde labels en in dezelfde volgorde. We gebruiken "Consolidatie per positie". En wanneer de gegevens in werkbladen niet onder dezelfde labels staan en niet in dezelfde volgorde. Vervolgens gebruiken we "Consolidatie per categorie". Het is net als het maken van een draaitabel.
Hoe gegevens in meerdere werkbladen te consolideren?
Laten we begrijpen hoe we gegevens in meerdere werkbladen kunnen consolideren met een paar voorbeelden.
Voorbeeld # 1 - Gegevens in dezelfde werkmap consolideren
Laten we zeggen dat we een werkblad hebben waarin we verkoopgegevens hebben met vier werkbladen gemarkeerd volgens hun regio als Noord, Zuid, Oost en West. Nu willen we de gegevens op één plaats consolideren in plaats van in een blad binnen dezelfde werkmap. Er is een vijfde blad met de naam geconsolideerd bestand.
Dit voorbeeld toont de geconsolideerde omzet voor alle regio's. Hier zijn de voorbeeldgegevensbestanden. Hier ziet u de verschillende werkbladnamen en het laatste geconsolideerde bestand waaraan we moeten werken.
Dit is onze sjabloon in het blad "geconsolideerd bestand" en nu gaan we gegevens uit de werkbladen consolideren.
We zullen nu op cel B3 klikken.
Waar we willen dat de functie "Consolideren" de gegevens uit andere bladen invoegt. Zoals we hierboven kunnen zien, is cel B3 geselecteerd en gaan we nu naar het tabblad Gegevens in Excel-lint en gaan we naar Consolideren.
Zodra we op Consolideren klikken, verschijnt het onderstaande venster:
Het eerste waar we naar kijken is de vervolgkeuzelijst “Functie” die veel rekenkundige functies toont zoals som, telling, max, gemiddelde, enz. Omdat we een som van verkopen willen, zullen we “Som” selecteren in de vervolgkeuzelijst. Nu gaan we naar het tabblad Verwijzing om een verwijzing naar onze gegevens uit verschillende werkbladen toe te voegen. We gaan dan naar ons eerste blad met de verkoopgegevens voor Noord. We hoeven alleen verkoopgegevens te selecteren en niet kop en rijen. Dit wordt hieronder getoond.
De verwijzing wordt op deze manier weergegeven in het vak "Referentie".
Klik op "Toevoegen" en de referentie wordt toegevoegd in het vak "Alle referentie".
Op dezelfde manier zullen we nu een referentie toevoegen van alle andere bladen zoals Noord, Zuid, Oost en West. Als we klaar zijn met het toevoegen van de referenties, klikt u op "OK".
Nu hebben we geconsolideerde gegevens voor de verkoop voor de leidinggevenden maandelijks, op nationaal niveau.
Voorbeeld # 2 - Consolideer jaarlijkse verkoopproduct Wise
Hierin hebben we kwartaalsgewijze verkopen voor producten A, B, C en D en we willen een geconsolideerde jaarlijkse verkoop qua producten.
Hier hebben we gegevens in vier bladen, eerste blad met verkoopgegevens voor Q1, vervolgens gegevens voor Q2 gevolgd door gegevens voor Q3 en Q4. We gaan nu naar het tabblad Verwijzing om verwijzingen naar onze gegevens uit verschillende werkbladen toe te voegen. We gaan naar ons eerste blad met de verkoopgegevens voor Q1. We zullen de gegevens selecteren zoals hieronder getoond.
Nu gaan we naar het tabblad Gegevens en klikken op Consolideren en het onderstaande venster verschijnt.
We klikken op "Toevoegen" en de referentie wordt toegevoegd in het vak "Alle referentie".
We klikken op "Toevoegen" en de referentie wordt toegevoegd in het vak "Alle referentie".
We klikken op "Toevoegen" en de referentie wordt toegevoegd in het vak "Alle referentie".
Hieronder vindt u onze sjabloon voor het geconsolideerde gegevensblad. We zullen nu cel B2 selecteren om de totale verkoopgegevens van andere bladen te krijgen.
We selecteren de functie "Som" in de vervolgkeuzelijst. Evenzo zullen we referenties van bladen Q2, Q3 en Q4 toevoegen. Het zal er zo uitzien. Alle referenties van alle bladen zijn nu toegevoegd. Klik OK"
We hebben nu onze geconsolideerde jaarlijkse verkoopgegevens met de somtotalen voor elk product.
In het geval dat we de geconsolideerde tabel in een leeg werkblad willen invoegen in plaats van een sjabloontabel te maken. Dat kunnen wij ook doen. Op basis van onze eerdere voorbeeldgegevens zullen we de onderstaande consolidatie uitvoeren.
We hebben geen sjabloon voor consolidatietabel en we willen geconsolideerde gegevens in een leeg werkblad met rij- en kolomlabels krijgen. We zullen een nieuw werkblad toevoegen, in ons geval is dit "Geconsolideerd bestand".
Nu gaan we zoals eerder naar het tabblad Gegevens We zullen nu de referentie in onze datasheets selecteren. We beginnen dit met het blad "Noord" en gaan dan verder met de bladen "Zuid", "Oost" en "West". We zullen de gegevens selecteren zoals hieronder getoond, inclusief rij- en kolomlabels. We zullen dan de referentie toevoegen in het vak "Alle referenties": Voeg nu alle verwijzingen op dezelfde manier toe uit alle gegevensbladen. We zullen nu zowel de "Bovenste rij" als "Linkerkolom" controleren en vervolgens op OK drukken. We zien nu geconsolideerde verkoopgegevens met rij- en kolomlabels. Als u de geconsolideerde gegevens wilt bijwerken wanneer het individuele blad wordt bijgewerkt, klikt u op het vakje "Koppelingen maken om gegevens te maken". Als u gegevens handmatig wilt bijwerken, vink dan het vakje niet aan en klik op OK. De cellen in de geconsolideerde tabel bevatten nu de somformule zoals hieronder weergegeven. Dit maakt het dynamisch van aard. Dit is een dynamische consolidatietabel. Als we gegevens in verschillende werkmappen hebben en we deze in één werkmap willen consolideren. We kunnen dit ook doen. Dit geeft ons veel flexibiliteit en gemak. We hebben kwartaalgewijze verkoopgegevens van verschillende producten, zoals A, B, C en D in verschillende werkboeken, zoals hieronder weergegeven. Nadat we alle werkmappen hebben geopend, gaan we naar een nieuwe werkmap en klikken op cel B3. Voeg de referenties van alle onderstaande werkbladen toe. We hebben gegevens uit alle werkmappen geconsolideerd in een nieuwe werkmap. Bovendien worden wijzigingen in de gegevens in werkboeken ook bijgewerkt in de nieuwe geconsolideerde gegevenswerkmap. We hebben dus geleerd hoe we de consolidatiefunctie in Excel kunnen gebruiken met behulp van voorbeelden. Het is een zeer nuttige functie bij het samenvoegen of verzamelen van gegevens in één blad van verschillende werkbladen of werkmappen. Dit is een gids voor consolidatie in Excel. Hier bespreken we hoe gegevens in meerdere werkbladen kunnen worden samengevoegd, samen met praktische voorbeelden en een downloadbare Excel-sjabloon. U kunt ook onze andere gerelateerde artikelen doornemen -
Voorbeeld # 3 - Gegevens uit verschillende werkmappen consolideren
Dingen om te onthouden over consolidatie in Excel
Aanbevolen artikelen