麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] 不同工作表，依日期區間條件加總 [打印本頁]

作者: gaishutsusuru 時間: 2022-6-14 22:26 標題: 不同工作表，依日期區間條件加總

大家好，
想請問如何將不同工作表，依日期區間條件加總，相關說明如下：

[attach]34994[/attach]
這是2022年1月的工作表資料，共3筆，金額合計是60

(註：2022年2月~5月的資料都獨立放在各月的工作表資料中。但每個月的筆數不一定都是3筆，例如4、5月的筆數是4筆。)
(註：本例已有簡化，因此僅列示5個月。)

[attach]34995[/attach]
此為「彙總」的工作表。在「計算期間」手動填入期間，
例：「2022年1月~2022年3月」則，公式會自動把2022年1~3月的合計加總起來得到1560
例：「2022年1月~2022年5月」則，公式會自動把2022年1~3月的合計加總起來得到6360
其他依此類推；而若「2022年1月~2022年1月」，則是僅顯示2022年1月的資料

最後附上附檔：[attach]34996[/attach]

想請教大家，該如何寫公式呢? 再麻煩各位大大，提供幫助，謝謝大家。

作者: samwang 時間: 2022-6-15 11:12

大家好，
想請問如何將不同工作表，依日期區間條件加總，相關說明如下：

這是2022年1月的工作表資料， ...
gaishutsusuru 發表於 2022-6-14 22:26

請測試看看，謝謝

作者: gaishutsusuru 時間: 2022-6-15 21:04

回復 2# samwang

samwang大大您好，謝謝您提供的巨集檔案。
經測試後，若改了年份，好像就會出錯了。例如，計算期間改到「2021年1月~2021年1月」，正確結果應該會是0。因為工作表的資料是2022年。但仍會顯示60。請問該如何修改呢? 謝謝。

-----
另外，想請教您，如果每個工作表的筆數都是3筆。亦即每個工作表的合計金額都會出現在各自工作表的B6儲存格。請問這樣有辦法用寫公式的方式處理嗎?謝謝您。

作者: samwang 時間: 2022-6-16 11:13

回復 3# gaishutsusuru

經測試後，若改了年份，好像就會出錯了。例如，計算期間改到「2021年1月~2021年1月」，正確結果應該會是0
>> 已更新如附件，請測試看看，謝謝
公式我不會，需要其他大大幫忙解題，謝謝

作者: gaishutsusuru 時間: 2022-6-16 21:23

回復 4# samwang

您好，經過測試後，
2022年3月~2022年5月：應該是5700，而卻顯示5730
2022年1月~2022年1月：應該是30，而卻顯示6390
……

可能要再麻煩您協助看如何修改，謝謝。

(公式的話，我再找看看，謝謝您)

作者: quickfixer 時間: 2022-6-17 00:09

本帖最後由 quickfixer 於 2022-6-17 00:15 編輯

回復 5# gaishutsusuru

試試，把#4的total換成這個

Sub total()
Dim d1 As Date, d2 As Date, t, sn As String
d1 = DateValue(Split(Replace(Replace(Sheets("彙總").Range("b1"), "年", "/"), "月", ""), "~")(0))
d2 = DateValue(Split(Replace(Replace(Sheets("彙總").Range("b1"), "年", "/"), "月", ""), "~")(1))
For i = 0 To DateDiff("m", d1, d2)
      sn = Format(DateAdd("m", i, d1), "yyyy年m月")
      If checksheet(sn) = True Then
         t = t + Sheets(sn).Cells(Sheets(sn).Cells(Sheets(sn).Rows.Count, 1).End(xlUp).Row, 2)
      End If
Next i
Sheets("彙總").Range("B2") = t
End Sub

Function checksheet(sheet_name) As Boolean
Dim check As Range
On Error Resume Next
Set check = Sheets(sheet_name).Range("a1")
If Err.Number <> 0 Then checksheet = False Else checksheet = True
On Error GoTo 0
End Function

作者: samwang 時間: 2022-6-17 12:03

回復 samwang

您好，經過測試後，
2022年3月~2022年5月：應該是5700，而卻顯示5730
2022年1月~2022年 ...
gaishutsusuru 發表於 2022-6-16 21:23

已更新，請再測試看看，謝謝

作者: gaishutsusuru 時間: 2022-6-17 19:16

回復 7# samwang

samwang大大，
非常謝謝您，目前測試是可以運作的^^

作者: gaishutsusuru 時間: 2022-6-17 19:17

回復 6# quickfixer

quickfixer大大，

謝謝您提供的巨集碼。

作者: quickfixer 時間: 2022-6-17 19:18

回復 6# quickfixer

修正#6,忘了沒有檢查的需要,去掉了function,縮短程式碼
Sub total()
Dim d1 As Date, d2 As Date, t, sn As String
d1 = DateValue(Split(Replace(Replace(Sheets("彙總").Range("b1"), "年", "/"), "月", ""), "~")(0))
d2 = DateValue(Split(Replace(Replace(Sheets("彙總").Range("b1"), "年", "/"), "月", ""), "~")(1))
On Error Resume Next
For i = 0 To DateDiff("m", d1, d2)
sn = Format(DateAdd("m", i, d1), "yyyy年m月")
t = t + Sheets(sn).Cells(Sheets(sn).Cells(Sheets(sn).Rows.Count, 1).End(xlUp).Row, 2)
Next i
Sheets("彙總").Range("B2") = t
End Sub

作者: gaishutsusuru 時間: 2022-6-17 22:18

回復 10# quickfixer

謝謝您，我再來試看看

作者: 准提部林 時間: 2022-6-18 12:24

本帖最後由准提部林於 2022-6-18 12:36 編輯

合計格固定在各表B6
B1輸入:202201
C1輸入:202203
格式設為:0000年00月

B2//陣列公式(三鍵齊按)
=SUM(iferror(N(INDIRECT(TEXT(IF(ROW(1:36)<=MOD(C1-B1,88)+1,TEXT(B1,"0-00")+ROW(1:36)*31-31),"'yyyy年m月'\!B\6"))),0))

ROW(1:36)...最多從B1月份起算36個月

合計位置不固定
=SUM(iferror(SUMIF(INDIRECT(TEXT(IF(ROW(1:36)<=MOD(C1-B1,88)+1,TEXT(B1,"0-00")+ROW(1:36)*31-31),"'yyyy年m月'\!A:A")),"合計",INDIRECT(TEXT(IF(ROW(1:36)<=MOD(C1-B1,88)+1,TEXT(B1,"0-00")+ROW(1:36)*31-31),"'yyyy年m月'\!B:B"))),0))

作者: gaishutsusuru 時間: 2022-6-18 14:23

本帖最後由 gaishutsusuru 於 2022-6-18 14:24 編輯

回復 12# 准提部林

謝謝准提部林大大，

經測試後，
第一個公式好像少寫了一個「\」，所以要加上去後才可以執行，修改後如下：
=SUM(iferror(N(INDIRECT(TEXT(IF(ROW(1:36)<=MOD(C1-B1,88)+1,TEXT(B1,"0-00")+ROW(1:36)*31-31),"'yyyy年m月'\!\B\6"))),0))

而第二個公式，好像無法成功執行，請問該如何修改呢，謝謝您。

作者: 准提部林 時間: 2022-6-18 14:42

本帖最後由准提部林於 2022-6-18 17:03 編輯

回復 13# gaishutsusuru

改成如下試試//
=SUM(iferror(SUMIF(INDIRECT(TEXT(IF(ROW(1:36)<=MOD(C1-B1,88)+1,TEXT(B1,"0-00")+ROW(1:36)*31-31),"'yyyy年m月'\!\A\:\A")),"合計",INDIRECT(TEXT(IF(ROW(1:36)<=MOD(C1-B1,88)+1,TEXT(B1,"0-00")+ROW(1:36)*31-31),"'yyyy年m月'\!\B\:\B"))),0))

或
=SUM(iferror(SUMIF(INDIRECT(TEXT(IF(ROW(1:36)<=MOD(C1-B1,88)+1,TEXT(B1,"0-00")+ROW(1:36)*31-31),"'yyyy年m月")&"'!A:A"),"合計",INDIRECT(TEXT(IF(ROW(1:36)<=MOD(C1-B1,88)+1,TEXT(B1,"0-00")+ROW(1:36)*31-31),"'yyyy年m月")&"'!B:B")),0))

================================
再修下//
=SUM(iferror(SUMIF(INDIRECT(TEXT(TEXT(B1,"0-00")+ROW(1:36)*31-31,"'yyyy年m月")&"'!A:A"),A2,INDIRECT(TEXT(TEXT(B1,"0-00")+ROW(1:36)*31-31,"'yyyy年m月")&"'!B:B"))/(ROW(1:36)<=MOD(C1-B1,88)+1),0))

作者: 准提部林 時間: 2022-6-18 17:25

本帖最後由准提部林於 2022-6-18 19:38 編輯

Sub 合計匯總()
Dim d0, d1, d2, S
d0 = CDate(Split([彙總!b1], "~")(0))
d1 = Format(Split([彙總!b1], "~")(0), "YYYYMM")
d2 = Format(Split([彙總!b1], "~")(1), "YYYYMM")
On Error Resume Next
For i = 0 To (d2 - d1) Mod 88
S = S + Val(Sheets(Format(d0 + i * 31, "yyyy年m月")).[b65536].End(3))
Next i
[彙總!B2] = S
End Sub

作者: gaishutsusuru 時間: 2022-6-18 18:34

本帖最後由 gaishutsusuru 於 2022-6-18 18:35 編輯

回復 gaishutsusuru

改成如下試試//
=SUM(iferror(SUMIF(INDIRECT(TEXT(IF(ROW(1:36)
准提部林發表於 2022-6-18 14:42

非常感謝准提部林大大的幫忙，公式和巨集都可以用。:)

作者: 准提部林 時間: 2022-6-18 19:39

回復 16# gaishutsusuru

vba有修正, d1不可為date, 應為yyyymm格式才對!!

作者: gaishutsusuru 時間: 2022-6-18 20:44

回復 17# 准提部林

您好，原本YYYYMM測試後是可以用的。若我將YYYYMM改為yyyymm，也是可以用的：

Sub 合計匯總()
Dim d0, d1, d2, S
d0 = CDate(Split([彙總!b1], "~")(0))
d1 = Format(Split([彙總!b1], "~")(0), "yyyymm")
d2 = Format(Split([彙總!b1], "~")(1), "yyyymm")
On Error Resume Next
For i = 0 To (d2 - d1) Mod 88
S = S + Val(Sheets(Format(d0 + i * 31, "yyyy年m月")).[b65536].End(3))
Next i
[彙總!B2] = S
End Sub

請問原本的(YYYYMM)會造成哪個地方出錯呢? 謝謝您。

作者: 准提部林 時間: 2022-6-19 09:38

回復 18# gaishutsusuru

原來的
d1 = CDate(Split([彙總!b1], "~")(0)) > yyyy/m/d
d2 = CDate(Split([彙總!b1], "~")(1)) > yyyy/m/d
是"日期", 計算月差 (d2 - d1) Mod 88 , 是不對的 ,
須用quickfixer大大的 DateDiff("m", d1, d2)

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)