Търсене в падащ списък в Excel

Падащият списък в Excel улеснява въвеждането на данни и възпрепятства грешното им записване. Когато обаче той е дълъг и не е сортиран предварително, тогава потребителят би се затруднил при търсенето в него. Колко лесно би било, ако при въведен текст, падащия списък визуализира всички записи, в които присъства.

В тази статия ще покажем как се създава падащо меню с възможност за търсене по определен запис,  известно още като searchable drop-down меню.

За примера разполагаме със списък с имена, които ще се появяват в падащото меню.

Клетка G3 ще бъде клетката, в която ще търсим съвпадение в имената, на база въведен запис в нея.

В колона A ще се извършва проверка дали въведения от нас текст фигурира в клетките за имена.

Използваме функцията за търсене SEARCH, която търси един текстов низ в друг и връща номера на позицията като символ, в която се намира. Ако не намери съвпадение връща грешка #VALUE. Ще я вградим в информативната функция ISNUMBER, която проверява дали резултатът е число и връща резултати TRUE или FALSE. За да превърнем логическия резултат в числов е необходимо да се поставят само два последователни минусови знака пред функцията ISNUMBER и автоматично резултатите TRUE и FALSE се заместват с техните числови еквиваленти 1 и 0.

Крайният резултат е:  =– -ISNUMBER(SEARCH($G$3,C2))

В колона B, ще подредим намерените съвпадения по възходящ ред.  За целта ще използваме логическата функция IF, в която ще проверяваме, ако в лявостоящата клетка стойността е равна на 1, тогава да се сумират всички клетки преди и включително нея, в противен случай да се записва числото 0.

Функцията в клетка B2 e : =IF(A2=1,SUM($A$2:A2),0) . С копиране на функцията надолу областта се променя, като горната клетка винаги остава фиксирана.

В колона D ще въведем последователно номeриране на редоветe от D2 до края на записа.
В колона Е чрез функцията за търсене  VLOOKUP ще намерим името на служителя отговарящо на цифрата на всеки номериран запис от колона D. За да не връща функцията резултат #N/A при липса на съвпадение ще я вградим в логическата IFERROR, която при грешка да връща празна стойност или още казано записа в клетка Е2 ще има следния вид : =IFERROR(VLOOKUP(D2,$B$2:$C$20,2,0),““)

В произволна клетка ще използваме функцията OFFSET, която връща препратка към диапазон, намиращ се на зададен брой редове и колони от клетка или диапазон от клетки.  Броят редове ще се бъде резултат от броенето на функцията COUNTIF, връщаща броя на непразните клетки в колона Е. Изхождайки от текущия пример функцията има следния вид: =OFFSET($E$1,1,0,COUNTIF($E$2:$E$20,“?*“))

Копираме тази формула в буферната памет и ще създадем именувана област от таб Formulas -> група Defined Names -> Define Name

Задайте подходящо име и в полето за референция поставете вече копираната функция.

Селектираме клетката за търсене, в случая G3, и от таб Data-> Data validation -> List  като източник (Source) въведете току-що именувата област.

От раздела Еrror Alert на прозореца Data validation премахнете отметката на горния чек-бутон и потвърдете с ОК.

Вашият падащ списък с възможност за търсене е готов! 😊

Очаквайте скоро и видео към статията.

 

 

Интересувате се от темата? Пишете ни!

Бързо запитване

Вашите имена *

Вашият Email *

Вашето съобщение *

captcha

Добавете коментар

Вашият имейл адрес няма да бъде публикуван. Задължителните полета са отбелязани с *