Izpolnite prazne celice do naslednje vrednosti v Excelu (3 enostavni načini)

Velikokrat lahko naletite na nabor podatkov Excel, kjer je samo ena celica napolnjena s podatki, celice pod njo pa so prazne do naslednje vrednosti.

Kot je prikazano spodaj:

Čeprav ta oblika deluje za nekatere ljudi, je težava s tovrstnimi podatki v tem, da jih ne morete uporabiti za ustvarjanje vrtilnih tabel ali jih uporabiti pri izračunih.

To je enostavno popraviti!

V tej vadnici vam bom pokazal, kakoHitro izpolnite celice do naslednje vrednosti polnjenja v Excelu.

To lahko enostavno storite z uporabo preprostih posebnih pogovornih tehnik Go-To, VBA ali Power Query.

Pa začnimo!

1. način – Izpolnite s formulo Go To Special +

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite podatke izpolniti v stolpcih A in B.

V stolpcu B je cilj napolniti "tiskalnik" do zadnje prazne celice pod njim, nato, ko se "skener" začne, nato napolniti "skener" v spodnji celici, dokler celica ni prazna.

Spodnji koraki uporabite pojdite na posebno, da izberete vse prazne celice in jih izpolnite s preprosto formulo:

1. Izberite podatke, ki jih želite izpolniti (A1:D21 v našem primeru)
2. Pojdite na zavihek "Domov".

3. V skupini za urejanje kliknite ikono Najdi in izberite (to bo razkrilo več možnosti v spustnem meniju)
4. Kliknite na možnost "Pojdi".

5. V pogovornem oknu Pojdi na, ki se odpre, kliknite gumb Posebno.To bo odprlo pogovorno okno »Pojdi na posebno«.

6. V pogovornem oknu Pojdi na posebno kliknite Prazen

7. Kliknite V redu

Zgornji koraki bodo izbrali vse prazne celice v naboru podatkov (prikazano spodaj).

Med izbranimi praznimi celicami boste opazili, da je ena celica svetlejša od ostalega izbora.Ta celica je aktivna celica, kamor želimo vnesti formulo.

Naj vas ne skrbi položaj celice v izboru, saj bo naša metoda v vsakem primeru delovala.

Tukaj so naslednji koraki za izpolnjevanje podatkov v izbranih praznih celicah:

1. Kliknite tipko z znakom enakosti (=) na tipkovnici.To bo v aktivno celico vstavilo znak enakosti
2. Pritisnite puščično tipko navzgor.To bo vstavilo referenco celice nad aktivno celico.Na primer, v našem primeru je B3 aktivna celica in ko naredimo ta dva koraka, v celico vnese =B2
3. Držite tipko Control in pritisnite Enter

Zgornji koraki bodo samodejno vstavili vse prazne celice z zgornjimi vrednostmi.

Čeprav se morda zdi, da je to preveč korakov, ko se boste navadili, boste lahko podatke v Excelu hitro izpolnili v nekaj sekundah.

Pri uporabi te metode se morate zavedati še dveh stvari.

Pretvarjanje formule v vrednost

Prvi je, da poskrbite, da boste formule pretvorili v vrednosti (da boste imeli statične vrednosti in se ne boste zmotili, ko boste v prihodnosti spremenili podatke).

Spremenite obliko datuma

Če v svojih podatkih uporabljate datume (kot sem naredil v svojem primeru podatkov), boste opazili, da so v stolpcu datuma številke namesto datumov.

Če je izpis v stolpcu datuma v želeni obliki datuma, potem ste v redu in vam ni treba storiti ničesar drugega.

Če pa ti datumi niso v pravilni obliki, je potrebna fina nastavitev.Medtem ko imate pravilno vrednost, morate samo spremeniti obliko, tako da se prikaže kot datum v celici.

Tu so koraki za to:

1. Izberite stolpce z datumi
2. Kliknite zavihek Domov
3. V skupini Številke kliknite spustni meni Oblika in izberite obliko datuma.

Če morate občasno opraviti to oblazinjenje, priporočam to posebno tehniko Go-To in formulo.

Čeprav ima nekaj korakov, je preprost in daje rezultate v naboru podatkov.

Če pa morate to početi pogosto, vam predlagam, da si ogledate VBA in naslednjo opisano metodo Power Query

2. način - Izpolnite s preprosto kodo VBA

Z zelo preprosto kodo makra VBA lahko hitro zapolnite celice v Excelu do zadnje prazne vrednosti.

Datoteki morate samo enkrat dodati kodo VBA, nato pa lahko to kodo enostavno znova uporabite večkrat v istem delovnem zvezku ali celo v več delovnih zvezkih v vašem sistemu.

Tukaj je koda VBA, ki se bo pomikala skozi vsako celico v izboru in izpolnila vse prazne celice:

Sub FillDown() Za vsako celico v izboru Če je celica = "" Potem cell.FillDown Konec, če je naslednja konec Sub

Zgornja koda uporablja zanko For za ponavljanje skozi vsako celico v izboru.

V zanki For uporabljam pogoj If-Then, da preverim, ali je celica prazna.

Če je celica prazna, se napolni z vrednostjo iz zgornje celice, če ne, zanka for prezre celico in se premakne na naslednjo celico.

