Съдържание
Да предположим, че вие и аз трябва да визуализираме данни от следната таблица със стойности на продажбите на автомобили по различни страни през 2021 г. (между другото реални данни са взети от тук):
Тъй като броят на сериите от данни (държави) е голям, опитът да се натъпчат всички в една графика наведнъж или ще доведе до ужасна „спагети диаграма“, или до изграждане на отделни диаграми за всяка серия, което е много тромаво.
Елегантно решение на този проблем може да бъде да се начертае диаграма само върху данните от текущия ред, т.е. редът, в който се намира активната клетка:
Прилагането на това е много лесно – имате нужда само от две формули и един малък макрос в 3 реда.
Стъпка 1. Текущ номер на ред
Първото нещо, от което се нуждаем, е наименуван диапазон, който изчислява номера на реда на листа, където сега се намира нашата активна клетка. Отваряне в раздел Формули – Мениджър на имена (Формули — Мениджър на имена), щракнете върху бутона Създаване на (Създаване) и въведете следната структура там:
- Име – всяко подходящо име за нашата променлива (в нашия случай това е TekString)
- Район – по-нататък трябва да изберете текущия лист, така че създадените имена да са локални
- Обхват – тук използваме функцията CELL (КЛЕТКА), който може да издаде куп различни параметри за дадена клетка, включително номера на реда, от който се нуждаем – за това отговаря аргументът „line“.
Стъпка 2. Линк към заглавието
За да покажем избраната държава в заглавието и легендата на диаграмата, трябва да получим препратка към клетката с нейното име (държава) от първата колона. За да направим това, създаваме друг локал (т.е Район = текущ лист, а не книга!) наименуван диапазон със следната формула:
Тук функцията INDEX избира от даден диапазон (колона A, където се намират подписалите ни държави) клетка с номера на реда, който сме определили преди това.
Стъпка 3. Връзка към данни
Сега, по подобен начин, нека да получим връзка към диапазон с всички данни за продажби от текущия ред, където сега се намира активната клетка. Създайте друг именуван диапазон със следната формула:
Тук третият аргумент, който е нула, кара INDEX да върне не една стойност, а целия ред като резултат.
Стъпка 4. Замяна на връзки в диаграмата
Сега изберете заглавката на таблицата и първия ред с данни (обхват) и изградете диаграма въз основа на тях, като използвате Вмъкване – Графики (Вмъкване — Графики). Ако изберете ред с данни в диаграмата, тогава функцията ще се покаже в лентата с формули РЕД (СЕРИИ) е специална функция, която Excel автоматично използва, когато създава диаграма, за да се позовава на оригиналните данни и етикети:
Нека внимателно заменим първия (подпис) и третия (данни) аргументи в тази функция с имената на нашите диапазони от стъпки 2 и 3:
Диаграмата ще започне да показва данни за продажбите от текущия ред.
Стъпка 5. Макрос за преизчисляване
Остава последният щрих. Microsoft Excel преизчислява формулите само когато данните в листа се променят или когато се натисне клавиш F9, и искаме преизчисляването да се извърши, когато селекцията се промени, т.е. когато активната клетка се премести през листа. За да направим това, трябва да добавим прост макрос към нашата работна книга.
Щракнете с десния бутон върху раздела на листа с данни и изберете командата източник (Програмен код). В прозореца, който се отваря, въведете кода на макро манипулатора за събитието за промяна на селекцията:
Както лесно можете да си представите, всичко, което прави, е да задейства преизчисляване на лист всеки път, когато позицията на активната клетка се промени.
Стъпка 6. Маркиране на текущия ред
За по-голяма яснота можете също да добавите правило за условно форматиране, за да подчертаете страната, която в момента се показва на диаграмата. За да направите това, изберете таблицата и изберете Начало — Условно форматиране — Създаване на правило — Използване на формула за определяне на клетки за форматиране (Начало — Условно форматиране — Ново правило — Използвайте формула, за да определите кои клетки да форматирате):
Тук формулата проверява за всяка клетка в таблицата дали нейният номер на ред съвпада с номера, съхранен в променливата TekRow, и ако има съвпадение, тогава се задейства запълването с избрания цвят.
Това е – просто и красиво, нали?
бележки
- На големи маси цялата тази красота може да се забави – условното форматиране е ресурсоемко нещо и преизчисляването за всяка селекция също може да бъде тежко.
- За да предотвратите изчезването на данни в диаграмата, когато клетка е избрана случайно над или под таблицата, можете да добавите допълнителна проверка към името на TekRow, като използвате вложени IF функции на формуляра:
=IF(CELL(“ред”)<4,IF(CELL("ред")>4,CELL(“ред”)))
- Маркиране на определени колони в диаграма
- Как да създадете интерактивна диаграма в Excel
- Избор на координати