Se video - Udfyld i Excel
Mange gange kan du støde på et datasæt, hvor kun en celle er fyldt med data, og cellerne under den er tomme indtil den næste værdi.
Noget som vist herunder:
Selvom dette format fungerer for nogle mennesker, er problemet med denne slags data, at du ikke kan bruge det til at oprette pivottabeller eller bruge det i beregninger.
Og dette har en let løsning!
I denne vejledning vil jeg vise dig, hvordan du hurtigt udfyld celler i Excel indtil den næste udfyldte værdi.
Du kan nemt gøre dette ved hjælp af en simpel Go-To speciel dialogboks teknik, VBA eller Power Query.
Så lad os komme i gang!
Metode 1 - Udfyld ved hjælp af Gå til Special + Formel
Antag, at du har et datasæt som vist herunder, og du vil udfylde data i kolonne A og kolonne B.
I kolonne B er målet at udfylde 'Printer' indtil den sidste tomme celle under den, og derefter når 'Scanner' starter, derefter fylde 'Scanner' i cellerne herunder, indtil cellerne er tomme.
Under trinene til brug skal du gå til special for at markere alle de tomme celler og udfylde ved hjælp af en simpel formel:
- Vælg de data, du vil udfylde (A1: D21 i vores eksempel)
- Gå til fanen 'Hjem'
- I gruppen Redigering skal du klikke på ikonet 'Find og vælg' (dette viser nogle flere muligheder i rullemenuen)
- Klik på indstillingen 'Gå til'
- Klik på knappen Special i dialogboksen Gå til, der åbnes. Dette åbner dialogboksen 'Gå til speciel'
- Klik på Emner i dialogboksen Gå til speciel
- Klik på OK
Ovenstående trin ville markere alle de tomme celler i datasættet (som vist nedenfor).
I de tomme celler, der er markeret, vil du bemærke, at en celle er lettere end resten af markeringen. Denne celle er den aktive celle, hvor vi skal indtaste en formel.
Du skal ikke bekymre dig om, hvor cellen er i markeringen, da vores metode under alle omstændigheder ville fungere.
Nu er trinene nedenfor for at udfylde dataene i de valgte tomme celler:
- Tryk på tasten lige med (=) på dit tastatur. Dette vil indsætte et lig med tegn i den aktive celle
- Tryk på pil op. Dette vil indsætte cellereferencen for cellen over den aktive celle. For eksempel er B3 i vores tilfælde den aktive celle, og når vi udfører disse to trin, indtaster den = B2 i cellen
- Hold Ctrl -tasten nede, og tryk på Enter -tasten
Ovenstående trin indsætter automatisk alle de tomme celler med værdien over dem.
Selvom dette kan ligne for mange trin, kan du, når du får styr på denne metode, hurtigt udfylde data i Excel inden for få sekunder.
Nu er der yderligere to ting, du skal passe på, når du bruger denne metode.
Konverter formler til værdier
Den første er at sikre, at du konverterer formler til værdier (så du har statiske værdier, og tingene ikke ødelægger, hvis du ændrer data i fremtiden).
Nedenfor er en video, der viser dig, hvordan du hurtigt konverterer formler til værdier i Excel.
Skift datoformat
Hvis du bruger datoer i dine data (som jeg er i eksempeldata), vil du bemærke, at de udfyldte værdier i datokolonnen er tal og ikke datoer.
Hvis output i dato -kolonnen er i det ønskede datoformat, er du god og behøver ikke gøre andet.
Men hvis disse ikke er i det korrekte datoformat, skal du foretage en mindre justering. Selvom du har de rigtige værdier, skal du bare ændre formatet, så det vises som en dato i cellen.
Nedenfor er trinene for at gøre dette:
- Vælg den kolonne, der har datoerne
- Klik på fanen Startside
- I gruppen Tal skal du klikke på rullemenuen Format og derefter vælge datoformatet.
Hvis du har brug for at udføre denne udfyldning en gang imellem, anbefaler jeg, at du bruger denne Go-To special- og formelteknik.
Selvom det har et par trin, er det enkelt og giver dig resultatet lige der i dit datasæt.
Men hvis du skal gøre dette ret ofte, så foreslår jeg, at du ser på VBA og Power Query -metoderne, der dækkes derefter
Metode 2 - Udfyld ved hjælp af en simpel VBA -kode
Du kan bruge en virkelig simpel VBA -makrokode til hurtigt at udfylde celler i Excel indtil den sidste tomme værdi.
Du behøver kun at tilføje VBA -koden til filen én gang, og derefter kan du nemt genbruge koden flere gange i den samme projektmappe eller endda flere projektmapper på dit system.
Nedenfor er VBA -koden, der vil gå gennem hver celle i markeringen og udfylde alle de celler, der er tomme:
'Denne VBA -kode er oprettet af Sumit Bansal fra TrumpExcel.com Sub FillDown () For hver celle i markering Hvis cell = "" Så cell.FillDown slutter, hvis næste ende Sub
Ovenstående kode bruger en For -sløjfe til at gå gennem hver celle i markeringen.
I For-sløjfen har jeg brugt en If-Then-betingelse, der kontrollerer, om cellen er tom eller ej.
Hvis cellen er tom, er den fyldt med værdien fra cellen ovenfor, og hvis den ikke er tom, ignorerer for -sløjfen simpelthen cellen og flytter til den næste.
Nu hvor du har VBA -koden, lad mig vise dig, hvor du skal placere denne kode i Excel:
- Vælg de data, hvor du vil udfylde
- Klik på fanen Udvikler
- Klik på Visual Basic -ikonet (eller du kan bruge tastaturgenvejen ALT + F11). Dette åbner Visual Basic Editor i Excel
- I Visual Basic Editor til venstre ville du have Project Explorer. Hvis du ikke kan se det, skal du klikke på "Vis" i menuen og derefter klikke på Project Explorer
- Hvis du har flere Excel -projektmapper åbne, viser Project Explorer dig alle projektmappens navne. Find navnet på projektmappen, hvor du har dataene.
- Højreklik på et af objekterne i projektmappen, gå til Indsæt, og klik derefter på Modul. Dette vil indsætte et nyt modul til den pågældende projektmappe
- Dobbeltklik på det 'modul', du lige har indsat i ovenstående trin. Det åbner kodevinduet for det pågældende modul
- Kopier og indsæt VBA-koden i kodevinduet
- Placer markøren et vilkårligt sted i koden, og kør derefter makrokoden ved at klikke på den grønne knap i værktøjslinjen eller bruge tastaturgenvejen F5
Ovenstående trin ville køre VBA -koden, og dine data ville blive udfyldt.
Hvis du vil bruge denne VBA-kode igen i fremtiden, skal du gemme denne fil som en makroaktiveret Excel-projektmappe (med en .XLSM-udvidelse)
En anden ting, du kan gøre, er at tilføje denne makro til din værktøjslinje til hurtig adgang, som altid er synlig, og du har adgang til denne makro med et enkelt klik (i projektmappen, hvor du har koden i backend).
Så næste gang du har data, hvor du skal udfylde, skal du blot foretage valget og derefter klikke på makro-knappen i værktøjslinjen Hurtig adgang.
Du kan også tilføje denne makro til den personlige makro -projektmappe og derefter bruge den i enhver projektmappe på dit system.
Klik her for at læse mere om personlige makro -projektmapper og hvordan du tilføjer kode til dem.
Metode 3 - Udfyld ved hjælp af Power Query
Power Query har en indbygget funktionalitet, der giver dig mulighed for at udfylde med et enkelt klik.
Det anbefales at bruge Power Query, når du alligevel bruger dem til at transformere dine data eller kombinere data fra flere regneark eller flere projektmapper.
Som en del af din eksisterende arbejdsgang kan du bruge udfyldningsindstillingen i Power Query til hurtigt at udfylde de tomme celler.
For at bruge Power Query anbefales det, at dine data er i et Excel -tabelformat. Hvis du ikke kan konvertere dine data til en Excel -tabel, skal du oprette et navngivet område for dataene og derefter bruge det navngivne område i Power Query.
Nedenfor har jeg det datasæt, som jeg allerede har konverteret til en Excel -tabel. Du kan gøre det ved at vælge datasættet, gå til fanen Indsæt og derefter klikke på ikonet Tabel.
Nedenfor trinene til at bruge Power Query til at udfylde data indtil den næste værdi:
- Vælg en celle i datasættet
- Klik på fanen Data
- Klik på 'Fra ark' i gruppen Hent og transformer data. Dette åbner Power Query -editoren. Bemærk, at de tomme celler vil vise værdien 'null' i Power Query
- Vælg de kolonner, du vil udfylde i Power Query -editoren. For at gøre dette skal du holde Ctrl -tasten nede og derefter klikke på overskriften på de kolonner, du vil vælge. I vores eksempel ville det være kolonnen Dato og Produkt.
- Højreklik på en af de valgte overskrifter
- Gå til indstillingen 'Udfyld', og klik derefter på 'Ned'. Dette ville udfylde dataene i alle de tomme celler
- Klik på fanen Filer, og klik derefter på 'Luk og indlæs'. Dette indsætter et nyt regneark i projektmappen med den resulterende tabel
Selvom denne metode kan lyde lidt af en overkill, er en stor fordel ved at bruge Power Query, at den giver dig mulighed for hurtigt at opdatere de resulterende data, hvis dine originale data ændres.
Hvis du f.eks. Tilføjer flere poster til de originale data, behøver du ikke at gøre alt det ovenstående igen. Du kan simpelthen højreklikke på den resulterende tabel og derefter klikke på Opdater.
Selvom Power Query -metoden fungerer godt, er det bedst at bruge den, når du allerede bruger Power Query i din arbejdsgang.
Hvis du bare har et datasæt, hvor du vil udfylde de tomme celler, ville det være mere bekvemt at bruge specialmetoden Gå til eller VBA -metoden (dækket ovenfor).
Så det er tre enkle måder, du kan bruge til udfyld tomme celler indtil den næste værdi i Excel.
Jeg håber, at du fandt denne vejledning nyttig.
