singo1232001

¦^´_ 13# oak0723-1

excel 2019
2017¦~ªº¹q¸£  r7-1700x  °O¾ÐÅé3000 cpu99%

10­¶ ¦@40¸Uµ§ 36¬í

Àɮפj¤p 336MB
https://drive.google.com/file/d/1dRNroDrAKxhnoKg9qp9IJhO7pemwGh6j/view?usp=sharing

¦hªº³¡¤À³£§R¤F  IN ¥kÃ䪺³£§R¤F  ¤ÓÃe¤j

­n¤j¶q¸ê®Æ ´N­nºÉ¶q¤£¥Î¥ô¦óÃC¦â®æ¦¡

singo1232001

¦^´_ 16# oak0723-1

§ï¦n¤F ·|¦bµ{§Ç¸Ì§ó·s
¤£¹Lªí¤W¤£Åܧ󠠭Y·Q­n¥Î=¸¹§ó·s01~10[I3~IN3] ÀÉ®×·|ÅܺC  
¥Ø«e01~10[I3:IN3]¬Æ¦Ü§R±¼¤]¥i¥H


    Sub test()
TT = Timer
Set sh0 = Sheets("¿é¤J")
ReDim ar(1 To Sheets.Count - 1): w = 1
ReDim ar0(1 To Sheets.Count - 1)
ReDim ar1(1 To Sheets.Count - 1)
sh0.[i5:T1048576].ClearContents
For Each Z In Sheets
If Z.Name <> "¿é¤J" Then
ar(w) = Z.Range("I5:IN" & Z.Cells(Rows.Count, 2).End(3).Row)
  ar0(w) = sh0.[I3:IN3]
   ReDim ir(1 To UBound(ar(w)), 0)
   ar1(w) = ir
    If MaxR < UBound(ar(w)) Then MaxR = UBound(ar(w))
     w = w + 1
End If
Next

ReDim ar2(1 To MaxR, 0)
For i = 1 To MaxR: ar2(i, 0) = 0: Next

For i = 1 To UBound(ar)
    For j = 1 To UBound(ar(i))
        For k = 1 To 240
        If ar(i)(j, k) = ar0(i)(1, k) Then
        ar1(i)(j, 0) = ar1(i)(j, 0) + 1
        ar2(j, 0) = ar2(j, 0) + 1
        End If
        Next
    Next
Next

For i = 1 To UBound(ar)
sh0.Cells(5, i + 8).Resize(UBound(ar1(i)), 1) = ar1(i)
Next
sh0.[s5].Resize(UBound(ar2), 1) = ar2

Set d = CreateObject("scripting.dictionary"): d.RemoveAll
For i = 1 To UBound(ar2): d(ar2(i, 0)) = "": Next: d0 = d.keys()
  For i = 0 To d.Count - 1
    For j = i + 1 To d.Count - 1
      If d0(i) < d0(j) Then tp = d0(i): d0(i) = d0(j): d0(j) = tp
    Next j:  Next i
ReDim d1(0 To d0(0))
For i = 0 To UBound(d0): d1(d0(i)) = i + 1: Next
  For i = 1 To UBound(ar2): ar2(i, 0) = d1(ar2(i, 0)): Next
   sh0.[T5].Resize(UBound(ar2), 1) = ar2
   
sh0.[A1] = Format(Timer - TT, "0.0")
End Sub

singo1232001

¦^´_ 19# oak0723-1

´ú¸Õ¬Ý¬Ý

    Sub test()
TT = Timer
Set sh0 = Sheets("¿é¤J")
ReDim ar(1 To Sheets.Count - 1): w = 1
ReDim ar0(1 To Sheets.Count - 1)
ReDim ar1(1 To Sheets.Count - 1)
sh0.[i5:T1048576].ClearContents
For Each Z In Sheets
If Z.Name <> "¿é¤J" Then
ar(w) = Z.Range("I5:IN" & Z.Cells(Rows.Count, 2).End(3).Row)
  ar0(w) = sh0.[I3:IN3]
   ReDim ir(1 To UBound(ar(w)), 0)
   ar1(w) = ir
    If MaxR < UBound(ar(w)) Then MaxR = UBound(ar(w))
     w = w + 1
End If
Next

ReDim ar2(1 To MaxR, 0)
For i = 1 To MaxR: ar2(i, 0) = 0: Next

For i = 1 To UBound(ar)
    For j = 1 To UBound(ar(i))
        For k = 1 To 240
        If ar0(i)(1, k) <> "" Then
            If ar(i)(j, k) = ar0(i)(1, k) Then
            ar1(i)(j, 0) = ar1(i)(j, 0) + 1
            ar2(j, 0) = ar2(j, 0) + 1
            End If
        End If
        Next
    Next
Next

For i = 1 To UBound(ar)
sh0.Cells(5, i + 8).Resize(UBound(ar1(i)), 1) = ar1(i)
Next
sh0.[s5].Resize(UBound(ar2), 1) = ar2

Set d = CreateObject("scripting.dictionary"): d.RemoveAll
For i = 1 To UBound(ar2): d(ar2(i, 0)) = "": Next: d0 = d.keys()
  For i = 0 To d.Count - 1
    For j = i + 1 To d.Count - 1
      If d0(i) < d0(j) Then tp = d0(i): d0(i) = d0(j): d0(j) = tp
    Next j:  Next i
ReDim d1(0 To d0(0))
For i = 0 To UBound(d0): d1(d0(i)) = i + 1: Next
  For i = 1 To UBound(ar2): ar2(i, 0) = d1(ar2(i, 0)): Next
   sh0.[T5].Resize(UBound(ar2), 1) = ar2
   
sh0.[A1] = Format(Timer - TT, "0.0")
End Sub

singo1232001

'­Y¨S°ÝÃDªº¸Ü  ³o­Ó¦³µy·L±Æª©¨Ã¤Wµù¸Ñ
Sub test()
TT = Timer
Dim sh0 As Worksheet
Set sh0 = Sheets("¿é¤J")
  ReDim ar(1 To Sheets.Count - 1): w = 1         '¥D°}¦C 1ºû+2ºû¦hÄæ ¤§«á²V¥[10±iªí Á`¸ê®Æ
   ReDim ar0(1 To Sheets.Count - 1)              '¦¸°}¦C1 1ºû+2ºû1Äæ 10±iªí I3:IN3 ¤§«á¤ñ¹ï¥Î
    ReDim ar1(1 To Sheets.Count - 1)             '¦¸°}¦C2 1ºû+2ºû1Äæ 10±iªí ¤§«á²V¥[¦Uªí±o¤À¥[Á`
     sh0.[i5:T1048576].ClearContents
   
For Each Z In Sheets
If Z.Name <> "¿é¤J" Then
  ar(w) = Z.Range("I5:IN" & Z.Cells(Rows.Count, 2).End(3).Row) '¥D°}¦C ²V¥[2ºû¤£³W«h°}¦C
   ar0(w) = sh0.[I3:IN3]
    ReDim ir(1 To UBound(ar(w)), 0)
     ar1(w) = ir
      If MaxR < UBound(ar(w)) Then MaxR = UBound(ar(w))
       w = w + 1
End If
Next

ReDim ar2(1 To MaxR, 0)                         '¦¸°}¦C3 2ºû1Äæ ¥[Á`10±iªíÁ`±o¤À
  For i = 1 To MaxR: ar2(i, 0) = 0: Next

For i = 1 To UBound(ar)                         '®Ú¾Ú¥D°}¦C»P¦¸°}¦C1¤ñ¹ïºâ¤À °O¿ý¦b¦¸°}¦C2
    For j = 1 To UBound(ar(i))                                      '10ªíÁ`¤À¬ö¿ý¦b¦¸°}¦C3
        For k = 1 To 240
         If ar0(i)(1, k) <> "" Then
          If ar(i)(j, k) = ar0(i)(1, k) Then
           ar1(i)(j, 0) = ar1(i)(j, 0) + 1
            ar2(j, 0) = ar2(j, 0) + 1
          End If
         End If
        Next
    Next
Next

For i = 1 To UBound(ar)                          '¦UªíÁ`¤À¨Ì§Ç©ñ¤J[I5:R1048576]
sh0.Cells(5, i + 8).Resize(UBound(ar1(i)), 1) = ar1(i)
Next
  sh0.[s5].Resize(UBound(ar2), 1) = ar2           '¥þ³¡Á`¤À©ñ¤J[S5:S1048576]

Set d = CreateObject("scripting.dictionary"): d.RemoveAll        '¥h­«
  For i = 1 To UBound(ar2): d(ar2(i, 0)) = "": Next: d0 = d.keys()

For i = 0 To d.Count - 1                                         '®ðªw±Æ§Ç
  For j = i + 1 To d.Count - 1
    If d0(i) < d0(j) Then tp = d0(i): d0(i) = d0(j): d0(j) = tp
  Next
Next
  
ReDim d1(0 To d0(0))                                          '±o¤ÀÂà±Æ¦W
For i = 0 To UBound(d0): d1(d0(i)) = i + 1: Next
  For i = 1 To UBound(ar2): ar2(i, 0) = d1(ar2(i, 0)): Next
   sh0.[T5].Resize(UBound(ar2), 1) = ar2                      '¥þ³¡±Æ¦W©ñ¤J[T5:T1048576]

sh0.[A1] = Format(Timer - TT, "0.0")
End Sub

singo1232001

¦^´_ 22# singo1232001


¨S·Q¨ì¥[¤F¤@­Ó§PÂ_
³t«×ºC¤F4­¿ Åܦ¨120¬í
³t«×¤w­×¥¿¦^ 40¸Uµ§ 38¬í
1¸Uµ§1¬í
2¸Uµ§2¬í
6¸Uµ§6¬í
12¸Uµ§11¬í
40¸Uµ§38¬í
³Ñ¤U­n¾a°O¾ÐÅé¤j¤p °O¾ÐÅé³t«× ¸òcpu
¤£¹L·|¦]¬°§A¸ê®Æªº¦h¼Ë©Ê ¦Ó¼vÅT«áÄò±Æ¦Wªº³t«×
¨Ì½d¨Ò¨Ó»¡¥u¦³6­Ó¦W¦¸ ¥H«á¥i¯à¦³600~60000¸U­Ó±Æ¦W®É ´N·|«ü¼Æ¯ÅÅܺC
¥Ø«e§Ú±Ä¥Î½u©Ê¤è¦¡±Æ¦W ¶V¤j¶qªº±Æ¦W ³t«×¤£·|¬ðµMÅܺC«Ü¦h ¬O½u©Ê¦¨ªø

Sub test()
TT = Timer  '<¥iµù¸Ñ²¾°£

Set sh0 = Sheets("¿é¤J")
  ReDim ar(1 To Sheets.Count - 1): w = 1    '¥D°}¦C  1ºû+2ºû¦hÄæ ¤§«á²V¥[10±iªí Á`¸ê®Æ
   ar0 = sh0.[I3:IN3]                       '¦¸°}¦C1 2ºû1Äæ ¿é¤J.[I3:IN3] ¤§«á¤ñ¹ï¥Î
    ReDim ar1(1 To Sheets.Count - 1)        '¦¸°}¦C2 1ºû+2ºû1Äæ 10±iªí ¤§«á²V¥[¦Uªí±o¤À¥[Á`
     sh0.[i5:T1048576].ClearContents

For Each Z In Sheets
If Z.Name <> "¿é¤J" Then
  ar(w) = Z.Range("I5:IN" & Z.Cells(Rows.Count, 2).End(3).Row) '¥D°}¦C ²V¥[2ºû¤£³W«h°}¦C
   ReDim ir(1 To UBound(ar(w)), 0)
    ar1(w) = ir
     If MaxR < UBound(ar(w)) Then MaxR = UBound(ar(w))
      w = w + 1
End If
Next

ReDim ar2(1 To MaxR, 0)                    '¦¸°}¦C3 2ºû1Äæ ¥[Á`10±iªíÁ`±o¤À
  For i = 1 To MaxR: ar2(i, 0) = 0: Next

For i = 1 To UBound(ar)                 '®Ú¾Ú¥D°}¦C»P¦¸°}¦C1¤ñ¹ïºâ¤À °O¿ý¦b¦¸°}¦C2
    For j = 1 To 240                                        '10ªíÁ`¤À¬ö¿ý¦b¦¸°}¦C3
      If ar0(1, j) <> "" Then '¬°ªÅ¤£ºâ
         For k = 1 To UBound(ar(i))
          If ar(i)(k, j) = ar0(1, j) Then '¿é¤J»P¸ê®Æ§PÂ_¬Û¦P
           ar1(i)(k, 0) = ar1(i)(k, 0) + 1 '¦Uªí ¸Ó¦C+1¤À
            ar2(k, 0) = ar2(k, 0) + 1      'Á`ªí ¸Ó¦C+1¤À
          End If
         Next
       End If
    Next
Next

For i = 1 To UBound(ar)                          '¦UªíÁ`¤À¨Ì§Ç©ñ¤J[I5:R1048576]
sh0.Cells(5, i + 8).Resize(UBound(ar1(i)), 1) = ar1(i)
Next
  sh0.[s5].Resize(UBound(ar2), 1) = ar2          '¥þ³¡Á`¤À©ñ¤J[S5:S1048576]

Set d = CreateObject("scripting.dictionary"): d.RemoveAll        '¥h­«
  For i = 1 To UBound(ar2): d(ar2(i, 0)) = "": Next: d0 = d.keys()

For i = 0 To d.Count - 1                                         '®ðªw±Æ§Ç
  For j = i + 1 To d.Count - 1
    If d0(i) < d0(j) Then tp = d0(i): d0(i) = d0(j): d0(j) = tp
  Next
Next

ReDim d1(0 To d0(0))                                         '±o¤ÀÂà±Æ¦W
  For i = 0 To UBound(d0): d1(d0(i)) = i + 1: Next           '³Ðd1±Æ¦W¹ï·Óªí°}¦C
   For i = 1 To UBound(ar2): ar2(i, 0) = d1(ar2(i, 0)): Next '¨Ì¹ï·Óªíindex­×§ï
    sh0.[T5].Resize(UBound(ar2), 1) = ar2                 '¥þ³¡±Æ¦W©ñ¤J[T5:T1048576]

sh0.[A1] = Format(Timer - TT, "0.0")  '<¥iµù¸Ñ²¾°£
End Sub

singo1232001

Sub test()
TT = Timer  '<¥iµù¸Ñ²¾°£

Set sh0 = Sheets("¿é¤J")
  ReDim ar(1 To Sheets.Count - 1): w = 1    '¥D°}¦C  1ºû+2ºû¦hÄæ ¤§«á²V¥[10±iªí Á`¸ê®Æ
   ar0 = sh0.[I3:IN3]                       '¦¸°}¦C1 2ºû1Äæ ¿é¤J.[I3:IN3] ¤§«á¤ñ¹ï¥Î
    ReDim ar1(1 To Sheets.Count - 1)        '¦¸°}¦C2 1ºû+2ºû1Äæ 10±iªí ¤§«á²V¥[¦Uªí±o¤À¥[Á`
     sh0.[i5:T1048576].ClearContents

For Each Z In Sheets
If Z.Name <> "¿é¤J" Then
  ar(w) = Z.Range("I5:IN" & Z.Cells(Rows.Count, 2).End(3).Row) '¥D°}¦C ²V¥[2ºû¤£³W«h°}¦C
   ReDim ir(1 To UBound(ar(w)), 0)
    ar1(w) = ir
     If MaxR < UBound(ar(w)) Then MaxR = UBound(ar(w))
      w = w + 1
End If
Next

ReDim ar2(1 To MaxR, 0)                    '¦¸°}¦C3 2ºû1Äæ ¥[Á`10±iªíÁ`±o¤À
  For i = 1 To MaxR: ar2(i, 0) = 0: Next

