Oglejte si video - izpolnite v Excelu
Velikokrat lahko naletite na nabor podatkov, kjer je samo ena celica napolnjena s podatki, celice pod njim pa prazne do naslednje vrednosti.
Nekaj, kot je prikazano spodaj:
Čeprav ta oblika deluje za nekatere ljudi, je težava s tovrstnimi podatki v tem, da je ne morete uporabiti za ustvarjanje vrtilnih tabel ali za izračun.
In to je enostavno popraviti!
V tej vadnici vam bom pokazal, kako hitro izpolnite celice v Excelu do naslednje zapolnjene vrednosti.
To lahko preprosto storite s preprosto tehniko posebnega pogovornega okna Go-To, VBA ali Power Query.
Pa začnimo!
Metoda 1 - Izpolnite s tipko Pojdi na Special + Formula
Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite izpolniti podatke v stolpcu A in stolpcu B.
V stolpcu B je cilj napolniti "Tiskalnik" do zadnje prazne celice pod njim, nato pa, ko se zažene "Optični bralnik", nato v celice spodaj napolnite "Skener", dokler celice niso prazne.
Pod koraki, ki jih želite uporabiti, pojdite na posebno, da izberete vse prazne celice in jih izpolnite s preprosto formulo:
- Izberite podatke, ki jih želite izpolniti (A1: D21 v našem primeru)
- Pojdite na zavihek »Domov«
- V skupini Urejanje kliknite ikono »Poišči in izberi« (to bo prikazalo še nekaj možnosti v spustnem meniju)
- Kliknite možnost »Pojdi na«
- V pogovornem oknu Pojdi, ki se odpre, kliknite gumb Poseben. S tem se odpre pogovorno okno »Pojdi na posebno«
- V pogovornem oknu Pojdi na posebno kliknite Praznine
- Kliknite V redu
Zgornji koraki bi izbrali vse prazne celice v nizu podatkov (kot je prikazano spodaj).
V praznih celicah, ki so izbrane, boste opazili, da je ena celica lažja od preostalega izbora. Ta celica je aktivna celica, v katero bomo vnesli formulo.
Ne skrbite, kje je celica v izboru, saj bi naša metoda v vsakem primeru delovala.
Spodaj so navedeni koraki za izpolnjevanje podatkov v izbranih praznih celicah:
- Pritisnite tipko enaka (=) na tipkovnici. S tem boste v aktivno celico vstavili znak enakega
- Pritisnite puščično tipko navzgor. To bo vstavilo referenčno celico celice nad aktivno celico. Na primer, v našem primeru je B3 aktivna celica in ko naredimo ta dva koraka, vnese = B2 v celico
- Držite tipko Control in pritisnite tipko Enter
Zgornji koraki bi samodejno vstavili vse prazne celice z vrednostjo nad njimi.
Čeprav je to morda videti kot preveč korakov, boste, ko se boste naučili te metode, v nekaj sekundah hitro napolnili podatke v Excelu.
Pri uporabi te metode morate paziti še na dve stvari.
Pretvorite formule v vrednosti
Prvi je zagotoviti, da formule pretvorite v vrednosti (tako da imate statične vrednosti in da se stvari ne pokvarijo, če v prihodnosti spremenite podatke).
Spodaj je videoposnetek, ki vam bo pokazal, kako hitro pretvorite formule v vrednosti v Excelu.
Spremenite obliko datuma
Če v svojih podatkih uporabljate datume (kot sem jaz v vzorčnih podatkih), boste opazili, da so izpolnjene vrednosti v stolpcu z datumi številke in ne datumi.
Če so rezultati v stolpcu z datumom v želeni obliki zapisa, ste dobri in vam ni treba storiti ničesar drugega.
Če pa niso v pravilnem formatu datuma, morate narediti manjšo prilagoditev. Čeprav imate prave vrednosti, morate le spremeniti obliko, da bo prikazana kot datum v celici.
Spodaj so navedeni koraki za to:
- Izberite stolpec z datumi
- Kliknite zavihek Domov
- V skupini Številka kliknite spustni meni Oblika in nato izberite obliko datuma.
Če morate to občasno napolniti, priporočam, da uporabite to posebno tehniko Go-To in formulo.
Čeprav ima nekaj korakov, je preprosto in vam daje rezultat v vašem naboru podatkov.
Če pa morate to početi precej pogosto, vam predlagam, da si ogledate naslednje metode VBA in Power Query
Metoda 2 - Izpolnite s preprosto kodo VBA
Za preprosto polnjenje celic v Excelu do zadnje prazne vrednosti lahko uporabite zelo preprosto kodo makra VBA.
Kodi VBA morate datoteko dodati le enkrat, nato pa lahko preprosto preprosto večkrat uporabite kodo v istem delovnem zvezku ali celo v več delovnih zvezkih v sistemu.
Spodaj je koda VBA, ki bo šla skozi vsako celico v izboru in zapolnila vse prazne celice:
"To kodo VBA je ustvaril Sumit Bansal iz TrumpExcel.com Sub FillDown () Za vsako celico v izbiri If cell =" "Potem celica. FillDown End If Next End Sub
Zgornja koda uporablja zanko For za prehod skozi vsako celico v izboru.
V zanki For sem uporabil pogoj Če-potem, ki preveri, ali je celica prazna ali ne.
Če je celica prazna, je napolnjena z vrednostjo iz zgornje celice, v primeru, da ni prazna, pa zanka for preprosto ignorira celico in se premakne na naslednjo.
Zdaj, ko imate kodo VBA, naj vam pokažem, kam to kodo postaviti v Excel:
- Izberite podatke, ki jih želite izpolniti
- Kliknite zavihek Razvijalec
- Kliknite ikono Visual Basic (ali uporabite bližnjico na tipkovnici ALT + F11). S tem se odpre urejevalnik Visual Basic v Excelu
- V urejevalniku Visual Basic na levi strani bi imeli Project Explorer. Če je ne vidite, v meniju kliknite možnost »Pogled« in nato kliknite Raziskovalec projektov
- Če imate odprtih več Excelovih delovnih zvezkov, vam bo Project Explorer prikazal vsa imena delovnih zvezkov. Poiščite ime delovnega zvezka, kjer imate podatke.
- Z desno tipko miške kliknite kateri koli predmet v delovnem zvezku, pojdite na Vstavi in nato kliknite na Modul. To bo vstavilo nov modul za ta delovni zvezek
- Dvokliknite »Modul«, ki ste ga pravkar vstavili v zgornjem koraku. Odprlo se bo okno za kodo tega modula
- Kodo VBA kopirajte in prilepite v okno za kodo
- Kazalec postavite kamor koli v kodi in nato zaženite kodo makra s klikom na zeleni gumb v orodni vrstici ali z bližnjico na tipkovnici F5
Zgornji koraki bi zagnali kodo VBA in vaši podatki bi bili izpolnjeni.
Če želite v prihodnje znova uporabiti to kodo VBA, morate to datoteko shraniti kot delovni zvezek Excel, ki podpira makro (z razširitvijo .XLSM)
Druga stvar, ki jo lahko storite, je, da ta makro dodate v orodno vrstico za hitri dostop, ki je vedno vidna in do tega makra boste imeli dostop z enim samim klikom (v delovnem zvezku, kjer imate kodo v ozadju).
Ko boste naslednjič imeli podatke, ki jih morate izpolniti, morate samo izbrati in nato klikniti gumb makra v orodni vrstici za hitri dostop.
Ta makro lahko dodate tudi v delovni zvezek osebnega makra in ga nato uporabite v katerem koli delovnem zvezku v sistemu.
Kliknite tukaj, če želite prebrati več o osebnih makro delovnih zvezkih in kako jim dodati kodo.
Metoda 3 - Izpolnite z uporabo Power Query
Power Query ima vgrajeno funkcionalnost, ki vam omogoča, da izpolnite z enim klikom.
Uporaba Power Query je priporočljiva, če jo vseeno uporabljate za preoblikovanje podatkov ali združevanje podatkov iz več delovnih listov ali več delovnih zvezkov.
Kot del obstoječega poteka dela lahko z možnostjo izpolnitve v Power Queryju hitro zapolnite prazne celice.
Za uporabo programa Power Query je priporočljivo, da so vaši podatki v obliki tabele Excel. Če podatkov ne morete pretvoriti v tabelo Excel, boste morali za podatke ustvariti poimenovano območje in ga nato uporabiti v Power Query.
Spodaj imam nabor podatkov, ki sem ga že pretvoril v Excelovo tabelo. To lahko storite tako, da izberete nabor podatkov, odprete zavihek Vstavi in nato kliknete ikono Tabela.
Pod koraki za uporabo Power Query za polnjenje podatkov do naslednje vrednosti:
- Izberite katero koli celico v naboru podatkov
- Kliknite zavihek Podatki
- V skupini Get & Transform Data kliknite "From Sheet". S tem se odpre urejevalnik Power Query. Upoštevajte, da bodo prazne celice v Power Query prikazovale vrednost 'null'
- V urejevalniku Power Query izberite stolpce, ki jih želite izpolniti. Če želite to narediti, pridržite tipko Control in nato kliknite glavo stolpcev, ki jih želite izbrati. V našem primeru bi bila to stolpca Datum in Izdelek.
- Z desno miškino tipko kliknite katero koli od izbranih glav
- Pojdite na možnost »Izpolni« in kliknite »Dol«. To bi zapolnilo podatke v vseh praznih celicah
- Kliknite zavihek Datoteka in nato »Zapri in naloži«. To bo v delovni zvezek vstavilo nov delovni list z nastalo tabelo
Čeprav lahko ta metoda zveni nekoliko pretirano, je ena velika prednost uporabe programa Power Query ta, da vam omogoča, da hitro posodobite nastale podatke, če se spremenijo izvirni podatki.
Če na primer izvornim podatkom dodate več zapisov, vam ni treba ponoviti vsega zgoraj navedenega. Nastalo tabelo lahko preprosto kliknete z desno miškino tipko in nato kliknete Osveži.
Čeprav metoda Power Query dobro deluje, je najbolje, da jo uporabite, ko v svojem delovnem toku že uporabljate Power Query.
Če imate samo nabor podatkov, v katerem želite zapolniti prazne celice, bi bila primernejša uporaba posebne metode Pojdi na ali VBA (obravnavana zgoraj).
To so torej trije preprosti načini izpolnite prazne celice do naslednje vrednosti v Excelu.
Upam, da vam je bila ta vadnica koristna.
