Computer help - file server


[ Blog ] - [ File Server ] - [ Удаленная компьютерная помощь ]

Табліцы Excel часта ўключаюць выпадаючыя вочкі для спрашчэння і/або стандартызацыі ўводу дадзеных. Гэтыя выпадальныя спісы ствараюцца з дапамогай функцыі праверкі дадзеных, каб паказаць спіс дазволеных запісаў.

Каб наладзіць просты выпадальны спіс, вылучыце ячэйку, куды будуць уводзіцца дадзеныя, затым націсніце Праверка дадзеных (на ўкладцы Дадзеныя), абярыце Праверку дадзеных, абярыце Спіс (пад Дазволіць :) і ўвядзіце элементы спісу (падзеленыя коскамі) у полі Крыніца: (гл. малюнак 1).



У гэтым тыпе асноўнага выпадальнага спісу спіс дазволеных запісаў паказваецца ў самой праверцы дадзеных; таму, каб унесці змены ў спіс, карыстальнік павінен адкрыць і адрэдагаваць праверку дадзеных.Аднак гэта можа быць цяжка для недасведчаных карыстальнікаў альбо ў тых выпадках, калі спіс выбараў доўгі.

Іншы варыянт - змясціць спіс у названы дыяпазон у электроннай табліцы, а затым паказаць гэтае імя дыяпазону (з папярэднім знакам роўнасці) у полі Source: праверкі дадзеных (як паказана на малюнку 2) ).



Гэты другі спосаб палягчае рэдагаванне выбараў у спісе, але даданне і выдаленне элементаў можа быць праблематычным. Паколькі названы дыяпазон (FruitChoices, у нашым прыкладзе) адносіцца да фіксаванага дыяпазону ячэек ($ H $ 3: $ H $ 10, як паказана), калі ў ячэйкі H11 або ніжэй дадаецца больш варыянтаў, яны не будуць адлюстроўвацца ў выпадальным меню (паколькі гэтыя клеткі не ўваходзяць у асартымент FruitChoices).

Сапраўды гэтак жа, калі, напрыклад, запісы Грушы і Клубніцы будуць выдалены, яны больш не будуць адлюстроўвацца ў выпадальным меню, але замест гэтага выпадальны спіс будзе ўключаць два "пустыя" варыянты, паколькі выпадальнае меню ўсё яшчэ спасылаецца на ўвесь асартымент FruitChoices , уключаючы пустыя клеткі H9 і H10.

Па гэтых прычынах, пры выкарыстанні нармальнага іменаванага дыяпазону ў якасці крыніцы спісу для выпадальнага меню, сам названы дыяпазон павінен быць адрэдагаваны, каб уключыць больш ці менш вочак, калі запісы дадаюцца альбо выдаляюцца са спісу.

Рашэннем гэтай праблемы з'яўляецца выкарыстанне імя дынамічнага дыяпазону ў якасці крыніцы выбару выпадальнага меню.Імя дынамічнага дыяпазону - гэта імя, якое аўтаматычна пашыраецца (альбо скарачаецца), каб дакладна адпавядаць памеру блока дадзеных пры даданні або выдаленні запісаў. Для гэтага вы выкарыстоўваеце формулу, а не фіксаваны дыяпазон адрасоў сот, для вызначэння названага дыяпазону. Як усталяваць дынамічны дыяпазон у Excel

Звычайнае (статычнае) імя дыяпазону адносіцца да зададзенага дыяпазону вочак ($ H $ 3: $ H $ 10 у нашым прыкладзе, гл. Ніжэй):



Але дынамічны дыяпазон вызначаецца з дапамогай формулы (гл. ніжэй, узятай з асобнай электроннай табліцы, якая выкарыстоўвае імёны дынамічнага дыяпазону):



Перш чым пачаць, пераканайцеся, што вы загрузілі наш прыкладны файл Excel (макрасы сартавання адключаны).

Давайце вывучым гэтую формулу падрабязна. Садавіна можна выбраць у блоку вочак непасрэдна пад загалоўкам (ФРУКТЫ). Гэтаму загалоўку таксама прысвоена імя: FruitsHeading:



Уся формула, якая выкарыстоўваецца для вызначэння дынамічнага дыяпазону выбару Fruits: = OFFSET (FruitsHeading, 1,0, IFERROR (MATCH (TRUE, INDEX (ISBLANK (OFFSET (FruitsHeading, 1,0,20,1)), 0,0), 0) -1,20), 1)

FruitsHeading адносіцца да загалоўка, які знаходзіцца на адзін радок над першым запісам у спісе. Лік 20 (у формуле выкарыстоўваецца два разы) - гэта максімальны памер (колькасць радкоў) спіса (яго можна адрэгуляваць па жаданні).

Звярніце ўвагу, што ў гэтым прыкладзе ў спісе ўсяго 8 запісаў, але ёсць і пустыя вочкі пад імі, дзе можна дадаць дадатковыя запісы. Лічба 20 адносіцца да ўсяго блока, дзе можна рабіць запісы, а не да рэальнай колькасці запісаў.

