Как се правят зависими падащи менюта е въпрос, който ми задават често напоследък. Те наистина могат да бъдат много полезни и дават възможност да се улеснят и автоматизират дейности по въвеждане на стандартни, еднотипни данни в бланка, фактура, списък със стоки/клиенти/дейности и какво ли още не. Затова реших да отделя подобаващо място на тази тема. В настоящата статия ще ви запозная с основната идея при създаването на зависими падащи менюта, а във втора част ще разгледам някои по-сложни (и забавни) техники.
Нека първо поясня какво разбирам под зависими падащи менюта. Да допуснем, че в една семейна фирма за производство на закуски искат да опростят и подобрят процеса на събиране на поръчки. За целта създават файл в ексел, който предоставят на клиентите си. В него клиентите могат да избират видовете закуски с падащи менюта, както е показано на долната картинка
Виждате, че в клетката под „Вид закуска“ има падащо меню с три стойности: Пица, Баница и Кифла. В съседната клетка, под Изделие, също е създаден падащ списък. Само, че стойностите в него се променят. Ако за вид закуска изберем „Пица“, изделията са няколко вида пица, но ако изберем „Кифла“ или „Баница“ – стойностите са съвсем други. Сиреч падащото меню за изделията зависи от стойността, избрана за закуска. Ето защо се нарича зависимо падащо меню или списък.
Как се прави?
Вероятно се досещате, че първата стъпка е да си създадем съответните списъци, както е показано по-долу:
Тук изключително важно е наименованията на колоните за видовете закуски да съответстват абсолютно точно на това, което пише в колоната Закуски. Ама много е важно!
След като създадем списъците, трябва да създадем наименовани области (Name ranges) за всеки един от тях. Това става като ги маркираме и отидем на Formulas > Defined Names > Create from Selection. Както виждате от картинката има си и клавишна комбинация – CTRL + SHIFT + F3.
Появява се меню, в което Ексел ни пита откъде да вземе имената за наименованите области. Тук трябва да сложим отметка на Top row и даваме ОК.
На пръв поглед това действие не води до никакви последствия. Нищо ново не се появява във файла. Но това не е съвсем така. Достатъчно е да отидем на Formulas > Name Manager и ще видим, че Ексел е създал четирите наименовани области:
Сега вече можем да създадем падащото меню за вида закуска. За целта отиваме на съответната клетка, а след това: Data > Data Validation и попълваме така:
По-подробно създаването на падащо меню с Data Validation e описано в тази статия.
Сега вече имаме нашия падащ списък за вида на закуската:
При създаването на зависимия падащ списък стъпките са същите, но с една съществена разлика. В полето Source въвеждаме формулата =INDIRECT($B$13).
И когато дадем ОК магията се случва:
Как работи?
Ключът тук е функцията INDIRECT. Тя връща като резултат не число или текст, а референция (връзка) към адрес като извиква това, което се намира на този адрес. Например, ако в клетка Е1 пише F1, а в клетка F1 пише „Баклава“, то формулата =INDIRECT(E1) ще прочете стойността в клетка Е1, ще създаде връзка към този адрес (F1) и ще върне като резултат това, което пише в клетка F1.
Така обяснено изглежда странно, защото кой е толкова луд, че да пише F1 в Е1, вместо да си направи най-простата формула на света =F1 и да си получи като резултат „Баклава“.
Но когато правим зависими падащи менюта, точно това ни трябва. Когато в клетка B13 пише „Пица“, INDIRECT прочита този текст и създава връзка към наименованата област „Пица“, която съдържа съответните видове пици. Когато го сменим с „Баница“ или „Кифла“, тогава INDIRECT реферира към съответните наименовани области и връща други резултати. Ето защо беше толкова важно заглавните редове на съответните изделия да са същите стойности като списъка с видовете закуски.
Други примери за използване на INDIRECT може да прочетете тук
Допълване на списъците
Няма нищо по-нормално от това нашата семейна фирма да иска да развива и обогатява своя асортимент. Възниква въпросът – как да добавя нови стойности в своите зависими падащи менюта. Например, ако започне производството на баница с праз. Има много начини, но в моята практика съм се убедил, че най-простият и интуитивен за потребителите е следният:
Когато правим наименованите области, обхващаме и един празен ред под списъка. След това го оцветяваме в яркожълто и даваме ясни инструкции, когато се добавят нови стойности в падащия списък, да се вмъква ред над жълтия. Това почти не променя нашия падащ списък. Той просто съдържа един празен ред най-отдолу
Но при добавяне на нова стойност по указания по-горе начин, тя автоматично се появява в падащото меню
Има едно малко неудобство, когато списъците са с различен брой клетки. Вижте като добавихме нова стойност за баниците, как при кифлите вече имаме две празни клетки най-отдолу
А при пиците там празните клетки са дори още повече.
Има няколко лекарства за тази болка. По-сложните ще разгледам в следващата статия. За сега нека се спрем на най-простото – да подредим списъците един под друг. Така колкото и редове да вмъкваме за баниците, това няма да създаде празни редове при кифлите и пиците
Заключение
С тази статия накратко показах логиката за създаване на падащи списъци в Ексел, единият от които се променя при промяна на стойностите в първия. Този метод вероятно ще ви свърши работа в 90% от случаите, в които ви трябва. Но когато почнете да създавате такива падащи менюта неминуемо ще се сблъскате с въпроси като „Как да се обновяват списъците автоматично при добавяне на нови стойности?“, „Защо ми дава грешка, когато стойностите в първия списък не са от една дума?“, „Има ли по-ефективен начин, защото трябва да направя повече от две нива на падащи менюта?“. На всички тези въпроси ще бъде посветена отделна статия, така че stay tuned
Ако искате да научите още нещо в Ексел, можете да споделите в съвсем свободна форма тук