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

[µo°Ý] ¨Ì«ü©w°Ï¶¡¤é´Á¡B±b¸¹ ¶ñ¤J¸ê®Æ

[µo°Ý] ¨Ì«ü©w°Ï¶¡¤é´Á¡B±b¸¹ ¶ñ¤J¸ê®Æ

¤j¤j­Ì¦n,

1..        ¦b"»¡©ú"¤u§@ªí¤¤¦³4­Ó±b¸¹"¤j¡B¬ü¡B¨Î¡B®¶",²Å¦X±b¸¹¡B¤é´Á(¦P±b¸¹¤Î¤é´Á,¤£¤@©w¥u¦³¤@µ§)
2..        ¥H"»¡©ú"¤u§@ªí¤¤ªº¥æ¹B¤é´Á,³Ì¤j­È&³Ì¤p­È¬°°Ï¶¡,±NMÄ檺¨úªO½s¸¹¡BOÄ檺¹w¬ùªO¼Æ¶ñ¤J"¹w¬ù"¤u§@ªí¤¤ªºG/CÄæ
3..        ¨úªO½s¸¹¶ñ¤J®É,¨Ì­ì¸¹½X,¦b³Ì«e­±¥[¤W#0000(¨Ò:½s¸¹¬°118120,«h¬°#0000118120)
4..        "¹w¬ù"¤u§@ªí¤¤,¤£ÄÝ©ó¥æ¹B¤é´Á°Ï¶¡­Èªº©Ò¦³¸ê®Æ¤Î±b¸¹,³£¤£­n¦³¥ô¦óÅÜ°Ê(¦³¸ê®Æªº,«O¯d­ì¸ê®Æ,µL¸ê®Æ«hªÅ¥Õ)
5..        ¨Ò¦p¡G1/13~1/29¤£¦b¦¹°Ï¶¡,¤w¦³ªº­ì¸ê®Æ¤£¥iÅÜ°Ê,1/15 & 1/18¤]¦³«DÄÝ4¤j±b¸¹,¤]¤£¥i¶ñ¤J¸ê®Æ

½Ð°Ý­n¹F¨ì³o¨Ç­n¨D,¥Î¨ç¼Æorµ{¦¡¥i¥H¹F¦¨?
¹w¬ù¨úªO.rar (21.1 KB)

¦^´_ 1# PJChen
¦³ªÅÀ°§Ú¸Õ¸Õ¬Ý¬O¤£¬O³o¼Ë¤ñ¹ï ·PÁ ^^"
  1. Public Sub ¸ó¤u§@ªí¤ñ¹ï½m²ß()

  2.     Arr = [»¡©ú!I2].CurrentRegion
  3.     Brr = [¹w¬ù!A1].CurrentRegion
  4.    
  5.     For X = 2 To UBound(Arr)
  6.         A = Arr(X, 1) & "-" & Arr(X, 4)
  7.         For Y = 2 To UBound(Brr)
  8.             B = Brr(Y, 2) & "-" & Brr(Y, 1)
  9.             If A = B And Arr(X, 4) <> "" Then
  10.                 Brr(Y, 3) = Arr(X, 7)
  11.                 Brr(Y, 7) = "#0000" & Arr(X, 5)
  12.             End If
  13.         Next Y
  14.     Next X
  15.    
  16.     [¹w¬ù!A1].Resize(UBound(Brr, 1), UBound(Brr, 2)) = Brr

  17. End Sub
½Æ»s¥N½X

TOP

Sub ¹w¬ù§ó·s()
Dim Arr, xD, i&, T$
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([»¡©ú!R1], [»¡©ú!i65536].End(xlUp))
For i = 3 To UBound(Arr)
    If Arr(i, 1) = "" Or IsDate(Arr(i, 4)) Then
       T = Arr(i, 1) & "|" & Arr(i, 4)
       xD(T) = xD(T) + Val(Arr(i, 7)) '¦P¤é¦P¸¹¤£¥u¤@µ§,²Ö¥[
       xD(T & "/m") = "#0000" & Arr(i, 5) '¨úªO½s¸¹
    End If
Next i
Arr = Range([¹w¬ù!G1], [¹w¬ù!A65536].End(xlUp))
For i = 2 To UBound(Arr)
    T = Arr(i, 2) & "|" & Arr(i, 1)
    If xD.Exists(T) Then
       Arr(i, 3) = xD(T)
       Arr(i, 7) = xD(T & "/m")
    End If
Next i
[¹w¬ù!A1].Resize(UBound(Arr), 7) = Arr
End Sub


'==========================

TOP

¦^´_ 3# ­ã´£³¡ªL
¦^´_ 2# °a¤ªºµ

·PÁ¨â¦ì¤j¤j,
¤G­Óµ{¦¡´ú¸Õµ²ªG³£¬Û¦P¡G
¦P¤@¤é´Á¡B¦P±b¸¹¦³¦hµ§¨úªO¸ê®Æ®É,±a¥Xªºµ²ªG¦³»~
¨úªO½s¸¹¥u·|±a¥X³Ì«á¤@µ§,¼Æ¶q·|Åܦ¨²Ö¥[

¥H¤U¹Ï¤ù¬°¶]µ{¦¡µ²ªG v.s. ¥¿½T¸ê®Æ

TOP

¦^´_ 4# PJChen


Sub ¹w¬ù§ó·s()
Dim Arr, xD, i&, T$
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([»¡©ú!R1], [»¡©ú!i65536].End(xlUp))
For i = 3 To UBound(Arr)
    If Arr(i, 1) <> "" And IsDate(Arr(i, 4)) Then
       T = Arr(i, 1) & "|" & Arr(i, 4) & "#0000" & Arr(i, 5)
       xD(T) = xD(T) + Val(Arr(i, 7))
    End If
Next i
Arr = Range([¹w¬ù!G1], [¹w¬ù!A65536].End(xlUp))
For i = 2 To UBound(Arr)
    T = Arr(i, 2) & "|" & Arr(i, 1) & Arr(i, 7)
    If xD.Exists(T) Then Arr(i, 3) = xD(T)
Next i
[¹w¬ù!A1].Resize(UBound(Arr), 7) = Arr
End Sub

TOP

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

§Ú¸Õ¤F´X¦¸,³£µL°Ê§@,¤£ª¾µo¥Í¤°»ò¨Æ...

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2021-1-17 18:24 ½s¿è

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

¸Õ¤F´X¦¸ÁÙ¬O¤£¦æ,½Ð¤j¤jÀ°¦£¬Ý¤Uµ{¦¡¡I
·PÁÂ
¹w¬ù¨úªO.rar (21.46 KB)

TOP

¦^´_ 7# PJChen

¦³ªÅ¦A¸Õ¸Õ¬Ý³o¼Ë¦æ¤£¦æ  ·PÁÂ
  1. Public Sub ¸ó¤u§@ªí¤ñ¹ï½m²ß()

  2.     Arr = [»¡©ú!I2].CurrentRegion
  3.     Brr = [¹w¬ù!A1].CurrentRegion

  4.     For X = 2 To UBound(Arr)
  5.         A = Arr(X, 1) & "-" & Arr(X, 4)
  6.         For Y = X To UBound(Brr)
  7.             B = Brr(Y, 2) & "-" & Brr(Y, 1)
  8.             If A = B And Brr(Y, 3) = "" Then K = 0
  9.             If A = B And Arr(X, 4) <> "" And K <> 1 Then
  10.                 K = 1
  11.                 Brr(Y, 3) = Arr(X, 7)
  12.                 Brr(Y, 7) = "#0000" & Arr(X, 5)
  13.             Exit For
  14.             End If
  15.         Next Y
  16.     Next X

  17.     [¹w¬ù!A1].Resize(UBound(Brr, 1), UBound(Brr, 2)) = Brr

  18. End Sub
½Æ»s¥N½X

TOP

¦^´_ 8# °a¤ªºµ

·PÁ¡I
³o­Óµ{¦¡OK¤F

TOP

¦^´_ 8# °a¤ªºµ
±z¦n,
µ{¦¡¦b¹ê§@®É,µo²{¤F°ÝÃD:°õ¦æµ{¦¡«á,·|±ND:FÄ檺¤½¦¡­È¤Æ,¥B¤£ÄÝ©ó³o­Ó°Ï¶¡ªº¤]³£­È¤Æ¤F,
½Ð°Ý­n¦p¦ó­×§ïµ{¦¡,ÅýD:FÄ椣¨üµ{¦¡¼vÅT,«O«ù­ì¼Ë(¦³¤½¦¡«h«O¯d)¡H
¹w¬ù¨úªO.rar (24.19 KB)

TOP

        ÀR«ä¦Û¦b : ­n¥Î¤ß¡A¤£­n¾Þ¤ß¡B·Ð¤ß¡C
ªð¦^¦Cªí ¤W¤@¥DÃD