Gledajte video - Popunite Excel
Mnogo puta možete naići na skup podataka u kojem je samo jedna ćelija ispunjena podacima, a ćelije ispod nje prazne do sljedeće vrijednosti.
Nešto kao što je prikazano ispod:
Iako ovaj format funkcionira za neke ljude, problem s ovom vrstom podataka je taj što ga ne možete koristiti za izradu zaokretnih tablica niti ih koristiti u izračunima.
I ovo se lako rješava!
U ovom vodiču ću vam pokazati kako to učiniti brzo ispunite ćelije u Excelu do sljedeće ispunjene vrijednosti.
To možete učiniti jednostavno pomoću jednostavne Go-To posebne tehnike dijaloškog okvira, VBA ili Power Query.
Pa krenimo!
Metoda 1 - Popunite pomoću Go to Special + Formule
Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite popuniti podatke u stupce A i stupac B.
U stupcu B cilj je ispuniti "Pisač" do posljednje prazne ćelije ispod njega, a zatim kada se pokrene "Skener", zatim ispunite "Skener" u donje ćelije dok se ćelije ne isprazne.
Ispod koraka za korištenje idite na posebne za odabir svih praznih ćelija i popunite ih pomoću jednostavne formule:
- Odaberite podatke koje želite popuniti (A1: D21 u našem primjeru)
- Idite na karticu "Početna"
- U grupi Uređivanje kliknite ikonu "Pronađi i odaberi" (ovo će prikazati još neke opcije u padajućem izborniku)
- Kliknite na opciju "Idi na"
- U dijaloškom okviru Go-To koji se otvori kliknite gumb Poseban. Ovo će otvoriti dijaloški okvir "Idi na posebno"
- U dijaloškom okviru Idi na posebno kliknite Praznine
- Pritisnite U redu
Gore navedeni koraci odabrali bi sve prazne ćelije u skupu podataka (kao što je prikazano u nastavku).
U praznim ćelijama koje ste odabrali primijetili biste da je jedna ćelija svjetlija od ostatka odabira. Ova ćelija je aktivna ćelija u koju ćemo unijeti formulu.
Ne brinite o tome gdje se ćelija nalazi u odabiru, jer bi naša metoda u svakom slučaju djelovala.
U nastavku slijede koraci za popunjavanje podataka u odabranim praznim ćelijama:
- Pritisnite tipku jednako na (=) na tipkovnici. To će umetnuti znak jednakosti u aktivnu ćeliju
- Pritisnite tipku sa strelicom prema gore. To će umetnuti referencu ćelije ćelije iznad aktivne ćelije. Na primjer, u našem slučaju, B3 je aktivna ćelija i kada napravimo ova dva koraka, ona unosi = B2 u ćeliju
- Držite tipku Control i pritisnite tipku Enter
Gore navedeni koraci automatski bi umetnuli sve prazne ćelije s vrijednošću iznad njih.
Iako ovo može izgledati kao previše koraka, kad se naviknete na ovu metodu, moći ćete brzo popuniti podatke u Excelu u roku od nekoliko sekundi.
Postoje još dvije stvari na koje morate paziti kada koristite ovu metodu.
Pretvorite formule u vrijednosti
Prvi je da se pobrinete da formule pretvorite u vrijednosti (tako da imate statičke vrijednosti i da se stvari ne pokvare u slučaju da promijenite podatke u budućnosti).
Ispod je videozapis koji će vam pokazati kako brzo pretvoriti formule u vrijednosti u Excelu.
Promijenite format datuma
U slučaju da koristite datume u svojim podacima (kao što sam ja u primjerima podataka), primijetili biste da su ispunjene vrijednosti u stupcu datuma brojevi, a ne datumi.
Ako su rezultati u stupcu datuma u željenom formatu datuma, dobri ste i ne morate raditi ništa drugo.
No ako nisu u ispravnom formatu datuma, morate izvršiti manju prilagodbu. Iako imate prave vrijednosti, samo trebate promijeniti format tako da se prikazuje kao datum u ćeliji.
U nastavku su navedeni koraci za to:
- Odaberite stupac s datumima
- Kliknite karticu Početna
- U grupi Broj kliknite padajući izbornik Oblikovanje, a zatim odaberite format datuma.
Ako s vremena na vrijeme trebate izvršiti ovo popunjavanje, preporučujem vam da koristite ovu Go-To posebnu tehniku i formulu formule.
Iako ima nekoliko koraka, jednostavno je i daje rezultat upravo u vašem skupu podataka.
Ali u slučaju da to morate činiti prilično često, onda vam predlažem da pogledate VBA i Power Query metode koje su obrađene sljedeće
Metoda 2 - Popunite jednostavnim VBA kodom
Možete koristiti vrlo jednostavan VBA makro kôd za brzo popunjavanje ćelija u Excelu do zadnje prazne vrijednosti.
Samo jednom morate dodati VBA kôd u datoteku, a zatim možete jednostavno ponovno koristiti kôd više puta u istoj radnoj knjizi ili čak u više radnih knjiga na vašem sustavu.
Ispod je VBA kôd koji će proći kroz svaku ćeliju u odabiru i ispuniti sve ćelije koje su prazne:
'Ovaj VBA kôd stvorio je Sumit Bansal sa stranice TrumpExcel.com Sub FillDown () Za svaku ćeliju u odabiru If cell = "" Zatim ćelija. FillDown End If Next End Sub
Gornji kôd koristi petlju For za prolazak kroz svaku ćeliju u odabiru.
Unutar For petlje koristio sam uvjet If-Then koji provjerava je li ćelija prazna ili nije.
U slučaju da je ćelija prazna, popunjena je vrijednošću iz gornje ćelije, a u slučaju da nije prazna, for petlja jednostavno zanemaruje ćeliju i prelazi na sljedeću.
Sada kada imate VBA kôd, dopustite mi da vam pokažem gdje staviti ovaj kôd u Excel:
- Odaberite podatke koje želite popuniti
- Kliknite karticu Developer
- Pritisnite ikonu Visual Basic (ili možete koristiti tipkovnički prečac ALT + F11). Ovo će otvoriti uređivač Visual Basic u Excelu
- U uređivaču Visual Basic, s lijeve strane, imate Project Explorer. Ako ga ne vidite, kliknite opciju 'Prikaz' u izborniku, a zatim kliknite Project Explorer
- U slučaju da imate više otvorenih radnih knjiga programa Excel, Project Explorer će vam pokazati sve nazive radnih knjiga. Pronađite naziv radne knjige gdje imate podatke.
- Desnom tipkom miša kliknite bilo koji objekt u radnoj knjizi, idite na Umetni, a zatim kliknite na Modul. Ovo će umetnuti novi modul za tu radnu knjigu
- Dvaput kliknite na ‘Modul’ koji ste upravo umetnuli u gornjem koraku. Otvorit će se prozor koda za taj modul
- Kopirajte i zalijepite VBA kôd u prozor koda
- Postavite kursor bilo gdje u kodu, a zatim pokrenite makro kôd klikom na zeleni gumb na alatnoj traci ili pomoću tipkovnog prečaca F5
Gore navedeni koraci pokrenuli bi VBA kôd i vaši bi se podaci popunili.
Ako ubuduće želite ponovo koristiti ovaj VBA kôd, morate spremiti ovu datoteku kao Excel radnu knjigu s omogućenim makroima (s nastavkom .XLSM)
Još jedna stvar koju možete učiniti je dodati ovu makronaredbu na svoju Alatnu traku za brzi pristup, koja je uvijek vidljiva i imat ćete pristup ovoj makronaredbi jednim klikom (u radnoj knjizi gdje imate kôd u pozadini).
Dakle, sljedeći put kad budete imali podatke koje trebate popuniti, sve što trebate učiniti je izvršiti odabir, a zatim kliknuti gumb makronaredbe na alatnoj traci za brzi pristup.
Ovu makronaredbu možete dodati i u Radnu knjigu osobnih makronaredbi, a zatim je koristiti u bilo kojoj radnoj knjizi na vašem sustavu.
Kliknite ovdje da biste pročitali više o osobnim makro radnim knjigama i kako im dodati kod.
Metoda 3 - Popunite pomoću Power Queryja
Power Query ima ugrađenu funkcionalnost koja vam omogućuje popunjavanje jednim klikom.
Upotreba programa Power Query preporučuje se kada ga svejedno koristite za pretvaranje podataka ili kombiniranje podataka s više radnih listova ili više radnih knjiga.
Kao dio vašeg postojećeg tijeka rada, možete upotrijebiti opciju popunjavanja u Power Queryju za brzo popunjavanje praznih ćelija.
Da biste koristili Power Query, preporučuje se da vaši podaci budu u Excel tabličnom formatu. Ako ne možete pretvoriti svoje podatke u Excel tablicu, morat ćete stvoriti imenovani raspon za podatke, a zatim koristiti taj imenovani raspon u Power Queryju.
U nastavku imam skup podataka koji sam već pretvorio u Excel tablicu. To možete učiniti odabirom skupa podataka, odlaskom na karticu Umetanje, a zatim klikom na ikonu Tablica.
Ispod koraka za korištenje Power Queryja za popunjavanje podataka do sljedeće vrijednosti:
- Odaberite bilo koju ćeliju u skupu podataka
- Kliknite karticu Podaci
- U grupi Get & Transform Data kliknite "From Sheet". Ovo će otvoriti uređivač Power Query. Imajte na umu da će prazne ćelije prikazati vrijednost 'null' u Power Queryju
- U uređivaču programa Power Query odaberite stupce koje želite popuniti. Da biste to učinili, držite tipku Control, a zatim kliknite zaglavlje stupaca koje želite odabrati. U našem primjeru to bi bili stupci Datum i Proizvod.
- Desnom tipkom miša kliknite bilo koje od odabranih zaglavlja
- Idite na opciju "Ispuni", a zatim kliknite na "Dolje". To bi ispunilo podatke u svim praznim ćelijama
- Kliknite karticu Datoteka, a zatim kliknite "Zatvori i učitaj". Ovo će umetnuti novi radni list u radnu knjigu s rezultirajućom tablicom
Iako ova metoda može zvučati pomalo pretjerano, jedna velika prednost korištenja programa Power Query je ta što vam omogućuje brzo ažuriranje rezultirajućih podataka u slučaju da se promijene izvorni podaci.
Na primjer, ako izvornim podacima dodate više zapisa, ne morate ponovno učiniti sve gore navedeno. Jednostavno desnom tipkom miša kliknite rezultirajuću tablicu, a zatim kliknite Osvježi.
Iako metoda Power Query dobro funkcionira, najbolje je koristiti je kada već koristite Power Query u tijeku rada.
Ako imate samo skup podataka u kojem želite popuniti prazne ćelije, bilo bi prikladnije koristiti posebnu metodu Idi na ili VBA metodu (gore opisano).
Dakle, ovo su tri jednostavna načina na koja možete koristiti ispunite prazne ćelije do sljedeće vrijednosti u Excelu.
Nadam se da vam je ovaj vodič bio koristan.
