Tegenwoordig is een oude techniek genaamd Data Consolidation. Als u gegevens uit meerdere werkbladen of meerdere werkmappen moet combineren, kan Consolidation de taak afhandelen als uw gegevens in een specifiek formaat zijn. Lees meer over deze oude tool.
Er zijn twee oude consolidatietools in Excel.
Om ze te begrijpen, moet u zeggen dat u drie gegevenssets heeft. Elk heeft namen aan de linkerkant en maanden bovenaan. Merk op dat de namen verschillend zijn en dat er in elke dataset een ander aantal maanden zit.
U wilt deze combineren tot één dataset. Sla de bladzijde om voor een bespreking van de twee methoden.
De eerste tool is de opdracht Consolideren op het tabblad Gegevens. Kies een leeg gedeelte van de werkmap voordat u de opdracht start. Gebruik de RefEdit-knop om naar elk van uw datasets te wijzen en klik vervolgens op Toevoegen. Kies linksonder de bovenste rij en de linkerkolom.
Als u op OK klikt, wordt een superset van alle drie de gegevenssets geproduceerd. De eerste kolom bevat een naam in een van de drie gegevenssets. Rij 1 bevat een willekeurige maand in een gegevensset.
Let in de bovenstaande afbeelding op drie ergernissen. Cel A1 wordt altijd leeg gelaten. De gegevens in A zijn niet gesorteerd. Als een persoon ontbrak in een dataset, worden cellen leeg gelaten in plaats van gevuld met 0.
Het invullen van cel A1 is eenvoudig genoeg. Sorteren op naam houdt in dat u Snel vullen gebruikt om de achternaam in kolom N te krijgen. Hier ziet u hoe u lege cellen met 0 vult:
- Selecteer alle cellen die nummers moeten hebben: B2: M11.
- Selecteer Home, Zoek & selecteer, Ga naar speciaal.
- Kies Blanks en klik vervolgens op OK. Alle lege cellen zijn geselecteerd.
-
Typ 0 en vervolgens Ctrl + Enter.
Ga naar speciaal
Het resultaat: een mooi opgemaakt samenvattend rapport.
Bekijk video
- Consolideren is een oude functie in Excel
- Geef meerdere bereiken op om te consolideren
- Gebruik labels in de bovenste rij en linkerkolom
- Ergernissen: A1 is altijd leeg, kolom A is niet gesorteerd, spaties in gegevens
- Ga naar Special, Blanks, 0, Ctrl + Enter
- Consolideren kan verwijzen naar externe werkmappen
Videotranscriptie
Leer Excel van podcast, aflevering 2046 - Consolideer werkbladen!
Ik zal al mijn tips uit dit boek podcasten, klik op de "i" in de rechterbovenhoek om naar de afspeellijst te gaan!
Oké, ik heb een geweldige, geweldige oude truc. Ik heb hier drie datasets, Q1, Q2, Q3, ze hebben allemaal een vergelijkbare vorm, en dat ze namen aan de linkerkant hebben, maanden aan de bovenkant, maar niet precies dezelfde vorm. Q1 heeft jan-feb-mrt, Q2 heeft 5 maanden, ik denk dat we lui zijn geworden en er niet achter kwamen om dit eind juni te doen en iemand deed het eindelijk eind augustus, en dan heeft Q4 4 maanden. Oké, de 1e gaat A: D, 2e A: F, 3e A: E. Verschillende namen, sommige namen zijn hetzelfde, zoals Michael Seeley er in allemaal staat, maar andere namen komen en gaan. Oké, dit is een geweldige functie, het bestaat altijd al. Ik herinner me dat ik dit zelfs in 1995 deed, Data, Consolidates!
Oké, we gaan de functie SOM gebruiken, ik heb nog nooit een van de andere gebruikt, maar ik denk dat ze er zijn. SOM-functie, het eerste dat we gaan doen is teruggaan naar Q1 en naar dit bereik wijzen, die vier kolommen, klikken op Toevoegen en dan naar Q2 gaan, deze kolommen selecteren, op Toevoegen klikken en vervolgens Q4, selecteer deze kolommen . Oké, vink dit vakje aan voor Gebruik labels in de bovenste rij en de linkerkolom, die knop Bladeren betekent dat deze gegevenssets in verschillende werkmappen kunnen staan! Maak links naar brongegevens, daar gaan we het aan het einde over hebben. Als ik op OK klik, krijgen ze elke naam die in een van de 3 lijsten staat, de maanden daar in een van de 3 lijsten, en we hebben nu deze geweldige superset, oke, ergernissen!
Dit is een geweldige functie, maar hier zijn de dingen die me zojuist hebben gehackt. Ze geven me niet het label in A1, ze doen niet de moeite om de gegevens naar beneden te sorteren, en als iemand geen record had in de eerste, geven ze me lege plekken in plaats van nullen. Oké, om de lege plekken met nullen te vullen, Home, Zoeken en selecteren, Ga naar speciaal, kies de lege plekken, klik op OK, typ een nul, Ctrl + Enter vult die in. Eenvoudig genoeg om de gegevens te sorteren, Data, AZ en het zal de gegevens sorteren, oké. Maak links, jeez, het werkt nooit goed, oké, maak links, om Links aan te maken moet het in een externe werkmap staan. Oké, dus ik ga hier op Bladeren klikken, ik heb een werkboek gemaakt met de naam OtherWorkbook en de gegevens staan in A1: D7, klik op Toevoegen, oké, en daar is de eerste. De volgende gegevens zijn in G1: L8, dus ik zal door OtherWorkbook bladeren, G1: L8,klik op Toevoegen, oké. Dus nu heb ik twee verwijzingen naar andere werkmappen, bovenste rij, linkerkolom, koppelingen naar brongegevens maken. Excel-help zegt dat zodra u Create links de brongegevens gebruikt, u die bereiken nooit meer kunt bewerken. Klik nogmaals op OK en dit is wat we krijgen.
Oké, ten eerste lijkt het erop dat het ons de resultaten heeft gegeven, het gaf ons de resultaten, maar er is een extra kolom B hier, en we hebben Groep en Overzicht. En als we naar de nummer 2-weergave gaan, ah. Dus idealiter zou je een werkboek hebben met de naam januari en een ander werkboek met de naam februari, en het zal je laten zien dat hier januari is, hier februari. Hier is het totaal voor Mike Seeley, dit zijn formules die naar die verkopen verwijzen, en dan is hier een som van die twee, oké, het is raar.
Als je dit echt de hele tijd gebruikt, wil ik van je horen in de YouTube-reacties, ik weet zeker dat er een gemakkelijkere manier is om het voor mij te doen, ik heb het nog nooit in mijn leven gedaan, wel een keer eerder vandaag en dan vandaag, gewoon zodat ik het kon uitleggen, oké. Maar als we bladen uit de huidige werkmap consolideren, een geweldige truc. Morgen gaan we het meervoudige consolidatiebereik, draaitabellen, vergelijken, maar al deze trucs staan in het boek, klik op de "i" in de rechterbovenhoek om bij dat boek te komen.
Consolideer, oude, oude functie in Excel, u specificeert meerdere bereiken om te consolideren, ik vink altijd het vakje aan voor de bovenste rij en linkerkolom. De resultaten zijn geweldig, maar A1 is leeg, kolom A is niet gesorteerd en er zijn lege plekken in de gegevens, gebruik Ga naar speciale lege plekken, typ een 0, Ctrl + Enter om die lege plekken op te vullen. En dan kan dat laatste voorbeeld daar, consolideren, verwijzen naar externe werkmappen, nuttig zijn!
Oké, ik wil je bedanken voor het langskomen, we zien je de volgende keer voor een nieuwe netcast van!
Download bestand
Download het voorbeeldbestand hier: Podcast2046.xlsm