Зараз разбяром формулу на кавалкі (каляровае кадаванне кожнай часткі), каб зразумець, як гэта працуе: = OFFSET (FruitsHeading, 1,0, IFERROR (MATCH (TRUE, INDEX (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 (TRUE, INDEX (ISBLANK (the above), 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 (the above, 0,0), 0) -1,20) , 1)

Зноў "вышэй" адносіцца да апісаных вышэй функцый ISBLANK і OFFSET.Функцыя INDEX вяртае масіў, які змяшчае 20 значэнняў TRUE/FALSE, створаных функцыяй ISBLANK.

INDEX звычайна выкарыстоўваецца для выбару пэўнага значэння (альбо дыяпазону значэнняў) з блока дадзеных, паказваючы пэўную радок і слупок (у гэтым блоку). Але ўстаноўка нулявых значэнняў для радкоў і слупкоў (як гэта зроблена тут) прымушае INDEX вяртаць масіў, які змяшчае ўвесь блок дадзеных.

Наступная частка формулы - гэта функцыя MATCH: = OFFSET (FruitsHeading, 1,0, IFERROR (MATCH (TRUE, the above, 0) -1,20), 1)

Функцыя MATCH вяртае пазіцыю першага TRUE значэння ў масіве, які вяртаецца функцыяй INDEX.Паколькі першыя 8 запісаў у спісе не пустыя, першыя 8 значэнняў у масіве будуць FALSE, а дзевятае значэнне будзе TRUE (так як 9-ы радок у дыяпазоне пусты).

Такім чынам, функцыя MATCH верне значэнне 9. Аднак у гэтым выпадку мы сапраўды хочам ведаць, колькі запісаў у спісе, таму формула адымае 1 ад значэння MATCH (што дае пазіцыя апошняга запісу). Такім чынам, у канчатковым рахунку, MATCH (TRUE, вышэй, 0) -1 вяртае значэнне 8.

Наступная частка формулы - гэта функцыя IFERROR: = OFFSET (FruitsHeading, 1,0, IFERROR (the вышэй, 20), 1)

Функцыя IFERROR вяртае альтэрнатыўнае значэнне, калі першае ўказанае значэнне прыводзіць да памылкі.Гэтая функцыя ўключана, бо, калі ўвесь блок вочак (усе 20 радкоў) запоўнены запісамі, функцыя MATCH верне памылку.

Гэта таму, што мы загадваем функцыі MATCH шукаць першае значэнне TRUE (у масіве значэнняў з функцыі ISBLANK), але калі НІ адна з вочак пустая, то ўвесь масіў запоўніць значэнні FALSE. Калі MATCH не можа знайсці мэтавае значэнне (TRUE) у масіве, які ён шукае, ён вяртае памылку.

Такім чынам, калі ўвесь спіс поўны (і, такім чынам, MATCH вяртае памылку), функцыя IFERROR замест гэтага верне значэнне 20 (ведаючы, што ў спісе павінна быць 20 запісаў).

Нарэшце, OFFSET (FruitsHeading, 1,0, вышэй, 1) вяртае дыяпазон, які мы на самай справе шукаем: Пачніце з ячэйкі FruitsHeading, апусціцеся на 1 радок і перавядзіце 0 слупкоў, затым выберыце вобласць гэта значыць шмат радкоў, пакуль у спісе ёсць запісы (і шырынёй у 1 слупок). Такім чынам, уся формула разам верне дыяпазон, які змяшчае толькі фактычныя запісы (аж да першай пустой ячэйкі).

Выкарыстанне гэтай формулы для вызначэння дыяпазону, які з'яўляецца крыніцай выпадальнага меню, азначае, што вы можаце свабодна рэдагаваць спіс (дадаваць і выдаляць запісы, пакуль астатнія запісы пачынаюцца ў верхняй ячэйцы і знаходзяцца побач) выпадальны спіс заўсёды будзе адлюстроўваць бягучы спіс (гл. Малюнак 6).



Прыкладны файл (дынамічныя спісы), які быў выкарыстаны тут, уключаны і можа быць загружаны з гэтага вэб-сайта. Аднак макрасы не працуюць, бо WordPress не любіць кнігі Excel, у якіх ёсць макрасы.

У якасці альтэрнатывы ўказанню колькасці радкоў у блоку спіса блоку спісаў можа быць прысвоена ўласнае імя дыяпазону, якое потым можа быць выкарыстана ў змененай формуле. У файле прыкладу другі спіс (Імёны) выкарыстоўвае гэты метад. Тут усяму блоку спісу (пад загалоўкам «НАЗВЫ», 40 радкоў у файле прыкладу) прысвойваецца імя дыяпазону NameBlock.Тады альтэрнатыўная формула для вызначэння спіса імёнаў: = OFFSET (NamesHeading, 1,0, IFERROR (MATCH (TRUE, INDEX (ISBLANK (NamesBlock), 0,0)), 0) -1, ROWS (NamesBlock)), 1)

дзе NamesBlock замяняе OFFSET (FruitsHeading, 1,0,20,1), а ROWS (NamesBlock) замяняе 20 (колькасць радкоў) у ранейшай формуле.

Такім чынам, для выпадальных спісаў, якія можна лёгка рэдагаваць (у тым ліку іншымі карыстальнікамі, якія могуць быць недасведчанымі), паспрабуйце выкарыстоўваць імёны дынамічнага дыяпазону! І звярніце ўвагу, што, нягледзячы на ​​тое, што гэты артыкул быў сканцэнтраваны на выпадаючых спісах, імёны дынамічнага дыяпазону могуць выкарыстоўвацца ў любым месцы, дзе трэба спасылацца на дыяпазон альбо спіс, якія могуць адрознівацца па памеры.Атрымлівайце асалоду!

.
SETUP UA COMPUTER BLOG