有一套总量552,有选择和判断题。怎么用Excel建立题库,并能实现随机抽取110道题。 如何用Excel和word制作随机题库并制作试卷谢谢
\u672c\u4eba\u60f3\u5229\u7528excel\u505a\u4e00\u4e2a\u81ea\u52a8\u51fa\u9898\u7cfb\u7edf\uff0c\u7b2c\u4e00\u4e2a\u5de5\u4f5c\u7c3f\u662f\u968f\u673a\u751f\u6210\u7684\u8bd5\u5377\uff0c\u540e\u9762\u7684\u51e0\u4e2a\u5de5\u4f5c\u7c3f\u662f\u9898\u5e93\uff0c\u8bf7\u95ee\u5982...Excel+Word \u8f7b\u677e\u81ea\u5236\u968f\u673a\u51fa\u5377\u7cfb\u7edf\u3000\u3000\u5148\u628a\u6536\u96c6\u5230\u7684\u5927\u91cf\u8003\u9898\u96c6\u4e2d\u4fdd\u5b58\u5230\u9898\u5e93\u4e2d\uff0c\u518d\u968f\u673a\u4ece\u4e2d\u62bd\u53d6\u6307\u5b9a\u6570\u91cf\u7684\u9898\u76ee\u5236\u4f5c\u8003\u5377\uff0c\u8fd9\u79cd\u968f\u673a\u62bd\u9898\u51fa\u5377\u7684\u65b9\u5f0f\u76f8\u4fe1\u5927\u591a\u6559\u5e08\u670b\u53cb\u90fd\u77e5\u9053\u5427\u3002\u4e0d\u8fc7\u4f60\u6709\u6ca1\u6709\u60f3\u8fc7\u53ef\u4ee5\u81ea\u5df1\u8bbe\u8ba1\u4e00\u4e2a\u968f\u673a\u51fa\u5377\u7cfb\u7edf\u5462\uff1f\u8fd9\u5176\u5b9e\u5f88\u5bb9\u6613\u5b9e\u73b0\uff0c\u6211\u4eec\u53ea\u8981\u7528Excel2010\u51fd\u6570\u914d\u5408Word2010\u7684\u90ae\u4ef6\u5408\u5e76\u5c31\u53ef\u4ee5\u8f7b\u8f7b\u677e\u677e\u5730\u6309\u81ea\u5df1\u7684\u8981\u6c42\u5b9a\u5236\u51fa\u5377\u7cfb\u7edf\u3002\u8bbe\u8ba1\u597d\u540e\u4f60\u53ea\u8981\u8f93\u5165\u8db3\u591f\u7684\u9898\u76ee\uff0c\u6309\u4e24\u4e0b\u9f20\u6807\u5c31\u53ef\u4ee5\u968f\u673a\u81ea\u52a8\u751f\u6210\u8bd5\u5377\u4e86\uff0c\u662f\u53ef\u4ee5\u76f4\u63a5\u6253\u5370\u51fa\u6765\u4f7f\u7528\u7684\u8bd5\u5377\u54e6\u3002
\u3000\u3000
\u3000\u3000\u4e00\u3001\u7528Excel2010\u5b9e\u73b0\u968f\u673a\u62bd\u9898
\u3000\u3000\u5728Excel\u4e2d\u5b9e\u73b0\u968f\u673a\u62bd\u9898\u7684\u539f\u7406\u5176\u5b9e\u5f88\u7b80\u5355\u3002\u6211\u4eec\u5148\u5bf9\u6240\u5c5e\u5355\u5143\u5728\u8003\u8bd5\u8303\u56f4\u5185\u7684\u9898\u76ee\u90fd\u7528RAND\u4ea7\u751f\u4e00\u4e2a\u968f\u673a\u6570\uff0c\u518d\u6309\u968f\u673a\u6570\u5bf9\u9898\u76ee\u6392\u540d\u6b21\u3002\u8fd9\u6837\u524dN\u540d\u7684\u9898\u76ee\u81ea\u7136\u662f\u968f\u673a\u7684\u3002\u7136\u540e\u518d\u7528VLOOKUP\u6309\u540d\u6b21\u503c\u628a\u524d1-N\u540d\u7684\u9898\u76ee\u987a\u6b21\u63d0\u53d6\u51fa\u6765\uff0c\u4e0d\u5c31\u7b49\u4e8e\u662f\u968f\u673a\u62bd\u53d6N\u9898\u4e86\u5417\uff1f\u800c\u6211\u4eec\u6bcf\u5237\u65b0\u4e00\u6b21\u968f\u673a\u6570\u90fd\u4f1a\u968f\u673a\u53d8\u5316\uff0c\u4e5f\u5c31\u53ef\u4ee5\u5f97\u5230\u4e0d\u540c\u7684\u968f\u673a\u8bd5\u5377\u9898\u76ee\u3002
\u3000\u3000
\u3000\u30001\u5efa\u7acb\u9898\u5e93
\u3000\u3000\u6253\u5f00Excel2010,\u65b0\u5efa\u201c\u51fa\u5377\u201d\u3001\u201c\u586b\u7a7a\u9898\u201d\u4e24\u4e2a\u5de5\u4f5c\u8868\u3002\u5728\u201c\u586b\u7a7a\u9898\u201d\u5de5\u4f5c\u8868\u7684A1\u5355\u5143\u683c\u8f93\u51650\uff0c\u5728B1:H1\u8f93\u5165\u968f\u673a\u6570\u3001\u201c\u4e00\u3001\u586b\u7a7a\u9898\u201d\u3001\u201c\u4e00\u3001\u586b\u7a7a\u9898\uff08\u7b54\u6848\uff09\u201d\u3001\u6240\u5c5e\u5355\u5143\u7b49\u6807\u9898\uff08\u56fe1\uff09\u3002\u5728B2\u5355\u5143\u683c\u8f93\u5165\u516c\u5f0f=IF(AND(E2>=\u51fa\u5377!B$7,E2<=\u51fa\u5377!D$7),RAND(),"")\uff0c\u516c\u5f0f\u4e2d\u7684B$7\u3001D$7\u662f\u201c\u51fa\u5377\u201d\u5de5\u4f5c\u8868\u4e2d\u663e\u793a\u51fa\u9898\u8303\u56f4\u7684\u5355\u5143\u683c\u3002\u5728A2=RANK(B2,B:B)+COUNTIF(B$2:B2,B2)-1\uff0cRANK\u7528\u4e8e\u6839\u636e\u968f\u673a\u6570\u8fd4\u56de\u8be5\u9898\u7684\u540d\u6b21\uff0c\u540e\u9762\u52a0\u4e0aCOUNTIF(B$2:B2,B2)\u5219\u662f\u786e\u4fdd\u4e07\u4e00\u968f\u673a\u6570\u76f8\u540c\u65f6\u4e5f\u4f1a\u9012\u589e\u6392\u540d\uff0c\u4ece\u800c\u4f7f\u540d\u6b21\u6570\u503c\u8fde\u7eed\u4e0d\u4e2d\u65ad\u3002\u9009\u4e2dA2:B2\u5355\u5143\u683c\uff0c\u62d6\u52a8\u5176\u586b\u5145\u67c4\u5411\u4e0b\u590d\u5236\u586b\u5145\u52305000\u884c\uff0c\u5177\u4f53\u884c\u6570\u8bf7\u6309\u5b9e\u9645\u4f1a\u4fdd\u5b58\u7684\u6700\u5927\u9898\u6570\u8003\u8651\u3002\u6700\u540e\u5728C:E\u5217\u9010\u4e00\u8f93\u5165\u9898\u76ee\u3001\u7b54\u6848\u3001\u6240\u5728\u5355\u5143\u5373\u53ef\uff0c\u5728\u6b64\u6211\u4eec\u5c31\u5148\u968f\u4fbf\u8f93\u5165\u51e0\u9053\u9898\u7528\u4f5c\u5b9e\u9a8c\u5427\u3002
\u3000\u3000\u53f3\u51fb\u201c\u586b\u7a7a\u9898\u201d\u5de5\u4f5c\u8868\u7684\u6807\u7b7e\u9009\u62e9\u201c\u79fb\u52a8\u6216\u590d\u5236\u201d\uff0c\u9009\u4e2d\u201c\u5efa\u7acb\u526f\u672c\u201d\u786e\u5b9a\u8fdb\u884c\u590d\u5236\u3002\u53f3\u51fb\u590d\u5236\u51fa\u7684\u201c\u586b\u7a7a\u9898(2)\u201d\u5de5\u4f5c\u8868\u6807\u7b7e\u9009\u62e9\u201c\u91cd\u547d\u540d\u201d\u628a\u5b83\u6539\u6210\u201c\u9009\u62e9\u9898\u201d\uff0c\u5e76\u628a\u201c\u9009\u62e9\u9898\u201d\u5de5\u4f5c\u8868\u4e2dC1\u7684\u9898\u76ee\u6539\u6210\u201c\u4e8c\u3001\u9009\u62e9\u9898\u201d\u3001D1\u6539\u6210\u201c\u4e8c\u3001\u9009\u62e9\u9898\uff08\u7b54\u6848\uff09\u201d\uff0c\u8fd9\u6837\u9009\u62e9\u9898\u5e93\u5c31\u505a\u597d\u4e86\u3002\u540c\u6837\u5efa\u7acb\u51fa\u95ee\u7b54\u9898\u9898\u5e93\u3002
\u3000\u3000
\u3000\u30002\u4e3b\u754c\u9762\u8bbe\u7f6e
\u3000\u3000\u5207\u6362\u5230\u201c\u51fa\u5377\u201d\u5de5\u4f5c\u8868\uff0c\u6309\u9700\u8981\u8bbe\u8ba1\u597d\u51fa\u5377\u754c\u9762\u3002\u5728E2\u5355\u5143\u683c\u8f93\u5165\u516c\u5f0f=C2*D2\uff0c\u5e76\u628a\u516c\u5f0f\u590d\u5236\u5230E3\u3001E4\uff0c\u5728C5\u8f93\u5165\u516c\u5f0f=SUM(C2:C4)\u5e76\u5411\u590d\u5236\u5230E5\u4ee5\u8ba1\u7b97\u51fa\u9898\u6570\u3001\u603b\u5206\u3002\u5728A2\u8f93\u51652\uff0c\u5728A3\u8f93\u5165\u516c\u5f0f=A2+C2+1\u5e76\u5411\u4e0b\u590d\u5236\u586b\u5145\u5230A4:A5\u5355\u5143\u683c\uff0c\u4ee5\u8ba1\u7b97\u51fa\u5404\u79cd\u9898\u578b\u5927\u6807\u9898\u6240\u5728\u7684\u884c\u6570\u3002
\u3000\u3000\u5728F2\u5355\u5143\u683c\u8f93\u5165\u516c\u5f0f=IF(ROW()>A$5,0,COUNTIF(G$1:G2,G2)-1)\u4ee5\u81ea\u52a8\u663e\u793a\u9898\u53f7\u3002G2\u8f93\u5165\u516c\u5f0f=VLOOKUP(ROW(),A:B,2,TRUE)\u4ee5\u663e\u793a\u5bf9\u5e94\u7684\u9898\u578b\u3002H2\u8f93\u5165\u516c\u5f0f=IF(F2=0,"",F2&".")&IFERROR(VLOOKUP(F2,INDIRECT(G2&"!A:D"),3,FALSE),"")\u6309\u9898\u53f7\u4ece\u9898\u5e93\u7684A\u5217\u4e2d\u627e\u5230\u7b26\u5408\u7684\u968f\u673a\u540d\u6b21\u5e76\u8fd4\u56de\u5728\u7b2c3\u5217\u7684\u9898\u76ee\u3002I2\u4e5f\u8f93\u5165\u4e0eH2\u76f8\u540c\u7684\u516c\u5f0f\u53ea\u662f\u8981\u628a\u516c\u5f0f\u4e2d\u76843\u6539\u62104\u4ee5\u8fd4\u56de\u9898\u5e93\u7b2c4\u5217\u7684\u7b54\u6848\u3002\u6700\u540e\u9009\u4e2dF2\uff1aI2\u5355\u5143\u683c\u62d6\u52a8\u5176\u586b\u5145\u67c4\u628a\u516c\u5f0f\u5411\u4e0b\u590d\u5236\u5230150\u884c\u5373\u53ef\uff08\u56fe2\uff09\u3002\u590d\u5236\u884c\u6570\u8bf7\u6309\u8bd5\u5377\u6700\u5927\u9898\u6570\u8003\u8651\uff0c\u4e00\u822c\u5e94\u8be5\u4e0d\u9700\u8981\u8d85\u8fc7150\u9898\u5566\u3002\u6700\u540e\u628a\u5de5\u4f5c\u8584\u547d\u540d\u4fdd\u5b58\u4e3a\u201c\u51fa\u5377\u7cfb\u7edf.xlsx\u201d\u3002
\u3000\u3000\u6ce8\uff1a\u8bd5\u5377\u5404\u5927\u6807\u9898\u4e2d\u5f80\u5f80\u8fd8\u9700\u8981\u6ce8\u660e\u9898\u6570\u3001\u5206\u503c\u3001\u603b\u8ba1\u5206\u7b49\uff0c\u8fd9\u53ef\u7528\u516c\u5f0f\u4ece\u51fa\u5377\u5de5\u4f5c\u8868\u4e2d\u5f15\u7528\u3002\u6bd4\u5982\uff1a\u5728\u586b\u7a7a\u9898\u5de5\u4f5c\u8868\u7684C1\u5355\u5143\u683c\u8f93\u5165\u516c\u5f0f="\u4e00\u3001\u586b\u7a7a\u9898\uff08\u5171"&\u51fa\u5377!C2&"\u9898\u6bcf\u9898"&\u51fa\u5377!D2&"\u5206\uff0c\u603b\u8ba1"&\u51fa\u5377!E2&"\u5206\uff09"\uff0c\u5373\u53ef\u5728C1\u81ea\u52a8\u663e\u793a\u6210\u201c\u4e00\u3001\u586b\u7a7a\u9898\uff08\u51714\u9898\u6bcf\u98985\u5206\uff0c\u603b\u8ba120\u5206\uff09\u201d\u8fd9\u6837\u7684\u9898\u76ee\u4e86\u3002
\u3000\u3000
\u3000\u3000
\u3000\u3000\u4e8c\u3001\u7528Word\u8bbe\u8ba1\u8bd5\u5377
\u3000\u3000\u63a5\u4e0b\u6765\u8981\u505a\u7684\u5c31\u662f\u628a\u63d0\u53d6\u51fa\u6765\u7684\u9898\u76ee\u6392\u7248\u751f\u6210\u4e00\u5f20\u53ef\u4ee5\u76f4\u63a5\u6253\u5370\u51fa\u6765\u4f7f\u7528\u7684\u8bd5\u5377\u3002\u8fd9\u4e2a\u53ef\u4ee5\u901a\u8fc7Word\u7684\u90ae\u4ef6\u5408\u5e76\u529f\u80fd\u81ea\u52a8\u5b8c\u6210\u3002
\u3000\u3000\u6253\u5f00Word2010\uff0c\u5207\u6362\u5230\u201c\u90ae\u4ef6\u201d\u9009\u9879\u5361\uff0c\u5355\u51fb\u201c\u9009\u62e9\u6536\u4ef6\u4eba\u201d\u9009\u62e9\u201c\u4f7f\u7528\u73b0\u6709\u5217\u8868\u201d\uff0c\u9009\u62e9\u6253\u5f00\u524d\u9762\u4fdd\u5b58\u7684\u201c\u51fa\u5377\u7cfb\u7edf.xlsx\u201d\u3002\u5728\u5f39\u51fa\u7684\u201c\u9009\u62e9\u8868\u683c\u201d\u7a97\u53e3\u4e2d\u221a\u9009\u201c\u6570\u636e\u9996\u884c\u5305\u542b\u5217\u6807\u9898\u201d\u9009\u9879\uff0c\u9009\u62e9\u201c\u51fa\u5377$\u201d\uff0c\u786e\u5b9a\u5b8c\u6210\u8bbe\u7f6e\u3002\u63a5\u7740\u5355\u51fb\u201c\u63d2\u5165\u5408\u5e76\u57df\u201d\u5728\u5f39\u51fa\u7a97\u53e3\u4e2d\u53cc\u51fb\u9009\u62e9\u63d2\u5165\u201c\u9898\u76ee\u201d\uff0c\u518d\u5355\u51fb\u201c\u89c4\u5219\u201d\u9009\u62e9\u201c\u4e0b\u4e00\u8bb0\u5f55\u201d\u3002\u7136\u540e\u9009\u4e2d\u63d2\u5165\u7684\u201c\u300a\u9898\u76ee\u300b\u300a\u4e0b\u4e00\u8bb0\u5f55\u300b\u201d\u8fdb\u884c\u590d\u5236\u3002\u518d\u8fde\u7eed\u6309Ctrl+V\u952e\u7c98\u8d34\u51fa150\u884c\u201c\u300a\u9898\u76ee\u300b\u300a\u4e0b\u4e00\u8bb0\u5f55\u300b\u201d\uff08\u56fe3\uff09\u3002\u73b0\u5728\u5355\u51fb\u201c\u67e5\u770b\u5408\u5e76\u6570\u636e\u201d\u5c31\u53ef\u4ee5\u770b\u5230\u6240\u6709\u8bd5\u9898\u4e86\u3002\u5f53\u7136\u5b9e\u9645\u4e0a\u51fa\u5377\u5de5\u4f5c\u8868\u4e2d\u7684\u9898\u76ee\u80af\u5b9a\u6ca1\u6709150\u9898\uff0c\u540e\u9762\u6ca1\u9898\u76ee\u7684\u8bb0\u5f55\uff0c\u5176\u5408\u5e76\u57df\u4f1a\u81ea\u52a8\u6d88\u5931\uff0c\u5305\u62ec\u57df\u540e\u9762\u7684\u56de\u8f66\u4e5f\u4f1a\u4e00\u8d77\u6d88\u5931\u54e6\u3002
\u3000\u3000\u8bd5\u5377\u9898\u76ee\u51c6\u5907\u597d\u4e86\uff0c\u518d\u6765\u8bbe\u7f6e\u4e00\u4e0b\u9996\u884c\u7f29\u8fdb\u3001\u6dfb\u52a0\u5bc6\u5c01\u7ebf\u5185\u5bb9\u3001\u8bbe\u7f6e\u7eb8\u5f20\u5927\u5c0f\u65b9\u5411\u548c\u5206\u680f\u7b49\u683c\u5f0f\u3002\u8fd9\u4e9b\u8bd5\u5377\u7684\u8bbe\u7f6e\u5df2\u6709\u4e0d\u5c11\u76f8\u5173\u4ecb\u7ecd\uff0c\u76f8\u4fe1\u5927\u5bb6\u90fd\u6bd4\u8f83\u6e05\u695a\uff0c\u9650\u4e8e\u7bc7\u5e45\u5c31\u4e0d\u7ec6\u8bf4\u4e86\uff0c\u6700\u540e\u6548\u679c\u5982\u56fe\uff08\u56fe4\uff09\u3002\u8bbe\u7f6e\u597d\u540e\u628a\u6587\u4ef6\u4fdd\u5b58\u4e3a\u201c\u8bd5\u5377.docx\u201d
\u3000\u3000\u6211\u4eec\u8fd8\u8981\u518d\u53e6\u5efa\u4e00\u4e2aWord\u6587\u6863\u7528\u6765\u663e\u793a\u6240\u6709\u8bd5\u9898\u7684\u6807\u51c6\u7b54\u6848\uff0c\u64cd\u4f5c\u548c\u201c\u8bd5\u5377.docx\u201d\u5dee\u4e0d\u591a\u3002\u5355\u51fb\u201c\u90ae\u4ef6\u201d\u9009\u9879\u5361\u7684\u201c\u9009\u62e9\u6536\u4ef6\u4eba\u201d\u9009\u62e9\u201c\u4f7f\u7528\u73b0\u6709\u5217\u8868\u201d\uff0c\u6253\u5f00\u201c\u51fa\u5377\u7cfb\u7edf.xlsx\u201d\u7684\u201c\u51fa\u5377$\u201d\u5de5\u4f5c\u8868\uff1b\u5355\u51fb\u201c\u63d2\u5165\u5408\u5e76\u57df\u201d\u9009\u62e9\u63d2\u5165\u300a\u7b54\u6848\u300b\uff0c\u5e76\u63d2\u5165\u201c\u6761\u4ef6\u201d\u7684\u300a\u4e0b\u4e00\u8bb0\u5f55\u300b\uff1b\u590d\u5236\u7c98\u8d34\u51fa150\u884c\uff1b\u5355\u51fb\u201c\u67e5\u770b\u5408\u5e76\u6570\u636e\u201d\u4ee5\u663e\u793a\u6240\u6709\u7b54\u6848\uff0c\u7136\u540e\u4fdd\u5b58\u4e3a\u201c\u7b54\u6848.docx\u201d\u3002
\u3000\u3000
\u3000\u3000\u4e09\u3001\u968f\u673a\u51fa\u5377\u7cfb\u7edf\u7684\u7ef4\u62a4\u4e0e\u4f7f\u7528
\u3000\u3000OK\uff0c\u5230\u6b64\u6211\u4eec\u7684\u968f\u673a\u51fa\u9898\u7cfb\u7edf\u8bbe\u8ba1\u5df2\u7ecf\u5927\u529f\u544a\u6210\u4e86\uff0c\u8fd9\u8bbe\u7f6e\u8fd8\u662f\u86ee\u7b80\u5355\u7684\u5427\u3002\u63a5\u4e0b\u6765\uff0c\u4f60\u53ea\u8981\u628a\u9898\u76ee\u9010\u4e00\u5f55\u5165\u9898\u5e93\u4e2d\u5c31\u53ef\u4ee5\u968f\u673a\u62bd\u9898\u51fa\u5377\u4e86\u3002
\u3000\u3000
\u3000\u30001\u9898\u76ee\u5f55\u5165
\u3000\u3000\u9898\u76ee\u5f55\u5165\u6bd4\u8f83\u7b80\u5355\uff0c\u6253\u5f00\u201c\u51fa\u5377\u7cfb\u7edf.xlsx\u201d\uff0c\u76f4\u63a5\u5728\u586b\u7a7a\u9898\u3001\u9009\u62e9\u9898\u3001\u95ee\u7b54\u9898\u9898\u5e93\u5de5\u4f5c\u8868\u7684C\uff1aE\u5217\u8f93\u5165\u5373\u53ef\u3002\u4e0d\u8fc7\u8003\u8651\u5230\u6700\u7ec8\u751f\u6210\u8bd5\u5377\u7684\u6548\u679c\uff0c\u5728\u9898\u76ee\u8f93\u5165\u65f6\u6709\u4e00\u4e9b\u8981\u6c42\u9700\u8981\u6ce8\u610f\u4e00\u4e0b\uff1a
\u3000\u3000(1) \u586b\u7a7a\u9898\u4e2d\u7559\u5f85\u586b\u5199\u7684\u586b\u7a7a\u533a\u57df\u8981\u901a\u8fc7\u63d2\u5165\u4e2d\u6587\u4e0b\u5212\u7ebf\u5b9e\u73b0\u3002\u4e0d\u80fd\u63d2\u5165\u7a7a\u683c\u518d\u8bbe\u7f6e\u4e0b\u5212\u7ebf\u683c\u5f0f\uff0c\u5426\u5219\u5728Word\u4e2d\u751f\u6210\u8bd5\u5377\u540e\u4f60\u8fd8\u5f97\u518d\u9010\u4e00\u8bbe\u7f6e\u4e0b\u5212\u7ebf\u3002\u5728\u4e2d\u6587\u8f93\u5165\u6cd5\u754c\u9762\u4e0a\u53f3\u51fb\u90a3\u4e2a\u952e\u76d8\u56fe\u6807\uff0c\u9009\u62e9\u201c\u7279\u6b8a\u7b26\u53f7\u201d\u4ee5\u663e\u793a\u8f6f\u952e\u76d8\uff08\u56fe5\uff09\uff0c\u5355\u51fb\u8f6f\u952e\u76d8N\u952e\u6216\u76f4\u63a5\u6309\u952e\u76d8N\u952e\u5373\u53ef\u63d2\u5165\u4e2d\u6587\u4e0b\u5212\u7ebf\u201c \uff3f \u201d\uff0c\u6309Esc\u5173\u95ed\u8f6f\u952e\u76d8\u3002
\u3000\u3000(2) \u9009\u62e9\u9898\u4e2d\u4f60\u9700\u8981\u6309Alt+\u56de\u8f66\u952e\u6362\u884c\uff0c\u518d\u63d2\u5165\u7a7a\u683c\u8c03\u6574\u5404\u9009\u9879\u4f4d\u7f6e\u3002\u5efa\u8bae\u5148\u5728B2\u5355\u5143\u683c\u4e2d\u8f93\u5165\u4e00\u4e2a\u7a7a\u884c\u4ee5\u53caA\u3001B\u3001C\u3001D\u9009\u9879\u5e76\u7528\u7a7a\u683c\u5b9a\u4f4d\u597d\uff0c\u62d6\u52a8\u586b\u5145\u67c4\u628a\u8fd9\u4e2a\u5355\u5143\u683c\u590d\u5236\u5230\u4e0b\u9762\u5404\u5355\u5143\u683c\u4e2d\uff0c\u518d\u9010\u4e00\u8f93\u5165\u9898\u76ee\u548c\u9009\u9879\u5185\u5bb9\uff08\u56fe6\uff09\uff0c\u8fd9\u6837\u4f1a\u6bd4\u8f83\u65b9\u4fbf\u8f93\u5165\u3002\u53ea\u8981\u4e0d\u8f93\u5165\u6240\u5c5e\u5355\u5143\u90a3\u4e9b\u7a7a\u884c\u5c31\u4e0d\u4f1a\u53c2\u4e0e\u9009\u9898\u51fa\u5377\u3002\u6b64\u5916\uff0c\u6700\u597d\u5148\u9002\u5f53\u8c03\u6574C\u5217\u5217\u5bbd\u8ba9\u5404\u884c\u663e\u793a\u7684\u5b57\u6570\u6b63\u597d\u4e0eWord\u8bd5\u5377\u4e2d\u7684\u5355\u884c\u5b57\u6570\u4e00\u81f4\uff0c\u4ee5\u4fbf\u51c6\u786e\u8c03\u6574\u9009\u9879\u4f4d\u7f6e\u3002
\u3000\u3000(3) \u95ee\u7b54\u9898\u4e0b\u7559\u5f85\u586b\u5199\u7684\u7a7a\u884c\uff0c\u540c\u6837\u53ef\u4ee5\u6309Alt+\u56de\u8f66\u952e\u5728\u5355\u5143\u683c\u4e2d\u6362\u884c\u589e\u52a0\u3002\u4f46\u8981\u6ce8\u610f\u7684\u662f\u6bcf\u4e00\u4e2a\u7a7a\u884c\u4e2d\u81f3\u5c11\u8981\u8f93\u5165\u4e00\u4e2a\u7a7a\u683c\uff08\u56fe7\uff09\uff0c\u5426\u5219\u5728Word\u4e2d\u4f1a\u53ea\u5269\u4e0b\u4e00\u4e2a\u7a7a\u884c\u3002\u5efa\u8bae\u5148\u5728B2\u5355\u5143\u683c\u4e2d\u8f93\u5165\u4e00\u4e9b\u542b\u7a7a\u683c\u7684\u7a7a\u884c\uff0c\u518d\u628aB2\u5411\u4e0b\u590d\u5236\u5230\u5404\u5355\u5143\u683c\u4e2d\uff0c\u7136\u540e\u518d\u9010\u4e00\u8f93\u5165\u9898\u76ee\u3002\u5f53\u7136\u4f60\u4e5f\u53ef\u4ee5\u53ea\u8f93\u5165\u9898\u76ee\uff0c\u7b49\u6700\u7ec8\u751f\u6210\u8bd5\u5377\u540e\u518d\u5728\u8bd5\u5377\u4e2d\u9898\u76ee\u4e0b\u6dfb\u52a0\u7a7a\u884c\uff0c\u8fd9\u4e2a\u5c31\u770b\u4e2a\u4eba\u4e60\u60ef\u4e86\u3002
\u3000\u3000(4) \u589e\u52a0\u9898\u76ee\u53ef\u4ee5\u5728\u9898\u5e93\u540e\u8ffd\u52a0\u8f93\u5165\uff0c\u6216\u9009\u4e2d\u5df2\u6709\u9898\u76ee\u884c\u8fdb\u884c\u590d\u5236\uff0c\u53f3\u51fb\u9009\u62e9\u201c\u63d2\u5165\u590d\u5236\u884c\u201d\u540e\u518d\u4fee\u6539\u6210\u65b0\u9898\u76ee\u3002\u5220\u9664\u9898\u76ee\u53ef\u76f4\u63a5\u5220\u9664\u884c\uff0c\u4e0d\u8fc7\u5bf9\u4e8e\u53ea\u662f\u6682\u65f6\u4e0d\u7528\u7684\u9898\u76ee\u53ef\u4ee5\u4e0d\u5fc5\u6574\u884c\u5220\u9664\uff0c\u4f60\u53ea\u8981\u628a\u8be5\u9898\u201c\u6240\u5c5e\u5355\u5143\u201d\u7684\u6570\u5b57\u5220\u9664\u5c31\u53ef\u4ee5\u8ba9\u5b83\u4e0d\u53c2\u4e0e\u51fa\u9898\uff0c\u4e0b\u6b21\u9700\u8981\u65f6\u518d\u91cd\u65b0\u8f93\u5165\u201c\u6240\u5c5e\u5355\u5143\u201d\u6570\u5b57\u5373\u53ef\u6062\u590d\u3002
1\u3001\u9898\u5e93\u8868\u683c\u5236\u4f5c\u8981\u6c42
(1)\u9009\u62e9\u9898\u9898\u5e93
A1 \uff1a\u5e8f\u53f7
B1 \uff1a\u9898\u76ee
A2:1
B2:\u300a\u623f\u5c4b\u5efa\u7b51\u5236\u56fe\u7edf\u4e00\u6807\u51c6\u300b\uff08GB/T50001\u20142001\uff09\u89c4\u5b9a\u6807\u9898\u680f\u5728\u56fe\u6846\u5185\u7684\u4f4d\u7f6e\u662f\uff1a \uff08 \uff09
A3:1.1
B3:A\u3001 \u5de6\u4e0a\u89d2 B\u3001\u5de6\u4e0b\u89d2 C\u3001 \u53f3\u4e0a\u89d2 D\u3001 \u53f3\u4e0b\u89d2
\u4e00\u6761\u9898\u5236\u4f5c\u5b8c\u6210
2 \u2026\u2026
2.1 \u2026\u2026
\u5982\u6b64\u4e0b\u53bb500\u4e2a\u603b\u51711001\u884c\u3002\u9898\u5e93A2:B1001\u540d\u79f0\u547d\u540dXST
(2)\u5224\u65ad\u9898\u5e93
A1 \uff1a\u5e8f\u53f7
B1 \uff1a\u9898\u76ee
A2:1
B2:\u5efa\u7b51\u65bd\u5de5\u56fe\u4e2d\u7684\u5c3a\u5bf8\u5c5e\u4e8e\u6807\u5fd7\u5c3a\u5bf8\u3002( )
\u5b8c\u6210\u4e00\u6761\u9898
2 \u2026\u2026
\u5982\u6b64\u4e0b\u53bb400\u4e2a\u603b\u5171401\u884c\u3002\u9898\u5e93A2:B401\u540d\u79f0\u547d\u540dPDT
(3)\u95ee\u7b54\u9898\u5e93
A1 \uff1a\u5e8f\u53f7
B1 \uff1a\u9898\u76ee
A2:1
B2:\u5916\u4fdd\u6e29\u590d\u5408\u8282\u80fd\u5899\u4f53\u7684\u4f18\u7f3a\u70b9\u53ca\u6539\u8fdb\u9014\u5f84\uff1f
\u5b8c\u6210\u4e00\u6761\u9898
2 \u2026\u2026
\u5982\u6b64\u4e0b\u53bb50\u4e2a\u603b\u517151\u884c\u3002\u9898\u5e93A2:B51\u540d\u79f0\u547d\u540dWDT
2\u3001\u5236\u4f5c\u6570\u636e\u8868(LPIN)\u548c\u8bd5\u9898\u8868\uff08JLB1)
\u7528Excel\u5f00\u53d1\u5efa\u7b51\u5de5\u7a0b\u8d44\u6599\u7684\u7cfb\u7edf
\u7528\u591a\u884c\u4e00\u8868\u6cd5
\uff081\uff09LPIN\u88684\u884c\u4e2d\u586b\u5199
\u9009\u62e9\u9898\u9898\u5e9330\u4e2a \uff0c\u5224\u65ad\u9898\u5e9320\u4e2a\uff0c\u95ee\u7b54\u9898\u5e932\u4e2a \uff0c\u752882\u884c\u5b8c\u6210\u4e00\u5f20\u8bd5\u5377
B4:\u5e8f\u53f7\u6700\u5927\u503c\u662f82
E4\uff1a\u9898\u5e93\u968f\u673a\u9898\u53f7
F4:\u9898\u76ee
5\u884c\u81f364\u5904\u7406\u9009\u62e9\u9898\u9898\u5e93\uff08\u9898\u5e93\u968f\u673a\u9898\u53f7\u662f\u5b8f\u81ea\u52a8\u751f\u6210\uff0c\u9898\u76ee\u662f\u7528\u516c\u5f0fF5:=VLOOKUP(E5,XST,2,FALSE)\uff0c\u76f4\u590d\u5236\u523064\u884c\uff09
\uff0c65\u884c\u81f384\u884c\u5904\u7406\u5224\u65ad\u9898\u5e93\uff08\u9898\u5e93\u968f\u673a\u9898\u53f7\u662f\u5b8f\u81ea\u52a8\u751f\u6210\uff0c\u9898\u76ee\u662f\u7528\u516c\u5f0fF65:=VLOOKUP(E65,PDT,2,FALSE)\uff0c\u76f4\u590d\u5236\u523084\u884c\uff09
\uff0c85\u884c\u81f386\u884c\u5904\u7406\u95ee\u7b54\u9898\u5e93\uff08\u9898\u5e93\u968f\u673a\u9898\u53f7\u662f\u5b8f\u81ea\u52a8\u751f\u6210\uff0c\u9898\u76ee\u662f\u7528\u516c\u5f0fF85:=VLOOKUP(E85,WDT,2,FALSE)\uff0c\u76f4\u590d\u5236\u523086\u884c\uff09
\u9898\u5e93\u968f\u673a\u9898\u53f7\u662f\u5b8f\u81ea\u52a8\u751f\u6210\u4ee3\u7801\u662f\uff1a
Sub \u751f\u6210\u9898\u5e93\u968f\u673a\u9898\u53f7()
Dim a(), b(), c(), d(), i As Long, j As Long, k As Long, r As Long
ReDim a(30, 1)
Randomize
For i = 1 To 30
my:
a(i, 1) = Int(500 * Rnd + 1)
If a(i, 1) = 0 Then GoTo my
For j = 1 To i - 1
If a(i, 1) = a(j, 1) Then i = i - 1: Exit For
Next
Next i
ReDim b(20, 1)
For i = 1 To 20
my1:
b(i, 1) = Int(400 * Rnd + 1)
If b(i, 1) = 0 Then GoTo my1
For j = 1 To i - 1
If b(i, 1) = b(j, 1) Then i = i - 1: Exit For
Next
Next i
ReDim c(2, 1)
For i = 1 To 2
my2:
c(i, 1) = Int(50 * Rnd + 1)
If c(i, 1) = 0 Then GoTo my2
For j = 1 To i - 1
If c(i, 1) = c(j, 1) Then i = i - 1: Exit For
Next
Next i
ReDim d(82, 1)
k = 0
For i = 1 To 30
k = k + 1
d(k, 1) = a(i, 1)
k = k + 1
d(k, 1) = a(i, 1) & ".1"
Next i
For i = 1 To 20
k = k + 1
d(k, 1) = b(i, 1)
Next i
For i = 1 To 2
k = k + 1
d(k, 1) = c(i, 1)
Next i
If ActiveSheet.Name = "LPIN" Then
r = Range("e" & Rows.Count).End(xlUp).Row + 1
Range("e" & r & ":e" & r + 81) = d
End If
End Sub
\u751f\u6210\uff1a
60
60.1
231
231.1
48
48.1
203
203.1
142
142.1
456
456.1
91
91.1
42
42.1
340
340.1
339
339.1
133
133.1
475
475.1
495
495.1
146
146.1
29
29.1
464
464.1
121
121.1
109
109.1
239
239.1
162
162.1
172
172.1
152
152.1
147
147.1
446
446.1
418
418.1
243
243.1
8
8.1
85
85.1
458
458.1
370
370.1
12
25
279
113
398
261
292
10
191
118
306
115
342
376
293
174
283
72
232
347
37
23
\uff082\uff09\u8bd5\u9898\u8868\uff08JLB1)
\u53c2\u8003\u7528Excel\u5f00\u53d1\u5efa\u7b51\u5de5\u7a0b\u8d44\u6599\u7684\u7cfb\u7edf\u7684\u516c\u5f0f\u5236\u4f5c
\u516c\u5f0f\u8981\u52a0\u5165\u5e8f\u53f7\u9898\u76ee\u5e8f\u53f7\u7b2c1\u9898\uff0c\u662f=IF(INDIRECT("LPIN!F"&LPIN!A3+4)="","",\u201c1\u3001\u201d&INDIRECT("LPIN!F"&LPIN!A3+4))\u3002
这个用基本操作可能不太好实现。需要用VBA自动处理。
不是说一句话 就能解决了。
绛旓細鍦╯heet1閲岄潰鎶552閬撻鍏ㄩ儴褰曡繘鍘伙紝瑕佹眰搴忓彿銆侀銆佹爣鍑嗙瓟妗堜笁鑰呭繀椤诲湪鐙珛鍒椾腑锛屽湪sheet2涓敤randbetween鍑芥暟鐢熸垚1-552鐨勯殢鏈烘暣鏁帮紝浣嗘槸鍋氬埌涓嶉噸澶嶈繕闇瑕佽皟鏁翠竴涓嬶紝鐒跺悗鏍规嵁闅忔満鐢熸垚鐨勯鍙风敤鍑芥暟绛涢夊嚭棰橈紝鍐嶇敤IF鍑芥暟鏉鍒ゆ柇绛旈鑰呯瓟妗堢殑瀵归敊
绛旓細璁剧涓濂鐨勬垚鏈槸XX*[1+25%]=135X=108鐩堝埄:135-108=27鍏冭绗簩濂楃殑鎴愭湰鏄痀Y[1-25%]=135Y=180浜忔崯:180-135=45鍏冩墍浠,鎬荤殑鏄簭浜,浜:45-27=18鍏 21.涓绉嶉ギ鐢ㄦ按鐨勫渾鏌卞舰姘存《鐨勫唴鐩村緞涓25鍘樼背,鍐呭楂樹负35鍘樼背,鏈変竴绉嶅唴寰勪负6鍘樼背,鍐呭楂樹负10鍘樼背鐨勭幓鐠冩澂,鑻ユ妸涓妗堕ギ鐢ㄦ按鍒嗙洓浜庤繖绉嶇幓鐠冩澂,闇瑕...
绛旓細瑙o細鑻规灉鍔犳ⅷ锛420*3/5=252(kg),姊ㄥ姞姗樺瓙锛420*5/7=300(kg),鑻规灉鍔2鍊嶆ⅷ鍔犳瀛愶細252+300=552(kg)锛屽洜涓鸿嫻鏋滃姞姊ㄥ姞姗樺瓙涓420锛屾墍浠ユⅷ涓552-420=132(kg)
绛旓細鍏跺疄浠ヤ笁鏄熼泦鍥㈠拰鑻规灉鍏徃鏉ュ姣,瀹屽叏鏄笉鍦ㄤ竴涓。娆′笂,涓夋槦闆嗗洟鐩墠鏉ヨ鍗犳嵁鐫鏁翠釜闊╁浗缁忔祹GDP鐨1/5宸﹀彸,杩欎笌鑻规灉鐩告瘮鏉ヨ鏈夌潃澶╁¥涔嬪埆,涓夋槦鎵嶆槸涓ユ牸鎰忎箟涓婄殑瀵″ご鍏徃銆 浠庣被鍒笂鏉ヨ,涓夋槦鐨勬墜鏈哄彧鏄笁鏄熼泦鍥竴涓瘮杈冮噸瑕佺殑閮ㄩ棬鑰屽凡銆備笁鏄熼泦鍥㈤櫎浜嗗啘涓氫箣澶栧嚑涔庡悇涓鍩熼兘鏈夋秹鍙,鍖呮嫭鐢靛瓙宸ヤ笟銆佹満姊板拰閲嶅伐涓氥佸寲瀛﹀伐...
绛旓細鍘嗗彶缁忛獙涓鍐嶅憡璇夋垜浠:鏈夋病鏈夊洟缁撲簰鍔╃殑閬撳痉椋庡皻,鏄鍒ゆ柇涓涓兢浣撴槸鍚﹀仴搴枫佷竴涓ぞ浼氭槸鍚﹀拰璋愮殑閲嶈鏍囧織涔嬩竴銆傜户鎵夸腑鍗庢皯鏃忎紭鑹殑浼犵粺鏂囧寲,寰堥噸瑕佺殑涓涓柟闈,灏辨槸寮樻壃鍥㈢粨鍙嬬埍銆佷簰甯簰鍔╃殑缇庡痉銆 鏄晩!鎴戝浗绱犱互鈥滅ぜ浠箣閭︹濊憲绉颁簬涓,璁测滀粊鐖扁濄佽鈥滃弸鍠勨濄侀噸鈥滀汉鍜屸濈殑浼犵粺缇庡痉銆傛垜浠柊涓浠g殑涓鐢熷簲璇ヤ紶鎵胯繖浜...
绛旓細涔熷氨鏄,鏍规嵁鍦伴渿浜х敓鐨勭绉嶈抗璞鍒ゅ畾,鍦伴渿蹇呴』鍏峰杩欐牱涓や釜鏉′欢:涓鏄,鍦扮悆鍐呴儴瑕佸叿澶囨瀬楂樼殑娓╁害浣垮唴閮ㄧ殑鐗╄川鍏峰娴佸姩鎬,鑳藉涓嶆柇鍦拌繘琛岃繍鍔ㄨ繖鏍风殑鍩虹,鍦扮悆鍐呴儴纭‘瀹炲疄鍏峰杩欐牱鐨勫熀纭鏉′欢;浜屾槸,鍦板3鐨勯儴浣嶄笂蹇呴』鍏峰鍌ㄥ瓨澶ч噺鐨勯珮鐑肩殑鑳介噺鐗╄川鎴栨斁灏勬х墿璐ㄨ繖鏍风殑澶栭儴鏉′欢,鍦扮悆涔熷悓鏍峰叿澶囪繖鏍风殑鏉′欢銆傜畝鍗曞湴璇,鍦版牳...
绛旓細宸ヤ綔鏁堢巼=宸ヤ綔鎬婚噺梅宸ヤ綔鏃堕棿涓轰粈涔堟婚噺鍙互鏄1?杩欓噷鏄妸宸ヤ綔鎬婚噺鐪嬪仛鍗曚綅1.姣斿:涓椤瑰伐绋,鐢查槦6澶╁仛瀹,閭d箞鐢查槦鐨勫伐浣滄晥鐜囧氨鏄1梅6=1/6 鏈洖绛旂敱鎻愰棶鑰呮帹鑽 涓炬姤| 璇勮(5) 16 7 pkzxxxjdc1 閲囩撼鐜:72% 鏉ヨ嚜鍥㈤槦:鏁板鐧惧垎鐧 鎿呴暱: 灏忓鏁欒偛 瀛︿範甯姪 鏁板 鍏朵粬鍥炵瓟 鍙互鏄叿浣撶殑閲,涔熷彲浠ユ槸瀹屾垚杩涘害...
绛旓細涓囧巻鍒濆勾锛屽叏鍥借储鏀挎敮鍑轰负1854涓囦袱閾跺瓙锛屼粎鐨囧灏辫姳浜552涓囦袱閾跺瓙锛岃繎涓夊垎涔嬩竴銆傚悓鏃讹紝涓囧巻涓夊緛鎵娑堣楃殑鐧介摱鎬婚噺鍙湁1280涓囦袱锛屼粎鐩稿綋浜庝袱骞村鏉ヤ緵鍏诲畻鏃忕殑鏁伴噺銆傚彲瑙侊紝鐨囧鑺变簡涓嶅皯閽便備竾鍘嗗勾闂达紝鏇翠笉鐢ㄨ宕囩ク鏃舵湡锛岃繖浜涙槑鏈濆埗搴︾殑鏈澶у彈鐩婅閫夋嫨浜嗗湪鏈濆环鍗遍毦涔嬮檯琚栨墜鏃佽鍠濋厭銆備粬浠殑濂緢鍔犻熶簡鏄庢湞...