Samengevoegde cellen zijn tegenwoordig het probleem, maar dit zijn verticale samengevoegde cellen. Bekijk de onderstaande afbeelding. Als er zeven rijen zijn voor de regio Midwest, wordt het woord Midwest alleen op de eerste rij weergegeven. Iemand voegt dan de resterende zes lege cellen samen met de Midwest-cel om een samengevoegde cel te maken die 7 cellen lang is.
Dit is een vreselijke manier om gegevens te verzenden. Als iemand bekwaam is in Excel, wil hij misschien de gegevens sorteren, subtotalen toevoegen en draaitabellen gebruiken. Samengevoegde cellen voorkomen dit.
Om het nog erger te maken, krijgt u elke dag een nieuw dossier van het hoofdkantoor dat zo is opgebouwd. Het hoofdkantoor stuurt soortgelijke bestanden naar 500 filialen. Omdat het slechts een kleine buitenpost is, heb je niet veel kans om het hoofdkantoor ervan te overtuigen dat dit een vreselijke manier is om gegevens te verzenden.
Het artikel van vandaag gaat over hoe u dit probleem snel kunt oplossen met Power Query. De Power Query-oplossing is op dagen 2 tot en met 9999 eenvoudiger dan de volgende stappen in Excel:
Excel-goeroes zouden deze stappen elke dag kunnen voorstellen:
- Selecteer alle cellen door de rechthoek boven en links van A1 te kiezen.
- Klik op de dialoogstarter in de rechterbenedenhoek van de groep Uitlijning van het tabblad Start.
- Klik tweemaal op het vak Cellen samenvoegen om de selectie ongedaan te maken.
- Klik op OK om het dialoogvenster Cellen opmaken te sluiten
- Selecteer vanaf het einde van kolom A terug naar A1.
- Start, Zoek & selecteer, Ga naar speciaal, Blanco, OK
- Typ = Pijl omhoog. Druk op Ctrl + Enter
- Selecteer vanaf het einde van kolom A terug naar A1.
- Ctrl + C om te kopiëren. Klik met de rechtermuisknop en plak waarden
Maar er is een veel gemakkelijkere manier. De nieuwe Power Query-tools zijn ingebouwd in Windows-versies van Office 365 en Excel 2016. Als u een Windows-versie van Excel 2010 of Excel 2013 heeft, kunt u Power Query gratis downloaden van Microsoft.
Hier is de strategie met Power Query:
- Maak een plan dat u de dagelijkse werkmap van het hoofdkantoor opslaat in dezelfde map met dezelfde naam.
- Open een nieuwe lege werkmap die de vaste gegevens van het hoofdkantoor zal bevatten.
-
Kies in de lege werkmap Gegevens, Gegevens ophalen, Uit bestand, Uit werkmap.
Start het Power Query-proces - Blader naar de map en het bestand uit stap 1.
-
Wanneer het Power Query-venster wordt geopend, ziet u dat de samengevoegde cellen verdwenen zijn. Je hebt Midwest in rij 1 en vervolgens zes cellen die "null" bevatten. Selecteer deze kolom door op het kopje Regio te klikken.
Power Query verwijdert automatisch de samengevoegde cellen -
Selecteer in Power Query het tabblad Transformeren. Kies Opvullen, Omlaag. Het woord Midwest wordt gekopieerd van rij 1 naar rij 2 tot en met 7. Soortgelijke transformaties vinden plaats in de hele gegevensset.
Vul de nulcellen met de waarde van bovenaf -
Home, sluiten en laden. Power Query wordt gesloten. Over ongeveer 10-20 seconden verschijnen de opgeschoonde gegevens van het hoofdkantoor in een nieuw werkblad in de werkmap.
Retourneer de gegevens naar Excel - Sla de werkmap op met een naam zoals CleanedDataFromHQ.xlsx.
- Wanneer de gegevens zijn geselecteerd, zou u het paneel Query's en verbindingen aan de rechterkant moeten zien. Zie je het paneel niet, ga dan naar Data, Query's & Connections.
-
Klik met de rechtermuisknop op de query in het paneel Query's en verbindingen. Kies Eigenschappen.
Open de eigenschappen voor deze query -
Kies "Gegevens vernieuwen bij openen van bestand". Klik OK.
Stel de query zo in dat deze elke keer dat u de werkmap opent, wordt uitgevoerd.
Uw workflow wordt dan: (a) Ontvang de werkmap van het hoofdkantoor per e-mail. (b) Sla de bijlage op in de juiste map met de juiste naam. (c) Open de CleanedDataFromHQ.xlsx-werkmap. De nieuwe gegevens worden in de werkmap geladen.
Bekijk video
Videotranscriptie
Leer Excel van Podcast, aflevering 2221: vul samengevoegde cellen naar beneden.
Hé, welkom terug bij de netcast. Ik ben Bill Jelen, ik was in Calumet City, Elizabeth komt opdagen en zegt dat ze dit bestand - ons bestand, zoals dit bestand - elke dag krijgt en het is vreselijk. Hier in kolom A is het niet alleen dat ze Midwest hebben geplaatst en een aantal lege plekken hebben achtergelaten, het is een samengevoegde cel. Oké? Ze sturen de … Het hoofdkantoor stuurt deze bestanden naar locaties over de hele wereld met deze gekke samengevoegde cellen hier. Samengevoegde cellen zijn slecht.
Oké, hier is hoe je hiervan af kunt komen. Ten eerste ga ik met de rechtermuisknop klikken, verplaatsen of kopiëren, een kopie maken en deze naar het nieuwe boek verplaatsen. Dus we doen net alsof het het werkboek is dat Elizabeth krijgt. Bestand, Opslaan als, en elke keer dat Elizabeth een van deze werkmappen krijgt, wil ik dat ze het op exact dezelfde plek plaatst met exact dezelfde naam. Oké? Precies dezelfde naam - perfect. Dus nu hebben we gewoon dat ene bestand, een enkel blad, ik zal het bestand sluiten. En dan gaan we een gloednieuw werkboek maken. Ik ga hier gewoon een nieuw werkblad invoegen en dit nieuwe werkblad wordt het rapport genoemd. En op deze blanco pagina doet ons rapport Gegevens, Gegevens ophalen, Uit bestand, Uit een werkmap verwijzen naar het bestand waarin we het gaan opslaan - dus met elke keer exact dezelfde naam - klik op Importeren , kies het eerste werkblad,en klik vervolgens op Bewerken. Oké. Ten eerste is dit geweldig geweldig - power-query zal geen van deze samenvoegcel-onzin hebben, ze converteren de samenvoegcellen onmiddellijk in individuele cellen en al deze cellen zijn nul. Kies die kolom, deze is hier al gekozen, en dan Transformeren, Vullen, Invullen, zoals dat, en dan Home, Sluiten en laden. Oké, dus daar is mijn werkboek.
Nu, ik zie dat sommigen hier wat extra kolommen hebben. Laat me met de rechtermuisknop klikken en we zullen bewerken.
En het lijkt erop dat alles aan de rechterkant van de kosten moet verdwijnen. Hoeveel extra spullen zijn er? Klik met de rechtermuisknop en verwijder die kolommen - perfect - Sluiten en laden. Oké, nu, de werkstroom wordt, elke keer dat Elizabeth een nieuw rapport van het hoofdkantoor krijgt, gaat ze het op diezelfde plek opslaan met een betrouwbare naam of wat dan ook - hoe dan ook - we noemen het, en dan opent ze dit werkboek, en klik dan hier op Vernieuwen.
Of, als we automatisch willen werken, klik hier met de rechtermuisknop, ga omlaag naar Eigenschappen, helemaal buiten uw scherm helemaal onderaan, en zeg dan: Elke keer dat ik dit bestand open, ververs de gegevens. Dus elke keer dat ik deze werkmap open, gaat het naar de nieuwe werkmap die we van het hoofdkantoor hebben gekregen, het zal die lege samengevoegde cellen vervangen door de waarden die er zouden moeten zijn, en het leven is zo geweldig.
Power Query wordt behandeld in mijn boek, maar het wordt ook behandeld in dit boek van Ken Puls en Miguel Escobar, M is voor (DATA) MONKEY.
Oké, dus Elizabeth: hoe kom ik af van verticale samenvoegcellen? Mijn hoofdkantoor blijft me deze bestanden elke dag sturen. Dus het plan: we gaan de werkmap opslaan op een betrouwbare plaats met een betrouwbare naam; maak een lege rapportagewerkmap, Gegevens, Gegevens ophalen, Uit bestand, Uit werkmap, specificeer het blad, BAM! De samenvoegcellen zijn verdwenen. Ik selecteer die kolom en vul omlaag, sluit en laad; vervolgens, elke keer dat u een nieuwe werkmap krijgt, die nieuwe werkmap op een betrouwbare plaats met dezelfde betrouwbare naam opslaan; Ik open de Rapportage-werkmap en Vernieuw, of we stellen de Rapportage-werkmap in op Automatisch vernieuwen bij openen.
Om de werkmap van de video van vandaag te downloaden, is de URL in de YouTube-beschrijving.
Ik wil Elizabeth bedanken voor het bijwonen van mijn seminar in Calumet City, ik wil je bedanken voor het langskomen. Ik zie je de volgende keer voor nog een netcast van.
Download Excel-bestand
Om het Excel-bestand te downloaden: fill-merged-cells-down.xlsx
Power Query is een geweldige tool - je kunt er verschillende
zegt Alimohammadi
