Vizionați video - Completați în Excel
De multe ori, puteți întâlni un set de date în care o singură celulă este umplută cu date și celulele de sub acesta sunt goale până la următoarea valoare.
Ceva așa cum se arată mai jos:
În timp ce acest format funcționează pentru unii oameni, problema cu acest tip de date este că nu o puteți utiliza pentru a crea tabele pivot sau pentru a le utiliza în calcule.
Și acest lucru are o soluție ușoară!
În acest tutorial, vă voi arăta cum să faceți rapid completați celulele din Excel până la următoarea valoare completată.
Puteți face acest lucru cu ușurință folosind o tehnică simplă de dialog special, VBA sau Power Query.
Asadar, haideti sa începem!
Metoda 1 - Completați folosind Go to Special + Formula
Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să completați datele din coloana A și coloana B.
În coloana B, scopul este să umpleți „Imprimantă” până la ultima celulă goală de sub ea, iar atunci când începe „Scanner”, apoi completați „Scanner” în celulele de mai jos până când celulele sunt goale.
Sub pașii de utilizat, mergeți la special pentru a selecta toate celulele goale și completați cu o formulă simplă:
- Selectați datele pe care doriți să le completați (A1: D21 în exemplul nostru)
- Accesați fila „Acasă”
- În grupul Editare, faceți clic pe pictograma „Găsiți și selectați” (aceasta va afișa câteva opțiuni în meniul derulant)
- Faceți clic pe opțiunea „Mergeți la”
- În caseta de dialog Go-To care se deschide, faceți clic pe butonul Special. Aceasta va deschide caseta de dialog „Mergi la special”
- În caseta de dialog Mergi la special, faceți clic pe Blanks
- Faceți clic pe OK
Pașii de mai sus ar selecta toate celulele goale din setul de date (așa cum se arată mai jos).
În celulele goale care sunt selectate, ați observa că o celulă este mai ușoară decât restul selecției. Această celulă este celula activă în care vom introduce o formulă.
Nu vă faceți griji cu privire la locul unde se află celula în selecție, deoarece metoda noastră ar funcționa în orice caz.
Acum, mai jos sunt pașii pentru completarea datelor în celulele goale selectate:
- Apăsați tasta egală cu (=) de pe tastatură. Aceasta va insera un semn egal cu celulă activă
- Apăsați tasta săgeată sus. Aceasta va introduce referința celulei celulei deasupra celulei active. De exemplu, în cazul nostru, B3 este celula activă și atunci când facem acești doi pași, intră = B2 în celulă
- Țineți apăsată tasta Control și apăsați tasta Enter
Pașii de mai sus ar insera automat toate celulele goale cu valoarea de deasupra lor.
Deși acest lucru poate arăta ca prea mulți pași, odată ce veți obține această metodă, veți putea completa rapid datele în Excel în câteva secunde.
Acum, mai sunt două lucruri de care trebuie să aveți grijă atunci când utilizați această metodă.
Convertiți formulele în valori
Primul este să vă asigurați că convertiți formule în valori (astfel încât să aveți valori statice și lucrurile să nu se încurce în cazul în care schimbați date în viitor).
Mai jos este un videoclip care vă va arăta cum să convertiți rapid formule în valori în Excel.
Schimbați formatul datei
În cazul în care utilizați date în datele dvs. (așa cum sunt și eu în datele de exemplu), ați observa că valorile completate din coloana de date sunt numere și nu date.
Dacă rezultatele din coloana de date sunt în formatul de dată dorit, sunteți bun și nu trebuie să faceți altceva.
Dar dacă acestea nu sunt în formatul corect de dată, trebuie să faceți o ajustare minoră. Deși aveți valorile corecte, trebuie doar să schimbați formatul, astfel încât să fie afișat ca dată în celulă.
Mai jos sunt pașii pentru a face acest lucru:
- Selectați coloana care are datele
- Faceți clic pe fila Acasă
- În grupul Număr, faceți clic pe meniul derulant Format, apoi selectați formatul datei.
Dacă trebuie să faceți această completare din când în când, vă recomand să utilizați această tehnică specială și formulă Go-To.
Deși are câțiva pași, este simplu și vă oferă rezultatul chiar în setul de date.
Dar, în cazul în care trebuie să faceți acest lucru destul de des, vă sugerez să vă uitați la metodele VBA și Power Query care vor fi prezentate în continuare
Metoda 2 - Completați folosind un cod VBA simplu
Puteți utiliza un cod macro VBA foarte simplu pentru a completa rapid celulele din Excel până la ultima valoare goală.
Trebuie să adăugați codul VBA la fișier o singură dată și apoi puteți reutiliza cu ușurință codul de mai multe ori în același registru de lucru sau chiar mai multe registre de lucru de pe sistemul dvs.
Mai jos este codul VBA care va trece prin fiecare celulă din selecție și va completa toate celulele care sunt goale:
'Acest cod VBA este creat de Sumit Bansal din TrumpExcel.com Sub FillDown () Pentru fiecare celulă din selecție If cell = "" Then cell.FillDown End If Next End Sub
Codul de mai sus folosește o buclă For pentru a parcurge fiecare celulă din selecție.
În cadrul buclei For, am folosit o condiție If-Then care verifică dacă celula este goală sau nu.
În cazul în care celula este goală, aceasta este umplută cu valoarea din celula de mai sus și, în cazul în care nu este goală, atunci bucla for pur și simplu ignoră celula și se mută la următoarea.
Acum că aveți codul VBA, permiteți-mi să vă arăt unde să pun acest cod în Excel:
- Selectați datele pe care doriți să le completați
- Faceți clic pe fila Dezvoltator
- Faceți clic pe pictograma Visual Basic (sau puteți utiliza comanda rapidă de la tastatură ALT + F11). Aceasta va deschide Editorul Visual Basic în Excel
- În Editorul Visual Basic, din stânga, veți avea Project Explorer. Dacă nu o vedeți, faceți clic pe opțiunea „Vizualizare” din meniu și apoi faceți clic pe Project Explorer
- În cazul în care aveți mai multe registre de lucru Excel deschise, Project Explorer vă va arăta toate numele registrului de lucru. Găsiți numele registrului de lucru unde aveți datele.
- Faceți clic dreapta pe oricare dintre obiectele din registrul de lucru, accesați Insert, apoi faceți clic pe Module. Aceasta va insera un nou modul pentru registrul de lucru respectiv
- Faceți dublu clic pe „Modulul” pe care tocmai l-ați introdus în pasul de mai sus. Se va deschide fereastra de cod pentru modulul respectiv
- Copiați-lipiți codul VBA în fereastra de cod
- Plasați cursorul oriunde în cod și apoi rulați codul macro făcând clic pe butonul verde din bara de instrumente sau utilizând comanda rapidă de la tastatură F5
Pașii de mai sus vor rula codul VBA și datele dvs. ar fi completate.
Dacă doriți să utilizați din nou acest cod VBA în viitor, trebuie să salvați acest fișier ca un registru de lucru Excel activat pentru macro (cu o extensie .XLSM)
Un alt lucru pe care îl puteți face este să adăugați această macrocomandă la bara de instrumente de acces rapid, care este întotdeauna vizibilă și veți avea acces la această macrocomandă cu un singur clic (în registrul de lucru unde aveți codul în backend).
Deci, data viitoare când aveți date în care trebuie să completați, tot ce trebuie să faceți este să faceți selecția și apoi să faceți clic pe butonul macro din bara de instrumente de acces rapid.
De asemenea, puteți adăuga această macrocomandă în registrul de lucru pentru macrocomenzi personale și apoi să o utilizați în orice registru de lucru din sistemul dvs.
Faceți clic aici pentru a citi mai multe despre registrele de lucru macro personale și despre cum să le adăugați cod.
Metoda 3 - Completați utilizând Power Query
Power Query are o funcționalitate încorporată care vă permite să completați cu un singur clic.
Utilizarea Power Query este recomandată atunci când o utilizați oricum pentru a vă transforma datele sau pentru a combina datele din mai multe foi de lucru sau mai multe registre de lucru.
Ca parte a fluxului de lucru existent, puteți utiliza opțiunea de completare din Power Query pentru a umple rapid celulele goale.
Pentru a utiliza Power Query, se recomandă ca datele dvs. să fie într-un format Excel Table. Dacă nu vă puteți converti datele într-un tabel Excel, va trebui să creați un interval denumit pentru date și apoi să utilizați acest interval denumit în Power Query.
Mai jos am setul de date pe care l-am convertit deja într-un tabel Excel. Puteți face acest lucru selectând setul de date, accesând fila Insert și apoi făcând clic pe pictograma Table.
Sub pașii pentru a utiliza Power Query pentru a completa datele până la următoarea valoare:
- Selectați orice celulă din setul de date
- Faceți clic pe fila Date
- În grupul Obțineți și transformați date, faceți clic pe „Din foaie”. Aceasta va deschide editorul Power Query. Rețineți că celulele goale ar afișa valoarea „nul” în Power Query
- În editorul Power Query, selectați coloanele pentru care doriți să completați. Pentru aceasta, țineți apăsată tasta Control și apoi faceți clic pe antetul coloanelor pe care doriți să le selectați. În exemplul nostru, ar fi data și coloanele Produs.
- Faceți clic dreapta pe oricare dintre antetele selectate
- Mergeți la opțiunea „Completare” și apoi faceți clic pe „Jos”. Acest lucru ar completa datele din toate celulele goale
- Faceți clic pe fila Fișier, apoi faceți clic pe „Închidere și încărcare”. Aceasta va insera o nouă foaie de lucru în registrul de lucru cu tabelul rezultat
Deși această metodă poate părea un pic excesivă, un mare avantaj al utilizării Power Query este că vă permite să actualizați rapid datele rezultate în cazul în care datele dvs. originale se modifică.
De exemplu, dacă adăugați mai multe înregistrări la datele originale, nu este necesar să faceți din nou toate cele de mai sus. Puteți face clic dreapta pe tabelul rezultat și apoi faceți clic pe Reîmprospătare.
Deși metoda Power Query funcționează bine, cel mai bine este să o utilizați atunci când utilizați deja Power Query în fluxul de lucru.
Dacă aveți doar un set de date în care doriți să completați celulele goale, atunci ar fi mai convenabil să folosiți metoda specială Mergi la sau metoda VBA (acoperită mai sus).
Deci, acestea sunt trei moduri simple pe care le puteți folosi completați celulele goale până la următoarea valoare în Excel.
Sper că ți s-a părut util acest tutorial.
