Диаграммы Excel - динамический конец серии настроек

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

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

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

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

Теперь перейдите на вкладку SERIES диалогового окна SOURCE DATA диаграммы и измените оператор VALUES на:

=Лист1!МОИ ДАННЫЕ

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

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

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

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

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

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

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

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

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

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

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

Выберите раскрывающиеся меню «Правка» — «Перейти», введите MyRange в поле ссылки. Ваша область данных для диаграммы должна быть выбрана.

Добавьте немного кода VBA

Откройте интегрированную среду разработки VBA (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. Если производительность является проблемой, измените код, чтобы он запускался только по запросу (например, через кнопку или меню).

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

Вы можете динамически установить диапазон для диаграммы в 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 (помните, что строка первая при обращении к объекту «Ячейки»). Вы также можете использовать переменную вместо ссылки на ячейки, если хотите сделать это полностью в коде. (Это работает как в 2007, так и в 2003 году.) Вы можете назначить эту процедуру кнопке и щелкнуть ее, чтобы обновить диаграмму после обновления ячейки, содержащей последнюю строку.

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

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

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

+1 за решение имени.

Обратите внимание, что имена на самом деле ссылаются не на диапазоны, а на формулы. Вот почему вы можете установить имя что-то вроде "=СМЕЩЕНИЕ(...)" или "=СЧЁТ(...)". Вы можете создавать именованные константы, просто сделайте ссылку на имя что-то вроде "=42".

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

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

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

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

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

А5 | Время | Данные1 | Данные2 |

A6 | 00:00 | 123123 | 234234 |

...

A3000 | 16:54 | 678678 | 987987 |

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

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

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

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

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

   я. Дайте диапазону данных имя, например например DataRange .

   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. Это может быть не так удобно, но это лучше, чем редактирование диапазона вручную...