Таңдалған ұяшық бойынша диаграмма

Сізге және маған 2021 жылы әртүрлі елдер бойынша автокөлік сату мәндерімен келесі кестедегі деректерді визуализациялау керек делік (айтпақшы, осы жерден алынған нақты деректер):

Деректер серияларының (елдердің) саны көп болғандықтан, олардың барлығын бірден бір графикке салу әрекеті не қорқынышты «спагетти диаграммасына» әкеледі, не әр серия үшін бөлек диаграммаларды құруға әкеледі, бұл өте қиын.

Бұл мәселенің талғампаз шешімі диаграмманы тек ағымдағы жолдағы деректерге, яғни белсенді ұяшық орналасқан жолға салу болуы мүмкін:

Мұны жүзеге асыру өте оңай – сізге тек екі формула және 3 жолда бір кішкентай макрос қажет.

1-қадам. Ағымдағы жол нөмірі

Бізге қажет бірінші нәрсе - біздің белсенді ұяшымыз орналасқан парақтағы жол нөмірін есептейтін атаулы ауқым. Қойындыда ашу Формулалар – атау менеджері (Формулалар — атау менеджері), түймешігін басыңыз жасау (Жасау) және сол жерге келесі құрылымды енгізіңіз:

2-қадам. Тақырыпқа сілтеме

Таңдалған елді диаграмманың тақырыбы мен легендасында көрсету үшін бірінші бағаннан оның (ел) аты бар ұяшыққа сілтеме алуымыз керек. Ол үшін біз басқа жергілікті (яғни аудан = Кітап емес, ағымдағы парақ!) келесі формуласы бар аталған ауқым:

Мұнда INDEX функциясы берілген ауқымнан (біздің қол қоюшы елдер орналасқан А бағаны) біз бұрын анықтаған жол нөмірі бар ұяшықты таңдайды.

3-қадам. Деректерге сілтеме

Енді, осыған ұқсас жолмен, белсенді ұяшық қазір орналасқан ағымдағы жолдан барлық сату деректері бар ауқымға сілтеме алайық. Келесі формуламен басқа аталған ауқымды жасаңыз:

Мұнда нөлге тең үшінші аргумент INDEX бір мәнді емес, нәтиже ретінде бүкіл жолды қайтарады.

Қадам 4. Диаграммадағы сілтемелерді ауыстыру

Енді кесте тақырыбын және деректері (диапазоны) бар бірінші жолды таңдап, олардың негізінде диаграмманы құрастырыңыз Кірістіру – Диаграммалар (Кірістіру — Диаграммалар). Диаграммадағы деректері бар жолды таңдасаңыз, функция формула жолында көрсетіледі ҚАТАР (СЕРИЯ) Excel бағдарламасы бастапқы деректер мен белгілерге сілтеме жасау үшін кез келген диаграмманы жасау кезінде автоматты түрде пайдаланатын арнайы функция болып табылады:

Осы функциядағы бірінші (қолтаңба) және үшінші (деректер) аргументтерін 2 және 3-қадамдардағы ауқымдарымыздың атауларымен мұқият ауыстырайық:

Диаграмма ағымдағы жолдан сату деректерін көрсете бастайды.

5-қадам. Қайта есептеу макросы

Соңғы жанасу қалады. Microsoft Excel парақтағы деректер өзгерген кезде немесе пернені басқанда ғана формулаларды қайта есептейді F9, және біз қайта есептеудің таңдау өзгерген кезде, яғни белсенді ұяшықты парақта жылжытқанда болғанын қалаймыз. Ол үшін жұмыс кітабымызға қарапайым макрос қосу керек.

Деректер парағы қойындысын тінтуірдің оң жақ түймешігімен басып, пәрменді таңдаңыз қайнар көз (бастапқы код). Ашылған терезеде таңдауды өзгерту оқиғасы үшін макроөңдеуіш кодын енгізіңіз:

Сіз оңай елестете алатындай, белсенді ұяшықтың орны өзгерген сайын парақты қайта есептеуді іске қосады.

Қадам 6. Ағымдағы сызықты бөлектеу

Түсінікті болу үшін қазіргі уақытта диаграммада көрсетілген елді бөлектеу үшін шартты пішімдеу ережесін қосуға болады. Ол үшін кестені таңдап, таңдаңыз Басты — Шартты пішімдеу — Ереже жасау — Пішімдеу үшін ұяшықтарды анықтау үшін формуланы пайдаланыңыз (Басты бет — Шартты пішімдеу — Жаңа ереже — Қай ұяшықтарды пішімдеу керектігін анықтау үшін формуланы пайдаланыңыз):

Мұнда формула кестедегі әрбір ұяшық үшін оның жол нөмірі TekRow айнымалысында сақталған санға сәйкес келетінін тексереді және сәйкестік болса, таңдалған түспен толтыру іске қосылады.

Міне, қарапайым және әдемі, солай емес пе?

Ескертулер

• Үлкен кестелерде бұл сұлулықтың бәрі баяулауы мүмкін - шартты пішімдеу ресурсты көп қажет етеді және әрбір таңдау үшін қайта есептеу де ауыр болуы мүмкін.
• Кестенің үстінде немесе астында ұяшық кездейсоқ таңдалғанда деректердің диаграммада жоғалып кетуіне жол бермеу үшін пішіннің кірістірілген IF функцияларын пайдаланып TekRow атына қосымша құсбелгі қосуға болады:

  =ЕГЕР(ҰЯШЫҚ(“жол”)<4,ЕГЕР(ҰЯШЫҚ(“жол”)>4,ҰЯШЫҚ(“жол”)))
  

• Диаграммадағы көрсетілген бағандарды бөлектеу
• Excel-де интерактивті диаграмманы қалай құруға болады
• Координаттарды таңдау