Zdaj, ko imate kodo VBA, naj vam pokažem, kam vstaviti to kodo v Excel:

1. Izberite podatke, ki jih želite izpolniti
2. Kliknite zavihek razvijalec

3. Kliknite ikono Visual Basic (ali pa uporabite bližnjico na tipkovnici ALT+F11).To bo odprlo urejevalnik Visual Basic v Excelu

4. V urejevalniku Visual Basic na levi boste imeli Project Explorer.Če ga ne vidite, v meniju kliknite možnost Pogled in nato Raziskovalec projektov

5. Če imate odprtih več Excelovih delovnih zvezkov, Project Explorer prikaže vsa imena delovnih zvezkov.Poiščite ime delovnega zvezka, v katerem imate podatke.
6. Z desno tipko miške kliknite kateri koli predmet v delovnem zvezku, pojdite na Vstavi in ​​kliknite Modul.To bo vstavilo nov modul za ta delovni zvezek

7. Dvokliknite "Modul", ki ste ga pravkar vstavili v zgornjih korakih.Odprlo se bo okno kode za ta modul

8. Kopirajte in prilepite kodo VBA v okno kode
9. Postavite kazalec kamor koli v kodo in zaženite kodo makra s klikom na zeleni gumb v orodni vrstici ali z bližnjico na tipkovnici F5

Zgornji koraki bodo zagnali kodo VBA in vaši podatki bodo izpolnjeni.

Če želite pozneje ponovno uporabiti to kodo VBA, boste morali to datoteko shraniti kot Excelov delovni zvezek z omogočenimi makri (razširitev .XLSM)

Druga stvar, ki jo lahko storite, je, da dodate ta makro v orodno vrstico za hitri dostop, ki je vedno vidna in do tega makra lahko dostopate z enim klikom (v delovnem zvezku s kodo v ozadju).

Ko boste naslednjič imeli podatke, ki jih morate izpolniti, lahko preprosto izberete in kliknete gumb makra v orodni vrstici za hitri dostop.

Ta makro lahko dodate tudi v svoj osebni delovni zvezek z makrami in ga nato uporabite v katerem koli delovnem zvezku v vašem sistemu.

Kliknite tukaj, če želite prebrati več o svojem osebnem delovnem zvezku z makrami in o tem, kako mu dodate kodo.

3. način - Izpolnite s pomočjo Power Query

poizvedba o močiIma vgrajeno funkcijo, ki vam omogoča, da jo izpolnite z enim klikom.

Priporočljivo je, če še vedno uporabljate Power Query za pretvorbo podatkov ali združevanje podatkov iz več delovnih listov ali več delovnih zvezkov.

Kot del obstoječega poteka dela lahko hitro zapolnite prazne celice z možnostjo Polni v Power Queryju.

Za uporabo Power Query priporočamo, da so vaši podatki v obliki tabele Excel.Če podatkov ne morete pretvoriti v Excelovo tabelo, morate ustvariti poimenovani obseg za podatke in nato uporabiti ta poimenovani obseg v Power Query.

Spodaj imam nabor podatkov, ki je bil pretvorjen v Excelovo tabelo.To lahko storite tako, da izberete nabor podatkov, odprete zavihek Vstavljanje in kliknete ikono Tabela.

Tu so koraki za uporabo Power Query za izpolnjevanje podatkov do naslednje vrednosti:

1. Izberite katero koli celico v naboru podatkov
2. Kliknite zavihek Podatki

3. V skupini Pridobi in pretvori podatke kliknite Iz delovnega lista.To bo odprlo urejevalnik Power Query.Upoštevajte, da bodo prazne celice prikazale vrednost "null" v Power Query

4. V urejevalniku Power Query izberite stolpec, ki ga želite izpolniti.Če želite to narediti, držite tipko Control in kliknite glavo stolpca, ki ga želite izbrati.V našem primeru bosta to stolpca datum in izdelek.
5. Desni klik na kateri koli izbrani naslov

6. Pojdite na možnost Fill in kliknite navzdol.To bo zapolnilo vse prazne celice s podatki

7. Kliknite zavihek Datoteka in nato Zapri in naloži.To bo v delovni zvezek vstavilo nov delovni list s tabelo rezultatov

Čeprav se ta pristop morda sliši kot pretirano, je ena od velikih prednosti uporabe Power Query ta, da vam omogoča hitro posodobitev dobljenih podatkov, ko se izvirni podatki spremenijo.

Na primer, če izvirnim podatkom dodate več zapisov, vam ni treba ponoviti vsega zgoraj navedenega.Lahko samo z desno miškino tipko kliknete na nastalo tabelo in kliknete Osveži.

Čeprav metoda Power Query deluje dobro, jo je najbolje uporabiti, ko že uporabljate Power Query v svojem delovnem toku.

Če imate za izpolnitev samo en nabor podatkov s praznimi celicami, je bolj priročno uporabiti posebno metodo Go To ali metodo VBA (opisano zgoraj).

Torej lahko uporabite te tri preproste metodeIzpolnite prazne celice do naslednje vrednosti v Excelu.

Upam, da se vam je ta vadnica zdela koristna.