gaishutsusuru

¦^´_ 10# quickfixer


ÁÂÁ±z¡A§Ú¦A¨Ó¸Õ¬Ý¬Ý

­ã´£³¡ªL

¦X­p®æ©T©w¦b¦UªíB6
B1¿é¤J:202201
C1¿é¤J:202203
®æ¦¡³]¬°:0000¦~00¤ë

B2//°}¦C¤½¦¡(¤TÁä»ô«ö)
=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)...³Ì¦h±qB1¤ë¥÷°_ºâ36­Ó¤ë

¦X­p¦ì¸m¤£©T©w
=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")),"¦X­p",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

¦^´_ 12# ­ã´£³¡ªL

ÁÂÁ­㴣³¡ªL¤j¤j¡A

¸g´ú¸Õ«á¡A
²Ä¤@­Ó¤½¦¡¦n¹³¤Ö¼g¤F¤@­Ó¡u\¡v¡A©Ò¥H­n¥[¤W¥h«á¤~¥i¥H°õ¦æ¡A­×§ï«á¦p¤U¡G
=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))

¦Ó²Ä¤G­Ó¤½¦¡¡A¦n¹³µLªk¦¨¥\°õ¦æ¡A½Ð°Ý¸Ó¦p¦ó­×§ï©O¡AÁÂÁ±z¡C

­ã´£³¡ªL

¦^´_ 13# gaishutsusuru


§ï¦¨¦p¤U¸Õ¸Õ//
=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")),"¦X­p",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"),"¦X­p",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))

================================
¦A­×¤U//
=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))

­ã´£³¡ªL

Sub ¦X­p¶×Á`()
Dim d0, d1, d2, S
d0 = CDate(Split([·JÁ`!b1], "~")(0))
d1 = Format(Split([·JÁ`!b1], "~")(0), "YYYYMM")
d2 = Format(Split([·JÁ`!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
[·JÁ`!B2] = S
End Sub

gaishutsusuru

¦^´_  gaishutsusuru


§ï¦¨¦p¤U¸Õ¸Õ//
=SUM(iferror(SUMIF(INDIRECT(TEXT(IF(ROW(1:36)
­ã´£³¡ªL µoªí©ó 2022-6-18 14:42

«D±`·PÁ­㴣³¡ªL¤j¤jªºÀ°¦£¡A¤½¦¡©M¥¨¶°³£¥i¥H¥Î¡C:)

­ã´£³¡ªL

¦^´_ 16# gaishutsusuru


vba¦³­×¥¿, d1¤£¥i¬°date, À³¬°yyyymm®æ¦¡¤~¹ï!!

gaishutsusuru

¦^´_ 17# ­ã´£³¡ªL

±z¦n¡A­ì¥»YYYYMM´ú¸Õ«á¬O¥i¥H¥Îªº¡C­Y§Ú±NYYYYMM§ï¬°yyyymm¡A¤]¬O¥i¥H¥Îªº¡G

Sub ¦X­p¶×Á`()
Dim d0, d1, d2, S
d0 = CDate(Split([·JÁ`!b1], "~")(0))
d1 = Format(Split([·JÁ`!b1], "~")(0), "yyyymm")
d2 = Format(Split([·JÁ`!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
[·JÁ`!B2] = S
End Sub

½Ð°Ý­ì¥»ªº(YYYYMM)·|³y¦¨­þ­Ó¦a¤è¥X¿ù©O?  ÁÂÁ±z¡C

­ã´£³¡ªL

¦^´_ 18# gaishutsusuru


­ì¨Óªº
d1 = CDate(Split([·JÁ`!b1], "~")(0))  > yyyy/m/d
d2 = CDate(Split([·JÁ`!b1], "~")(1))  > yyyy/m/d
¬O"¤é´Á", ­pºâ¤ë®t (d2 - d1) Mod 88 , ¬O¤£¹ïªº ,
¶·¥Îquickfixer¤j¤jªº DateDiff("m", d1, d2)