[¼ö°­±â°£] 2010. 11. 1 ~ 2011. 10. 31
½Åû¹®ÀÇ : À¥Å¸ÀÓ±³À°¼¾ÅÍ 02-3477-8300, ¿î¿µ°ü¸® : ¿ÀÇǽºÆ©ÅÍ 070-7098-2554

±³À°°úÁ¤ ³ªÀÇÇнÀ ¿ÀÇǽºÆÁ °Ë»ö ¹«·á°­ÁÂ
Home ·Î±×ÀΠȸ¿ø°¡ÀÔ °í°´¼¾ÅÍ »çÀÌÆ®¸Ê
¿ÀÇǽºÆ©ÅÍ ±â¾÷ASP¼­ºñ½º > ¹«·á°­ÁÂ
¿¢¼¿ 2000 µû¶óÇϱâ
Ȩ·¯´× ¿¢¼¿ 2000°­ÁÂ
À繫 ÇÔ¼ö °­ÁÂ
¿¢¼¿ 2002 »õ·Î¿î ±â´É
Ȳ±â¼º´ÔÀÇ VBA°­ÁÂ
°û½ÂÁÖ´ÔÀÇ VBAÀ̾߱â
Home > ¹«·á°­Á > ¿¢¼¿
¿¢¼¿, °û½ÂÁÖ´ÔÀÇ ¿ÀÆ©°ø±¸ÇÔ Á¦ÀÛÀ¸·Î ¹è¿öº¸´Â VBA À̾߱â, Excel
  

16. Á¤·Ä ¼¼ ¹ø° À̾߱â

¾È³çÇϼ¼¿ä, ¿À´ÃÀº Á¤·Ä¿¡ °üÇÑ ¸¶Áö¸· ½Ã°£À¸·Î Dave SteppanÀÇ Á¤·ÄÇÔ¼ö¸¦ ¼³¸íÇص帮°íÀÚ ÇÕ´Ï´Ù. Áö³­ ½Ã°£¿¡ ÀÌ°ÍÀ» °¡Áö°í ÆûÀ» ¸¸µé¾î 4°³ ÀÌ»óÀÇ Å°¸¦ È¿°úÀûÀ¸·Î Á¤·ÄÇÏ´Â °ÍÀ» º¸¿©µå·È½À´Ï´Ù. 

ÇÁ·Î½ÃÁ®´Â 2°³ÀÔ´Ï´Ù. Çϳª´Â MultiColumnSort()ÇÔ¼öÀÌ°í, ³ª¸ÓÁö Çϳª´Â RankSort()ÇÁ·Î½ÃÁ®ÀÔ´Ï´Ù. ±×¸®°í MultiColumnSort()°¡ RankSort()¸¦ È£ÃâÇÕ´Ï´Ù. ¾Ë°í¸®ÁòÀÇ ´ë°­À» ¼³¸íµå¸®ÀÚ¸é MultiColumnSort()ÇÔ¼ö´Â Á¤·ÄÇÒ ¸ñ·Ï°ú Á¤·Ä¼ø¼­¸¦ ÀԷ¹޽À´Ï´Ù. ±×¸®°í ¸ñ·Ï³» °¢ ¿­ÀÇ ±æÀ̸¦ °íÁ¤½ÃÅ°°í ¸ñ·ÏÀÇ °¢ ¿­À» Çϳª·Î ÇÕÃÄ RankSort()¿¡ ³Ñ°ÜÁÝ´Ï´Ù. ±×¸®°í RankSort()ÇÔ¼ö´Â ÇϳªÀÇ ¿­·Î ÇÕÃÄÁø º¤Å͸¦ ¹Þ¾Æ ´Ü¼øÈ÷ Á¤·ÄÇÕ´Ï´Ù. ±×°É·Î ³¡ÀÔ´Ï´Ù.

¼³¸íÀ» À§ÇØ ´ÙÀ½°ú °°Àº µ¥ÀÌÅ͸¦ Á¤·ÄÇÑ´Ù°í °¡Á¤ÇÏ°Ú½À´Ï´Ù.

Function MultiColumnSort(UnSortedArray As Variant, SortOrder As Integer)
     Dim OriginalArray As Variant
     Dim SortedArray As Variant
     Dim SingleList As Variant
     Dim MaxStringLen As Variant

     'Make a copy of the unsorted array
     OriginalArray = UnSortedArray
Á¤·ÄµÇÁö ¾ÊÀº ¿øº»À» OriginalArray¿¡ º¹»çÇÕ´Ï´Ù. 

     'For loop bounds...
     NumRows = UBound(UnSortedArray, 1)
ÇàÀÇ °³¼ö¸¦ ¾Ë¾Æ³À´Ï´Ù. À§ÀÇ ¿¹¿¡¼­´Â 9°¡ µË´Ï´Ù.
     NumCols = UBound(UnSortedArray, 2)
¿­ÀÇ °³¼ö¸¦ ¾Ë¾Æ³À´Ï´Ù. À§ÀÇ ¿¹¿¡¼± 4°¡ µË´Ï´Ù.
UBound()ÇÔ¼ö¿Í LBound()ÇÔ¼ö´Â ¹è¿­ÀÇ »óÀ§À妽º °ª°ú ÇÏÀ§À妽º °ªÀ» ¾Ë·ÁÁÝ´Ï´Ù. ±×·±µ¥ ¹è¿­ÀÌ 1Â÷¿ø ÀÌ»óÀÌ¸é ¹è¿­¸í ´ÙÀ½¿¡ Á¶»çÇÒ Â÷¿øÀ» ÁöÁ¤ÇÕ´Ï´Ù. 

      ReDim SingleList(1 To NumRows)
Á¤·ÄÇÒ ¸ñ·ÏÀ»°¡Áú SingleList()¹è¿­°ú °¢ ¿­¿¡¼­ ÃÖ°í ±æÀÌ(¹®ÀÚ¿­·Î »ý°¢ÇßÀ»¶§)¸¦ °¡Áø °ªÀ» ÀúÀåÇÒ MaxStringLen()¹è¿­À» Çà°ú ¿­ÀÇ ¼ö¸¸Å­ ¹è¿­ÀÇ Å©±â¸¦ ÁöÁ¤ÇÕ´Ï´Ù. 

     'Find max length string in each column
     For j = 1 To NumCols

À§ÀÇ ¹Ýº¹¹®¿¡¼­´Â °¢ ¿­¿¡¼­ ¹®ÀÚ¿­ÀÇ ÃÖ´ë±æÀ̸¦ ¾Ë¾Æ³» MaxStringLen()¹è¿­¿¡ ÀúÀåÇÕ´Ï´Ù. À§ÀÇ ¿¹¿¡¼± ´ÙÀ½°ú °°Àº °á°ú¸¦ º¸¿©ÁÙ °Ì´Ï´Ù. 

MaxStringLen(1)

 MaxStringLen(2)

 MaxStringLen(3)

  MaxStringLen(4)

3

 2

 1

  1

     'Make all strings in each column the same length
     'by adding Chr(0) to make the same length
     For j = 1 To NumCols
          For i = 1 To NumRows
               Do While Len(UnSortedArray(i, j)) < MaxStringLen(j)
                    UnSortedArray(i, j) = UnSortedArray(i, j) & Chr(0)
               Loop
          Next
     Next

´Ù½Ã Á¤·ÄµÇÁö ¾ÊÀº ¸ñ·ÏÀ» ÀÏÀÏÀÌ ¹Ýº¹Çϸ鼭 °¢ ¹è¿­¿ä¼ÒÀÇ ±æÀ̸¦ ±× ¿­ÀÇ ÃÖ´ë¹®ÀÚ¿­ ±æÀÌ¿¡ ¸ÂÃç Chr(0)°ªÀ¸·Î ä¿ó´Ï´Ù. ÀÌ°ÍÀº Á¤·ÄÀ» ¿øÇÒÇÏ°Ô Çϱâ À§ÇÑ °ÍÀÔ´Ï´Ù. ¿¹¸¦ µé¾î ¼ø¼­´ë·Î ¹øÈ£¸¦ ºÙ¿© ÆÄÀÏÀ» ¸¸µé´Ùº¸¸é Ž»ö±â¿¡¼­ ÆÄÀÏÀ̸§À¸·Î Á¤·ÄÇصµ ÆÄÀÏÀÇ ¼ø¼­°¡ ¾ûÅ͸®·Î Á¤·ÄµÈ °ÍÀ» º¼ ¼ö ÀÖ½À´Ï´Ù. 

°¡·É 1.xls, 2.xls, 3.xls, 4.xls, 5.xls, 6.xls, 7.xls, 8.xls, 9.xls, 10.xls, 11.xls, 12.xls, 13.xls, 14.xls, 15.xls, 16.xls, 17.xls, 18.xls, 19.xls, 20.xls, 21.xls, 22.xls µîµîÀÇ ÆÄÀÏÀÌ ÀÖ´Â °æ¿ì À̸§¼ø¼­·Î Á¤·ÄÇϸé 1.xls, 10.xls, 11.xls, 12.xls, 13.xls, 14.xls, 15.xls, 16.xls, 17.xls, 18.xls, 19.xls, 2.xls, 20.xls, 21.xls, 22.xls, 3.xls, 4.xls, 5.xls, 6.xls, 7.xls, 8.xls, 9.xls ¼ø¼­´ë·Î µË´Ï´Ù. 

±×·¡¼­ ÀÌ·± Çö»óÀ» ¿¹¹æÇϱâ À§ÇØ 01.xls, 02.xls, 03.xls,...µîµî 10ÀÌÇÏÀÇ À̸§À» °®´Â °æ¿ì "0"À» ºÙ¿© ÁÝ´Ï´Ù. °°Àº ÀÌÀ¯·Î ¿©±â¿¡¼­µµ Chr(0)°ªÀ» ¿­ÀÇ ÃÖ´ë°ª¸¸Å­ ¸ÂÃß¾î ºÙ¿©ÁÝ´Ï´Ù. µû¶ó¼­ À§ÀÇ ¿¹¿¡ µû¸¥ °á°ú´Â ´ÙÀ½°ú °°À» °Ì´Ï´Ù.

a00

 b0

 d

  1

aa0

 b0

 e

  2

aaa

 b0

 e

  3

a00

 bb

 d

  4

aa0

 bb

 e

  5

aaa

 bb

 e

  6

a00

 a0

 d

  7

aa0

 a0

 d

  8

aaa

 a0

 d

  9

(Chr(0)°ªÀ» 0À̶ó°í °¡Á¤ÇÏ´Â °æ¿ì)

    
'Concatenate strings to make array into a single
      'vector to sort
     For i = 1 To NumRows
          For j = 1 To NumCols
               SingleList(i) = SingleList(i) & UnSortedArray(i, j)
          Next
     Next

ÀÏÁ¤ÇÑ Å©±â·Î ä¿öÁø °¢ ¿­ÀÇ °ªÀ» Çึ´Ù ¸ðµÎ ÇÕÃÄ ÇϳªÀÇ µ¥ÀÌÅͷΠǥ½ÃÇÕ´Ï´Ù. ÀÌ °á°ú SingleList()ÀÇ °ªÀº ´ÙÀ½°ú °°À» °Ì´Ï´Ù.

SingleList(1)

  a00b0d1

SingleList(2)

  aa0b0e2

SingleList(3)

  aaab0e3

SingleList(4)

  a00bbd4

SingleList(5)

  aa0bbe5

SingleList(6)

  aaabbe6

SingleList(7)

  a00a0d7

SingleList(8)

  aa0a0d8

SingleList(9)

  aaaa0d9

     'Use a traditional single array sorting algorithm
     Call RankSort(SingleList, SortOrder, RankList)
Á¤·ÄÇÏ´Â ÇÁ·Î½ÃÁ®¸¦ È£ÃâÇÕ´Ï´Ù. Á¤·ÄµÈ °á°ú´Â RankList()¿¡ ÀúÀåµË´Ï´Ù.

     ReDim SortedArray(1 To NumRows, 1 To NumCols)
Á¤·ÄµÈ ¸ñ·ÏÀ» ÀúÀåÇÒ ¹è¿­ÀÇ Å©±â¸¦ Á¤ÇÕ´Ï´Ù.

     'Translate results back into a sorted array
     For k = 1 To NumRows
          For j = 1 To NumRows
               If k = RankList(j) Then
                    For i = 1 To NumCols
                         SortedArray(k, i) = OriginalArray(j, i)
                    Next
               End If
          Next
    Next
Á¤·ÄµÈ °á°ú¿¡ µû¶ó ¿øº»À» Á¤·ÄÇÑ ¼ø¼­´ë·Î ¿Å°Ü ÀúÀåÇÕ´Ï´Ù. Sub RankSort() ÇÁ·Î½ÃÀúÀÇ ½ÇÇà°á°ú RankList()¹è¿­¿¡´Â °¢ ÇàÀÇ ¼øÀ§°¡ µé¾î°©´Ï´Ù. ±×¸®°í ±×¼øÀ§¿¡ ¸ÂÃç ¿øº»µ¥ÀÌÅ͸¦ Á¤·ÄµÈ ¹è¿­(SortedArray())¿¡ ÀúÀåÇÕ´Ï´Ù.

     'Feed the beast output
     MultiColumnSort = SortedArray

End Function

Sub RankSort()´Â ½ÇÁ¦ Á¤·ÄÀ» ÇÏ´Â ºÎºÐÀÔ´Ï´Ù. ÀúÀÚÀÇ ÁÖ¼®('Use a traditional single array sorting algorithm')´ë·Î Æò¹üÇÑ ¾Ë°í¸®ÁòÀ» »ç¿ëÇÑ´Ù°í ÇÏÁö¸¸ ¾Ë°í¸®ÁòÀÌ ±×·¸µí µûÁö°í µé·Á´Ï ¸Å¿ì ¸Ó¸®°¡ ¾îÁö·´±º¿ä. Çؼ®Àº ¾ÈÇÏ°í Åë°úÇÏ°Ú½À´Ï´Ù(Àúµµ ²Ò°¡ ³ª¼­ ^^;) 
¾Æ¸¶ ´ÙÀ½ Ä÷³Àº »õÇØ¿¡ ½ÃÀÛÇÒ °Í °°½À´Ï´Ù. ¹Ì¸® Àλ縦 µå¸³´Ï´Ù. Áñ°Å¿î ¿¬¸»À» º¸³»½Ã°í, »õÇØ º¹ ¸¹ÀÌ ¸¹À¸½Ê½Ã¿À. ²Ù¹÷ 

Sub RankSort(IArray, ByVal nOrder As Integer, rankarray)
     Dim Distance
     Dim Size
     Dim Index
     Dim NextElement
     Dim TEMP
     Dim trankarray() As Integer

     ReDim trankarray(LBound(IArray) To UBound(IArray))
     For i = LBound(IArray) To UBound(IArray)
          trankarray(i) = i
     Next

     ASCENDING_ORDER = 1
     DESCENDING_ORDER = -1
     Size = UBound(IArray) - LBound(IArray) + 1
     Distance = 1

     While (Distance <= Size)
          Distance = 2 * Distance
     Wend

     Distance = (Distance / 2) - 1

     While (Distance > 0)
          NextElement = LBound(IArray) + Distance
          While (NextElement <= UBound(IArray))
               Index = NextElement
               Do
                    If Index >= (LBound(IArray) + Distance) Then
                         If nOrder = ASCENDING_ORDER Then
                              If IArray(Index) < IArray(Index - Distance) Then
                                   TEMP = IArray(Index)
                                   IArray(Index) = IArray(Index - Distance)
                                   IArray(Index - Distance) = TEMP
                                   TEMP = trankarray(Index)
                                   trankarray(Index) = trankarray(Index - Distance)
                                   trankarray(Index - Distance) = TEMP
                                   Index = Index - Distance
                                   gIterations = gIterations + 1
                              Else
                                   Exit Do
                              End If
                         ElseIf nOrder = DESCENDING_ORDER Then
                              If IArray(Index) >= IArray(Index - Distance) Then
                                   TEMP = IArray(Index)
                                   Array(Index) = IArray(Index - Distance)
                                   IArray(Index - Distance) = TEMP
                                  TEMP = trankarray(Index)
                                  trankarray(Index) = trankarray(Index - Distance)
                                  trankarray(Index - Distance) = TEMP
                                  Index = Index - Distance
                                  gIterations = gIterations + 1
                             Else
                                  Exit Do
                             End If
                        End If
                   Else
                        Exit Do
                   End If
              Loop
              NextElement = NextElement + 1
              gIterations = gIterations + 1
         Wend
         Distance = (Distance - 1) / 2
         gIterations = gIterations + 1
     Wend

     ReDim rankarray(LBound(IArray) To UBound(IArray))
     For i = LBound(IArray) To UBound(IArray)
          rankarray(trankarray(i)) = i
     Next
End Sub

¡¡

¸ñÂ÷ | ÀÌÀü | ´ÙÀ½

¡¡

¿ÀÇǽºÆ©ÅÍ ±â¾÷ASP¼­ºñ½º 135-880 ¼­¿ï½Ã °­³²±¸ »ï¼ºµ¿ 157-3 ¿¤ÁöÆ®À©ÅÚ 2Â÷ 1603È£ ¢Ï070-7098-2554
Copyright ¨Ï 1999-2008 Officetutor.com All rights reserved
¡¡