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

[µo°Ý] ¸ê®Æ±Æ¦C°ÝÃD

[µo°Ý] ¸ê®Æ±Æ¦C°ÝÃD

¥»©«³Ì«á¥Ñ sandra_wang ©ó 2010-10-29 15:28 ½s¿è

·Q½Ð±Ð¦U¦ì«e½ú~
(½Ð¤j®aÀ°§Ú¬Ý¬Ýªþ¥ó)
§Ú­n¦p¦ó§â¥ªÃ䪺¸ê®Æ¨Ì·ÓMax¼Æ¶q,Min¼Æ¶q,Middle¼Æ¶q§â¸ê®Æ±q¥ªÃä±Æ¦C¨ì¥kÃä?
±Æ¦Cªº³W«h¬O±qHÄæ¦ì¶}©l¡A¥u­nHÄæ¦ì¤£¬O0ªº´N­n§âÄæ¦ìB~Eªº¸ê®Æ¶ñ¨ì¥kÃäªí®æ
HÄæ¦ì¼Æ¶q¦pªG¬O"1"¡A¥kÃäªí®æ´N¶ñ¤J¤@¦¸¡A¼Æ¶q¦pªG¬O"2"¡A¥kÃäªí®æ´N¶ñ¤J2¦¸¡A¨Ì¦¹Ãþ±À...

HÄæ¦ìªº³¡¥÷³B²z§¹¡A±µµÛ´«IÄæ¦ìªº³¡¥÷¡A
IÄæ¦ì¼Æ¶q¦pªG¬O"1"¡A¥kÃäªí®æ´N¶ñ¤J¤@¦¸¡A¼Æ¶q¦pªG¬O"2"¡A¥kÃäªí®æ´N¶ñ¤J2¦¸¡A¨Ì¦¹Ãþ±À¡K

³Ì«á¬OJÄæ¦ìªº³¡¥÷¡A
JÄæ¦ì¼Æ¶q¦pªG¬O"1"¡A¥kÃäªí®æ´N¶ñ¤J¤@¦¸¡A¼Æ¶q¦pªG¬O"2"¡A¥kÃäªí®æ´N¶ñ¤J2¦¸¡A¨Ì¦¹Ãþ±À¡K

ÁÙ¦³¥kÃäªí®æªºªíÀY¤]­n¦³Åý¥¦¦Û°Ê¶]¥X¨ÓMax_1, Max_2,...

«ô°U¤j®aÀ°À°§Ú¬Ý¬Ý­n«ç»ò¥ÎVBA¼g©O?
(§ÚÁÙ¬O¤p¾Ç¥Í¨S¿ìªk¤U¸üÀɮסA½Ð¤j®a¦h¦h«ü±Ð!!)

2010-10-27 ¤U¤È 03-59-58.jpg (146.17 KB)

§Úªº°ÝÃD

2010-10-27 ¤U¤È 03-59-58.jpg

°ÝÃD.zip (4.58 KB)

§ÚªºExcelÀÉ®×

§Úªº°ÝÃDpart2.zip (345.93 KB)

§Úªº°ÝÃDpart2

¨ä¹ê³o­Ó¤£¤@©w­n¥ÎVBA
¦pªGMAX,MIDDLE,MIN¤U¼Æ¦r¨S­«ÂÐ ¥i¥H¥Î¤½¦¡±o¥X
M3=INDIRECT("B"&MATCH(1,H:H,0))
M4=INDIRECT("C"&MATCH(1,H:H,0))
M5=INDIRECT("D"&MATCH(1,H:H,0))
M6=INDIRECT("E"&MATCH(1,H:H,0))

N3=INDIRECT("B"&MATCH(2,H:H,0))
N4=INDIRECT("C"&MATCH(2,H:H,0))
N5=INDIRECT("D"&MATCH(2,H:H,0))
N6=INDIRECT("E"&MATCH(2,H:H,0))

....

¤½¦¡ªº¤èªk¤]¦³«Ü¦h,¥i¥H¸Õ¸Õ§ä§ä
À´±oµo°Ý,µª®×´N·|¦b¨ä¤¤

¤µ¤éの¤@¬íは  ©ú¤éにない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

