змест
Калі вы хаця б знаёмыя з функцыяй ВПР (ВПР) (калі няма, то спачатку запусціце сюды), тады вы павінны разумець, што гэтая і іншыя падобныя ёй функцыі (VIEW, INDEX і SEARCH, SELECT і інш.) заўсёды даюць у выніку значэнне – нумар, тэкст або дата, якія мы шукаем у дадзенай табліцы.
Але што, калі замест значэння мы жадаем атрымаць жывую гіперспасылку, націснуўшы на якую, мы маглі б імгненна перайсці да знойдзенага супадзення ў іншай табліцы, каб паглядзець на яго ў агульным кантэксце?
Дапусцім, у нас ёсць вялікая табліца заказаў для нашых кліентаў у якасці ўваходных дадзеных. Для зручнасці (хоць гэта і не абавязкова) я пераўтварыў табліцу ў дынамічнае «разумнае» спалучэнне клавіш Ctrl+T і даў на таб Канструктар (Дызайн) яе імя укладкаЗаказы:
На асобным аркушы зводны Я пабудаваў зводную табліцу (хоць гэта не павінна быць менавіта зводная табліца - у прынцыпе падыдзе любая табліца), дзе па зыходных дадзеных разлічваецца дынаміка продажаў па месяцах для кожнага кліента:
Давайце дадамо слупок у табліцу заказаў з формулай, якая шукае імя кліента для бягучага заказу на аркушы зводны. Для гэтага мы выкарыстоўваем класічны набор функцый індэкс (ІНДЭКС) и БОЛЬШ ВЫКЛЮЧАНЫ (МАТЧ):
Зараз давайце згорнем нашу формулу ў функцыю КАЛІ (КЛЕТКА), якую мы папросім адлюстраваць адрас знойдзенай ячэйкі:
І, нарэшце, усё, што атрымалася, ставім у функцыі ГІПЕРСПЫЛКА (ГІПЕРСПЫЛКА), які ў Microsoft Excel можа стварыць жывую гіперспасылку на зададзены шлях (адрас). Адзінае, што невідавочна, дык гэта тое, што да атрыманага адрасу прыйдзецца прыляпіць хэш (#) у пачатку, каб спасылка карэктна ўспрымалася Excel як унутраная (з ліста на ліст):
Цяпер пры націску на любую са спасылак мы імгненна пераходзім да вочка з назвай кампаніі на лісце са зводнай табліцай.
Каб гэта было сапраўды добра, давайце трохі дапрацуем нашу формулу, каб пераход адбываўся не на імя кліента, а на канкрэтнае лікавае значэнне менавіта ў графе месяца, калі быў выкананы адпаведны заказ. Для гэтага трэба памятаць, што функцыя індэкс (ІНДЭКС) у Excel вельмі ўніверсальны і можа выкарыстоўвацца, сярод іншага, у фармаце:
=ІНДЭКС( XNUMXD_дыяпазон; Нумар_радка; Нумар_слупка )
Гэта значыць, у якасці першага аргументу мы можам паказаць у зводнай не слупок з назвамі кампаній, а ўсю вобласць дадзеных зводнай табліцы, а ў якасці трэцяга аргумента дадаць нумар патрэбнага нам слупка. Яго можна лёгка вылічыць з дапамогай функцыі МЕСЯЦ (МЕСЯЦ), які вяртае нумар месяца для даты здзелкі:
Паляпшэнне 2. Прыгожы сімвал спасылкі
Другі аргумент функцыі ГІПЕРСПЫЛКА – тэкст, які адлюстроўваецца ў ячэйцы са спасылкай – можна прыгажэць, калі замест банальных знакаў «>>» выкарыстоўваць нестандартныя сімвалы з шрыфтоў Windings, Webdings і таму падобнае. Для гэтага вы можаце выкарыстоўваць функцыю SYMBOL (ЗНАК), які можа адлюстроўваць сімвалы па іх коду.
Так, напрыклад, код сімвала 56 у шрыфце Webdings дасць нам добрую падвойную стрэлку для гіперспасылкі:
Паляпшэнне 3. Вылучыце бягучы радок і актыўную ячэйку
Ну а для канчатковай перамогі прыгажосці над здаровым сэнсам можна таксама далучыць да нашага файла спрошчаны варыянт вылучэння бягучага радка і ячэйкі, на якую мы пераходзім па спасылцы. Для гэтага спатрэбіцца просты макрас, які мы павесім для апрацоўкі падзеі змены выбару на аркушы зводны.
Для гэтага пстрыкніце правай кнопкай мышы на ўкладцы «Зводка» і абярыце каманду выгляд код (Прагляд код). Устаўце наступны код у акно рэдактара Visual Basic, якое адкрыецца:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.Interior.ColorIndex = -4142 Cells(ActiveCell.Row, 1).Resize(1, 14).Interior.ColorIndex = 6 ActiveCell.Interior.ColorIndex = 44 End Sub
Як вы лёгка заўважылі, тут мы спачатку выдаляем заліванне з усяго ліста, а затым заліваем увесь радок у зводцы жоўтым колерам (код колеру 6), а затым аранжавым (код 44) бягучай ячэйкай.
Цяпер пры выбары якой-небудзь ячэйкі ўнутры зводнай ячэйкі (не важна - уручную або ў выніку кліку па нашай гіперспасылцы) будзе выдзелена ўся радок і вочка з патрэбным нам месяцам:
Прыгажосць 🙂
PS Толькі не забудзьце захаваць файл у фармаце з падтрымкай макрасаў (xlsm або xlsb).
- Стварэнне знешніх і ўнутраных спасылак з дапамогай функцыі HYPERLINK
- Стварэнне лістоў з дапамогай функцыі HYPERLINK