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

[µo°Ý] ¦p¦óÅý¤½¦¡²£¥Íªº©ú²Óªí¥i¥H¥[Á`

[µo°Ý] ¦p¦óÅý¤½¦¡²£¥Íªº©ú²Óªí¥i¥H¥[Á`

¬°¦ó¤½¦¡²£¥Íªº¼Æ­ÈµLªk¥[Á`¡A¦]¨Ó·½Á`ªíÄæ¬OÄÝ©ó
¼Æ­È¡A¹¢¿ï¹L¨Ó½T¤£¯à¥[Á`¡A­ì¦]¬°¦ó¡A¤½¦¡À³¦p¦ó­×¥¿¡C

Book6.rar (4.62 KB)

§Æ±æ¤ä«ù!

¥»©«³Ì«á¥Ñ p212 ©ó 2013-9-12 10:52 ½s¿è

¦^´_ 1# s7659109
¥H¡uA001¡v¤u§@ªí¤§H3Àx¦s®æ¤½¦¡¬°¨Ò¡A½Ð­×§ï¦¨
{=--(INDEX(Á`ªí!H:H,SMALL(IF(Á`ªí!$B$1:$B$21=$A$1,ROW(Á`ªí!$B$1:$B$21),4^8),ROW(1:1)))&"")}
¤§«á¦V¤U½Æ»s¤½¦¡§Y¥i¡A½Ð°Ñ¦ÒHsieh¶Wª©ªº«ü¾É http://forum.twbts.com/thread-1277-1-1.html

TOP

¦ý¦pªG¤½¦¡¤@ª½©¹¤U©Ô¡A·|¥X²{¿ù»~(#VALUE!),·|¼v
ÅT¥[Á`¡C

Book6-1.rar (5.06 KB)

§Æ±æ¤ä«ù!

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2013-9-12 17:33 ½s¿è

¦^´_ 3# s7659109
¥H¡uA001¡v¤u§@ªí¤§H3Àx¦s®æ¤½¦¡¬°¨Ò¡A½Ð­×§ï¦¨
{=IFERROR(--(INDEX(Á`ªí!H:H,SMALL(IF(Á`ªí!$B$1:$B$21=$A$1,ROW(Á`ªí!$B$1:$B$21),4^8),ROW(1:1)))&""),"")}
©Î
{=IFERROR((INDEX(Á`ªí!H:H,SMALL(IF(Á`ªí!$B$1:$B$21=$A$1,ROW(Á`ªí!$B$1:$B$21),4^8),ROW(1:1)))&"")*1,"")}
À³¥i­×¥¿¡u¤½¦¡¤@ª½©¹¤U©Ô¡A·|¥X²{¿ù»~#VALUE!¡vªº°ÝÃD¡A½Ð°Ñ¦Ò¡I

TOP

¦^´_ 1# s7659109

VBA»²§U¨Óªº®e©ö
  1. Sub ex()
  2. Dim Ay(2), Ary()
  3. Set dic = CreateObject("Scripting.Dictionary")
  4. With Sheets("Á`ªí")
  5.     For Each a In .Range(.[B3], [B3].End(xlDown))
  6.       If IsEmpty(dic(a.Value)) Then
  7.          ar = Array(a.Value, "", "¶i³f", "", "", "¥X³f", "", "")
  8.          Ay(0) = ar
  9.          Ay(1) = .[A2:H2].Value
  10.          Ay(2) = a.Offset(, -1).Resize(, 8).Value
  11.          dic(a.Value) = Ay
  12.          Else
  13.          Ary = dic(a.Value)
  14.          ReDim Preserve Ary(UBound(Ary) + 1)
  15.          Ary(UBound(Ary)) = a.Offset(, -1).Resize(, 8).Value
  16.          dic(a.Value) = Ary
  17.         End If
  18.       Next
  19. End With
  20. For Each ky In dic.keys
  21.    With Sheets(ky)
  22.      .[A1].Resize(UBound(dic(ky)) + 1, 8) = Application.Transpose(Application.Transpose(dic(ky)))
  23.      .Cells(.Rows.Count, 1).End(xlUp).Offset(1) = "¦X­p"
  24.      .Cells(.Rows.Count, 8).End(xlUp).Offset(1) = "=SUM(R1C:R[-1]C)"
  25.    End With
  26. Next
  27. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

1.¦]ªºoffice¬O2003µLªk¨Ï¥Îiferrorªº¨ç¼Æ¡A½Ð°Ý­n¦p¦ó­×§ï¡A¥ta001»P¨ä¥La002¡Ba003ªºÄæ¦ì³£¤@¼Ë¡A¥i§_±Na001ªº¤½¦¡¤@¦¸®M¥Î¨ìa002¡Ba003¡C
2.¦p¦ó¤u§@ªíÂX¤j¨ì50­Ó©Î200(a001~a200)­Ó°µªk¬O§_¤]¤@¼Ë¡C
§Æ±æ¤ä«ù!

TOP

¦³Ãö§Q¥Îvbªº³¡¤À¡A¬O¦b¤u§@©³½Z¤U«ö¥kÁä¶i¤JÀ˥ܵ{¦¡½X¡Aª½±µ¶K¤W¦sÀÉ¡A§Ú¸ÕµÛ¶K¤WµL¤Ï¬M¡A¬°¦ó¡H
§Æ±æ¤ä«ù!

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2013-9-13 10:37 ½s¿è

¦^´_ 6# s7659109
­Y¬OExcel 2003¡A¤½¦¡·|Åܱo«Üªø
{=IF(ISERROR(--(INDEX(Á`ªí!H:H,SMALL(IF(Á`ªí!$B$1:$B$21=$A$1,ROW(Á`ªí!$B$1:$B$21),4^8),ROW(1:1)))&"")),"",--(INDEX(Á`ªí!H:H,SMALL(IF(Á`ªí!$B$1:$B$21=$A$1,ROW(Á`ªí!$B$1:$B$21),4^8),ROW(1:1)))&""))}
©Î
{=IF(ISERROR((INDEX(Á`ªí!H:H,SMALL(IF(Á`ªí!$B$1:$B$21=$A$1,ROW(Á`ªí!$B$1:$B$21),4^8),ROW(1:1)))&"")*1),"",(INDEX(Á`ªí!H:H,SMALL(IF(Á`ªí!$B$1:$B$21=$A$1,ROW(Á`ªí!$B$1:$B$21),4^8),ROW(1:1)))&"")*1)}
½Ð°Ñ¦Ò¡I
µù¡G½Ð«ö [¦^ÂÐ] «ö¶s¦^¬M°ÝÃD¡AµªÂЪ̤~·|±o¨ì³qª¾¡AÁÂÁ¡I

TOP

ÁÂÁ´£¿ô!¥H«á§Ú·|°O¦í¡C
§Æ±æ¤ä«ù!

TOP

        ÀR«ä¦Û¦b : ·R¤£¬O­n¨D¹ï¤è¡A¦Ó¬O­n¥Ñ¦Û¨­ªº¥I¥X¡C
ªð¦^¦Cªí ¤W¤@¥DÃD