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

[µo°Ý] Åý¤½¦¡ªº­È,ª½±µ±a¤JÀx¦s®æ

¦^´_ 69# PJChen

1) AÄæ¤ë¥÷2020..6¦³¦ó°µ¥Î??? §ï¦¨202006¤£¬O§óª½±µ, ±Æ§Ç¤]¨S°ÝÃD!

2) ¤½¦¡¬O¥Ñ¤W¦Ó¤U²Ö­pªº, «ü©w¤é´Á¥i¯à·|¦³»~®tµo¥Í? ¤£¤Ó¥i¾a!

3) =IF(COUNTIF(¥_°Ï!$B$3:$B3,¥_°Ï!$B3)=1,SUMIFS(¥x¤¤!$D:$D,¥x¤¤!$B:$B,"¤j",¥x¤¤!$A:$A,¥_°Ï!$B3),IF(COUNTIF(¥_°Ï!$B$3:$B3,¥_°Ï!$B3)=2,SUMIFS(·s¦Ë!$D:$D,·s¦Ë!$B:$B,"¤j",·s¦Ë!$A:$A,¥_°Ï!$B4),IF(COUNTIF(¥_°Ï!$B$3:$B3,¥_°Ï!$B3)=3,SUMIFS(«n°Ï!$D:$D,«n°Ï!$B:$B,"¤j",«n°Ï!$A:$A,¥_°Ï!$B5),"")))
¤u§@ªí¥u¦³"¥_°Ï", ¨ä¥¦¤£¨£, «ç´ú¸Õµ{¦¡¤Î¤½¦¡???

´£°Ý«eÀ³¦A¦¸½T»{µ¹ªº¸ê®Æ¤Î³W«h»¡©ú¬O§_§¹¾ã,
¶È±q¤½¦¡¤Îµ{¦¡½X¤¤¥h¸ÑŪ»Ý¨D³W«h, ¬O­nªá§ó¦h®É¶¡ªº~~

TOP

¦^´_ 69# PJChen
³o­ÓÀɮ׸ê®Æ³£¬O¦b­pºâ¤@¨Ç¼Æ­È,¦pªG¬O§Ú¦Û¤v­n¥Î,§Úı±o¨ç¼Æ¤½¦¡¼g¦bÀx¦s®æ¤U©Ô´N¸Ñ¨M¤F
¦]¬°¹ê»Úªº¸ê®Æ¦h¹è¥u¦³§A¤F¸Ñ,¥[¤W³¡¥÷¸ê®Æ¤]¨S´£¨Ñ(­ã¤j´£¤Î³¡¥÷),µLªkÅçÃÒ
¥u¯à±N§A©Ò´£¨Ñªºµ{¦¡¾ã²z¤@¤U,¦Ü©ó¨ä¥L©Ò»Ýªº³¡¥÷¥u¯à¾a§A¦Û¦æ¼W¥[Åo!!

Sub ¥_°Ï_A_EX()
Dim Sh As Worksheet, xS As Worksheet, xR
Set xS = ThisWorkbook.Sheets("VBA")  'µ{¦¡¨Ó·½
Set Sh = Workbooks("¥þ¬Ù®Ö¾P©ú²Ó.xlsm").Sheets("¥_°Ï")
d = xS.[AA3] 'Date
Sh.Activate
'------------ 'A ¨úBÄæ¦~.¤ë
For Each xR In Range([b3], [b65535].End(3)) '¦V¤W End(3) = End(xlup).Row
   If xR >= d Then
      xR.Offset(, -1) = Year(xR) & ".." & Month(xR)     'A ¨úBÄæ¦~.¤ë
      xR.Offset(, 9) = xR.Offset(-1, 9) + xR.Offset(, 5) - xR.Offset(, 4) - xR.Offset(, 6) - xR.Offset(, 7) + xR.Offset(, 8) '¥_°Ï_K_µ²¾l
      xR.Offset(, 22) = xR.Offset(-1, 22) + xR.Offset(, 5) + xR.Offset(, 8) - xR.Offset(, 6) - xR.Offset(, 7) - xR.Offset(, 21) '¥_°Ï_X_¬£ªOµ²¾l
      '-------------------------RÄæµL³æ¸¹
      If xR.Offset(, 16) = "" Then
         xR.Offset(, 3) = "µL¥æ³f"
      Else
         xR.Offset(, 3) = xR.Offset(, 18) & xR.Offset(, 17) & xR.Offset(, 16) 'T&S&R
      End If
      '------------------------------¨ÑÀ³°Ó
      If xR.Offset(, 1) = "¤j" Then 'l+g-f+j-n
         xR.Offset(, 10) = xR.Offset(-1, 10) + xR.Offset(, 5) - xR.Offset(, 4) + xR.Offset(, 8) - xR.Offset(, 12)
         xR.Offset(, 11) = xR.Offset(-1, 11) - xR.Offset(, 13)
      Else  '¤£¬O"¤j"À³¸Ó´N¬O"¬ü"Åo
         xR.Offset(, 10) = xR.Offset(-1, 10) + xR.Offset(, 8) - xR.Offset(, 12)
         xR.Offset(, 11) = xR.Offset(-1, 11) + xR.Offset(, 5) - xR.Offset(, 4) - xR.Offset(, 13)
      End If
      '------------------------------©±¦W
      If xR.Offset(, 2) = "¤¤©M" Or xR.Offset(, 2) = "¤º´ò" Or xR.Offset(, 2) = "¦Á¤î" Then
         xR.Offset(, 19) = xR
      Else
         xR.Offset(, 19) = xR + 1
      End If
      '-----------------------------½LÂI®t²§
      If xR.Offset(, 24) = "" Then
         xR.Offset(, 23) = ""
      Else 'z-x
         xR.Offset(, 23) = xR.Offset(, 24) - xR.Offset(, 22)
      End If
   End If
Next
End Sub

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2020-6-25 10:11 ½s¿è

«Ü³Â·Ðªºªí®æ~~¦pªG¨C¤Ñ³£­n³B²z~~«Ü²Ö§a!!!
¤½¦¡¤§¶¡¦³¤¬¬Û¤Þ¥Î, ©Ò¥H¤W­±ªº¤èªk³£·|¥X¿ùªº~~
­Y¥Î³v®æ¶ñ¤J~~¤£·|§Ö¨ì­þ???

¬Ýµo©«ªºÀɮצ³¤£¤Öª©¥», ¤½¥qÀ³¦³¤@©wªº³W¼Ò, ¬°¦ó¤£½Ð±M·~ªº¥h³]­p?
®É¶¡´N¬Oª÷¿ú, °£¤F®ö¶O®É¶¡ºë¯«Åé¤O¥~, ¸ê®Æ¤]¥i¯àºâ¿ù!!!
§Ú­Ì¦³ªÅ¤]¥u¯àµyÀ°´X³\, ¦ýÁ`¤£¯à³o¼ËµL¤î¹Òªº°µ, ¤@¤ÁÁÙ¬O­n¾a¦Û¤v~~

§Ë¤F¨âºØª©¥»,
1) ¦r¨å+°}¦Cª©, ¼g¤@¥b¥»·Q©ñ±ó, ¦]¬°¥i¯à(À³¸Ó¬O)¬Ý¤£À´µ{¦¡½X, µ¹¤F¤]¨S¥Î§a!! ¤Ó½ÆÂø~~
Xl0000140(¦r¨å+°}¦C).rar (45.62 KB)

2) »²§U¤½¦¡ª©, ¯à¼W¶i¦h¤Ö³t«×, ¨S¹ê´ú
Xl0000140(»²§U¤½¦¡).rar (56.41 KB)

©Ò¦³ªº­pºâ³£±q¤W­±©«¤l¤¤ªº¤½¦¡¥Î²qªº(´X¥G¨C¦¸²q), ¨Ì¼Ëµe¸¬, ¦Û¦æ¥h­×¥¿~~


======================================================

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2020-6-25 22:46 ½s¿è

¦^´_ 73# ­ã´£³¡ªL
·PÁ­ã¤j,
§Ú·|¦A§ì®É¶¡´ú¸Õ

§Ú­Ì¤½¥q³W¼Ò«Ü¤p¡B«Üáàªù,³¡ªù¤º©Ò¦³Àɮ׳£¥Ñ§Ú³]­p,
¬Ý°_¨ÓÅv­­¦n¹³«Ü¤j,¦ý«Ü²Ö(³o¬O­Ó¨S¤H·Q±µªº¤u§@),
¤£¹L§Ú¦³¤Ñ°¨¦æªÅªº·Q¹³¤O,³s°µ¹Ú³£·|¹Ú¨£excel¡B
¹Ú¨£¤½¦¡ªº¹B§@,¦ý¯à§_¹ê²{,¤S¬O¥t¤@¦^¨Æ¡I
¤j³¡¥÷ªº°ÝÃD§Ú³£¯à¦Û¦æ¸Ñ¨M,¦U¦ì¬Ý¨ì§Úµo°Ý,
¨ä¹ê¥u¬O¦B¤s¤@¨¤^.^
¤£¹L»¡¨ì©³¬O¯à¤O¦³­­,¯à«÷´ê¦h¤Öºâ¦h¤ÖÅo...

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2020-6-25 22:43 ½s¿è

¦^´_ 58# jcchiang

±z¦n,
¥ý»¡ÁnÁÂÁÂ,§A¼gªºµ{¦¡,Á`¬OÅý§Ú±o¨ì«Ü¤jªº±Òµo
³o¬qµ{¦¡,§Úµy§@­×§ï,§Æ±æ¥¦¥i¥H¦Û°Ê¹ïÀ³,¼W¥[¤è«K©Ê,
¦ý¦³¨Ç¤G­Ó°ÝÃD§ÚµLªk¸Ñ¨M...µ{¦¡¤w¼g¤Jmacro_D    §ó·s²z³f³æ.rar (130.47 KB)
macro_Dªº"²z³f³æ"¤u§@ªí,W1 & W2ªº¹ïÀ³­È
x1 = xS.[w1] '¹ïÀ³ ¬¡°Ê½d³òa
x2 = xS.[w2] '¹ïÀ³ÀɦW
For k = 1 To 7 (­ì7­ÓÀÉ,¥ý¥Î"¤U­Ó¤ë²z³f³æ"¸ê®Æ§¨ªº2­ÓÀÉ´ú¸Õ)
xS.[V1] = k
·íxS.[w1]=1="·x·x1"
xS.[w2]="·x·x",«h¥´¶}¤½¥Î²z³f§t¦³"·x·x"¦r¼ËªºÀÉ®×,
±N²z³f³æIIªºBÄæ="·x·x1"ªºÀx¦s®æF:Pªº¸ê®Æ,
copy¨ì"1"¤u§@ªíªºB3¶K¤W­È,
¨Ï¥Î®Éµo²{µ{¦¡copy¸ê®Æ¨Ã¤£¬O«Ü§Ö³t
For Each a In Range("B:B")
If a = x1 Then d(a.Address) = d(a.Address)

©Ò¥H§Ú¬O¥Î²z³f³æIIªºF:P°Ï°ìÂл\B:L,·Q¨Ïcopy¤@¦¸§¹¦¨,
µM«á¦A±ND:F,I:Jªº¤½¦¡¥N¤J«á¤U©Ô
²{¦b¹J¨ì°ÝÃD¦p¤U¡G
1) ÁöµM¼g¤F
For k = 1 To 7
    xS.[V1] = k
¦ý¥¦¥u·|¥´¶}²Ä¤@­ÓÀÉ,§Ú­n¦p¦óÅý¥¦§â"¤U­Ó¤ë²z³f³æ"¸ê®Æ§¨,¥þ³¡Àɳ£¨Ì§Ç¥´¶},
µM«á¨Ìk = 1 To 7,©Ò¹ïÀ³ªº­È¶K¨ì¸Ó¶Kªº¦a¤è?

2) D:F,I:Jªº¤½¦¡,key¤J«á,§Æ±æ¥X²{¤½¦¡,¦Ó¤£¬O­È,¨Ò¦p¡GªL¤fªºÀÉ®×
I3­È¬O50,¦ý§Ú§Æ±æµ{¦¡
[i3] = "=" & "Int(" & [m3] & "/" & [L3] & ")" '½c¼Æ
[i3] ©Ò±o¨ìªºµª®×¬O¤½¦¡=INT(M3/L3),
D:F,I:J...4Äæ¤]³£§Æ±æ§e²{¤½¦¡¦Ó«D­È

TOP

¦^´_ 58# jcchiang
§Ú¤w¸g¸Ñ¨M°ÝÃDÅo...·PÁÂ

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2020-7-7 19:36 ½s¿è

¦^´_ 42# ­ã´£³¡ªL
­ã¤j¦n,
§Ú¥Î42¼Óªºµ{¦¡,­×§ï«á¥Î¨Ó§ì¨ú«È¤á¤U³æªº"­qÁʼÆ",­×§ï«áªºµ{¦¡¦p¤U¡G
  1. Sub ²z³f­qÁʶq()
  2. Dim Rw&, xR As Range, xH As Range, c%, Fx$
  3. Rw = Cells(Rows.Count, "K").End(xlUp).Row
  4. If Rw <= 2 Then Exit Sub
  5. '´ú¸Õ¨ä¤¤¤@­Ó«È¤áªº¤U³æ¼Æ...¥þ³£
  6. [q2] = "=SUMIFS(ºô³æ.¥þ³£!$I:$I,ºô³æ.¥þ³£!$C:$C,BF²z³f!$D2," & _
  7. "ºô³æ.¥þ³£!$K:$K,BF²z³f!$C2)+IF(BF²z³f!$R$283=BF²z³f!$B$283,BF²z³f!$R2,0)"
  8. For Each xR In Range("K2:K" & Rw)
  9.     If xR = "«~¦W" Then Set xH = xR(2, 7): c = 1: GoTo 101
  10.     If xR = "¦X­p" Then
  11.        If c = 0 Then GoTo 101
  12.        With Range(xH, xR(0, 7)) 'QÄæ¶ñ¤J¤½¦¡
  13.             .FormulaR1C1 = [q2].FormulaR1C1
  14.             .Value = .Value
  15.             .Replace 0, "", 1  '*****(1,§¹¥þ²Å¦X)
  16.        End With
  17.        c = 0
  18.     End If
  19. 101: Next
  20. [q2] = "­qÁʼÆ"
  21. End Sub
½Æ»s¥N½X
°ÝÃD¦p¤U¡G   ²z³f³æ_­qÁʼÆ.rar (81.02 KB)
a) QÄ檺­qÁʼÆ,¬O«È¤á¤U³æªº¼Æ¶q,¦¬¨ì­q³æªº®É¶¡³£¤£¬O¦P®Éªº
b) «È¤á­q³æ¦WºÙ°Ï¤À¦bAÄæ,«È¤áªº­q³æ®æ¦¡³£¤£¬Û¦P,©Ò¥H6­Ó«È¤á¦³6­Ó¤½¦¡§ì¨ú¸ê®Æ
c) §Ú­×§ï¤F¤§«eªº¤@­Óµ{¦¡,¥Î¨Ó§ì¨úQÄ檺­qÁʼÆ,¦ýµ{¦¡¤£¬O±M¬°³o­Ó¦Ó³]­p,©Ò¥H¤U¤@­Ó«È¤áªº­q³æ,·|§â«e¤@­q³æ¼Æµ¹Âл\
d) RÄ檺¥[´î¼Æ¶q,¬O¦]À³«È¤á¦³­q³æ"¥[¶q" or "´î¶q"ªº»Ý¨D¦Ó³],¦³®É«È¤H·|¦b¤U³æ´X¤Ñ«e´N§iª¾,¦ý¤£·|­×§ï·í¤é­q³æ,©Ò¥H»Ý­n¥Î¨ìRÄ檺"¥[´î¼Æ¶q",
¥i¥H¹w¥ýkey¤J,¦ý®É¶¡¥¼¨ì®É«h¤£¤©­p¤J¡I
e) ½Ð°Ý­n¦p¦ó­×§ïµ{¦¡,¥i¥H±NAÄæ¦WºÙ(«È¤á)¦C¤Jµ{¦¡¤¤,Åý¤£¦P®É¶¡¤U³æªº6­Ó«È¤á,¦U¦Ûªº­q³æ¼Æ¤£·|³QÂл\?
''----------AÄæ¦WºÙ1) ¥þ³£
[q2] = "=SUMIFS(ºô³æ.¥þ³£!$I:$I,ºô³æ.¥þ³£!$C:$C,BF²z³f!$D2," & _
"ºô³æ.¥þ³£!$K:$K,BF²z³f!$C2)+IF(BF²z³f!$R$283=BF²z³f!$B$283,BF²z³f!$R2,0)"
''----------AÄæ¦WºÙ2) ²Î²Î
[q2] = "=SUMIFS(ºô³æ.²Î²Î!$R:$R,ºô³æ.²Î²Î!$M:$M,BF²z³f!$D2,ºô³æ.²Î²Î!$AC:$AC,BF²z³f!$C2," & _
"ºô³æ.²Î²Î!$AE:$AE,BF²z³f!$B$1)+IF(BF²z³f!$R$1=BF²z³f!$B$1,BF²z³f!$R2,0)"
''----------AÄæ¦WºÙ3) ¼wQQK
'    [q2] = "=SUMIF(ºô³æ.¼wQQK!$E:$E,BF²z³f!$D2,ºô³æ.¼wQQK!$G:$G)+IF(BF²z³f!$R$388=BF²z³f!$B$388,BF²z³f!$R2,0)"
'''----------AÄæ¦WºÙ4) MªÀ
'    [q2] = "=SUMPRODUCT((ºô³æ.MªÀ!$R$2:$R$300=BF²z³f!$D2)*(ºô³æ.MªÀ!$AP$2:$AP$300))+IF(BF²z³f!$R$561=BF²z³f!$B$561,BF²z³f!$R2,0)"
'''----------AÄæ¦WºÙ5) ±o¨Ó
'    [q2] = "=SUMIFS(ºô³æ.±o¨Ó!$L:$L,ºô³æ.±o¨Ó!$H:$H,BF²z³f!$D2,ºô³æ.±o¨Ó!$O:$O,BF²z³f!$C2)+IF(BF²z³f!$R$420=BF²z³f!$B$420,BF²z³f!$R2,0)"
'''----------AÄæ¦WºÙ6) W±d
'    [q2] = "=SUMPRODUCT((ºô³æ.W±d!$C$6:$C$298=BF²z³f!$D2)*(ºô³æ.W±d!$D$6:$D$298))+IF(BF²z³f!$R$508=BF²z³f!$B$508,BF²z³f!$R2,0)"

TOP

¦^´_ 77# PJChen

¤£¬ã¨s¤½¦¡¬O¹ï©Î¿ù, ¤]¨SªkÅçÃÒ, ·Ó§Û!!! ­Y¦³»~¦Û¦æ¥h­×§ï  

Sub ²z³f­qÁʶq()
Dim Rw&, xR As Range, xD, xH As Range, c$, Fx$
Rw = Cells(Rows.Count, "K").End(xlUp).Row
If Rw <= 2 Then Exit Sub
Set xD = CreateObject("Scripting.Dictionary")
xD("¥þ³£") = "=SUMIFS(ºô³æ.¥þ³£!$I:$I,ºô³æ.¥þ³£!$C:$C,BF²z³f!$D2," & _
           "ºô³æ.¥þ³£!$K:$K,BF²z³f!$C2)+IF(BF²z³f!$R$283=BF²z³f!$B$283,BF²z³f!$R2,0)"
xD("²Î²Î") = "=SUMIFS(ºô³æ.²Î²Î!$R:$R,ºô³æ.²Î²Î!$M:$M,BF²z³f!$D2,ºô³æ.²Î²Î!$AC:$AC,BF²z³f!$C2," & _
          "ºô³æ.²Î²Î!$AE:$AE,BF²z³f!$B$1)+IF(BF²z³f!$R$1=BF²z³f!$B$1,BF²z³f!$R2,0)"
xD("¼wQQK") = "=SUMIF(ºô³æ.¼wQQK!$E:$E,BF²z³f!$D2,ºô³æ.¼wQQK!$G:$G)+IF(BF²z³f!$R$388=BF²z³f!$B$388,BF²z³f!$R2,0)"
xD("MªÀ") = "=SUMPRODUCT((ºô³æ.MªÀ!$R$2:$R$300=BF²z³f!$D2)*(ºô³æ.MªÀ!$AP$2:$AP$300))+" & _
           "IF(BF²z³f!$R$561=BF²z³f!$B$561,BF²z³f!$R2,0)"
xD("±o¨Ó") = "=SUMIFS(ºô³æ.±o¨Ó!$L:$L,ºô³æ.±o¨Ó!$H:$H,BF²z³f!$D2,ºô³æ.±o¨Ó!$O:$O,BF²z³f!$C2)" & _
           "+IF(BF²z³f!$R$420=BF²z³f!$B$420,BF²z³f!$R2,0)"
xD("W±d") = "=SUMPRODUCT((ºô³æ.W±d!$C$6:$C$298=BF²z³f!$D2)*(ºô³æ.W±d!$D$6:$D$298))+" & _
           "IF(BF²z³f!$R$508=BF²z³f!$B$508,BF²z³f!$R2,0)"

For Each xR In Range("K2:K" & Rw)
    If xR = "«~¦W" Then Set xH = xR(2, 7): c = Range("A" & xR.Row): GoTo 101
    If xR = "¦X­p" Then
       Fx = xD(c)
       If c = "" Or Fx = "" Then GoTo 101
       [Q2].Formula = Fx
       With Range(xH, xR(0, 7)) 'QÄæ¶ñ¤J¤½¦¡
            .FormulaR1C1 = [Q2].FormulaR1C1
            .Value = .Value
            .Replace 0, "", 1  '*****(1,§¹¥þ²Å¦X)
       End With
       c = ""
    End If
101: Next
[Q2] = "­qÁʼÆ"
End Sub


======================================

TOP

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

­ã¤j,
¤S¨Ó³Â·Ð±z¤F....
µ{¦¡ÁöµM¨Ó¦^¬d§ä,À³¸Ó»¡´ú¸Õ¤F´X¤Ñ,¦ý¨S¦³¤@¦¸¥i¥H¹B§@¦¨¥\¡I´N¬O§ä¤£¨ì°ÝÃDÂI...
§Ú©ñ¤W2­Ó­q³æ,§Ú·Q³o2­Ó¥i¥H¹B§@ªº¸Ü,¨ä¥LªºÀ³¸Ó´N¤£·|¦³°ÝÃD¤F,
¨ç¼Æ¤è­±½T©wµL»~,³o¬O¨C¤Ñ¥²°µªº¥\½Ò,¹B¦æOK.   ²z³f³æ_­qÁʼÆ.rar (373.36 KB)

TOP

¦^´_ 79# PJChen

§ï¤U:
If xR = "«~¦W" Then Set xH = xR(2, 7): c = Range("A" & xR.Row + 1).Value: GoTo 101

TOP

        ÀR«ä¦Û¦b : µÊ®ð¼L¤Ú¤£¦n¡A¤ß¦a¦A¦n¤]¤£¯àºâ¬O¦n¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD