Kako automatski podesiti kombinirani okvir ili listu na osnovu dinamičkih raspona podataka u Excelu

Podijeli sada:

Tamo gdje podaci preuzeti iz baze podataka prelaze raspon kombiniranog okvira, nove stavke se jednostavno ne prikazuju. Da bi se ovo suprotstavilo, raspon koji se nalazi u okviru liste ili kombiniranih okvira mora se proširiti ili skupiti kako bi odgovarao podacima. Ovaj članak ispituje kako to učiniti automatski. 

Pretpostavlja se da čitač ima prikazanu Developer traku i da je upoznat sa VBA Editorom. Ako ne, molimo Google "Excel Developer Tab" ili "Excel Code Window".

Profesionalni način prikazivanja kombinovanih okvira je da se njihovi rasponi prošire ili skupe po potrebi. Na primjer:Profesionalni način prikazivanja kombiniranih kutija

Ključ za dinamički raspon je praćenje broja popunjenih redova u relevantnoj koloni, koristeći funkciju =countA. Ova funkcija broji naseljene elemente u nizu ćelija sve dok ne pogodi posljednju; u slučaju prvog dijagrama na gornjoj slici, to bi bio red 11.

Za automatsko održavanje raspona potrebna su definirana imena za praćenje broja popunjenih redova. Na primjer, koristimo eCol (krajnji stupac) i eRow (krajnji red) da definiramo granice našeg raspona. Što više redova popunimo, to je veća vrijednost eRow-a.Koristite eCol i eRow za definiranje granica dometa

Gore definirana imena su postavila listu na širinu jednog stupca (eCol = 1) prema broju popunjenih redova u eCol-u (eRow = 11)

Konačno, naslovi se pojavljuju u rasponu “A2:A” & eRow. Primijetit ćete da se funkcija indeksa koristi za uspostavljanje posljednje ćelije u rasponu pod nazivom “Naslovi”. U stvari, opseg „A2:A“ & eRow se u ovoj fazi prevodi u „A2:A11“.Funkcija indeksa

Možemo automatski podesiti dinamički opseg kada se radna sveska otvori, koristeći potproceduru Auto_open, koja se pokreće pre nego što radna sveska postane vidljiva.

Kodeks

Otvorite radnu svesku i popunite je kombinovanim okvirom i nekim podacima. Primjer radne sveske korištene u ovoj vježbi možete pronaći OVDJE.

Otvorite prozor VBA koda i umetnite modul. Kopirajte kod ispod u modul.

Događaj Auto_Open postavlja vrijednosti posljednjeg reda i posljednje kolone za dinamički raspon “Naslovi” i bilježi naknadne promjene.

Sub auto_open()
     Dim eRow As Integer, eCol As Integer, i As Long
 
     On Error Resume Next
 
     'Clear the present define names, to avoid any duplications
     activeworkbook.Names("eCol").Delete
     activeworkbook.Names("eRow").Delete
     activeworkbook.Names("Titles").Delete
     Range("A1").Select
 
    'Titles will appear in the first column, A in this case
    eCol = 1
 
    'Find the last populated row
    eRow = Sheets("Main").Cells(Rows.Count, eCol).End(xlUp).Row
 
    'Define the names
     activeworkbook.Names.Add Name:="eCol", RefersTo:="=COUNTA($1:$1)"
     activeworkbook.Names.Add Name:="eRow", RefersToR1C1:="=COUNTA(C" & ColNo & ")"
     activeworkbook.Names.Add Name:="Titles", RefersTo:="=A2:INDEX($2:$200," & "eRow," & "eCol)"
End Sub

Sub DropDown1_Change()
     MsgBox "Directed by " & Cells(2, 5)
End Sub

Napomena: Sve je smješteno na jednu stranicu radi pojednostavljenja pregleda. Obično bi vrijednosti u kolonama A i B, te D i E bile na drugom, moguće skrivenom, listu. Također imajte na umu da se druga kolona, ​​B, ne koristi u definicijama raspona u ovoj konkretnoj vježbi; vrijednosti kolone B se dobijaju na uobičajeni način putem svojstva ćelijske veze kombiniranog okvira (D2 odražava odabir trećeg elementa u rasponu, koji počinje od A2, zajedno s funkcijom Index u "E3" za pronalaženje direktora (=INDEX(B:B,D2+1,1)).Combo Box će u početku biti popunjen Auto_open

Sačuvajte radnu svesku, a zatim je ponovo otvorite. Kombinirani okvir će u početku biti popunjen Auto_open. Dodajte stavke u kolone A i B i promatrajte promjene u kombinovanom okviru.

Spasite oštećene Excel datoteke

S vremena na vrijeme, Excel datoteke se mogu oštetiti nakon što se Excel neočekivano sruši. Ako imate sigurnosnu kopiju, tada možete jednostavno vratiti podatke pomoću sigurnosne kopije. U suprotnom, možda ćete morati potražiti stručnog stručnjaka ili alat za oporavak korumpirani Excel datoteke.

Uvod za autora:

Felix Hooker je stručnjak za oporavak podataka DataNumen, Inc., koji je svjetski lider u tehnologijama za oporavak podataka, uključujući popravak rar-a i sql softverski proizvodi za oporavak. Za više informacija posjetite www.datanumen.com

Podijeli sada:

Komentari su zatvoreni.