Зміст
- Створення іменованих діапазонів в Excel 2007 і 2010
- Створення динамічної діаграми в Excel 2007 і 2010
- Висновок
Роббі здебільшого пише про Skyrim, але також іноді проливає світло на дивацтва таких програм Microsoft, як Excel та Outlook.
Привіт і ласкаво просимо до моєї статті про використання ЗМІСТИТИ функція. У сьогоднішній статті я збираюся дослідити, як створювати динамічні діаграми в Excel за допомогою ЗМІСТИТИ так само, як Визначені імена для створення іменованих діапазонів.
Це дозволить вам зробити, це створити діаграми з ваших даних, які автоматично оновлюватимуться при додаванні або видаленні даних. За допомогою статичної діаграми під час додавання даних вам доведеться вручну оновити діаграму так, щоб вона відображала нові дані. Динамічна діаграма робить це за вас автоматично.
В моєму попередня стаття на ЗМІСТИТИ функцію, я дослідив, як використовувати його з КОНТА функція для створення динамічних діапазонів даних, щоб ви могли, наприклад, використовувати її з СУММА складати рахунки-фактури, дані про продажі або веб-трафік. Якщо ви видалите або додасте дані до динамічного діапазону, ваша формула буде автоматично перерахована для відображення нових даних.
Створення іменованих діапазонів в Excel 2007 і 2010
Першим кроком є визначення іменованих діапазонів з наших даних.
Потім названі діапазони будуть використані для визначення даних, які ми будемо використовувати для створення динамічних діаграм.
Нам потрібно створити два іменовані діапазони, один для самих даних, а інший для міток діаграми. По-перше, ми створимо діапазон для даних, які з’являться на нашій діаграмі.
- Виберіть Визначте ім’я кнопку на Формули на вкладці Визначені імена групи.
- Визначте a Ім'я (без пробілів), яке ви можете бачити. Я використовував DailyVisits.
- В Відноситься до у полі введіть таку формулу OFFSET:
= OFFSET (DynamicCharts! $ B $ 2,1,0, COUNTA (DynamicCharts! $ B: $ B), 1)
Наведена формула створює динамічний діапазон, починаючи з комірки B2. Він відраховує ту саму кількість рядків, що і кількість рядків, які, як виявлено, містять дані КОНТА.
Отже, якщо КОНТА знаходить 23 комірки, що містять дані, діапазон починається з B2 і закінчується в B25.
КОНТА є динамічним. Якщо дані додаються або видаляються, вони автоматично оновлюються, що змінює діапазон. ЗМІСТИТИ Ця функція пояснюється набагато детальніше в моєму першому концентраторі ЗМІСТИТИ (посилання можна знайти у Вступі).
Примітка:При внесенні змін до Відноситься до , не використовуйте клавіші зі стрілками для переміщення по цьому діалоговому вікні, інакше воно додасть посилання на клітинки до формули. Перемістіть, натиснувши замість цього мишу.
Примітка:щоб редагувати іменовані діапазони після їх створення, використовуйте Менеджер імен зліва від кнопки Визначити ім'я, яку ви використовували для її створення.
- Далі нам потрібно повторити описаний вище процес, цього разу використовуючи Label для Ім'я і формула нижче в Відноситься до , щоб створити наш динамічний діапазон для міток діаграм.
= OFFSET (DynamicCharts! $ A $ 2,1,0, COUNTA (DynamicCharts! A $ 3: $ A $ 33), 1
Створення динамічної діаграми в Excel 2007 і 2010
Тепер у нас є названі діапазони, нам потрібно створити діаграму:
- Виберіть Стовпець кнопку в Діаграми група на Вставити вкладку
Це створить абсолютно порожню діаграму.
- Вибравши діаграму, натисніть на Виберіть Дані кнопку в Дані група на Дизайн вкладка під Інструменти діаграм.
- Змініть діапазон даних діаграми так, щоб ім’я, яке ви дали своєму іменованому діапазону вище для даних (я використовував DailyVisits), було введено після назви вкладки, як показано нижче.
Отже, у своєму прикладі я змінив:
= DynamicCharts! $ F $ 7
до
= DynamicCharts! DailyVisits
Діаграма тепер виглядатиме так.
Щоб додати наші динамічні мітки:
- Виберіть діаграму ще раз.
- Клацніть на Виберіть Дані кнопку в Дані група на Дизайн вкладка під Інструменти діаграм.
- Під Горизонтальні (категорії) мітки осей, натисніть Редагувати.
- В Мітка осі діапазон, введіть назву вашого іменованого діапазону для ваших міток (я вибрав Мітки для свого).
- Клацніть гаразд двічі, і Excel тепер додасть ваші мітки до діаграми.
Нарешті, нам потрібно переконатися, що діаграма використовує названі діапазони. Зробити це:
- Переконайтеся, що діаграма ще раз обрана.
- Клацніть на ряд даних.
- У рядку формул ви побачите формулу, що починається з = СЕРІЯ.
Формула на моїй діаграмі:
= СЕРІЯ (, 'Огляд аналітики.xlsm'! Мітки, DynamicCharts! $ B $ 3: $ B $ 39,1)
Перш ніж редагувати цю формулу, давайте розглянемо її синтаксис.
Формула складається з чотирьох частин.
- Заголовок діаграми (це необов’язково)
- Розташування міток для горизонтальної осі
- Розташування даних для серії
- Порядок появи серій на графіку (1 для першої, 2 для другої тощо)
Є три частини формули, які слід редагувати.
- Перший - це додати заголовок діаграми, якщо потрібно, із заголовка даних (якщо клацнути ліворуч від першої коми у формулі, а потім вибрати клітинку, що містить заголовок даних (що в моєму прикладі є B2), Excel буде додайте його автоматично для вас), щоб перша частина формули стала:
= СЕРІЯ (DynamicCharts! $ B $ 2,
- Друга частина - це частина, що містить динамічні мітки. Вони оновлюватимуться автоматично, якщо мітки у вашій електронній таблиці змінюються.
'Огляд аналітики.xlsm'! Мітки,
- Третя частина - забезпечити оновлення діаграми при динамічному додаванні або видаленні даних. Зробити це:
Змініть останню частину формули $ B $ 3: $ B $ 39 на ваш іменований діапазон даних (мій DailyVisits).
DynamicCharts! $ B $ 3: $ B $ 39,
Формула стає:
= СЕРІЯ (DynamicCharts! $ B $ 2, 'Analytics Overview.xlsm'! Мітки, DynamicCharts! DailyVisits, 1)
Тепер у нас є діаграма завершена, ще одне останнє, на що слід звернути увагу при введенні даних. Якщо ви введете дані без відповідних міток у сусідньому рядку, Excel не додасть їх до вашої діаграми. Нові дані відображатимуться лише після додавання міток. Якщо ви додасте кількість міток без даних, Excel відобразить першу мітку без даних, але більше не додасть міток без даних на діаграму.
Висновок
Оскільки наше життя стає зайнятішим і обсяг інформації, з якою ми працюємо, збільшується, завжди є дуже корисним як з точки зору економії часу, так і з точки зору зменшення помилок, автоматизувати, наскільки це можливо, і ЗМІСТИТИ Функція дійсно дуже корисна, дозволяючи нам автоматизувати як формули, так і діаграми в Excel. Особливо для електронних таблиць, які часто оновлюються, автоматизація оновлення діаграм дуже зручна.
ЗМІСТИТИ Функція в Excel дозволяє створювати динамічні діапазони та динамічні діаграми, які автоматично оновлюються при додаванні чи видаленні даних у межах діапазону. У цій статті ми створили іменовані діапазони та використали їх разом із ЗМІСТИТИ функція для створення динамічних діаграм.
Велике спасибі за читання, і я сподіваюся, що ви зараз насолоджуєтесь своїми динамічно оновлюваними діаграмами та проводите свій новий вільний час на щось приємніше. Будь ласка, залиште будь-які коментарі, які у вас можуть бути, нижче.
Ця стаття є точною та вірною, наскільки відомо автору. Вміст призначений лише для інформаційних чи розважальних цілей і не замінює особистих порад чи професійних порад у ділових, фінансових, юридичних чи технічних питаннях.
