Как получить диаграмму в Excel для автоматического добавления новых рядов данных?

Question

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

Мне нужно будет добавлять новые компании ежемесячно, и я хотел бы, чтобы диаграмма автоматически создавала новый ряд данных, когда я создаю строку для новой компании в своем листе Excel и вставляю в нее данные.

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

Кто-нибудь знает как это решить?

Answer 1

Я решил эту проблему для курса Excel, преподаваемого в моей компании с использованием следующего ресурса: http://www.techrepublic.com/blog/microsoft-office/two-ways-to-build-dynamic-charts-in-excel/ Сначала я нахожу решение довольно сложным, но оно работает как шарм и избегает всех VBA и таблиц! Работает и для старых версий Excel.

Один отказ от ответственности заключается в том, что он использует столбчатые сгруппированные столбчатые диаграммы с группами, играющими роль "серии". Это выглядит следующим образом:

Создайте диапазон данных, который расширяется вниз:

Далее подготовьте именованные диапазоны, которые мы загрузим в график. Вы создаете именованные диапазоны, нажимая на ленте Формулы -> Определенные имена -> Определить имя.

Определите имена в пределах листа, а не в рабочей книге - это хорошая практика для разделения пространств имен.

Создайте следующие три диапазона:

KPI1Range =OFFSET(Sheet1!$B$2;0;0;COUNTA(Sheet1!$B$2:$B$13);1)
KPI2Range =OFFSET(Sheet1!$C$2;0;0;COUNTA(Sheet1!$C$2:$C$13);1)
LabelRange =OFFSET(Sheet1!$A$2;0;0;COUNTA(Sheet1!$A$2:$A$13);1)

Затем создайте сгруппированную гистограмму и выберите данные:

Для диапазона меток введите следующее: и т. д. для KPI2

Затем добавьте горизонтали аналогично

Теперь вы можете добавить строки в таблицу данных с KPI для вашей новой компании!

Answer 2

«Именованные (клеточные) диапазоны» - один из секретов успеха в том, что вы хотите сделать; отличный ресурс уже существует в Интернете:http://pubs.logicalexpressions.com/pub0009/lpmarticle.asp?id=518

Answer 3

Если вы сохраните данные диаграммы в таблице Excel, то при добавлении новой строки данных в таблицу она автоматически добавит данные в связанную диаграмму. Это не сводная диаграмма - в отличие от сводной диаграммы, все ряды данных поддерживаются как стандартная диаграмма, и вы имеете полный контроль над настройкой диаграммы. Единственное, что вам нужно сделать, это убедиться, что ваши данные организованы таким образом, что они могут быть сохранены в виде таблицы.

Примечание. Это решение предназначено для Excel 2010 . Это может работать в более ранних версиях, но они не включали функциональность таблицы.

РЕДАКТИРОВАТЬ: Основываясь на комментарии Джона, я убедился, что он работает в Excel 2010 , но у меня нет других версий, поэтому я не уверен насчет Excel 2007 . Кроме того, я попробовал это с гистограммой, xy графиком и линейным графиком, и все они, кажется, работают одинаково. Он также удаляет серии, когда они удаляются из таблицы.

Answer 4

Если вы не возражаете против использования VBA, вы можете следовать протоколу в разделе « Исходные данные динамической диаграммы» в моем блоге. В статье показано, как запустить некоторый код из события изменения рабочего листа для обновления исходных данных диаграммы, включая как длину каждой серии (строки таблицы), так и количество серий (столбцы таблицы).