vlookup ¤½¦¡¤U©Ô«á¦³ªº·|§ì¨ì¡A¦³ªº·|¥X²{#n/a
- ©«¤l
- 28
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 35
- ÂI¦W
- 0
- §@·~¨t²Î
- windows
- ³nÅ骩¥»
- office2010
- ¾\ŪÅv
- 10
- ©Ê§O
- ¤k
- µù¥U®É¶¡
- 2014-3-13
- ³Ì«áµn¿ý
- 2014-4-18
|
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¥un¿ï¨ú¤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 |
|
|
|
|
|
|
- ©«¤l
- 49
- ¥DÃD
- 3
- ºëµØ
- 0
- ¿n¤À
- 74
- ÂI¦W
- 0
- §@·~¨t²Î
- windows
- ³nÅ骩¥»
- XP
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¶³ªL
- µù¥U®É¶¡
- 2014-2-13
- ³Ì«áµn¿ý
- 2020-9-18
|
³y¦¨ªºì¦]¦³«Ü¦h¡A«Øij§A§âÀɮפW¶Ç¤W¨Ó¬Ý¬Ý |
|
°Ê©À¦¨¦]¿n¦]¦¨ªG by huijuang
|
|
|
|
|
- ©«¤l
- 396
- ¥DÃD
- 12
- ºëµØ
- 0
- ¿n¤À
- 425
- ÂI¦W
- 1
- §@·~¨t²Î
- Win10
- ³nÅ骩¥»
- Office 2016
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¤¤
- µù¥U®É¶¡
- 2013-7-2
- ³Ì«áµn¿ý
- 2024-8-23
|
¦^´_ 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),"") |
|
|
|
|
|
|
- ©«¤l
- 28
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 35
- ÂI¦W
- 0
- §@·~¨t²Î
- windows
- ³nÅ骩¥»
- office2010
- ¾\ŪÅv
- 10
- ©Ê§O
- ¤k
- µù¥U®É¶¡
- 2014-3-13
- ³Ì«áµn¿ý
- 2014-4-18
|
¥»©«³Ì«á¥Ñ §±æ¢ ©ó 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¥Øªº¬On²Î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 |
|
|
|
|
|
|
- ©«¤l
- 28
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 35
- ÂI¦W
- 0
- §@·~¨t²Î
- windows
- ³nÅ骩¥»
- office2010
- ¾\ŪÅv
- 10
- ©Ê§O
- ¤k
- µù¥U®É¶¡
- 2014-3-13
- ³Ì«áµn¿ý
- 2014-4-18
|
°ÝÃ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¤£¬On¥[¤WSUM??©ÎªÌ§Ú¤£¸Ó¦b«á±¥[¤W+F9 ?? |
|
|
|
|
|
|
- ©«¤l
- 49
- ¥DÃD
- 3
- ºëµØ
- 0
- ¿n¤À
- 74
- ÂI¦W
- 0
- §@·~¨t²Î
- windows
- ³nÅ骩¥»
- XP
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¶³ªL
- µù¥U®É¶¡
- 2014-2-13
- ³Ì«áµn¿ý
- 2020-9-18
|
¦^´_ 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
|
|
|
|
|
- ©«¤l
- 28
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 35
- ÂI¦W
- 0
- §@·~¨t²Î
- windows
- ³nÅ骩¥»
- office2010
- ¾\ŪÅv
- 10
- ©Ê§O
- ¤k
- µù¥U®É¶¡
- 2014-3-13
- ³Ì«áµn¿ý
- 2014-4-18
|
¨Ï¥Î =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!!
§Ú쥻·Qn¥Î=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þ?? |
|
|
|
|
|
|
- ©«¤l
- 49
- ¥DÃD
- 3
- ºëµØ
- 0
- ¿n¤À
- 74
- ÂI¦W
- 0
- §@·~¨t²Î
- windows
- ³nÅ骩¥»
- XP
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¶³ªL
- µù¥U®É¶¡
- 2014-2-13
- ³Ì«áµn¿ý
- 2020-9-18
|
¥»©«³Ì«á¥Ñ 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
|
|
|
|
|
- ©«¤l
- 2025
- ¥DÃD
- 13
- ºëµØ
- 0
- ¿n¤À
- 2053
- ÂI¦W
- 0
- §@·~¨t²Î
- WIN7
- ³nÅ骩¥»
- Office2007
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¥_¥«
- µù¥U®É¶¡
- 2011-3-2
- ³Ì«áµn¿ý
- 2024-3-14
|
¦^´_ 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¤½¦¡
|
|
|
|
|