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

[µo°Ý] ¦p¦ó¥Î¤½¦¡Åý¥L¦Û°Ê¹ïÀ³­ì©l¸ê®Æ

[µo°Ý] ¦p¦ó¥Î¤½¦¡Åý¥L¦Û°Ê¹ïÀ³­ì©l¸ê®Æ

¥»©«³Ì«á¥Ñ maomin ©ó 2010-7-21 21:59 ½s¿è

AAA.rar (5.52 KB)

­ì©l¸ê®Æ¬O¤@¦ê³sÄòÄò¸ê®Æº¸¥B°¸¦Ó·|ÅÜ°Ê

SHEET2¬O¨C©P¥²¶·µoµ¹²{³õªº¸ê®Æ,Åý¦o­Ìª¾¹D¨C¤Ñ­n¥X­þ¨Ç­q³æ

SHEET2¥i¥H¥Î¤½¦¡Åý¥L¦Û°Ê¹ïÀ³­ì©l¸ê®Æ¶Ü?³o¼Ë§Ú´N¤£¥Î¤@­Ó¤@­Ó¥´¤F,¦Ó¥BÁÙ·|¥´¿ù

¨Ò¦p:§Ú¦b¤W­±¤é´Á¥´7/5¸¹
¹w­p¤é´Á      LOT           «È¤á
  6/21       990387          PIG
  7/12       990388          PIG
  6/17       990822-7       PIG
     .                .               .
     .                .               .

¦^´_ 1# maomin


    A3°}¦C¤½¦¡
{=IF(ROW($A1)>COUNTIF(­ì©l¸ê®Æ!$A:$A,A$1),"",INDIRECT("­ì©l¸ê®Æ!R"&SMALL(IF(­ì©l¸ê®Æ!$A$1:$A$121=A$1,ROW($1:$121)),ROW($A1))&"C"&COLUMN()+1-INT((COLUMN()-1)/3)*3,0))}
   B3°}¦C¤½¦¡
{=IF(ROW($A1)>COUNTIF(­ì©l¸ê®Æ!$A:$A,A$1),"",INDIRECT("­ì©l¸ê®Æ!R"&SMALL(IF(­ì©l¸ê®Æ!$A$1:$A$121=A$1,ROW($1:$121)),ROW($A1))&"C"&COLUMN()+1-INT((COLUMN()-1)/3)*3,0))}
  C3°}¦C¤½¦¡
{=IF(ROW($A1)>COUNTIF(­ì©l¸ê®Æ!$A:$A,A$1),"",INDIRECT("­ì©l¸ê®Æ!R"&SMALL(IF(­ì©l¸ê®Æ!$A$1:$A$121=A$1,ROW($1:$121)),ROW($A1))&"C"&COLUMN()+1-INT((COLUMN()-1)/3)*3,0))}
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥H¤U¬O§Ú·Q¨ìªº¤è¦¡¤ñ¸ûª½Ä±¤@¨Ç¨Ñ§A°Ñ¦Ò :
1. ¥ý¦b¨C¤@®æ«e­±·s¼W¤@ªÅ¥ÕÄæ(¨Ò¦p "A"¡B"E" ...Äæ),°µ¬°¯Á¤Þ­È¥H§ä¥X¨C¤@µ§­n¨qªº¸ê®Æ.

2. ¦b¯Á¤Þ­È¨ºÄ檺²Ä3¦C(§Y¸ê®ÆÅã¥Üªº¶}©l¦C)¨Ì¾Ú¸Ó®æ¤W¤èªº¤é´Á§ä¥X"­ì©l¸ê®Æ" ¤¤²Ä¤@µ§¸Ó¤é´Á¸ê®Æªº¦C¸¹.
A3 = MATCH(B$1,­ì©l¸ê®Æ!$A$1A$500,0)
  *** E3 ¡B I3 ... ¬Ò¥iª½±µ½Æ»s¤½¦¡®M¥Î.

3. ¨Ì¦¸¼W¥[¯Á¤ÞÄ檺¦C¸¹¼Æ¦r,¨Ã¤ñ¹ï "­ì©l¸ê®Æ" ¤¤¸Ó¦C¸¹ªº¤é´Á¬O§_»P¤W¤èªº¤é´Á¬Û¦P,­Y¬Û¦P«hÅã¥Ü¥X¸Ó¦C¸¹,­Y¤£¦P«hªí¥Ü¸Ó¤é´Á¸ê®Æ¤w§ä´M§¹²¦,¥H¤U¬Ò¤£¦AÅã¥Ü¥ô¦ó¸ê®Æ.
A4 = IF(A3="","",IF(INDIRECT(CONCATENATE("­ì©l¸ê®Æ!","A",A3+1))=B$1,A3+1,""))
  *** E5 ¡B I5 ... ¬Ò¥iª½±µ½Æ»s¤½¦¡®M¥Î.
  *** A5 ¥H¤U¬Ò¥iª½±µ½Æ»s¤½¦¡®M¥Î.

4. ¨Ì¾Ú¯Á¤ÞÄ檺¦C¸¹¼Æ¦r¨Ì¦¸±N "¹w­p¤é´Á"¡B"L O T"¡B"«È¤á"¤TÄ檺¸ê®Æ±a¥X¨Ó.
B3 = IF(A3="","",INDIRECT(CONCATENATE("­ì©l¸ê®Æ!","B",A3)))
C3 = IF(A3="","",INDIRECT(CONCATENATE("­ì©l¸ê®Æ!","C",A3)))
D3 = IF(A3="","",INDIRECT(CONCATENATE("­ì©l¸ê®Æ!","D",A3)))
  *** F3 - H3 ¡B J3 - L3 ... ¬Ò¥iª½±µ½Æ»s¤½¦¡®M¥Î.
  *** B4 - D4 ¥H¤U¬Ò¥iª½±µ½Æ»s¤½¦¡®M¥Î.

5. ¤½¦¡¥u©µ¦ù¨ì¦UÄ檺²Ä70¦C, ­YÁٻݭn©µ¦ù¥u»Ý¥H²Ä70¦C¬°¤½¦¡½d¥»,¦P®É±N¥|­Ó¸ê®ÆÄæ¤@ª½½Æ»s¤U¥h(§YMark B70 ~ D70 µM«á´å¼Ð²¾¨ìD70¥k¤U¨¤,«öµÛ·Æ¹«¥ªÁ䩹¤U©Ô§Y¥i)§Y¥i.

6. ³Ì«á¦A±N "A"¡B"E" ...µ¥¯Á¤ÞÄæ "ÁôÂÃ" °_¨Ó§Y¥i.

7. ´ú¸Õ¤è¦¡ : ª½±µ­×§ï "B1"¡B"F1"...µ¥Àx¦s®æªº¤ºªº¤é´Á, ©³¤U B3 - D3 ¤Î¨ä¥L "B" - "D" Ä檺­È·|¬ÛÀ³¦ÓÅÜ.

8. ·í©óÀx¦s®æ¨Ï¥Î±a¦³¯Á¤Þ¥\¯àªº¤½¦¡®É,½Ð°È¥²°O±o­ì©l¸ê®Æ¤£­n¦³§R°£¾ã¦C(©Î¬Y¨Ç±¡§Î¤Uªº¾ãÄæ)ªº±¡§Î,¤£µM·|¾É­P¤½¦¡¥X²{¿ù»~.

µ{¦¡¦pªþ¥ó,½Ð°Ñ¦Ò.

AAA_a.zip (17.43 KB)

TOP

§Úı±oHsieh¤j¤jªº¤è¦¡¤ñ¸û¦n
¤£¥Î¥[»²§UÄæ
¤S¾Ç¨ì¤@­Ó·s¤èªk
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

§Ú¤]¤@ª½¦b¦VHsieh¤j¾Ç²ß¤¤,
©Î³\¬O¦Û¤v Excel µ{«×ÁÙ¬O¤Ó²L,
±`±`¦³¨Ç¤½¦¡¬Ýªº¤£¬O«ÜÀ´,
´N¹³¤W­±¨º¨Ç "°}¦C¤½¦¡" ¤å¦r¦n¹³´N¤£¯àª½±µ¶K¨ì A3 - C3 Àx¦s®æ,
¦b§Ú³o·|³Q¿ëÃѦ¨ "¤å¦r" ¦Ó«D¤½¦¡.

¦ý­Y±N { »P } ®³±¼¤º®e¤S·|Åܦ¨¨ä¤W¤@®æªº¤º®e,
¥B¤]¤£¯à¥Î·Æ¹«¤U©Ôªº¤è¦¡½Æ»s¨ì©³¤UªºÀx¦s®æ¤º, (·|¦³¿ù»~).

§Ú·QÀ³¸Ó¬O§ÚÁÙ¤£©ú¥Õ "°}¦C¤½¦¡" ­n«ç»ò®M¥Î¨ìÀx¦s®æ¤º.

TOP

·PÁ¦U¦ì¤j¤jªº«ü±Ð

  ÁÂÁ§A­Ì,°ÝÃD¤w¸g¸Ñ¨M¤F

TOP

¦^´_ 5# luhpro

½Æ»s¤½¦¡¶K¤W«á,±N{}§R°£
CTRL+SHIFT+ENTER¿é¤J
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2010-7-22 22:56 ½s¿è

¦^´_ 7# Hsieh


¶â...
­ì¨Ó­n¥Î Ctrl + Shift + Enter ¿é¤J¤~·|¥X²{ { },
¤§«e¦bºô¯¸¤W¬Ý¨ì«o¤@ª½¸Õ¤£¥X¨Ó,
§ä»¡©ú¡B¬d®Ñ¡B¤Wºô¯¸³£¤£±o¨äªù¦Ó¤J,
¤Sµo²{¦n¹³³£¨S¦³¤H¦³°Ý¹L·Q»¡¨º¥i¯à¬O±`ÃÑ,
¦p¦¹´N§ó¤£¦n·N«ä°Ý¤F.

¥t¥~¤£ª¾¹D ¦³ »P ¨S¦³ { } ªº®t§O¦b­þ¸Ì©O?
ÁÂÁ§Aªº¸Ñµª.^^

¦^´_ 9# gong
¶â...³o¼Ë§Ú´N¤F¸Ñ¤F,
ÁÂÁ gong  ¤jªº¸Ñµª.

TOP

{}¬O°}¦C¤½¦¡
°}¦C¥i¥H¬O¤@²Õ¼Æ,¦b¤@­ÓÀx¦s®æ¤º¦s¦b
±N¸Ó¤½¦¡¥þ¿ï«öf9¬Ý¬Ý

¦pªG¨S¦³{}
¨º´N¬O¤@¯ë¤½¦¡,¦b¸ÓÀx¦s®æ¤º¦s¦bµÛ¤@­Ó­È,
¸Ó­È¬°¸Ó°}¦C²Ä¤@²Õ¼Æ

®t§O¬°¤@²Õ¼Æ»P¤@­Ó¼Æ
ª¾¤§¬°ª¾¤§¡A¤£ª¾¬°¤£ª¾¡A¸Û¹ê¤]¡I

TOP

¦^´_ 1# maomin


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
ÁÂÁ¦U¦ì«e½ú«ü¾É
«á¾ÇÂǦ¹¥DÃD½m²ßVBA¦r¨å»P°}¦C,½Ð¦U¦ì«e½ú¦A«ü¾É

¸ê®Æªí:


µ²ªGªí°õ¦æ«e:


°õ¦æµ²ªG:


Option Explicit
Sub TEST()
Dim Brr, Crr(1 To 100, 1 To 3), i&, j&, k&, TT, T(1 To 4), Y, A, D$
Set Y = CreateObject("Scripting.Dictionary")
Brr = Range([­ì©l¸ê®Æ!D2], [­ì©l¸ê®Æ!A65536].End(3))
For i = 1 To UBound(Brr)
   For k = 1 To 4: T(k) = Brr(i, k): TT = TT & "|" & T(k): Next
   If Y(TT) <> "" Or T(1) = "" Then GoTo i01
   A = Y(T(1) & "/a")
   If Not IsArray(A) Then A = Crr
   D = T(1): Y(D) = Y(D) + 1
   For j = 1 To 3: A(Y(D), j) = T(j + 1): Next
   Y(T(1) & "/a") = A: Y(TT) = 1: TT = ""
i01:
Next
With Sheets("SHEET2")
   .UsedRange.Offset(2, 0).ClearContents
   For i = 1 To 13 Step 3
      D = .Cells(1, i): .Cells(3, i).Resize(Y(D), 3) = Y(D & "/a")
   Next
   Application.Goto .[A1]
End With
Set Y = Nothing: Erase Brr, Crr, T, A
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¤H­nª¾ºÖ¡B±¤ºÖ¡B¦A³yºÖ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD