В тази статия ще ви покажа как може да добавите скрол бар (scroll bar или на български: плъзгач) към графика за по-удобно и динамично представяне на данните в нея. Това обикновено се налага, когато данните са толкова много, че когато ги представим в една графика, тя става много сгъстена и се възприема трудно.
Казусът
Имаме таблица с най-високите върхове на планините в България
и искаме да я представим с графика.
Графиката изглежда ефектно, но данните се разчитат много трудно. Като за начало, понеже стълбовете са твърде много, Ексел пропуска да изпише част от върховете на хоризонталната ос, защото просто няма място за тях. Вижте как между Мусала в Рила и Ботев в Стара Планина липсва Вихрен в Пирин. И така е до края – всеки втори връх не е изписан.
Освен това можете ли бързо и лесно да видите колко е височината на първенеца на Верила Голям Дебелец?
Дори и да добавим числата на височините до стълбовете, това пак не помага особено. Аз лично няколко пъти връщах мишката напред назад по стълбчето, за да се уверя, че височината на Голям Дебелец е 1,415 метра.
Ясно е, че стандартна графика в Ексел не е удобна. Какво може да направим, за да се възприемат данните по-добре? Едно възможно решение е да направим графиката да показва по-малък брой стълбове, например 10, и с един скрол бар да се „движим“ нагоре-надолу. Така във всеки един момент ще виждаме обозрим брой стълбове, а ако трябва да видим всички, просто ще скролваме надолу и нагоре с плъзгача.
Добавяне на скрол бар
Добавявено на плъзгач става от таб Developer в лентата с бутони.
Ако не го виждате, трябва да направите следното:
- Идете на File > Options и там изберете Customize Ribbon
- Сложете отметка на Developer
След като Developer е вече наличен в лентата за бутони, можем да отидем на нов шийт и там да кликнем на Developer > Control > Insert и за изберем scroll bar.
След това кликаме с ляв бутон на мишката някъде в шийта, задържаме левия бутон натиснат и влачим надолу. Появява се един правоъгълник.
Това е бъдещият скрол бар. С влаченето му задаваме необходимите размери. И когато пуснем бутона на мишката, скрол барът придобива вид.
Сега трябва да го настроим. За целта – десен клик и избираме Format Control
Появява се меню, в което най-важен е разделът Control. Там се задават различните настройки за „поведението“ на нашия скрол бар.
Ето какво означават те отдолу нагоре:
Cell link: Тук се посочва клетката, която се контролира от скрол бара. Когато движим плъзгача нагоре и надолу стойността в тази клетка расте или намалява. Както виждте за такава клетка е избрана $A$1
Page change: С колко единици нараства или намалява контролираната клетка ($A$1), когато кликаме между стрелките на скрол бара и плъзгача. В случая 10
Incremental change: С колко единици нараства или намалява контролираната клетка, когато натиснем стрелките. В случая – 1
Maximum value: Максималната стойност на контролираната клетка. Тук е логично да бъде общия брой редове в таблицата с данни. В нашия пример: 30, което е равно на броя на планините (40) минус максималната стъпка на промяна, която можем да имаме, чрез Page change, т.е. 10.
Minimum value: Минималната стойност на контролираната клетка. В нашия случай: 1
Current value: тук се отбелязва стойноста в момента на клетката.
След като сме настроили нашия скрол бар вече е време да пристъпим към
Подготовка на данните за графиката
Идеята е да създадем такава формула, която да извиква от данните определен брой редове (например 10). А кои точно да са тези редове да се определя от контролираната от скрол бара клетка ($A$1). Идеалната фунция за целта е INDEX – тя връща стойност от дадена колона, която се намира на определен номер ред.
Като знаем как работи INDEX, можем да добавим една помощна колона No с числата от 1 до 10 (или до колкото искаме да е броят на стойностите, които да показваме в графиката). И да напишем следната формула:
Искаме от Ексел да отиде в таблицата с данните в Sheet1 и там в колоната B2:B41, където са имената на планините и най-високите им върхове. След това да намери реда, който е равен на сбора от контролираната клетка и номера на реда във втората ни таблица. Когато копираме формулата надолу до ред номер 10, тя връща планините и върховете в първите десет реда. Абсолютно същото правим и за височините на върховете, само че там вече гледаме в колона C2:C41
И сега, нека да преместим плъзгача няколко стойности да видим какво ще се случи:
При стойност на контролираната клетка 20, на първия ред вече е Верила с нейния първенец Голям Дебелец, който както споменахме е висок цели 1,415 метра. Надолу са следващите 9 планини и върхове. И сега вече сме готови да си направим желаната графика
Създаване на графиката
Със съвсем стандартни действия по създаване и форматиране на графика, стигаме до това:
Какви са тези действия може да прочетете в тази статия.
Но нашата графика има един бъг, който проличава на долната картинка:
Виждате как стълбчето на Големи връх в Ерулска планина е с различен размер.
Това е защото Ексел автоматично преоразмерява стойностите на хоризонталната ос спрямо най-голямата и най-малката височини на върхове, които се визуализират с графиката.
За да го оправим, трябва първо да покажем хоризонталната ос от Design > Add chart element > Axes > Primary Horizontal.
След това кликаме върху нея и отиваме на Axis Options, където ръчно променяме максимумът да не е автоматичен, а с фиксирана стойност от 3,000 (малко по-висока от стойността на най-високия връх) или колкото е необходимо.
И сега вече всичко е наред
Бонус съвет
Графиката се получи чудесно, но е хубаво да имаме някаква база за сравнение, с която да поставяме данните в един и същи контекст независимо колко нагоре или надолу сме се плъзнали със скрол бара.
За целта можем да фиксираме формулата на първия ред винаги да ни връща само и единствено Мусала и неговата височина. Трябва просто да сложим 1 във втория параметър на INDEX.
По този начин в каквато и позиция да сме, Мусала винаги ще е най-отгоре. Освен това можем да кликнем два пъти върху стълбчето на Мусала и да го оцветим в някакъв друг цвят, за да се откроява визуално
Може да свалите файла от тук