Диаграммы Excel - установка динамического завершения серии

Вы можете установить диапазон для диаграммы динамически в Excel. Для этого вы можете использовать что-то вроде следующего кода VBA:

Private Sub Worksheet_Change(ByVal Target as Range)
    Select Case Target 
    Case Cells(14, 2)
        Sheet1.ChartObjects(1).Chart.SetSourceData Range("$C5:$C$" & Cells(14,2))
    ...
    End Select
End Sub

В этом случае ячейка, содержащая номер последней строки, которую нужно включить, - это B14 (запомните первую строку при обращении к объекту Cells). Вы также можете использовать переменную вместо ссылки на ячейки, если хотите сделать это полностью в коде. (Это работает как в 2007, так и в 2003 году). Вы можете назначить эту процедуру кнопке и щелкнуть ее, чтобы обновить диаграмму после обновления ячейки, содержащей последнюю строку.

Однако это может быть не совсем то, что вы хотите сделать ... Я не знаю, как использовать формулу непосредственно в диаграмме для указания исходных данных.

Редактировать: И как PConroy указывает в комментарии, вы можете поместить этот код в событие Change для этого рабочего листа, так что для запуска кода не требуется ни кнопки, ни комбинации клавиш. Вы также можете добавить код, чтобы он обновлял каждую диаграмму только при редактировании соответствующей ячейки.

Я обновил приведенный выше пример, чтобы отразить это.

Вы можете посмотреть динамические диапазоны. Если вы используете функцию СМЕЩЕНИЕ, вы можете указать начальную ячейку и количество строк и столбцов для выбора. Этот сайт содержит некоторую полезную информацию о присвоении имени диапазону OFFSET.

Хорошо, мне нужно было провести еще немного исследований, вот как заставить его работать, полностью в электронной таблице (без VBA):

Используя A1 в качестве конца желаемого диапазона, и диаграмма находится на том же листе, что и данные:

Назовите первую ячейку данных (C5) именованным диапазоном, скажем TESTRANGE.
. Создан именованный диапазон MYDATA по следующей формуле:

= СМЕЩЕНИЕ (ТЕСТРАНЖ; 0; 0; Лист1! $ A $ 1; 1)

Теперь перейдите на вкладку СЕРИЯ диалогового окна ИСТОЧНИК ДАННЫХ диаграммы, и измените выражение VALUES на:

= Sheet1! MYDATA

Теперь каждый раз, когда вы меняете значение ячейки A1, диаграмма будет изменяться.

Спасибо Роберту Мирнсу за обнаружение недостатков в моем предыдущем ответе.

+1 к названию.

Обратите внимание, что имена на самом деле не ссылаются на диапазоны, они ссылаются на формулы. Вот почему вы можете задать имя примерно как «= OFFSET (...)» или «= COUNT (...)». Вы можете создавать именованные константы, просто сделайте ссылку на имя примерно как «= 42».

Именованные формулы и формулы массива - это два метода работы с рабочими листами, которые я снова и снова применяю к рабочим листам не очень опытных пользователей.

Этого можно добиться в два этапа:

• Создайте динамический именованный диапазон
• Добавьте код VBA для обновления источника данных диаграмм до именованного диапазона.

Создайте динамический диапазон с именем Range

Введите количество строк в диапазоне данных в ячейку на листе данных.

Создайте именованный диапазон в вашем листе данных (Вставить - Имя - Определить) с именем MyRange, который имеет формулу, подобную этой:

=OFFSET(Sheet1!$A$1,0,0,Sheet1!$D$1,3)

Обновите формулу в соответствии с вашим макетом

• Sheet1! $ A $ 1 установите это в верхнюю левую часть вашего диапазона данных
• Лист1! $ D $ 1 устанавливает это в ячейку, содержащую количество строк
• 3 устанавливает это значение на количество столбцов

Проверьте, что именованный диапазон работает:

Выберите раскрывающееся меню Edit - Go To, введите MyRange в справочное поле. Ваша область данных для диаграммы должна быть выбрана.

Добавьте код VBA

Откройте VBA IDE (Alt-F11)

Выберите Sheet1 в окне VBAProject и вставьте этот код

Private Sub Worksheet_Change(ByVal Target As Range)

        If Target.Address <> "$D$1" Then Exit Sub
    'Change $D$1 to the cell where you have entered the number of rows
    'When the sheet changes, code checks to see if the cell $D$1 has changed

       ThisWorkbook.Sheets("Sheet1").ChartObjects(1).Chart.SetSourceData _
         Source:=ThisWorkbook.Sheets("Sheet1").Range("MyRange")
    '  ThisWorkbook.Sheets("Chart1").SetSourceData _
         Source:=ThisWorkbook.Sheets("Sheet1").Range("MyRange")
    'The first line of code assumes that chart is embedded into Sheet1
    'The second line assumes that the chart is in its own chart sheet
    'Uncomment and change as required

    'Add more code here to update all the other charts

End Sub

На что стоит обратить внимание

Не используйте именованный диапазон напрямую в качестве источника данных для диаграммы. Если вы введете именованный диапазон «MyRange» в качестве исходных данных - диапазон данных для диаграммы, Excel автоматически преобразует именованный диапазон в фактический диапазон. Поэтому любые будущие изменения в названном диапазоне не будут обновлять вашу диаграмму.

На производительность могут повлиять перечисленные выше подходы.

Функция КОМПЕНСИРОВАТЬ в названном диапазоне является «изменчивой», что означает, что она пересчитывается всякий раз, когда вычисляется любая ячейка в книге. Если производительность является проблемой, замените ее формулой ПОКАЗАТЕЛЬ.

=Sheet1!$A$1:INDEX(Sheet1!$1:$65536,Sheet1!$D$1,2)

Код запускается каждый раз при изменении данных на Sheet1. Если производительность является проблемой, измените код, чтобы он запускался только по запросу (например, с помощью кнопки или меню).

Простой способ сделать это - просто скрыть строки / столбцы, которые вы не хотите включать - когда вы переходите к графику, он автоматически исключает скрытые строки / столбцы.

Усиление ответа @Robert Mearns, вот как использовать динамические диапазоны ячеек для графиков используя только формулы Excel (VBA не требуется):

Создайте динамический диапазон с именем Range

Скажем, у вас есть 3 столбца, например:

A5 | Время | Data1 | Data2 |

A6 | 00:00 | 123123 | 234234 |

...

A3000 | 16:54 | 678678 | 987987 |

Теперь диапазон ваших данных может меняться в зависимости от данных, которые у вас могут быть, например, у вас есть 20 строк данных, 3000 строк данных или даже 25000 строк данных. Вам нужен график, который будет обновляться автоматически без необходимости повторно устанавливать диапазон ваших данных каждый раз, когда вы обновляете сами данные.

Вот как это сделать просто:

1. Определите другую ячейку, значение которой будет иметь количество занятых ячеек с данными, и поместите в нее формулу =COUNTIF(A:A,"<>"&""). Например, это будет в ячейкеD1.

2. Перейдите на вкладку «Формулы» -> «Определить имя», чтобы определить диапазон имен.

3. В окне «Новое имя»:

   я. Дайте вашему диапазону данных имя, нравитьсяДиапазон данныхНапример.

   II. В поле «Относится к» установите формулу: =OFFSET(Sheet1!$A$1, 0, 0,Sheet1!$D$1,3),

   где:

   • Sheet1!$A$1 => Ссылка: это ссылка, на которой вы хотите основать смещение.

   • 0 => Строки: это количество строк вверх или вниз, на которые вы хотите, чтобы верхняя левая ячейка результатов ссылалась.

   • 0 => Столбцы: количество столбцов слева или справа, на которые должна ссылаться верхняя левая ячейка результатов.

   • Sheet1!$D$1 => Высота: это высота в количестве строк, которую вы хотите получить в результате.

   • 3 => Ширина: это ширина в количестве столбцов, которую вы хотите получить в результате.

4. Добавьте график и в окне «Выбор источника данных» в диапазоне данных диаграммы вставьте созданную формулу. Например: =Sheet1!DataRange

Минусы: Если вы напрямую используете именованный диапазон в качестве источника данных для диаграммы, Excel автоматически преобразует именованный диапазон в фактический диапазон. Поэтому любые будущие изменения в названном диапазоне не будут обновлять вашу диаграмму.

Для этого вам нужно отредактировать диаграмму и каждый раз заново устанавливать диапазон на =Sheet1!DataRange. Возможно, это не так удобно, но это лучше, чем редактировать диапазон вручную ...