1. 获取第一张和最后一张 Sheet 的名称

1. 获取第一张和最后一张 Sheet 的名称

我有一个文件,其中的数据每天输入到单独的工作表 (TAB) 中。布局相同,我正在寻找解决方案,从这些单独的 TAB 中获取过去 1-3 个月的动态平均值。每天都会创建一个新的 TAB,每个月手动执行此操作会花费太多时间。有什么建议吗?

答案1

您可以尝试使用数据 > 合并或 3D 公式

例如:您有以下工作表:一月 二月 三月 摘要

假设每张表 A1 中都有一些数字

转到摘要表 A1 中输入以下公式 =average('Jan:Mar'!A1)

答案2

为此,我们将完成两个步骤:

1. 获取第一张和最后一张 Sheet 的名称

据我了解,您每天都会添加一张新工作表。您需要确保工作表名称的格式相同。例如,假设格式为:DD-MM-YY(因此 2020 年 8 月 1 日将显示为 01-08-20)。

在此步骤中,我们需要生成一个工作表范围文本过去 3 个月(例如:01-03-20:01-01-20),以供 INDIRECT 公式使用,创建 3d 公式(第二步)。

假设日期持有最后的相关日期(因此它可以是另一个单元格的引用,或 TODAY() 或 TODAY()-1 等公式)。现在,决定第一的相关日期。如果距最后日期还有 3*30 天,则使用:

=LDATE-3*30

或者,如果它应该恰好在 DATE 之前 3 个月,则使用:

=DATE(YEAR(LDATE),MONTH(LDATE)-3,DAY(LDATE))

让我们把这个第一次约会命名为日期

最后,这将是我们的格式化工作表范围文本(请注意,这假设您已将工作表名称格式化为 DD-MM-YY,只需根据需要更改为任何其他格式):

="'"&TEXT(LDATE,"DD-MM-YY")&":"&TEXT(FDATE,"DD-MM-YY")&"'!"

现在的值应如下所示:'14-07-20:14-04-20'!

我们将整个范围命名为板材范围

2. 间接 + 3d 公式

INDIRECT 函数从给定的文本字符串返回有效引用,'Sheet1:Sheet4'!A1:B2 是 Sheet1 到 Sheet4 之间所有工作表中所有 A1:B2 单元格的范围。

因此,以下将获取动态范围的平均值(例如,假设您需要所有 A3 单元格的平均值):

=AVERAGE(INDIRECT(SHEETRANGE&"A3"))

或者,如果我们想让所有操作都在一个单元格中发生(LDATE 除外,由您定义):

=AVERAGE(INDIRECT("'"&TEXT(LDATE,"DD-MM-YY")&":"&TEXT(DATE(YEAR(LDATE),MONTH(LDATE)-3,DAY(LDATE)),"DD-MM-YY")&"'!"&"A3"))

祝你好运!

相关内容