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

[µo°Ý] ½Ð±Ð±Æ°£­«½Æ¤H¦W¤§«á¡A­pºâ¤H¼Æ°ÝÃD

[µo°Ý] ½Ð±Ð±Æ°£­«½Æ¤H¦W¤§«á¡A­pºâ¤H¼Æ°ÝÃD

­pºâ¤H¦W.zip (6.69 KB) 1.¸ê®Æ¬O¤j¶qªº¦³¤W¤dµ§¡A¨C¤@­ÓÀx¦s®æ¦³1­Ó¦W¦r¡A¤]¦³2­Ó¦W¦r¡B3­Ó¦W¦r¡B4­Ó¦W¦r¡B5­Ó.....¤£µ¥¡C
2.·Q­n±Æ°£­«½Æªº¤H¦W¤§«á¡AAøó¹ê»Ú¦³´X¤H¡A½Ð¦U¦ì¥ý¶i«ü¾É¡AÁÂÁÂ
julie

¦^´_ 1# julieh96590

­pºâ¤H¦W.rar (7.47 KB)

D5°}¦C¤½¦¡¦p¤U
=IFERROR(TRIM(MID(SUBSTITUTE(INDEX(A:A,INT(MIN((COUNTIF(D$4:D4,TRIM(MID(SUBSTITUTE(A$2:A$19," ",REPT(" ",99)),COLUMN(A:I)*99-98,99)))>0)*9999+ROW(A$2:A$19)*10+COLUMN(A:I))/10))," ",REPT(" ",99)),
MOD(MIN((COUNTIF(D$4:D4,TRIM(MID(SUBSTITUTE(A$2:A$19," ",REPT(" ",99)),COLUMN(A:I)*99-98,99)))>0)*9999+ROW(A$2:A$19)*10+COLUMN(A:I)),10)*99-98,99)),"")
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

±Æ°£­«½Æ¤§«á ,Aø󦳴X¤H

55-4185.rar (9.96 KB)

±Æ°£­«½Æ¤§«á ,Aø󦳴X¤H

ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¥Î¦r¨å°O¿ýÄ渹»P¦C¸¹ªº¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
°õ¦æ«e:
20231219_1.jpg
2023-12-19 12:48


°õ¦æµ²ªG:
20231219_2.jpg
2023-12-19 12:48


Option Explicit
Sub TEST_A()
Dim ¸ê®Æ°}¦C, ªÅ°}¦C, ¦r¨å, i&, j%, ¦r¦ê¤À³Î¤@ºû°}¦C, °ÑÁɱo¼úªÌ, ¦W¦¸%
Dim ªÅ°}¦CÄ渹%, ªÅ°}¦C¦C¸¹&, °ÑÁɱo¼ú¤H¼Æ%, ¦W¦¸¼Æ&, °ÑÁɱo¼ú¤H¦¸&
Set ¦r¨å = CreateObject("Scripting.Dictionary")
¸ê®Æ°}¦C = Range([B2], [A65536].End(xlUp))
ReDim ªÅ°}¦C(1000, 1000)
For i = 1 To UBound(¸ê®Æ°}¦C)
   ¦W¦¸ = Val(¸ê®Æ°}¦C(i, 2))
   ªÅ°}¦C¦C¸¹ = ¦r¨å(¦W¦¸)
   If ªÅ°}¦C¦C¸¹ = 0 Then
      ¦W¦¸¼Æ = ¦W¦¸¼Æ + 1
      ªÅ°}¦C¦C¸¹ = ¦W¦¸¼Æ
      ¦r¨å(¦W¦¸) = ªÅ°}¦C¦C¸¹
      ªÅ°}¦C(ªÅ°}¦C¦C¸¹, 0) = ¦W¦¸
   End If
   ¦r¦ê¤À³Î¤@ºû°}¦C = Split(¸ê®Æ°}¦C(i, 1) & " ", " ")
   For Each °ÑÁɱo¼úªÌ In ¦r¦ê¤À³Î¤@ºû°}¦C
      If °ÑÁɱo¼úªÌ = "" Then GoTo i01
      ªÅ°}¦CÄ渹 = ¦r¨å(°ÑÁɱo¼úªÌ)
      If ªÅ°}¦CÄ渹 = 0 Then
         °ÑÁɱo¼ú¤H¼Æ = °ÑÁɱo¼ú¤H¼Æ + 1
         ªÅ°}¦CÄ渹 = °ÑÁɱo¼ú¤H¼Æ
         ¦r¨å(°ÑÁɱo¼úªÌ) = ªÅ°}¦CÄ渹
         ªÅ°}¦C(0, ªÅ°}¦CÄ渹) = °ÑÁɱo¼úªÌ
      End If
      ªÅ°}¦C(ªÅ°}¦C¦C¸¹, ªÅ°}¦CÄ渹) = ªÅ°}¦C(ªÅ°}¦C¦C¸¹, ªÅ°}¦CÄ渹) + 1
      °ÑÁɱo¼ú¤H¦¸ = °ÑÁɱo¼ú¤H¦¸ + 1
i01: Next
Next
With [E4].Resize(¦W¦¸¼Æ + 1, °ÑÁɱo¼ú¤H¼Æ + 1)
   .Value = ªÅ°}¦C
   .Item(1) = "¦W¦¸\°ÑÁɱo¼úªÌ"
   .Borders.LineStyle = xlContinuous
   .EntireColumn.AutoFit
End With
MsgBox "°ÑÁɱo¼ú¤H¼Æ " & °ÑÁɱo¼ú¤H¼Æ & " ¤H , °ÑÁɱo¼ú¤H¦¸ " & °ÑÁɱo¼ú¤H¦¸ & " ¤H¦¸"
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

(¿é¤J½s¸¹12156) googleºô§}:https://hcm19522.blogspot.com/
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¤H¤£©È¿ù¡A´N©È¤£§ï¹L¡A§ï¹L¨Ã¤£Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD