
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 Report
tabela 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 |
| ... | ... |