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

问题描述

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

这是我最初尝试的:

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

A 列是我的工作表中选项卡名称的选择列表.所以第一个实例有效，但当然，它不会像我希望的那样填充到列中.我意识到我可以将公式复制到列中，但是当我向列表中添加行时，某种类型的 ARRAYFORMULA 是理想的.

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

我也找到了这个答案，但我想既然它已经 2.5 岁了，也许有人发现了一个聪明的方法来避免复制的拖拽.

解决方案

答案:

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

更多信息:

不幸的是，对于 INDIRECT 和 ARRAYFORMULA 的用户来说，发现一个聪明的方法不是问题 - 只能用公式来完成的限制这个问题的根源.

设置自定义函数:

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

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

确保然后使用保存图标保存脚本.

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

=ARRAYINDIRECT(A2:A)

函数概要:

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

参考:

• JavaScript 数组 reduce() 方法
• 间接 - 文档编辑器帮助
• 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?

This is what I originally attempted:

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

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.

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.

解决方案

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.

More Information:

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.

Setting up a custom function:

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)

Rundown of the function:

• 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

References:

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

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