Žiūrėkite vaizdo įrašą - užpildykite „Excel“
Daug kartų galite susidurti su duomenų rinkiniu, kuriame tik vienas langelis yra užpildytas duomenimis, o langeliai po juo yra tušti iki kitos vertės.
Kažkas, kaip parodyta žemiau:
Nors šis formatas tinka kai kuriems žmonėms, tokio tipo duomenų problema yra ta, kad jūs negalite jų naudoti kurdami „Pivot“ lenteles ar naudodamiesi skaičiavimais.
Ir tai lengva išspręsti!
Šioje pamokoje aš jums parodysiu, kaip greitai užpildykite „Excel“ langelius iki kitos užpildytos vertės.
Tai galite lengvai padaryti naudodami paprastą „Go-To“ dialogo lango techniką, VBA arba „Power Query“.
Taigi pradėkime!
1 metodas - užpildykite naudodami „Eiti į specialią + formulę“
Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite užpildyti A ir B stulpelių duomenis.
B stulpelyje siekiama užpildyti „spausdintuvą“ iki paskutinio tuščio langelio po juo, o tada, kai prasideda „skaitytuvas“, tada užpildykite „skaitytuvą“ žemiau esančiose ląstelėse, kol langeliai bus tušti.
Žemiau pateikiami veiksmai, kuriuos reikia atlikti, eikite į specialųjį, kad pasirinktumėte visus tuščius langelius ir užpildytumėte naudodami paprastą formulę:
- Pasirinkite duomenis, kuriuos norite užpildyti (mūsų pavyzdyje A1: D21)
- Eikite į skirtuką „Pagrindinis“
- Redagavimo grupėje spustelėkite piktogramą „Rasti ir pasirinkti“ (išskleidžiamajame meniu bus parodyta daugiau parinkčių)
- Spustelėkite parinktį „Eiti į“
- Atsidariusiame dialogo lange Eiti į spustelėkite specialų mygtuką. Bus atidarytas dialogo langas „Eiti į specialųjį“
- Dialogo lange Eiti į specialųjį elementą spustelėkite Blanks
- Spustelėkite Gerai
Pirmiau minėti veiksmai pasirinktų visas tuščias duomenų rinkinio ląsteles (kaip parodyta žemiau).
Tuščiuose pasirinktuose langeliuose pastebėsite, kad vienas langelis yra lengvesnis nei likęs pasirinkimas. Ši ląstelė yra aktyvi ląstelė, kurioje mes įvesime formulę.
Nesijaudinkite dėl to, kur yra ląstelė, nes mūsų metodas bet kokiu atveju veiktų.
Žemiau yra žingsniai, kaip užpildyti duomenis pasirinktuose tuščiuose langeliuose:
- Paspauskite klaviatūros klavišą (=). Tai į aktyvų langelį įterps lygybės ženklą
- Paspauskite rodyklės aukštyn klavišą. Tai įterps ląstelės nuorodą virš aktyvios ląstelės. Pvz., Mūsų atveju, B3 yra aktyvi ląstelė, o kai mes atliekame šiuos du veiksmus, ji ląstelėje įveda = B2
- Laikykite nuspaudę „Control“ klavišą ir paspauskite klavišą „Enter“
Pirmiau minėti veiksmai automatiškai įterptų visus tuščius langelius, kurių vertė yra virš jų.
Nors tai gali atrodyti per daug žingsnių, susipažinęs su šiuo metodu, galėsite greitai užpildyti duomenis „Excel“ per kelias sekundes.
Dabar, naudojant šį metodą, reikia pasirūpinti dar dviem dalykais.
Konvertuokite formules į reikšmes
Pirmasis - įsitikinkite, kad formules konvertuojate į vertes (kad turėtumėte statines vertes ir viskas nesutrumpėtų, jei ateityje keistumėte duomenis).
Žemiau yra vaizdo įrašas, kuriame bus parodyta, kaip greitai konvertuoti formules į „Excel“ vertes.
Pakeiskite datos formatą
Jei naudojate datas datose (kaip ir aš pavyzdiniuose duomenyse), pastebėsite, kad užpildytos datos stulpelio vertės yra skaičiai, o ne datos.
Jei išvestis datos stulpelyje yra norimo datos formato, jums viskas gerai ir nereikia nieko daryti.
Bet jei jie nėra tinkamo datos formato, turite šiek tiek pakoreguoti. Nors turite tinkamas reikšmes, jums tiesiog reikia pakeisti formatą, kad jis būtų rodomas langelyje kaip data.
Žemiau pateikiami žingsniai, kaip tai padaryti:
- Pasirinkite stulpelį, kuriame yra datos
- Spustelėkite skirtuką Pagrindinis
- Grupėje Skaičius spustelėkite išskleidžiamąjį meniu Formatas, tada pasirinkite datos formatą.
Jei kartais reikia atlikti šį užpildymą, rekomenduoju naudoti šią specialią „Go-To“ ir formulės techniką.
Nors jis turi keletą žingsnių, jis yra paprastas ir duoda rezultatą ten, kur yra jūsų duomenų rinkinys.
Bet jei tai turite padaryti gana dažnai, siūlau pažvelgti į toliau aprašytus VBA ir „Power Query“ metodus
2 metodas - užpildykite naudodami paprastą VBA kodą
Galite naudoti tikrai paprastą VBA makro kodą, kad greitai užpildytumėte „Excel“ langelius iki paskutinės tuščios vertės.
Jums tereikia vieną kartą pridėti failą prie VBA kodo ir tada galite lengvai pakartotinai naudoti kodą kelis kartus toje pačioje darbaknygėje ar net keliose darbaknygėse savo sistemoje.
Žemiau yra VBA kodas, kuris eis per kiekvieną pasirinktą langelį ir užpildys visas tuščias ląsteles:
„Šį VBA kodą sukūrė Sumitas Bansalis iš„ TrumpExcel.com “Sub FillDown () kiekvienai pasirinktai langai If cell =" "then cell.FillDown End If Next End Sub
Aukščiau pateiktas kodas naudoja ciklą „For“, kad pereitų per kiekvieną pasirinkimo langelį.
„For“ cikle naudojau sąlygą „If-then“, kuri patikrina, ar langelis tuščias, ar ne.
Jei langelis tuščias, jis užpildomas aukščiau esančio langelio reikšme, o jei jis nėra tuščias, for ciklas tiesiog ignoruoja langelį ir pereina prie kito.
Dabar, kai turite VBA kodą, leiskite parodyti, kur įdėti šį kodą „Excel“:
- Pasirinkite duomenis, kuriuos norite užpildyti
- Spustelėkite skirtuką Kūrėjas
- Spustelėkite „Visual Basic“ piktogramą (arba galite naudoti sparčiuosius klavišus ALT + F11). Tai atidarys „Visual Basic“ redaktorių „Excel“
- „Visual Basic“ redaktoriuje, kairėje, turėsite „Project Explorer“. Jei nematote, spustelėkite meniu parinktį „Peržiūrėti“, tada spustelėkite „Project Explorer“
- Jei atidarote kelias „Excel“ darbaknyges, „Project Explorer“ parodys visus darbaknygės pavadinimus. Raskite darbaknygės pavadinimą, kuriame turite duomenis.
- Dešiniuoju pelės mygtuku spustelėkite bet kurį darbaknygės objektą, eikite į Įterpti, tada spustelėkite Modulis. Tai įterps naują tos darbo knygos modulį
- Dukart spustelėkite „modulį“, kurį ką tik įterpėte atlikdami aukščiau nurodytą veiksmą. Bus atidarytas to modulio kodo langas
- Nukopijuokite ir įklijuokite VBA kodą į kodo langą
- Užveskite žymeklį bet kurioje kodo vietoje ir paleiskite makrokomandos kodą spustelėdami žalią mygtuką įrankių juostoje arba naudodami spartųjį klavišą F5
Pirmiau minėti veiksmai paleis VBA kodą ir jūsų duomenys bus užpildyti.
Jei ateityje norite vėl naudoti šį VBA kodą, turite išsaugoti šį failą kaip „Excel“ darbaknygę, kurioje įgalintos makrokomandos (su .XLSM plėtiniu)
Kitas dalykas, kurį galite padaryti, yra pridėti šią makrokomandą prie savo sparčiosios prieigos įrankių juostos, kuri visada matoma ir turėsite prieigą prie šios makrokomandos vienu paspaudimu (darbaknygėje, kurioje turite kodą).
Taigi, kai kitą kartą turėsite užpildyti duomenis, viskas, ką jums reikia padaryti, yra pasirinkti ir spustelėti makrokomandos mygtuką greitos prieigos įrankių juostoje.
Taip pat galite pridėti šią makrokomandą prie asmeninės makrokomandos darbaknygės ir naudoti ją bet kurioje savo sistemos darbaknygėje.
Spustelėkite čia, jei norite sužinoti daugiau apie asmenines makro darbo knygas ir kaip į jas pridėti kodą.
3 metodas - užpildykite naudodami „Power Query“
„Power Query“ turi integruotą funkciją, leidžiančią užpildyti duomenis vienu paspaudimu.
Rekomenduojama naudoti „Power Query“, kai vis tiek jį naudojate, kad pakeistumėte savo duomenis arba sujungtumėte duomenis iš kelių darbalapių ar kelių darbaknygių.
Kaip esamos darbo eigos dalį galite naudoti „Power Query“ užpildymo parinktį, kad greitai užpildytumėte tuščius langelius.
Jei norite naudoti „Power Query“, rekomenduojama, kad jūsų duomenys būtų „Excel“ lentelės formatu. Jei negalite konvertuoti duomenų į „Excel“ lentelę, turėsite sukurti pavadintą duomenų diapazoną ir tada naudoti tą pavadintą diapazoną „Power Query“.
Žemiau turiu duomenų rinkinį, kurį jau konvertavau į „Excel“ lentelę. Tai galite padaryti pasirinkę duomenų rinkinį, eidami į skirtuką Įterpti ir spustelėdami lentelės piktogramą.
Toliau pateikiami žingsniai, kaip naudoti „Power Query“ norint užpildyti duomenis iki kitos vertės:
- Pasirinkite bet kurį duomenų rinkinio langelį
- Spustelėkite skirtuką Duomenys
- Grupėje „Gauti ir transformuoti duomenis“ spustelėkite „Iš lapo“. Bus atidarytas „Power Query“ redaktorius. Atminkite, kad „Power Query“ tuščiuose langeliuose būtų rodoma „null“ reikšmė
- „Power Query“ redaktoriuje pasirinkite stulpelius, kuriuos norite užpildyti. Norėdami tai padaryti, laikykite nuspaudę „Control“ klavišą, tada spustelėkite norimų pasirinkti stulpelių antraštę. Mūsų pavyzdyje tai būtų stulpeliai Data ir Produktas.
- Dešiniuoju pelės mygtuku spustelėkite bet kurią pasirinktą antraštę
- Eikite į parinktį „Užpildyti“ ir spustelėkite „Žemyn“. Tai užpildytų visus tuščius langelius
- Spustelėkite skirtuką Failas, tada spustelėkite „Uždaryti ir įkelti“. Tai į darbalapį įterps naują darbalapį su gauta lentele
Nors šis metodas gali atrodyti šiek tiek perdėtas, vienas didelis „Power Query“ naudojimo pranašumas yra tas, kad jis leidžia greitai atnaujinti gautus duomenis, jei pasikeistų jūsų pradiniai duomenys.
Pavyzdžiui, jei prie pradinių duomenų pridedate daugiau įrašų, jums nereikia dar kartą atlikti visų aukščiau išvardytų veiksmų. Galite tiesiog dešiniuoju pelės klavišu spustelėti gautą lentelę, tada spustelėkite Atnaujinti.
Nors „Power Query“ metodas veikia gerai, geriausia jį naudoti, kai jau naudojate „Power Query“ savo darbo eigoje.
Jei tiesiog turite duomenų rinkinį, kuriame norite užpildyti tuščius langelius, būtų patogiau naudoti specialų metodą Eiti į arba VBA metodą (aptartą aukščiau).
Taigi tai yra trys paprasti būdai, kuriais galite pasinaudoti užpildykite tuščius langelius iki kitos „Excel“ vertės.
Tikiuosi, kad ši pamoka jums buvo naudinga.
