Excel - Como vincular a fórmula de índice / correspondência com a fórmula indireta para referência de coluna dinâmica

Question

Eu tenho uma planilha na qual estou procurando utilizar a fórmula Index / Matching para extrair os valores exatos para a intersecção exclusiva de Department & Valores da conta em uma folha de dados separada.

No entanto, preciso que o Index Array seja flexível, pois estou procurando a mesma interseção "exclusiva" do departamento & valores de conta em várias colunas (as colunas representam meses na minha folha de dados).

Eu tentei usar uma fórmula Indireta referindo-me ao intervalo de valores na planilha de dados, também tentei outra variação da fórmula Indireta nomeando meu intervalo - ambas as fórmulas não retornam resultados, embora eu saiba que valores existem no meu ponto de intersecção. Se eu codificar duro meu Index Array, a fórmula funciona e recebo os resultados para preencher na minha planilha.

Aqui estão minhas fórmulas agora:

=IFERROR(INDEX(INDIRECT(_2015_December_Act), MATCH(AB$15&$G17, ('Essbase Data'!$C$8:$C$356&'Essbase Data'!$A$8:$A$356), 0)), "") 

Onde _2015_December_Act é um intervalo nomeado na guia de dados do Essbase para valores no intervalo de Q8: Q356.

e também:

=IFERROR(INDEX(INDIRECT(""&$A$8&"!"&$B$8), MATCH(AB$15&$G16, ('Essbase Data'!$C$8:$C$356&'Essbase Data'!$A$8:$A$356), 0)), "")

Onde A8 = Dados do Essbase e b8 = Q8: Q356 - esse intervalo representa os valores no mês de dezembro na minha planilha de dados Essbase.

O que estou fazendo de errado?

Estou tentando automatizar a atualização da referência do Array, para que o relatório possa ser atualizado mensalmente com atualizações manuais mínimas no Array de índice.

    

Answer 1

Essbase Data

| Field Name | Jan - 2016 | Feb - 2016 | Mar - 2016 |
| Key 1      | A          | B          | C          |
| Key 2      | D          | E          | F          |
| Key 3      | G          | H          | I          |
| Key 4      | J          | K          | L          |
| Key 5      | M          | N          | O          |

Report

| Date:        | Mar - 2016   |
| Tab:         | Essbase Data |
| Date range:  | ?            |
| Field range: | ?            |
| Data range:  | ?            |
| Column:      | ?            |
|              |              |
| Field Name   | Value        |
| Key 1        | ?            |
| Key 2        | ?            |
| Key 3        | ?            |
| Key 4        | ?            |
| Key 5        | ?            |
| ...          | ...          |

Identificando seus intervalos

* Os nomes das guias remotas que contêm espaços devem ser agrupados em aspas simples ( 'Tab name' ). Pode ser uma fonte ruim, mas na minha perspectiva parece que eles estão faltando em suas fórmulas acima.

Primeiramente, você deseja identificar o intervalo para correspondência de seus pontos fixos no tempo. Período:

="'" & B2 & "'!$1:$1"

Exemplo de saída seria 'Essbase Data'!$1:$1 . Isso será usado para MATCH() da coluna desejada.

Em segundo lugar, você deseja identificar o intervalo para correspondência de seus nomes de campo. Campo:

="'" & B2 & "'!$A:$A"

Exemplo de saída seria 'Essbase Data'!$A:$A . Isso será usado para MATCH() da linha desejada.

Por fim, você deseja identificar o intervalo de todo o seu conjunto de dados. Intervalo de dados:

="'" & B2 & "'!$A:$D"

Exemplo de saída seria 'Essbase Data'!$A:$D . Isso será usado para INDEX() da coluna e linha desejadas. Você pode usar COUNTA() e ADDRESS() para construí-los dinamicamente. ou seja,

="'" & B2 & "'!$A$1:" & ADDRESS(COUNTA('Essbase Data'!A:A), COUNTA('Essbase Data'!1:1))

Exemplo de saída seria 'Essbase Data'!$A$1:$D$6

Indexação

Primeiramente, você deseja identificar sua coluna especificada, usando o período no seu exemplo. Coluna:

=MATCH(B1,INDIRECT(B3),0)

Exemplo de saída seria 4 . Em que B1 é o cabeçalho especificado e B3 é o intervalo de datas especificado na tabela Report acima.

Por último, você deseja criar sua fórmula para indexação de linha que retorna seu respectivo valor da coluna identificada acima.

=INDEX(
   INDIRECT($B$5), <-- Data range
   MATCH($A9, <-- Field lookup value
     INDIRECT($B$4), <-- Field range
     0
   ),
   $B$6 <-- Column number
 )

Arraste para baixo e sua saída deve se parecer com:

| Field Name   | Value        |
| Key 1        | C            |
| Key 2        | F            |
| Key 3        | I            |
| Key 4        | L            |
| Key 5        | O            |
| ...          | ...          |