Екцел табеле често укључују падајуће ћелије ћелија како би поједноставиле и / или стандардизовале унос података. Ове падајуће листе креирају се помоћу функције валидације података да би се одредила листа дозвољених уноса.
Да бисте подесили једноставну падајућу листу, изаберите ћелију у коју ће подаци бити унети, а затим кликните на Валидација података (на картици Подаци ), изаберите Провера ваљаности података, изаберите Лист (у оквиру Дозволи :), а затим унесите ставке листе (одвојене зарезима) ) у пољу Извор : (види слику 1).
У овом типу основног падајућег менија, листа дозвољених уноса је специфицирана унутар саме валидације података; стога, да бисте направили измене на листи, корисник мора да отвори и уреди валидацију података. Међутим, ово може бити тешко за неискусне кориснике или у случајевима када је листа избора дуготрајна.
Друга опција је да списак ставите у именовани опсег у табеларној табели, а затим наведете име опсега (претходно означено знаком једнакости) у пољу Извор : поље валидације података (као што је приказано на слици 2).
Овај други метод олакшава уређивање избора у листи, али додавање или уклањање ставки може бити проблематично. Пошто се именовани опсег (ФруитЦхоицес, у нашем примеру) односи на фиксни опсег ћелија ($ Х $ 3: $ Х $ 10 као што је приказано), ако се више ћелија дода ћелијама Х11 или ниже, неће се појавити у падајућем менију (пошто те ћелије нису део ФруитЦхоицес опсега).
Исто тако, ако се, на пример, ставке Пеарс и Стравберриес бришу, оне се више неће појављивати у падајућем менију, већ ће се у падајућем менију укључити два "празна" избора, будући да се падајући изборник још увијек односи на цијели ФруитЦхоицес опсег, укључујући празне ћелије Х9 и Х10.
Из тих разлога, када користите нормални именовани опсег као извор листе за падајући мени, именовани опсег се мора уредити тако да укључује више или мање ћелија ако се уноси или бришу са листе.
Решење овог проблема је коришћење имена динамичког опсега као извора за падајуће изборе. Име динамичког опсега је оно које се аутоматски проширује (или уговара) тако да се тачно поклапа са величином блока података, јер се уноси додају или уклањају. Да бисте то урадили, користите формулу, а не фиксни опсег адреса ћелија, да бисте дефинисали именовани опсег.
Како да подесите динамички опсег у Екцелу
Нормално (статички) назив опсега односи се на одређени опсег ћелија ($ Х $ 3: $ Х $ 10 у нашем примеру, погледајте доле):
Међутим, динамички опсег се дефинише помоћу формуле (погледајте испод, преузето из засебне табеле која користи имена динамичког опсега):
Пре него што почнемо, проверите да ли сте преузели наш примерак програма Екцел (макрои за сортирање су онемогућени).
Погледајмо ову формулу детаљно. Избор за воће је у блоку ћелија непосредно испод наслова ( ВОЋЕ ). Томе је додељено и име: Воће :
Читава формула која се користи за дефинисање динамичког опсега за избор воћа је:
= ОФФСЕТ (ФруитсХеадинг, 1, 0, ИФЕРРОР (МАТЦХ) (ТРУЕ, ИНДЕКС (ИСБЛАНК (ОФФСЕТ (ФруитсХеадинг, 1, 0, 20, 1)), 0, 0), 0) -1, 20), 1)
ФруитсХеадинг се односи на заглавље које је један ред изнад првог уноса у листи. Број 20 (кориштен два пута у формули) је максимална величина (број редова) за листу (ово се може подесити по жељи).
Имајте на уму да у овом примеру постоји само 8 уноса у листи, али испод њих постоје и празне ћелије где се могу додати додатни уноси. Број 20 се односи на цео блок где се уноси могу извршити, а не на стварни број уноса.
Сада разделимо формулу на делове (бојање сваког дела), да бисмо разумели како функционише:
= ОФФСЕТ (ФруитсХеадинг, 1, 0, ИФЕРРОР (МАТЦХ) (ТРУЕ, ИНДЕКС (ИСБЛАНК ( ОФФСЕТ (ФруитсХеадинг, 1, 0, 20, 1) ), 0, 0), 0) -1, 20), 1)
"Најдубљи" комад је ОФФСЕТ (Воће, 1, 0, 20, 1) . Ово се односи на блок од 20 ћелија (испод ћелије воћа) где се могу унети избори. Ова ОФФСЕТ функција у основи каже: Почните од ћелије ФруитсХеадинг, идите доле 1 ред и преко 0 колона, а затим изаберите област која је дугачка 20 редова и 1 ширину колоне. То нам даје блок од 20 редова у који се уносе избори воћа.
Следећи део формуле је ИСБЛАНК функција:
= ОФФСЕТ (ФруитсХеадинг, 1, 0, ИФЕРРОР (МАТЦХ) (ТРУЕ, ИНДЕКС ( ИСБЛАНК (горе), 0, 0), 0) -1, 20), 1)
Овде је ОФФСЕТ функција (објашњена горе) замењена са “горе” (да би ствари биле лакше читљиве). Међутим, функција ИСБЛАНК-а ради на опсегу ћелија од 20 реда које дефинира функција ОФФСЕТ.
ИСБЛАНК затим креира сет од 20 ТРУЕ и ФАЛСЕ вредности, указујући да ли је свака од појединачних ћелија у опсегу од 20 редова на које упућује ОФФСЕТ функција празна (празна) или не. У овом примеру, првих 8 вредности у скупу ће бити ФАЛСЕ, јер првих 8 ћелија нису празне и последњих 12 вредности ће бити ТРУЕ.
Следећи део формуле је ИНДЕКС функција:
= ОФФСЕТ (ФруитсХеадинг, 1, 0, ИФЕРРОР (МАТЦХ) (ТРУЕ, ИНДЕКС (горе, 0, 0), 0) -1, 20), 1)
Опет, “горе” се односи на ИСБЛАНК и ОФФСЕТ функције описане горе. Функција ИНДЕКС враћа низ који садржи 20 ТРУЕ / ФАЛСЕ вриједности креираних ИСБЛАНК функцијом.
ИНДЕКС се обично користи за бирање одређене вриједности (или распона вриједности) из блока података, навођењем одређеног реда и ступца (унутар тог блока). Али постављање реда и колона на нулу (као што је овде урађено) узрокује да ИНДЕКС врати низ који садржи цео блок података.
Следећи део формуле је МАТЦХ функција:
= ОФФСЕТ (ФруитсХеадинг, 1, 0, ИФЕРРОР ( МАТЦХ (ТРУЕ, горе, 0) -1, 20), 1)
МАТЦХ функција враћа позицију прве ТРУЕ вриједности, унутар поља који се враћа функцијом ИНДЕКС. Пошто првих 8 уноса у листи нису празни, првих 8 вредности у низу биће ФАЛСЕ, а девета вредност ће бити ТРУЕ (пошто је 9. ред у опсегу празан).
Тако ће функција МАТЦХ вратити вредност 9 . У овом случају, међутим, заиста желимо да знамо колико је уноса у листи, тако да формула одузима 1 од вредности МАТЦХ (која даје позицију последњег уноса). На крају, МАТЦХ (ТРУЕ, горе, 0) -1 враћа вредност 8 .
Следећи део формуле је функција ИФЕРРОР:
= ОФФСЕТ (ФруитсХеадинг, 1, 0, ИФЕРРОР (горе, 20), 1)
Функција ИФЕРРОР враћа алтернативну вриједност, ако прва специфицирана вриједност резултира грешком. Ова функција је укључена јер, ако је читав блок ћелија (свих 20 редова) попуњен са уносима, МАТЦХ функција ће вратити грешку.
То је зато што говоримо МАТЦХ функцији да тражи прву ТРУЕ вриједност (у низу вриједности из ИСБЛАНК функције), али ако НОНЕ од ћелија није празна, онда ће цијели низ бити испуњен ФАЛСЕ вриједностима. Ако МАТЦХ не може да пронађе циљну вредност (ТРУЕ) у низу који тражи, враћа грешку.
Дакле, ако је цијела листа пуна (и стога, МАТЦХ враћа грешку), ИФЕРРОР функција ће умјесто тога вратити вриједност 20 (знајући да мора бити 20 уноса у попису).
Коначно, ОФФСЕТ (ФруитсХеадинг, 1, 0, горе, 1) враћа опсег који заправо тражимо: Почните од ћелије ФруитсХеадинг, идите доле за 1 ред и преко 0 колона, а затим изаберите област која има много редова постоје ставке у листи (и 1 колона широко). Тако ће цијела формула заједно вратити распон који садржи само стварне уносе (до прве празне ћелије).
Користећи ову формулу да бисте дефинисали опсег који је извор за падајуће листе, можете слободно уредити листу (додавање или уклањање уноса, све док преостали уноси почињу у горњој ћелији и налазе се у близини) и падајући мени ће увек одражавати тренутну (види слику 6).
Датотека примера (Динамичке листе) која је овде коришћена је укључена и може се преузети са овог веб сајта. Међутим, макрои не раде, јер ВордПресс не воли Екцелове књиге са макроима у њима.
Као алтернативу одређивању броја редова у блоку листе, блоку листе може се доделити сопствено име опсега, које се затим може користити у модификованој формули. У датотеци примера, друга листа (Имена) користи овај метод. Овде је целом блоку листа (испод наслова „ИМЕНА“, 40 редова у датотеци примера) додељено име опсега НамеБлоцк . Алтернативна формула за дефинисање НамесЛист-а је:
= ОФФСЕТ (НамесХеадинг, 1, 0, ИФЕРРОР (МАТЦХ) (ТРУЕ, ИНДЕКС (ИСБЛАНК ( НамесБлоцк ), 0, 0), 0) -1, РОВС (НамесБлоцк) ), 1)
где НамесБлоцк замењује ОФФСЕТ (ФруитсХеадинг, 1, 0, 20, 1) и РОВС (НамесБлоцк) замењује 20 (број редова) у ранијој формули.
Дакле, за падајуће листе које се могу лако уређивати (укључујући и друге кориснике који могу бити неискусни), покушајте да користите имена динамичког опсега! И имајте на уму да, иако је овај чланак фокусиран на падајуће листе, имена динамичког опсега се могу користити било где да се позовете на опсег или листу која може да варира у величини. Уживати!
