별도의 데이터 시트에서 부서 및 계정 값의 고유한 교차점에 대한 정확한 값을 가져오기 위해 색인/일치 수식을 활용하려는 워크시트가 있습니다.
그러나 여러 열에 걸쳐 부서 및 계정 값의 동일한 "고유한" 교차점을 찾고 있으므로 인덱스 배열이 유연해야 합니다(열은 데이터 시트에서 월을 나타냄).
데이터 시트의 값 범위를 참조하여 간접 수식을 사용하려고 시도했으며 범위 이름을 지정하여 간접 수식의 또 다른 변형도 시도했습니다. 값이 존재한다는 것을 알고 있지만 두 수식 모두 결과를 반환하지 않습니다. 내 교차점에서. 인덱스 배열을 하드 코딩하면 수식이 작동하고 결과가 워크시트에 채워집니다.
이제 내 공식은 다음과 같습니다.
=IFERROR(INDEX(INDIRECT(_2015_December_Act), MATCH(AB$15&$G17, ('Essbase Data'!$C$8:$C$356&'Essbase Data'!$A$8:$A$356), 0)), "")
여기서 _2015_December_Act는 Q8:Q356 범위의 값에 대한 내 Essbase 데이터 탭의 명명된 범위입니다.
그리고 또한:
=IFERROR(INDEX(INDIRECT(""&$A$8&"!"&$B$8), MATCH(AB$15&$G16, ('Essbase Data'!$C$8:$C$356&'Essbase Data'!$A$8:$A$356), 0)), "")
여기서 A8 = Essbase 데이터, b8 = Q8:Q356 - 이 범위는 Essbase 데이터 시트의 12월 값을 나타냅니다.
내가 도대체 뭘 잘못하고있는 겁니까?
인덱스 배열에 대한 수동 업데이트를 최소화하면서 보고서를 매월 새로 고칠 수 있도록 배열 참조 업데이트를 자동화하려고 합니다.
답변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 | ? |
| ... | ... |
범위 식별
*공백이 포함된 원격 탭 이름은 작은따옴표( )로 묶어야 합니다 'Tab name'. 잘못된 글꼴일 수도 있지만 내 관점에서는 위 수식에서 글꼴이 누락된 것 같습니다.
먼저, 고정된 시점과 일치하는 범위를 식별하려고 합니다. 날짜 범위:
="'" & B2 & "'!$1:$1"
예제 출력은 입니다 'Essbase Data'!$1:$1. 이는 MATCH()원하는 열에 사용됩니다 .
둘째, 필드 이름과 일치하는 범위를 식별하려고 합니다. 분야 범위:
="'" & B2 & "'!$A:$A"
예제 출력은 입니다 'Essbase Data'!$A:$A. 이는 MATCH()원하는 행 에 사용됩니다 .
마지막으로 전체 데이터 세트의 범위를 식별하려고 합니다. 데이터 범위:
="'" & B2 & "'!$A:$D"
예제 출력은 입니다 'Essbase Data'!$A:$D. 이는 INDEX()원하는 열과 행 에 사용됩니다 . COUNTA()및 를 사용하여 ADDRESS()이를 동적으로 구성할 수 있습니다. 즉
="'" & B2 & "'!$A$1:" & ADDRESS(COUNTA('Essbase Data'!A:A), COUNTA('Essbase Data'!1:1))
예제 출력은 다음과 같습니다.'Essbase Data'!$A$1:$D$6
인덱싱
먼저, 예의 날짜 범위를 사용하여 지정된 열을 식별하고 싶을 것입니다. 열:
=MATCH(B1,INDIRECT(B3),0)
예제 출력은 입니다 4. B1지정된 제목은 어디에 있고, 위 표 B3에서 지정된 날짜 범위는 어디에 있습니까?Report
마지막으로 위에서 식별된 열의 해당 값을 반환하는 행 인덱싱에 대한 수식을 만들고 싶을 것입니다.
=INDEX(
INDIRECT($B$5), <-- Data range
MATCH($A9, <-- Field lookup value
INDIRECT($B$4), <-- Field range
0
),
$B$6 <-- Column number
)
아래로 드래그하면 출력은 다음과 같아야 합니다.
| Field Name | Value |
| Key 1 | C |
| Key 2 | F |
| Key 3 | I |
| Key 4 | L |
| Key 5 | O |
| ... | ... |