用 INDEX 替换 INDIRECT 以迭代关闭的工作簿 [英] Replace INDIRECT with INDEX to iterate closed workbooks

问题描述

昨天我开始了一个关于将某些工作簿中的单元格迭代到新的摘要工作簿中的主题.为此，我使用了 INDIRECT 函数:

Yesterday I started a topic in regards of iterating cells from some workbooks into a new, summary workbook. For this I used INDIRECT function:

使用 INDIRECT 在工作簿之间迭代

然而，实现 INDIRECT 的回溯我想实现同样的事情，但是通过使用 INDEX，正如我从论坛中读到的那样，即使引用的工作簿关闭，INDEX 也能工作，而 INDIRECT 则不能.

However, realizing the backdrafts of INDIRECT I want to achieve the same thing, but by using INDEX, as I've read from forums that INDEX works even when the referenced workbook is closed, wheras INDIRECT does not.

然后，使用

 =INDEX(CONCATENATE("'C:Pathtofile[";O282;"]";$O$283;"'!";ADDRESS(11;KOLUMN()-23));1)

其中 O282 是我要引用的工作簿(以这种方式引用，因为我需要迭代一些工作簿，例如工作簿 2015-01 (O282)、2015-02 (P282) 等)，而 O283 是相应工作簿的工作表，在这种情况下是静态的.我想在引用的工作簿和工作表中导入单元格 D12 的值，实际上 INDEX 会这样做.;1 用于表示我只想返回 1 个单元格.(我相信 US excel 使用,"而不是;").

Where O282 is the workbook I want to reference (which is referenced in this manner as I will need to iterate some workbooks like the workbook 2015-01 (O282), 2015-02 (P282) etc.) and O283 is the sheet of the corresponding workbook, which in this case is static. I want to import the value of cell D12 in the referenced workbook and worksheet with this, and actually the INDEX does this. ;1 is used to indicate that I only want to return 1 cell. (I believe US excel uses "," instead of ";").

问题在于它返回:

 'C:Pathtofile[2015-01]Data'!$D$11

所以基本上.INDEX 引用正确，但它返回路径而不是预期的值.

so basically. INDEX references correct, but it returns the path and not the value which is intended.

我试过了:

 =INDEX(CONCATENATE("'C:Pathtofile[";O282;"]";$O$283;"'!";ADDRESS(11;KOLUMN()-23):ADRESS(12;KOLUMN()-23));1)

获取数组 D12:E12，然后引用第一个引用行的值，即 D12.但这并没有奏效，但这是一种避免与 INDEX 将我的输入解释为返回引用而不是值的方法相关的问题的方法.

to get the Array D12:E12, and then reference to the value at the first referenced row, i.e. D12. This hasn't worked though, but it has been a way to avoid the problems associated with INDEX interpreting my input as a mean to return a reference, rather than a value.

推荐答案

INDEX，在您的情况下，需要两个参数:引用和位置.您指定了 address 而不是引用，因此 Excel 将其理解为包含一个值的范围 - 这是您的地址并将其返回给您.你应该:

INDEX, in your case, takes two arguments: reference and position. Instead of reference, you specified address, so Excel understands it as range with one value in it - which is your address and returns it to you. You should:

=INDEX(INDIRECT(CONCATENATE(...));1)

但是当您使用引用范围中的第一个值时，您可以简单地执行以下操作:

But as you use the first value in referenced range, you can simply do:

=INDIRECT(CONCATENATE(...))

这篇关于用 INDEX 替换 INDIRECT 以迭代关闭的工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！