☰

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()來動態建立它們。 IE

="'" & 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            |
| ...          | ...          |

相關內容