Excel: автоматическое обновление графика с изменяемой отправной точкой

Question

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

Так что до сути проблемы.

Скажем, у меня есть данные в виде:

2012    I   3.6
    II  3.4
    III 3.3
    IV  2.8
    V   2.2
    VI  1.9
    VII 1.7
    VIII    1.7
    IX  1.9
    X   1.6
    XI  1.5
    XII 1.6
2013    I   0.6
    II  0.3
    III 0.2
    IV  -0.3
    V   -0.1
    VI  0.2
    VII 0.3
    VIII    -0.2
    IX  -0.4
    X   -0.1
    XI  -0.4
    XII -0.4

Отсюда я могу сделать объект в менеджере имен с (пример):

=OFFSET(CPI!$C$2;0;0;COUNT(CPI!$C$2:$C$145))

Это будет подсчитывать все ячейки, в которых есть какое-то значение, и автоматически обновлять диаграмму, которая ссылается на объект имени, когда значения добавляются. Но я хочу, чтобы ссылка $ C $ 2 в OFFSET() и COUNT() была управляемой. Под этим я подразумеваю, в качестве примера, ячейку $ X $ 1 со значением 2012, которая определяет начальную точку имени объекта и, следовательно, диаграмму, которая на него ссылается.

Я знаю, как ссылаться на конкретную ячейку путем сопоставления значений ячейки с помощью VLOOKUP(), но она возвращает значение ячейки, а не координаты. Следовательно, это бесполезно (или, по крайней мере, я так думаю).

Answer 1

Я бы предложил использовать сводную диаграмму

настройте данные немного по-другому (как показано ниже) и перетащите следующие поля в сводную диаграмму.

Filter: Year
Row Values: RN
Values: Value

Фильтр по нужному году

Answer 2

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

Примечание: вам нужно заменить мои запятые на точки с запятой для вашей системы

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

=OFFSET(Sheet1!$C$1,MATCH(Sheet1!$E$1,Sheet1!$A:$A,0)-1,,COUNTA(INDIRECT("C"&MATCH(Sheet1!$E$1,Sheet1!$A:$A,0)&":C1000")))

Обратите внимание на C1000 в конце формулы - вы можете сделать этот C15000 или аналогичный, если у вас есть больше данных.

Метод заключается в следующем:

OFFSET(
Start: C1
Rows: Find year using MATCH() formula and move down this many cells -1
Columns: N/A
Height: INDIRECT effectively creates the range between Cx, where x is the row of the 
        selected year and C1000. Then using COUNTA over this range gives the required 
        height of the range
Width: N/A
)