·PÁª©¥Dªº¦^µª~~
¦ý¬OÄæ¦ìB-Äæ¦ìJ ªº¸ê®Æ·|¤@ª½ÅܤơA¨C¦¸¶i¨Óªº¸ê®Æ³£¤£¦P¡A©Ò¥H¨S¿ìªk¥ÎINDIRECT¥h¤ñ¹ï¡C

¦]¬°¨C¦¸¶i¨Óªº¸ê®ÆÄæ¦ìH-Jªº¼Æ¶q³£¤£¤@©w¡A¥u­n¤£¬O¹sªº®É­Ô¡A´N­n§â¨º¦Cªº¸ê®Æ¶ñ¨ì¥kÃäªí®æ¡A
¦Ó¥B¼Æ¶q¬O¦h¤Ö¡A¨ºµ§¸ê®Æ´N­n¥X²{´X¦¸¡A

ÁÙ¬O§Æ±æ¦U¦ì«e½ú¥i¥H¦A«ü¾É§Ú¤@¤U¡AÁÂÁ¤j®a¡C

TOP

§AÀ³¸Ópo§Aªº¤å¥ó¡A·QÀ°§Aªº¤H¤~À°±o¤W¤â¡C
Ãø¹D§A­n§O¤H°Ñ·Ó§Aªº¹Ï¥h«Ø­ÓÀɮסH

TOP

ÁÂÁ¼ӤWªº¶W¯Åª©¥D´£¿ô!!
¤w¸g¸É¤W¤F!!

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2010-10-28 19:56 ½s¿è

¦^´_ 5# sandra_wang
©pªº½d¨Òªºmiddle¦n¹³À³¸Ó¥u¦³¤@µ§¤~¹ï
  1. Sub Ex()
  2. Dim A As Range, d As Object, Col As Integer, s As Long, r As Long, k As Integer, Mystr As String
  3. Dim ky As Variant
  4. Set d = CreateObject("Scripting.Dictionary")
  5. Set A = Range([M1], [M1].End(xlToRight))
  6. A.Resize(10, A.Count) = ""
  7. Col = 13
  8. For k = 8 To 10
  9. If Application.Count(Columns(k)) > 0 Then
  10.    For Each A In Columns(k).SpecialCells(xlCellTypeConstants, 1)
  11.       For i = 1 To A
  12.          s = s + 1
  13.          r = A.Row
  14.          Mystr = Replace(Cells(1, k), "¼Æ¶q", "_") & s
  15.          d(Mystr) = Application.Transpose(Cells(r, 2).Resize(, 4))
  16.       Next
  17.    Next
  18.    
  19.    For Each ky In d.keys
  20.       Cells(1, Col) = ky
  21.       Cells(3, Col).Resize(4, 1) = d(ky)
  22.       Col = Col + 1

  23.    Next
  24. End If
  25. s = 0: d.RemoveAll
  26. Next
  27. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^ÂÐ#6 Hsieh ¶W¯Åª©¥D~
ÁÂÁ§A´£¨Ñ§Ú³o­Óµª®×¡A¦]¬°¦³¨Ç§ÚÁÙ¬O¬Ý¤£¬O«ÜÀ´¡A¦ý¬O§Ú¦Û¤v¦³½Õ¾ã«á¦A¸Õ¤F¤@¤U!

¦]¬°§Ú¦³¤TºØ²£«~»Ý­n³o¼Ëªº±Æ¦C¡ACPU,HDD,Memory
¦ý¬O§Ú¦Û¤v§ï¹L¤§«á¡ACPUªº±Æ¦C§Ú¥i¥H¶¶§Q¶]¥X¨Ó¡A¥i¬OHDD©MMemoryªº³¡¥÷´N¶]¤£¥X¨Ó¡A
¥i¥H½Ð¼Ó¤Wªº¶W¯Åª©¥DÀ°§Ú¬Ý¬Ý§Ú­þ¸Ì¿ù¤F¶Ü?(§Ú§ï¹Lªº³¡¤À¦³¥Î¬õ¦âªº¦rÅé¼Ð¥Ü)
§Ú¦³ªþ¤WÀɮסA½ÐÀ°§Ú¬Ý"°ÝÃDpart2"ªºªþ¥ó

TOP

