我在挥发性工作簿中讨论了报告工作簿,以测试Excel查找方法的计算时间。该工作簿包含一个包含五列的报告,每列包含1,000行公式。每个公式从
æ娓æ¥åæ§å·¥ä½ç°¿”ä¸è®¨è®ºäºæ¥åå·¥ä½ç°¿ï¼ä»¥æµè¯Excelæ¥æ¾æ¹æ³ç计ç®æ¶é´ã该工ä½ç°¿å å«ä¸ä¸ªå å«äºåçæ¥åï¼æ¯åå å«1,000è¡å ¬å¼ãæ¯ä¸ªå ¬å¼ä»50,000è¡æ°æ®åºè¿åæ°æ®ã
为äºå¯¹è®¡ç®è¿è¡è®¡æ¶ï¼æä¾é CalcTimer.xlså·¥ä½ç°¿ï¼è¯¥å·¥ä½ç°¿å¨ä½¿ç¨Excel VBAæµè¯æ¥å计ç®æ¶é´ä¸è¿è¡äºè®¨è®ºã
æå¨è¿è¡1.60 GHzçæ§ç¬è®°æ¬çµèä¸è¿è¡äºè¿äºæµè¯ãæ¨çç»æå¯è½ä¼æ´å¿«ï¼ä½å¤§çº¦ä¸æçææ£æ¯ã
计ç®é´æ¥è´¹ç¨æ¶é´
Exceléè¦è±è´¹ä¸å®çæ¶é´æ¥æ¾ç¤ºAåä¸æ¾ç¤ºçéæºéæ©ç代ç ã尽管该æ¶é´å¯¹äºæææµè¯é½æ¯ä¸è´çï¼ä½å®å¯è½ä¼ææ²ä¸¤ä¸ªæµè¯ä¹é´çæ¯çã
å æ¤ï¼æé¦å æµè¯äºå¨ä¸ä½¿ç¨ä»»ä½æ¥æ¾å ¬å¼çæ åµä¸è®¡ç®25次æéçæ¶é´ã以ä¸ææ“å计ç®æ¶é´”å¼ååå»ä»¥ä¸è®¡ç®æ¶é´ã
计ç®æ¶é´ï¼0.141ç§ã
使ç¨æªæåºæ°æ®çExcelæ¥æ¾æ¶é´
VLOOKUPåINDEX-MATCHåå¯ç¨äºæªæåºçæ°æ®ãå¨ä»¥ä¸æè¦ä¸ï¼ææä¾äº“å ³é®å ¬å¼”ãå¨æææ åµä¸ï¼è¯·æ ¹æ®éè¦å°å®ä»¬å¤å¶å°æ¥åçå ¶ä½åºåã
å æ¤ï¼è®©æ们ççè¿äºæ¥æ¾æ¹æ³æ¯å¦ä½æ§è¡çã
VLOOKUPï¼æªåç±»çæ°æ®
æ¤è¯ç¨çä¸çå ¬å¼ä¾èµäºVLOOKUPï¼å ¶æ ¼å¼å¦ä¸ï¼
= VLOOKUPï¼lookup_valueï¼table_arrayï¼col_index_numï¼range_lookupï¼
range_lookupåæ°æ¯å¯éçãå¦æå®çå¼ä¸ºFALSEï¼åVLOOKUPè¿åä¸ä¸ªå®å ¨å¹é 项ï¼å¯ç¨äºæªæåºçæ°æ®ãå¦æ为TRUEæçç¥ï¼åè¿åè¿ä¼¼å¹é 项ï¼å¹¶ä¸å¿ é¡»ä» å¯¹å·²æåºçæ°æ®ä½¿ç¨ã
å ³é®å ¬å¼æ¯ï¼
B3ï¼= VLOOKUPï¼$ A3ï¼Dataï¼B $ 1ï¼FALSEï¼
ä¸é¢çå计ç®æ¶é´ä¼¼ä¹å¾é¿ï¼å¤§çº¦260ç§ãä½æ¯è¯·è®°ä½ï¼è¯¥æ¥åå ·æ5000个æ¥æ¾å ¬å¼ï¼æ们计ç®äº25次ãå æ¤ï¼è¿125,000次æ¥è¯¢ä¸çæ¯ä¸æ¬¡ä» è±è´¹å¤§çº¦.0011ç§ã
å计ç®æ¶é´ï¼260.781
å¨ä¸å¼ä¸è¿è¡INDEX-MATCHï¼æªåç±»çæ°æ®
æ¤è¯éªä¸çå ¬å¼åæ¶ä½¿ç¨INDEXåMATCHï¼
= INDEXï¼åèï¼row_numï¼column_numï¼area_numï¼
= MATCHï¼lookup_valueï¼lookup_arrayï¼match_typeï¼
MATCHçmatch_typeåæ°æ¯å¯éçãå®å¯ä»¥å ·æä¸ä¸ªå¼ï¼
0ï¼å®å ¨å¹é ãå¯ä»¥ç¨äºæªæåºçæ°æ®ã
1ï¼è¿ä¼¼å¹é ãï¼é»è®¤å¼ãï¼å¿ é¡»ç¨äºæååºæåºçæ°æ®ã
-1ï¼è¿ä¼¼å¹é ãå¿ é¡»ç¨äºæéåºæåºçæ°æ®ã
INDEXåMATCHå½æ°å¯ä»¥å¨ä¸ä¸ªå ¬å¼ä¸æå¨åç¬çå ¬å¼ä¸ä½¿ç¨ã该æµè¯ä½¿ç¨äºä¸ä¸ªå ¬å¼ï¼
B3ï¼= INDEXï¼Dataï¼MATCHï¼$ A3ï¼Codeï¼0ï¼ï¼B $ 1ï¼
请注æï¼ä»¥ä¸å计ç®æ¶é´ä¸VLOOKUP大è´ç¸åã
å计ç®æ¶é´ï¼253.453
ä¸¤ä¸ªå ¬å¼ä¸çINDEX-MATCHï¼æªæåºçæ°æ®
ä¸VLOOKUPä¸åï¼INDEX-MATCHæ¹æ³å¯ä»¥åä¸ºä¸¤ä¸ªå ¬å¼ãå¦å¾ä¸æ示ï¼å½å ä¸ªå ¬å¼éè¦ä»æ°æ®åºçåä¸è¡æåä¸åè¿åæ°æ®æ¶ï¼æ¤åè½é常æç¨ãéè¿è¿ç§è®¾è®¡ï¼ä¸ä¸ªMATCHå ¬å¼å¯ä»¥å®æè¾æ ¢çå·¥ä½ï¼èä»»ä½INDEXå ¬å¼é½å¯ä»¥è¿è¡è¾å¿«çå·¥ä½ãè¿å¤§å¤§å å¿«äºè®¡ç®é度ã
以ä¸æ¯ä¸»è¦å ¬å¼ï¼
B3ï¼= INDEXï¼æ°æ®ï¼$ G3ï¼B $ 1ï¼
G3ï¼= MATCHï¼$ A3ï¼ä»£ç ï¼0ï¼
å¨Lotus 1-2-3åExcelçæ©æï¼æ们被åç¥ï¼å½å ¬å¼å¼ç¨å ¬å¼ä¸æ¹å左侧çåå æ ¼æ¶ï¼å ¬å¼å°æ´å¿«å°è¿è¡è®¡ç®ãå æ¤ï¼å¦ææ¨æ¯åæè¿æ ·çèæåï¼æ¨å¯è½æ³ç¥éï¼å¦æå°ä¸å¾ä¸ç“è¡”å移尓æ°æ®1”åç左侧ï¼æ¤æ¥åæ¯å¦å¯ä»¥æ´å¿«å°è®¡ç®åºãæå°è¯äºè¯¥è®¾è®¡ï¼å¹¶ä¸è®¡ç®æ¶é´å®å ¨æ²¡æåå°å½±åã
ä»ä¸é¢ç计ç®æ¶é´å¯ä»¥çåºï¼è¯¥è®¾è®¡ä»£è¡¨äºæªæåºæ°æ®çæä½³å®è·µãå 为æ们使ç¨çæ¯å两ç§æ¹æ³ä½¿ç¨çæ¥æ¾å ¬å¼çæ°éçäºåä¹ä¸ï¼æ以æ们ç计ç®æ¶é´å¤§çº¦æ¯åæ¥çäºåä¹ä¸ã
å计ç®æ¶é´ï¼52.234
使ç¨æåºæ°æ®çExcelæ¥æ¾æ¶é´
å½VLOOKUPåMATCHå¤çæåºçæ°æ®æ¶ï¼å¯ä»¥å°å®ä»¬è®¾ç½®ä¸ºä½¿ç¨äºè¿å¶æç´¢æ¹æ³ï¼è¯¥æ¹æ³æ¯ä¸é¢è®¨è®ºçæ¹æ³å¿«å¾å¤ã
ä¸å¹¸çæ¯ï¼æ¤æ¹æ³è¿å两个å½æ°çè¿ä¼¼å¹é ãè¿å¾ä¸å¹¸ï¼å ä¸ºæ ¹æ®æçç»éªï¼å¤§å¤æ°æ¥æ¾é½éè¦å®å ¨å¹é ãä¹å°±æ¯è¯´ï¼å¦ææ们寻æ¾æ°æ®ä¸ä¸åå¨çå¼ï¼åéè¦ä½¿ç¨å ¬å¼æ¥è¿åé误å¼ãæ们ä¸å¸æä»ä»¬è¿åæä½³çæµã
幸è¿çæ¯ï¼æä¸ç§ç®åçæ¹æ³å¯ä»¥è§£å³æ¤é®é¢ï¼å¦ä¸æ示ã尽管该解å³æ¹æ¡ç计ç®æ¶é´å ä¹å¢å äºä¸åï¼ä½æé«ç精度å¼å¾ä¸è²ç代价ã
VLOOKUPï¼æåºæ°æ®
该è¯éªçå ³é®å ¬å¼ç®çææï¼å¯ä»¥ç¨ä¸¤ç§æ¹å¼ç¼åï¼
B3ï¼= VLOOKUPï¼$ A3ï¼æ°æ®ï¼B $ 1ï¼
B3ï¼= VLOOKUPï¼$ A3ï¼æ°æ®ï¼B $ 1ï¼TRUEï¼
注æï¼éè¿å¯¹æ°æ®è¿è¡æåºå¹¶ä½¿ç¨äºè¿å¶æç´¢ææ¯ï¼ç±range_lookupåæ°ç¡®å®ï¼ï¼æ们å°è®¡ç®æ¶é´ä»å¤§çº¦260ç§åå°å°å¤§çº¦åç§ã
å®é ä¸ï¼æç´¢æ¶é´å¦æ¤ä¹çï¼ä»¥è³äºå¦ææ们å°æ¥æ¾æ¬¡æ°å åï¼ååªä¼å°æ»è®¡ç®æ¶é´å¢å 约åç§ãè¿ä½¿æ们å¯ä»¥èªç±ä¿®æ¹æç´¢å ¬å¼ï¼ä»¥ä½¿æ们ä¸æåºåçæ°æ®å®å ¨å¹é ï¼
B3ï¼= IFï¼VLOOKUPï¼$ A3ï¼Dataï¼1ï¼= $ A3ï¼VLOOKUPï¼$ A3ï¼Dataï¼B $ 1ï¼ï¼NAï¼ï¼ï¼
å¨è¿éï¼æ们é¦å æ¥æ¾ä»£ç ï¼ç¶åè¿åæ¾å°ç代ç ãå¦æè¿åç代ç çäºåå§ä»£ç ï¼åæ们å®å ¨å¹é ãå æ¤ï¼æ们å次æ¥æ¾ä»£ç 并è¿åæ们å®é æ³è¦çå¼ãå¦åï¼å¦æ没æå®å ¨å¹é ï¼åè¿åï¼N / Aã
è¿ä¼¼å¹é ç
å计ç®æ¶é´ï¼0.594 å®å ¨å¹é çæ¬çå计ç®æ¶é´ï¼0.781
ç¨ä¸ä¸ªå ¬å¼è¿è¡INDEX-MATCHï¼æåºæ°æ®
æ¤çæ¬çå¯é¥å ¬å¼å¯ä»¥ç¨ä¸¤ç§æ¹å¼ç¼åï¼
B3ï¼= INDEXï¼æ°æ®ï¼MATCHï¼$ A3ï¼ä»£ç ï¼1ï¼ï¼B $ 1ï¼
B3ï¼= INDEXï¼æ°æ®ï¼MATCHï¼$ A3ï¼ä»£ç ï¼ï¼B $ 1ï¼
ä¸VLOOKUPä¸æ ·ï¼æ们å¯ä»¥ä¿®æ¹æ¤å ¬å¼ä»¥æä¾å®å ¨å¹é çå 容ï¼
B3ï¼= IFï¼INDEXï¼Codeï¼MATCHï¼$ A3ï¼Codeï¼1ï¼ï¼= $ A3ï¼INDEXï¼Dataï¼MATCHï¼$ A3ï¼Codeï¼1ï¼ï¼B $ 1ï¼ï¼NAï¼ï¼ï¼
è¿ä¼¼å¹é ç
å计ç®æ¶é´ï¼0.453 精确å¹é çæ¬çå计ç®æ¶é´ï¼0.688
ä¸¤ä¸ªå ¬å¼ä¸çINDEX-MATCHï¼å·²æåºçæ°æ®
æåï¼è¯¥è¯éªå¯¹INDEXåMATCH使ç¨åç¬çå ¬å¼ï¼
B3ï¼= INDEXï¼æ°æ®ï¼$ G3ï¼B $ 1ï¼
G3ï¼= MATCHï¼$ A3ï¼ä»£ç ï¼1ï¼
å¨è¿éï¼æ们å¯ä»¥ä¿®æ¹åå æ ¼G3以æä¾å®å ¨å¹é ï¼
G3ï¼= IFï¼INDEXï¼Codeï¼MATCHï¼$ A3ï¼Codeï¼1ï¼ï¼= $ A3ï¼MATCHï¼$ A3ï¼Codeï¼1ï¼ï¼NAï¼ï¼ï¼
æ¢å¥è¯è¯´ï¼å¯¹æåºåçæ°æ®ä½¿ç¨ä¸¤ç§å½¢å¼çINDEX-MATCHæ¹æ³å¯è½æ¯ä½¿ç¨VLOOKUPæä¸ç§å½¢å¼çINDEX-MATCHææ¯è¦å¿«å¾å¤ï¼è¿æ¯æä½³å®è·µã
è¿ä¼¼å¹é ç
å计ç®æ¶é´ï¼0.391 精确å¹é çå计ç®æ¶é´ï¼0.438
äºè§£æ¥æ¾æµè¯ç»æ
è¿æ¯ææ»ç»è¿äºç»æçæ¹æ³ï¼
å¦æè¦ä»è¡¨çä¸è¡è¿åå¤ä¸ªå¼ï¼è¯·å¨ä¸ä¸ªå ¬å¼ä¸ä½¿ç¨MATCHæ¥æ¾è¯¥è¡ï¼ç¶åå¨å ¶ä»å ¬å¼ä¸ä½¿ç¨INDEXè¿åå¼ãè¿å§ç»æ¯æå¿«çæ¹æ³ã
å¦ææ¨çæ°æ®å·²æåºï¼è¯·ä½¿ç¨ä¸é¢è¯´æçæåºæ°æ®çæ¬ãè¿æ ·åå¯ä»¥å°è®¡ç®æ¶é´ç¼©çå 个æ°é级ã
å¦ææ¨éè¦æåºæ°æ®ä¸ç精确å¹é ï¼è¯·ä¸è¦ä¾èµè¿ä¼¼å¹é ãç¸åï¼è¯·å§ç»ä½¿ç¨åéæ¥æ¾æ¹æ³æ¥ç¡®ä¿Excelå®é ä¸å·²ç»æ¾å°äºæ¨çlookup_valueã
å¨æåçæ åµä¸ï¼INDEX-MATCHæ¹æ³çé度ä¸VLOOKUPå·®ä¸å¤ãå¨æ好çæ åµä¸ï¼é度è¦å¿«å¾å¤ã
æå ³VLOOKUPåINDEX-MATCHçæ´å¤ä¿¡æ¯ï¼è¯·åè§ï¼ExcelçVLOOKUPä¸INDEX-MATCHå½æ°ã