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

[µo°Ý] ¦p¦ó§ä¥X¯S©w¼Æ­È©Ò¹ïÀ³ªºÀx¦s®æ¤º®e

[µo°Ý] ¦p¦ó§ä¥X¯S©w¼Æ­È©Ò¹ïÀ³ªºÀx¦s®æ¤º®e

¦U¦ì¤j¤j

¤p§Ì¦³¤A­ÓÀɮ׬Oª½±µ¶×¤Jsheet1«á, §Q¥ÎA/B¨âÄæ©Ò²£¥Íªº­×§ï«e/«á¼Æ­È(CÄæ©MDÄæ),

¦A¥h¤ñ¹ïsheet2ªºD:VÄæ(¨ä¤¤F1:V1¥Nªí0-16¼Æ¦r)§ä¥X¸ÓÄæ©Ò¹ïÀ³ªºÀx¦s®æ,

«ö¤U¤A­Ó«ö¶s«á, ±N¤ñ¹ïµ²ªGÂà´«(¦pªþÀÉ»¡©ú).

·Ð½Ð¥ý¶i«ü¾É

TEST7.rar (22.97 KB)

¦^´_ 1# luke
  1. Sub ex()
  2. Dim Ar(), A As Range, C As Range, B As Range
  3. Set d = CreateObject("Scripting.Dictionary")
  4. With sheet2
  5. For Each A In .Range(.[D2], .[D2].End(xlDown))
  6.    x = A & A.Offset(, 1)
  7.    For Each C In .Range(.[F1], .[F1].End(xlToRight))
  8.    d(x & C) = .Cells(A.Row, C.Column)
  9.    Next
  10. Next
  11. End With
  12. With sheet1
  13. For Each A In .Range(.[A1], .[A1].End(xlDown))
  14. Set B = A.Resize(, 4)
  15. p = IIf(d(B(1) & B(2) & B(3)) = "", Replace(B(2), "X", ""), d(B(1) & B(2) & B(3)))
  16. n = IIf(d(B(1) & B(2) & B(4)) = "", Replace(B(2), "X", ""), d(B(1) & B(2) & B(4)))
  17. A.Offset(, 5).Resize(, 4) = Array(B(1), B(2), p, n)
  18. Next
  19. End With
  20. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 1# luke

ª½±µ¥Î¤u§@ªí¨ç¼Æ,°Ñ¦Ò¥Î

F1=IF(A1="","",A1)  ¥k©Ô¤U©Ô

H1=IF(C1="","",IF(ISNA(INDEX(sheet2!$A$1:$V$16,MATCH($A1&$B1,sheet2!$D$1:$D$16&sheet2!$E$1:$E$16,0),MATCH(C1,sheet2!$A$1:$V$1,0))),C1,INDEX(sheet2!$A$1:$V$16,MATCH($A1&$B1,sheet2!$D$1:$D$16&sheet2!$E$1:$E$16,0),MATCH(C1,sheet2!$A$1:$V$1,0))))
°}¦C¤½¦¡   ¥k©Ô¤U©Ô

TOP

¦^´_ 2# Hsieh


    ´ú¸ÕOK

    ÁÂÁÂH¶Wª©¤j

TOP

¦^´_ 3# register313


    ¥N¤JH1°}¦C¤½¦¡«á¥X²{¿ù»~ #VALUE!

    ÀˬdINDEX(sheet2!$A$1:$V$16,MATCH($A1&$B1,sheet2!$D$1:$D$16&sheet2!$E$1:$E$16,0), ¦¹³B¦³°ÝÃD

    ¥H¤W

TOP

¦^´_ 5# luke

°}¦C¤½¦¡
¿é¤J¦n¤½¦¡ ­n«ö²Õ¦XÁäCtrl+Shift+Enter¨Ó§@½T©w
­ì¤½¦¡ªº«e«á·|¦Û°Ê¥[¤W{ }

TOP

¦^´_ 6# register313

´ú¸ÕOK
   
ÁÂÁÂregister313

TOP

¦^´_ 5# luke


    register313¤j¤j, ¦U¦ì¥ý¶i

     ¤µ¤Ñ´ú¦¡°}¦C¤½¦¡µo²{¤@­Ó°ÝÃD "­Y¸ê®Æ¶W¹L1¸Uµ§®É,¹q¸£ÅܺC¶]¤£°Ê"

      ½Ð°Ý¦p¦ó©ñ¤J°}¦C¤½¦¡©óVBA¤¤?

      ·Ð½Ð¥ý¶i¤£§[«ü¾É ÁÂÁÂ!

TOP

¦^´_ 8# luke

©ó¤u§@ªí¨Ï¥Î°}¦C¤½¦¡µo²{¤@­Ó°ÝÃD "­Y¸ê®Æ¶W¹L1¸Uµ§®É,¹q¸£ÅܺC¶]¤£°Ê"
¨º¥ÎVBAªº¤è¦¡¼g°}¦C¤½¦¡©ó¤u§@ªíÁÙ¤£¬O¤@¼Ë,´«´ö¤£´«ÃÄ
=>³£¬O¥Î¤F°}¦C¤½¦¡

¨Ï¥Î2# Hsieh¶Wª©ªºVBAµ{¦¡§a(¤@¯ë¨Ï¥ÎVBA¤ñ¸û¤Ö¦b¤u§@ªí¼g¤J¤½¦¡)

TOP

¥»©«³Ì«á¥Ñ luke ©ó 2012-4-13 09:33 ½s¿è

¦^´_ 9# register313


register313¤j¤j

ÁÂÁ±zªº¦^µª, §Ú¸I¨ìªº°ÝÃD¦p¤U¡F
1.H¶Wª©ªºµ{¦¡¬O¹ïsheet1 ªºBÄæ°Ñ¼Æ¯S©w¼Æ­È¶i¦æ´À¥N¡A­YBÄ檺¼Æ­È¹w³]¬°¤Q¤»¶i¦ì(«D¯S©w­È)®É, ·|²£¥Íµ{¦¡§PÂ_¤W¿ù»~¡A½Ð°Ñ¦ÒªþÀɬõ¦â¼Ð¥Ü, ¥¿½TÀ³¬°¯»¬õ¦â¼Ð¥Ü¡C
2.­Ysheet1ªíC/D¨âÄ檺­×§ï«e/«á¼Æ¦r¬°ªÅ¥Õ®É, ¤]·|²£¥Íµ{¦¡§PÂ_¤W¿ù»~¦p²Ä13¦C¬õ¦r¡C
3.­Ysheet1ªíAÄ欰¼Æ¦r100®É, ¤]·|²£¥Íµ{¦¡§PÂ_¤W¿ù»~¦p²Ä14+15¦C¬õ¦r¡¨oo¡¨¡C

·Ð½Ð¥ý¶i«ü¾É¦p¦ó­×§ïµ{¦¡
¤£³Ó·P¿E!
TEST7-1.rar (13.15 KB)

TOP

        ÀR«ä¦Û¦b : ¨C¤ÑµL©Ò¨Æ¨Æ¡A¬O¤H¥Íªº®ø¶OªÌ¡A¿n·¥¡B¦³¥Î¤~¬O¤H¥Íªº³Ð³yªÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD