Cum să ajustați automat caseta sau lista combinată pe baza intervalelor de date dinamice în Excel

Distribuie acum:

Acolo unde datele descărcate dintr-o bază de date depășesc intervalul unei casete combinate, elementele noi pur și simplu nu sunt afișate. Pentru a contracara acest lucru, intervalul care stă la baza casetelor Listă sau Combo trebuie să se extindă sau să se contracte pentru a se potrivi cu datele. Acest articol examinează cum să faceți acest lucru automat. 

Se presupune că cititorul are afișată panglica pentru dezvoltatori și este familiarizat cu Editorul VBA. Dacă nu, vă rugăm să Google „Fila Dezvoltator Excel” sau „Fereastra Cod Excel”.

O modalitate profesională de afișare a casetelor combinate este de a avea gamele să se extindă sau să se contracte după cum este necesar. De exemplu:Un mod profesional de afișare a casetelor combinate

Cheia pentru un interval dinamic este monitorizarea numărului de rânduri populate din coloana relevantă, folosind funcția =countA. Această funcție numără elementele populate dintr-o secvență de celule până când atinge ultima; în cazul primei diagrame din imaginea de mai sus, acesta ar fi rândul 11.

Pentru a menține un interval automat este nevoie de nume definite pentru a urmări numărul de rânduri populate. De exemplu, folosim eCol (coloana finală) și eRow (rândul final) pentru a defini limitele intervalului nostru. Cu cât populăm mai multe rânduri, cu atât valoarea eRow devine mai mare.Utilizați eCol și eRow pentru a defini limitele intervalului

Numele definite de mai sus au stabilit lista la o coloană lățime (eCol = 1) după numărul de rânduri populate în eCol (eRow = 11)

În cele din urmă, titlurile apar în intervalul „A2:A” și eRow. Veți observa că funcția Index este utilizată pentru a stabili ultima celulă din intervalul numit „Titluri”. În mod efectiv, intervalul „A2:A” și eRow se traduce în „A2:A11” în această etapă.Funcția de index

Putem seta intervalul dinamic în mod automat când se deschide registrul de lucru, utilizând subprocedura Auto_open, care rulează înainte ca registrul de lucru să devină vizibil.

Codul

Deschideți un registru de lucru și completați-l cu o casetă combinată și câteva date. Exemplul de caiet de lucru folosit în acest exercițiu poate fi găsit aici.

Deschideți fereastra de cod VBA și introduceți un modul. Copiați codul de mai jos în modul.

Evenimentul Auto_Open setează valorile ultimului rând și ultimei coloane pentru intervalul dinamic „Titluri” și notează modificările ulterioare efectuate.

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

Notă: Totul a fost plasat pe o singură pagină pentru a simplifica vizualizarea. De obicei, valorile din coloanele A și B și D și E s-ar afla pe o altă foaie, eventual ascunsă. De asemenea, rețineți că a doua coloană, B, nu este utilizată în definițiile intervalului din acest exercițiu particular; valorile coloanei B se obțin în mod obișnuit prin intermediul proprietății de legătură de celule a casetei combinate (D2 reflectă selecția celui de-al treilea element din interval, care începe de la A2, cuplată cu o funcție Index în „E3” pentru a găsi directorul ( =INDEX(B:B;D2+1;1)).Caseta combinată va fi inițial completată cu Auto_open

Salvați registrul de lucru, apoi redeschideți-l. Caseta combinată va fi inițial completată de Auto_open. Adăugați elemente în coloana A și B și observați modificările din caseta combinată.

Salvați fișierele Excel deteriorate

Din când în când, fișierele Excel pot fi corupte după ce Excel se blochează în mod neașteptat. Dacă aveți o copie de rezervă, atunci puteți pur și simplu să restaurați datele cu copia de rezervă. În caz contrar, poate fi necesar să căutați un expert sau un instrument profesionist pentru a recupera Excel corupt fișiere.

Introducerea autorului:

Felix Hooker este un expert în recuperarea datelor DataNumen, Inc., care este lider mondial în tehnologiile de recuperare a datelor, inclusiv reparare rar și produse software de recuperare sql. Pentru mai multe informații vizitați www.datanumen.com

Distribuie acum:

Comentariile sunt închise.