ªð¦^¦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

¦^´_ 13# ML089


    ¦n¡AÁÂÁ§A!!

TOP

¦^´_ 12# §±æ¢

§A­n«ö¦U·N¨£¤U¤èªº¦^ÂСA¨t²Î¤~·|³qª¾¸Ó¤H­û¡A¤£µM§Aªº·N¨£®e©ö³Q©¿²¤¹L¥h¡C

¦³ªÅ¦h¨Ó¥»°ÏÆ[¼¯§O¤Hªº¤½¦¡¡A«Ü§Ö´N¯à¶i¨B
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

ÁÂÁÂ!!
§ÚÁ`ı±o¨ç¼Æ¦nÃø¡A¥i¬O¶W¦n¥Îªº¡A³o¦¸¯uªº­n»{¯uªº¾Ç¤F

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

¦^´_ 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

¥»©«³Ì«á¥Ñ 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

¨Ï¥Î =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

¦^´_ 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

°ÝÃ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

        ÀR«ä¦Û¦b : §Ú­Ì³Ì¤jªº¼Ä¤H¤£¬O§O¤H¡D¥i¯à¬O¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD