Търсене в падащ списък в 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 премахнете отметката на горния чек-бутон и потвърдете с ОК.
Вашият падащ списък с възможност за търсене е готов! 😊
Прочете още: Как да копирате само видимите клетки от определена област в Ексел , ОТКРИВАНЕ НА РАЗЛИКИ МЕЖДУ ДВЕ ГОЛЕМИ ШАБЛОННИ ТАБЛИЦИ С ДАННИ
За още по-ефективна работа с Еxcel разгледайте и предлаганите от нас курсове с приложението.