Создание диаграммы с использованием динамического именованного диапазона в Excel 2010

Question

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

1. Откройте Excel, создайте новую книгу.

2. Введите некоторые данные:

3. В ячейке D1 введите: $A$1:$B$5. (В моем реальном листе это вычисляется динамически, но при ручном вводе все еще возникают проблемы).

4. На ленте нажмите Формулы, Определить имя. Определите MyRange1как локальное имя листа, используя, =INDIRECT(Sheet1!$D$1)как показано ниже:

5. Нажмите «ОК», а затем вставьте диаграмму рассеяния.

6. Откройте диалоговое окно «Выбор данных» и введите='Sheet1'!MyRange1

7. Excel зависает...

Проблема возникает как в Windows XP, так и в Windows 7 с Excel 2010 в обоих случаях и повторяется каждый раз.

---

Я также пробовал:

• Определение отдельных диапазонов для данных x и y и использование диалогового окна «Изменить ряд». После ввода ='Sheet1'!MyXRangeв поле значения X Excel перестает принимать ввод с клавиатуры и мыши, за исключением клавиши Escape, которая закрывает диалоговое окно. Если я возвращаюсь в диалоговое окно, то оно вылетает.

• Область действия именованного диапазона в рабочей книге вместо рабочего листа. Это действительно останавливает сбой, но я получаю ошибки в диалоговом окне «Выбрать данные» в зависимости от того, ввожу ли я =MyRange1или ='Sheet1'!MyRange1:

---

Это известная проблема или есть куда сообщить об этом? У меня нет Excel 2007 или 2003, чтобы проверить, касается ли проблема версии 2010. Если у меня не получится это сделать, я, вероятно, просто воспользуюсь VBA вместо динамических именованных диапазонов.

---

Обновление: Я думал, что разобрался (я опубликовал ответ, теперь удален). Я изменил значение в ячейке D1 = $A$1:$B$5 на D1 = 'Лист1'!$A$1:$B$5, и диаграмма создана правильно. Однако, похоже, что при создании диаграммы она не была динамической — она просто использовала текущие значения для создания рядов X и Y, поэтому изменение D1 не приводит к обновлению диаграммы.

Answer 1

Согласно ссылке ниже и моему личному тестированию, вам необходимо ввести ряд данных в форме

'WorkbookName.xls'!RangeNameX

Если ваше имя динамическое, то и результирующая диаграмма будет такой же. Формула, которую я использовал для своего динамического именованного диапазона, была (измените для своей ситуации и используйте для обоих диапазонов X и Y).

=OFFSET($A$1,0,0,MATCH(1E+306,$A:$A,1),1)

Это помогло сделать диаграмму динамичной, добавив в нее новые значения (требовалось ввести значения X и Y перед обновлением диаграммы).

Источник информации о том, как настроить диаграмму — сайт вопросов и ответов Microsoft Источник для горячих динамических диапазонов - OZGrid

Answer 2

1. Когда INDIRECT используется для определения диапазонов, диаграммы часто не используют диапазоны правильно, и часто даже не принимают эти именованные диапазоны. Существуют лучшие (более надежные) способы определения динамических диапазонов, например, с использованием INDEX или OFFSET.

2. Даже если имя будет распознано диаграммой, оно будет преобразовано в адрес ячейки в поле Диапазон данных диаграммы диалогового окна Выбор источника данных. Имена сохраняются только в диапазонах для X, Y и имени серии для каждой отдельной серии.

3. Если вы добавляете к диапазону имя листа в ячейке D1? Диаграммам нужны полностью определенные диапазоны, поэтому, если ячейка D1 содержит, Sheet1!$A$1:$B$5вы можете использовать свое определенное имя в поле Диапазон данных диаграммы диалогового окна Выбор источника данных. Обратите внимание, что согласно пункту 2 Excel преобразует этот диапазон в адрес своей ячейки, когда вы нажимаете ОК.

Answer 3

Вот как я создаю динамическую диаграмму.

1. Создайте таблицу на основе ваших данных.

   

2. Выделите таблицу

3. Перейдите на вкладку «Вставка» и выберите нужный тип диаграммы.

При добавлении данных в таблицу они также будут обновлены на диаграмме.