☰

Excel - 動的な列参照のためにインデックス/マッチング式を間接式にリンクする方法

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
Excel - 動的な列参照のためにインデックス/マッチング式を間接式にリンクする方法

インデックス/マッチング式を使用して、別のデータ シート上の部門とアカウントの値の一意の交差部分の正確な値を取得しようとしているワークシートがあります。

ただし、複数の列にわたって部門とアカウントの値の同じ「一意の」交差点を探しているため、インデックス配列は柔軟である必要があります (列はデータ シート内の月を表します)。

データ シートの値の範囲を参照して間接式を使用しようとしましたが、範囲に名前を付けて間接式の別のバリエーションも試しました。交差点に値が存在することはわかっていますが、両方の式とも結果を返しません。インデックス配列をハード コーディングすると、式は機能し、ワークシートに結果が表示されます。

これが私の公式です:

=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 は、Essbase データ タブ上の Q8:Q356 の範囲の値の名前付き範囲です。

そしてまた:

=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            |
| ...          | ...          |

関連情報