Електронните таблици на Excel често включват падащи клетки, за да се опрости и / или стандартизира въвеждането на данни. Тези падащи менюта се създават с помощта на функцията за проверка на данните, за да се определи списък с допустими записи.
За да настроите просто падащ списък, изберете клетката, в която ще бъдат въведени данните, след това щракнете върху Проверка на данните (в раздела Данни ), изберете Валидиране на данни, изберете Списък (под Разрешаване :) и след това въведете елементите от списъка (разделени със запетаи) ) в полето Източник : (виж Фигура 1).
В този тип основно падащо меню списъкът на допустимите записи е посочен в самата проверка на данните; следователно, за да направите промени в списъка, потребителят трябва да отвори и редактира валидирането на данните. Това обаче може да бъде трудно за неопитни потребители или в случаите, когато списъкът с възможности за избор е дълъг.
Друга възможност е списъкът да бъде поставен в именуван диапазон в електронната таблица и след това да се посочи това име на обхвата (предхождано със знак за равенство) в полето Източник : на валидирането на данни (както е показано на фигура 2).
Този втори метод улеснява редактирането на избора в списъка, но добавянето или премахването на елементи може да бъде проблематично. Тъй като именуваният диапазон (FruitChoices, в нашия пример) се отнася за фиксиран диапазон от клетки ($ H $ 3: $ H $ 10, както е показано), ако към клетките H11 или по-долу са добавени повече възможности, те няма да се показват в падащото меню (тъй като тези клетки не са част от гамата FruitChoices).
По същия начин, ако например записите на Pears и Strawberries бъдат изтрити, те вече няма да се появяват в падащото меню, но вместо това падащото меню ще включва два „празни“ избора, тъй като падащото меню все още се отнася за целия диапазон на FruitChoices, включително празните клетки H9 и H10.
Поради тези причини, когато се използва нормален диапазон с имена като източник на списък за падащо меню, самият асоцииран диапазон трябва да се редактира, за да включи повече или по-малко клетки, ако записите се добавят или изтриват от списъка.
Решението на този проблем е да се използва име на динамичен обхват като източник за избора на падащото меню. Името на динамичния обхват е това, което автоматично се разширява (или се сключва), за да съответства точно на размера на блок от данни, когато записите се добавят или премахват. За да направите това, използвайте формула, а не фиксиран диапазон от адреси на клетки, за да определите посочения диапазон.
Как да настроите динамичен диапазон в Excel
Нормално (статично) име на обхват се отнася за определен диапазон от клетки ($ H $ 3: $ H $ 10 в нашия пример, вижте по-долу):
Но динамичен обхват се определя с помощта на формула (вж. По-долу, взета от отделна електронна таблица, която използва имена на динамични диапазони):
Преди да започнете, уверете се, че сте изтеглили нашия примерен файл на Excel (макросите за сортиране са деактивирани).
Нека разгледаме подробно тази формула. Изборът за плодове е в блок от клетки непосредствено под заглавието ( FRUITS ). На това заглавие се присвоява и име: Плодове :
Цялата формула, използвана за определяне на динамичния диапазон за избора на плодове, е:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (вярно, индекс (ISBLANK (OFFSET (FruitsHeading, 1, 0, 20, 1)), 0, 0), 0) -1, 20), 1)
FruitsHeading се отнася до заглавието, което е един ред над първото вписване в списъка. Числото 20 (използвано два пъти във формулата) е максималният размер (брой редове) за списъка (това може да се коригира по желание).
Имайте предвид, че в този пример в списъка има само 8 записа, но под тях има и празни клетки, където могат да се добавят допълнителни записи. Числото 20 се отнася за целия блок, където могат да се правят записи, а не за действителния брой записи.
Сега нека разбием формулата на парчета (цветно кодиране на всяко парче), за да разберем как работи:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH) (ИСТИНСКИ, ИНДЕКС (ISBLANK ( OFFSET (FruitsHeading, 1, 0, 20, 1) ), 0, 0), 0) -1, 20), 1)
" Вътрешната " част е OFFSET (FruitsHeading, 1, 0, 20, 1) . Това се позовава на блок от 20 клетки (под клетката FruitsHeading), където могат да бъдат въведени възможности за избор. Тази функция OFFSET главно казва: Започнете от клетката FruitsHeading, спуснете се 1 ред и над 0 колони, след това изберете област, която е дълга 20 реда и широка 1 колона. Така че това ни дава 20-ред блок, където са въведени избори за плодове.
Следващата част от формулата е ISBLANK функцията:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH) (ИСТИНА, ИНДЕКС ( ISBLANK (по-горе), 0, 0), 0) -1, 20), 1)
Тук функцията OFFSET (обяснена по-горе) е заменена с “по-горе” (за да се улеснят четенето). Но функцията ISBLANK работи на 20-ред диапазон от клетки, които функцията OFFSET определя.
След това ISBLANK създава набор от 20 стойности TRUE и FALSE, показващи дали всяка от отделните клетки в 20-редната редица, посочена от функцията OFFSET, е празна (празна) или не. В този пример първите 8 стойности в набора ще бъдат FALSE, тъй като първите 8 клетки не са празни и последните 12 стойности ще бъдат TRUE.
Следващата част от формулата е функцията INDEX:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX (по-горе, 0, 0), 0) -1, 20), 1)
Отново, "по-горе" се отнася до ISBLANK и OFFSET функциите, описани по-горе. Функцията INDEX връща масив, съдържащ 20 стойности TRUE / FALSE, създадени от ISBLANK функцията.
ИНДЕКС обикновено се използва за избиране на определена стойност (или диапазон от стойности) от блок данни, като се посочва определен ред и колона (в рамките на този блок). Но задаването на входа на ред и колона на нула (както е направено тук) кара INDEX да върне масив, съдържащ целия блок данни.
Следващата част от формулата е MATCH функцията:
= OFFSET (FruitsHeading, 1, 0, IFERROR ( MATCH (TRUE, горе, 0) -1, 20), 1)
Функцията MATCH връща позицията на първата стойност TRUE в масива, който се връща от функцията INDEX. Тъй като първите 8 записа в списъка не са празни, първите 8 стойности в масива ще бъдат FALSE, а деветата стойност ще бъде TRUE (тъй като деветият ред в диапазона е празен).
Така функцията MATCH ще върне стойността 9 . В този случай, обаче, наистина искаме да знаем колко записа са в списъка, така че формулата изважда 1 от стойността на MATCH (която дава позицията на последния запис). Така че в крайна сметка, MATCH (TRUE, по-горе, 0) -1 връща стойността на 8 .
Следващата част от формулата е функцията IFERROR:
= OFFSET (FruitsHeading, 1, 0, IFERROR (по-горе, 20), 1)
Функцията IFERROR връща алтернативна стойност, ако първата определена стойност води до грешка. Тази функция е включена, тъй като ако целият блок от клетки (всичките 20 реда) са запълнени с записи, функцията MATCH ще върне грешка.
Това е така, защото казваме на MATCH функцията да търси първата TRUE стойност (в масива от стойности от ISBLANK функцията), но ако NONE от клетките са празни, целият масив ще бъде запълнен с FALSE стойности. Ако MATCH не може да намери целевата стойност (TRUE) в масив, който търси, той връща грешка.
Така че, ако целият списък е пълен (и следователно MATCH връща грешка), функцията IFERROR вместо това ще върне стойността от 20 (знаейки, че трябва да има 20 записа в списъка).
И накрая, OFFSET (FruitsHeading, 1, 0, по-горе, 1) връща диапазона, който всъщност търсим: Започнете от клетката FruitsHeading, слезете надолу с 1 ред и над 0 колони, след което изберете област, която е много редове, има записи в списъка (и 1 колона широк). Така цялата формула заедно ще върне диапазона, който съдържа само действителните записи (до първата празна клетка).
Използването на тази формула за определяне на обхвата, който е източникът на падащото меню, означава, че можете свободно да редактирате списъка (добавяне или премахване на записи, докато останалите записи започват от горната клетка и са съседни) и падащото меню винаги ще отразява текущото списък (вж. фигура 6).
Примерният файл (динамични списъци), който се използва тук, е включен и може да бъде изтеглен от този уебсайт. Макросите обаче не работят, защото WordPress не харесва Excel книги с макроси в тях.
Като алтернатива на задаване на броя на редовете в блоковия списък, блокът със списък може да бъде назначен собствено име на обхват, което след това може да се използва в модифицирана формула. В примера, вторият списък (Имена) използва този метод. Тук целият блок от списък (под заглавието „NAMES“, 40 реда в примерния файл) се присвоява името на обхвата на NameBlock . След това алтернативната формула за дефиниране на NamesList е:
= OFFSET (NamesHeading, 1, 0, IFERROR (MATCH) (ИСТИНА, ИНДЕКС (ISBLANK ( NamesBlock ), 0, 0), 0) -1, ROWS (NamesBlock) ), 1)
където NamesBlock замества OFFSET (FruitsHeading, 1, 0, 20, 1) и ROWS (NamesBlock) заменя 20 (брой редове) в по-ранната формула.
Така че, за падащите списъци, които могат лесно да бъдат редактирани (включително и от други потребители, които могат да са неопитни), опитайте да използвате имена на динамични диапазони! И имайте предвид, че макар тази статия да е съсредоточена върху падащите списъци, имената на динамичните диапазони могат да се използват навсякъде, където трябва да се позовете на диапазон или списък, които могат да се различават по размер. Наслади се!
