Діаграма по виділеній комірці

Припустимо, що нам з вами потрібно візуалізувати дані з наступної таблиці зі значеннями продажів автомобілів у різних країнах у 2021 році (реальні дані, до речі, взяті звідси):

Оскільки кількість серій даних (країн) велика, спроба втиснути їх усі в один графік одночасно призведе або до жахливої ​​«спагетті-діаграми», або до побудови окремих діаграм для кожної серії, що дуже громіздко.

Елегантним вирішенням цієї проблеми може бути побудова діаграми тільки на даних поточного рядка, тобто рядка, де знаходиться активна комірка:

Реалізувати це дуже просто – вам потрібні лише дві формули та один маленький макрос у 3 рядки.

Крок 1. Номер поточного рядка

Перше, що нам потрібно, це іменований діапазон, який обчислює номер рядка на аркуші, де зараз розташована наша активна клітинка. Відкриття на вкладці Формули – менеджер імен (Формули — Менеджер імен), натисніть на кнопку Створювати (Створити) і введіть туди таку структуру:

Крок 2. Посилання на назву

Для відображення вибраної країни в заголовку та легенді діаграми нам потрібно отримати посилання на комірку з її назвою (країни) з першого стовпця. Для цього ми створюємо інший локальний (тобто Область = поточний аркуш, а не книга!) іменований діапазон із такою формулою:

Тут функція INDEX вибирає з заданого діапазону (стовпець A, де знаходяться наші підписані країни) клітинку з номером рядка, який ми визначили раніше.

Крок 3. Посилання на дані

Тепер подібним чином отримаємо посилання на діапазон з усіма даними про продажі з поточного рядка, де зараз знаходиться активна комірка. Створіть інший іменований діапазон за такою формулою:

Тут третій аргумент, який дорівнює нулю, змушує INDEX повертати не одне значення, а весь рядок як результат.

Крок 4. Підстановка посилань у діаграмі

Тепер виберіть заголовок таблиці та перший рядок з даними (діапазон) і побудуйте на їх основі діаграму Вставка – діаграми (Вставка — діаграми). Якщо вибрати рядок з даними на діаграмі, то функція буде відображена в рядку формул ROW (СЕРІЯ) це спеціальна функція, яку Excel автоматично використовує під час створення будь-якої діаграми для посилання на вихідні дані та мітки:

Давайте обережно замінимо перший (підпис) і третій (дані) аргументи в цій функції іменами наших діапазонів з кроків 2 і 3:

Діаграма почне відображати дані про продажі з поточного рядка.

Крок 5. Макрос перерахунку

Залишився останній штрих. Microsoft Excel перераховує формули тільки при зміні даних на аркуші або при натисканні клавіші F9, і ми хочемо, щоб перерахунок відбувався, коли змінюється виділення, тобто коли активну комірку переміщують по аркушу. Для цього нам потрібно додати простий макрос до нашої книги.

Клацніть правою кнопкою миші вкладку таблиці даних і виберіть команду Source (Вихідний код). У вікні, що відкриється, введіть код макро-обробника події зміни вибору:

Як ви можете легко собі уявити, усе, що він робить, це запускає перерахунок аркуша щоразу, коли змінюється позиція активної комірки.

Крок 6. Виділення поточного рядка

Для ясності ви також можете додати правило умовного форматування, щоб виділити країну, яка зараз відображається на діаграмі. Для цього виділіть таблицю і виберіть Головна — Умовне форматування — Створити правило — Використовувати формулу для визначення клітинок для форматування (Домашня сторінка — Умовне форматування — Нове правило — Використовуйте формулу, щоб визначити, які клітинки потрібно відформатувати):

Тут формула перевіряє кожну комірку в таблиці на відповідність номера її рядка числу, збереженому в змінній TekRow, і якщо відповідність є, запускається заливка вибраним кольором.

Ось так – просто і красиво, чи не так?

примітки

• На великих таблицях уся ця краса може сповільнюватися – умовне форматування є ресурсомісткою річчю, і перерахунок для кожного виділення також може бути важким.
• Щоб запобігти зникненню даних на діаграмі, коли комірку випадково вибрано над або під таблицею, ви можете додати додаткову перевірку до імені TekRow за допомогою вкладених функцій IF форми:

  =IF(CELL(“рядок”)<4;IF(CELL("рядок")>4,CELL(“рядок”)))
  

• Виділення вказаних стовпців у діаграмі
• Як створити інтерактивну діаграму в Excel
• Вибір координат