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

[µo°Ý] ¨Ì±ø¥ó±N¸ê®Æ¶ñ¤J

[µo°Ý] ¨Ì±ø¥ó±N¸ê®Æ¶ñ¤J

§Ú¦³¤@­Ó³øªí
¨C¤Ñ·|±q¸ê®Æ®w¶×¥X Sheet1 ªº¸ê®Æ
½Ð°Ý¦³¨S¦³§Ö³tªº¤èªk¥i¥H±N Sheet1 ªº¸ê®Æ¾ã²z¦¨ Summary ­¶­±ªº¼Ë¤l¡H

¦b Sheet1 ¤¤ Sammple1 ©Ò¹ïÀ³ªº EQ ¨C¤Ñ³£·|¤£¤@¼Ë¡@¥i¯à·|¼W¥[¤]¥i¯à·|´î¤Ö
§Úªº·Qªk¬O¥ý§âªí®æ©Ôªø¡@«Ý­È³£¶ñ¤J«á¡@¦A±N¦h¾lªºªí®æ§R°£
³o¼Ëªº§@ªk¤£¾å±o¦æ¤£¦æ¡H

¡]¨C­Óªí®æ¤W¤è³£·|¦³¤@±i¹Ï¡^

½ÐÀ°À°¦£¡@ÁÂÁ¡ã

Example.zip (7.1 KB)

¦^´_ 1# Genie
§Q¥Î¹Ï¤ù©w¦ì
Example.zip (591.8 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh


ÁÂÁ Hsieh ª©¥D¡@µ{¦¡¨S¦³°ÝÃD
¤£¹L¦³­Ó°ÝÃD¡@´N¬O´¡¤Jªº¸ê®Æ³£·|Åܦ¨¤å¦r®æ¦¡
¬O§_¦³¿ìªk±N´¡¤Jªº¼Æ¦rÅܦ¨©Ò»Ýªº®æ¦¡¡H(ex: ¨ú¨ì¤p¼ÆÂI²Ä¤@¦ì¡^

ÁÂÁ¡ã

TOP

¦^´_ 3# Genie
  1. Sub ex()
  2. Application.ScreenUpdating = False
  3. Set d = CreateObject("Scripting.Dictionary")
  4. With Sheet1
  5.   For Each a In .Range(.[A2], .[A2].End(xlDown))
  6.   mystr = Join(Application.Transpose(Application.Transpose(a.Offset(, 1).Resize(, 3))), ";")
  7.      If d(a.Value) = "" Then
  8.         d(a.Value) = mystr
  9.         Else
  10.         d(a.Value) = d(a.Value) & Chr(10) & mystr
  11.      End If
  12.   Next
  13. End With
  14. With Sheet2
  15.   For Each pic In .Pictures
  16.     Set a = pic.TopLeftCell.Offset(-1, 0)
  17.     If d.exists(a.Value) = True Then
  18.     r = a.Offset(23).Row
  19.     Do While .Cells(r, "B") <> "" Or .Cells(r, "F") <> ""
  20.       a.Offset(23).EntireRow.Delete
  21.     Loop
  22.        ar = Split(d(.Cells(a.Row, "B").Value), Chr(10))
  23.        ar1 = Split(d(.Cells(a.Row, "F").Value), Chr(10))
  24.        a.Offset(23).Resize(Application.Max(UBound(ar), UBound(ar1)) + 1, 1).EntireRow.Insert
  25.        For i = 2 To 6 Step 4
  26.          ay = Split(d(.Cells(a.Row, i).Value), Chr(10))
  27.          For j = 0 To UBound(ay)
  28.            For x = 0 To 2
  29.              If x = 0 Then
  30.                 .Cells(a.Row, i + x).Offset(23 + j) = Split(ay(j), ";")(x)
  31.                 Else
  32.                 .Cells(a.Row, i + x).Offset(23 + j) = Round(Val(Split(ay(j), ";")(x)), 1)
  33.              End If
  34.            Next
  35.          Next
  36.          d.Remove .Cells(a.Row, i).Value
  37.        Next
  38.     End If
  39.   Next
  40. End With
  41. Application.ScreenUpdating = True
  42. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 4# Hsieh


ÁÂÁ Hsieh ª©¥D
¦³°µ¨ì§Ú»Ý­nªº®æ¦¡
«D±`·PÁ¡I¡I¡I

¤£¹L¦³¦a¤è¬Ý¤£¤ÓÀ´
²Ä 28 ¦æ "For x = 0 To 2" ¶}©l¡@¨ì²Ä 33 ¦æ
³o¤@¾ã¬q¬O¦b´y­z­þ­Ó³¡¥÷©O¡H
¬°¤°»ò­n¤À x=0 or x¡Ú0¡H

TOP

¦^´_ 5# Genie

Split(ay(j), ";")·|¤Á³Î¥X3Ä檺¸ê®Æ¡A¦¹°}¦C¯Á¤Þ¬°0~2
²Ä¤@­Ó¤¸¯À¬O¤å¦r¡AµLªk¨Ï¥Î¼Æ­È­pºâ
©Ò¥H¡A·í²Ä¤@­Ó¤¸¯À®É´Nª½±µ¼g¤J
²Ä2~3­Ó¤¸¯À¤~¥|±Ë¤­¤J
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 6# Hsieh


ÁA¸Ñ¤F
ÁÂÁ»¡©ú¡I

TOP

        ÀR«ä¦Û¦b : ¤Hªº¤ß¦a¬O¤@²¥¥Ð¡A¤g¦a¨S¦³¼½¤U¦nºØ¤l¡A¤]ªø¤£¥X¦nªºªG¹ê¡C -
ªð¦^¦Cªí ¤W¤@¥DÃD