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

[µo°Ý] ¦p¦ó¥H¦h­ÓÄæ¦ì¬°¤ñ¹ï±ø¥ó¡A±N©Ò¹ïÀ³¨ìªº¸ê®Æ¦^¶Ç¶i·s³øªí

¥»©«³Ì«á¥Ñ daniel91276 ©ó 2011-3-16 15:59 ½s¿è

¦^´_ 10# ANGELA


    ANGELA¤j¤j±z¦n¡A­º¥ý«D±`ÁÂÁ±zªº¸Ñµª¨Ï§Ú¤F¸Ñ¨Ï¥Î INDIRECT »Ý±N³øªí¥´¶}

    ¡u¦pªG¤@©w­n¥Î¦X¨Ö®æ,¥i±NA6,A7,A8³£¿é¤JCL0,¦A¦b¨ä¥¦ªÅ¥Õªº®æ¤l°²³]¬O
    AA6,AA7,AA8¦X¨Ö¦A¥Î®æ¦¡¨ê¨êA6,A7, A8§Y¥i¡v³o¤@¬qÁÙ¬O¦³ÂI¤£¤ÓÀ´¡A¯à¤£¯à
    ¦A¸Ô²Ó¤@ÂI¡C¤£¦n·N«ä¤p§Ì¸£³UÂण¹LÅs¡C

    ¦pªG§Ú¥i¥H§âTEST³øªí¥k¤èªº±ø¥óÅܦ¨¨S¦³Àx¦s®æ(¦p¤U¹Ï)¡A¨º¬O§_´N¥i¥H³æ¯Â­×§ï¤@¤U
    VLOOKUPªº¤½¦¡§Y¥i¡H
   


    7¼Óªº¤½¦¡ §Ú¦³¸ÕµÛ¨Ï¥Î¨Ã¥ÎCHAR(65535)¨ú¥N öT¡A¦ý¬O±o¨ìªºµª®×¤´µM¬O NA
   

TOP

¥»©«³Ì«á¥Ñ ANGELA ©ó 2011-3-16 16:54 ½s¿è

¦^´_ 11# daniel91276
°}¦C¤½¦¡»ÝCTRL+SHIFT+ENTER

J6,J7,J8¥ý¦X¨Ö¦A½Æ»s®æ¦¡¨ìA6,A7,A8
   

TOP

¸ê®Æ®w³¡¥÷¤£²Å¦X¸ê®Æ®w³W«h
³o¼Ë¦hÀÉ®×Â^¨ú¸ê®Æ¡A¥Î¤½¦¡³sµ²¬O«D±`¤£´¼¤§Á|
³o¼Ëªº°ÝÃD¥i¨Ï¥ÎVBA»²§U
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 12# ANGELA


   ·PÁ ANGELA ¤j¤j¡A§Ú¤w±NÀx¦s®æ®æ¦¡Åܦ¨¨C®æ¤º¦³¥i±a¤J
   ¤½¦¡ªº³¡¤À¡A°}¦C¤w¸g¥[¤W(¦p¹Ï)¡A¥i¬O¼Æ¦r©Ò±a¨ì C10 ªº­ÈÁÙ¬O¸ò C9 ªº­È¤@¼Ë¡C*(¥¿½Tªº­ÈÀ³¬°5)    :'(

TOP

¦^´_ 13# Hsieh


Hsieh ¤j¤j±z¦n¡A¤p§Ìª¾¹D¦pªG³z¹L VBA ·|¤è«K«Ü¦h¡A¦ý¤p§Ì¥Ø«e¥u¯à¹ï"°ò¥»"ªº excel °µÀ³¥Î
¦pªG¤W¤Éªºµ{¦¡½X´N¤£¦æ¤F><¡C
©Ò¥H¥u¦n²Â¤H¥Î²Â¤èªk¡AºCºC¥H¤½¦¡±a¤J¦A¤gªk·Ò¿û¡A½Ðª©¤j¨£½Ì!

TOP

¦^´_ 15# daniel91276

³o¤£¬O§A­n¤èªk¨ú±Ëªº°ÝÃD
°ò¥»¤W¨ç¼Æ­n®e©ö°µ¨ìªº¸Ü¡A§Aªº¸ê®Æ¥²¶·²Å¦X¸ê®Æ®w«Ø¥ß³W«h
¤£¨Ï¥Î¦X¨ÖÀx¦s®æ¡A¨C¦C°O¿ý¤@µ§¸ê®Æ¡A¯Á¤Þ©ú½T
§Aªºªí®æ¨S¤@¼Ë²Å¦X¡A­n¥Î¨ç¼Æ©ÎEXCEL¤º«Ø¥\¯à¨Ó§¹¦¨
Ãø«×³£»·¤ñVBA¨Óªº°ª
§Ú¨Ã«D»¡«D±o¨Ï¥ÎVBA¡A­Y­n´î¤Ö³Â·Ð³Ì­«­nÁÙ¬O«Ø¥ß¸ê®Æªí®É¦h¤UÂI¥\¤Ò
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ ANGELA ©ó 2011-3-16 19:00 ½s¿è

¦^´_ 14# daniel91276

VLOOKUP½T¹ê¦æ¤£³q¥ÎLOOKUP§a

    =LOOKUP(2,1/((C$4&$A6=INDIRECT("["&$C$4&".xls]b!a6:A176"))*(B6=INDIRECT("["&$C$4&".xls]b!B6:B176"))),OFFSET(INDIRECT("["&$C$4&".xls]b!a6:A176"),,MATCH($A$1,INDIRECT("["&C$4&".xls]b!b5:l5"))))
¸ê®Æ®w¤£«ö³W«h«Ø¥ß½T¹ê¼W¥[¤£¤Ö³Â·Ð.¦X¨Ö®æ³Ì¦n¥u¥Î¦b¤£¼v响¹Bºâªº¦a¤è.

TOP

¥»©«³Ì«á¥Ñ ANGELA ©ó 2011-3-18 00:37 ½s¿è

¦^´_ 17# ANGELA


ANGELA ¤j¤j±z¦n¡A¦]¬°³o¦¸ªº¸ê®Æ¤]¬O¤@¥÷¤w¸g°µ¦nªº³øªí¡A©Ò¥H¨S¦³¿ìªk¨Ì·Ó¥¿±`±o¸ê®Æ®w¤è¦¡»s§@
³y¦¨±zªº¤£«K¯uªº«D±`©êºp¡C
±z«ØijªºLOOKUP¤½¦¡ÁÙ¬O·|±a¥X NA¡A©Ò¥H§Ú§â¹Ï¦A¦¸¶K¤W¨Óµ¹±z¬Ý¬Ý¡A¬O¤£¬O§Ú¦³¦a¤è§Ë¿ù¤F


¤£¦n·N«ä  ¤@ª½³Â·Ð±z¡A¦pªG¬O§Ú¦b¾Þ§@¤W­±¦³°ÝÃD¡A¥i§_¶K¹Ïµ¹§Ú¬Ý©O¡H  «D±`·PÁ±z!!
¦X¨Ö®æ¦³§ï¤F¶Ü?°Ñ·Ó¤Î³Q°Ñ·Ó³£­n¥Î®æ¦¡¨ê¨ê¹L¨Ì12¼Óªº¤è¦¡.

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2011-3-17 19:32 ½s¿è

¦^´_ 18# daniel91276
¬JµM±z¦p¦¹°í«ù
¿ï¨úC6Àx¦s®æ
©w¸q¦WºÙ
a=MOD(ROW(),3)
x=OFFSET(B!$C6,-MOD(ROW(),3),-2)
y=B!C$4
z=y&x
¥´¶}©Ò¦³¬¡­¶Ã¯903.xls~912.xls
TEST.xls
C6=IF(ISERROR(MATCH(z,INDIRECT("["&y&".xls]B!$A:$A"),0)),"",INDEX(INDIRECT("["&y&".xls]B!$C:$C"),MATCH(z,INDIRECT("["&y&".xls]B!$A:$A"),0)+a,))
¦V¥k¦V¤U½Æ»s
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 19# Hsieh


«D±`·PÁ Hsieh ¤j¤j¡A§Ú¤w¸g¦¨¥\ªº§¹¦¨¤F¡A¥u¬O­è­èÀˬd¤F¤@¤U¡A
µo²{³o¼Ëªº¤èªk¬O¹ïÀ³¨C­Ó³øªíªº¦ì¸m¡A¦ý¥Ñ©ó³øªí¸Ì­±¦P¤@Äæ¦ì¥i¯à
·|¦]¬°¤H­ûªº²§°Ê³y¦¨¸ê®ÆµLªk¹ïÀ³¥¿½T¡C

·Q½Ð°Ý¦³¨S¦³¿ìªk¼W¥[¤ñ¹ï±ø¥ó¨Ó§ì¨ú¥¿½Tªº¸ê®Æ¡HÁÙ¬O¥u¯à¤@­Ó¤@­Ó§â¸ê®Æ½Õ¾ã¨ì¦ì¦A¶i¦æ³B²z¡C

TOP

        ÀR«ä¦Û¦b : ¡i¬O§_µo´§¤F¨}¯à¡H¡j¤H¶¡¹Ø©R¦]¬°µu¼È¡A¤~§óÅã±o¬Ã¶Q¡CÃø±o¨Ó¤@½ë¤H¶¡¡AÀ³°Ý¬O§_¬°¤H¶¡µo´§¤F¦Û¤vªº¨}¯à¡A¦Ó¤£­n¤@¨ý¨Dªø¹Ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD