如何在公式中动态设置选项卡名称? [英] How to dynamically set tab name in a formula?

问题描述

我有一个摘要选项卡和多个其他选项卡，它们的构造方式相同.我希望在摘要"标签中收集所有其他标签中的信息

I have a summary tab and multiple other tabs which are constructed the same way. I would like in my summary tab to gather information from all other tabs

今天，我在摘要"标签中的每一行都有:

Today, in the summary tab I have for each line:

• 第一个单元格:My tab 1->同一电子表格中标签的名称
• 第二个单元格:=COUNTIF('My tab 1'!$B7:$B; "OK")-> B列中所有带有"OK"文本的单元格的计数.

• first cell: My tab 1 -> name of a tab in the same spreadsheet
• second cell: =COUNTIF('My tab 1'!$B7:$B; "OK") -> count of all cells with "OK" text in column B.

我想使用在第二个单元格的公式内的第一个单元格中定义的选项卡名称.有可能吗?如果可以，怎么办?

I would like to use the tab name defined in the first cell inside the formula of the second cell. Is it possible and, if yes, how?

推荐答案

尝试INDIRECT函数，如下所述: https://support.google.com/docs/answer/3093377?hl=zh_CN

Try the INDIRECT function, described here: https://support.google.com/docs/answer/3093377?hl=en

= INDIRECT($ A $ 1)

=INDIRECT($A$1)

为了被视为范围的引用，似乎INDIRECT需要将整个范围引用作为单个字符串.这为您提供了类似您问题的答案:

In order to be seen as a reference for the range, INDIRECT needs the entire range reference as a single string, it seems. This gives you something like the answer to your question:

= COUNTIF(INDIRECT(CONCATENATE($ A $ 1，！$ B7:$ B"))，确定")

=COUNTIF(INDIRECT(CONCATENATE($A$1, "!$B7:$B")), "OK")

您所放弃的是地址的$ B7:$ B部分现在是一个字符串，因此，如果您在页面下复制此单元格，则7不会更新为8、9，依此类推. .

What you give up there is that the $B7:$B portion of your address is now a string, so if you copy this cell down the page, the 7 won't get updated to 8, 9, and so on.

可能还有其他功能组合可以使您更接近:这是我经过几分钟的搜索后得出的.

There may be other combinations of functions that will get you closer: this is the one I arrived at with a few minutes of searching.

这篇关于如何在公式中动态设置选项卡名称?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！