Als onderdeel van mijn fulltime baan een paar jaar geleden, was een van de dingen waar ik mee te maken had, het combineren van gegevens uit verschillende werkmappen die door andere mensen werden gedeeld.
En een van de gemeenschappelijke taken was om de gegevens zo te combineren dat er geen dubbele records waren.
Hieronder staat bijvoorbeeld een dataset die meerdere records bevat voor dezelfde regio.
En het uiteindelijke resultaat moet een geconsolideerde dataset zijn waarin elk land slechts één keer wordt gerapporteerd.
In deze zelfstudie laat ik u zien hoe u dubbele rijen kunt combineren en de waarden kunt optellen om een enkele geconsolideerde gegevensset te maken.
Gegevens combineren en optellen met de optie Consolideren
Als u alleen gegevens hoeft te consolideren en alle waarden voor de herhalende records hoeft toe te voegen, kunt u het beste de consolidatiefunctie in Excel gebruiken.
De andere methode is om een draaitabel te gebruiken en de gegevens samen te vatten (hierna beschreven in deze zelfstudie).
Stel je hebt een dataset zoals hieronder weergegeven waarbij de landnaam meerdere keren wordt herhaald.
Hoewel dit unieke records zijn omdat de verkoopwaarde anders is, wilt u voor rapportagedoeleinden mogelijk meerdere exemplaren van hetzelfde land verwijderen en de verkoopwaarde weergeven als één geconsolideerde som.
Hieronder staan de stappen om dit te doen:
- Kopieer de koppen van de originele gegevens en plak deze waar u de geconsolideerde gegevens wilt
- Selecteer de cel onder de meest linkse kop
- Klik op het tabblad Gegevens
- Klik in de groep Gegevenshulpmiddelen op het pictogram Consolideren
- Selecteer in het dialoogvenster Consolideren de optie Som in de vervolgkeuzelijst voor functies (indien niet standaard al geselecteerd)
- Klik op het bereikselectiepictogram in het veld Referentie.
- Selecteer het bereik A2:B9 (de gegevens exclusief de koppen)
- Schakel het selectievakje Linkerkolom in
- Klik OK
De bovenstaande stappen zouden de gegevens consolideren door de dubbele vermeldingen te verwijderen en de waarden voor elk land toe te voegen.
In het eindresultaat krijgt u een unieke lijst met landen samen met de verkoopwaarde uit de originele dataset.
Ik koos ervoor om de SOM van de waarden van elk record te krijgen. U kunt ook andere opties kiezen, zoals Aantal of Gemiddelde of Max/Min.
In dit voorbeeld heb ik u laten zien hoe u de gegevens in een enkele gegevensset in een werkblad kunt consolideren. u kunt deze functie ook gebruiken om gegevens van meerdere werkbladen in dezelfde werkmap en zelfs meerdere verschillende werkmappen te consolideren.
Gegevens combineren en optellen met behulp van draaitabellen
Een draaitabel is het Zwitserse zakmes van het snijden en in blokjes snijden van gegevens in Excel.
Het kan u gemakkelijk een samenvatting geven die een gecombineerde gegevensset is zonder duplicaten en de waarden die de som zijn van alle vergelijkbare records, en nog veel meer.
Het nadeel van deze methode in vergelijking met de vorige is dat deze meer klikken en een paar seconden meer kost in vergelijking met de vorige.
Stel u heeft een dataset zoals hieronder weergegeven waarbij de landnaam meerdere keren wordt herhaald en u wilt deze gegevens consolideren.
Hieronder vindt u de stappen om een draaitabel te maken:
- Selecteer een cel in de dataset
- Klik op het tabblad Invoegen
- Klik in de groep Tabellen op de optie Draaitabel
- Controleer in het dialoogvenster Draaitabel maken of de tabel/het bereik correct is
- Klik op het bestaande werkblad
- Selecteer de locatie waar u de resulterende draaitabel wilt invoegen.
- Klik OK
De bovenstaande stappen zouden een draaitabel invoegen in de geselecteerde doelcel.
Nu kunnen we allerlei dingen doen met een draaitabel - inclusief het consolideren van onze dataset en het verwijderen van de duplicaten.
Hieronder staan de stappen om dit te doen:
- Klik ergens in het draaitabelgebied om het draaitabelvenster aan de rechterkant te openen
- Sleep het veld Plaats het land naar het gebied Rij
- Sleep en plaats het veld Verkoop in het gebied Waarden
De bovenstaande stappen vatten de gegevens samen en geven u de som van de verkopen voor alle landen.
Als dit alles is wat je wilt en je hebt geen draaitabel nodig, dan kun je de gegevens kopiëren en als waarden ergens anders plakken en de draaitabel verwijderen.
Dit zal u ook helpen de grootte van uw Excel-werkmap te verkleinen.
Dit zijn dus twee snelle en gemakkelijke methoden die u kunt gebruiken om de gegevens te consolideren waar het de dubbele rijen zou combineren en alle waarden in die records zou optellen.
Ik hoop dat je deze tutorial nuttig vond!
