Садржај
Претпоставимо да ви и ја треба да визуализујемо податке из следеће табеле са вредностима продаје аутомобила по различитим земљама у 2021. (успут, стварни подаци преузети одавде):
Пошто је број серија података (земаља) велики, покушај да их све угурате у један графикон ће или довести до ужасног „шпагети графикона“ или до прављења засебних графикона за сваку серију, што је веома гломазно.
Елегантно решење овог проблема може бити исцртавање графикона само на подацима из тренутног реда, односно реда у коме се налази активна ћелија:
Имплементација овога је веома лака – потребне су вам само две формуле и један мали макро у 3 реда.
Корак 1. Број тренутне линије
Прва ствар која нам је потребна је именовани опсег који израчунава број реда на листу где се сада налази наша активна ћелија. Отварање на картици Формуле – Менаџер имена (Формуле — Менаџер имена), кликните на дугме Створити (Креирај) и унесите следећу структуру тамо:
- Име – било које одговарајуће име за нашу променљиву (у нашем случају, ово је ТекСтринг)
- Област – у даљем тексту, потребно је да изаберете тренутни лист тако да креирани називи буду локални
- Домет – овде користимо функцију ЋЕЛИЈА (МОБИЛНИ), који може да изда гомилу различитих параметара за дату ћелију, укључујући и број линије који нам је потребан – за то је одговоран аргумент „лине“.
Корак 2. Веза до наслова
Да бисмо приказали изабрану земљу у наслову и легенди графикона, потребно је да из прве колоне добијемо референцу на ћелију са њеним називом (државе). Да бисмо то урадили, креирамо још један локални (тј Област = тренутни лист, а не књига!) именовани опсег са следећом формулом:
Овде функција ИНДЕКС бира из датог опсега (колона А, где се налазе наше земље потписнице) ћелију са бројем реда који смо претходно одредили.
Корак 3. Веза са подацима
Сада, на сличан начин, хајде да добијемо везу до опсега са свим подацима о продаји из тренутног реда, где се сада налази активна ћелија. Направите други именовани опсег са следећом формулом:
Овде, трећи аргумент, који је нула, узрокује да ИНДЕКС врати не једну вредност, већ цео ред као резултат.
Корак 4. Замена веза у графикону
Сада изаберите заглавље табеле и први ред са подацима (опсег) и направите графикон на основу њих користећи Уметнути – графикони (Инсерт — Цхартс). Ако изаберете ред са подацима у графикону, функција ће бити приказана у траци формуле РЕД (СЕРИЈА) је посебна функција коју Екцел аутоматски користи приликом креирања било ког графикона за упућивање на оригиналне податке и ознаке:
Хајде да пажљиво заменимо први (потпис) и трећи (подаци) аргумент у овој функцији са именима наших опсега из корака 2 и 3:
Графикон ће почети да приказује податке о продаји из тренутног реда.
Корак 5. Макро за поновно израчунавање
Последњи додир остаје. Мицрософт Екцел поново израчунава формуле само када се подаци на листу промене или када се притисне тастер F9, и желимо да се поновно израчунавање деси када се избор промени, односно када се активна ћелија помера преко листа. Да бисмо то урадили, морамо да додамо једноставан макро у нашу радну свеску.
Кликните десним тастером миша на картицу са подацима и изаберите команду извор (Изворни код). У прозору који се отвори унесите код макро руковаоца за догађај промене избора:
Као што можете лако замислити, све што ради је да покрене поновно израчунавање листа кад год се промени положај активне ћелије.
Корак 6. Истицање тренутне линије
Ради јасноће, можете додати и правило условног форматирања да бисте истакли земљу која је тренутно приказана на графикону. Да бисте то урадили, изаберите табелу и изаберите Почетна — Условно обликовање — Креирајте правило — Користите формулу за одређивање ћелија за форматирање (Почетна — Условно форматирање — Ново правило — Користите формулу да одредите које ћелије да форматирате):
Овде формула проверава за сваку ћелију у табели да ли се њен број реда поклапа са бројем ускладиштеним у променљивој ТекРов, и ако постоји подударање, тада се покреће попуњавање изабраном бојом.
То је то – једноставно и лепо, зар не?
белешке
- На великим табелама, сва ова лепота може да успори – условно форматирање је ствар која захтева ресурсе, а прерачунавање за сваки избор такође може бити тешко.
- Да бисте спречили да подаци нестану на графикону када је ћелија случајно изабрана изнад или испод табеле, можете додати додатну проверу имену ТекРов користећи угнежђене ИФ функције обрасца:
=ИФ(ЦЕЛЛ(„ред”)<4,ИФ(ЦЕЛЛ(„ред”)>4,ЦЕЛЛ(„ред”)))
- Истицање одређених колона у графикону
- Како направити интерактивни графикон у Екцелу
- Избор координата