¤TÓÄæ¦ì¹ï¤ñ¸ê®Æ¤ÎŪ¨ú¸ê®Æ
- ©«¤l
- 430
- ¥DÃD
- 65
- ºëµØ
- 0
- ¿n¤À
- 521
- ÂI¦W
- 30
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-10-30
- ³Ì«áµn¿ý
- 2024-8-13
|
¤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 ´Npºâ¡AÄæD ´î¥h ÄæF - ³Ì«á¤@Ä榳¼Æ¾Úªº |
-
-
½d¨Òa.rar
(19.2 KB)
|
|
|
|
|
|
- ©«¤l
- 430
- ¥DÃD
- 65
- ºëµØ
- 0
- ¿n¤À
- 521
- ÂI¦W
- 30
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-10-30
- ³Ì«áµn¿ý
- 2024-8-13
|
¦^´_ 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. |
|
|
|
|
|
|
- ©«¤l
- 1440
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1464
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-8-22
|
ÁÂÁ½׾Â,ÁÂÁ¦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
|
|
|
|
|
- ©«¤l
- 163
- ¥DÃD
- 45
- ºëµØ
- 0
- ¿n¤À
- 246
- ÂI¦W
- 26
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- ie
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2015-7-8
- ³Ì«áµn¿ý
- 2024-8-3
|
¥»©«³Ì«á¥Ñ 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 |
|
|
|
|
|
|
- ©«¤l
- 1440
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1464
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-8-22
|
¦^´_ 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
|
|
|
|
|
- ©«¤l
- 430
- ¥DÃD
- 65
- ºëµØ
- 0
- ¿n¤À
- 521
- ÂI¦W
- 30
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-10-30
- ³Ì«áµn¿ý
- 2024-8-13
|
¦^´_ 5# Andy2483
¦r¨åªºì²z¤Î¹B¥Î¦³¨S¦³þùØ¥i¥H¦³¤ñ¸û¸Ô²ÓªºÁ¿¸Ñ¤Î¥Ü½d¡H |
|
|
|
|
|
|
- ©«¤l
- 121
- ¥DÃD
- 8
- ºëµØ
- 0
- ¿n¤À
- 170
- ÂI¦W
- 62
- §@·~¨t²Î
- win10
- ³nÅ骩¥»
- Office2016
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2021-1-21
- ³Ì«áµn¿ý
- 2024-8-20
|
¦^´_ 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¤£§[«ü¾É¤À¨É!! |
|
|
|
|
|
|
- ©«¤l
- 1373
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 1383
- ÂI¦W
- 0
- §@·~¨t²Î
- WIN
- ³nÅ骩¥»
- 2007
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2015-9-11
- ³Ì«áµn¿ý
- 2024-9-6
![¼ö¯P°Ñ»P¾±³¹ ¼ö¯P°Ñ»P¾±³¹](images/common/medal7.gif)
|
|
google"EXCEL°g" blog ©Îgoogleºô§}:https://hcm19522.blogspot.com/
|
|
|
|
|
- ©«¤l
- 1440
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1464
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-8-22
|
¥»©«³Ì«á¥Ñ 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
|
|
|
|
|
- ©«¤l
- 1440
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1464
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-8-22
|
¦^´_ 6# 198188
ÁÂÁ«e½ú¤@°_¾Ç²ß
¨ä¹ê¦pªG¦r¨åkey¤£·|«½Æ¯S©Ê »P5# ªº¦r¨å¯S©ÊÀ´¤F,±`¹B¥Î´N·|¶i¨B |
|
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y
|
|
|
|
|