我有一個文件,每天在單獨的工作表(TAB)中輸入資料。佈局是相同的,我正在尋找解決方案來從這些單獨的選項卡中獲取過去 1-3 個月的動態平均值。每天都會建立一個新的選項卡,每月手動建立一個選項卡會花費很多時間。有什麼建議 ?
答案1
您可以嘗試使用資料>合併或3D公式
範例:您有以下工作紙 一月 二月 三月 摘要
假設每張紙有 n 個,A1 中有一些數字
前往摘要表,在 A1 中輸入以下公式 =average('Jan:Mar'!A1)
答案2
為此,我們將完成 2 個步驟:
1. 取得第一個和最後一個sheet的名稱
據我了解,您每天都會新增一張新工作表。您需要確保工作表名稱的格式相同。例如,假設格式為:DD-MM-YY(因此 2020 年 8 月 1 日將顯示為 01-08-20)。
在這一步中我們想要產生一個工作表範圍文本過去 3 個月的數據(例如:01-03-20:01-01-20),由間接公式使用來創建 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"))
祝你好運!