Екцел филтер једна је од најчешће коришћених функција када радите са подацима. У овом посту на блогу ћу вам показати како да креирате оквир за претрагу динамичког Екцел филтера тако да филтрира податке на основу онога што унесете у оквир за претрагу.
Нешто као што је приказано испод:
Ово има двоструку функцију - можете да изаберете назив земље са падајуће листе или можете ручно да унесете податке у оквир за претрагу и приказаће вам све одговарајуће записе. На пример, када откуцате „И“, добићете све називе земаља са абецедом И.
Гледајте видео - Креирање оквира за претрагу динамичког Екцел филтера
Креирање оквира за претраживање динамичког Екцел филтера
Овај динамички Екцел филтер може се направити у 3 корака:
- Добијање јединствене листе артикала (у овом случају земље). Ово би се користило за креирање падајућег менија.
- Креирање оквира за претрагу. Овде сам користио Цомбо Бок (АцтивеКс Цонтрол).
- Подешавање података. Овде бих користио три помоћне колоне са формулама за издвајање одговарајућих података.
Ево како изгледају сирови подаци:
КОРИСНИ САВЕТ: Скоро увек је добра идеја претворити податке у Екцел табелу. То можете учинити одабиром било које ћелије у скупу података и помоћу пречице на тастатури Цонтрол + Т.
Корак 1 - Добијање јединствене листе ставки
- Одаберите све земље и залепите их у нови радни лист.
- Изаберите листу земаља -> Иди на Подаци -> Уклони дупликате.
- У дијалогу Уклањање дупликата изаберите колону у којој имате листу и кликните на дугме У реду. Ово ће уклонити дупликате и дати вам јединствену листу као што је приказано испод:
- Још један додатни корак је креирање именованог опсега за ову јединствену листу. Да уради ово:
- Идите на картицу Формула -> Дефинишите назив
- У дијалогу за дефинисање имена:
- Назив: ЦоунтриЛист
- Опсег: Радна свеска
- Односи се на: = УникуеЛист! $ А $ 2: $ А $ 9 (имам листу на посебној картици под именом УникуеЛист у А2: А9. Можете се позвати свуда где се налази ваша јединствена листа)
НАПОМЕНА: Ако користите методу „Уклони дупликате“ и проширите податке да бисте додали још записа и нових земаља, мораћете поново да поновите овај корак. Алтернативно, можете такође дати формулу како бисте овај процес учинили динамичним.
Корак 2 - Креирање оквира за претрагу динамичког Екцел филтера
Да би ова техника функционисала, морали бисмо да направимо „оквир за претрагу“ и повежемо га са ћелијом.
Можемо да користимо Цомбо Бок у Екцелу за креирање овог филтера оквира за претрагу. На овај начин, кад год унесете било шта у Цомбо Бок, то ће се такође одразити у ћелији у реалном времену (као што је приказано испод).
Ево корака за то:
- Идите на картицу Девелопер -> Цонтролс -> Инсерт -> АцтивеКс Цонтролс -> Цомбо Бок (АцтивеКс Цонтролс).
- Ако картица Девелопер није видљива, ево корака како бисте је омогућили.
- Ако картица Девелопер није видљива, ево корака како бисте је омогућили.
- Кликните било где на радном листу. Убациће Цомбо Бок.
- Кликните десним тастером миша на Цомбо Бок и изаберите Својства.
- У прозору Својства направите следеће промене:
- Повезана ћелија: К2 (можете изабрати било коју ћелију у којој желите да приказује улазне вредности. Ову ћелију ћемо користити за подешавање података).
- ЛистФиллРанге: ЦоунтриЛист (ово је именовани опсег који смо креирали у 1. кораку. Ово ће приказати све земље у падајућем менију).
- МатцхЕнтри: 2-фмМатцхЕнтриНоне (ово осигурава да се реч не довршава аутоматски док куцате)
- Када је Цомбо Бок изабран, идите на Девелопер Таб -> Цонтролс -> Кликните на Десигн Моде (ово вас избацује из режима дизајна, па сада можете да откуцате било шта у Цомбо Бок. Сада, шта год да откуцате, одразиће се у ћелији К2 у реалном времену)
Корак 3 - Подешавање података
Коначно, све повезујемо помоћним колонама. Овде користим три помоћне колоне за филтрирање података.
Колона помоћника 1: Унесите серијски број за све записе (у овом случају 20). За то можете користити формулу РОВС ().
Колона помоћника 2: У помоћној колони 2 проверавамо да ли се текст унесен у оквир за претрагу подудара са текстом у ћелијама у колони за државу.
Ово се може урадити комбинацијом функција ИФ, ИСНУМБЕР и СЕАРЦХ.
Ево формуле:
= ИФ (ИСНУМБЕР (СЕАРЦХ ($ К $ 2, Д4)), Е4, "")
Ова формула ће тражити садржај у оквиру за претрагу (који је повезан са ћелијом К2) у ћелији која има назив земље.
Ако постоји подударање, ова формула враћа број реда, иначе враћа празно. На пример, ако комбиновано поље има вредност „САД“, сви записи са земљом као „САД“ имаће број реда, а сви остали ће бити празни („“)
Колона помоћника 3: У помоћној колони 3, морамо да скупимо све бројеве редова из помоћне колоне 2. Да бисмо то урадили, можемо користити комбинацију формула ИФЕРРОР и СМАЛЛ. Ево формуле:
= ИФЕРРОР (МАЛИ ($ Ф $ 4: $ Ф $ 23, Е4), "")
Ова формула слаже све одговарајуће бројеве редова заједно. На пример, ако Цомбо Бок има вредност УС, сви бројеви редова са „УС“ у њему се слажу заједно.
Сада када имамо бројеве редова наслагане заједно, само морамо да издвојимо податке у ове бројеве редова. То се може лако урадити помоћу формуле индекса (уметните ову формулу тамо где желите да издвојите податке. Копирајте је у горњу леву ћелију у коју желите да се подаци издвоје, а затим је превуците надоле и удесно).
= ИФЕРРОР (ИНДЕКС ($ Б $ 4: $ Д $ 23, $ Г4, ЦОЛУМНС ($ И $ 3: И3)), "")
Ова формула има 2 дела:
ИНДЕКС - Ово издваја податке на основу броја реда.
ИФЕРРОР - Ово враћа празно ако нема података.
Ево снимка онога што коначно добијате:
Цомбо Бок је падајући мени као и оквир за претрагу. Можете сакрити изворне податке и помоћне колоне да бисте приказали само филтриране записе. Такође можете имати необрађене податке и помоћне колоне на неком другом листу и креирати овај динамички екцел филтер на другом радном листу.
Будите креативни! Испробајте неке варијације
Можете покушати да га прилагодите својим захтевима. Можда ћете желети да направите више екцел филтера уместо једног. На пример, можда ћете желети да филтрирате записе у којима је продајни представник Мике, а земља Јапан. Ово се може урадити тачно пратећи исте кораке са неким изменама у формули у помоћним колонама.
Друга варијација би могла бити филтрирање података који почињу знаковима које унесете у комбиновани оквир. На пример, када унесете „И“, можда ћете желети да извучете земље које почињу са И (у поређењу са тренутном конструкцијом где би вам такође дали Сингапур и Филипине јер садржи абецеду И).
Као и увек, већина мојих чланака инспирисана је питањима/одговорима мојих читалаца. Волео бих да добијем ваше повратне информације и учим од вас. Оставите своје мисли у одељку за коментаре.
Напомена: У случају да користите Оффице 365, можете да користите функцију ФИЛТЕР да бисте брзо филтрирали податке док куцате. Лакши је од методе приказане у овом водичу.
