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

vlookup ¤½¦¡¤U©Ô«á¦³ªº·|§ì¨ì¡A¦³ªº·|¥X²{#n/a

vlookup ¤½¦¡¤U©Ô«á¦³ªº·|§ì¨ì¡A¦³ªº·|¥X²{#n/a

§Ú¦³¤u§@ªí1©M¤u§@ªí2¡A¦b¤u§@ªí1¬O¥X³f³æ¡Aªí2¬O¦X¬ù¼Ð³æ
¥X³f³æªº¶µ¥Ø¦WºÙ¦³³]©w¤U©Ô¿ï³æ¡A¥u­n¿ï¨ú¤U©Ô¿ï³æ¡A³æ¦ì¤Î¦X¬ù¼Æ¶q§Y¥i¦Û°Ê¥X²{
¦ý¥Ø«e¦³­Ó°ÝÃD¬O§Ú¼g§¹¤½¦¡«á¡A¦³ªº·|¥X²{¥¿½Tªºµ²ªG¡A¦³ªº«h·|¥X²{#N/A
§Ú¼gªº¤½¦¡¦p¤U
=IF(E9="","",VLOOKUP(E9,¦X¬ù!$B$3:$D$2470,2,0))
¬d¤å»¡¦³¥i¯à¬OÀx¦s®æªº®æ¦¡¡A©Ò¥H®æ¦¡¥þ§ï¬°³q¥Î®æ¦¡
¥X²{#N/Aªº¦a¤èÁÙ¬O#N/A
·Q½Ð°ÝÁÙ¦³¤°»ò­ì¦]·|³y¦¨³¡¥÷¥iŪ¨ú¡A³¡¥÷µLªkŪ¨ú¡C

³y¦¨ªº­ì¦]¦³«Ü¦h¡A«Øij§A§âÀɮפW¶Ç¤W¨Ó¬Ý¬Ý
°Ê©À¦¨¦]¿n¦]¦¨ªG by huijuang

TOP

¦^´_ 1# §±æ¢

    ¦]¬°¥L¤ñ¹ï¤£¨ì¸ê®Æªº®É­Ô´N·|¥X²{#N/A§r¡A¦³¤ñ¹ï¨ì¸ê®Æ´N·|¨q¥X¹ïÀ³ªº¼Æ­È¡A
    ¦pªG§ï¦¨³o¼Ë¡A¦³¤ñ¹ï¨ì´N·|¨q¥X¹ïÀ³¼Æ­È¡A¦ý¨S¤ñ¹ï¨ì¡A´N·|ªÅ¥Õ¡G
    =IF(ISERROR(VLOOKUP(E9,¦X¬ù!$B$3:$D$2470,2,0)),"",VLOOKUP(E9,¦X¬ù!$B$3:$D$2470,2,0))

    °O±oOFFICE 2007¥H¤W¦³­ÓIFERRORªº¨ç¼Æ¡A¤£¹L§Ú¨S2010ªº¨t²Î¡A¨S¿ìªk½T©w¥Îªk¡C¦pªG¨S¿ùªº¸Ü¬O¥Î¡G
    =IFERROR(VLOOKUP(E9,¦X¬ù!$B$3:$D$2470,2,0),"")

TOP

¥»©«³Ì«á¥Ñ §±æ¢ ©ó 2014-3-26 13:45 ½s¿è

§Ú­n¬d¸ßªºÀx¦s®æ½T©w³£¦³¸ê®Æ¡A©Ò¥H¤~ı±o«Ü©_©Ç~.~
ªþ¥ó¤W¶Ç¤F¡A³Â·Ð¦U¦ì«e½úÀ°§Ú¬Ý¤@¤U¡AÁÂÁÂ!!
1¡B¡u¥X³f³æ¡vªº³æ¦ì¤Î¦X¬ù¼Æ¶q¬O°Ñ·Ó¡u¦X¬ù¡v¤u§@ªí¡A¦³ªº¥i¥HŪ¨ú¡A¦³ªº¥X²{#N/A¡C
       §Ú¦Û¤v¦b²q°ÝÃDÀ³¸Ó¬O¥X¦b¡u¦X¬ù¡v©ÎªÌ¡u³]³Æ³]©w¡v¸ÌÀY¡A¦ý§¹¥þ¬Ý¤£¥X¨Ó¬O¤°»ò°ÝÃD¡C
       ¤U©Ô¿ï³æªº²M³æ©ñ¦b¡u³]³Æ³]©w¡v¸Ì¡A¬O±q¦X¬ù¸Ì¾ã²z¥X¨Óªº¡C
       µLªkŪ¨úªºÀx¦s®æ¡A§Ú¦³¦b¡u¦X¬ù¡v¤¤¶ñº¡¶À¦â¡A¦ý¼Æ¶q¤Ó¦h¡AµLªk¤@¤@¤ñ¹ï¡A©Ò¥H¥u¦³¥Î¤@¨Ç¨Ç¡C
2¡B¡u¥X³f³æ¡vªºKÄæ¡A¬O°Ñ·Ó¡u¥X³f²M³æ¡vªºFÄæ¼Æ¶q¡A¥Øªº¬O­n²Î­p¥X³f²M³æ¤¤¬Û¦P¦WºÙªº¼Æ¶q
      §Ú¥Î³o­Ó¤½¦¡¡A·|¦³¼Æ¶q¦³ªº¨S¥[¨ìªº°ÝÃD¡A½Ð°Ý¦³§ó¦nªº¤½¦¡¶Ü??
      =IF(ISERROR(VLOOKUP(E9,¥X³f²M³æ!$E$3:$H$100000,2,0)),"0",VLOOKUP(E9,¥X³f²M³æ!$E$3:$H$100000,2,0))+F9

ª«®ÆºÞ²z3¤ë25¤é-5.zip (165.55 KB)

TOP

°ÝÃD¦b¡u³]³Æ³]©w¡v¸ÌÀY
§Ú±N¡u³]³Æ³]©w¡v­«·s¾ã²z¡Aª½±µ±q¦X¬ù¡v¤¤½Æ»s¹L¥h¡A°ÝÃD¤w¸Ñ¨M¡A¦³¥i¯à¬O·íªì¾ã²zªº¤p©f¾Þ§@¤W¦³»~¡A©ÎªÌ¦³¦P¨Æ°Ê¹L§a¡A«D±`·PÁ¤G¦ì¦Ê¦£¤§¤¤¯àÀ°§Ú
§Ú¨M©w¥Î¦nªº¸ê®Æ¤WÂê¤F¡C

§Úªº²Ä¤G­Ó°ÝÃD¤]Åý§Ú«ÜÀYµh¡A²Ä¤@µ§¥[¤W²Ä¤Gµ§µ²ªG¬O¥¿½Tªº¡A¦A¥[¤W²Ä¤Tµ§´NÅܦ¨´î¤F¡K
¬O¤£¬O­n¥[¤WSUM??©ÎªÌ§Ú¤£¸Ó¦b«á­±¥[¤W+F9 ??

TOP

¦^´_ 5# §±æ¢

¤£ª¾¹D²Ä¤Tµ§ÅÜ´î¡ö¬O¦ó·N
¤£¹L¦pªG²Î­pªº¸Ü
¥i¥H¥Î¤U¦Cªº¤½¦¡¬Ý¬Ý¦æ¤£¦æ
=IF(ISERROR(SUMIF(¥X³f²M³æ!E2:E2000,¥X³f³æ!E9,¥X³f²M³æ!F2:F2000)),"0",SUMIF(¥X³f²M³æ!E2:E2000,¥X³f³æ!E9,¥X³f²M³æ!F2:F2000))+F9
°Ê©À¦¨¦]¿n¦]¦¨ªG by huijuang

TOP

¨Ï¥Î =IF(ISERROR(SUMIF(¥X³f²M³æ!E2:E2000,¥X³f³æ!E9,¥X³f²M³æ!F2:F2000)),"0",SUMIF(¥X³f²M³æ!E2:E2000,¥X³f³æ!E9,¥X³f²M³æ!F2:F2000))+F9
¥[Á`ªº¼Æ¶q´N¥¿½T¤F¡AÁÂÁ©O!!

§Ú­ì¥»·Q­n¥Î=IF(E9="","",SUMIF(¥X³f²M³æ!$E$3:$E$1048576,E9,¥X³f²M³æ!$F$3:$F$1048576)+F9)¡A·Q½Ð°Ý¥[¤WISERRORªº®t§O¦b­þ??

TOP

¥»©«³Ì«á¥Ñ huijuang ©ó 2014-3-27 10:13 ½s¿è

¨Ï¥ÎsumifÀ³¸Ó¤£¥ÎISERROR
§Ú°µªº®É­Ô¬Oª½±µ®³§A¤§«eªº¤½¦¡¨Ó§ï
©Ò¥H§Ñ¤F§âISERROR®³±¼¤F

=IF(E9="","",SUMIF(¥X³f²M³æ!$E$2:$E$2000,¥X³f³æ!E9,¥X³f²M³æ!$F$2:$F$2000)+F9)
°Ê©À¦¨¦]¿n¦]¦¨ªG by huijuang

TOP

¦^´_ 4# §±æ¢

VLOOKUP¥X²{#NAªº¿ù»~¡A¤£¥~¥G§ä¤£¨ì¸ê®Æ
¥HE3¤º®e
ÂI¥ú (3F~35FªF«n¦V.¦è¥_¦V¶§¥x¤W¤è®æ¬])
¥Î=COUNTIF(¦X¬ù!B:B,¥X³f³æ!E9)¥i±o¨ì1¡Aªí¥Ü¤º®e¤@©w¦³¹ïÀ³¨ì
¦ý¬°¦ó¥X¿ù?­ì¦]¦b©óªi®ö¸¹
¤½¦¡§ï¬°=IF(E9="","",VLOOKUP(SUBSTITUTE(E9,"~","~~"),¦X¬ù!$B$2:$C$2447,2,0))
´N§ä¨ì¸ê®Æ¤F
E14»PE15¬O§A²M³æ¸ê®ÆÁä¤J¿ù»~
²M³æ¬O-¸¹¡A¦X¬ù¤º®e¬O¤U½u_

²M³æ¨Ó·½À³¸Ó»P¹ê»Ú¤½¦¡°Ñ·Óªº½d³ò¬°¤º®e¡A¥HÁקK¸ê®Æ¿é¤J¿ù»~
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 1# §±æ¢


    * ~ ¬°VLOOKUP¡BMATCH ..µ¥ªº¯S®í²Å¸¹¡A³y¦¨¬d¸ß¿ù»~

¥H¤U¨ç¼Æ¨Ï¥Î®É¤ä´©¸U¥Î¦r¤¸¡A¬d¸ß¤å¦r¤¤¦³ ? * ~ µ¥²Å¸¹­nª`·N¨Ï¥Î

COUNTIF
HLOOKUP
MATCH
SEARCH
SUMIF
VLOOKUP

¸U¥Î¦r¤¸¥i°Ñ¦Ò
http://office.microsoft.com/zh-tw/excel-help/HP005203612.aspx

¥X³f³æ!E9 = "ÂI¥ú (3F~35FªF«n¦V.¦è¥_¦V¶§¥x¤W¤è®æ¬])"
¦]¦r¦ê¤º¦³ ~ ¦r¤¸·|³y¦¨¬d¸ß¿ù»~
J9
­ì¤½¦¡ =IF(E9="","",VLOOKUP(E9,¦X¬ù!$B:$D,3,))

§ï¬° =IF(E9="","",VLOOKUP(SUBSTITUTE(E9,"~","~~"),¦X¬ù!$B:$D,3,))
©Î§ï¬° =IF(E9="","",LOOKUP(,0/(E9=¦X¬ù!$B:$B),¦X¬ù!$D:$D))


¥X³f³æ!E14 = "EMT¾É½uºÞ  1_1/2" (E51)"
¦X¬ù!B1070 = "EMT¾É½uºÞ  1-1/2" (E51)"

½Ð±N _ §ï¬° - ´N¹ï¤F

E15 : "Web ºô¸ô³q°T¦øªA¥D¾÷ 573992 Ehternet Gateway TCP.IP_SCS"
°ÝÃD¦P¤W
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¦³¦h¤Ö¤O¶q´N°µ¦h¤Ö¨Æ¡A¤£­n¤ß¦sµ¥«Ý¡Aµ¥«Ý¤~·|¸¨ªÅ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD