是否存在从多个标签访问范围的动态方式? [英] Is there a dynamic way of accessing ranges from multiple tabs?

问题描述

我的Google表格文档包含4个标签:2019年6月，2019年7月，2019年8月和主"标签.在主"选项卡中，我从所有具有相同布局的每月"选项卡中收集数据.在主"选项卡中，我具有以下功能:

My Google Sheets document contains 4 tabs: June2019, July2019, August2019, and Master. In the Master tab, I collect the data from all monthly tabs, which all have the same layout. In the Master tab, I have functions such as:

=SUM(June2019!Profits, July2019!Profits, August2019!Profits)

或

=COUNTA(QUERY({June2019!A3:C;July2019!A3:C;August2019!A3:C},"select Col2 where Col2 = 'L' and Col3 = 'P'"))

随着这一年的发展，我将更多的每月标签添加到我的Google表格文档中，我不想遍历主"标签上的所有公式并更新其参数以包括新的每月标签，例如:

As the year progresses and I add more monthly tabs to my Google Sheets document, I don't want to have to go through all the formulas on my Master tab and update their arguments to include the new monthly tabs, for example to:

=SUM(June2019!Profits, July2019!Profits, August2019!Profits, September2019!Profits)

在主"选项卡上，是否可以指定1次，即您要从中提取数据的每月选项卡列表?然后将公式用作范围参数，即该列表中每月选项卡的范围吗?这样，我不必在每次添加新月份时都为每个公式的范围参数更新month-tab-ranges.我只想在列表中添加一个月.对于SUM()公式和QUERY()公式，此方法可能会有所不同.

On the Master tab, is there a way to specify 1 time, the list of monthly tabs you want to pull data from? And then have the formulas use as range arguments, the ranges from monthly tabs in that list? This way I wouldn't have to update the monthly-tab-ranges for every formula's range argument every time a new month is added. I would just add a month to the list. The approach would probably be different for the SUM() formula and the QUERY() formula.

我想不出解决方案，因为范围参数不是字符串.如果是这样，则可以列出A列中的所有选项卡名称，然后在B列的A列中的每个自变量后附加！A3:C"，然后将整个B列用作range参数的自变量，像这样:

I cannot think of a solution, as the range arguments aren't strings. If they were, you could possibly list all the tab names in column A, then append something like "!A3:C" to each argument in column A for column B, and use the entire column B as an argument for the range parameter, something like:

=COUNTA(QUERY(B1:B5,"select Col2 where Col2 = 'L' and Col3 = 'P'"))

推荐答案

一种方法是预先使用 IFERROR

=SUM(IFERROR(June2019!Profits), 
     IFERROR(July2019!Profits), 
     IFERROR(August2019!Profits), 
     IFERROR(September2019!Profits))

这样，一旦创建了9月，就会对其进行会计处理(例如，如果不存在该工作表，则将其从计算中排除)

this way as soon as the September is created it will be accounted (eg if such sheet does not exist it will be left out from calculation)

这篇关于是否存在从多个标签访问范围的动态方式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！