Se video - Fyll ned i Excel
Mange ganger kan du støte på et datasett der bare én celle er fylt med data og cellene under den er tomme til neste verdi.
Noe som vist nedenfor:
Selv om dette formatet fungerer for noen mennesker, er problemet med denne typen data at du ikke kan bruke dem til å lage pivottabeller eller bruke dem i beregninger.
Og dette har en enkel løsning!
I denne opplæringen vil jeg vise deg hvordan du raskt fyll ut cellene i Excel til neste fylte verdi.
Du kan enkelt gjøre dette ved å bruke en enkel Go-To spesiell dialogboks-teknikk, VBA eller Power Query.
Så la oss komme i gang!
Metode 1 - Fyll ned med Go to Special + Formula
Anta at du har et datasett som vist nedenfor, og du vil fylle ut data i kolonne A og kolonne B.
I kolonne B er målet å fylle "Skriver" til den siste tomme cellen under den, og deretter når "Skanner" starter, og deretter fylle "Skanner" i cellene nedenfor til cellene er tomme.
Under trinnene for å bruke, gå til spesial for å velge alle de tomme cellene og fyll ut ved hjelp av en enkel formel:
- Velg dataene du vil fylle ut (A1: D21 i vårt eksempel)
- Gå til fanen "Hjem"
- I redigeringsgruppen klikker du på "Finn og velg" -ikonet (dette viser noen flere alternativer i rullegardinmenyen)
- Klikk på "Gå til" -alternativet
- Klikk på Spesial-knappen i dialogboksen Gå til. Dette åpner dialogboksen "Gå til spesiell"
- Klikk på Tomrom i dialogboksen Gå til spesiell
- Klikk OK
Trinnene ovenfor vil velge alle de tomme cellene i datasettet (som vist nedenfor).
I de tomme cellene som er merket, vil du legge merke til at en celle er lettere enn resten av utvalget. Denne cellen er den aktive cellen der vi skal skrive inn en formel.
Ikke bekymre deg for hvor cellen er i utvalget, da vår metode uansett ville fungere.
Nå er trinnene nedenfor for å fylle ut dataene i de valgte tomme cellene:
- Trykk på tasten som er lik (=) på tastaturet. Dette vil sette inn en likhetstegn i den aktive cellen
- Trykk på pil opp. Dette vil sette inn cellereferansen til cellen over den aktive cellen. For eksempel, i vårt tilfelle, er B3 den aktive cellen, og når vi gjør disse to trinnene, kommer den inn = B2 i cellen
- Hold inne Control -tasten, og trykk på Enter -tasten
De ovennevnte trinnene vil automatisk sette inn alle de tomme cellene med verdien over dem.
Selv om dette kan se ut som for mange trinn, kan du raskt fylle ut data i Excel innen få sekunder når du får tak i denne metoden.
Nå er det to ting du må ta vare på når du bruker denne metoden.
Konverter formler til verdier
Den første er å sørge for at du konverterer formler til verdier (slik at du har statiske verdier og ting ikke roter i tilfelle du endrer data i fremtiden).
Nedenfor er en video som viser deg hvordan du raskt konverterer formler til verdier i Excel.
Endre datoformatet
Hvis du bruker datoer i dataene dine (slik jeg er i eksempeldataene), vil du legge merke til at de utfylte verdiene i datokolonnen er tall og ikke datoer.
Hvis utdataene i dato -kolonnen er i ønsket datoformat, er du flink og trenger ikke gjøre noe annet.
Men hvis disse ikke er i riktig datoformat, må du gjøre en liten justering. Selv om du har de riktige verdiene, trenger du bare å endre formatet slik at det vises som en dato i cellen.
Nedenfor er trinnene for å gjøre dette:
- Velg kolonnen som har datoene
- Klikk på kategorien Hjem
- Klikk på rullegardinmenyen Format i Nummer-gruppen, og velg deretter datoformatet.
Hvis du trenger å gjøre denne utfyllingen en gang i blant, anbefaler jeg at du bruker denne Go-To spesial- og formelteknikken.
Selv om det har noen få trinn, er det enkelt og gir deg resultatet der i datasettet ditt.
Men hvis du må gjøre dette ganske ofte, så foreslår jeg at du ser på VBA og Power Query -metodene som dekkes neste
Metode 2 - Fyll ned ved hjelp av en enkel VBA -kode
Du kan bruke en veldig enkel VBA -makrokode til raskt å fylle ut celler i Excel til den siste tomme verdien.
Du trenger bare å legge til VBA -koden i filen én gang, og deretter kan du enkelt gjenbruke koden flere ganger i den samme arbeidsboken eller til og med flere arbeidsbøker på systemet ditt.
Nedenfor er VBA -koden som går gjennom hver celle i utvalget og fyller ut alle cellene som er tomme:
'Denne VBA -koden er opprettet av Sumit Bansal fra TrumpExcel.com Sub FillDown () For hver celle i utvalg Hvis cell = "" Deretter cell.FillDown Slutt hvis Next End Sub
Koden ovenfor bruker en For -løkke for å gå gjennom hver celle i utvalget.
I For-løkken har jeg brukt en If-Then-tilstand som kontrollerer om cellen er tom eller ikke.
Hvis cellen er tom, er den fylt med verdien fra cellen ovenfor, og i tilfelle den ikke er tom, ignorerer for -sløyfen ganske enkelt cellen og går til den neste.
Nå som du har VBA -koden, la meg vise deg hvor du skal sette denne koden i Excel:
- Velg dataene du vil fylle ut
- Klikk på Utvikler -fanen
- Klikk på Visual Basic -ikonet (eller du kan bruke hurtigtasten ALT + F11). Dette åpner Visual Basic Editor i Excel
- I Visual Basic Editor, til venstre, vil du ha Project Explorer. Hvis du ikke ser det, klikker du på "View" -alternativet i menyen og klikker deretter på Project Explorer
- Hvis du har flere Excel -arbeidsbøker åpne, viser Project Explorer deg alle arbeidsboknavnene. Finn arbeidsboknavnet der du har dataene.
- Høyreklikk på et av objektene i arbeidsboken, gå til Sett inn, og klikk deretter på Modul. Dette vil sette inn en ny modul for den arbeidsboken
- Dobbeltklikk på "Modulen" du nettopp satte inn i trinnet ovenfor. Det åpner kodevinduet for den modulen
- Kopier og lim inn VBA-koden i kodevinduet
- Plasser markøren hvor som helst i koden, og kjør deretter makrokoden ved å klikke på den grønne knappen på verktøylinjen eller bruke hurtigtasten F5
Trinnene ovenfor vil kjøre VBA -koden, og dataene dine vil bli fylt ut.
Hvis du vil bruke denne VBA-koden igjen i fremtiden, må du lagre denne filen som en makroaktivert Excel-arbeidsbok (med en .XLSM-forlengelse)
En annen ting du kan gjøre er å legge denne makroen til verktøylinjen for hurtig tilgang, som alltid er synlig, og du har tilgang til denne makroen med et enkelt klikk (i arbeidsboken der du har koden i backend).
Så neste gang du har data der du trenger å fylle ut, er alt du trenger å gjøre å velge og deretter klikke på makroknappen i verktøylinjen for hurtig tilgang.
Du kan også legge denne makroen til Personal Macro Workbook og deretter bruke den i en hvilken som helst arbeidsbok på systemet ditt.
Klikk her for å lese mer om personlige makroarbeidsbøker og hvordan du legger til kode i dem.
Metode 3 - Fyll ned ved hjelp av Power Query
Power Query har en innebygd funksjonalitet som lar deg fylle ned med et enkelt klikk.
Det anbefales å bruke Power Query når du bruker det uansett for å transformere dataene dine eller kombinere data fra flere regneark eller flere arbeidsbøker.
Som en del av din eksisterende arbeidsflyt kan du bruke utfyllingsalternativet i Power Query for raskt å fylle de tomme cellene.
For å bruke Power Query, anbefales det at dataene er i et Excel -tabellformat. Hvis du ikke kan konvertere dataene dine til en Excel -tabell, må du opprette et navngitt område for dataene og deretter bruke det navngitte området i Power Query.
Nedenfor har jeg datasettet som jeg allerede har konvertert til en Excel -tabell. Du kan gjøre det ved å velge datasettet, gå til kategorien Sett inn og deretter klikke på tabellikonet.
Under trinnene for å bruke Power Query til å fylle ut data til neste verdi:
- Velg en celle i datasettet
- Klikk kategorien Data
- Klikk på "Fra ark" i gruppen Hent og transformer data. Dette åpner Power Query -editoren. Vær oppmerksom på at de tomme cellene viser verdien 'null' i Power Query
- I Power Query -editoren velger du kolonnene du vil fylle ut. For å gjøre dette, hold inne Ctrl -tasten og klikk deretter på toppteksten til kolonnene du vil velge. I vårt eksempel vil det være dato- og produktkolonnene.
- Høyreklikk på en av de valgte overskriftene
- Gå til "Fyll" -alternativet, og klikk deretter på "Ned". Dette vil fylle ut dataene i alle de tomme cellene
- Klikk på Fil -fanen, og klikk deretter på "Lukk og last". Dette vil sette inn et nytt regneark i arbeidsboken med den resulterende tabellen
Selv om denne metoden kan høres litt overkill ut, er en stor fordel med å bruke Power Query at den lar deg raskt oppdatere de resulterende dataene i tilfelle de originale dataene endres.
For eksempel, hvis du legger til flere poster i de originale dataene, trenger du ikke å gjøre alt det ovennevnte igjen. Du kan ganske enkelt høyreklikke på den resulterende tabellen og deretter klikke på Oppdater.
Selv om Power Query -metoden fungerer bra, er det best å bruke den når du allerede bruker Power Query i arbeidsflyten din.
Hvis du bare har et datasett der du vil fylle ut de tomme cellene, ville det være mer praktisk å bruke den spesialiserte metoden Gå til eller VBA -metoden (dekket ovenfor).
Så dette er tre enkle måter du kan bruke til fyll ut tomme celler til neste verdi i Excel.
Jeg håper du synes denne opplæringen var nyttig.
