Направите Екцел падајућу листу са предлозима за претрагу

Сви користимо Гоогле као део своје дневне рутине. Једна од његових карактеристика је предлог за претрагу, где се Гоогле понаша паметно и даје нам листу предлога док куцамо.

У овом водичу ћете научити како да креирате падајућу листу која се може претраживати у Екцелу-тј. Падајућу листу која ће приказати одговарајуће ставке док куцате.

Испод је видео овог водича (у случају да више волите да гледате видео уместо читања текста).

Падајућа листа у Екцелу која се може претраживати

За потребе овог водича користим податке 20 најбољих земаља према БДП -у.

Намера је да се направи Екцел падајућа листа са механизмом за предлоге претраживања, тако да приказује падајући мени са одговарајућим опцијама док уносим у траку за претрагу.

Нешто као што је приказано испод:

Да бисте наставили, преузмите датотеку примера одавде

Креирање падајуће листе која се може претраживати у Екцелу био би процес из три дела:

1. Конфигурисање оквира за претрагу.
2. Подешавање података.
3. Писање кратког ВБА кода да би функционисало.

Корак 1 - Конфигурисање оквира за претрагу

У овом првом кораку користићу комбиновани оквир и конфигурисати га тако да се приликом уноса текст такође одражава у ћелији у реалном времену.

Ево корака за то:

1. Идите на картицу Девелопер -> Инсерт -> АцтивеКс Цонтролс -> Цомбо Бок (АцтивеКс Цонтрол).
   • Постоји могућност да картицу за програмере не пронађете на траци. Подразумевано је скривено и потребно га је омогућити. Кликните овде да бисте сазнали како да добијете картицу програмера на траци у програму Екцел.
2. Померите курсор на област радног листа и кликните било где. Убациће комбиновани оквир.
3. Десним тастером миша кликните Цомбо Бок и изаберите Пропертиес.
4. У дијалогу за својства унесите следеће промене:
   • АутоВордСелецт: Нетачно
   • ЛинкедЦелл: Б3
   • ЛистФиллРанге: ДропДовнЛист (креираћемо именовани опсег са овим именом у кораку 2)
   • МатцхЕнтри: 2 - фмМатцхЕнтриНоне

(Ћелија Б3 је повезана са Цомбо Бок -ом, што значи да је све што унесете у Цомбо Бок унето у Б3)

4. Идите на картицу Девелопер и кликните на Десигн Моде. Ово ће вам омогућити да унесете текст у Цомбо Бок. Такође, пошто је ћелија Б3 повезана са комбинованим оквиром, сваки текст који унесете у комбиновани оквир такође ће се одразити на Б3 у реалном времену.

Корак 2 - Подешавање података

Сада када је поље за претрагу све постављено, морамо поставити податке на своје место. Идеја је да чим унесете било шта у оквир за претрагу, приказује само оне ставке које у себи имају тај текст.

Да бисмо то урадили, користићемо

• Три помоћне колоне.
• Један динамички именовани опсег.

Колона помоћника 1

Ставите следећу формулу у ћелију Ф3 и превуците је за целу колону (Ф3: Ф22)

=-ИСНУМБЕР (ИФЕРРОР (СЕАРЦХ ($ Б $ 3, Е3,1), ""))

Ова формула враћа 1 када се текст у Цомбо Бок налази у називу земље са леве стране. На пример, ако откуцате УНИ, само вредности за Унитед Стате анд Унитед Кингдом су 1, а све преостале вредности су 0.

Колона помоћника 2

Ставите следећу формулу у ћелију Г3 и превуците је за целу колону (Г3: Г22)

= ИФ (Ф3 = 1, ЦОУНТИФ ($ Ф $ 3: Ф3,1), "") 

Ова формула враћа 1 за прву појаву где се Цомбо Бок текст подудара са именом земље, 2 за другу појаву, 3 за трећу итд. На пример, ако унесете УНИ, Г3 ћелија ће приказати 1 као што одговара Сједињеним Државама, а Г9 ће приказати 2 као што одговара Уједињеном Краљевству. Остатак ћелија ће бити празан.

Колона помоћника 3

Ставите следећу формулу у ћелију Х3 и превуците је за целу колону (Х3: Х22)

= ИФЕРРОР (ИНДЕКС ($ Е $ 3: $ Е $ 22, МАТЦХ (РОВС ($ Г $ 3: Г3), $ Г $ 3: $ Г $ 22,0)), "") 

Ова формула слаже сва одговарајућа имена заједно без празних ћелија између њих. На пример, ако откуцате УНИ, ова колона ће приказати 2 и 9 заједно, а остатак ћелије ће бити празан.

Креирање распона динамичких назива

Сада када су помоћне колоне постављене, морамо креирати динамички именовани опсег. Овај именовани опсег односиће се само на оне вредности које одговарају тексту унетом у комбиновани оквир. Овај динамички именовани опсег користићемо за приказивање вредности у падајућем оквиру.

Белешка: У првом кораку смо унели ДропДовнЛист у опцију ЛистФиллРанге. Сада ћемо креирати именовани опсег са истим именом.

Ево корака за његово креирање:

1. Идите на Формуле -> Управитељ имена.
2. У дијалогу управитеља имена кликните на Ново. Отвориће се дијалошки оквир Ново име.
3. У поље за име унесите ДропДовнЛист
4. У поље Реферс то Фиелд унесите формулу: = $ Х $ 3: ИНДЕКС ($ Х $ 3: $ Х $ 22, МАКС ($ Г $ 3: $ Г $ 22), 1)

Корак 3 - Увођење ВБА кода у функцију

Ускоро смо тамо.

Последњи део је писање кратког ВБА кода. Овај код чини падајући мени динамичким тако да приказује одговарајуће ставке/имена док куцате у пољу за претрагу.

Да бисте додали овај код у радну свеску:

1. Десним тастером миша кликните картицу Радни лист и изаберите Прикажи код.
2. У прозору ВБА копирајте и залепите следећи код:

   Привате Суб ЦомбоБок1_Цханге () ЦомбоБок1.ЛистФиллРанге = "ДропДовнЛист" Ме.ЦомбоБок1.ДропДовн Енд Суб

То је то!!

Све вам је постављено са сопственом траком за претрагу типа Гоогле која приказује ставке које се подударају док је уносите.

За бољи изглед и осећај, можете да покријете ћелију Б3 помоћу Цомбо Бок -а и сакријете све помоћне колоне. Сада се можете мало похвалити овим невероватним Екцел триком.

Да бисте наставили, преузмите датотеку одавде

Шта мислиш? Да ли бисте могли да користите овај падајући списак предлога за претрагу у свом раду? Реците ми своја размишљања остављајући коментар.

Ако сте уживали у овом водичу, сигуран сам да бисте волели и следеће Екцел водиче:

• Динамички филтер - Извлачите одговарајуће податке док куцате.
• Издвајање података на основу одабира падајуће листе.
• Креирање зависних падајућих листа у Екцелу.
• Врхунски водич за коришћење Екцел ВЛООКУП функције.
• Како направити више избора на падајућој листи у програму Екцел.
• Како уметнути и користити поље за потврду у програму Екцел.