☰

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

相关内容