Excel: gráfico de atualização automática com ponto de partida variável

Question

Liguei o excel a um banco de dados de atualização automática, mas agora quero dar um passo além e tornar todos os itens de relatório essenciais automáticos também. E não só isso - eu quero fazer o relatório de maneira parece facilmente controlável por alguns valores.

Então, para o núcleo do problema.

Digamos que eu tenha dados na forma de:

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

A partir daqui, posso criar um objeto no gerenciador de nomes com (exemplo):

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

Isso contará todas as células que tiverem algum valor nelas e atualizará automaticamente o gráfico que se refere ao objeto de nome à medida que os valores forem adicionados. Mas o que eu quero é que a referência de $ C $ 2 em OFFSET () e COUNT () seja controlável. Com isso quero dizer, como exemplo, eu tenho uma célula $ X $ 1 com valor 2012, que determina o ponto de partida do nome do objeto e, portanto, o gráfico que se refere a ele.

Eu sei como se referir a uma célula específica, combinando valores de célula com VLOOKUP (), mas retorna o valor de célula, não as coordenadas. Por isso, é inútil (ou pelo menos eu penso assim).

    

Answer 1

Outro método está abaixo, usei meus próprios valores para testar:

Nota: Você precisará substituir minhas vírgulas por ponto e vírgula pelo seu sistema

Usando a fórmula de intervalo nomeado e a configuração abaixo iniciará o intervalo nomeado do ano selecionado e continuará até o final dos dados (ou C1000 , se isso ocorrer primeiro)

=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")))

Observe o C1000 no final da fórmula - você poderia fazer com que C15000 ou similar se tivesse mais dados.

O método é o seguinte:

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
)

    

Answer 2

Sugiro usar um gráfico dinâmico

configure seus dados de maneira ligeiramente diferente (como abaixo) e arraste os seguintes campos para um gráfico dinâmico.

Filter: Year
Row Values: RN
Values: Value

Filtre no ano que você precisa