¦^ÂÐ#6 Hsieh ¶W¯Åª©¥D~
¥i¥H½Ð§AÀ°¬Ý¬Ý"§Úªº°ÝÃDpart2"¡A
§ÚªºCPU¥»¨Ó¥i¥H¶]¥X¨Ó¡A²{¦bPriority 3 & Vendorªº¸ê®Æ¤S¶]¤£¥X¨Ó¤F¡A·|¥X²{#N/A¡C
¨ä¥LHDD&Memoryªº¸ê®Æ§Ú­n«ç»ò½Õ¾ã¥¦¤~·|¶]¨ì¾ï¦â¸ò²LºñÂŦ⪺¦ì¸m©O?
ÁÂÁ§A!!

TOP

¦^´_ 8# sandra_wang
¨C¤@µ{§Ç¬O¨Ì¾Ú,¨C¦U­Ó®×©Ò¶q¨­­q»sªº .¤£¤@©w®M¥Îªº¤W.
½Ð­n»¡©ú¤@¤U®t²§¬O¦b­þ¸Ì, Àɮ׬O¦p¦¹Ãe¤j§Ú§ä±o²´ªá¤]¬Ý¤£¥X¨Ó¦p¦óÀ°§A.
²Ä¤@­Ó°ÝÃD ¥t¦³¤@¸Ñ½Ð°Ñ¦Ò
  1. Sub Ex()
  2.     Dim Rng(1 To 2) As Range, i%, ii%, C%, T%, E
  3.     With Sheet1
  4.         .Range("M1", .Range("M1").End(xlToRight)).EntireColumn.Clear
  5.         Set Rng(1) = .Range("L3", .Range("L3").End(xlDown))
  6.         C = 1
  7.         For i = 8 To 10
  8.             T = 1   'Äæ¦ì¼Æ¶q->Âk¹s
  9.             For Each E In .Range(.Cells(1, i), .Cells(Rows.Count, i).End(xlUp)).SpecialCells(xlCellTypeConstants).Cells
  10.                 If Val(E) > 0 Then
  11.                     For ii = 1 To Val(E)
  12.                         Set Rng(2) = Rng(1).Offset(, C)
  13.                         .Cells(1, Rng(2).Column) = Replace(.Cells(1, i), "¼Æ¶q", "_" & T)
  14.                         Rng(2).Value = Application.Transpose(.Cells(E.Row, "b").Resize(, 4))
  15.                         Rng(2).Interior.ColorIndex = E.Interior.ColorIndex
  16.                         C = C + 1 '©¹¥k¥[1Äæ  Rng(1).Offset(, C)
  17.                         T = T + 1
  18.                     Next
  19.                 End If
  20.             Next
  21.         Next
  22.     End With
  23. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2010-10-29 19:37 ½s¿è

¦^´_ 8# sandra_wang
  1. Sub Ex()
  2. Dim A As Range
  3. Set d = CreateObject("Scripting.Dictionary")
  4. Set Rng = Range("CL2,CX2,DJ2")
  5. [ED2:EL65536] = ""
  6. r = 2
  7. For Each A In Rng
  8.    k = 135
  9.    For i = 7 To 9
  10.    s = 1
  11.       If Application.Count(A.Offset(, i).EntireColumn) > 0 Then
  12.       Cells(r, 134).Resize(5, 1) = Application.Transpose(Array(A, A.Offset(1, 1), A.Offset(1, 2), A.Offset(1, 3), A.Offset(1, 4)))
  13.          For Each b In A.Offset(, i).EntireColumn.SpecialCells(xlCellTypeConstants, 1)
  14.             For j = 1 To b
  15.                Mystr = Cells(3, b.Column) & "_" & s
  16.                s = s + 1
  17.                d(Mystr) = Application.Transpose(Cells(b.Row, A.Column + 1).Resize(, 4))
  18.             Next
  19.          Next
  20.          For Each ky In d.keys
  21.             Cells(r, k) = ky: Cells(r + 1, k).Resize(4, 1) = d(ky)
  22.             k = k + 1
  23.          Next
  24.          d.RemoveAll
  25.       End If
  26.       Next
  27.       r = r + 12
  28. Next
  29. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ºÉ¦h¤Ö¥»¥÷¡A´N±o¦h¤Ö¥»¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD