Creați o listă drop-down Excel cu sugestii de căutare

Cu toții folosim Google ca parte a rutinei noastre zilnice. Una dintre caracteristicile sale este sugestia de căutare, în care Google acționează inteligent și ne oferă o listă de sugestii în timp ce tastăm.

În acest tutorial, veți afla cum să creați o listă derulantă în căutare în Excel - adică o listă derulantă care va afișa elementele potrivite pe măsură ce tastați.

Mai jos este un videoclip al acestui tutorial (în cazul în care preferați să vizionați un videoclip în loc să citiți textul).

Listă drop-down căutabilă în Excel

În scopul acestui tutorial, folosesc datele primelor 20 de țări în funcție de PIB.

Intenția este de a crea o listă verticală excel cu un mecanism de sugestie de căutare, astfel încât să afișeze o listă derulantă cu opțiunile de potrivire pe măsură ce scriu în bara de căutare.

Ceva așa cum se arată mai jos:

Pentru a continua, descărcați exemplul de fișier de aici

Crearea listei drop-down căutabile în Excel ar fi un proces în trei părți:

1. Configurarea casetei de căutare.
2. Setarea datelor.
3. Scrierea unui scurt cod VBA pentru a-l face să funcționeze.

Pasul 1 - Configurarea casetei de căutare

În acest prim pas, voi folosi o casetă combinată și o voi configura astfel încât, atunci când o tastați, textul să fie reflectat și într-o celulă în timp real.

Iată pașii pentru a face acest lucru:

1. Accesați fila Dezvoltator -> Inserare -> Comenzi ActiveX -> Casetă combinată (Control ActiveX).
   • Este posibil să nu găsiți fila dezvoltator în panglică. În mod implicit, este ascuns și trebuie activat. Faceți clic aici pentru a afla cum să obțineți fila dezvoltator în panglica din Excel.
2. Mutați cursorul în zona foii de lucru și faceți clic oriunde. Se va introduce o casetă combinată.
3. Faceți clic dreapta pe caseta combinată și selectați Proprietăți.
4. În caseta de dialog proprietăți, efectuați următoarele modificări:
   • AutoWordSelect: Fals
   • LinkedCell: B3
   • ListFillRange: DropDownList (vom crea un interval numit cu acest nume la pasul 2)
   • MatchEntry: 2 - fmMatchEntryNone

(Celula B3 este legată de caseta combinată, ceea ce înseamnă că tot ce introduceți în caseta combinată este introdus în B3)

4. Accesați fila Dezvoltator și faceți clic pe Mod de proiectare. Acest lucru vă va permite să introduceți text în caseta combinată. De asemenea, deoarece celula B3 este legată de caseta combinată, orice text pe care îl introduceți în caseta combinată ar fi reflectat și în B3 în timp real.

Pasul 2 - Setarea datelor

Acum că este complet setată caseta de căutare, trebuie să punem datele la locul lor. Ideea este că, de îndată ce tastați ceva în caseta de căutare, acesta afișează numai acele elemente care au textul respectiv.

Pentru a face acest lucru, vom folosi

• Trei coloane de ajutor.
• Un interval dinamic numit.

Coloana de ajutor 1

Puneți următoarea formulă în celula F3 și trageți-o pentru întreaga coloană (F3: F22)

= - ISNUMBER (IFERROR (CAUTARE ($ B $ 3, E3,1), ""))

Această formulă returnează 1 când textul din caseta combinată este acolo în numele țării din stânga. De exemplu, dacă tastați UNI, atunci numai valorile pentru United state și UniRegatul este 1 și toate valorile rămase sunt 0.

Coloana de ajutor 2

Puneți următoarea formulă în celula G3 și trageți-o pentru întreaga coloană (G3: G22)

= IF (F3 = 1, COUNTIF ($ F $ 3: F3,1), "") 

Această formulă returnează 1 pentru prima apariție în care textul casetei combinate se potrivește cu numele țării, 2 pentru a doua apariție, 3 pentru a treia și așa mai departe. De exemplu, dacă tastați UNI, celula G3 va afișa 1 așa cum se potrivește cu Statele Unite, iar G9 va afișa 2 așa cum se potrivește cu Regatul Unit. Restul celulelor vor fi necompletate.

Coloana de ajutor 3

Puneți următoarea formulă în celula H3 și trageți-o pentru întreaga coloană (H3: H22)

= IFERROR (INDEX ($ E $ 3: $ E $ 22, MATCH (ROWS ($ G $ 3: G3), $ G $ 3: $ G $ 22,0)), "") 

Această formulă stivuiește toate numele potrivite împreună, fără celule goale între ele. De exemplu, dacă tastați UNI, această coloană va afișa 2 și 9 împreună, iar restul celulei ar fi necompletat.

Crearea gamei dinamice numite

Acum că coloanele de asistență sunt la locul lor, trebuie să creăm intervalul denumit dinamic. Acest interval denumit se va referi numai la acele valori care se potrivesc cu textul introdus în caseta combinată. Vom folosi acest interval dinamic denumit pentru a afișa valorile în caseta derulantă.

Notă: La pasul 1 am introdus DropDownList în opțiunea ListFillRange. Acum vom crea gama numită cu același nume.

Iată pașii pentru a-l crea:

1. Accesați Formule -> Manager de nume.
2. În caseta de dialog manager de nume, faceți clic pe Nou. Se va deschide o casetă de dialog Nume nou.
3. În câmpul Nume introduceți DropDownList
4. În câmpul Se referă la câmp introduceți formula: = $ H $ 3: INDEX ($ H $ 3: $ H $ 22, MAX ($ G $ 3: $ G $ 22), 1)

Pasul 3 - Punerea în funcțiune a codului VBA

Aproape am ajuns.

Ultima parte este să scrieți un cod VBA scurt. Acest cod face ca meniul derulant să fie dinamic astfel încât să afișeze articolele / numele potrivite pe măsură ce introduceți text în caseta de căutare.

Pentru a adăuga acest cod în registrul de lucru:

1. Faceți clic dreapta pe fila Foaie de lucru și selectați Vizualizare cod.
2. În fereastra VBA, copiați și lipiți următorul cod:

   Private Sub ComboBox1_Change () ComboBox1.ListFillRange = "DropDownList" Me.ComboBox1.DropDown Sfârșitul Sub

Asta e!!

Sunteți pregătiți cu bara dvs. de căutare de tip Google care afișează elementele potrivite pe măsură ce introduceți în ea.

Pentru un aspect mai bun, puteți acoperi celula B3 cu Combo Box și ascunde toate coloanele de ajutor. Acum puteți să vă arătați puțin cu acest uimitor truc Excel.

Pentru a continua, descărcați fișierul de aici

Tu ce crezi? Ați putea folosi această listă derulantă de sugestii de căutare în munca dvs.? Spune-mi gândurile tale lăsând un comentariu.

Dacă v-a plăcut acest tutorial, sunt sigur că doriți și următoarele tutoriale Excel:

• Filtru dinamic - Extrageți datele potrivite în timp ce tastați.
• Extrageți datele pe baza unei selecții din lista derulantă.
• Crearea listelor drop-down dependente în Excel.
• Ghidul final pentru utilizarea funcției Excel VLOOKUP.
• Cum se fac mai multe selecții într-o listă derulantă în Excel.
• Cum se introduce și se folosește o casetă de selectare în Excel.