{"id":3398,"date":"2026-04-01T13:45:04","date_gmt":"2026-04-01T05:45:04","guid":{"rendered":"https:\/\/smproject.com.tw\/wordpress\/?p=3398"},"modified":"2026-04-05T19:32:17","modified_gmt":"2026-04-05T11:32:17","slug":"excel-%e5%b1%85%e7%84%b6%e9%80%99%e9%ba%bc%e5%a5%bd%e7%94%a8%ef%bc%9f%e6%96%87%e6%9b%b8%e8%99%95%e7%90%86%e5%a5%b3%e5%ad%a9%e5%bf%85%e5%ad%b8%e6%8a%80%e5%b7%a7","status":"publish","type":"post","link":"https:\/\/smproject.com.tw\/wordpress\/2026\/04\/excel-%e5%b1%85%e7%84%b6%e9%80%99%e9%ba%bc%e5%a5%bd%e7%94%a8%ef%bc%9f%e6%96%87%e6%9b%b8%e8%99%95%e7%90%86%e5%a5%b3%e5%ad%a9%e5%bf%85%e5%ad%b8%e6%8a%80%e5%b7%a7\/","title":{"rendered":"Excel \u5c45\u7136\u9019\u9ebc\u597d\u7528\uff1f\u6587\u66f8\u8655\u7406\u5973\u5b69\u5fc5\u5b78\u6280\u5de7"},"content":{"rendered":"
\n
\n

\u65b0\u6642\u4ee3\u5973\u5b69\u5b50\u306e\u96fb\u8166\u5c0f\u5e6b\u624b<\/span><\/p>\n

Excel \u5c45\u7136\u9019\u9ebc\u597d\u7528\uff1f\u6587\u66f8\u8655\u7406\u5973\u5b69\u5fc5\u5b78\u6280\u5de7<\/h1>\n
\n

\u9084\u5728\u624b\u52d5\u8907\u88fd\u8cbc\u4e0a\u55ce\uff1f\u5b78\u6703\u9019\u4e00\u62db\uff0c\u6e96\u6642\u4e0b\u73ed\u4e0d\u662f\u5922\u3002<\/p>\n

\u27a4 \u5225\u518d\u50bb\u50bb\u571f\u6cd5\u7149\u92fc\u4e86\u2014\u2014\u6703 Excel \u7684\u5973\u5b69\uff0c\u904b\u6c23\u90fd\u4e0d\u6703\u592a\u5dee<\/strong>\u3002<\/p>\n<\/div>\n

\u6bcf\u6b21\u770b\u5230\u5bc6\u5bc6\u9ebb\u9ebb\u7684\u6578\u5b57\u5c31\u982d\u75bc\uff1f\u89ba\u5f97 Excel \u53ea\u662f\u62ff\u4f86\u586b\u8868\u683c\u7684\u5de5\u5177\uff1f\u932f\u4e86\u3002\u9019\u500b\u770b\u8d77\u4f86\u7121\u804a\u7684\u8edf\u9ad4\uff0c\u5176\u5be6\u662f\u6bcf\u500b\u6587\u66f8\u5973\u5b69\u6700\u5f37\u7684\u6b66\u5668\u3002<\/p>\n

Excel \u52a0\u901f\u6280\u5de7<\/h2>\n

7 \u500b\u8b93\u4f60\u63d0\u65e9\u4e0b\u73ed\u7684\u6bba\u624b\u7d1a\u6280\u5de7<\/h3>\n
\n
\u6280\u5de7 01<\/div>\n

Ctrl+E \u2014 \u5feb\u901f\u586b\u6eff\uff0c\u79d2\u641e\u5b9a\u91cd\u8907\u8cc7\u6599<\/h4>\n

\u9084\u5728\u4e00\u683c\u4e00\u683c\u8907\u88fd\u8cbc\u4e0a\uff1fCtrl+E<\/span> \u53ea\u8981\u4f60\u5728\u7b2c\u4e00\u683c\u793a\u7bc4\u60f3\u8981\u7684\u6548\u679c\uff0cExcel \u81ea\u52d5\u5e6b\u4f60\u5b8c\u6210\u5269\u4e0b\u7684\u3002\u96fb\u8a71\u683c\u5f0f\u7d71\u4e00\u3001\u65e5\u671f\u6574\u7406\u3001\u6587\u5b57\u622a\u53d6\uff0c\u5168\u90e8\u4e00\u6b21\u641e\u5b9a\u3002<\/p>\n<\/div>\n

\n
\u6280\u5de7 02<\/div>\n

SUM \/ SUMIF \u2014 \u5e33\u76ee\u518d\u4e5f\u4e0d\u7b97\u932f<\/h4>\n

\u8a18\u5e33\u6700\u6015\u7684\u5c31\u662f\u6309\u8a08\u7b97\u6a5f\u6309\u5230\u773c\u82b1\uff1f=SUM()<\/span> \u9078\u53d6\u6578\u5b57\u7bc4\u570d\uff0c\u7e3d\u548c\u4e00\u79d2\u51fa\u4f86\u3002\u642d\u914d\u7be9\u9078\u689d\u4ef6\u7528 =SUMIF()<\/span>\uff0c\u652f\u51fa\u5206\u985e\u7d71\u8a08\u4e5f\u80fd\u81ea\u52d5\u5b8c\u6210\u3002\u8001\u95c6\u554f\u5831\u8868\uff0c3 \u79d2\u5167\u7d66\u7b54\u6848\u3002<\/p>\n<\/div>\n

\n
\u6280\u5de7 03<\/div>\n

\u689d\u4ef6\u5f0f\u683c\u5f0f\u8a2d\u5b9a \u2014 \u8cc7\u6599\u8996\u89ba\u5316\uff0c\u904e\u6642\u81ea\u52d5\u8b8a\u8272<\/h4>\n

\u7d66\u5132\u5b58\u683c\u88dd\u4e0a\u300c\u5927\u8166\u300d\u5427\u3002\u7b26\u5408\u689d\u4ef6\u81ea\u52d5\u8b8a\u8272\uff0c\u6578\u5b57\u4e00\u904e\u6642\u3001\u7de8\u865f\u91cd\u8907\u3001\u5eab\u5b58\u5feb\u6c92\u4e86\u2014\u2014\u984f\u8272\u76f4\u63a5\u544a\u8a34\u4f60\u3002\u4e0d\u518d\u9700\u8981\u773c\u775b\u4e00\u884c\u884c\u76ef\u3002<\/p>\n<\/div>\n

\n
\u6280\u5de7 04<\/div>\n

VLOOKUP \u2014 \u8de8\u8868\u67e5\u8a62\uff0c\u544a\u5225\u4eba\u5de5\u6bd4\u5c0d<\/h4>\n

\u5169\u4efd\u5831\u8868\u8981\u5c0d\u6578\u64da\uff0c\u773c\u90fd\u8981\u5c0d\u82b1\u4e86\uff1f=VLOOKUP()<\/span> \u5728\u5927\u578b\u8cc7\u6599\u8868\u88e1\u79d2\u627e\u76ee\u6a19\u8cc7\u6599\u3002\u5ba2\u6236\u96fb\u8a71\u3001\u7522\u54c1\u50f9\u683c\u3001\u54e1\u5de5\u8cc7\u6599\u2014\u2014\u4e00\u9375\u5e36\u5165\uff0c\u4e0d\u518d\u624bkey\u5230\u5d29\u6f70\u3002<\/p>\n<\/div>\n

\n
\u6280\u5de7 05<\/div>\n

\u6a1e\u7d10\u5206\u6790\u8868 \u2014 \u4e00\u9375\u5b8c\u6210\u6578\u5c0f\u6642\u7d71\u8a08<\/h4>\n

\u9084\u5728\u571f\u6cd5\u7149\u92fc\u6162\u6162\u6578\uff1f\u6a1e\u7d10\u5206\u6790\u8868\u662f Excel \u6700\u5f37\u5927\u7684\u5de5\u5177\u3002\u9078\u53d6\u8cc7\u6599\u3001\u63d2\u5165\u6a1e\u7d10\u5206\u6790\u8868\uff0c\u5206\u985e\u5f59\u7e3d\u3001\u4ea4\u53c9\u5206\u6790\uff0c\u5168\u90e8\u4e00\u9375\u5b8c\u6210\u3002\u904e\u53bb\u8981\u5e7e\u5c0f\u6642\u7684\u5de5\u4f5c\uff0c\u73fe\u5728\u4e09\u5206\u9418\u3002<\/p>\n<\/div>\n

\n
\u6280\u5de7 06<\/div>\n

Ctrl+D \/ Ctrl+R \u2014 \u6b04\u5217\u5feb\u901f\u586b\u6eff<\/h4>\n

\u540c\u4e00\u6b04\u8981\u586b\u540c\u6a23\u5167\u5bb9\u6216\u516c\u5f0f\uff1fCtrl+D<\/span> \u5411\u4e0b\u586b\uff0cCtrl+R<\/span> \u5411\u53f3\u586b\u3002\u9078\u53d6\u7bc4\u570d\u518d\u6309\u4e0b\uff0c\u8f15\u8f15\u9b06\u9b06\u641e\u5b9a\uff0c\u4e0d\u9700\u8981\u8907\u88fd\u8cbc\u4e0a\u4f86\u56de\u6298\u9a30\u3002<\/p>\n<\/div>\n

\n
\u6280\u5de7 07<\/div>\n

\u7be9\u9078 + \u6392\u5e8f \u2014 \u77ac\u9593\u627e\u5230\u76ee\u6a19\u8cc7\u6599<\/h4>\n

\u8cc7\u6599\u5e7e\u767e\u7b46\u600e\u9ebc\u7ffb\uff1f\u9ede\u9078\u7be9\u9078\u529f\u80fd\uff0c\u652f\u63f4\u4f9d\u984f\u8272\u3001\u5167\u5bb9\u3001\u65e5\u671f\u7b49\u591a\u7a2e\u689d\u4ef6\u904e\u6ffe\u3002\u60f3\u627e\u7279\u5b9a\u5ba2\u6236\u3001\u67d0\u6708\u8a02\u55ae\uff0c\u5168\u90e8\u6307\u6a19\u9ede\u4e00\u9ede\uff0c\u4e0d\u7528\u6efe\u8f2a\u6efe\u5230\u5929\u8352\u5730\u8001\u3002<\/p>\n<\/div>\n

\u5be6\u7528\u5834\u666f<\/h2>\n

\u8a18\u5e33\u6536\u652f \u2192 SUMIF + \u5713\u9905\u5716<\/h3>\n

\u5834\u666f\uff1a\u4f60\u662f\u9910\u5ef3\u5e97\u54e1\uff0c\u6bcf\u6708\u8981\u7d71\u8a08\u5404\u985e\u5225\u652f\u51fa\u3002\u8001\u95c6\u8981\u6c42\u9031\u4e94\u4e0b\u5348\u56de\u5831\u4e0a\u9031\u9910\u98f2\u3001\u4ea4\u901a\u3001\u98f2\u6599\u5404\u82b1\u4e86\u591a\u5c11\u3002<\/p>\n

\u4f60\u7684 Excel \u9577\u9019\u6a23\uff1a<\/p>\n

\u65e5\u671f       \u5206\u985e     \u91d1\u984d\n01\/01     \u5348\u9910     150\n01\/02     \u4ea4\u901a     80\n01\/03     \u5348\u9910     200\n01\/03     \u98f2\u6599     60\n01\/04     \u5348\u9910     180\n01\/04     \u4ea4\u901a     45\n...\uff08\u4e00\u500b\u6708\u4e0b\u4f86\u5927\u7d0460\u7b46\uff09<\/pre>\n
Step 1\uff1a<\/strong>\u5728\u6700\u4e0b\u9762\u65b0\u589e\u4e00\u5217\u300c\u7e3d\u8a08\u300d\uff0c\u5728\u91d1\u984d\u6b04\u53f3\u908a\u9078\u4e00\u500b\u7a7a\u683c\uff0c\u8f38\u5165\uff1a<\/p>\n
=SUMIF(B:B,\"\u5348\u9910\",C:C)<\/pre>\n
\u9019\u884c\u7684\u610f\u601d\u662f\uff1a\u300c\u5728 B \u6b04\u627e\u6240\u6709\u5beb\u300e\u5348\u9910\u300f\u7684\u5217\uff0c\u7136\u5f8c\u628a\u5c0d\u61c9\u7684 C \u6b04\u6578\u5b57\u52a0\u8d77\u4f86\u300d<\/p>\n
Step 2\uff1a<\/strong>\u628a\u300c\u5348\u9910\u300d\u6539\u6210\u300c\u4ea4\u901a\u300d\uff0c\u8907\u88fd\u516c\u5f0f\u5f80\u4e0b\u8cbc\uff1a<\/p>\n
=SUMIF(B:B,\"\u4ea4\u901a\",C:C)\n=SUMIF(B:B,\"\u98f2\u6599\",C:C)<\/pre>\n
\u4e0d\u5230\u4e00\u5206\u9418\uff0c\u4e09\u500b\u5206\u985e\u7684\u7e3d\u91d1\u984d\u5168\u90e8\u51fa\u4f86\u3002<\/p>\n
Step 3\uff1a<\/strong>\u9078\u53d6\u5206\u985e\u548c\u7e3d\u8a08\u5169\u6b04\uff0c\u300c\u63d2\u5165\u300d\u2192\u300c\u5713\u9905\u5716\u300d\uff0c\u7acb\u523b\u6709\u4e00\u5f35\u6f02\u4eae\u7684\u5716\u3002\u8001\u95c6\u770b\u5230\u53ea\u6703\u554f\uff1a\u300c\u59b3\u600e\u9ebc\u505a\u9019\u9ebc\u5feb\uff1f\u300d<\/p>\n
\n
\ud83d\udca1 \u5c0f\u6487\u6b65\uff1a\u5982\u679c\u4f60\u7684\u5206\u985e\u540d\u7a31\u5beb\u6cd5\u4e0d\u4e00\u81f4\uff08\u5982\u300c\u5348\u9910 \u300d\u591a\u4e86\u7a7a\u683c\uff09\uff0cSUMIF \u6703\u6293\u4e0d\u5230\u3002\u5148\u7528 Ctrl+H \u53d6\u4ee3\u529f\u80fd\uff0c\u628a\u591a\u9918\u7a7a\u683c\u522a\u6389\u518d\u7d71\u8a08\u3002<\/p>\n<\/div>\n
\u5ba2\u6236\u8cc7\u6599 \u2192 VLOOKUP \u5be6\u6230<\/h3>\n
\u5834\u666f\uff1a\u696d\u52d9\u90e8\u9580\u7d66\u4f60\u5169\u5f35\u8868\u2014\u2014A \u8868\u662f\u9019\u500b\u6708\u7684\u8a02\u55ae\uff08\u6709\u5ba2\u6236\u7de8\u865f\u3001\u7522\u54c1\u3001\u6578\u91cf\uff09\uff0cB \u8868\u662f\u5ba2\u6236\u4e3b\u6a94\uff08\u6709\u7de8\u865f\u3001\u96fb\u8a71\u3001\u5730\u5740\uff09\u3002\u4f60\u8981\u5728 A \u8868\u88e1\u88dc\u4e0a\u96fb\u8a71\u548c\u5730\u5740\u3002<\/p>\n
A\u8868\uff08\u8a02\u55ae\uff09\u9577\u9019\u6a23\uff1a<\/p>\n
\u8a02\u55ae\u7de8\u865f   \u5ba2\u6236\u7de8\u865f   \u7522\u54c1     \u6578\u91cf\nA001       C101       \u9375\u76e4     2\nA002       C105       \u6ed1\u9f20     5\nA003       C102       \u87a2\u5e55     1<\/pre>\n
B\u8868\uff08\u5ba2\u6236\u4e3b\u6a94\uff09\u9577\u9019\u6a23\uff1a<\/p>\n
\u5ba2\u6236\u7de8\u865f   \u96fb\u8a71         \u5730\u5740\nC101       0912-345678  \u53f0\u5317\u5e02\nC102       0922-111222  \u65b0\u5317\u5e02\nC105       0933-222333  \u53f0\u4e2d\u5e02<\/pre>\n
Step 1\uff1a<\/strong>\u5728 A \u8868 D \u6b04\u65b0\u589e\u300c\u96fb\u8a71\u300d\u6b04\u3002\u5728 D2\uff08\u7b2c\u4e00\u7b46\u8cc7\u6599\u7684\u96fb\u8a71\u6b04\uff09\u8f38\u5165\uff1a<\/p>\n
=VLOOKUP(B2, C\u8868!A:C, 2, FALSE)<\/pre>\n
\u8aaa\u660e\uff1aB2 \u662f\u8981\u6bd4\u5c0d\u7684\u5ba2\u6236\u7de8\u78bc\u3001C\u8868!A:C \u662f\u8981\u53bb\u54ea\u500b\u7bc4\u570d\u627e\u30012 \u662f\u627e\u5230\u5f8c\u53d6\u7b2c2\u6b04\uff08\u5373\u96fb\u8a71\uff09\u3001FALSE \u662f\u8981\u5b8c\u5168\u543b\u5408\u3002<\/p>\n
Step 2\uff1a<\/strong>\u5728\u540c\u4e00\u6b04\u8f38\u5165\u4f4d\u5740\u6b04\uff1a<\/p>\n
=VLOOKUP(B2, C\u8868!A:C, 3, FALSE)<\/pre>\n
\u53ea\u5dee\u5728\u7b2c\u4e09\u500b\u53c3\u6578\u8b8a\u6210 3\uff08\u7b2c3\u6b04\uff1d\u4f4d\u5740\uff09\u3002<\/p>\n
Step 3\uff1a<\/strong>\u9078\u53d6 D2 \u548c E2\uff0c\u6e38\u6a19\u79fb\u5230\u53f3\u4e0b\u89d2\uff0c\u51fa\u73fe\u9ed1\u8272\u5341\u5b57\u6642\u96d9\u64ca\u2014\u2014\u5176\u9918\u8cc7\u6599\u5168\u90e8\u81ea\u52d5\u586b\u6eff\u3002<\/p>\n
60 \u7b46\u8cc7\u6599\u4e0d\u5230 30 \u79d2\u5168\u90e8\u5e36\u5b8c\uff0c\u4e0d\u7528\u4e00\u500b\u4e00\u500b\u8907\u88fd\u8cbc\u4e0a\u3002<\/p>\n
\u884c\u7a0b\u7ba1\u7406 \u2192 \u689d\u4ef6\u5f0f\u683c\u5f0f<\/h3>\n
\u5834\u666f\uff1a\u4f60\u662f\u52a9\u7406\uff0c\u624b\u4e0a\u6709\u5168\u516c\u53f8\u7684\u884c\u7a0b\u8868\uff0c\u5305\u542b\u696d\u52d9\u62dc\u8a2a\u3001\u6703\u8b70\u3001\u8acb\u6b3e\u622a\u6b62\u65e5\u3002\u5e38\u5e38\u6709\u4eba\u5fd8\u8a18\u622a\u6b62\u65e5\u671f\uff0c\u5c0e\u81f4\u6b3e\u9805\u5ef6\u9072\u3002<\/p>\n
Step 1\uff1a<\/strong>\u9078\u53d6\u65e5\u671f\u6b04\uff08\u5047\u8a2d\u662f C \u6b04\uff09\u3002<\/p>\n
Step 2\uff1a<\/strong>\u9ede\u9078\u300c\u5e38\u7528\u300d\u2192\u300c\u689d\u4ef6\u5f0f\u683c\u5f0f\u300d\u2192\u300c\u65b0\u589e\u898f\u5247\u300d\u3002<\/p>\n
Step 3\uff1a<\/strong>\u9078\u64c7\u300c\u4f7f\u7528\u516c\u5f0f\u4f86\u6c7a\u5b9a\u8981\u683c\u5f0f\u5316\u54ea\u4e9b\u5132\u5b58\u683c\u300d\uff0c\u8f38\u5165\uff1a<\/p>\n
=C2<TODAY()<\/pre>\n
Step 4\uff1a<\/strong>\u9ede\u300c\u683c\u5f0f\u300d\uff0c\u8a2d\u5b9a\u300c\u586b\u6eff\u300d\u2192\u300c\u7d05\u8272\u300d\u3002<\/p>\n
Step 5\uff1a<\/strong>\u518d\u65b0\u589e\u4e00\u689d\u898f\u5247\uff1a<\/p>\n
=AND(C2>=TODAY(), C2<=TODAY()+3)<\/pre>\n
\u683c\u5f0f\u8a2d\u70ba\u300c\u586b\u6eff\u300d\u2192\u300c\u9ec3\u8272\u300d\u2014\u2014\u9019\u500b\u6708\u8981\u5230\u7684\u884c\u7a0b\u3002<\/p>\n
\u7d50\u679c\uff1a\u904e\u671f\u7684\u5168\u90e8\u8b8a\u7d05\u8272\uff0c\u4e09\u5929\u5167\u7684\u8b8a\u9ec3\u8272\uff0c\u4e00\u773c\u5c31\u80fd\u5340\u5206\u8f15\u91cd\u7de9\u6025\u3002<\/p>\n
\u5831\u8868\u5f59\u6574 \u2192 \u6a1e\u7d10\u5206\u6790\u8868<\/h3>\n
\u5834\u666f\uff1a\u8001\u95c6\u7d66\u4f60\u4e00\u4efd\u5b63\u5ea6\u8a02\u55ae\u8cc7\u6599\uff0c\u5171 500 \u7b46\uff0c\u8981\u4f60\u5206\u6790\u6bcf\u500b\u696d\u52d9\u54e1\u6bcf\u500b\u6708\u7684\u92b7\u552e\u984d\u3002<\/p>\n
\u8cc7\u6599\u9577\u9019\u6a23\uff08\u7bc0\u9304\uff09\uff1a<\/p>\n
\u65e5\u671f       \u696d\u52d9\u54e1    \u7522\u54c1     \u91d1\u984d\n2026\/01\/05 \u5c0f\u7f8e     \u9375\u76e4     1200\n2026\/01\/07 \u5927\u83ef     \u6ed1\u9f20     600\n2026\/01\/10 \u5c0f\u7f8e     \u87a2\u5e55     3500\n2026\/02\/03 \u5927\u83ef     \u9375\u76e4     980\n...\uff08\u5171500\u7b46\uff09<\/pre>\n
Step 1\uff1a<\/strong>\u9ede\u9078\u8cc7\u6599\u7bc4\u570d\u5167\u4efb\u610f\u4e00\u683c\uff08Excel \u6703\u81ea\u52d5\u5224\u65b7\u6574\u500b\u8cc7\u6599\u7bc4\u570d\uff09\u3002<\/p>\n
Step 2\uff1a<\/strong>\u9ede\u300c\u63d2\u5165\u300d\u2192\u300c\u6a1e\u7d10\u5206\u6790\u8868\u300d\u2192\u300c\u6a1e\u7d10\u5206\u6790\u8868\u300d\u3002\u78ba\u8a8d\u7bc4\u570d\u7121\u8aa4\u5f8c\uff0c\u6309\u300c\u78ba\u5b9a\u300d\u3002<\/p>\n
Step 3\uff1a<\/strong>\u53f3\u5074\u51fa\u73fe\u300c\u6a1e\u7d10\u5206\u6790\u6b04\u4f4d\u300d\uff0c\u62d6\u66f3\uff1a<\/p>\n