Excel biedt een Consolideren-opdracht op het tabblad Gegevens. Met deze oude opdracht kunt u gegevens uit meerdere werkbladen of werkmappen consolideren, zelfs als de gegevens een andere vorm hebben.
Ik veronderstel dat het vreemd is om over Consolidate te praten als Power Query veel flexibeler is in het combineren van werkbladen. Maar er zijn momenten waarop een snelle Consolidate-opdracht het werk zal doen zonder Power Query te activeren.
Consolidate is ontworpen voor een gegevensset met één rij koppen bovenaan en één kolom met labels aan de linkerkant. Alle andere cellen in de tabel moeten numeriek zijn.
Stel dat u deze twee gegevenssets wilde consolideren. Normaal gesproken zouden ze op verschillende werkbladen of verschillende werkmappen staan, maar ik heb ze hier naast elkaar gelegd. Merk op dat het oké is als er items ontbreken in een dataset. Het belangrijkste is dat beide datasets items aan de linkerkant en maanden bovenaan hebben.
Zoek een leeg gedeelte van het werkblad waar de resultaten zullen verschijnen. Selecteer de cel in de linkerbovenhoek. Kies Gegevens, consolideren.
In het dialoogvenster Consolideren:
- Merk op dat u kunt kiezen uit 11 functies in de vervolgkeuzelijst Functie. Ik heb nooit iets anders gekozen dan Sum
- Kies linksonder de bovenste rij en de linkerkolom
- Klik in het referentievak. Markeer het eerste bereik dat moet worden geconsolideerd (in dit geval A1: E14). Klik op de knop Toevoegen om het specificeren van het eerste bereik te voltooien.
- Klik nogmaals in het referentievak. Markeer het tweede bereik dat wordt geconsolideerd (G1: M18).
- Als u meer bereiken heeft, klikt u op Toevoegen en specificeert u het volgende bereik. Wanneer u het laatste bereik specificeert, hoeft u niet op Toevoegen te klikken. Klik gewoon op OK.
De resultaten zijn hieronder weergegeven. (Ik heb de kolombreedten verkleind zodat ze op je scherm passen.) Ik ben altijd een beetje geïrriteerd door het volgende:
- Excel sorteert de resultaten niet.
- De cel linksboven wordt altijd leeg gelaten, zelfs als de originele tabellen daar een label hadden.
- Als er geen gegevenspunt voor een cel is, wordt dit leeg gelaten in plaats van nul.
Om de resultaten te corrigeren, typ ik een kop in de linkerbovenhoek. Selecteer de nummers. Ctrl + H om te vervangen. Laat Zoeken wat leeg. Typ 0 in Vervangen door. Klik op Alles vervangen. Sorteer eventueel op de eerste kolom.
f
Merk op dat elke werkmap de instellingen voor één consolidatie kan onthouden. Als u de consolidatie moet bijwerken, kunt u dezelfde of een nieuwe cel linksboven selecteren en de opdracht Consolideren opnieuw uitvoeren.
Thoracale donderdag - mijn favoriete hartverscheurende functies in Excel.
Excel-gedachte van de dag
Ik heb mijn Excel Master-vrienden om advies over Excel gevraagd. De gedachte van vandaag om na te denken:
"Waarom tellen als je kunt COUNTA?"
Duane Aubin
