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

[µo°Ý] Äæ¦ìÂà´«

[µo°Ý] Äæ¦ìÂà´«

½Ð±Ð¦U¦ì¤j¤j
¤p§Ì§Ú¦³¤@¸ê®ÆÂà´«®æ¬O¦p¤UA ±N COST ¸ò DATEÂà´«®æ¦¡¡A¤£ª¾¹D¸Ó«ç»ò¼g¤ñ¸û¦n©O
®æ¦¡A
ID        NAME        COST        DATE
AXXX        JACK        50        1¤ë1¤é
AXXX        JACK        60        1¤ë2¤é
BXXX        MARY        30        1¤ë3¤é
BXXX        MARY        40        1¤ë4¤é

·QÂà´«¦¨®æ¦¡B

ID        NAME        COST1        COST2        DATE1        DATE2
AXXX        JACK        50        60        1¤ë1¤é        1¤ë2¤é
BXXX        MARY        30        40        1¤ë3¤é        1¤ë4¤é
80 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

°²³]§Aªº¸ê®Æ "ID" ´N¦b [A1] ¶}©l, ¥N½X¦p¤U:
  1. Sub zz()
  2. Dim d As Object, dd As Object, k, t, n&, m&, HL$, b()
  3. Set d = CreateObject("scripting.dictionary")
  4. Set dd = CreateObject("scripting.dictionary")
  5. a = [a1].CurrentRegion
  6. For i = 2 To UBound(a)
  7.     k = a(i, 1)
  8.     d(k) = d(k) + 1
  9.     dd(k) = dd(k) & "|" & i
  10. Next
  11. n = Application.Max(d.items)
  12. HL = a(1, 1) & "|" & a(1, 2)
  13. For j = 3 To UBound(a, 2)
  14.     For jj = 1 To n
  15.         HL = HL & "|" & a(1, j) & jj
  16.     Next
  17. Next
  18. t = Split(HL, "|")
  19. ReDim b(1 To d.Count + 1, 1 To UBound(t) + 1)
  20. Workbooks.Add 1
  21. [a1].Resize(1, UBound(b, 2)) = t
  22. k = dd.keys: n = 0
  23. For i = 0 To UBound(k)
  24.     t = Split(dd(k(i)), "|")
  25.     n = n + 1
  26.     b(n, 1) = a(t(1), 1)
  27.     b(n, 2) = a(t(1), 2)
  28.     m = 2
  29.     For j = 3 To UBound(a, 2)
  30.         For jj = 1 To UBound(t)
  31.             m = m + 1
  32.             b(n, m) = a(t(jj), j)
  33.         Next
  34.     Next
  35. Next
  36. [a2].Resize(n, UBound(b, 2)) = b
  37. End Sub
½Æ»s¥N½X

TOP

ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

        ÀR«ä¦Û¦b : ªY½à§O¤H´N¬O²øÄY¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD