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

[µo°Ý] ½Ð°Ý¦p¦óŪ¨úÀx¦s®æ¸Ìªºµüªº¦¸¼Æ?

¦^´_ 20# gaishutsusuru

¸Õ¸Õ¬Ý:
¥¿«h-²Î­p­^¤å³æ¦r-v02.rar (23.58 KB)

»P­ì©«Áö¦³¬ÛÃö, ¦ý»Ý¨D§¹¥þ¤£¦P ,¦³°ÝÃD³Ì¦n¥t¦æ¶}©«, ¨S¦³®É¶¡³o¼Ë¸ò©«~~

TOP

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

­ã´£³¡ªL¤j¤j±z¦n¡A

¯uªºÁÂÁ±zªºÀ°¦£¡C
¤£¦n·N«ä¡A¥H«áµo¤å·|¦A¦h¦hª`·Nªº¡AÁÂÁ±z¡C

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-11-10 08:17 ½s¿è

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


    'ÁÂÁ«e½ú
'³o©«¾Ç¨ì«Ü¦hª¾ÃÑ
'1.=HYPERLINK(), =HYPERLINK(""#xx"",""yy"")==HYPERLINK(""#¤u§@ªí1!xx"",""yy"")
'2.§ó»{ÃÑ ¦r¦êÅܼƸ̫O¯d Âù¤Þ¸¹" ³o¦r¤¸
'3."[^A-Za-z\'-]" ¥¿«h¤å¦r³W«h---«O¯d"­^¤å¦r" + "³æ¤Þ¸¹" + "-"
'4.ºë½TªºÀx¦s®æ¦ì¸m±a¤J°}¦C­È

°õ¦æ«e:


°õ¦æµ²ªG:

Sub TEST_A1()
Dim Arr, Brr, xD, B, i&, j%, Fx$, CT$, T$, T1$, R&, C%, Cx%
'¡ô«Å§iÅܼÆ
Set xD = CreateObject("Scripting.Dictionary")
'¡ô¥O xD¬O¦r¨å
Call ²M°£ '²M°£E¦C¥k¤è¤º®e
'¡ô°õ¦æ Sub ²M°£() °Æµ{¦¡

Arr = Range([h1], [h65536].End(xlUp)).Resize(, 200)
'¡ô¥OArr¬O°}¦C!­Ë¤J[H1]¨ìHÄæ³Ì«á¤@­Ó¦³¤º®eÀx¦s®æ¦A©¹¥kÂX®i200Äæ(¨ì¤FGYÄæ)
For i = 2 To UBound(Arr)
'¡ô³]¶¶°j°é!±q2 ¨ìArr°}¦CÁa¦V³Ì«á¦C¸¹
    T = LCase(Trim(Arr(i, 1)))
    '¡ô¥ý±NArr°}¦Cªº²Ä¤@Äæ°j°é¦C¥h°£«e«áªÅ®æ«á,
    '¦A§â³Ñ¤Uªº¦r¦ê¸Ìªº­^¤å¦r¥ÀÂà¤p¼g
    If T <> "" Then xD(T) = i
    '¡ô¦pªGT¦r¦ê¤£¬OªÅªº!´N±NT¦r¦ê·íkey(Áä)¯Ç¤J¦r¨å, item¬O°j°é¼Æ¤]¬O(¤u§@ªíªº¦C¸¹)  '@@
    'T¤]¬O«á­±µ{§Ç­n¥ÎªºÃöÁä¦r
Next i
Fx = "=HYPERLINK(""#xx"",""yy"")"  '¶W³sµ²¤½¦¡¦@¥Î¦r¦ê--xx´À´«¬°¦ì§}--yy´À´«¬°­nÅã¥Ü¤å¦r
'¡ô¥OFx¦r¦ê¬O "=HYPERLINK("#xx","yy")"
'¡ôÂǦ¹©«¾Ç²ß¦p¦óÅý¦r¦êÅܼƸ̫O¯d Âù¤Þ¸¹" ³o¦r¤¸!
'¬ã¨sµ²ªG:¥ý±q¸Ó¦æµ{¦¡½XÀY§À¦U¥h°£±¼¤@­Ó " ,¥H¤U¬O²Ó¸`
'1.³Ñ¤Uªº¦r¤¸ =HYPERLINK(""#xx"",""yy""),³sÄòªº""·|«O¯d¤@­Ó",¦¨¬°ÅܼƸ̪º"¦r¤¸!
'   ¦¨¬° =HYPERLINK("#xx","yy")
'2.­nª`·N¦pªG¤À°t¨ì³Ì«á³Ñ¤U¤@­Ó"¦r¤¸ ,¬O¤£¤¹³\ªº!
'  ¨Ò¦p MsgBox "1""¬O¿ùªº!¦ý¬OVBA·|À°¸É1­Ó",Åܦ¨ MsgBox "1"""  (¥h°£ÀY§À",³Ñ1"",³Ì«áÅã¥Ü1")
'3.«DÂù¤Þ¸¹¦r¤¸¤§¶¡¤À°t¶¡¹j³Ñ¤U¤@­Ó"¦r¤¸,¤]¬O¤£¤¹³\ªº!
'  ¨Ò¦pMsgBox "1"2"¬O¿ùªº! (¥h°£ÀY§À",³Ñ1"2,¥u³Ñ¤¤¶¡¤@­Ó",·|¥X²{½sĶ¿ù»~°T®§),
'  MsgBox "1"2""¤]¬O¿ùªº!  §ï¬°  MsgBox "12"""  °T®§µ¡ 12",§ï¬°  MsgBox "1""2"  °T®§µ¡ 1"2
'4.MsgBox """1""2""3""4"  ,°T®§µ¡ "1"2"3"4


CT = "[^A-Za-z\'-]"  '¤å¦r³W«h---«O¯d"­^¤å¦r" + "³æ¤Þ¸¹" + "-"
'¡ô¥OCT ¬O¦r¦ê "[^A-Za-z\'-]"  ,
'¦p¦óÅý¦r¦êÅܼƸ̫O¯d ³æ¤Þ¸¹' ³o¦r¤¸?? ¦r¦êªºÀY§À¦³Âù¤Þ¸¹¥]§¨¦í´N¥i¥H
Brr = Range([b1], [a65536].End(xlUp))
'¡ô¥OArr¬O°}¦C!­Ë¤J[B1]»PAÄ榳¤º®eÀx¦s®æ¶¡ ÂX®i¬°³Ì¤p¤è¥¿°Ï°ìÀx¦s®æªº­È
For i = 2 To UBound(Brr)
'¡ô³]¥~¶¶°j°é!±q 2¨ìBrr°}¦CÁa¦V³Ì«á¦C¸¹
    T = Trim(¥¿«hÂà´«(LCase(Brr(i, 2)), " ", CT))
    '¡ô±N¤å¦rÂà¤p¼g,¨Ã¥H¥¿«h±N¤£­nªº¤å¦r´À´«ªÅ®æ«á¶Ç¦^
    '¥OT¬OBrr°}¦C¸Ì²Ä¤GÄæ°j°é¦Cªº³Q³B²z¹Lªº­È! ¦p¦ó³B²z??
    '¥ý±N­ì­È¦r¦êÂà´«¬°¤p¼g°µ¬° ¥¿«hÂà´«()¦Û­q¸q¨ç¼Æªº³Q¥¿«h¦r¦ê,¨ú¥N¤å¦r¬OªÅ¥Õ¦r¤¸,
    '³W«h¦r²Å¬O CT = "[^A-Za-z\'-]"
    '¥¿«h¤§«á¦A¥h±¼¦r¦êÀY§ÀªºªÅ¥Õ¦r¤¸

    For Each B In Split(T, " ") '¥HªÅ®æ¤À©î³æ¦r
    '¡ô³]¤º¶¶°j°é!¥O B ¬O ¤@ºû°}¦C¸Ìªº¤@­û!
    '­þ¨Óªº¤@ºû°}¦C? ¥¿«h¤§«áªº¦r¦ê¥Î ªÅ¥Õ¦r¤¸¤À³Î´N¬O¤@ºû°}¦C

        R = xD(B & "")
        '¡ô¥OBÅܦ¨¦r¦ê«á·íkey(Áä)!¬d¦r¨å¸Ìªºitem¬O¤°»ò? ¥áµ¹R ªø¾ã¼Æ¸ËµÛ!
        '¦pªG¤£¬Oªì©l­È 0 ´N¬Oªø¾ã¼Æ(¤u§@ªíªº¦C¸¹)
'¦b«e­±@@¼Ðµù¦ì¸m
        If R = 0 Then GoTo b01
        '¡ô¦pªG R=0, ªí¥Ü¤£¬OÃöÁä¦r©ÎªÅ®æ. ²¤¹L! ´N¸õ¨ì b01ªº¦ì¸mÄ~Äò°õ¦æ
        T1 = B & "|" & i
        '¡ô¥OT1¦r¦ê¬O B¦r¦ê & "|" & ¥~°j°é¼Æªº²Õ¦X¦r¦ê(¥H¤UºÙB|i²Õ¦X¦r¦ê)!
       'ÃöÁä¦r+i¦C¸¹...¥Î¤_±Æ°£¦P¤@¦C¦r¦ê¥X²{¬Û¦PÃöÁä¦r¤@¦¸¥H¤W
        xD(T1) = xD(T1) + 1
        '¡ô¥O B|i²Õ¦X¦r¦ê ·íkey ,item²Ö¥[ 1
        If xD(T1) > 1 Then GoTo b01
        '¡ô¦P¤@¦r¦ê¥X²{1¦¸¥H¤W, ¤£¦A³B²z, ²¤¹L! ´N¸õ¨ì b01ªº¦ì¸mÄ~Äò°õ¦æ
        Arr(R, 2) = Arr(R, 2) + 1
       '¡ô¥OArr°}¦Cªº²Ä¤GÄæ(ÃöÁä¦r©Ò¦bªº¦C)¦ì¸m²Ö¥[ 1
        C = Arr(R, 2)
       '¡ô¥OC¬O ¦¸¼Æ²Ö­p
        Arr(R, C + 2) = Replace(Replace(Fx, "xx", "A" & i), "yy", Brr(i, 1))
        '¡ô¥Ñ¥ª¦Ó¥k¶ñ¤J"ÃD¸¹"...´À¥N¦¨¶W³s¤½¦¡
       'Arr°}¦C¸ÌÃöÁä¦r©Ò¦bªº¦C¸¹(¦¸¼Æ²Ö­p+2Äæ)ªº¦ì¸m¶ñ¤J Fx³Q³B²z¹Lªº¦r¦ê
        'Fx«ç»ò³Q³B²z??  Fx³oÅܼƦb«e¤è¦­´N³Q©w¸q¬°©T©wÅÜ¼Æ "=HYPERLINK("#xx","yy")"
        '¦b¦¹³B¥u¬O¨C­Ó²Å¦X§PÂ_¦¡ªº°j°é,®³¥¦¥X¨ÓÅܬ°·Q­nªº¶W³sµ²¤½¦¡ªº¤å¦r¦ê
        '¦]¬°¦¹¤å¦r¦êªº«e¤è = ¦b Arr°}¦C¸Ì´N¥u¬O¦r¤¸! ³Ì«á­±µ{§Ç¶K¨ìÀx¦s®æ¸Ì·|Åܦ¨¶W³sµ²¤½¦¡

        If C > Cx Then
       '¡ôCx¦pªG¦¸¼Æ²Ö­p ¤j©ó Cx(³o¤£ª¾¹D¬O¤°»òªºµu¾ã¼Æ??) (Cxªºªì©l­È¬O 0)
           Cx = C
          '¡ôCx¬O­n¸Ë¤J C¦¸¼Æ²Ö­p¥[ 1ªº¼Æ¦r!
          '¦h³o­Ó CxÅܼƥt¤@­Ó¥Øªº¬O¬°¤F­nªº¦b«á­±µ{§Ç¤¤ºë½TªºÀx¦s®æ¦ì¸m¶K¤J­È
           Arr(1, C + 2) = "ÃD¸¹-" & Cx
           '¡ô¼ÐÃD¦C¥[§Ç¸¹
        End If
b01: Next
i01: Next i
Arr(1, 2) = "¦¸¼Æ"
If Cx = 0 Then Exit Sub
'¡ô¦pªG¨S¦³¸ê®Æ!´Nµ²§ô°õ¦æ
With [h1].Resize(UBound(Arr), Cx + 2)
'¡ô±µ¤U¨Ó¬OÃö©ó[H1]Àx¦s®æ¦V¤UÂX®iArr°}¦CÁa¦V³Ì¤j¦C¸¹,¦V¥kÂX®i ¼ÐÃD¦C_ÃD¸¹§Ç¸¹¦A¥[ 2 Äæ
   .Value = Arr
   '¡ô±NArr°}¦C­È±q [h1]¶}©l±a¤J
   .EntireColumn.AutoFit
   '¡ô³o¨ÇÄæ¦ì¦Û°Ê½Õ¾ãÄæ¼e
End With
End Sub

TOP

        ÀR«ä¦Û¦b : ¬Ý§O¤H¤£¶¶²´¡A¬O¦Û¤v­×¾i¤£°÷¡C
ªð¦^¦Cªí ¤W¤@¥DÃD