从多个工作表中获取同一单元格的值 [英] Get value of same cell from multiple sheets

问题描述

我想从工作表中的多个标签中获取单元格A1的值.我试图使用INDIRECT，只是提醒我，它在ARRAYFORMULA中不起作用.是否存在某种可以做同样事情的临时"公式?

I'd like to get the value of cell A1 from multiple tabs in my sheet. I attempted to use INDIRECT, only to be reminded that it wouldn't work in an ARRAYFORMULA. Is there some sort of "makeshifty" formula that would do the same thing?

这是我最初尝试的:

 =ArrayFormula(IF(LEN(A2:A),INDIRECT(A2:A&"!A1"),))

A列是我工作表中选项卡名称的选择列表.因此，第一个实例起作用了，但是当然，它并没有像我希望的那样填充到该列中.我意识到我可以将公式复制到列的下方，但是当我在列表中添加行时，ARRAYFORMULA的某些类型将是理想的选择.

Column A is a select list of the tab names in my sheet. So the first instance works, but of course, it doesn't populate down the column as I had hoped. I realize I can just copy the formula down the column, but some type of an ARRAYFORMULA would be ideal as I add rows to the list.

我找到了这个答案，但看不到如何将其应用于我的情况.

I found this answer, but don't see how I could apply it to my situation.

我还找到了此答案，但我认为，自从它2.5岁以来，也许有人发现了一种避免的聪明方法拖动进行复制.

I also found this answer, but thought since it's 2.5 years old, maybe someone has discovered a clever way to avoid the drag of copying.

推荐答案

答案:

您需要使用脚本或使用拖动方法来执行此操作-INDIRECT使用字符串引用，因此不能与数组一起使用.

Answer:

You need to do this with a script or by using the drag method - INDIRECT uses a string reference and so can't be used with an array.

不幸的是，对于使用ARRAYFORMULA的INDIRECT的用户来说，发现一个聪明的方法并不是问题-只能使用公式来解决此问题，这是该问题的根源.

Unfortunately for the user of INDIRECT with ARRAYFORMULA, a discovery of a clever method isn't the issue - the limitation of what can be done with only formulae that is the root of this problem.

通过工具>脚本编辑器菜单项，可以创建脚本.您可以使用的示例自定义公式如下:

From the Tools > Script editor menu item, you can create scripts. An example custom formula that you could use would be as follows:

function ARRAYINDIRECT(input) {  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  return input.reduce(function(array, el) {
    if (el[0]) array.push(el[0])
    return array;
  }, []).map(x => ss.getSheetByName(x).getRange("A1").getValue());
}

请确保随后使用保存图标保存脚本.

Make sure to then save the script with the save icon.

然后，您可以在工作表中这样调用此自定义公式:

In your Sheet you can then call this custom formula as so:

=ARRAYINDIRECT(A2:A)

功能概要:

• 从调用公式的地方获取输入范围-在这种情况下为A2:A
• 减少输入以删除所有空单元格
• 将存储在单元格中的工作表名称映射到其引用的工作表的A1中的值
• 返回要输出的数组

• Takes the input range from where the formula is called - in this case A2:A
• Reduces the input to remove all cells that are empty
• Maps the name of the sheet which is stored in the cell to the value in A1 of the sheet it references
• Returns an array to output

希望对您有帮助！

• JavaScript数组的reduce()方法
• 直接-文档编辑器帮助
• ARRAYFORMULA-文档编辑器帮助

• JavaScript Array reduce() Method
• INDIRECT - Docs Editors Help
• ARRAYFORMULA - Docs Editors Help

这篇关于从多个工作表中获取同一单元格的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！