Тандалган уяча боюнча диаграмма

Сиз жана мен 2021-жылы ар кайсы өлкөлөр боюнча унаа сатуунун баалуулуктары менен төмөнкү таблицадагы маалыматтарды визуализациялообуз керек дейли (чыныгы маалыматтар бул жерден алынган):

Маалымат серияларынын (өлкөлөрдүн) саны көп болгондуктан, алардын баарын бир эле графикке жыйноо аракети же коркунучтуу “спагетти диаграммасына” алып келет, же ар бир серия үчүн өзүнчө диаграммаларды түзүүгө алып келет, бул абдан түйшүктүү.

Диаграмманы учурдагы катардагы маалыматтарга, башкача айтканда активдүү уяча жайгашкан сапка гана түзүү бул маселенин көрктүү чечими болушу мүмкүн:

Муну ишке ашыруу абдан оңой – сизге эки формула жана 3 сапта бир кичинекей макро керек.

1-кадам. Учурдагы саптын номери

Бизге керек болгон биринчи нерсе - бул биздин активдүү уячабыз жайгашкан барактагы катар номерин эсептеген аталган диапазон. Өтмөктө ачуу Формулалар – ысым менеджери (Формулалар - Аты-жөнү менеджери), баскычын басыңыз түзүү (Түзүү) жана ал жерде төмөнкү структураны киргизиңиз:

2-кадам. Титулга шилтеме

Диаграмманын аталышында жана легендасында тандалган өлкөнү көрсөтүү үчүн биринчи тилкеден анын (өлкөнүн) аты жазылган уячага шилтемени алышыбыз керек. Бул үчүн биз башка жергиликтүү түзөбүз (б.а Аянт = учурдагы баракча, Китеп эмес!) төмөнкү формула менен аталган диапазон:

Бул жерде INDEX функциясы берилген диапазондон (биздин кол койгон өлкөлөр жайгашкан А тилкеси) биз мурда аныктаган катар номери бар уячаны тандайт.

3-кадам. Маалыматтарга шилтеме

Эми, ушундай эле жол менен, азыр активдүү уяча жайгашкан учурдагы саптан бардык сатуу маалыматтары менен диапазонго шилтеме алалы. Төмөнкү формула менен башка аталган диапазонду түзүңүз:

Бул жерде нөл болгон үчүнчү аргумент INDEX бир гана маанини эмес, натыйжада бүт сапты кайтарып берет.

Кадам 4. Диаграммадагы шилтемелерди алмаштыруу

Эми таблицанын башын жана маалыматтары бар биринчи сапты (диапазон) тандап, алардын негизинде диаграмма түзүңүз Кыстаруу – Диаграммалар (Киргизүү — Диаграммалар). Эгерде сиз диаграммада маалыматтары бар сапты тандасаңыз, анда функция формула тилкесинде көрсөтүлөт ROW (СЕРИЯ) баштапкы маалыматтарга жана энбелгилерге шилтеме кылуу үчүн кандайдыр бир диаграмманы түзүүдө Excel автоматтык түрдө колдонгон өзгөчө функция:

Келгиле, бул функциядагы биринчи (кол) жана үчүнчү (маалыматтар) аргументтерин 2 жана 3-кадамдардагы диапазондорубуздун аталыштары менен кылдаттык менен алмаштыралы:

Диаграмма учурдагы саптан сатуу маалыматтарын көрсөтө баштайт.

5-кадам. Кайра эсептөө макро

Акыркы тийүү калды. Microsoft Excel баракчадагы маалыматтар өзгөргөндө же баскыч басылганда гана формулаларды кайра эсептейт F9, жана биз кайра эсептөө тандоо өзгөргөндө, башкача айтканда, активдүү уяча баракта жылдырганда болушун каалайбыз. Бул үчүн биз иш китебибизге жөнөкөй макросту кошуубуз керек.

Маалымат баракчасынын өтмөгүндө оң баскычты чыкылдатып, буйрукту тандаңыз булак (Булак коду). Ачылган терезеде, тандоону өзгөртүү окуясы үчүн макро иштетүүчүнүн кодун киргизиңиз:

Сиз оңой элестете тургандай, мунун баары жигердүү уячанын абалы өзгөргөн сайын баракты кайра эсептөөгө түрткү берет.

Кадам 6. Учурдагы линияны бөлүп көрсөтүү

Түшүнүктүү болушу үчүн, сиз учурда диаграммада көрсөтүлгөн өлкөнү бөлүп көрсөтүү үчүн шарттуу форматтоо эрежесин кошо аласыз. Бул үчүн, таблицаны тандап, тандаңыз Негизги бет — Шарттуу форматтоо — Эреже түзүү — Форматталуучу уячаларды аныктоо үчүн формуланы колдонуңуз (Башкы бет — Шарттуу форматтоо — Жаңы эреже — Кайсы уячаларды форматтоо үчүн формуланы колдонуңуз):

Бул жерде формула таблицадагы ар бир уяча үчүн анын сап номери TekRow өзгөрмөсүндө сакталган санга дал келерин текшерет жана дал келүү бар болсо, анда тандалган түс менен толтуруу ишке киргизилет.

Мына ушундай – жөнөкөй жана сулуу, туурабы?

жазуулар

• Чоң столдордо мунун баары сулуулук жайлатышы мүмкүн - шарттуу форматтоо ресурсту көп талап кылган нерсе жана ар бир тандоо үчүн кайра эсептөө да оор болушу мүмкүн.
• Таблицадан жогору же ылдый кокусунан уяча тандалып алынганда, маалыматтардын диаграммада жоголуп кетишине жол бербөө үчүн, форманын ички IF функцияларын колдонуп, TekRow атына кошумча чекти кошо аласыз:

  =ЭГЕР(CELL("сап")<4,ЭГЕР(CELL("сап")>4,CELL("сап")))
  

• Диаграммадагы көрсөтүлгөн тилкелерди бөлүп көрсөтүү
• Excelде интерактивдүү диаграмманы кантип түзүүгө болот
• Координатты тандоо