Дыяграма па выбранай ячэйцы

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

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

Элегантным рашэннем гэтай праблемы можа быць пабудова дыяграмы толькі на дадзеных з бягучага радка, гэта значыць радка, дзе знаходзіцца актыўная ячэйка:

Рэалізаваць гэта вельмі проста - вам патрэбныя толькі дзве формулы і адзін маленькі макрас у 3 радках.

Крок 1. Нумар бягучага радка

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

Крок 2. Спасылка на назву

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

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

Крок 3. Спасылка на дадзеныя

Цяпер падобным чынам атрымаем спасылку на дыяпазон з усімі дадзенымі аб продажах з бягучага радка, дзе цяпер знаходзіцца актыўная вочка. Стварыце іншы найменны дыяпазон з дапамогай наступнай формулы:

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

Крок 4. Падстаноўка спасылак у дыяграме

Цяпер вылучыце загаловак табліцы і першы радок з дадзенымі (дыяпазон) і пабудуйце на іх аснове дыяграму Устаўка – дыяграмы (Устаўка — Дыяграмы). Калі вы вылучыце радок з дадзенымі ў дыяграме, то функцыя будзе адлюстроўвацца ў радку формул ROW (СЕРЫЯ) гэта спецыяльная функцыя, якую Excel аўтаматычна выкарыстоўвае пры стварэнні любой дыяграмы для спасылкі на зыходныя дадзеныя і цэтлікі:

Давайце акуратна заменім першы (подпіс) і трэці (дадзеныя) аргументы ў гэтай функцыі імёнамі нашых дыяпазонаў з крокаў 2 і 3:

Дыяграма пачне адлюстроўваць дадзеныя аб продажах з бягучага радка.

Крок 5. Макрас пераразліку

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

Пстрыкніце правай кнопкай мышы на ўкладцы табліцы дадзеных і абярыце каманду крыніца (Зыходны код). У якое адкрылася акне ўвядзіце код макраапрацоўшчыка падзеі змены выбару:

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

Крок 6. Вылучэнне бягучага радка

Для нагляднасці вы таксама можаце дадаць правіла ўмоўнага фарматавання, каб вылучыць краіну, якая зараз адлюстроўваецца на дыяграме. Для гэтага вылучыце табліцу і абярыце Дадому — Умоўнае фарматаванне — Стварыць правіла — Выкарыстоўваць формулу для вызначэння вочак для фарматавання (Галоўная — Умоўнае фарматаванне — Новае правіла — Выкарыстоўвайце формулу, каб вызначыць, якія ячэйкі фарматаваць):

Тут формула правярае для кожнай ячэйкі ў табліцы, што яе нумар радка супадае з лікам, захаваным у зменнай TekRow, і калі супадзенне ёсць, то запускаецца заліванне абраным колерам.

Вось так - проста і прыгожа, праўда?

нататкі

• На вялікіх сталах уся гэтая прыгажосць можа тармазіць - умоўнае фарматаванне - рэсурсаёмістая рэч, і пераразлік для кожнага выбару таксама можа быць цяжкім.
• Каб прадухіліць знікненне даных на дыяграме, калі ячэйка выпадкова выбрана над або пад табліцай, вы можаце дадаць дадатковую праверку да назвы TekRow з дапамогай укладзеных функцый IF формы:

  =КАЛІ(ЯЧЭЙКА(“радок”)4,ЯЧЭЙКА(“радок”)))
  

• Вылучэнне вызначаных слупкоў у дыяграме
• Як стварыць інтэрактыўную дыяграму ў Excel
• Выбар каардынатаў