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

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

¦^´_ 53# jcchiang

¤j¤j¦n,
²z³f³æII.xlsx¬°¨Ó·½ÀÉ,BF²z³f.sheet¥HBÄ檺«È¤á¬°¦C¼Æ
BÄ檺«È¤á¹ïÀ³"«È¤á.sheet"ÂkÃþ
ÂkÃþ¦WºÙ¬Û¦Pªº,¨ä«È¤áªºªí®æ¥þ³£¬Û¦P
EX:
B252:B274¬°ªL¤f...¹ïÀ³ÂkÃþ¬°"¤¤¥x"
G:Pªº¸ê®Æ,B252:B274¦³23¦C
½Æ»sG252:P274ªº¸ê®Æ
¥´¶} "¤½¥Î²z³f-¤¤¥x.xlsx" (ÂkÃþªºÀɦW¦³§t"¤¤¥x")
¥u¶K­È¨ì"2.sheet" C3 (Âл\"¤½¥Î²z³f-¤¤¥x.xlsx"ªº¸ê®Æ)
¥H¤W¬O»Ý­n°õ¦æªº°Ê§@

¤§«e§Ú³£¬O¥ÎÃþ¦ü³o¼Ëªº»yªk
            xRow = Cells(Cells.Rows.Count, "G").End(xlUp).Row
                Range("C3:L" & xRow) = "=¤½¦¡"
                Range("C3:L" & xRow).Value = Range("C3:L" & xRow).Value
1) ²{¦b­n¥H7­ÓÂkÃþ¦WºÙ¥h§ä½Æ»sªº¬¡°Ê½d³ò
²ÎÎëA
²ÎÎëB
¤¤¥x
OK
µÜ
¥Ã±d
±o¦Ü

2) ²z³f³æII.xlsx BÄ檺«È¤á,­pºâ­Ó¼Æ§@¬° ½Æ»sªº¦C¼Æ¨Ì¾Ú
½Ð±Ð»yªk­n«ç»ò¼g?
¸ê®Æ.rar (313.11 KB)

TOP

¦^´_ 53# jcchiang
±z¦n,
©ÎªÌ¥ý±q¤ñ¸û²³æªº¶}©l
EX:
B252:B274¬°ªL¤f

­n¦p¦ó«ü©w½Æ»s¦C¼Æ,¤]´N¬O¦Û°Ê­pºâ B:B="ªL¤f"ªº¦C¼Æ=23¦C
µM«á¥i¥H«ü©w°Ï¶¡G252:P274¦¨¬°½Æ»sªº¨Ó·½?

TOP

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

­ã¤j¦n,
²z³fªí®æ¥Ø«e»Ý­n¤@­Ó±Æ§Ç¥\¯à,¥Ø«e¥H¤§«eµ{¦¡­×§ï,¥i¥H±Æ§Ç,¦ýµLªk¨Ì·Ó§Ú·Q­nªº¥\¯à±Æ,
¥i§_À°¦£¬Ý¤U¦p¦ó¨Ì¥H¤U»Ý¨D§@±Æ§Ç?   ²z³f³æ.rar (427.55 KB)

Åý¨C­Ó«È¤áªí®æ,¥u­nFÄ榳¼Æ¦rªº,´N¨Ìkey1-FÄæ,key-2-LÄæ±Æ§Ç
¼Æ¦r¥Ñ¤p¨ì¤j,ªí®æFÄæªÅ¥ÕªÌ,´N²¤¹L¤£±Æ§Ç(¦]¦³¨Ç«È¤á¤£»Ý­n¦¹¥\¯à)

±Æ§Ç«á,ÁÙ·|¥Î¨ìÂk¦ìªº¥\¯à,¥t¤@µ{¦¡´N¥HSÄ檺¼Æ¦r°µÂk¦ì°Ê§@,¼Æ¦r¥Ñ¤p¨ì¤j
  1. Sub ²z³f±Æ§Ç()
  2. Dim R&, xR As Range, xH As Range, C%
  3. R = Cells(Rows.Count, "K").End(xlUp).Row
  4. If R <= 2 Then Exit Sub
  5. For Each xR In Range("K2:K" & R)
  6.     If xR = "«~¦W" Then Set xH = xR(2): C = 1: GoTo 101
  7.     If xR = "¦X­p" Then
  8.        If C = 0 Then GoTo 101
  9.        Set S = Range(xH(1, -4), xR(0, -4))
  10.        C = 0
  11.         .Cells.Sort Key1:=S, Key2:=.Columns("L"), Header:=xlNo    'SortingµLªíÀY
  12.     End If
  13. 101: Next
  14. End With
  15. End Sub
½Æ»s¥N½X

TOP

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

½Ð°Ý­ã¤j,
¨C­Óªí®æ¤ºªº±Æ§Ç,¯à°÷¾ã¦C±Æ§Ç¶Ü?
¥u¦³F:Qªº½d³ò±Æ§Ç,ªí®æ¥~ªº¸ê®Æ,¦³¨Ç¬Oªí®æ¤ºªº¯Á¤Þ,¤£¸òµÛ±Æ§ÇÅܰʪº¸Ü,¾ã­Ó¸ê®Æ·|¶Ã±¼¡I

¥t¥~´ú¸Õµ²ªG:
F320¥HªÅ¥Õ®æ´ú¸Õ,±Æ§Ç«á·|¦b³Ì¤U¤è,¦ý¤¤¶¡·|ªÅ¤@¦C,¨ä¥L¥ÎªÅ®æ´ú¸Õ®É,¥¼µo²{¬Û¦P±¡§Î

²z³f³æ3.rar (324.35 KB)

TOP

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

­ã¤j,
´ú¸Õµ²ªG¤w¥i¥¿±`°õ¦æ,ÁÂÁ¡I

TOP

¦^´_ 58# jcchiang

·PÁ¦ʦ£¤¤©âªÅÀ°¦£,³o´X¤Ñ´ú¸Õµ{¦¡¤w¥i¹B§@¡I

TOP

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

­ã¤j¦n,

²z³f³æ¨Ì»Ý¨D¼W¥[¤p­pÄæ¦ì«á,µLªk¥¿±`¹B§@,¥i§_À°¦£¬Ý¬Ý!   ²z³f³æ_¨Ò¥~.rar (24.08 KB)
»Ý­n­×§ï¥H¤U
Sub ¦Û°Ê½c²~()
1) ¤p­p¥[Á` ½c.²~
2) ¦X­p¥[Á` ½c.²~ (¦ý¤£¯à­«ÂÐ¥[Á`¤p­pªº­È)

Sub ¤¹¦¬¤é¤½¦¡
3) ¹J¤p­pÄæ¦ì®É,H & JÄæ ¤¹¦¬­nªÅ¥Õ

Sub ²z³f³æ­q³æ­È¤Æ
4) ¤]¦]¬°¦³¤F¤p­pÄæ¦ì,·|§â¤p­pµ¹Âл\,¦p¦óÅý¥¦¹J¤p­pÄæ¦ì®É,¤£­nÂл\¤p­pÄæ¦ì?
  1. Sub ¦Û°Ê½c²~()
  2. Dim R&, arr, Brr, i&, S&(1 To 2), V1, V2, C%
  3. R = Cells(Rows.Count, "K").End(xlUp).Row
  4. If R <= 2 Then Exit Sub
  5. arr = Range("K2:Q" & R)  '®Æ¸¹~¼Æ¶qÄæ
  6. Brr = Range("M2:N" & R)  '½c¼Æ~²~¼ÆÄæ
  7. For i = 1 To UBound(arr)
  8.     If arr(i, 1) = "«~¦W" Then Erase S: C = 1: GoTo 101
  9.     If arr(i, 1) = "¦X­p" Then
  10.        Brr(i, 1) = S(1) '½c¼Æ¦X­p
  11.        Brr(i, 2) = S(2) '²~¼Æ¦X­p
  12.        Erase S: C = 0: GoTo 101
  13.     End If
  14.     If C = 1 Then
  15.        Brr(i, 1) = "":    Brr(i, 2) = ""
  16.        V1 = Val(arr(i, 6)) '¥]¸Ë¼Æ
  17.        V2 = Val(arr(i, 7)) '­qÁʼÆ
  18.        If arr(i, 2) = "" Or V1 = 0 Then GoTo 101
  19.        Brr(i, 1) = Int(V2 / V1) '½c¼Æ
  20.        S(1) = S(1) + Brr(i, 1)  '½c¼Æ²Ö­p
  21.        Brr(i, 2) = V2 Mod V1  '²~¼Æ
  22.        S(2) = S(2) + Brr(i, 2) '²~¼Æ²Ö­p
  23.     End If
  24. 101: Next i
  25. Range("M2:N" & R) = Brr
  26. End Sub
½Æ»s¥N½X
  1. Sub ¤¹¦¬¤é¤½¦¡()
  2. Dim R&, xR As Range, xH As Range, C%, Fx$(1 To 3), j%
  3. R = Cells(Rows.Count, "K").End(xlUp).Row
  4. If R <= 2 Then Exit Sub
  5. Fx(1) = "=IF(J3="""","""",J3-U3-V3+1)" '¤¹¦¬(°_)
  6. Fx(2) = "=IF(J3="""","""",""~"")"
  7. Fx(3) = "=IF(OR(B$_X="""",K3=""""),"""",B$_X+T3-2)" '¤½¦¡(¨´)
  8. For Each xR In Range("K2:K" & R)
  9.     If xR = "«~¦W" Then Set xH = xR(2, -2): C = xH.Row: GoTo 101
  10.     If xR = "¦X­p" Then
  11.        If C = 0 Then GoTo 101
  12.        For j = 1 To 3
  13.            Range(xH(1, j), xR(0, -3 + j)) = Replace(Replace(Fx(j), 3, C), "_X", C - 2)
  14.        Next j
  15. '          With Range(xH, xR(0, 0)): .Value = .Value: End With
  16.             With Range(xH, xR(0, 0)): .Calculate: .Value = .Value: End With
  17.        C = 0
  18.     End If
  19. 101: Next
  20. End Sub
½Æ»s¥N½X
  1. Sub ²z³f³æ­q³æ­È¤Æ()
  2. Dim Sh As Worksheet
  3.     Set Sh = Workbooks("²z³f³æ_¨Ò¥~.xlsx").Sheets("ÂA")
  4.             With Sh
  5.                 Sh.Activate
  6.                     .Range("¶KÂA­¹") = "=SUMIFS(ºô³æ!$I:$I,ºô³æ!$C:$C,ÂA!$C3,ºô³æ!$K:$K,ÂA!$B3)+IF(ÂA!$R$1=ÂA!$B$1,ÂA!$R3,0) 'QÄæ
  7.                     .Range("¶KÂA­¹").Value = Range("¶KÂA­¹").Value
  8.             End With
  9. End Sub
½Æ»s¥N½X

TOP

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

ÁÂÁ­ã¤j,
°õ¦æ¨S°ÝÃD

TOP

¦^´_ 51# jcchiang

±z¦n,
­ìµ{¦¡¼gªk³£¬O±q²Ä3¦C¶}©l§ó·s¤½¦¡,ÀHµÛ¸ê®Æ«ùÄò¼W¥[,
§ó·s®É¶¡¶V¨Ó¶Vªø,¥B¨ç¼Æ¼g¦¨ªº¤½¦¡«Ü¤¾ªø,·Q§ï¬°«D¨ç¼Æªº¼gªk,
ÀɮרC¦¸§ó·s«á,¤½¦¡«K­È¤Æ,°£«D¦³Åܰʸê®Æ,§_«h¤£»Ý­n¨C¦¸³£±q²Ä3¦C¶}©l§ó·s¤½¦¡,
©Ò¥H§Ú¦b"VBA".sheetªº[AA3]«ü©w¤@­Ó¤é´Á,·íBÄæ>=³o­Ó¤é´Áªº¸ê®Æ¤~»Ý­n§ó·s,
§Ú¨Ì·Ó³o­Ó¼Ò¦¡,§ï¤F²Ä¤@­Ó"¤ë¥÷"Äæ,°õ¦æ¨S°ÝÃD,¦ý­n®M¨ì¥H¤U11ºØ¤£¦P¤½¦¡ªº¼gªk,
If xR = "¤ë¥÷" Or xR = "±ÄÁʳ渹½X" Or xR = "µ²¾l" Or xR = "¤j" Or xR = "¬ü"  Or xR = "¤j¤¤«n°Ï" Or xR = "¬ü¤¤«n°Ï" Or xR = "¬£ªO¹ïÀ³³æ¾Ú¤é" Or xR = "¬£ªO-¥æªO®t²§" Or xR = "¬£ªOµ²¾l" Or xR = "½LÂI®t²§"
«o¤£ª¾¦p¦ó¤U¤â,¥H¤U¬O­ìµ{¦¡, ¥i§_À°¦£¬Ý¤U,­n¦p¦ó­×§ï?   ¥þ¬Ù®Ö¾P©ú²Ó.rar (35.18 KB)
  1. Sub ¥_°Ï_¤½¦¡§ó·s()
  2. Dim Sh As Worksheet, xS As Worksheet, xR
  3. Set xS = ThisWorkbook.Sheets("VBA")  'µ{¦¡¨Ó·½
  4. Set Sh = Workbooks("¥þ¬Ù®Ö¾P©ú²Ó.xlsx").Sheets("¥_°Ï")
  5. d = xS.[AA3] 'Date
  6. Sh.Activate
  7. '------------ 'A ¨ú¥XBÄæ¦~.¤ë,³o¬q¬O¨Ì·Ó±zªºµ{¦¡­×§ï«áªº
  8. For Each xR In Range([b3], [b65535].End(3)) '¦V¤W End(3) = End(xlup).Row
  9.     If xR >= d Then
  10.         x = Year(xR) & ".." & Month(xR)
  11.         xR.Offset(, -1) = x
  12.     End If
  13. Next
  14. '------------
  15. xRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
  16. Range("E3:E" & xRow).Formula = "=IF(¥_°Ï!$R3="","µL¥æ³f",¥_°Ï!$T3&¥_°Ï!$S3&¥_°Ï!$R3)"
  17. Range("E3:E" & xRow).Value = Range("E3:E" & xRow).Value

  18. Range("K3:K" & xRow).Formula = "=K$2+SUM(G$3:G3)-SUM(F$3:F3)-SUM(H$3:I3)+SUM(J$3:J3)" 'µ²¾l
  19. Range("K3:K" & xRow).Value = Range("K3:K" & xRow).Value

  20. Range("L3:L" & xRow).Formula = "=L$2+SUMIF($C$3:$C3,L$1,$G$3:$G3)-SUMIF($C$3:$C3,L$1,$F$3:$F3)+SUM(J$3:J3)-SUM(N$3:N3)"
  21. Range("L3:L" & xRow).Value = Range("L3:L" & xRow).Value

  22. Range("M3:M" & xRow).Formula = "=M$2+SUMIF($C$3:$C3,M$1,$G$3:$G3)-SUMIF($C$3:$C3,M$1,$F$3:$F3)-SUM(O$3:O3)"
  23. Range("M3:M" & xRow).Value = Range("M3:M" & xRow).Value

  24. Range("N3:N" & xRow).Formula = "=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),"")))"
  25. Range("N3:N" & xRow).Value = Range("N3:N" & xRow).Value

  26. Range("O3:O" & xRow).Formula = "=IF(COUNTIF(¥_°Ï!$B$3:$B3,¥_°Ï!$B3)=1,SUMIFS(¥x¤¤!$D:$D,¥x¤¤!$B:$B,"¬ü",¥x¤¤!$A:$A,¥_°Ï!$B3),IF(COUNTIF(¥_°Ï!$B$3:$B3,¥_°Ï!$B3)=2,SUMIFS(·s¦Ë!$D:$D,·s¦Ë!$B:$B,"¬ü",·s¦Ë!$A:$A,¥_°Ï!$B4),IF(COUNTIF(¥_°Ï!$B$3:$B3,¥_°Ï!$B3)=3,SUMIFS(«n°Ï!$D:$D,«n°Ï!$B:$B,"¬ü",«n°Ï!$A:$A,¥_°Ï!$B5),"")))"
  27. Range("O3:O" & xRow).Value = Range("O3:O" & xRow).Value

  28. Range("U3:U" & xRow).Formula = "=IF(OR($D3=""¤¤©M"",$D3=""¤º´ò"",$D3=""¦Á¤î""),$B3,$B3+1)" '¬£ªO¹ïÀ³³æ¾Ú¤é
  29. Range("U3:U" & xRow).Value = Range("U3:U" & xRow).Value

  30. Range("V3:V" & xRow).Formula = "=IF(COUNTIFS(B$3:B3,B3,D$3:D3,D3)=1,SUMIFS(X:X,B:B,B3,D:D,D3)-SUMIFS(F:F,B:B,V3,D:D,D3),0)" '¬£ªO-¥æªO¼Æ®t²§
  31. Range("V3:V" & xRow).Value = Range("V3:V" & xRow).Value

  32. Range("X3:X" & xRow).Formula = "=Y$2+SUM($G$3:$G3)+SUM(J$3:J3)-(SUM($H$3:$H3)+SUM($I$3:$I3)+SUM($X$3:$X3))" '¬£ªOµ²¾l
  33. Range("X3:X" & xRow).Value = Range("X3:X" & xRow).Value

  34. Range("Y3:Y" & xRow).Formula = "=½LÂI®t²§"
  35. Range("Y3:Y" & xRow).Value = Range("Y3:Y" & xRow).Value
  36. End Sub
½Æ»s¥N½X

TOP

¦^´_ 51# jcchiang

±z¦n,
ªí®æ¤W¶Ç®É¦³¤p¤pÅܰʤF®æ¦¡,¤½¦¡§Ñ¤F§ï&¦³³¡¥÷µ{¦¡¤w§ï¦¨§Ú·Q­nªº°õ¦æ¤è¦¡,¤£¹L¤£ª¾¬O¼gªk¤£¦n,©Î¸ê®Æ¤Ó¦h,¶]±o¦³ÂIºC,
¸Õ¹L³æ¤@Äæ±q¦~ªì¸ê®Æ¶}©l§ó·s,µ²ªGºC±o¹³·í¾÷¤@¼Ë,¦pªG¯à«ü¾É¤U§ó§Öªº¼gªk,´N¤Ó¦n¤F¡I
·s¼g¥hÁöµM§¹¦¨,¦ý§Ú¤£ª¾¦p¦ó§ï¬°°}¦C,©Ò¥H¬O¨CÄ檺§ó·s¤À¶}¼g¡I
  1. Sub ¥_°Ï_A_¨ú¦~¤ë()
  2. Dim Sh As Worksheet, xS As Worksheet, xR
  3. Set xS = ThisWorkbook.Sheets("VBA")  'µ{¦¡¨Ó·½
  4. Set Sh = Workbooks("¥þ¬Ù®Ö¾P©ú²Ó.xlsx").Sheets("¥_°Ï")
  5. d = xS.[AA3] 'Date
  6. Sh.Activate
  7. '------------ 'A ¨úBÄæ¦~.¤ë
  8. For Each xR In Range([b3], [b65535].End(3)) '¦V¤W End(3) = End(xlup).Row
  9.     If xR >= d Then
  10.         xR.Offset(, -1) = Year(xR) & ".." & Month(xR)
  11.     End If
  12. Next
  13. End Sub

  14. Sub ¥_°Ï_E_±ÄÁʳ渹½X()
  15. Dim Sh As Worksheet, xS As Worksheet, xR
  16. Set xS = ThisWorkbook.Sheets("VBA")  'µ{¦¡¨Ó·½
  17. Set Sh = Workbooks("¥þ¬Ù®Ö¾P©ú²Ó.xlsx").Sheets("¥_°Ï")
  18. d = xS.[AA3] 'Date
  19. Sh.Activate
  20. '------------ 'E ±ÄÁʳ渹½X
  21. For Each xR In Range([b3], [b65535].End(3))
  22.     If xR.Offset(, 16) = "" Then 'RÄæµL³æ¸¹
  23.     xR.Offset(, 3) = "µL¥æ³f"
  24.     End If
  25.     If xR >= d And xR.Offset(, 16) <> "" Then
  26.     xR.Offset(, 3) = xR.Offset(, 18) & xR.Offset(, 17) & xR.Offset(, 16) 'T&S&R
  27.     End If
  28. Next
  29. End Sub

  30. Sub ¥_°Ï_K_µ²¾l()
  31. Dim Sh As Worksheet, xS As Worksheet, xR
  32. Set xS = ThisWorkbook.Sheets("VBA")  'µ{¦¡¨Ó·½
  33. Set Sh = Workbooks("¥þ¬Ù®Ö¾P©ú²Ó.xlsx").Sheets("¥_°Ï")
  34. d = xS.[AA3] 'Date
  35. Sh.Activate
  36. '------------ 'K µ²¾l
  37. For Each xR In Range([b3], [b65535].End(3))
  38.     If xR >= d Then 'k+g-f-h-i+j
  39.         xR.Offset(, 9) = xR.Offset(-1, 9) + xR.Offset(, 5) - xR.Offset(, 4) - xR.Offset(, 6) - xR.Offset(, 7) + xR.Offset(, 8)
  40.     End If
  41. Next
  42. End Sub

  43. Sub ¥_°Ï_L_¤j_µ²¾l()
  44. Dim Sh As Worksheet, xS As Worksheet, xR
  45. Set xS = ThisWorkbook.Sheets("VBA")  'µ{¦¡¨Ó·½
  46. Set Sh = Workbooks("¥þ¬Ù®Ö¾P©ú²Ó.xlsx").Sheets("¥_°Ï")
  47. d = xS.[AA3] 'Date
  48. Sh.Activate
  49. '------------ 'L ¤j,µ²¾l
  50. For Each xR In Range([b3], [b65535].End(3))
  51.     If xR >= d And xR.Offset(, 1) = "¤j" Then 'l+g-f+j-n
  52.         xR.Offset(, 10) = xR.Offset(-1, 10) + xR.Offset(, 5) - xR.Offset(, 4) + xR.Offset(, 8) - xR.Offset(, 12)
  53.         Else 'l+j-n
  54.         xR.Offset(, 10) = xR.Offset(-1, 10) + xR.Offset(, 8) - xR.Offset(, 12)
  55.     End If
  56. Next
  57. End Sub

  58. Sub ¥_°Ï_M_¬ü_µ²¾l()
  59. Dim Sh As Worksheet, xS As Worksheet, xR
  60. Set xS = ThisWorkbook.Sheets("VBA")  'µ{¦¡¨Ó·½
  61. Set Sh = Workbooks("¥þ¬Ù®Ö¾P©ú²Ó.xlsx").Sheets("¥_°Ï")
  62. d = xS.[AA3] 'Date
  63. Sh.Activate
  64. '------------
  65. For Each xR In Range([b3], [b65535].End(3))
  66.     If xR >= d And xR.Offset(, 1) = "¬ü" Then 'm+g-f-o
  67.         xR.Offset(, 11) = xR.Offset(-1, 11) + xR.Offset(, 5) - xR.Offset(, 4) - xR.Offset(, 13)
  68.         Else 'm-o
  69.         xR.Offset(, 11) = xR.Offset(-1, 11) - xR.Offset(, 13)
  70.     End If
  71. Next
  72. End Sub

  73. Sub ¥_°Ï_U_¬£ªO¹ïÀ³³æ¾Ú¤é()
  74. Dim Sh As Worksheet, xS As Worksheet, xR
  75. Set xS = ThisWorkbook.Sheets("VBA")  'µ{¦¡¨Ó·½
  76. Set Sh = Workbooks("¥þ¬Ù®Ö¾P©ú²Ó.xlsx").Sheets("¥_°Ï")
  77. d = xS.[AA3] 'Date
  78. Sh.Activate
  79. '------------ 'U'¬£ªO¹ïÀ³³æ¾Ú¤é
  80. For Each xR In Range([b3], [b65535].End(3))
  81.     If xR >= d And (xR.Offset(, 2) = "¤¤©M" Or xR.Offset(, 2) = "¤º´ò" Or xR.Offset(, 2) = "¦Á¤î") Then
  82.         xR.Offset(, 19) = xR
  83.         Else
  84.         xR.Offset(, 19) = xR + 1
  85.     End If
  86. Next
  87. End Sub

  88. Sub ¥_°Ï_X_¬£ªOµ²¾l()
  89. Dim Sh As Worksheet, xS As Worksheet, xR
  90. Set xS = ThisWorkbook.Sheets("VBA")  'µ{¦¡¨Ó·½
  91. Set Sh = Workbooks("¥þ¬Ù®Ö¾P©ú²Ó.xlsx").Sheets("¥_°Ï")
  92. d = xS.[AA3] 'Date
  93. Sh.Activate
  94. '------------ 'X'¬£ªOµ²¾l g+j-h-i-w
  95. For Each xR In Range([b3], [b65535].End(3))
  96.     If xR >= d Then 'x+g+j-h-i-w
  97.         xR.Offset(, 22) = xR.Offset(-1, 22) + xR.Offset(, 5) + xR.Offset(, 8) - xR.Offset(, 6) - xR.Offset(, 7) - xR.Offset(, 21)
  98.     End If
  99. Next
  100. End Sub

  101. Sub ¥_°Ï_Y_½LÂI®t²§()
  102. Dim Sh As Worksheet, xS As Worksheet, xR
  103. Set xS = ThisWorkbook.Sheets("VBA")  'µ{¦¡¨Ó·½
  104. Set Sh = Workbooks("¥þ¬Ù®Ö¾P©ú²Ó.xlsx").Sheets("¥_°Ï")
  105. d = xS.[AA3] 'Date
  106. Sh.Activate
  107. '------------ 'Y'½LÂI®t²§
  108. For Each xR In Range([b3], [b65535].End(3))
  109.     If xR >= d And xR.Offset(, 24) = "" Then
  110.         xR.Offset(, 23) = ""
  111.         Else 'z-x
  112.         xR.Offset(, 23) = xR.Offset(, 24) - xR.Offset(, 22)
  113.     End If
  114. Next
  115. End Sub
½Æ»s¥N½X
¥Ø«e¥u³Ñ3Ä檺¤½¦¡,¦]¥]§t¤Fcountifªº¨ç¼Æ,¬d¤F¨Ç¸ê®Æ,¨S§ä¨ìÃö©ócountifªº¨ç¼Æ¦p¦ó§ï¬°VBAªº¼gªk¡I
  1. Range("N3:N" & xRow).Formula = "=¥_°Ï_¤j_¤¤«n°Ï" '=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),"")))
  2. Range("N3:N" & xRow).Value = Range("N3:N" & xRow).Value

  3. Range("O3:O" & xRow).Formula = "=¥_°Ï_¬ü_¤¤«n°Ï" '=IF(COUNTIF(¥_°Ï!$B$3:$B3,¥_°Ï!$B3)=1,SUMIFS(¥x¤¤!$D:$D,¥x¤¤!$B:$B,"¬ü",¥x¤¤!$A:$A,¥_°Ï!$B3),IF(COUNTIF(¥_°Ï!$B$3:$B3,¥_°Ï!$B3)=2,SUMIFS(·s¦Ë!$D:$D,·s¦Ë!$B:$B,"¬ü",·s¦Ë!$A:$A,¥_°Ï!$B4),IF(COUNTIF(¥_°Ï!$B$3:$B3,¥_°Ï!$B3)=3,SUMIFS(«n°Ï!$D:$D,«n°Ï!$B:$B,"¬ü",«n°Ï!$A:$A,¥_°Ï!$B5),"")))
  4. Range("O3:O" & xRow).Value = Range("O3:O" & xRow).Value

  5. Range("V3:V" & xRow).Formula = "=IF(COUNTIFS(B$3:B3,B3,D$3:D3,D3)=1,SUMIFS(W:W,B:B,B3,D:D,D3)-SUMIFS(F:F,B:B,U3,D:D,D3),0)" '¬£ªO-¥æªO®t²§
  6. Range("V3:V" & xRow).Value = Range("V3:V" & xRow).Value
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : §g¤l¬°¥Ø¼Ð¡A¤p¤H¬°¥Øªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD