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

¤T­ÓÄæ¦ì¹ï¤ñ¸ê®Æ¤ÎŪ¨ú¸ê®Æ

¤T­ÓÄæ¦ì¹ï¤ñ¸ê®Æ¤ÎŪ¨ú¸ê®Æ

KH ªí ®Ö¹ï Data ªí

±qKH ªí²Ä3¦æ¶}©l¨ì³Ì«á¤@¦æ¡A¦bData ªí内§ä´M¤U­±¤T­Ó¹ï¤ñ³£¬Û¦Pªº
KH ªíÄæB ¹ï¤ñ Data ªíÄæA
KH ªíÄæC ¹ï¤ñ Data ªíÄæB
KH ªíÄæD ¹ï¤ñ Data ªíÄæC

¤W­±¤T¼Ë³£¬Û¦Pªº¸Ü¡A±NKH ªíªºÄæE ¾É¤JDATA ªíªº¸Ó¦æ¬Û¦P¼Æ¾ÚªºÄæD

¦pªG3¼Ë¦³¥ô¦ó1¼Ë¤£¦Pªº¡A±NKH ªíªºÄæB - D ¾É¤JDATA ªí³Ì«á¤@¦æªºÄæA-D

³Ì«áDATA ÄæE ´N­pºâ¡AÄæD ´î¥h ÄæF - ³Ì«á¤@Ä榳¼Æ¾Úªº

½d¨Òa.rar (19.2 KB)

¦^´_ 1# 198188


¬õ¦â¦r³o¼Ëªº½d¨Ò¡CK00351 & K00371 ¼Æ¶q»Ý­nKHªíªº¥[Á`¼Æ¶q
94099 - 95090 ¦]爲Data ªí¥»¨­¨S¦³ªº¡A´N¦b³Ì«á¤@¦æ¥[¤W¡C
95088  ¼Æ¶q»Ý­nKHªíªº¥[Á`¼Æ¶q

¥t¥~§Ñ¤F¦³­Ó±ø¥ó¡A´N¬O­ì¥»Data ªí¦³¡AKHªí¨S¦³ªº¨º¦æ¡A»Ý­n¶À¦âHIGHLIGHT.

TOP

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤ß±o¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
°õ¦æ«e:


°õ¦æµ²ªG:


Option Explicit
Sub TEST()
Dim Brr, Z, i&, T$
'¡ô«Å§iÅܼÆ:&¬Oªø¾ã¼Æ,$¬O¦r¦êÅܼÆ,¨S¦³«ü©wªº¬O³q¥Î«¬ÅܼÆ
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OZÅܼƬO ¦r¨å
Brr = Range([KH!E3], [KH!A65536].End(xlUp))
'¡ô¥OBrrÅܼƬO¤Gºû°}¦C,¥HÀx¦s®æ­È±a¤J
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é!¥Oi±q1 ¨ìBrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
   T = Trim(Brr(i, 2)) & "/" & Val(Brr(i, 3)) & "/" & Val(Brr(i, 4))
   '¡ô¥OTÅܼƬO3­ÓÄæ¦ìBrr°}¦C­È¥H"/"²Å¸¹¦ê±µ¦¨ªº·s¦r¦ê
   Z(T) = Z(T) + Val(Brr(i, 5))
   '¡ô¥OTÅܼƬ°key,item²Ö¥[²Ä5ÄæBrr°}¦C­È
Next
Brr = Range([Data!G1], [Data!A65536].End(xlUp))
'¡ô¥OBrrÅܼƬO¤Gºû°}¦C,¥H¥tªíÀx¦s®æ­È±a¤J
For i = 2 To UBound(Brr)
'¡ô³]¶¶°j°é!¥Oi±q1 ¨ìBrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
   T = Trim(Brr(i, 1)) & "/" & Val(Brr(i, 2)) & "/" & Val(Brr(i, 3))
   '¡ô¥OTÅܼƬO3­ÓÄæ¦ìBrr°}¦C­È¥H"/"²Å¸¹¦ê±µ¦¨ªº·s¦r¦ê
   Brr(i - 1, 1) = Z(T)
   '¡ô¥OBrr°}¦C²Ä1Äæ¼g¤J¬dZ¦r¨åªºitem­È
Next
With [Data!D2].Resize(UBound(Brr) - 1)
'¡ô¥H¤U¬OÃö©óµ²ªGÀx¦s®æªºµ{§Ç
   .Value = Brr
   '¡ô¥OÀx¦s®æ­È¥HBrr°}¦C­È¼g¤J
   .Offset(, 1) = "=D2-SUM(F2:" & [Data!IV1].End(xlToLeft)(2).Address(0, 0) & ")"
   '¡ô¥Oµ²¾lÄæ¼g¤J¤½¦¡ Åý¦Û°Ê¹Bºâ
End With
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ mdr0465 ©ó 2024-3-1 12:56 ½s¿è

¦^´_ 3# Andy2483

Andy ¾Ç¥S,§A¦n
¤j«á½ú¥¿¦b§V¤O­«·Å©Ò¦³¾Ç¥Sµ¹¦U¦ì½×¾Â«á¾Çªº¸Ô²Óª`¸Ñ,
¦ý¦³¤@ÂI¤j«á½ú¤£©ú¥Õªº,½Ð¾Ç¥S¤£¹½¨ä·Ð«ü¾É«á½ú

½Ð¬Ý¥H¤Uªº¬õ¦â¬O§Ú¤£©ú¥Õªº¦a¤è

Option Explicit
Sub TEST()
Dim Brr, Z, i&, T$
'¡ô«Å§iÅܼÆ:&¬Oªø¾ã¼Æ,$¬O¦r¦êÅܼÆ,¨S¦³«ü©wªº¬O³q¥Î«¬ÅܼÆ
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OZÅܼƬO ¦r¨å
Brr = Range([KH!E3], [KH!A65536].End(xlUp))
'¡ô¥OBrrÅܼƬO¤Gºû°}¦C,¥HÀx¦s®æ­È±a¤J
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é!¥Oi±q1 ¨ìBrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
   T = Trim(Brr(i, 2)) & "/" & Val(Brr(i, 3)) & "/" & Val(Brr(i, 4))
   '¡ô¥OTÅܼƬO3­ÓÄæ¦ìBrr°}¦C­È¥H"/"²Å¸¹¦ê±µ¦¨ªº·s¦r¦ê
   Z(T) = Z(T) + Val(Brr(i, 5))
   '¡ô¥OTÅܼƬ°key,item²Ö¥[²Ä5ÄæBrr°}¦C­È


³o¬OZ(T) (¬O¼g¤Jkey­È) =  Z(T) + Val(Brr(i, 5)) (¬O¼g¤Jitem­È)

Next
Brr = Range([Data!G1], [Data!A65536].End(xlUp))
'¡ô¥OBrrÅܼƬO¤Gºû°}¦C,¥H¥tªíÀx¦s®æ­È±a¤J
For i = 2 To UBound(Brr)
'¡ô³]¶¶°j°é!¥Oi±q1 ¨ìBrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
   T = Trim(Brr(i, 1)) & "/" & Val(Brr(i, 2)) & "/" & Val(Brr(i, 3))
   '¡ô¥OTÅܼƬO3­ÓÄæ¦ìBrr°}¦C­È¥H"/"²Å¸¹¦ê±µ¦¨ªº·s¦r¦ê
   Brr(i - 1, 1) = Z(T)
   '¡ô¥OBrr°}¦C²Ä1Äæ¼g¤J¬dZ¦r¨åªºitem­È

³o¤@¥y, ¬°¦ó³o¼Ëªº½s¼g¬O¼g¤Jitem­È¦Ó¤£¬O¼g¤Jkey­È©O?
Next
With [Data!D2].Resize(UBound(Brr) - 1)
'¡ô¥H¤U¬OÃö©óµ²ªGÀx¦s®æªºµ{§Ç
   .Value = Brr
   '¡ô¥OÀx¦s®æ­È¥HBrr°}¦C­È¼g¤J
   .Offset(, 1) = "=D2-SUM(F2:" & [Data!IV1].End(xlToLeft)(2).Address(0, 0) & ")"
   '¡ô¥Oµ²¾lÄæ¼g¤J¤½¦¡ Åý¦Û°Ê¹Bºâ
End With
End Sub

TOP

¦^´_ 4# mdr0465


    ÁÂÁ«e½ú¤@°_¾Ç²ß,¥H¤U¥N½X½Ð°õ¦æ¬Ý¬Ý
Option Explicit
Sub TEST()
Application.DisplayAlerts = False
Dim Z, T$, V&
Set Z = CreateObject("Scripting.Dictionary")
MsgBox Z.Count
'¡ô¸õ¥X´£¥Üµ¡,Åã¥ÜZ¦r¨å¸Ìkey¼Æ¶q0
T = "ÃöÁä¦r"
V = Z(T)
'¡ôÁÙ¨S±µÄ²¦r¨å¤§«e«á¾Ç»{¬° Z(T)= ??? ³o¼Ëªº»yªk¤~¯à²K¥[key¨ì¦r¨å¸Ì,
'¦ý¬O¦¹¦æ¤w¸g±NT³okey©ñ¤JZ¦r¨å¤¤,item¬OªÅªº   ³o¬O¤ñ¸û¯S§Oªº¦a¤è

MsgBox Z.Count
'¡ô¸õ¥X´£¥Üµ¡,Åã¥ÜZ¦r¨å¸Ìkey¼Æ¶q1

MsgBox Z(T)
'¡ô¸õ¥X´£¥Üµ¡,Åã¥Ü¥HTÅܼƬ°key¬dZ¦r¨å±o¨ìªºitem­È ªÅ¦r¤¸
V = V + 10
Z(T) = Z(T) + V
'¡ô¥OZ¦r¨å¸Ì key¬O TÅܼƪºitem­È + VÅܼÆ
MsgBox Z("ÃöÁä¦r")
'¡ô¸õ¥X´£¥Üµ¡,Åã¥Ü¥H"ÃöÁä¦r"¦r¦ê¬°key¬dZ¦r¨å±o¨ìªºitem­È 10
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 5# Andy2483


    ¦r¨åªº­ì²z¤Î¹B¥Î¦³¨S¦³­þùØ¥i¥H¦³¤ñ¸û¸Ô²ÓªºÁ¿¸Ñ¤Î¥Ü½d¡H

TOP

¦^´_ 5# Andy2483
T = "ÃöÁä¦r"
V = Z(T)
'¡ôÁÙ¨S±µÄ²¦r¨å¤§«e«á¾Ç»{¬° Z(T)= ??? ³o¼Ëªº»yªk¤~¯à²K¥[key¨ì¦r¨å¸Ì,
'¦ý¬O¦¹¦æ¤w¸g±NT³okey©ñ¤JZ¦r¨å¤¤,item¬OªÅªº   ³o¬O¤ñ¸û¯S§Oªº¦a¤è
MsgBox Z.Count
-------------------------------------------------------------------------------------------------------
°õ¦æ§¹ÅÜ1¬O¦]¬°¦r¨åªº¯S©Ê¶Ü?
¦pªG¦b§ïÅܤ@­Ó key ®É¨S¦³µo²{¸Ó key¡A¨º»ò±N³Ð«Ø¤@­Ó·sªº key ¨Ã¥B¨ä¬ÛÃöÁpªº item ³Q³]¸m¬°ªÅ¡C
·PÁÂAndy¤j¤£§[«ü¾É¤À¨É!!

TOP

°Ñ¦Ò(·j´M½s¸¹12459) googleºô§}:https://hcm19522.blogspot.com/
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2024-3-1 14:49 ½s¿è

¦^´_ 7# shuo1125


ÁÂÁ«e½ú¤@°_¾Ç²ß
¬Oªº! ¦r¨å«Ü¯S§O
Option Explicit
Sub TEST()
Dim Z, V&
Set Z = CreateObject("Scripting.Dictionary")
V = Z("ÃöÁä¦r1") + Z("ÃöÁä¦r2") + Z("ÃöÁä¦r3") + Z("ÃöÁä¦r4") + 100
MsgBox Z.Count '¦¹®ÉZ¦r¨å¸Ì¤w¸g¦³4­Ókey
MsgBox Z("ÃöÁä¦r1") + Z("ÃöÁä¦r2") + Z("ÃöÁä¦r3") + Z("ÃöÁä¦r4")
End Sub
­n±`À³¥Î¦r¨å¤~¯à»P¦r¨å¦¨¬°¦n¤Í
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 6# 198188

ÁÂÁ«e½ú¤@°_¾Ç²ß
¨ä¹ê¦pªG¦r¨åkey¤£·|­«½Æ¯S©Ê »P5# ªº¦r¨å¯S©ÊÀ´¤F,±`¹B¥Î´N·|¶i¨B
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ºw¤ô¦¨ªe¡C²É¦Ì¦¨ÅÚ¡A¤Å»´¤vÆF¡A¤Å¥Hµ½¤p¦Ó¤£¬°¡C
ªð¦^¦Cªí ¤W¤@¥DÃD