Руководствовался вот
этим
У меня Excel 2007 английский.
Итоговый документ
здесь.
По шагам:
1) Надо сочинить формулу для определения диапазона ячеек. Мне было лень её продумывать, я использовал просто «количество непустых среди A1:A8, но она не будет работать, например, для A1:A6 непустые, A7 = #VALUE!, A8 непустая. Если возникнут сложности с этой формулой — сделаю, это несложно.
У меня формула получилась такая =OFFSET(Sheet1!$A$1;0;0;COUNT(Sheet1!$A$1:$A$8);1)
комментарии: OFFSET изменяет заданный диапазон (в данном случае одну ячейку $A$1), смещая её левый верхний угол на 0 строк/столбцов (т.е. не смещая) и изменяя её высоту до некоторого числа, а ширину до 1 столбца. Новая высота определяется вот этой частью формулы COUNT(Sheet1!$A$1:$A$8), т.е. количество непустых ячеек в диапазоне $A$1:$A$8. По условию надо более длинную, с IF-ами.
Комментарии:
— здесь и далее $A$1 то же что и A1, только координаты абсолютные, т.е. если вставить строку столбец, останутся те ми же и будут ссылаться уже на другие данные. Можно вместо них относительные — это просто A1, они будут изменяться при добавлении/удалении строк/столбцов.
— в интернетах пишут примеры формул для OFFSET с запятыми, разделяющими аргументы, у меня так не получилось, но получилось с точками с запятой. Наверное зависит от локали.
— работоспособность формулы можно проверить вставив ее в любую ячейку и окружив её, например SUM, т.е. =SUM(OFFSET($A$1;0;0;COUNT($A$1:$A$8);1))
2) Надо как-то назвать этот динамический диапазон. Это делает команда Formulas — Define Name. Там Name — какой угодно, например MyName, а значение — формула из пункта 1.
3) Надо графу задать ='Sheet1'!MyName в качетсве горизонтального диапазона. Я делал так: Graph — Select Data — Legend Entries (Series) — Add — Series Values =Book1!MyName (имя любое).
