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

[µo°Ý] ¦C¥X¤£¬Û¦P¼Æ­È©Î¦r¦ê¥X²{¦¸¼Æ

¦^´_ 10# jesscc


    ©ñ¤j¨ú >=5  µ²ªG¤@¼Ë   5   9   99    999   §¡¥i
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß1#½d¨Ò¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

°õ¦æ«e:
20230605_1.jpg
2023-6-5 16:10


°õ¦æµ²ªG:
20230605_2.jpg
2023-6-5 16:10



Option Explicit
Sub TEST_1()
Dim Brr, Crr, Y, R&, i&, j%, N&, T1$, T2$, V1&, V2&
Set Y = CreateObject("Scripting.Dictionary")
[E2:F2000].ClearContents
Brr = Range([B7], Cells(Rows.Count, 1).End(3))
ReDim Crr(1 To UBound(Brr), 1 To 2)
For i = 1 To UBound(Brr)
   T1 = Brr(i, 1): T2 = Brr(i, 2)
   If T1 = "" Or Val(T2) = 0 Then GoTo i01
   V1 = Val(T2): V2 = StrReverse(Split(StrReverse(T2), "-")(0))
   For j = V1 To V2
      If Y(T1) = "" Then N = N + 1: Y(T1) = N: Crr(N, 1) = T1
      If Y(T1 & j) = "" Then
         R = Y(T1): Y(T1 & j) = 1: Crr(R, 2) = Crr(R, 2) + 1
      End If
   Next
i01: Next
If N > 0 Then [E2].Resize(N, 2) = Crr
Set Y = Nothing: Erase Brr, Crr
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¤f»¡¤@¥y¦n¸Ü¡A¦p¤f¥X½¬ªá¡F¤f»¡¤@¥yÃa¸Ü¦p¤f¦R¬r³D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD