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

[µo°Ý] EXCEL®Ú¾Ú±ø¥ó¿z¿ï

[µo°Ý] EXCEL®Ú¾Ú±ø¥ó¿z¿ï

¦U¦ì¤j¤j, ¦³¤u§@ªº­û¤u¯à¥Î¨ç¼Æ«ö­^¤å¾¦ì¦WºÙ±Æ§Ç¶Ü?
¨D¦U¦ì«ü±Ð¡AÁÂÁ¡I

book.zip (7.29 KB)

¦^´_ 1# ffff56666


    ¨ç¼Æ§Ú·Q¤£¨ì...
¤£¹L¥i¥H¥Î²³æªº¥¨¶°¸Ñ¨M§Aªº°ÝÃD¡C
  1. Private Sub CommandButton1_Click()
  2. [G2:I1000] = ""
  3. [L2:N1000] = ""
  4. For Each aa In Range([A2], [A2].End(xlDown))
  5. Select Case aa.Offset(, 3)
  6.     Case "Yes"
  7.         nextrow = Sheets("Sheet1").Cells(Rows.Count, 7).End(xlUp).Row + 1
  8.         form = 7
  9.     Case "No"
  10.         nextrow = Sheets("Sheet1").Cells(Rows.Count, 12).End(xlUp).Row + 1
  11.         form = 12
  12. End Select
  13. Cells(nextrow, form) = nextrow - 1
  14. Cells(nextrow, form + 1) = aa.Offset(, 1)
  15. Cells(nextrow, form + 2) = aa.Offset(, 2)
  16. Next
  17. End Sub
½Æ»s¥N½X

book.rar (22.99 KB)

¤@¤À§V¤O¡A¤@¤À¦¬Ã¬¡C
µo°ÝÃD«e¥i¥H¥ý·j¯Á¤º¤å¬O§_¦³¬ÛÃö½d¨Ò¡C

TOP

(¥ý)H2:I2{=IFERROR(INDEX(B:B,RIGHT(SMALL(IF($D$2:$D$9="Yes",CODE($B$2:$B$9)/1%+ROW(B$2:B$9)),ROW(A1)),2)),"")

G2=IF(H2="","",COUNTIF(H$2:H2,"<>"))
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

ÁÂÁ¦U¦ì¸Ñµª, °ÝÃD¸Ñ¨M¤F

TOP

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾Ç¾Ç²ß°}¦C»P¦r¨å,(³ÎÂû¤û¤M)¤èªk¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

°õ¦æ«e:
20230608_1.jpg
2023-6-8 14:17


°õ¦æµ²ªG:
20230608_2.jpg
2023-6-8 14:18



Option Explicit
Sub TEST()
Dim Brr, Crr(1 To 1000, 1 To 3), V, Z, i&, R&, N&, T$
'¡ô«Å§iÅܼÆ
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OZÅܼƬO ¦r¨å
Brr = Range([D2], [B65536].End(3))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥HB~DÄæÀx¦s®æ­È±a¤J°}¦C¤¤
With [G2].Resize(UBound(Brr), 3)
'¡ô¥H¤U¬OÃö©ó[G2]ÂX®i¦V¤UBrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹,¦V¥kÂX®i3Äæ½d³òÀx¦s®æªºµ{§Ç
   .Value = Brr
   '¡ô¥O¸Ó°Ï°ìÀx¦s®æ­È¬O Brr°}¦C­È
   .Sort KEY1:=.Item(3), Order1:=2, Header:=2, _
         Key2:=.Item(1), Order2:=1, Header:=2, Orientation:=1
   '¡ô¥O¸Ó°Ï°ìÀx¦s®æ°µ±Æ§Ç
   Brr = .Value: .ClearContents: .Offset(0, 5).ClearContents
   '¡ô¥OBrr°}¦C´«¸Ë²±±Æ§Ç¹L«áªº¸Ó°Ï°ìÀx¦s®æ­È,
   '¥Oµ²ªG°Ï°ìÀx¦s®æ²M°£¤º®e

End With
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é
   T = Brr(i, 3): V = Z(T)
   '¡ô¥OTÅܼƬO²Ä1Äæ°}¦C­È,VÅܼƬOTÅܼƬdZ¦r¨åªºitem
   If Not IsArray(V) Then V = Crr
   '¡ô¦pªGVÅܼƤ£¬O°}¦C? True´N¥OVÅܼƬOCrr°}¦C
   R = Z(T & "|r") + 1: Z(T & "|r") = R
   '¡ô¥ORÅܼƬOTÅܼƳs±µ"|r"²Õ¦¨ªº·s¦r¦ê¬dZ¦r¨åitem­È+1,
   '¥OTÅܼƳs±µ"|r"²Õ¦¨ªº¦r¦ê¦bZ¦r¨åªºkey,©Ò¹ïÀ³ªºitem¬O RÅܼÆ

   V(R, 1) = R: V(R, 2) = Brr(i, 1): V(R, 3) = Brr(i, 2)
   '¡ô¥OV°}¦C¨Ì§Ç¼g¤J­È
   Z(T) = V
   '¡ô¥OV°}¦C©ñ¦^Z¦r¨å¤¤
Next
For Each V In Z.KEYS
'¡ô³]³v¶µ°j°é!¥OVÅܼƬOZ¦r¨åªºkey
   If Not IsArray(Z(V)) Then GoTo i01
   '¡ô¦pªG¥HVÅܼƬdZ¦r¨å¦^¶Çitem¤£¬O°}¦C? True´N¸õ¨ì¼Ð¥Üi01¦ì¸mÄ~Äò°õ¦æ
   Cells(2, 7 + N * 5).Resize(Z(V & "|r"), 3) = Z(V)
   '¡ô¥O¦r¨å¤¤ªº°}¦Citem¼g¤JÀx¦s®æ¤¤
   N = N + 1
   '¡ô¥ONÅܼƲ֥[1
i01: Next
Set Z = Nothing: Erase Brr, Crr
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¶¢¤HµL¼Ö½ì¡A¦£¤HµL¬O«D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD