Tengo una hoja de trabajo en la que busco utilizar la fórmula Índice/Coincidencia para obtener valores exactos para la intersección única de los valores de Departamento y Cuenta en una hoja de datos separada.
Sin embargo, necesito que Index Array sea flexible ya que busco la misma intersección "única" de valores de departamento y cuenta en varias columnas (las columnas representan meses en mi hoja de datos).
Intenté usar una fórmula indirecta haciendo referencia al rango de valores en la hoja de datos, también probé otra variación de la fórmula indirecta nombrando mi rango; ambas fórmulas no devuelven resultados, aunque sé que existen valores. en mi punto de intersección. Si codifico mi Index Array, la fórmula funciona y obtengo que los resultados se completen en mi hoja de trabajo.
Aquí están mis fórmulas ahora:
=IFERROR(INDEX(INDIRECT(_2015_December_Act), MATCH(AB$15&$G17, ('Essbase Data'!$C$8:$C$356&'Essbase Data'!$A$8:$A$356), 0)), "")
Donde _2015_December_Act es un rango con nombre en mi pestaña de datos de Essbase para valores en el rango de Q8:Q356.
y también:
=IFERROR(INDEX(INDIRECT(""&$A$8&"!"&$B$8), MATCH(AB$15&$G16, ('Essbase Data'!$C$8:$C$356&'Essbase Data'!$A$8:$A$356), 0)), "")
Donde A8 = Datos de Essbase y b8 = Q8:Q356: este rango representa los valores del mes de diciembre en mi hoja de datos de Essbase.
¿Qué estoy haciendo mal?
Estoy intentando automatizar la actualización de la referencia de la matriz, de modo que el informe pueda actualizarse mensualmente con actualizaciones manuales mínimas en la matriz de índice.
Respuesta1
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 sus rangos
*Los nombres de pestañas remotas que contienen espacios deben estar entre comillas simples ( 'Tab name'). Podría ser una mala fuente, pero desde mi perspectiva parece que faltan en las fórmulas anteriores.
En primer lugar, desea identificar el rango para hacer coincidir sus puntos fijos en el tiempo. Rango de fechas:
="'" & B2 & "'!$1:$1"
El resultado de ejemplo sería 'Essbase Data'!$1:$1. Esto se utilizará para MATCH()la columna deseada.
En segundo lugar, desea identificar el rango para hacer coincidir los nombres de sus campos. Rango de campo:
="'" & B2 & "'!$A:$A"
El resultado de ejemplo sería 'Essbase Data'!$A:$A. Esto se utilizará para MATCH()la fila deseada.
Por último, desea identificar el rango de todo su conjunto de datos. Rango de datos:
="'" & B2 & "'!$A:$D"
El resultado de ejemplo sería 'Essbase Data'!$A:$D. Esto se utilizará para INDEX()la columna y fila deseada. Podrías usar COUNTA()y ADDRESS()para construirlos dinámicamente. es decir
="'" & B2 & "'!$A$1:" & ADDRESS(COUNTA('Essbase Data'!A:A), COUNTA('Essbase Data'!1:1))
La salida de ejemplo sería'Essbase Data'!$A$1:$D$6
Indexación
En primer lugar, querrá identificar la columna especificada, utilizando el rango de fechas de su ejemplo. Columna:
=MATCH(B1,INDIRECT(B3),0)
El resultado de ejemplo sería 4. ¿Dónde B1está el encabezado especificado y B3el rango de fechas especificado en la Reporttabla anterior?
Por último, querrá crear su fórmula para la indexación de filas que devuelva su valor respectivo de la columna identificada anteriormente.
=INDEX(
INDIRECT($B$5), <-- Data range
MATCH($A9, <-- Field lookup value
INDIRECT($B$4), <-- Field range
0
),
$B$6 <-- Column number
)
Arrastre hacia abajo y su salida debería verse así:
| Field Name | Value |
| Key 1 | C |
| Key 2 | F |
| Key 3 | I |
| Key 4 | L |
| Key 5 | O |
| ... | ... |