Коли ви створюєте діаграму в Excel і вихідні дані змінюються, вам потрібно оновити джерело даних діаграми, щоб переконатися, що воно відображає нові дані.
Якщо ви працюєте з діаграмами, які часто оновлюються, краще створити динамічний діапазон діаграм.
Що таке діапазон динамічних діаграм?
Діапазон динамічних діаграм - це діапазон даних, який автоматично оновлюється при зміні джерела даних.
Цей динамічний діапазон потім використовується як вихідні дані на діаграмі. У міру зміни даних динамічний діапазон миттєво оновлюється, що призводить до оновлення діаграми.
Нижче наведено приклад діаграми, яка використовує діапазон динамічних діаграм.
Зауважте, що діаграма оновлюється новими даними даних за травень та червень, як тільки введені дані.
Як створити діапазон динамічних діаграм у Excel?
Існує два способи створення діапазону динамічних діаграм у Excel:
- Використання таблиці Excel
- Використання формул
У більшості випадків використання таблиці Excel є найкращим способом створення динамічних діапазонів у програмі Excel.
Давайте подивимося, як працює кожен із цих методів.
Натисніть тут, щоб завантажити файл прикладу.
Використання таблиці Excel
Використання таблиці Excel - найкращий спосіб створення динамічних діапазонів, оскільки вона автоматично оновлюється, коли до неї додається нова точка даних.
Функція Таблиця Excel була представлена у версії Windows 2007 для Excel 2007, і якщо у вас є версії до неї, ви не зможете її використовувати (див. Наступний розділ про створення діапазону динамічних діаграм за допомогою формул).
Професійна порада: Щоб перетворити діапазон клітинок у таблицю Excel, виділіть клітинки та скористайтесь комбінацією клавіш - Control + T (утримуйте клавішу Control і натисніть клавішу T).У наведеному нижче прикладі ви можете побачити, що як тільки я додаю нові дані, таблиця Excel розширюється, включаючи ці дані як частину таблиці (зверніть увагу, що рамка та форматування розширюються, щоб включити їх у таблицю).
Тепер нам потрібно використовувати цю таблицю Excel під час створення діаграм.
Ось точні кроки для створення динамічної лінійної діаграми за допомогою таблиці Excel:
- Виберіть всю таблицю Excel.
- Перейдіть на вкладку Вставка.
- У групі діаграм виберіть діаграму «Лінія з маркерами».
Це воно!
Наведені вище кроки вставлятимуть лінійну діаграму, яка автоматично оновлюватиметься, коли ви додасте більше даних до таблиці Excel.
Зауважте, що хоча додавання нових даних автоматично оновлює діаграму, видалення даних не призведе до повного видалення точок даних. Наприклад, якщо видалити 2 точки даних, на діаграмі буде показано вільне місце праворуч. Щоб виправити це, перетягніть синю позначку в нижньому правому куті таблиці Excel, щоб видалити видалені точки даних з таблиці (як показано нижче).
Хоча я взяв приклад лінійної діаграми, ви також можете створити інші типи діаграм, такі як стовпчасті/стовпчасті діаграми, використовуючи цю техніку.
Використання формул Excel
Як я вже згадував, використання таблиці Excel - найкращий спосіб створення динамічних діапазонів діаграм.
Однак, якщо з якихось причин ви не можете використовувати таблицю Excel (можливо, якщо ви використовуєте Excel 2003), існує інший (трохи складний) спосіб створення динамічних діапазонів діаграм за допомогою формул Excel та іменованих діапазонів.
Припустимо, у вас є набір даних, як показано нижче:
Щоб створити діапазон динамічних діаграм з цих даних, нам потрібно:
- Створіть два динамічні іменовані діапазони за формулою OFFSET (по одному для стовпців «Значення» та «Місяці»). Додавання/видалення точки даних автоматично оновлює ці іменовані діапазони.
- Вставте діаграму, яка використовує названі діапазони як джерело даних.
Дозвольте мені зараз детально пояснити кожен крок.
Крок 1 - Створення динамічних іменованих діапазонів
Нижче наведено кроки для створення динамічних іменованих діапазонів:
- Перейдіть на вкладку «Формули».
- Натисніть «Менеджер імен».
- У діалоговому вікні "Менеджер імен" вкажіть ім'я як ChartValues і введіть таку формулу в Посилається на частину: = ЗМІЩЕННЯ (Формула! $ B $ 2 ,,, COUNTIF (Формула! $ B $ 2: $ B $ 100, ””))
- Натисніть OK.
- У діалоговому вікні «Менеджер імен» натисніть «Створити».
- У діалоговому вікні "Менеджер імен" вкажіть ім'я як ChartMonths і введіть таку формулу в Посилається на частину: = OFFSET (Формула! $ A $ 2 ,,, COUNTIF (Формула! $ A $ 2: $ A $ 100, ""))
- Натисніть ОК.
- Натисніть Закрити.
Наведені вище кроки створили два іменовані діапазони у книзі - ChartValue та ChartMonth (вони стосуються значень та діапазонів місяців у наборі даних відповідно).
Якщо ви перейдете та оновите стовпець значення, додавши ще одну точку даних, діапазон імен ChartValue тепер автоматично оновиться, щоб показати додаткову точку даних у ньому.
Магія тут виконується функцією OFFSET.
У формулі діапазону імен «ChartValue» ми вказали B2 як точку відліку. Формула OFFSET починається там і поширюється на всі заповнені клітинки у стовпці.
Така ж логіка працює і у формулі діапазону імен ChartMonth.
Крок 2 - Створіть діаграму, використовуючи ці іменовані діапазони
Тепер все, що вам потрібно зробити, це вставити діаграму, яка буде використовувати названі діапазони як джерело даних.
Нижче наведено кроки для вставлення діаграми та використання діапазонів динамічних діаграм:
- Перейдіть на вкладку Вставка.
- Натисніть «Вставити діаграму лінії або області» та вставте діаграму «Лінія з маркерами». Це додасть діаграму до робочого аркуша.
- Вибравши діаграму, перейдіть на вкладку Дизайн.
- Натисніть Вибрати дані.
- У діалоговому вікні "Вибір джерела даних" натисніть кнопку "Додати" в "Записи легенди (серія)".
- У полі Значення ряду введіть = Formula! ChartValues (зверніть увагу, що вам потрібно вказати ім’я аркуша перед іменованим діапазоном, щоб це працювало).
- Натисніть OK.
- Натисніть кнопку Редагувати у «Мітки осі горизонтальної (категорії) осі».
- У діалоговому вікні "Мітки осі" введіть = Formula! ChartMonths
- Натисніть ОК.
Це воно! Тепер ваша діаграма використовує динамічний діапазон і буде оновлюватися, коли ви додаєте/видаляєте точки даних у діаграмі.
Кілька важливих речей, які потрібно знати при використанні іменованих діапазонів з діаграмами:
- У даних діаграми не повинно бути порожніх клітинок. Якщо є порожнє місце, іменований діапазон не буде посилатися на правильний набір даних (оскільки загальна кількість призведе до того, що він посилатиметься на меншу кількість клітинок).
- Під час використання імені аркуша у джерелі діаграми потрібно дотримуватися умов найменування. Наприклад, якщо назва аркуша - це одне слово, наприклад, Формула, то ви можете використовувати = Formula! ChartValue. Але якщо є більше одного слова, наприклад, діаграма формул, то вам потрібно використовувати = "діаграма формул"! ChartValue.
