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

[µo°Ý] ¶ñ¤J¼t¯Ê&µù¸Ñ

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

­ã¤j,
¦P¤@ÀÉ®×,¦³¨ä¥LÄæ¦ì,¤]»Ý¥Î¨ìÃþ¦üªºµù¸Ñ¸ü¤J,¦ý¦]¬°¬O¤£¦P®É¶¡µo¥Í,»Ý­n³æ¿Wµ{¦¡, ¸ü¤J¹L¤¹¤é¦L.rar (820.83 KB)
³W«h¦p¤U¡G
³Ì·s®w¦sB H1 ¬°4/21,«h¹ïÀ³¤ë®w¦sªí.xlsxªºsheet 21
³Ì·s®w¦sB ED¹ïÀ³ ¤ë®w¦sªíAU
³Ì·s®w¦sB EE¹ïÀ³ ¤ë®w¦sªíAV
³Ì·s®w¦sB GA:GDªºCK­È<0«h¬°¹L¤¹¦¬
·íEFÄæ(¦^¼t¼Æ­È)ªº¦X­p>0,
«h´M§äGA:GDªºCK­È<0ªºÄæ¦ì
EX:
EF17>0
¥BGA17ªºCK1<0
«hGE17ªº¤é´Á1´N¸ü¤J¤ë®w¦sªíªº¬Û¹ïÄæ¦ì
¦bAU:AVÄæ,¼W¥[µù¸Ñ¤å¦r "¤é¦L5/9"

TOP

¦^´_ 3# ­ã´£³¡ªL
¸É¥R¡G
³W«h¦p¤U¡G
³Ì·s®w¦sB H1 ¬°4/21,«h¹ïÀ³¤ë®w¦sªí.xlsxªºsheet 21
³Ì·s®w¦sB ED¹ïÀ³ ¤ë®w¦sªíAU
³Ì·s®w¦sB EE¹ïÀ³ ¤ë®w¦sªíAV
³Ì·s®w¦sB GA:GDªºCK­È<0«h¬°¹L¤¹¦¬(¦³¥i¯à¦hµ§<0,©Ò¥HGA:GDªºCK­È,­n¯à¥þ³¡³£À˵ø)
·íEFÄæ(¦^¼t¼Æ­È)ªº¦X­p>0,
«h´M§äGA:GDªºCK­È<0ªºÄæ¦ì
¦P®É³Ì·s®w¦sB GQ:GTªº¤p­pÄæ¦ì,¤]¬O¹ïÀ³GA:GDªºCK­È
·íGA:GDªºCK­È<0,«h¥i¨úGQ:GTªº¤p­p...¬°¹L¤¹¼Æ¶q

°²³]¡G
EF17>0
CK_1 >0 (µL¹L¤¹,¤£¤©­p¤J)
CK_2 = 0  (µL¹L¤¹,¤£¤©­p¤J)
CK_3 < 0 (¹L¤¹,«hGS17ªº¤p­p3¼Æ­È¬°¹L¤¹)
CK_4 ªÅ¥Õ  (ªí¥ÜµL¼Æ¾Ú,¤£¤©­p¤J)
GQ:GTªº¤p­pÄæ¦ìªº©Ò¦³¹L¤¹¦X­p = EFÄ檺¦^¼t¦X­p
¹L¤¹¸ê®Æ¸ü¤J ¤ë®w¦sªíªº¬Û¹ïÄæ¦ì ¼W¥[µù¸Ñ
¦bAU:AVÄæ,¼W¥[µù¸Ñ¤å¦r(°²³]­È) "¤é¦L5/9 * 12"
­Y¦³¦hµ§¹L¤¹¼Æ,«h´«¦æ
¤é¦L5/9 * 12
¤é¦L5/10 * 10
....¨Ì¦¹Ãþ±À

TOP

¦^´_ 3# ­ã´£³¡ªL
­ã¤j¦n,

¦]¬°¤ë®w¦sªíªº¦WºÙ¨C­Ó¤ë³£·|¤£¦P,§Ú·Q§ï¬°¶}±ÒÀɮפ覡¬° *¤ë®w¦sªí*
½Ð°Ýµ{¦¡­n¦p¦ó­×§ï?

TOP

¦^´_ 13# PJChen


ÀɮצWºÙ¥Î«ü©wÀx¦s®æ¿é¤J¤º®e¨Ó¦s¨ú~~

TOP

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

­ã¤j,
¨C¤ë·|ÅܰʪºÀɦW,«ü©wÀx¦s®æ§@¥Î¤£¤j,§ÚÁÙ¬O·Q¥Î*¤ë®w¦sªí*ªº¶}±Ò¤è¦¡,
²¤­×§ïµ{¦¡,¥i¥H¶}±ÒÀÉ®×,¦ýµLªk¸ü¤J¼t¯Ê,¥i§_À°¦£¬Ý¤U°ÝÃD©Ò¦b?
  1. Sub ¸ü¤J¼t¯Êµù¸Ñ()
  2. Dim Sht As Worksheet, PH$, FN$, xB As Workbook, xS As Worksheet
  3. Dim xD, xR As Range, Arr, R&, C&
  4. Application.ScreenUpdating = False
  5. Application.Calculation = xlManual
  6. Set Sht = Workbooks("³Ì·s®w¦sB.xlsx").Sheets("­¸¤ñ")  '¨Ó·½
  7. PH = "T:\½d¨Ò\"
  8. FN = Dir(PH & "*¤ë®w¦sªí*.xlsx")
  9. Do While FN <> ""
  10. On Error Resume Next: Set xB = Workbooks(FN): On Error GoTo 0
  11. If xB Is Nothing Then Set xB = Workbooks.Open(PH & FN) '¤½¥Î®w¦sªí
  12. FN = Dir
  13. Loop

  14. xB.Activate '¤½¥Î®w¦sªí
  15. On Error Resume Next: Set xS = xB.Sheets(Day(Sht.[H1]) & ""): On Error GoTo 0  '¤ñ¹ï¤é´Á
  16. If xS Is Nothing Then MsgBox "¡e¤é®w¦sªí¡f¤£¦s¦b! ": Exit Sub
  17. If xS.[J2] = "***" Then MsgBox "¥»¤é®w¦s¤w¦©°£! ": Exit Sub
  18. '-------------------------------------
  19. Set xD = CreateObject("Scripting.Dictionary") '°O¿ý[¨Ó·½]Äæ¦ì¸m
  20. For Each xR In Range(xS.[E4], xS.[E65536].End(xlUp)) '¤½¥Î®w¦sªí
  21.     If xR <> "" Then xD(xR & "") = xR.Row
  22. Next
  23. For Each xR In xS.[J3:AZ3] '°O¿ý¤½¥Î®w¦sªí[°Ó«~¦WºÙ]¦C¦ì¸m
  24.     If xR = "" Or xR = "¦X­p" Then Exit For
  25.     xD(xR & "") = xR.Column
  26. Next
  27. '-------------------------------------
  28. R = Sht.[H65536].End(xlUp).Row  '¥H¨Ó·½[°Ó«~¦WºÙ]¬°¸ê®Æ¦C¼Æ
  29. Arr = Sht.Range("BJ4:CB" & R)  '¨Ó·½¼t¯Ê½d³ò
  30. For i = 1 To UBound(Arr)
  31.     R = xD(Sht.[H4].Cells(i, 1) & "") '¨ú±o¨Ó·½[°Ó«~¦WºÙ]¦C¦ì¸m
  32.     If R = 0 Then GoTo i01
  33. For j = 1 To UBound(Arr, 2)
  34.     C = xD(Sht.[BJ3].Cells(1, j) & "") '¨ú±o[¨Ó·½]Äæ¦ì¸m
  35.     If C = 0 Then GoTo j01
  36.     If Val(Arr(i, j)) = 0 Then GoTo j01
  37.     Set xR = xS.Cells(R, C)
  38. '    xR = Val(xR) - Arr(i, j) '¸ü¤J¼t¯Ê,Åܦ¨­È
  39.     xR = "=" & Val(xR) & "-" & Arr(i, j)  '¸ü¤J¼t¯Ê
  40.     xR.NoteText "PJ:" & Chr(10) & "¼t¯Ê*" & Arr(i, j) 'µù¸Ñªº¤º®e,Chr(10)´«¦æ
  41.     With xR.Comment.Shape 'µù¸Ñªº®Ø¤j¤p
  42.         .Top = xR.Top
  43.         .Left = xR.Cells(1, 2).Left + 1
  44.         .Height = xR.Height + 12
  45.         .Width = 50
  46. '        .TextFrame.Characters.Font.Size = 9   '¦Û­q¦rÅé¤j¤p,¦b®a¥i¥H¹B§@,¦ý¤½¥q¤£¦æ
  47.         '.Shadow.Visible = False '¨ú®ø³±¼v
  48.     End With

  49. j01: Next j
  50. i01: Next i
  51. xS.[J2] = "***" '¦©°£®w¦s¥H3¬Pµù°O(ÁקK­«ÂЦ©°£)
  52. Application.Calculation = xlCalculationAutomatic
  53. Application.GoTo xS.[J3]  '¤½¥Î®w¦sªí°_©l¦C¦ì¸m
  54. End Sub
½Æ»s¥N½X

TOP

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

­ã¤j,
¥t¤@Ãþ¦ü«e­±ªºµù¸Ñ¸ü¤J,¦ý¦]¬°¤£¦P®É¶¡µo¥Í,»Ý­n³æ¿Wµ{¦¡,
³o­Óµ{¦¡ªº³W«h¤ñ¸û¦h,§Ú¤£ª¾¹D«ç»ò¤U¤â,¤]½ÐÀ°¦£....
­«·s¾ã²z³W«h¦p¤U¡G(µ{¦¡»P ¤ë®w¦sªí & ³Ì·s®w¦sB ¬O¤À¶}ªºÀÉ®×)   ¸ü¤J¹L¤¹¤é¦L.rar (822.24 KB)
1) ¤ë®w¦sªíAU:AVÄ檺¼Æ¾Ú,·|¦Û¦æ¶ñ¤J,¥u­n¼W¥[µù¸Ñ´N¦n,¶}±Ò¤è¦¡¥Î *¤ë®w¦sªí*

2) ¤é´Á¹ïÀ³¡G¨Ó·½: ³Ì·s®w¦sB H1 ¬°4/21,«h¹ïÀ³ ¥ØªºÀÉ¡G¤ë®w¦sªí.xlsxªºsheet 21

3) CK_1~CK_4<0,¬°¹L¤¹,«h§â¹ïÀ³¸ê®Æ,¦b¤ë®w¦sªí·s¼Wµù¸Ñ
³Ì·s®w¦sB.xlsxªº¹ïÀ³¸ê®Æ¦p¤U,
CK_1 ¹ïÀ³ ¤é´Á1(GE) ¹ïÀ³ ¤p­p1(GQ)               
CK_2 ¹ïÀ³ ¤é´Á2(GH) ¹ïÀ³ ¤p­p2(GR)
CK_3 ¹ïÀ³ ¤é´Á3(GK) ¹ïÀ³ ¤p­p3(GS)
CK_4 ¹ïÀ³ ¤é´Á4(GN) ¹ïÀ³ ¤p­p4(GT)

4) ³Ì·s®w¦sB.xlsxªº ED:EEÄ榳¼Æ¾Úªº¸Ü,«h ¤ë®w¦sªí.xlsxªº AU:AV¤]·|¶ñ¤J¦P¼Ë¼Æ¾Ú,¥B­n·s¼Wµù¸Ñ

5) EX1
³Ì·s®w¦sB ªº®Æ¸¹ A1,¨äCK_1 & CK_2ªº­È<0
«h·s¼W¤ë®w¦sªí.xlsxµù¸Ñ:
AUÄæ
¤é¦L5/5 * 36½c
¤é¦L5/7 * 140½c
AVÄæ
¤é¦L5/5 * 5

6) EX2
³Ì·s®w¦sB ªº®Æ¸¹ A16¨äCK_1 ªº­È<0
«h·s¼W¤ë®w¦sªí.xlsxµù¸Ñ:
AVÄæ
¤é¦L5/9 * 215

TOP

¦^´_ 14# ­ã´£³¡ªL
­ã¤j¦n,
Àɮפw¥i¦Û°Ê¶}±Ò,¥t¤@·s»Ý¨D¡G¸ü¤J¹L¤¹¤é¦L,ÁÙ¦³¤@³¡¥÷µLªk¸Ñ¨M¡Gµù¸ÑµLªk¸ü¤J¤é´Á&¦U¦Ûªº¼Æ¶q
ªþ¤WÀÉ®×.... ¸ü¤J¹L¤¹¤é¦L.rar (766.13 KB)
ED4:EE4¦³¦^¼t¼Æ­È
±N³Ì·s®w¦sBªºED:EE¹L¤¹¼Æ¶q¸ü¤J¤ë®w¦sªíAV:AW
µù¸Ñ¨C­Ó¹L¤¹ªº¤é¦L¤Î¼Æ¶q
EX:1
¤ë®w¦sªíAV4Åã¥Üµù¸Ñ¬°:
¤é¦L5/5 *36½c
¤é¦L5/7 *140½c

¤ë®w¦sªíAW4Åã¥Üµù¸Ñ¬°:
¤é¦L5/5 *5

GA64<0
EX:2
¤ë®w¦sªíAV4Åã¥Üµù¸Ñ¬°:
¤é¦L 5/9 *3½c+3
  1. Sub ¸ü¤J¹L¤¹µù¸Ñ()
  2. Dim Sht As Worksheet, PH$, FN$, xB As Workbook, xS As Worksheet
  3. Dim xD, xR As Range, arr, R&, C&
  4. Application.ScreenUpdating = False
  5. Application.Calculation = xlManual
  6. Set Sht = Workbooks("³Ì·s®w¦sB.xlsx").Sheets("­¸¤ñ")  '¨Ó·½
  7. PH = "T:\¨p\½d¨Ò\VBA\¸ü¤J¹L¤¹¤é¦L\"
  8. FN = Dir(PH & "*¤ë®w¦sªí*.xlsx")
  9. Do While FN <> ""
  10. On Error Resume Next: Set xB = Workbooks(FN): On Error GoTo 0
  11. If xB Is Nothing Then Set xB = Workbooks.Open(PH & FN)
  12. FN = Dir
  13. Loop
  14. xB.Activate '¤½¥Î®w¦sªí
  15. On Error Resume Next: Set xS = xB.Sheets(Day(Sht.[H1]) & ""): On Error GoTo 0  '¤ñ¹ï¤é´Á,xS =¤½¥Îªº·í¤é¤u§@ªí
  16. If xS Is Nothing Then MsgBox "¡e¤é®w¦sªí¡f¤£¦s¦b! ": Exit Sub
  17. If xS.[AV2] = "***" Then MsgBox "¥»¤é¹L¤¹¤w¦©°£! ": Exit Sub
  18. '-------------------------------------
  19. Set xD = CreateObject("Scripting.Dictionary") '°O¿ý[¨Ó·½]Äæ¦ì¸m
  20. For Each xR In Range(xS.[B4], xS.[C65536].End(xlUp)) '¤½¥Î®w¦sªí,®Æ¸¹¬°¹ï¶H
  21.     If xR <> "" Then xD(xR & "") = xR.Row
  22. Next
  23. For Each xR In xS.[AV3:AW3] '°O¿ý¤½¥Î®w¦sªí[®Æ¸¹]¦C¦ì¸m
  24.     If xR = "" Or xR = "¦X­p" Then Exit For
  25.     xD(xR & "") = xR.Column
  26. Next
  27. '-------------------------------------
  28. R = Sht.[F65536].End(xlUp).Row  '¥H¨Ó·½[®Æ¸¹]¬°¸ê®Æ¦C¼Æ
  29. arr = Sht.Range("ED4:EE" & R)  '¨Ó·½¹L¤¹
  30. For i = 1 To UBound(arr)
  31.     R = xD(Sht.[E4].Cells(i, 1) & "") '¨ú±o¨Ó·½[®Æ¸¹]¦C¦ì¸m
  32.     If R = 0 Then GoTo i01
  33. For j = 1 To UBound(arr, 2)
  34.     C = xD(Sht.[ED3].Cells(1, j) & "") '¨ú±o[¨Ó·½]Äæ¦ì¸m
  35.     If C = 0 Then GoTo j01
  36.     If Val(arr(i, j)) = 0 Then GoTo j01
  37.     Set xR = xS.Cells(R, C)
  38. '    xR = Val(xR) - Arr(i, j) '¸ü¤J¹L¤¹,Åܦ¨­È
  39.     xR = arr(i, j)  '¸ü¤J¹L¤¹

  40.     xR.NoteText "PJ:" & vbCrLf & "¤é¦L *" & arr(i, j) 'µù¸Ñªº¤º®e,vbCrLf´«¦æ
  41.     With xR.Comment.Shape 'µù¸Ñªº®Ø¤j¤p
  42.         .Top = xR.Top
  43.         .Left = xR.Cells(1, 2).Left + 1
  44.         .Height = xR.Height + 24
  45.         .Width = 60
  46.     End With

  47. j01: Next j
  48. i01: Next i

  49. xS.[AV2] = "***" '¦©°£¹L¤¹¥H3¬Pµù°O(ÁקK­«ÂЦ©°£)
  50. Application.Calculation = xlCalculationAutomatic
  51. Application.GoTo xS.[J3]  '¤½¥Î®w¦sªí°_©l¦C¦ì¸m
  52. End Sub
½Æ»s¥N½X

TOP

¦^´_ 17# PJChen

Á`¬O¬Ý¤£À´³W«h, ºN¯Á°µ, ¦Û¦æ¥h­×§ï:
¸ü¤J¹L¤¹¤é¦Lv01.rar (190.13 KB)

TOP

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

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

ÁÂÁ­ã¤j¦Ê¦£¤¤À°¦£,§Ú¤]¤£ª¾¹D§ï¤£§ï±o¦¨! ¥i§_À°¦£¸Ñ»¡³o¬qªº·N«ä?

T1 = "": T2 = ""
        If Val(Brr(i, j * 2 + 1)) < 0 Then _
           T1 = Format(Crr(i, j * 6 + 1), "¤é¦Lm/d-") & Format(Crr(i, j * 6 + 2), "0½c") & Format(Crr(i, j * 6 + 3), "+0;;#;")
        If Val(Brr(i, j * 2 + 2)) < 0 Then _
           T2 = Format(Crr(i, j * 6 + 4), "¤é¦Lm/d-") & Format(Crr(i, j * 6 + 5), "0½c") & Format(Crr(i, j * 6 + 6), "+0;;#;")
        TT = Replace(Replace(Trim(T1 & " " & T2), " ", vbCrLf), "-", " *")
§Æ±æ§Ú¯à°÷¸Ñ»¡±o§ó²M·¡¨Ç,Àɮפ¤§Ú¼W¥[¤F¤@¨Ç½d¨Ò,§Æ±æ¦³À°§U¡I ¸ü¤J¹L¤¹¤é¦L_2.rar (772.3 KB)
ED:EE¦³¼Æ­È®É,¤~­n¶ñ¤Jµù¸Ñ
·íED:EE¦³¼Æ­È®É,«h§P§OGA:GD ¼Æ­È<0ªº¨º¤@²Õ,´N¬O¶ñ¤Jµù¸Ñªº¨Ó·½
µù¸Ñ¼g¤J¨Ó·½¥HEEÄ欰¥D,EDÄ欰¤â°ÊÄæ¦ì,·í¤u¼t¦³«ü¥Ü­n°h¦^®É,EDÄæ¤~·|¥X²{¼Æ­È,¥B¤j¦h¥H¾ã½c¬°³æ¦ì

TOP

¦^´_ 19# PJChen


Á`¬Ý¤£À´»Ý¨D³W«h, ¦Û¦æ§ï§ï:
¸ü¤J¹L¤¹¤é¦Lv02.rar (179.24 KB)

TOP

        ÀR«ä¦Û¦b : µêªÅ¦³ºÉ¡D§ÚÄ@µL½a¡AµoÄ@®e©ö¦æÄ@Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD