Аналіз УСКОСНАЙ функцыі на прыкладах

На першы погляд (асабліва пры чытанні даведкі) функцыя ускосна (УСКАСНЫ) выглядае проста і нават непатрэбна. Яе сутнасць заключаецца ў пераўтварэнні тэксту, які выглядае як спасылка, у паўнавартасную спасылку. Тыя. калі нам трэба звярнуцца да ячэйкі A1, то мы можам альбо звычайна зрабіць прамую спасылку (увядзіце знак роўнасці ў D1, націсніце на A1 і націсніце Enter), альбо мы можам выкарыстоўваць ускосна з той жа мэтай:

Калі ласка, звярніце ўвагу, што аргумент функцыі - спасылка на A1 - уводзіцца ў двукоссе, так што, па сутнасці, гэта тэкст.

«Ну добра», - скажаце вы. «А якая карысць?» 

Але не судзіце па першым уражанні - яно зманлівае. Гэтая функцыя можа дапамагчы вам у многіх сітуацыях.

Прыклад 1. Транспанаваць

Класіка жанру: трэба круціць вертыкальны дыя

баразёнкі ў гарызантальную (транспанаваць). Вядома, вы можаце выкарыстоўваць спецыяльную ўстаўку або функцыю ТРАНСП (ТРАНСПАНІРАВАЦЬ) у формуле масіва, але вы можаце абыйсціся і нашай ускосна:

Логіка простая: каб атрымаць адрас наступнай ячэйкі, мы склейваем літару «А» са спецыяльным сімвалам «&» і нумар слупка бягучай ячэйкі, які выдае нам функцыя калонка (КАЛОНКА).

Зваротную працэдуру лепш зрабіць крыху інакш. Так як на гэты раз нам неабходна сфармаваць спасылку на ячэйкі B2, C2, D2 і г.д., то зручней выкарыстоўваць рэжым спасылкі R1C1 замест класічнага «марскога бою». У гэтым рэжыме нашы вочкі будуць адрознівацца толькі нумарам слупка: B2=R1C2, C2=R1C3, D2=R1C4 і г.д.

Тут з'яўляецца другі неабавязковы аргумент функцыі. ускосна. Калі роўна ХЛУСНІЦЬ (ХЛУСНЯ), то вы можаце ўсталяваць адрас спасылкі ў рэжыме R1C1. Такім чынам, мы можам лёгка перанесці гарызантальны дыяпазон назад у вертыкальны:

Прыклад 2. Сумаванне па прамежку

Мы ўжо разбіралі адзін спосаб сумавання па акне (дыяпазону) зададзенага памеру на аркушы з дапамогай функцыі ВЫКАРЫСТАННЕ (ЗРУШЭННЕ). Падобную задачу таксама можна вырашыць з дапамогай ускосна. Калі нам трэба абагульніць дадзеныя толькі з пэўнага дыяпазону-перыяду, то мы можам склеіць іх з частак, а потым ператварыць у паўнавартасную спасылку, якую можна ўставіць унутр функцыі SUM (СУМ):

Прыклад 3. Выпадальны спіс разумнай табліцы

Часам Microsoft Excel не разглядае імёны разумных табліц і слупкі як поўныя спасылкі. Так, напрыклад, пры спробе стварыць выпадальны спіс (таб Дадзеныя – Праверка даных) на аснове слупка Супрацоўнікі з разумнай табліцы Людзі мы атрымаем памылку:

Калі мы «абгарнем» спасылку нашай функцыяй ускосна, то Excel лёгка прыме яго, і наш выпадальны спіс будзе дынамічна абнаўляцца пры даданні новых супрацоўнікаў у канец разумнай табліцы:

Прыклад 4. Неразрыўныя сувязі

Як вядома, Excel аўтаматычна выпраўляе спасылкі ў формулах пры ўстаўцы або выдаленні радкоў-слупкоў на аркушы. У большасці выпадкаў гэта правільна і зручна, але не заўсёды. Дапусцім, нам трэба перанесці імёны з даведніка супрацоўнікаў у справаздачу:

Калі ставіць звычайныя спасылкі (у першай зялёнай ячэйцы ўвесці =B2 і скапіяваць уніз), то пры выдаленні, напрыклад, Дашы, мы атрымаем #СПАСЫЛКУ! памылка ў адпаведнай ёй зялёнай клетцы. (#СПАСЫЛКА!). У выпадку выкарыстання функцыі стварэння спасылак ускосна такой праблемы не будзе.

Прыклад 5: Збор дадзеных з некалькіх аркушаў

Дапусцім, у нас ёсць 5 лістоў з аднатыпнымі справаздачамі ад розных супрацоўнікаў (Міхаіла, Алены, Івана, Сяргея, Дзмітрыя):

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

Вы можаце збіраць дадзеныя з усіх лістоў (не сумаваць, а складаць адзін пад адным у «кучу») па адной формуле:

Як бачыце, ідэя тая ж: прыляпляем спасылку на патрэбную вочка дадзенага ліста, і ускосна ператварае яго ў "жывы". Для зручнасці над табліцай я дапісаў літары слупкоў (B,C,D), а справа – нумары радкоў, якія трэба ўзяць з кожнага аркуша.

Пасткі

Калі вы карыстаецеся ускосна (УСКАСНЫ) трэба памятаць пра яго слабыя бакі:

• Калі вы спасылаецеся на іншы файл (шляхам склейвання назвы файла ў квадратных дужках, назвы аркуша і адраса ячэйкі), то гэта працуе, толькі калі зыходны файл адкрыты. Калі мы закрыем яго, мы атрымаем памылку #LINK!
• INDIRECT не можа спасылацца на дынамічны найменны дыяпазон. На статыцы - без праблем.
• INDIRECT - гэта зменлівая або «нестабільная» функцыя, г.зн. яна пералічваецца для любых змяненняў у любой ячэйцы аркуша, а не толькі для ўплыву на ячэйкі, як у звычайных функцыях. Гэта дрэнна адбіваецца на прадукцыйнасці і вялікімі табліцамі INDIRECT лепш не захапляцца.

• Як стварыць дынамічны дыяпазон з дапамогай аўтаматычнай змены памеру
• Сумаванне па акне дыяпазону на аркушы з функцыяй OFFSET