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

[µo°Ý] ½Ð±Ðvba³o¬qµ{¦¡ªº·N«ä

[µo°Ý] ½Ð±Ðvba³o¬qµ{¦¡ªº·N«ä

Dear Sirs

·Q½Ð±Ð¦U¦ì¡A§Ú¤§«e¦bª¾¥G¬Ý¨ìvba¼¶¼gfunction
µ²ªG¦p¤U
v2-7caf172023ddf5f5a5b21b5ca4abc4b3_b.jpg
2018-8-22 16:51

345

v2-eae1c0f5faa55d00c88c6605a2c27c84_b.gif
2018-8-22 16:51

https://zhuanlan.zhihu.com/p/41740899

¥¦ªºµ{¦¡¦p¤U¡A¶¶«K±N§Úªº¸ÑŪ¼¶¼g¦p¤U

Function ´£¨ú­«ÂÐ(rg1 As Range, rg2 As Range)
Dim arr1, arr2   
arr1 = Split(rg1, ",")  '±Nªø¦ê¼Æ¾Ú1¡A«ö³r¸¹©î¤À¦¨¤@²Õ¼Æ¾Ú
arr2 = Split(rg2, ",")  '±Nªø¦ê¼Æ¾Ú2¡A«ö³r¸¹©î¤À¦¨¤@²Õ¼Æ¾Ú   
a = ""
For h = 0 To UBound(arr1)           '³v­Ó¤ñ¹ï¨â²Õ¼Æ¦r  
For i = 0 To UBound(arr2)  
If arr1(h) = arr2(i) Then    a = a & arr2(i) & ","  '±N¨â²Õ¼Æ¾Ú¬Û¦Pªº³¡¤À¯d¤U¡A¥H","¤À¹j¬Û¦P¼Æ¾Ú¡A²Õ¦¨¤@³s¦ê¦r¦ê
End If
Next
Next

Dim arr, d As Object    '­ç°£结ªG¤¤ªº­«ÂЭÈ
Set d = CreateObject("scripting.dictionary") '±Nd³]¬°¤@­Ó¦r¨å
arr = Split(Mid(a, 1, Len(a) - 1), ",")  '±Nªø¦ê¼Æ¾Úa¡A«ö³r¸¹©î¤À¦¨¤@²Õ¼Æ¾Ú¡A¶¶«K±Na¦r¦ê³Ì«á¤@­Ó³r¸¹®ø°£¡C

For i = 0 To UBound(arr)
d(arr(i)) = "" '¤£ª¾¹D¬°¤°»ò­n±Nd(arr(i))³]¬°ªÅ¥Õ
Next i
´£¨ú­«Î` = Join(d.keys, ",") '¬°¤°»ò·|¥Îd.keys¡A¬Ý¤£À´
End Function

¦A³Â·Ð¦U¦ì¤j¼w¡A·Q¤£³q¡A¤]¬d¤£¤Ó¨ì¸ê®Æ¡AÁÂÁ±z¡C

¦^´_ 1# alsr
VBA ªº»¡©ú
Dictionary ª«¥ó
ª«¥ó¡A¥Î©óÀx¦s¸ê®ÆÃöÁä¦r©M¶µ¥Ø¹ï¡C
½Ðª`·N
Dictionary ª«¥ó»P PERL ¬ÛÃö°}¦C¥þµ¥¡C¥i¥H¬O¥ô¦ó«¬¦¡ªº¸ê®Æªº¶µ¥Ø³QÀx¦s¦b°}¦C¤¤¡C¨C­Ó¶µ¥Ø³£»P¤@­Ó°ß¤@ªºÃöÁä¦r¬ÛÃö¡C¸ÓÃöÁä¦r¥Î¨Ó¨ú¥X³æ­Ó¶µ¥Ø¡A³q±`¬O¾ã¼Æ©Î¦r¦ê¡A¥i¥H¬O°£°}¦C¥~ªº¥ô¦ó«¬ºA¡C

Split ¨ç¼Æ
¶Ç¦^¤@­Ó³¯¦C¯Á¤Þ±q¹s¶}©lªº¤@ºû°}¦C¡A¥¦¥]§t«ü©w¼Æ¥Øªº¤l¦r¦ê¡C
  1. Option Explicit
  2. Sub Ex()
  3.     Dim D As Object, KEY As Variant
  4.     Set D = CreateObject("SCRIPTING.DICTIONARY")  '¦r¨åª«¥ó
  5.     D("ABC") = "ISABC"    'ABC ¬O¦r¨åª«¥óªºKEY (ÃöÁä¦r) 'ISABC ¬O¦r¨åª«¥óªºITEM
  6.     D("BCD") = ""
  7.     [a1] = "Excel_VBA"
  8.     Set D("CDE") = [a1]
  9.     MsgBox Join(D.KEYS, ",")
  10.     MsgBox Join(D.ITEMS, ",")
  11.     For Each KEY In D.KEYS
  12.         Debug.Print KEY
  13.     Next
  14.     For Each KEY In D.ITEMS
  15.         Debug.Print KEY
  16.     Next
  17. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

ÁÂÁ±z¡A§Ú¦A½T»{¬Ý¬Ý¡C

TOP

¦A½Ð±Ð¤@¤U¡A¦bdictionary¤¤¡A©Ò¦³ªº"key"¬Ò¬°³æ¤@©Ê¡A¤£¯à­«ÂжܡAÁÂÁ±z¡C

TOP

¦^´_ 4# alsr

¹ï¡A¤£¯à­«½Æ¡C

TOP

¦^´_ 5# a5007185

·PÁ±z¡C

TOP

        ÀR«ä¦Û¦b : ½_ÁJµ²±o¶V¹¡º¡¡A¶V·|©¹¤U««¡A¤@­Ó¤H¶V¦³¦¨´N¡A´N­n¶V¦³Á¾¨Rªº¯ÝÃÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD