ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] «ü©w¤é´Á­pºâµ²¾l¼Æ

[µo°Ý] «ü©w¤é´Á­pºâµ²¾l¼Æ

¤j¤j¦n,
½Ð±Ð
¥_°Ïªº¸ê®Æ¬O¾ã¦~«×ªº,¸ê®Æ·|¤£Â_¼W¥[,
BÄ檺¤é´Á>=VBA¤u§@ªíªºAF2
«h­pºâKÄ檺µ²¾l¼Æ
K3=k2+g3-f3-h3-i3+j3
­ìµ{¦¡¼gªk¬O
For Each xR In Range([B3], [b65535].End(3))
    If xR >= d Then 'k+g-f-h-i+j
        xR.Offset(, 9) = xR.Offset(-1, 9) + xR.Offset(, 5) - xR.Offset(, 4) - xR.Offset(, 6) - xR.Offset(, 7) + xR.Offset(, 8)
    End If
Next
µ²ªG·|Åܦ¨­È,¦ý¦³®É»Ý­n«O¯d¤½¦¡,
¨Ã¥B§Ú³oºØ¼gªk,ı±o¤£¦n¥Î,
½Ð°Ý¤j¤j,¦p¦ó§ïµ½µ{¦¡?
»Ý­n¦³2ºØ¼gªk
1. ­pºâ«áÅܦ¨­È
2. ­pºâ«á«O¯d¤½¦¡
®Ö¾P©ú²Ó2021.rar (18.67 KB)

¦^´_ 2# samwang

±z¦n,
´ú¸Õ¨S°ÝÃD,¥t¥~·Q½Ð±Ð
¦P¤@­ÓÀÉ®×
CÄæ"¨ÑÀ³°Ó"¤À¬°¤j & ¬ü
±q²Ä3¦C¶}©l,CÄæ<>¬ü,«h¾ã¦C§R°£
³o¬O­n³æ¿Wªºµ{¦¡....

TOP

¦^´_ 2# samwang

¥t¥~,¦A½Ð±Ð

1) ¬°¤°»òµ{¦¡,¥¦¯à¦Û°Ê§ä¨ì>=dªº¤é´Á,¶i¦æ­pºâ©O?
d = Sheets("VBA").[Af2]

2) AÄæ,­n¨úBÄ檺¦~¤ë,YYYY..M
­ì¥ýªº¼gªk¬O
xR.Offset(, -1) = Year(xR) & ".." & Month(xR)
§Ú®M¥Î²{¦b·sªºµ{¦¡,§ï¬°
.Formula = "=Format([b3], "YYYY") & ".." & Format([b3], "M")"
¦ýµLªk±a¥X§Ú­nªº­È,½Ð°Ý­n¦p¦ó§ï³o¬q¡H

TOP

¦^´_ 2# samwang

±z¦n,
§Ú±NKÄæ²MªÅ,¤ÏÂаõ¦æµ{¦¡,µo²{¥¦¤£·|§P§OBÄæ>=d
d = Sheets("VBA").[Af2]
¤u§@ªí¤é´Á¬O±q6/1¶}©l,°²³]·íd=6/19
¥¦µLªk§ä¨ìBÄæ>=dªº¤é´Á,¦A¶i¦æ­pºâ
¦Ó¬O¥Ã»·³£±qK3¶}©l­pºâ

TOP

¦^´_ 6# samwang

§Ú¤§«eªº³oºØ¼gªk,­n­×§ïªº®É­Ô,´N±o¤@ª½ºâÀx¦s®æ,Åý§ÚÀY©ü@@
xR.Offset(, 9) = xR.Offset(-1, 9) + xR.Offset(, 5) - xR.Offset(, 4) - xR.Offset(, 6) - xR.Offset(, 7) + xR.Offset(, 8)
½Ð°Ý°£¤F"RC"ªº¼gªk,ÁÙ¦³¨ä¥¦¤è¦¡¶Ü?

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2021-9-25 21:11 ½s¿è

¦^´_ 11# samwang
±z¬O«üK3=K2+G3-F3-H3-I3+J3ªº¤½¦¡¶Ü?
³o¬O¬°¤FÅý¦^ÂЪ̪¾¹D­n¦p¦ó­pºâ
¦pªG¬O«ü³o­Óªº¸Ü,¦]¬°§Ú¤£·|¨ä¥L¼gªk,¥Î¤F¤@¬q®É¶¡«á,¤S­n·Q¾Ç¨ä¥L¤è¦¡,
For Each xR In Range([B3], [b65535].End(3))
    If xR >= d Then 'k+g-f-h-i+j
        xR.Offset(, 9) = xR.Offset(-1, 9) + xR.Offset(, 5) - xR.Offset(, 4) - xR.Offset(, 6) - xR.Offset(, 7) + xR.Offset(, 8)
    End If
Next

§Ú³Ì·Q¾Çªº¬O°}¦C¤è¦¡,©Ò¥H¨D±Ð·|¼gªº¤H
§Ú±`¬Ý­ã¤jªºµ{¦¡,¦ý§Ú¸ê½è¤£¦n,¤@ª½¨S¾Ç·|,
²{¦b³o­ÓÀÉ,¨C¤@Äæ­pºâ,§Ú³£¨Ï¥Î³æ¿Wªºµ{¦¡,

¹³«e­±ªº°ÝÃD
AÄæ,­n¨úBÄ檺¦~¤ë,YYYY..M
§Ú­ì¥ý¤]¬O³æ¿Wªº¼gªk,¦ý§Ú¨S·Q¨ì§A§â¤G­Ó»Ý¨D¨Ö¦b¤@°_¤F

§Ú·Q§â³o­ÓÀÉ,¨C­Ó»Ý­n­pºâªºÄæ¦ì,³£Âà´«¬°°}¦C¼gªk,
¦]¬°¥u¼g¤@Ä檺­pºâ¤ñ¸û¨S¨º»ò½ÆÂø,
§Æ±æ¬Ý§O¤Hªºµ{¦¡«á,¥i¥H¾Ç±o¤@©Û¥b¦¡¡I

§Ú¦A¦Û¦æ­×§ï,¹J¨ì§ï¤£¥X¨Ó¦A´£°Ý,
§Ú¤£¤Ó²M·¡¬O§_¨CºØ»Ý¨D³£¥i¥H¥Î°}¦C...
³o¥u¬O§Ú¤ß¸Ì·Qªº¡I

TOP

¦^´_ 12# samwang

³o¤G­Óµ{¦¡°õ¦æ«á
³£¥¿½T¡I

TOP

¦^´_ 10# samwang

#6
  .Cells(i, "a").Formula = "=YEAR(RC[1]) & "".."" & MONTH(RC[1])"
³o¬qµ{¦¡,³£¥u¯à±q²Ä3¦C©¹¤U¼g

#10
  .Formula = "=YEAR(B3) & "".."" & MONTH(B3)"
³o¬qµ{¦¡,µLªkºI¨úBÄæ¸ê®Æ,±NYYYY..M¶ñ¤JAÄæ

TOP

¦^´_ 17# samwang
´ú¸Õ¨S°ÝÃD¤F¡I

¦A½Ð°Ý¦P¤@ÀÉ®×,¥t¤@¬qµ{¦¡ ®Ö¾P©ú²Ó2021.rar (28.77 KB)
EÄæ­n¶ñ¤J³æ¸¹(³sµ²)T&S&R
¦ýRÄæ=""®É,«hEÄæ="µL¥æ³f"
½Ð°Ý­n¦p¦ó§ï³o¬qµ{¦¡?
        If Arr(i, 2) >= d And Arr(i, "r") = "" Then 'Arr(i, 2),²Ä2Äæ¬O¤é´Á,RÄæµL³æ¸¹
            .Cells(i, "e").Formula = "µL¥æ³f"
        Else
            .Cells(i, "e").Formula = "=T&S&R"
        End If
    Next

TOP

¦^´_ 17# samwang

¥t¥~¤@­ÓUÄæµ{¦¡,¬õ¦r³o¤@¬q,¤é´Á+1ªº¼gªk¤£¯à¥Î,½Ð°Ý¦p¦óÅý¤é´Á+1
¨Ò¦p:u3=8/3,«h+1=8/4
    For i = 3 To UBound(Arr)
        If Arr(i, 2) >= d And Arr(i, 4) <> "¤¤©M" Or Arr(i, 4) <> "¤º´ò" And Arr(i, 4) <> "¦Á¤î" Then 'i=¦C
            .Cells(i, "u").Formula = "=Arr(i, 2)" + 1

        ElseIf Arr(i, 2) >= d And Arr(i, 4) = "¤¤©M" Or Arr(i, 4) = "¤º´ò" And Arr(i, 4) = "¦Á¤î" Then
            .Cells(i, "u").Formula = "Arr(i, 2)"
        End If
    Next

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¤H¤£©È¿ù¡A´N©È¤£§ï¹L¡A§ï¹L¨Ã¤£Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD