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

[µo°Ý] ¦p¦ó²¾°£°}¦C¤º­«½Æªº³¡¤À(STRING)

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-5-18 12:46 ½s¿è

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


«¢«¢¡A·PÁ·ǤjªÈ¥¿~~~ ¬O§Ú¤í¦Ò¼{¤F

­×¥¿µ{¦¡
  1. Sub test()
  2. Arr = Array("XX", "aa", "ab", "ab", "ab", "XX", "ab")
  3. T$ = ""
  4. For Each Item In Arr
  5.   If InStr(" " & T$ & " "," " &  Item & " ") = 0 Then T = Trim(T & " " & Item)
  6. Next
  7. Brr = Split(T, " ")
  8. End Sub
½Æ»s¥N½X
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 8# ML089

·PÁÂM¤j

§A¤@¥ÎKey¨ú¥N­ì¦³ªºE
¦A¥[¤W»¡©ú ¤@¤Á´N²M·¡¤F

²z¸Ñ¦p¤U:
§@ªk¨ä¹ê¬O¥Î¦r¨åªºKey¤£·|­«½Æªº¯S©Ê
§â°}¦C­È¥[¨ì¦r¨åKey­È
³Ì«á¦A¦L¥XKey­È´N¬O¤£­«½Æªºµ²ªG¤F!
¥Î¥\¨ì¥@¬É¥½¤é¨º¤@¤Ñ¡ã¡ã¡ã

TOP

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

·PÁ­㴣³¡ªLª©¥D¼ö¤ß¦^ÂÐ

¬Ý¤F8¼ÓM¤j¸Ñ»¡

©úÁA¤F !

ÁÂÁÂ
¥Î¥\¨ì¥@¬É¥½¤é¨º¤@¤Ñ¡ã¡ã¡ã

TOP

¦^´_ 11# n7822123

¨S¥Î¹LInStr¾Þ§@

¾Ç²ß¤F ·PÁ¤j¤j¤À¨É
¥Î¥\¨ì¥@¬É¥½¤é¨º¤@¤Ñ¡ã¡ã¡ã

TOP

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


    «e½ú¦n
½Ð±Ð«e½ú ªþ¥ó½d¨Ò­n¾ã²z¤£­«½Æ«~¸¹,­þ¸Ì¿ù¤F?

¤£­«½Æ_20210915.zip (297.6 KB)

TOP

¦^´_ 15# Andy2483

    [C1].Resize(D.Count, 1) = D.KEYS
§ï¬°
    [C1].Resize(D.Count, 1) = Application.Transpose(D.KEYS)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 16# ML089


    ÁÂÁ«e½ú«ü¾É
key¬O¾î¦V±Æ¦Cªº,±N¤§Âà¸m«á¤~¦¨¬°Áa¦V±Æ¦C
ÁÂÁ«ü¾É

TOP

¦^´_ 16# ML089


    «e½ú¦n
1.½Ð±Ð¤W¶Ç½d¨Ò¤¤ªº¤£­«½Æ¤é´Á°}¦C¦p¦ó±Æ§Ç
2.½Ð±z«ü¾É°}¦CªºÆ[©À»P»yªk.µ²ºc..µ¥¬O§_¦³¿ù©Î°õ¦æ®Ä²v¦p¦ó´£¤É
3.°õ¦æµ²ªG±N¥Î©ó§e²{²Î­p¹Ïªí
°}¦CÆ[©ÀÃjÃjÀ´À´,½Ð«e½ú­Ì«ü¾É

¤é´Á°}¦C±Æ§Ç_20210916-1.zip (745.65 KB)

TOP

¦^´_ 18# Andy2483

°ÝÃD¡G
1.½Ð±Ð¤W¶Ç½d¨Ò¤¤ªº¤£­«½Æ¤é´Á°}¦C¦p¦ó±Æ§Ç
2.½Ð±z«ü¾É°}¦CªºÆ[©À»P»yªk.µ²ºc..µ¥¬O§_¦³¿ù©Î°õ¦æ®Ä²v¦p¦ó´£¤É
3.°õ¦æµ²ªG±N¥Î©ó§e²{²Î­p¹Ïªí


¦^µª
1.±Æ§Ç¤@¯ë¥i¥H¸ê®Æ¼g¤Jªí®æ«á¡A¦A¥ÎEXCEL±Æ§Ç¥\¯à³B²z¡A°Ñ¦Ò¦p¤U

' ±Æ§Ç J:L ½d³òªº¸ê®Æ
' Key1:=Range("J1")    ¨Ì¾Ú A Äæ±Æ§Ç
' Order1:=xlAscending  ¤É¾­±Æ§Ç
' Header:=xlYes        ¦³¼ÐÃD¦C
Range("J:L").sort Key1:=Range("J1"), Order1:=xlAscending, Header:=xlYes


2.§Ú¤]¬O³Ìªñ¤~¦bºNVBA¤£¾A«ÜÀ´¡A§Ú±`¥Î¨ç¼ÆÆ[©À¦b¼gVBA¦³ÂI©Ç©Çªº¡A§Ú¦Û¤v¤]¦bºN¯Á¤¤¤]¸ò¤j®a¤@¼Ë¬Û¤¬Æ[¼¯¾Ç²ß¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2021-9-17 09:33 ½s¿è

¦^´_ 18# Andy2483

µ{¦¡¥i¥H¤À¬q´¡¤J "®É¶¡¾¹" ¬Ý¬Ý­þ¤@¬q¦³°ÝÃD
®É¶¡¾¹¦p¤Uªí¤ñ¸û **4**¤Î**5**¤ñ¸û¯Ó®É¡A¦A²`¤J±´°Q

¤À¬q        ­ì©l        Àu¤Æ«á       
**1**        0        0.004       
**2**        0.168        0.168       
**3**        0.023        0.023       
**4**        1.859        1.867        ¥¼­×§ï
**5**        2.375        0.023        ­×¥¿«á
**6**        0.246        0.234       

±´°Q**5**µ{¦¡¡A Arr¦³15000¦C¡AD¦r¨å¦³100¦C¡A¥ÎÂù°j°é´N¦³15000*100 ­pºâ¨BÆJ¡A
Arr¬O¸ê®ÆµLªkÁY´î°j°é¡A¦]¦¹¦p¦ó²¤Æ D¦r¨å³o°j°é100¨BÆJ±N¬OÃöÁä
¥i¥H¦Ò¼{±N D¦r¨å ªº­È¥[¤J §Ç¸¹ ´N¥i¥H ´À¥N³o100­Ó°j°é
­ì¥ý 2.375¬í¡A¸g¹LÀu¤Æ«áÅܦ¨ 0.023¡A­è¦n¤Ö100­¿

­×§ï°Ñ¦Ò¦p¤U

Debug.Print "**4**"
Debug.Print Format(Timer - tm, "0.000"): tm = Timer

'
'Set D = Nothing
'Set D = CreateObject("SCRIPTING.DICTIONARY")
'For i = 1 To UBound(Arr)
'   TT = Arr(i, ¥æ³f¤é´ÁÄæC)
'   D(TT) = ""
'Next
'¥æ³f¤é = Application.Transpose(D.KEYS) '½Ð±Ð«e½ú ¤é´Á°}¦C­n«ç»ò±Æ§Ç??
'ReDim KLrr(1 To D.Count, 1 To 2)
'For x = 2 To UBound(Arr)
'   Cdr = Arr(x, ¥æ³f¤é´ÁÄæC)
'   For i = 2 To D.Count
'      Jdr = ¥æ³f¤é(i, 1)
'      If Cdr = Jdr Then
'         KLrr(i, 1) = KLrr(i, 1) + 1
'         KLrr(i, 2) = KLrr(i, 2) + Arr(x, SAP_POÄæM)
'         Exit For
'      End If
'   Next
'Next


Set D = Nothing
Set D = CreateObject("SCRIPTING.DICTIONARY")
For i = 2 To UBound(Arr)
   Cdr = Arr(i, ¥æ³f¤é´ÁÄæC)
   If Not D.Exists(Cdr) Then
        D(Cdr) = D.Count + 1
   End If
Next
¥æ³f¤é = Application.Transpose(D.KEYS) '½Ð±Ð«e½ú ¤é´Á°}¦C­n«ç»ò±Æ§Ç??
ReDim KLrr(1 To D.Count, 1 To 2)
For x = 2 To UBound(Arr)
   Cdr = Arr(x, ¥æ³f¤é´ÁÄæC)
   i = D(Cdr)
   KLrr(i, 1) = KLrr(i, 1) + 1
   KLrr(i, 2) = KLrr(i, 2) + Arr(x, SAP_POÄæM)
Next


Debug.Print "**5**"
Debug.Print Format(Timer - tm, "0.000"): tm = Timer
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¯Ê¤fªºªM¤l¡A¦pªG´«¤@­Ó¨¤«×¬Ý¥¦¡A¥¦¤´µM¬O¶êªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD