Як створити діапазон динамічних діаграм в Excel

Цей підручник покаже, як створити діапазон динамічних діаграм у всіх версіях Excel: 2007, 2010, 2013, 2016 та 2022 роки.

За замовчуванням, коли ви розгортаєте або скорочуєте набір даних, що використовується для побудови діаграми в Excel, основні вихідні дані також потрібно коригувати вручну.

Однак, створюючи динамічні діапазони діаграм, можна уникнути цих неприємностей.

Діапазони динамічних діаграм дозволяють автоматично оновлювати вихідні дані кожного разу, коли ви додаєте або видаляєте значення з діапазону даних, заощаджуючи багато часу та зусиль.

У цьому уроці ви дізнаєтесь все, що вам потрібно знати, щоб розкрити силу Діапазони динамічних діаграм.

Діапазони динамічних діаграм - Вступ

Розглянемо такий набір зразків даних, що аналізує коливання норми прибутку:

В принципі, існує два способи налаштування діапазону динамічних діаграм:

1. Перетворення діапазону даних у таблицю
2. Використання динамічних іменованих діапазонів як вихідних даних діаграми.

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

Без зайвих слів, почнемо.

Табличний метод

Дозвольте мені почати з того, що я покажу вам найшвидший і найпростіший спосіб виконання поставленого завдання. Отже, ось вправа: Перетворіть діапазон даних у таблицю, і вам стане легше, ніж лушпиння гороху.

Таким чином, все, що ви вводите в комірки в кінці цієї таблиці, автоматично буде включено до вихідних даних діаграми.

Ось як це можна зробити за два простих кроки.

Крок №1: Перетворіть діапазон даних у таблицю.

Прямо за воротами, перетворіть діапазон комірок, що містить дані діаграми, у таблицю.

1. Виділити весь діапазон даних (А1: В6).
2. Натисніть на Вставити вкладка.
3. Натисніть кнопку "Таблиця”.

В Створити таблицю діалогове вікно, виконайте наведені нижче дії.

1. Двічі перевірте, чи виділений діапазон комірок відповідає всій таблиці даних.
2. Якщо у вашій таблиці немає рядка заголовка, зніміть прапорець біля "У моїй таблиці є заголовки”Коробка.
3. Натисніть «В ПОРЯДКУ.“

У підсумку ви повинні отримати таку таблицю:

Крок 2: Створіть діаграму на основі таблиці.

Основа закладена, а це означає, що тепер ви можете створити діаграму за допомогою таблиці.

1. Виділіть всю таблицю (А1: В6).
2. Перейдіть до Вставити вкладка.
3. Створіть будь-яку 2-D діаграму. Для ілюстрації створимо просту стовпчасту діаграму (Вставити стовпець або стовпчасту діаграму> Кластерна колонка).

Це воно! Щоб перевірити техніку, спробуйте додати нові точки даних внизу таблиці побачити їх автоматично нанесеними на сюжет. Наскільки це може бути простіше?

ПРИМІТКА: При такому підході набір даних повинен ніколи містить порожні клітинки-це зіпсує діаграму.

Метод динамічного іменованого діапазону

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

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

Крок №1: Створіть динамічні іменовані діапазони.

Для початку налаштуйте іменовані діапазони, які в кінцевому підсумку будуть використовуватися як вихідні дані для вашої майбутньої діаграми.

1. Перейдіть до Формули вкладка.
2. Натисніть «Менеджер імен.”
3. В Менеджер імен у діалоговому вікні, що з’явиться, виберіть «Новий.”

В Нова назва діалогове вікно, створіть абсолютно новий іменований діапазон:

1. Введіть "Квартал" поруч з "Назва”Поле. Для вашої зручності встановіть назву динамічного діапазону відповідно до відповідної комірки рядка заголовка стовпець А (A1).
2. В "Область застосування”, Виберіть поточний робочий аркуш. У нашому випадку це так Аркуш 1.
3. Введіть таку формулу в «Відноситься до”Поле: = OFFSET (Sheet1! $ A $ 2,0,0, COUNTA (Sheet1! $ A: $ A) -1,1)

Простою англійською мовою щоразу, коли ви змінюєте будь -яку клітинку на аркуші, функція OFFSET повертає лише фактичні значення у стовпець А, опускаючи комірку рядка заголовка (A1), тоді як функція COUNTA перераховує кількість значень у стовпці кожного разу, коли аркуш оновлюється-ефективно виконуючи всю брудну роботу за вас.

Давайте розберемо формулу більш детально, щоб допомогти вам зрозуміти, як вона працює:

ПРИМІТКА: Назва іменованого діапазону має починатися з літери або підкреслення та не містити пробілів.

З тієї ж точки зору, налаштуйте інший іменований діапазон на основі стовпець Марж прибутку (стовпець В) за допомогою цієї формули і позначте її "Profit_Margin”:

1	= OFFSET (Аркуш1! $ B $ 2,0,0, COUNTA (Аркуш1! $ B: $ B) -1,1)

Повторіть той самий процес, якщо ваша таблиця даних містить кілька стовпців з фактичними значеннями. У нашому випадку в результаті ви повинні мати два іменовані діапазони, готові до дії:

Крок 2: Створіть порожню діаграму.

Ми пройшли найскладнішу частину. Настав час налаштувати порожню діаграму, щоб ви могли вручну вставити в неї динамічні іменовані діапазони.

1. Виберіть будь -яку порожню клітинку на поточному аркуші (Аркуш 1).
2. Поверніться до Вставити вкладка.
3. Налаштуйте будь-яку 2-D діаграму, яку хочете. Для нашого прикладу ми створимо стовпчасту діаграму (Вставити стовпець або стовпчасту діаграму> Кластерна колонка).

Крок #3: Додайте іменований діапазон/діапазони, що містять фактичні значення.

Спочатку вставте іменований діапазон (Profit_Margin) пов'язані з фактичними значеннями (стовпець В) в діаграму.

Клацніть правою кнопкою миші на порожній діаграмі та виберіть "Виберіть Дані»З контекстного меню.

В Виберіть Джерело даних у діалоговому вікні натисніть «Додати.”

В Редагувати серію box, створіть новий ряд даних:

1. Під "Назва серії, ”Виділіть відповідну комірку рядка заголовка (В1).
2. Під "Серійні значення", Вкажіть іменований діапазон, який буде нанесено на діаграму, ввівши наступне:"= Аркуш1! Прибуток_Маржина.”Посилання складається з двох частин: імен поточного робочого аркуша (= Аркуш 1) та відповідний динамічний іменований діапазон (Profit_Margin). Знак оклику використовується для з’єднання двох змінних між собою.
3. Виберіть "В ПОРЯДКУ.”

Потрапивши туди, Excel автоматично відобразить значення:

Крок №4: Вставте названий діапазон з мітками осі.

Нарешті, замініть мітки осі категорії за замовчуванням на іменований діапазон, що складається з стовпець А (Квартал).

В Виберіть Джерело даних діалогове вікно в розділі "Мітки осі горизонтальної (категорії),"Виберіть"Редагувати”.

Потім вставте іменований діапазон у діаграму, ввівши таке посилання під "Діапазон міток осі:”

1	= Лист1! Квартал

Нарешті, стовпчаста діаграма на основі діапазону динамічних діаграм готова:

Перевірте це: діаграма оновлюється автоматично щоразу, коли ви додаєте або видаляєте дані в динамічному діапазоні.