змест
Віншую! Вы дабраліся да апошняга дня марафону 30 функцый Excel за 30 дзён. Гэта было доўгае і цікавае падарожжа, падчас якога вы даведаліся шмат карыснага аб функцыях Excel.
30-ы дзень марафону мы прысвяцім вывучэнню функцыі ускосна (INDIRECT), які вяртае спасылку, зададзеную тэкставым радком. З дапамогай гэтай функцыі вы можаце ствараць залежныя выпадальныя спісы. Напрыклад, пры выбары краіны з выпадальнага спісу вызначае, якія варыянты з'явяцца ў выпадальным спісе гарадоў.
Такім чынам, давайце больш падрабязна разгледзім тэарэтычную частку функцыі ускосна (УСКОСНЫ) і вывучыць практычныя прыклады яго прымянення. Калі ў вас ёсць дадатковая інфармацыя або прыклады, падзяліцеся імі ў каментарах.
Функцыя 30: УСКОСНАЯ
функцыя ускосна (INDIRECT) вяртае спасылку, зададзеную тэкставым радком.
Як вы можаце выкарыстоўваць функцыю INDIRECT?
Паколькі функцыя ускосна (INDIRECT) вяртае спасылку, зададзеную тэкставым радком, вы можаце выкарыстоўваць яго, каб:
- Стварыце пачатковую спасылку без зрушэння.
- Стварыце спасылку на статычны найменны дыяпазон.
- Стварыце спасылку, выкарыстоўваючы інфармацыю аркуша, радка і слупка.
- Стварыце незрухлівы масіў лікаў.
Сінтаксіс INDIRECT (УСКОСНЫ)
функцыя ускосна (INDIRECT) мае наступны сінтаксіс:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- спасылка_тэкст (link_to_cell) - гэта тэкст спасылкі.
- a1 – калі роўна TRUE (ІСЦІНА) або не вызначана, то будзе выкарыстоўвацца стыль спасылкі A1; а калі FALSE (ХЛУСНЯ), то і стыль R1C1.
Пасткі УСКОСНА (УСКОСНА)
- функцыя ускосна (INDIRECT) пералічваецца кожны раз, калі значэнні ў працоўным аркушы Excel змяняюцца. Гэта можа значна запаволіць вашу кнігу, калі функцыя выкарыстоўваецца ў многіх формулах.
- Калі функцыя ускосна (УСКОСНАЯ) стварае спасылку на іншую кнігу Excel, гэтая кніга павінна быць адкрыта, інакш формула паведаміць пра памылку #REF! (#СПАСЫЛКА!).
- Калі функцыя ускосна (УСКОСНАЯ) спасылаецца на дыяпазон, які перавышае ліміт радкоў і слупкоў, формула паведаміць пра памылку #REF! (#СПАСЫЛКА!).
- функцыя ускосна (INDIRECT) не можа спасылацца на дынамічны найменны дыяпазон.
Прыклад 1: Стварыце пачатковую спасылку без зрушэння
У першым прыкладзе слупкі C і E змяшчаюць аднолькавыя лічбы, іх сумы разлічваюцца з дапамогай функцыі SUM (SUM) таксама аднолькавыя. Аднак формулы трохі адрозніваюцца. У ячэйцы C8 формула:
=SUM(C2:C7)
=СУММ(C2:C7)
У ячэйцы E8 функцыя ускосна (УСКОСНАЯ) стварае спасылку на пачатковую ячэйку E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Калі вы ўставіце радок у верхняй частцы аркуша і дадасце значэнне за студзень (студзень), то сума ў слупку C не зменіцца. Формула зменіцца ў залежнасці ад дадання радка:
=SUM(C3:C8)
=СУММ(C3:C8)
Аднак функцыя ускосна (УСКОСНАЯ) фіксуе E2 у якасці пачатковай ячэйкі, таму студзень аўтаматычна ўключаецца ў разлік агульных вынікаў слупка E. Канцавая ячэйка змянілася, але пачатковая ячэйка не закранута.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Прыклад 2: Спасылка на статычны найменны дыяпазон
функцыя ускосна (INDIRECT) можа стварыць спасылку на найменны дыяпазон. У гэтым прыкладзе сінія вочкі складаюць дыяпазон NumList. Акрамя таго, дынамічны дыяпазон таксама ствараецца са значэнняў у слупку B NumListDyn, у залежнасці ад колькасці лічбаў у гэтым слупку.
Суму для абодвух дыяпазонаў можна вылічыць, проста даўшы яе назву ў якасці аргумента функцыі SUM (SUM), як вы можаце бачыць у ячэйках E3 і E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Замест таго, каб уводзіць назву дыяпазону ў функцыю SUM (SUM), вы можаце звярнуцца да імя, запісанага ў адной з вочак працоўнага ліста. Напрыклад, калі імя NumList запісваецца ў вочку D7, то формула ў вочку E7 будзе выглядаць так:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
На жаль функцыя ускосна (УСКОСНАЯ) не можа стварыць спасылку на дынамічны дыяпазон, таму пры капіраванні гэтай формулы ў ячэйку E8 вы атрымаеце памылку #REF! (#СПАСЫЛКА!).
Прыклад 3: Стварэнне спасылкі з выкарыстаннем інфармацыі аркуша, радка і слупка
Вы можаце лёгка стварыць спасылку на аснове нумароў радкоў і слупкоў, а таксама выкарыстоўваючы значэнне FALSE (ХЛУСНЯ) для другога аргумента функцыі ускосна (УСКОСНА). Так ствараецца спасылка стылю R1C1. У гэтым прыкладзе мы дадаткова дадалі назву аркуша да спасылкі – 'MyLinks'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
Прыклад 4: Стварыце масіў лікаў без зрушэння
Часам вам трэба выкарыстоўваць масіў лікаў у формулах Excel. У наступным прыкладзе мы хочам вылічыць сярэдняе 3 найбольшыя лічбы ў слупку B. Лікі можна ўвесці ў формулу, як гэта робіцца ў ячэйцы D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Калі вам патрэбен большы масіў, то вы наўрад ці захочаце ўводзіць усе лічбы ў формулу. Другі варыянт - выкарыстанне функцыі ROW (ROW), як зроблена ў формуле масіва, уведзенай у ячэйку D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
Трэці варыянт - выкарыстанне функцыі ROW (STRING) разам з ускосна (УСКОСНА), як зроблена з формулай масіва ў ячэйцы D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Вынік для ўсіх 3 формул будзе аднолькавым:
Аднак, калі радкі ўстаўлены ў верхняй частцы аркуша, другая формула верне няправільны вынік з-за таго, што спасылкі ў формуле зменяцца разам са зрухам радка. Цяпер замест сярэдняга трох найбольшых лікаў формула вяртае сярэдняе 3-га, 4-га і 5-га найбольшых лікаў.
Выкарыстанне функцый ускосна (УСКОСНАЯ), трэцяя формула захоўвае правільныя спасылкі на радкі і працягвае паказваць правільны вынік.