For i = 1 To UBound(ar)                 '®Ú¾Ú¥D°}¦C»P¦¸°}¦C1¤ñ¹ïºâ¤À °O¿ý¦b¦¸°}¦C2
    For j = 1 To 240                                        '10ªíÁ`¤À¬ö¿ý¦b¦¸°}¦C3
      If ar0(1, j) <> "" Then '¬°ªÅ¤£ºâ
         For k = 1 To UBound(ar(i))
          If ar(i)(k, j) = ar0(1, j) Then '¿é¤J»P¸ê®Æ§PÂ_¬Û¦P
           ar1(i)(k, 0) = ar1(i)(k, 0) + 1 '¦Uªí ¸Ó¦C+1¤À
            ar2(k, 0) = ar2(k, 0) + 1      'Á`ªí ¸Ó¦C+1¤À
                If ar(i)(k, j) = "" And ar0(1, j) = 0 Then
                ar1(i)(k, 0) = ar1(i)(k, 0) - 1 '¹J¨ì0="" §â¤À¼Æ¦©¦^¨Ó-1
                ar2(k, 0) = ar2(k, 0) - 1       '¹J¨ì0="" §â¤À¼Æ¦©¦^¨Ó-1
                End If
          End If
         Next
       End If
    Next
Next

For i = 1 To UBound(ar)                          '¦UªíÁ`¤À¨Ì§Ç©ñ¤J[I5:R1048576]
sh0.Cells(5, i + 8).Resize(UBound(ar1(i)), 1) = ar1(i)
Next
  sh0.[s5].Resize(UBound(ar2), 1) = ar2          '¥þ³¡Á`¤À©ñ¤J[S5:S1048576]

Set d = CreateObject("scripting.dictionary"): d.RemoveAll        '¥h­«
  For i = 1 To UBound(ar2): d(ar2(i, 0)) = "": Next: d0 = d.keys()

For i = 0 To d.Count - 1                                         '®ðªw±Æ§Ç
  For j = i + 1 To d.Count - 1
    If d0(i) < d0(j) Then tp = d0(i): d0(i) = d0(j): d0(j) = tp
  Next
Next

ReDim d1(0 To d0(0))                                         '±o¤ÀÂà±Æ¦W
  For i = 0 To UBound(d0): d1(d0(i)) = i + 1: Next           '³Ðd1±Æ¦W¹ï·Óªí°}¦C
   For i = 1 To UBound(ar2): ar2(i, 0) = d1(ar2(i, 0)): Next '¨Ì¹ï·Óªíindex­×§ï
    sh0.[T5].Resize(UBound(ar2), 1) = ar2                 '¥þ³¡±Æ¦W©ñ¤J[T5:T1048576]

sh0.[A1] = Format(Timer - TT, "0.0")  '<¥iµù¸Ñ²¾°£
End Sub

singo1232001

¦^´_ 27# oak0723-1

­Y§AÁÙ­n¦A§Ö
³o¸ÌÁÙ¦³¤@­Ó¦A§Ö8%~15%ªº
¯ÊÂI¬O ¦Uªí±o¤À¤£·|ºâ ¤]¤£·|Åã¥Ü
¥u·|ºâ¥þ³¡ªí±o¤À¸ò±Æ¦W ·|Åã¥Ü  
­Y§Aªº°O¾ÐÅé³t«×2400 Mhz(¤u§@ºÞ²z­û>®Ä¯à>¤º¥i¥H¬Ý°O¾ÐÅé³t«×)
14¸Uµ§¤j·§13¬í    ­Yª©¦³Åã¥Üªº¤j·§14¬í   ·sª©ªº§Ö8~15%   (·íµM°O¾ÐÅé§Ö·|§ó§Ö)

­Y§Aªº°O¾ÐÅé³t«×3200Mhz §Ú¥Î¥t¤@¥x3200¹q¸£´ú  
14¸Uµ§¤j·§8.5¬í  Âª©ªº9¬í

±À¦ô°O¾ÐÅé3600Mhz¤j·§¥i¥H¨ì7¬í ³o¼Ë
¤j·§§ì1¸Uµ§0.6¬í   (·íµMÁÙ¬O­n¬Ý¸ê®Æªº½ÆÂøµ{«×´£¤É ·|ªá§ó¦h®É¶¡)


   Sub testFast()
TT = Timer  '<¥iµù¸Ñ²¾°£

Set sh0 = Sheets("¿é¤J")
  ReDim ar(1 To Sheets.Count - 1): w = 1      '¥D°}¦C  1ºû+2ºû¦hÄæ ¤§«á²V¥[10±iªí Á`¸ê®Æ
   ar0 = sh0.[I3:IN3]                       '¦¸°}¦C0 2ºû1Äæ ¿é¤J.[I3:IN3] ¤§«á¤ñ¹ï¥Î
    'ReDim ar1(1 To Sheets.Count - 1)        '¦¸°}¦C1 1ºû+2ºû1Äæ 10±iªí ¤§«á²V¥[¦Uªí±o¤À¥[Á`
     sh0.[i5:T1048576].ClearContents

For Each Z In Sheets
If Z.Name <> "¿é¤J" Then
  ar(w) = Z.Range("I5:IN" & Z.Cells(Rows.Count, 2).End(3).Row) '¥D°}¦C ²V¥[2ºû¤£³W«h°}¦C
'   ReDim ir(1 To UBound(ar(w)), 0)
'    ar1(w) = ir
     If MaxR < UBound(ar(w)) Then MaxR = UBound(ar(w))
      w = w + 1
End If
Next

ReDim ar2(1 To MaxR, 0) As Integer                   '¦¸°}¦C2 2ºû1Äæ ¥[Á`10±iªíÁ`±o¤À
  'For i = 1 To MaxR: ar2(i, 0) = 0: Next

For i = 1 To UBound(ar)                 '®Ú¾Ú¥D°}¦C»P¦¸°}¦C1¤ñ¹ïºâ¤À °O¿ý¦b¦¸°}¦C2
    For j = 1 To 240                                        '10ªíÁ`¤À¬ö¿ý¦b¦¸°}¦C3
      If ar0(1, j) <> "" Then '¬°ªÅ¤£ºâ
         For k = 1 To UBound(ar(i))
          If ar(i)(k, j) = ar0(1, j) Then '¿é¤J»P¸ê®Æ§PÂ_¬Û¦P
          ' ar1(i)(k, 0) = ar1(i)(k, 0) + 1 '¦Uªí ¸Ó¦C+1¤À
            ar2(k, 0) = ar2(k, 0) + 1      'Á`ªí ¸Ó¦C+1¤À
                If ar(i)(k, j) = "" Then
                If ar0(1, j) = 0 Then
                'ar1(i)(k, 0) = ar1(i)(k, 0) - 1 ' '¹J¨ì0="" §â¤À¼Æ¦©¦^¨Ó-1
                ar2(k, 0) = ar2(k, 0) - 1         '¹J¨ì0="" §â¤À¼Æ¦©¦^¨Ó-1
                End If
                End If
          End If
         Next
       End If
    Next
Next

'For i = 1 To UBound(ar)                          '¦UªíÁ`¤À¨Ì§Ç©ñ¤J[I5:R1048576]
'sh0.Cells(5, i + 8).Resize(UBound(ar1(i)), 1) = ar1(i)
'Next
  sh0.[s5].Resize(UBound(ar2), 1) = ar2          '¥þ³¡Á`¤À©ñ¤J[S5:S1048576]

Set d = CreateObject("scripting.dictionary"): d.RemoveAll        '¥h­«
  For i = 1 To UBound(ar2): d(ar2(i, 0)) = "": Next
    d0 = d.keys()                                                                       '¦¸°}¦C3

For i = 0 To d.Count - 1                                         '¦¸°}¦C3 ®ðªw±Æ§Ç
  For j = i + 1 To d.Count - 1
    If d0(i) < d0(j) Then tp = d0(i): d0(i) = d0(j): d0(j) = tp
  Next
Next

ReDim d1(0 To d0(0))                                         '±o¤ÀÂà±Æ¦W
  For i = 0 To UBound(d0): d1(d0(i)) = i + 1: Next           '³Ðd1±Æ¦W¹ï·Óªí°}¦C
   For i = 1 To UBound(ar2): ar2(i, 0) = d1(ar2(i, 0)): Next '¨Ì¹ï·Óªíindex­×§ï¦¸°}¦C3
    sh0.[T5].Resize(UBound(ar2), 1) = ar2                 '¥þ³¡±Æ¦W©ñ¤J[T5:T1048576]

sh0.[A1] = Format(Timer - TT, "0.0")  '<¥iµù¸Ñ²¾°£
End Sub