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

§ä¥X¯S©w½d³ò¤º¥¼¦C¥Xªº¼Æ¦r

§ä¥X¯S©w½d³ò¤º¥¼¦C¥Xªº¼Æ¦r

¤j®a¦n¡A¤p§Ì¦A¦¸¨Ó¦V³Â»¶®a±Úªº¤j¤j«ô°Ý

¼Ë¥»¤ºªº¨C¤@¦C¼Æ¦r¡A³£¬O±q1¨ì24ªº¼Æ¦r¤ºªºÀH¾÷¿ï¥X12­Ó¤£­«½Æ¼Æ¦r

½Ð°Ý¸Ó¦p¦ó¿z¿ï¥X¨C¤@¦C¥¼Àò¿ïªº¼Æ¦r¡H

Àµ½Ð³Â»¶®a±Úªº¤j¯«­Ì±oªÅ¥X¤â¸Ñµª¡C¦A¦¸¥ýÁÂÁ¤j®aÀ°¦£¡C

Files (2).zip (7.96 KB)

½d¨ÒÀÉ®×

¦^´_ 1# f00l01

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁ¡C

Sub test()
Dim Arr, xD, i&, j&, x%
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([A1], [L65536].End(3))
For i = 1 To UBound(Arr)
    For x = 1 To 24: xD(x & "") = "": Next
    For j = 1 To UBound(Arr, 2)
        xD.Remove (Arr(i, j) & "")
    Next
    Cells(i, 15).Resize(1, xD.Count) = xD.keys
    xD.RemoveAll
Next
End Sub

TOP

¦¬¨ì¡A§Ú¸Õ¸Õ¬Ý¡AÁÂÁÂ

TOP

¦^´_ 2# samwang


   ¬Ý°_¨Ó¼Æ¦r³£¦³¥X²{¡A¦Ó¥B¨S¦³»Ý­n°»¿ùªº¦a¤è

·PÁ¤j¤jÀ°¦£¡I~

Files (3).zip (143.73 KB)

´ú¸Õµ²ªG

TOP

¦^´_ 4# f00l01


µ²ªGÅã¥Ü¦b¦P¤@®æ¡A½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁ¡C
Sub test2()
Dim Arr, xD, i&, j&, x%
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([A1], [L65536].End(3))
For i = 1 To UBound(Arr)
    For x = 1 To 24: xD(x & "") = "": Next
    For j = 1 To UBound(Arr, 2)
        xD.Remove (Arr(i, j) & "")
    Next
    Arr(i, 1) = Join(xD.keys, ",")
    xD.RemoveAll
Next
Range("n1").Resize(UBound(Arr)) = Arr
End Sub

TOP

¦^´_ 5# samwang


    ³£¦³Åã¥Ü¦b¦P¤@®æ¡A¦A¦¸·PÁÂ

Files (4).zip (212.76 KB)

TOP

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-6-17 10:12 ½s¿è

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú

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

°õ¦æµ²ªG:


Option Explicit
Sub TEST_1()
Dim Brr, i&, j%, y&, x%, V&, u%, R&, C%, A$
Brr = [A1].CurrentRegion: y = UBound(Brr): x = 12
For i = 1 To y
   For j = 1 To x: A = A & "/" & Brr(i, j): Next
   For j = 1 To 24
      If InStr(A & "/", "/" & j & "/") = 0 Then
         C = C + 1: Brr(i, C) = j
      End If
   Next
   A = "": C = 0
Next
[O1].Resize(y, 12) = Brr
End Sub

Sub TEST_2()
Dim Brr, Crr, i&, j%, y&, x%, C%, Z
Set Z = CreateObject("Scripting.Dictionary")
Brr = [A1].CurrentRegion
ReDim Crr(1 To UBound(Brr), 1 To 24)
For i = 1 To UBound(Brr)
   For j = 1 To 24
      If j <= UBound(Brr, 2) Then Z(Brr(i, j)) = 1
      If Z(j) = "" Then: C = C + 1: Crr(i, C) = j
   Next
   Z.RemoveAll: C = 0
Next
[O1].Resize(UBound(Brr), 12) = Crr
End Sub

Sub TEST_3()
Dim Brr, Crr, i&, j%, y&, x%, C%, Z, A%, B%, D%
Set Z = CreateObject("Scripting.Dictionary")
Brr = [A1].CurrentRegion: A = 1
ReDim Crr(1 To UBound(Brr), 1 To 24)
For i = 1 To UBound(Brr)
   For j = 1 To 24
      If j <= UBound(Brr, 2) Then Z(Brr(i, j)) = A
      If Z(j) = B Then: C = C + 1: Crr(i, C) = j: Z(j) = A
   Next
   D = A: A = B: B = D: C = 0
Next
[O1].Resize(UBound(Brr), 12) = Crr
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-11-9 07:13 ½s¿è

¦^´_ 8# Andy2483



ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß´î¤ÖÅܼƪº¨Ï¥Î,½Ð¦U¦ì«e½ú«ü±Ð

Sub TEST_4()
Dim Brr, i&, j%, C%, Z
Set Z = CreateObject("Scripting.Dictionary")
Brr = [A1].CurrentRegion
For i = 1 To UBound(Brr)
   For j = 1 To 24
      If j <= UBound(Brr, 2) Then Z(Brr(i, j)) = i
      If Z(j) <> i Then: C = C + 1: Brr(i, C) = j
   Next
   C = 0
Next
[O1].Resize(UBound(Brr), 12) = Brr
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ hcm19522 ©ó 2023-11-9 11:16 ½s¿è

(¿é¤J½s¸¹11987) googleºô§}:https://hcm19522.blogspot.com/
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

        ÀR«ä¦Û¦b : ¯u¥¿ªº·R¤ß¡A¬O·ÓÅU¦n¦Û¤vªº³oÁû¤ß¡C
ªð¦^¦Cªí ¤W¤@¥DÃD