Динамический диапазон диаграмм с помощью INDIRECT: эта функция недействительна (несмотря на выделение диапазона)

Я пытаюсь создать диаграмму с динамическим диапазоном, используя функцию INDIRECT. Excel распознает диапазон, который я создаю, используя INDIRECT, поскольку он выделяет соответствующий диапазон на листе:

Однако при сохранении диаграммы я получаю сообщение об ошибке, указывающее, что функция недействительна:

Кто-нибудь знает, в чем проблема/как создать динамический диапазон диаграмм от определенного начала до определенной конечной точки?

PS: Здесь вы можете скачать приведенную выше таблицу. Формула, которую я использовал:
=INDIRECT("sheet!"&E2&":"&E3)

Ответ 1

Мина похожа на превосходный ответ Шона, но позволяет начать и завершить день. Сначала создайте два именованных диапазона, которые используют формулы Index/Match, чтобы выбрать начальный и конечный дни на основе E2 и E3:

rngDay

=INDEX(Sheet1!$A:$A,MATCH(Sheet1!$E$2,Sheet1!$A:$A,0)):INDEX(Sheet1!$A:$A,MATCH(Sheet1!$E$3,Sheet1!$A:$A,0))

rngValue

=INDEX(Sheet1!$B:$B,MATCH(Sheet1!$E$2,Sheet1!$A:$A,0)):INDEX(Sheet1!$B:$B,MATCH(Sheet1!$E$3,Sheet1!$A:$A,0))

Затем вы можете щелкнуть серию на диаграмме и изменить формулу на:

=SERIES(Sheet1!$B$1,Sheet1!rngDay,Sheet1!rngValue,1)

Вот хороший пост Chandoo о том, как использовать динамические диапазоны в диаграммах.

Ответ 2

То, как вы пытаетесь это сделать, невозможно. Диапазон данных диаграммы должен иметь фиксированный адрес.

Есть способ обойти это и использовать именованные диапазоны

Поместите количество строк, которые вы хотите в свои данные в ячейке (например, E1)
Итак, используя ваш пример, я положил Number of Rows в D1 и 6 в E1

В менеджере имен определите имена ваших данных и заголовков
Я использовал xrange и yrange и определил их как:

xrange: = OFFSET (Sheet1! $A $2,0,0, Sheet1! $E $1)
yrange: = OFFSET (Sheet1! $B $2,0,0, Sheet1! $E $1)

теперь, к вашей диаграмме - вам нужно знать название рабочей книги (после того, как вы ее настроите, функция отслеживания Excel будет следить за тем, чтобы ссылка оставалась верной независимо от любого переименования)

Оставьте пустой пробел Chart data range для Legend Entries (Series) введите заголовок как обычно, а затем имя, которое вы определили для данных (обратите внимание, что имя книги требуется для использования именованных диапазонов)

для Horizontal (Category) Axis Labels, введите имя, которое вы определили для меток

теперь, изменив число в E1, вы увидите изменение диаграммы:

Ответ 3

Еще один ответ для бит и googles.

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

Дневной диапазон:

="Sheet1!"&$F$2&":"&ADDRESS(ROW(INDIRECT($F$3)),COLUMN(INDIRECT($F$2)))

Диапазон значений:

="Sheet1!"&ADDRESS(ROW(INDIRECT($F$2)),COLUMN(INDIRECT($F$3)))&":"&$F$3

Затем добавьте два диапазона, ссылающихся на значения INDIRECT этих ячеек

Нажмите Ctrl + F3, нажмите "Создать" и добавьте новый диапазон с именем "chart_days", ссылаясь на =INDIRECT(Sheet1!$F$4); и новый диапазон с именем "chart_values", ссылаясь на =INDIRECT(Sheet1!$F$5)

Наконец, в вашей диаграмме добавьте серию, которая ссылается на =nameOfYourWorkbook!chart_values

и отредактируйте категорию, чтобы обратиться к =nameOfYourWorkbook!chart_days

Ответ 4

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

Для простого графика я определяю имя CategoryLabels следующим образом:

= OFFSET($A$5;  (InicitialMonth-1);  0;  LastMonth - (InitialMonth-1))

и DataCars:

= OFFSET($B$5;  (InicitialMonth-1);  0;  LastMonth - (InitialMonth-1))

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

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

Помните, что, как объяснил Шон Чешир и другие, чтобы использовать имена для значений диаграммы, необходимо включить имя электронной таблицы.

Ответ 5

В соответствии с приведенной формулой: = INDIRECT ( "лист!" & E2 & ":" & E3) вы неправильно указываете лист.

Я бы подумал, что это будет Лист1! или Sheet2! и т.п. Ваша формула разрешает: = sheet! E2: E3, который не является допустимым адресом. Сообщение об ошибке, которое вы получаете, означает, что Excel не может разрешить ввод для INDIRECT. INDIRECT является допустимой функцией, поэтому аргумент, который вы предлагаете, должен быть недействительным.

Все приведенные выше ответы, в которых указано имя листа, исправили вашу ошибку, но не упоминайте об этом...;)

Ответ 6

Именованная формула с косвенными функциями НЕ РАБОТАЕТ В ЧАРТАХ. Он работает в других форматах, так как желаемый динамический источник будет выделен, но при его отображении в диаграмме он не будет оцениваться. Надеюсь, что Microsoft исправит это.

Ответ 7

Если диапазон линейной диаграммы является именованной переменной и переменная имеет ссылки INDIRECT() через ячейку на диапазон, тогда переменная должна содержать не менее 2 INDIRECT(), разделенных запятой.