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

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

Tenho uma planilha na qual pretendo utilizar a fórmula Índice/Correspondência para obter valores exatos para a interseção exclusiva dos valores de Departamento e Conta em uma planilha de dados separada.

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

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 existem valores no meu ponto de intersecção. Se eu codificar meu Index Array, a fórmula funciona e obtenho os resultados para preencher 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 minha guia de dados do Essbase para valores no intervalo 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 do mês de dezembro na minha planilha de dados do 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 Index Array.

Responder1

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

*Nomes de guias remotas que contêm espaços devem ser colocados entre aspas simples ( 'Tab name'). Pode ser uma fonte ruim, mas da minha perspectiva, parece que elas estão faltando nas fórmulas acima.

Em primeiro lugar, você deseja identificar o intervalo para combinar seus pontos fixos no tempo. Período:

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

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

Em segundo lugar, você deseja identificar o intervalo para correspondência com os nomes dos seus campos. Faixa de campo:

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

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

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

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

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

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

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

Indexação

Em primeiro lugar, você gostaria de identificar a coluna especificada usando o intervalo de datas do seu exemplo. Coluna:

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

A saída de exemplo seria 4. Onde B1é o título especificado e B3é o intervalo de datas especificado na Reporttabela acima.

Por último, você gostaria de criar sua fórmula para indexação de linha que retorne 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 deverá ficar assim:

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

informação relacionada