Как создать динамический диапазон диаграммы?
У меня есть веская причина использовать динамический диапазон графика. Иногда случается, что вы создаете диаграмму и при ее обновлении приходится вручную менять ее диапазон.
Даже когда вы удаляете некоторые данные, вам необходимо изменить их диапазон. Возможно, кажется, что изменение диапазона графика не имеет большого значения. Но как насчет того, когда вам нужно часто обновлять данные?
Вам нужен динамический диапазон графики.
Вы уверены, что вам нужен динамический диапазон диаграммы?
Да, 100%. Хорошо, позвольте мне показать вам кое-что.
Ниже у вас есть график с ежемесячной суммой, и когда вы добавляете сумму за июнь, значения графика такие же, изменений нет. Дело в том, что вам нужно вручную обновить диапазон диаграммы, чтобы включить в нее июнь. Как вы думаете, помогает ли использование динамического графического диапазона экономить время?
Использование таблицы данных для диапазона динамической диаграммы
Если вы используете Excel версии 2007 или более поздней версии, лучшим решением будет использование таблицы данных вместо обычного диапазона .
Все, что вам нужно сделать, это преобразовать ваш обычный диапазон в таблицу (используйте сочетание клавиш Ctrl + T) , а затем использовать эту таблицу для создания диаграммы. Теперь каждый раз, когда вы добавляете данные в таблицу, диаграмма также автоматически обновляется.
В таблице выше, когда я добавил сумму за июнь, таблица обновляется автоматически. Единственное, что заставляет вас использовать следующий метод, это то, что при удалении данных из таблицы ваша диаграмма не будет обновляться.
Решение этой проблемы заключается в том, что если вы хотите удалить данные из диаграммы, просто удалите эту ячейку, используя опцию удаления.
Использование динамического именованного диапазона
Использовать динамический именованный диапазон для диаграммы немного сложно, но это уникальная настройка. Как только вы это сделаете, управлять им станет очень легко. Поэтому я разделил весь процесс на два этапа.
- Создание динамического именованного диапазона.
- Изменение исходных данных диаграммы на динамический именованный диапазон.
Создание динамического именованного диапазона для динамической диаграммы
Чтобы создать динамический именованный диапазон, мы можем использовать функцию СМЕЩ.
Краткое введение в Offset: он может возвращать ссылку на диапазон, который представляет собой указанное количество строк и столбцов из ячейки или диапазона ячеек. У нас есть следующие данные для создания именованного диапазона.
В столбце A у нас есть месяцы и суммы в столбце B. И нам нужно создать динамические именованные диапазоны для обоих столбцов, чтобы при обновлении данных ваша диаграмма обновлялась автоматически.
Загрузите этот файл, чтобы следовать дальше .
Вот шаги.
- Перейдите на вкладку «Формулы» -> «Определенные имена» -> «Диспетчер имен».
- Нажмите «Новый», чтобы создать именованный диапазон.
- Теперь в окне нового имени введите следующую формулу (как она работает, я расскажу позже).
- =СМЕЩ(Лист2!$B$2,0,0,СЧЕТЗ(Лист2!$B:$B)-1,1)
- Назовите свой диапазон «сумма».
- Нажмите ОК.
- Теперь создайте еще один именованный диапазон, используя следующую формулу.
- =СМЕЩ(Лист2!$A$2,0,0,СЧЕТЗ(Лист2!$A:$A)-1,1)
- Назовите его «месяц».
- Нажмите ОК.
На данный момент у нас есть два именованных диапазона: «месяц» и «сумма». Теперь позвольте мне рассказать вам, как это работает. В приведенных выше формулах я использовал функцию count для подсчета общего количества ячеек со значением. Затем я использовал это значение счетчика в качестве высоты смещения для обозначения диапазона.
В диапазоне месяцев мы использовали A2 в качестве отправной точки для смещения и подсчета общего количества ячеек в столбце B со значением counta (-1 для исключения заголовка), которое относится к A2: A7 .
Изменение исходных данных диаграммы на динамический именованный диапазон
Теперь нам нужно изменить исходные данные на именованные диапазоны, которые мы только что создали. Ой, извини, я забыл сказать тебе, чтобы создать график, пожалуйста, вставьте линейный график . Вот следующие шаги.
- Щелкните правой кнопкой мыши диаграмму и выберите «Выбрать данные».
- Под заголовком нажмите «Изменить».
- В «Значениях серий» измените ссылку на диапазон на диапазон с именем «сумма».
- Нажмите ОК.
- На горизонтальной оси нажмите «Изменить».
- Введите диапазон с именем «месяц» для метки оси.
- Нажмите ОК.
Все сделано. Поздравляем, ваша диаграмма теперь имеет динамический диапазон.
Пример файла
Последние слова
Использование динамического диапазона графики значительно экономит время и сэкономит вам массу усилий. Вам не нужно снова и снова менять диапазон данных. Каждый раз, когда вы обновляете свои данные, ваша диаграмма мгновенно обновляется.