Consolideer gegevens in Excel

Wat is gegevens consolideren in Excel?

Terwijl we in Excel werken, hebben we veel gegevens beschikbaar in verschillende werkbladen en als we gegevens op een enkel werkblad willen samenvoegen of consolideren, heeft Excel een functie om dit te doen na het openen van alle werkbladen waarin gegevens moeten worden geconsolideerd, de knop Consolideren is beschikbaar in het gedeelte datatools van het tabblad Data.

Als u niet op de hoogte bent van de Consolidate-tool in Excel, helpt dit artikel u de dynamiek van de Consolidate-tool in Excel te begrijpen.

Als we gegevens van verschillende mensen ontvangen, krijgen we veel werkbladen om mee om te gaan. Alles consolideren is een enorme taak, vooral voor gemiddelde levelers. In het begin van mijn carrière kreeg ik gegevens uit een breed scala aan bronnen.

Ik was bijvoorbeeld gewend om verkoopgegevens uit 4 verschillende zones van zonekoppen te krijgen. Vier verschillende zones bevatten 4 verschillende werkbladen. Aan het einde van de gegevens moest ik alles in één werkblad verzamelen en een database maken voor analysedoeleinden. Ik deed het handmatig op de traditionele manier van kopiëren en plakken.

Maar ik heb wat tijd besteed aan het leren van nieuwe dingen en het consolideren van tools is er een van en het heeft me veel geholpen om de gegevens met minimale inspanningen te verzamelen.

Hoe gegevens in Excel te consolideren? (Stap voor stap met voorbeeld)

U kunt dit Excel-sjabloon voor gegevens consolideren hier downloaden - Excel-sjabloon voor gegevens consolideren

Ik heb verkoopgegevens uit vier verschillende regio's in vier verschillende werkbladen.

Elk bevat hetzelfde formaat en hetzelfde aantal rijen en kolommen. Ik moet alle bladen consolideren in één blad met de naam Samenvatting.

Ik heb dezelfde sjabloon gemaakt als op de andere 4 bladen.

  • Stap 1: Nadat u de bovenstaande sjabloon in de samenvatting van het blad hebt gemaakt, selecteert u de cel B2 waar we alle 4 verschillende regio's willen consolideren.

  • Stap 2: Ga nu naar Data> Consolideren

  • Stap 3: Nadat u de optie Consolideren heeft geselecteerd, ziet u het onderstaande venster.

  • Stap 4: Dit is het venster dat we nodig hebben om de magie te doen. Ten eerste zien we de vervolgkeuzelijst Functie in deze vervolgkeuzelijst die we nodig hebben om ons type consolidatie te selecteren. Hier moeten we de SOM-functie in Excel selecteren, omdat we alle verkoopgegevens van de zone consolideren en een samenvattend rapport maken. Selecteer dus SOM onder deze optie.
  • Stap 5: De volgende stap is om naar alle 4 verschillende regiobladen te verwijzen. Klik op het referentievak en ga naar het eerste blad van het hele blad van de zone, dwz Zuid. Klik na het selecteren van het bereik op de knop Toevoegen.

  • Stap 6: Nadat u op de knop Toevoegen hebt geklikt, worden de verwijzingen naar alle celverwijzingen toegevoegd.

  • Stap 7: Maak op dezelfde manier een Excel-link voor de bladen van alle andere regio's. Eindelijk ziet het er zo uit.

  • Stap 8: Klik nu op de OK-knop, uw geconsolideerde gegevens zijn klaar zonder fouten.

  • Stap 9: Nu we een samenvattend rapport hebben gemaakt, is dit geen dynamisch rapport omdat we geen link hebben gemaakt naar de andere tabellen. Als er wijzigingen zijn, blijven tabellen hetzelfde en gaat ons rapport in dit geval fout. We moeten de tabel aan de andere bladen koppelen om het rapport dynamisch te maken.
  • Stap 10: Schakel in het dialoogvenster Consolideren het selectievakje Koppelingen naar brongegevens maken in. Klik op OK.

Vervolgens is het proces voltooid.

  • Stap 11: Nu heeft het de SOM-functie gemaakt. Er is een plus (+) pictogram aan de rechterkant van de spreadsheet, klik op het plus (+) pictogram om het uiteenvallen te zien.

Andere opties in Consolidate

  1. Functie: Dit is de lijst met alle beschikbare functies in de tool Consolideren. U kunt de SUM, AVERAGE, COUNT, MAX, MIN-functie gebruiken in Excel, enz
  2. Referentie: hier moeten we de bereiken van verschillende bladen selecteren om de gegevens op één blad te consolideren.
  3. Bladeren: dit is het mooie van de optie consolideren. Het consolideert niet alleen vanuit dezelfde werkmap, maar het kan ook de gegevens uit andere werkmappen ophalen.
  4. Toevoegen en verwijderen: hier kunnen we alle referenties toevoegen en verwijderen.
  5. Bovenste rij: gebruik de bovenste rij als rijkoppen
  6. Linkerkolom: gebruik de linkerkant van de gegevens als kolomtitels.
  7. Maak koppelingen naar brongegevens: dit maakt het rapport dynamisch. De geconsolideerde optie creëert de formule voor alle andere bladen om het rapport echt dynamisch te maken.

Dingen om te onthouden

  • Onder consolideren moeten optiebereiken van alle werkbladen hetzelfde zijn voor alle werkbladen.
  • We kunnen elke functie gebruiken in de consolideeroptie.