{"id":523,"date":"2021-12-20T20:35:35","date_gmt":"2021-12-20T12:35:35","guid":{"rendered":"http:\/\/www.henrycome.com\/wordpress\/?p=523"},"modified":"2022-03-18T07:20:38","modified_gmt":"2022-03-17T23:20:38","slug":"20211220204413","status":"publish","type":"post","link":"http:\/\/www.henrycome.com\/wordpress\/202112202035\/523","title":{"rendered":"\u3010WPS\u5f00\u53d1\u3011VBA\u6279\u91cf\u5904\u7406excel\u6587\u4ef6\uff1a\u66f4\u6b63\u3001\u6392\u5e8f\u3001\u5224\u65ad\u8bc4\u5206"},"content":{"rendered":"\n
\"\"<\/a>
\u542f\u793a\u8fd0\u884c\u754c\u9762\uff1a\u529f\u80fd\u9009\u62e9<\/figcaption><\/figure><\/div>\n\n\n\n

\u4e00\u59574\u4e2a\u8868\u683c\u6587\u4ef6\uff0c\u5206\u4e3a\u811a\u672c.xlam\u6587\u4ef6\uff0c\u57fa\u7840\u6570\u636e.xlsx\uff0c\u8fd8\u6709\u7528\u6765\u767b\u8bb0\u811a\u672c\u6311\u9009\u51fa\u6570\u636e\u7684\u6587\u4ef6\u7535\u8bdd\u53f7\u7801.xlsx\u548c\u767b\u8bb0\u9700\u63d0\u5206.xlsx2\u4e2a\u6587\u4ef6\u3002\u3010\u6c34\u5e73\u6709\u9650\uff0c\u6b22\u8fce\u63d0\u51fa\u6539\u8fdb\u610f\u89c1\u3011\uff08vba\u811a\u672c\u662f\u53d7\u4eb2\u621a\u59d4\u6258\u7ed9\u5355\u4f4d\u5236\u4f5c\u7684\u7528\u6765\u6279\u91cf\u5904\u7406excel\u8868\u683c\u7684\uff0c\u5927\u69822017\u5e74\u3002\uff09<\/p>\n\n\n\n

\u7a97\u4f53\u4ee3\u7801\uff1a<\/p>\n\n\n\n

Public \u586b\u5145\u9009\u9879, \u66f4\u6b63\u683c\u5f0f\nPublic \u5b58\u6b3e\u4f59\u989d\u5c0f\u9879, \u8d1f\u503a\u62c5\u4fdd\u5c0f\u9879, \u52a1\u5de5\u5730\u70b9\u5c0f\u9879, \u5408\u4f5c\u8d44\u91d1\u9700\u6c42\u5c0f\u9879, \u586b\u7535\u8bdd\u53f7\u7801\u5c0f\u9879, \u56db\u79cd\u4fe1\u7528\u5224\u5b9a\u5c0f\u9879, \u8bc4\u5206\u5c0f\u9879, \u767b\u8bb0\u9700\u63d0\u5206\u5c0f\u9879\n\n'Private Sub CheckBox11_change()'\u8c03\u8bd5\u7684\u65f6\u5019\u70b9\u51fb\u9009\u9879\u4f1a\u51fa\u73b0\u5230\u8fd9\u91cc\u4e86\u3002\u5947\u602a\u3002\n'CheckBox1.Enabled = False\n'CheckBox1.Value = 1\n'CheckBox2.Enabled = False\n'CheckBox2.Value = 1\n'CheckBox3.Enabled = False\n'CheckBox3.Value = 1\n'CheckBox4.Enabled = False\n'CheckBox4.Value = 1\n'CheckBox5.Enabled = False\n'CheckBox5.Value = 0\n'CheckBox6.Enabled = False\n'CheckBox6.Value = 0\n'CheckBox7.Enabled = False\n'CheckBox5.Value = 0\n'CheckBox8.Enabled = False\n'CheckBox6.Value = 0\n'End Sub\n\n\nPrivate Sub CheckBox9_Change() '\u8c03\u8bd5\u7684\u65f6\u5019\u70b9\u51fb\u9009\u9879\u4f1a\u51fa\u73b0\u5230\u8fd9\u91cc\u4e86\uff0c\u7ee7\u7eed\u8c03\u8bd5\u5230\u8fd9\u4e2a\u8bed\u53e5\u5b8c\uff0c\u4e0d\u4f1a\u53bb\u7ee7\u7eed\u8c03\u8bd5\u4e86\u3002\u4f46\u662f\u7a0b\u5e8f\u8fd8\u5728\u8fd0\u884c\u4e2d\u3002\u90a3\u4ee5\u540e\u600e\u4e48\u8c03\u8bd5\u554a\uff1f\u90c1\u95f7\u3002\nIf CheckBox9.Value = True Then\nCheckBox1.Enabled = True\n'CheckBox1.Value = 1\nCheckBox2.Enabled = True\n'CheckBox2.Value = 1\nCheckBox3.Enabled = True\n'CheckBox3.Value = 1\nCheckBox4.Enabled = True\n'CheckBox4.Value = 1\nCheckBox5.Enabled = True\n'CheckBox5.Value = 0\nCheckBox6.Enabled = True\n'CheckBox6.Value = 0\n'CheckBox7.Enabled = True\n'CheckBox5.Value = 0\n'CheckBox8.Enabled = True\n'CheckBox6.Value = 0\nCheckBox8.Enabled = True\n'CheckBox6.Value = 0\nCheckBox10.Enabled = True\n'CheckBox6.Value = 0\n\nElse\nCheckBox1.Enabled = False\n'CheckBox1.Value = 1\nCheckBox2.Enabled = False\n'CheckBox2.Value = 1\nCheckBox3.Enabled = False\n'CheckBox3.Value = 1\nCheckBox4.Enabled = False\n'CheckBox4.Value = 1\nCheckBox5.Enabled = False\n'CheckBox5.Value = 0\nCheckBox6.Enabled = False\n'CheckBox6.Value = 0\n'CheckBox7.Enabled = False\n'CheckBox5.Value = 0\n'CheckBox8.Enabled = False\n'CheckBox6.Value = 0\n'CheckBox10.Enabled = False\n'CheckBox6.Value = 0\nEnd If\nEnd Sub\n\n\nPrivate Sub CommandButton1_Click()\n\u586b\u5145\u9009\u9879 = CheckBox9.Value\n\u66f4\u6b63\u683c\u5f0f = CheckBox10.Value\n\u5b58\u6b3e\u4f59\u989d\u5c0f\u9879 = CheckBox1.Value\n\u8d1f\u503a\u62c5\u4fdd\u5c0f\u9879 = CheckBox2.Value\n\u52a1\u5de5\u5730\u70b9\u5c0f\u9879 = CheckBox3.Value\n\u5408\u4f5c\u8d44\u91d1\u9700\u6c42\u5c0f\u9879 = CheckBox4.Value\n\u586b\u7535\u8bdd\u53f7\u7801\u5c0f\u9879 = CheckBox5.Value\n\u56db\u79cd\u4fe1\u7528\u5224\u5b9a\u5c0f\u9879 = CheckBox6.Value\n\u8bc4\u5206\u5c0f\u9879 = CheckBox7.Value\n\u767b\u8bb0\u9700\u63d0\u5206\u5c0f\u9879 = CheckBox8.Value\n\nLabel1.Visible = True\n\n'If \u586b\u5145\u9009\u9879 = True Then '\u8fd9\u91cc\u5fc5\u987b\u5199true\u6216\u8005false\uff0co\u548c1\u4e0d\u597d\u4f7f\u5566\u3002\u8d4b\u503c\u7684\u65f6\u5019\u53ef\u4ee5\uff0c\u53d6\u503c\u7684\u65f6\u5019\u5c31\u4e0d\u884c\u4e86\u3002\nCall \u586b\u5145\u9009\u9879\u5f00\u59cb '\u8fd9\u662f\u5165\u53e3\n'ElseIf \u66f4\u6b63\u683c\u5f0f = True Then\n'Call \u66f4\u6b63\u683c\u5f0f\u5f00\u59cb\n'End If\n\nLabel1.Visible = False\n\n\nEnd Sub\n\nPrivate Sub CommandButton2_Click()\nUserForm1.Hide\n\n\nEnd Sub\n\nPrivate Sub UserForm_Initialize()\n'\u7a97\u53e3\u521d\u59cb\u5316\u7684\u5176\u4e2d\u63a7\u4ef6\u5c5e\u6027\u597d\uff0c\u8fd8\u662f\u5c5e\u6027\u81ea\u5df1\u8bbe\u7f6e\u597d\u4e86\u597d\u5462\uff1f\u611f\u89c9\u4e00\u6837\u3002initialize\u662f\u4ec0\u4e48\u65f6\u5019\u7684\u4e8b\u4ef6\u3002\u662f\u7a97\u53e3\u7b2c\u4e00\u6b21\u751f\u6210\uff0c\u8fd8\u662fshow\u7684\u65f6\u5019\u5462\uff1f\u8fd8\u662fhide\u4e4b\u540eshow\u7684\u65f6\u5019\u7b97\u4e48\uff1f\nCheckBox1.Enabled = False\n'CheckBox1.Value = 1\nCheckBox2.Enabled = False\n'CheckBox2.Value = 1\nCheckBox3.Enabled = False\n'CheckBox3.Value = 1\nCheckBox4.Enabled = False\n'CheckBox4.Value = 1\nCheckBox5.Enabled = False\n'CheckBox5.Value = 0\nCheckBox6.Enabled = False\n'CheckBox6.Value = 0\n'CheckBox7.Enabled = False\n'CheckBox5.Value = 0\n'CheckBox8.Enabled = False\n'CheckBox6.Value = 0\n'CheckBox10.Enabled = False\n'CheckBox6.Value = 0\nEnd Sub\n<\/code><\/pre>\n\n\n\n
\u6a21\u57571\uff1a<\/p>\n\n\n\n
    '\u672c\u6a21\u5757\u4ee5\u586b\u5145\u4e3a\u4e3b\n    Dim gg As String   '\u5b9a\u4e49fld,gg\u4e3a\u53d8\u91cf\n    Public file, filename As Variant '\u672c\u6a21\u5757\u4f7f\u7528\uff0c\u5982\u4e3apulic\u5168\u7a0b\u5e8f\u4f7f\u7528\u7a97\u4f53\u4e2d\u7684public\u5728\u5176\u4ed6\u6a21\u5757\u5f15\u7528\uff0c\u683c\u5f0f\u4e3a \u7a97\u4f53\u540d.\u53d8\u91cf\u540d\u3002\n    Public wb As Workbook '\u5728\u4e0a\u9762\u58f0\u660e\u4e3apublic\n\n    \n    \nSub \u586b\u5145\u9009\u9879\u5f00\u59cb()\n    'UserForm2.Show'\u4e0d\u80fd\u5728\u8fd9\u91cc\u6dfb\u52a0\uff0c\u56e0\u4e3a\u7a97\u53e3\u51fa\u6765\u4e86\uff0c\u4e0d\u4f1a\u6389\uff0c\u53ea\u80fd\u6309\u4e2a\u6309\u94ae\u52a0\u4e0a\u4ee3\u7801\u624d\u884c\u5509\u3002\n    Dim shell As Variant\n    Set shell = CreateObject(\"Shell.Application\")\n    Set filePath = shell.BrowseForFolder(&O0, \"\u9009\u62e9\u6587\u4ef6\u5939\", &H1 + &H10, \"\")   '\u83b7\u53d6\u6587\u4ef6\u5939\u8def\u5f84\u5730\u5740\n    Set shell = Nothing\n  If filePath Is Nothing Then                 '\u68c0\u6d4b\u662f\u5426\u83b7\u5f97\u6709\u6548\u8def\u5f84\uff0c\u5982\u53d6\u6d88\u76f4\u63a5\u8df3\u51fa\u7a0b\u5e8f\n       Exit Sub\n    Else\n       gg = filePath.Items.Item.Path\n  End If\n    Call \u586b\u5145\u9009\u9879FileSearch(gg, \"*.xls?\")\nEnd Sub\n\nSub \u586b\u5145\u9009\u9879FileSearch(Path As String, Target As String)\n\n    Dim FSO As Object, Folder As Variant\n    Set FSO = CreateObject(\"Scripting.FileSystemObject\")\n    For Each Folder In FSO.GetFolder(Path).SubFolders\n        Call \u586b\u5145\u9009\u9879FileSearch(Folder.Path, Target)\n    Next Folder\n    For Each file In FSO.GetFolder(Path).Files\n    \n    filename = Right(file, Len(file) - Len(Path) - 1)\n    If (filename Like Target Or filename Like \"*.xls\") And Left(filename, 2) <> \"~$\" Then\n    Call \u586b\u5145\u9009\u9879\u6253\u5f00\u6587\u4ef6\n    End If\n    Next file\n    \nEnd Sub\n\nPublic Sub \u586b\u5145\u9009\u9879\u6253\u5f00\u6587\u4ef6()\n    'Dim wb As Workbook '\u5728\u4e0a\u9762\u58f0\u660e\u4e3apublic\n    Set wb = Application.Workbooks.Open(file)\n    If wb Is Nothing Then MsgBox (\"\u6587\u4ef6\u6253\u5f00\u5931\u8d25\uff0c\u8bf7\u68c0\u67e5\" & pth & fn & \"\u662f\u5426\u5b58\u5728\uff01\"): Exit Sub\n    Call \u586b\u5145\u9009\u9879 '\u5728\u6b64\u6dfb\u52a0\u64cd\u4f5c\u4ee3\u7801\n    'wb.Close True '\u5982\u679c\u65e0\u9700\u4fdd\u5b58\uff0c\u672c\u53c2\u6570\u7528false\n    'wb.Close True\n    Workbooks(filename).Close True '\u56e0\u4e3a\u7ecf\u8fc7\u540e\u9762\u4e00\u7cfb\u5217\u7684\u8fc7\u7a0b\uff0c\u73b0\u5728\u7684filename\u6765\u5173\u95ed\u6587\u4ef6\u3002\u800c\u4e0d\u662f\u7528wb\u4e86\u3002\u56e0\u4e3awb\u53ef\u80fd\u5df2\u7ecf\u6ca1\u6709\u4e86\uff08\u5728\u66f4\u6b63\u683c\u5f0f\u90a3\u91cc\uff0cwb.close\u4e86\u3002\u662f\u4e0d\u662f\u8fd9\u4e2a\u53d8\u91cf\u4e5f\u6ca1\u6709\u4e86\uff0c\u5c31\u50cfkill file\u4e00\u6837\u3002\uff09\u3002\n    \nEnd Sub\nSub \u586b\u5145\u9009\u9879()\n\nWith Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\")\n\n    '====\u4e0b\u9762\u90e8\u5206\u4e3a\u4fee\u6539\u88684\u7684\u5bb6\u5ead\u8d44\u4ea7\u90e8\u5206====\n    If UserForm1.\u5b58\u6b3e\u4f59\u989d\u5c0f\u9879 = True Then\n    .[C36] = a '\u6ca1\u6709\u8d4b\u503c\u7684a\u548cb\uff0c\u5355\u5143\u683c\u5c31\u662f\u521d\u59cb\u7a7a\u683c\u3002\u907f\u514d\u5355\u5143\u683c\u683c\u5f0f\u4e3a0\u6216\"\"\u95ee\u9898\u3002\n    .[H36] = B\n    End If\n    \n    '====\u4e0b\u9762\u90e8\u5206\u4e3a\u4fee\u6539\u88684\u7684\u501f\u6b3e\u62c5\u4fdd\u90e8\u5206====\n    If UserForm1.\u8d1f\u503a\u62c5\u4fdd\u5c0f\u9879 = True Then\n     If .[B38] = 0 Or .[B38] = \"\" Then\n     .[B38] = 0\n     End If\n     If .[D38] = 0 Or .[D38] = \"\" Then\n     .[D38] = 0\n     End If\n    .[F38] = 0 '\u4ed6\u884c\u501f\u6b3e\u7b49\u5f00\u59cb\n    .[H38] = 0\n    .[J38] = 0\n    .[L38] = 0\n    End If\n    \n        '====\u4e0b\u9762\u90e8\u5206\u4e3a\u4fee\u6539\u88684\u7684\u52a1\u5de5\u5730\u70b9\u201c\u5b9d\u9e21\u5e02\u201d\u90e8\u5206====\n    If UserForm1.\u52a1\u5de5\u5730\u70b9\u5c0f\u9879 = True Then ' value\u53ea\u80fd\u662ftrue\u6216\u8005false\n    .[F52] = \"A-\u519c\u3001\u6797\u3001\u7267\u3001\u6e14\u4e1a\"\n    .[G52] = \"610000\u9655\u897f\u7701\"\n    .[H52] = \"610300\u5b9d\u9e21\u5e02\"\n    End If\n    \n    '====\u4e0b\u9762\u90e8\u5206\u4e3a\u4fee\u6539\u88684\u7684\u6211\u884c\u5408\u4f5c\u53ca\u8d44\u91d1\u9700\u6c42\u90e8\u5206====\n    If UserForm1.\u5408\u4f5c\u8d44\u91d1\u9700\u6c42\u5c0f\u9879 = True Then\n    .CheckBoxes(\"Check Box 64\").Value = xlOn '\u5408\u4f5c\u9009\u5176\u4ed6\n    .[D61] = \"\u65e0\" '\u9700\u6c42\u9009\u65e0000\n    .[G61] = 0\n    .[I61] = 0\n    End If\n    \n    If UserForm1.\u586b\u7535\u8bdd\u53f7\u7801\u5c0f\u9879 = True Then\n    \n    Dim i As Integer\n          For i = 1 To Workbooks.Count\n            If Workbooks(i).Name = \"\u7535\u8bdd\u53f7\u7801.xlsx\" Then\n            h1 = 1\n            Exit For\n            End If\n          Next\n          \n            If h1 = 0 Then '\u56e0\u4e3ah1 \u4e0d\u88ab\u8d4b\u503c\u7684\u8bdd\uff0c\u90a3\u4e48\u5c31\u662f\u7a7a\u503c\uff0c\u903b\u8f91\u4e0a\u7b49\u4e8e0\n            Dim wb2 As Workbook\n            Dim file2\n            file2 = ThisWorkbook.Path & \"\/\u7535\u8bdd\u53f7\u7801.xlsx\"\n            Set wb2 = Application.Workbooks.Open(file2)\n               If wb2 Is Nothing Then MsgBox (\"\u6587\u4ef6\u6253\u5f00\u5931\u8d25\uff0c\u8bf7\u68c0\u67e5\" & pth & fn & \"\u662f\u5426\u5b58\u5728\uff01\"): Exit Sub\n            End If\n            \n        Dim \u7535\u8bdd\u53f7\u7801, \u8eab\u4efd\u8bc1\u53f7, \u6240\u5728\u884c\u53f7\n        \u8eab\u4efd\u8bc1\u53f7 = .[B6]\n        If .[B5] = Application.WorksheetFunction.VLookup(\u8eab\u4efd\u8bc1\u53f7, Workbooks(\"\u7535\u8bdd\u53f7\u7801.xlsx\").Worksheets(\"\u7535\u8bdd\u53f7\u7801\").Range(\"$A:$C\"), 2, False) Then '.[B5]\u662f\u59d3\u540d\u3002\n        \u7535\u8bdd\u53f7\u7801 = Application.WorksheetFunction.VLookup(\u8eab\u4efd\u8bc1\u53f7, Workbooks(\"\u7535\u8bdd\u53f7\u7801.xlsx\").Worksheets(\"\u7535\u8bdd\u53f7\u7801\").Range(\"$A:$C\"), 3, False)\n        .[I5] = \u7535\u8bdd\u53f7\u7801\n        Else\n        \u6240\u5728\u884c\u53f7 = Application.WorksheetFunction.Match(\u8eab\u4efd\u8bc1\u53f7, Workbooks(\"\u7535\u8bdd\u53f7\u7801.xlsx\").Worksheets(\"\u7535\u8bdd\u53f7\u7801\").Range(\"$A:$A\"), 0)\n        Workbooks(\"\u7535\u8bdd\u53f7\u7801.xlsx\").Worksheets(\"\u7535\u8bdd\u53f7\u7801\").Range(\"D\" & \u6240\u5728\u884c\u53f7) = filename\n        Workbooks(\"\u7535\u8bdd\u53f7\u7801.xlsx\").Worksheets(\"\u7535\u8bdd\u53f7\u7801\").Hyperlinks.Add Anchor:=Workbooks(\"\u7535\u8bdd\u53f7\u7801.xlsx\").Worksheets(\"\u7535\u8bdd\u53f7\u7801\").Range(\"D\" & \u6240\u5728\u884c\u53f7), Address:=file\n\n        End If\n    \n    End If\n    \n    If UserForm1.\u56db\u79cd\u4fe1\u7528\u5224\u5b9a\u5c0f\u9879 = True Then\n    Dim \u672c\u884c\u501f\u6b3e, \u672c\u884c\u62c5\u4fdd, \u4e0d\u826f\u501f\u6b3e\u4f59\u989d, \u4e0d\u826f\u62c5\u4fdd\u4f59\u989d\n         \u672c\u884c\u501f\u6b3e = .[B38]\n         \u672c\u884c\u62c5\u4fdd = .[D38]\n         \u4e0d\u826f\u501f\u6b3e\u4f59\u989d = .[J39]\n         \u4e0d\u826f\u62c5\u4fdd\u4f59\u989d = .[L39]\n         \n         '\u521d\u59cb\u9009\u5b9a\u4e3a\"2-\u5426\"\uff0c\u907f\u514d\u51fa\u73b0\u591a\u4e2a\"1-\u662f\"\n         .[B12] = \"2-\u5426\"\n         .[E12] = \"2-\u5426\"\n         .[G12] = \"2-\u5426\"\n         .[J12] = \"2-\u5426\"\n         \n         If (\u672c\u884c\u501f\u6b3e > 0 Or \u672c\u884c\u62c5\u4fdd > 0) And (\u4e0d\u826f\u4f59\u989d = 0 And \u4e0d\u826f\u62c5\u4fdd = 0) Then\n         .[B12] = \"1-\u662f\"\n         ElseIf \u672c\u884c\u501f\u6b3e + \u672c\u884c\u62c5\u4fdd + \u4e0d\u826f\u501f\u6b3e\u4f59\u989d + \u4e0d\u826f\u62c5\u4fdd\u4f59\u989d = 0 Then\n         .[E12] = \"1-\u662f\"\n'        ElseIf \u4e4b\u524d\u6709\u8fc7\u4e0d\u826f\u501f\u6b3e\u548c\u4e0d\u826f\u62c5\u4fdd\u7684\u6570\u636e\u6216\u8868\u8ff0(\u73b0\u5728\u6ca1\u6709\u6240\u4ee5\u5ffd\u7565\u8fd9\u79cd\u60c5\u51b5) And (\u4e0d\u826f\u501f\u6b3e\u4f59\u989d + \u4e0d\u826f\u62c5\u4fdd\u4f59\u989d = 0) Then\n'       .[G12] = \"1-\u662f\"\n         ElseIf \u4e0d\u826f\u501f\u6b3e\u4f59\u989d > 0 Or \u4e0d\u826f\u62c5\u4fdd\u4f59\u989d > 0 Then\n         .[J12] = \"1-\u662f\"\n         End If\n        \n    End If\nEnd With ' \u8bc4\u5206\u91cc\u9762\u7684\u88684\u7684\uff0c\n\n    If UserForm1.\u66f4\u6b63\u683c\u5f0f = True Then\n    Call \u66f4\u6b63\u683c\u5f0f\u6253\u5f00\u6587\u4ef6\n    End If\n    \n    If UserForm1.\u8bc4\u5206\u5c0f\u9879 = True Then\n    Call \u88682\u8bc4\u5206\n    End If\n    \n    If UserForm1.\u767b\u8bb0\u9700\u63d0\u5206\u5c0f\u9879 = True Then\n    Call \u767b\u8bb0\u9700\u63d0\u5206\u5c0f\u9879\n    End If\n    \nEnd Sub\n<\/code><\/pre>\n\n\n\n
\u6a21\u57572\uff1a<\/p>\n\n\n\n
    'Dim gg As String   '\u5b9a\u4e49fld,gg\u4e3a\u53d8\u91cf'\u4e3a\u4e86\u540e\u7eed\u529f\u80fd\u5de5\u4f5c\uff0c\u5408\u5e76\u5230\u4e00\u8d77\u4e86\u5462\u3002\u4e0d\u5728\u672c\u6a21\u5757\u91cc\u9762\u58f0\u660e\u4e86\u3002\u6a21\u57571\u91cc\u9762\u53d8\u6210public\u53d8\u91cf\u4e86\u3002\n    'Dim file, filename As Variant'\u4e3a\u4e86\u540e\u7eed\u529f\u80fd\u5de5\u4f5c\uff0c\u5408\u5e76\u5230\u4e00\u8d77\u4e86\u5462\u3002\u4e0d\u5728\u672c\u6a21\u5757\u91cc\u9762\u58f0\u660e\u4e86\u3002\n    'Dim wb As Workbook ''\u4e3a\u4e86\u540e\u7eed\u529f\u80fd\u5de5\u4f5c\uff0c\u5408\u5e76\u5230\u4e00\u8d77\u4e86\u5462\u3002\u4e0d\u5728\u672c\u6a21\u5757\u91cc\u9762\u5173\u95ed\u4e86\u3002\n    Public hsave '\u5982\u679chsave\u6709\u503c\u4e86\uff0c\u8bf4\u660e\u7ecf\u8fc7\u4e86\u8fd9\u4e2a\u66f4\u6b63\u683c\u5f0f\u7684\u8fc7\u7a0b\uff0c\u90a3\u4e48xls\u4e00\u5b9a\u662fxlsx\u4e86\u3002\u540e\u9762\u518d\u7528\u5230\u6fc0\u6d3b\u5de5\u4f5c\u7c3f\u7684\u65f6\u5019\uff0c\u597d\u5f15\u7528\u6b63\u786e\u7684\u540d\u5b57\u3002\n    \n    \nSub \u66f4\u6b63\u683c\u5f0f\u5f00\u59cb()\nApplication.DisplayAlerts = False\nCall \u66f4\u6b63\u683c\u5f0f\u9009\u53d6\u6587\u4ef6\u5939\nEnd Sub\nSub \u66f4\u6b63\u683c\u5f0f\u9009\u53d6\u6587\u4ef6\u5939()\n    Dim shell As Variant\n    Set shell = CreateObject(\"Shell.Application\")\n    Set filePath = shell.BrowseForFolder(&O0, \"\u9009\u62e9\u6587\u4ef6\u5939\", &H1 + &H10, \"\")   '\u83b7\u53d6\u6587\u4ef6\u5939\u8def\u5f84\u5730\u5740\n    Set shell = Nothing\n  If filePath Is Nothing Then                 '\u68c0\u6d4b\u662f\u5426\u83b7\u5f97\u6709\u6548\u8def\u5f84\uff0c\u5982\u53d6\u6d88\u76f4\u63a5\u8df3\u51fa\u7a0b\u5e8f\n       Exit Sub\n    Else\n       gg = filePath.Items.Item.Path\n  End If\n    Call \u66f4\u6b63\u683c\u5f0fFileSearch(gg, \"*.xlsx\")\nEnd Sub\n\nPublic Sub \u66f4\u6b63\u683c\u5f0fFileSearch(Path As String, Target As String)\n\n    Dim FSO As Object, Folder As Variant\n    Set FSO = CreateObject(\"Scripting.FileSystemObject\")\n    For Each Folder In FSO.GetFolder(Path).SubFolders\n        Call \u66f4\u6b63\u683c\u5f0fFileSearch(Folder.Path, Target)\n    Next Folder\n    For Each file In FSO.GetFolder(Path).Files\n'    Dim filename '\u5df2\u7ecf\u5728\u58f0\u660e\u533a\u516c\u5f00\u53d8\u91cf\u4e86\u3002\n    filename = Right(file, Len(file) - Len(Path) - 1)\n    If (filename Like Target Or filename Like \"*.xls\") And Left(filename, 2) <> \"~$\" Then\n    Call \u66f4\u6b63\u683c\u5f0f\u6253\u5f00\u6587\u4ef6\n    End If\n    \n    Next file\n    \nEnd Sub\n\nPublic Sub \u66f4\u6b63\u683c\u5f0f\u6253\u5f00\u6587\u4ef6()\n    'Dim wb As Workbook '\u653e\u5230\u58f0\u660e\u533a\u4e86\uff0c\u516c\u5171\u53d8\u91cf\u3002\n    \n        'Set wb = Application.Workbooks.Open(file)\n        'If wb Is Nothing Then MsgBox (\"\u6587\u4ef6\u6253\u5f00\u5931\u8d25\uff0c\u8bf7\u68c0\u67e5\" & pth & fn & \"\u662f\u5426\u5b58\u5728\uff01\"): Exit Sub\n    \n    '\u6253\u5f00\u57fa\u7840\u6a21\u7248\n    Dim wb2 As Workbook\n    Set wb2 = Application.Workbooks.Open(ThisWorkbook.Path & \"\\\" & \"\u57fa\u7840\u6a21\u7248.xlsx\")\n     If wb2 Is Nothing Then MsgBox (\"\u6587\u4ef6\u6253\u5f00\u5931\u8d25\uff0c\u8bf7\u68c0\u67e5\" & pth & fn & \"\u662f\u5426\u5b58\u5728\uff01\"): Exit Sub\n    Call \u66f4\u6b63\u683c\u5f0f\u63d0\u53d6\u6570\u636e '\u5728\u6b64\u6dfb\u52a0\u64cd\u4f5c\u4ee3\u7801\n    'wb.Close True '\u5982\u679c\u65e0\u9700\u4fdd\u5b58\uff0c\u672c\u53c2\u6570\u7528false '\u79fb\u52a8\u5230\u4e86\u6700\u4e0b\u9762\u4fdd\u5b58\u4e4b\u540e\u4e86\u3002\u56e0\u4e3a\u4e0b\u9762call\u5d4c\u5957\u592a\u591a\uff0c\u4e0d\u505c\u8fd4\u56de\u7684\u8bdd\u65f6\u95f4\u957f\u3002\nEnd Sub\n'Sub \u590d\u5236\u57fa\u7840\u6a21\u7248\u8868\u5230\u7528\u6237\u4fe1\u606f\u7c3f()\n'    Windows(\"\u4fee\u6b63\u9519\u8bef\u683c\u5f0f0619.xlsm\").Activate '\u56e0\u4e3a\u57fa\u7840\u6a21\u7248\u653e\u5230\u4e86\u5e26\u5b8f\u7684\u5de5\u4f5c\u7c3f\u91cc\u9762\uff0c\u6240\u4ee5\uff0c\u53ef\u4ee5\u7528thisworkbook.activate.\u5982\u4e0b\uff1a\n'    ThisWorkbook.Activate\n'    Sheets(\"\u57fa\u7840\u6a21\u7248\").Select '\u63d0\u793a\u4e0b\u6807\u8d8a\u754c\uff0c\u53ef\u80fd\u52a0\u8f7d\u5b8f\u683c\u5f0f\u7684\u4e0d\u9002\u5408select\u548cactivate\u65b9\u6cd5\u5427\u3002\u4f46\u662f\u7b2c\u4e8c\u5929\u6d4b\u8bd5\uff0c\u53c8\u53ef\u4ee5\u4e86\u3002\n    'Application.CutCopyMode = False\n'    ThisWorkbook.Sheets(\"\u57fa\u7840\u6a21\u7248\").Copy Before:=Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\")\n'    Windows(filename).Activate\n'    Call \u63d0\u53d6\u6570\u636e\n    \n'End Sub\n\n\n\nSub \u66f4\u6b63\u683c\u5f0f\u63d0\u53d6\u6570\u636e()\n\nWorkbooks(filename).Activate '\u4e0d\u77e5\u9053\u54ea\u4e00\u4e2a\u5de5\u4f5c\u8868\u5728\u6fc0\u6d3b\u72b6\u6001\u3002\u56e0\u4e3a\u524d\u9762\u5df2\u7ecf\u6253\u5f00\u4e00\u4e2a\"\u57fa\u7840\u6a21\u677f.xlsx\"\uff0c\u5904\u4e8e\u6fc0\u6d3b\u72b6\u6001\u3002\n\nDim \u5e74\u9f84, \u5065\u5eb7\u72b6\u51b5, \u6587\u5316\u7a0b\u5ea6, \u7ecf\u8425\u5e74\u9650, \u65b0\u578b\u519c\u4e1a\u7ecf\u8425\u4e3b\u4f53, \u4f4f\u623f\u6027\u8d28, \u5a5a\u59fb\u72b6\u51b5, \u5bb6\u5ead\u52b3\u529b, \u9075\u7eaa\u5b88\u6cd5, \u5bb6\u5ead\u548c\u7766, \u6d89\u8bc9\u88ab\u6cd5\u9662\u6267\u884c\u60c5\u51b5, \u6c11\u95f4\u503a\u52a1\u53ca\u6c11\u95f4\u4e0d\u826f\u4fe1\u8a89, \u4e2a\u4eba\u94f6\u884c\u4fe1\u7528\u8bb0\u5f55, \u5bb6\u5ead\u5e74\u51c0\u6536\u5165, \u5bb6\u5ead\u51c0\u8d44\u4ea7, \u8d44\u4ea7\u8d1f\u503a\u7387, \u652f\u51fa\u6536\u5165\u6bd4\u7387, \u5bb6\u5ead\u8d44\u4ea7\u53ef\u53d8\u73b0\u91d1\u989d\nWith Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\")\n    \u5e74\u9f84 = .[G5]\n    \u5065\u5eb7\u72b6\u51b5 = .[I13]\n    \u6587\u5316\u7a0b\u5ea6 = .[B7]\n    \u7ecf\u8425\u5e74\u9650 = .[J7]\n    \u65b0\u578b\u519c\u4e1a\u7ecf\u8425\u4e3b\u4f53 = .[B8]\n    \u4f4f\u623f\u6027\u8d28 = .[I14]\n    \u5a5a\u59fb\u72b6\u51b5 = .[I6]\n    \u5bb6\u5ead\u52b3\u529b = .[G6]\n    \u9075\u7eaa\u5b88\u6cd5 = .[E10]\n    \u5bb6\u5ead\u548c\u7766 = .[B10]\n    \u6d89\u8bc9\u88ab\u6cd5\u9662\u6267\u884c\u60c5\u51b5 = .[I10]\n    \u6c11\u95f4\u503a\u52a1\u53ca\u6c11\u95f4\u4e0d\u826f\u4fe1\u8a89 = .[G10]\n'    \u4e2a\u4eba\u94f6\u884c\u4fe1\u7528\u8bb0\u5f55 =\n      \u6709\u501f\u6b3e\u4e14\u6709\u62c5\u4fdd\u65e0\u4e0d\u826f\u4fe1\u7528\u8bb0\u5f55 = .[B12]\n      \u65e0\u501f\u6b3e\u53ca\u62c5\u4fdd\u8bb0\u5f55\u4e14\u65e0\u4e0d\u826f\u4fe1\u7528\u8bb0\u5f55 = .[E12]\n      \u66fe\u6709\u4e0d\u826f\u501f\u6b3e\u53ca\u62c5\u4fdd\u4f46\u5df2\u6e05\u507f = .[G12]\n      \u4ecd\u6709\u4e0d\u826f\u501f\u6b3e\u6216\u4e0d\u826f\u4fe1\u7528\u8bb0\u5f55 = .[J12]\n    \u5bb6\u5ead\u5e74\u51c0\u6536\u5165 = .[J57]\n    \u5bb6\u5ead\u51c0\u8d44\u4ea7 = .[F39]\n    \u8d44\u4ea7\u8d1f\u503a\u7387 = .[H39]\n    \u652f\u51fa\u6536\u5165\u6bd4\u7387 = .[L57]\n    \u5bb6\u5ead\u8d44\u4ea7\u53ef\u53d8\u73b0\u91d1\u989d = .[K36]\n    \nDim \u5efa\u6863\u5355\u4f4d, \u6751\u7ec4\u7f16\u7801, \u59d3\u540d, \u6027\u522b, \u79fb\u52a8\u7535\u8bdd, \u8eab\u4efd\u8bc1\u53f7, \u5bb6\u5ead\u4eba\u53e3, \u4ece\u4e8b\u884c\u4e1a\u6216\u5de5\u4f5c\u5355\u4f4d, \u5c45\u4f4f\u5730\u5740, \u6237\u7c4d\u5730\u5740, \u662f\u5426\u8d2b\u82e6\u6237, \u672c\u884c\u501f\u6b3e, \u672c\u884c\u62c5\u4fdd, \u4e0d\u826f\u501f\u6b3e\u4f59\u989d, \u4e0d\u826f\u8d37\u6b3e\u4f59\u989d, \u4ed6\u884c\u501f\u6b3e, \u4ed6\u884c\u62c5\u4fdd, \u793e\u4f1a\u501f\u6b3e, \u5176\u4ed6\u8d1f\u503a, \u5bb6\u5ead\u7ecf\u8425\u6027\u652f\u51fa, \u751f\u6d3b\u652f\u51fa, \u5176\u4ed6\u652f\u51fa\n'\u4e00\u3001\u6237\u4e3b\u57fa\u672c\u60c5\u51b5\n\u5efa\u6863\u5355\u4f4d = .[B3]\n\u6751\u7ec4\u7f16\u7801 = .[H3]\n\u59d3\u540d = .[B5]\n\u6027\u522b = .[E5]\n\u79fb\u52a8\u7535\u8bdd = .[I5]\n\u8eab\u4efd\u8bc1\u53f7 = .[B6]\n\u5bb6\u5ead\u4eba\u53e3 = .[E6]\n\u4ece\u4e8b\u884c\u4e1a\u6216\u5de5\u4f5c\u5355\u4f4d = .[H7]\n\u5c45\u4f4f\u5730\u5740 = .[E13]\n\u6237\u7c4d\u5730\u5740 = .[E14]\n\u662f\u5426\u8d2b\u82e6\u6237 = .[L13]\n'\u4e09\u3001\u5bb6\u5ead\u8d1f\u503a\u53ca\u5bf9\u5916\u62c5\u4fdd\u60c5\u51b5\n\u672c\u884c\u501f\u6b3e = .[B38]\n\u672c\u884c\u62c5\u4fdd = .[D38]\n\u4e0d\u826f\u501f\u6b3e\u4f59\u989d = .[J39]\n\u4e0d\u826f\u8d37\u6b3e\u4f59\u989d = .[L39]\n\n\u4ed6\u884c\u501f\u6b3e = .[F38]\n\u4ed6\u884c\u62c5\u4fdd = .[H38]\n\u793e\u4f1a\u501f\u6b3e = .[J38]\n\u5176\u4ed6\u8d1f\u503a = .[L38]\n\n'\u56db\u3001\u4e3b\u8981\u7ecf\u8425\u53ca\u6536\u652f\u60c5\u51b5\n\u5bb6\u5ead\u7ecf\u8425\u6027\u652f\u51fa = .[B57]\n\u751f\u6d3b\u652f\u51fa = .[D57]\n\u5176\u4ed6\u652f\u51fa = .[F57]\n\nDim \u7cae\u98df, \u68c9\u82b1, \u6cb9\u6599, \u82f9\u679c, \u6cb9\u6843, \u674f, \u6a31\u6843, \u897f\u74dc, \u8461\u8404, \u7315\u7334\u6843, \u67a3, \u77f3\u69b4, \u68a8, \u836f\u6750, \u82b1\u5349, \u8336\u53f6, \u70e4\u70df, \u852c\u83dc, \u98df\u7528\u83cc, \u82d7\u6728, \u79cd\u690d\u4e1a\u5176\u4ed6\nDim \u732a, \u7f8a, \u725b, \u9e21, \u9e2d, \u9e45, \u9c7c, \u5154, \u8695\u6851, \u517b\u6b96\u4e1a\u5176\u4ed6\nDim \u7cae\u68c9\u6cb9\u52a0\u5de5, \u8336\u53f6\u52a0\u5de5, \u98df\u54c1\u52a0\u5de5, \u836f\u6750\u52a0\u5de5, \u519c\u4ea7\u54c1\u52a0\u5de5, \u52a0\u5de5\u4e1a\u5176\u4ed6\nDim \u5316\u80a5, \u519c\u836f, \u79cd\u5b50, \u519c\u673a\u5177, \u519c\u4e1a\u670d\u52a1\u4e1a\u5176\u4ed6\nDim \u6279\u53d1\u96f6\u552e, \u9910\u996e, \u8fd0\u8f93, \u533b\u836f\u548c\u533b\u7597, \u519c\u526f\u4ea7\u54c1\u6536\u8d2d, \u5546\u4e1a\u670d\u52a1\u4e1a\u5176\u4ed6\nDim \u957f\u671f\u96c7\u7528, \u4e34\u65f6\u6253\u5de5\nDim \u5de5\u8d44\u6536\u5165, \u7ecf\u8425\u6536\u5165, \u52b3\u52a1\u6536\u5165, \u5176\u4ed6\u6536\u5165 '\u5bb6\u5ead\u5176\u4ed6\u6210\u5458\u6536\u5165\n\n'\u79cd\u690d\u4e1a\n\u7cae\u98df = .[Q40]\n\u68c9\u82b1 = .[R40]\n\u6cb9\u6599 = .[S40]\n\u82f9\u679c = .[T40]\n\u6cb9\u6843 = .[U40]\n\u674f = .[V40]\n\u6a31\u6843 = .[W40]\n\u897f\u74dc = .[S40]\n\u8461\u8404 = .[Y40]\n\u7315\u7334\u6843 = .[Z40]\n\u67a3 = .[AB40]\n\u77f3\u69b4 = .[AA40]\n\u68a8 = .[AC40]\n\u836f\u6750 = .[AD40]\n\u82b1\u5349 = .[AE40]\n\u8336\u53f6 = .[AF40]\n\u70e4\u70df = .[AG40]\n\u852c\u83dc = .[AH40]\n\u98df\u7528\u83cc = .[AI40]\n\u82d7\u6728 = .[AJ40]\n\u79cd\u690d\u4e1a\u5176\u4ed6 = .[AK40]\n\n'\u517b\u6b96\u4e1a=.\n\u732a = .[Q41]\n\u7f8a = .[R41]\n\u725b = .[S41]\n\u9e21 = .[T41]\n\u9e2d = .[U41]\n\u9e45 = .[V41]\n\u9c7c = .[W41]\n\u5154 = .[X41]\n\u8695\u6851 = .[Y41]\n\u517b\u6b96\u4e1a\u5176\u4ed6 = .[Z41]\n\n'\u52a0\u5de5\u4e1a=.\n\u7cae\u68c9\u6cb9\u52a0\u5de5 = .[Q42]\n\u8336\u53f6\u52a0\u5de5 = .[R42]\n\u98df\u54c1\u52a0\u5de5 = .[S42]\n\u836f\u6750\u52a0\u5de5 = .[T42]\n\u519c\u4ea7\u54c1\u52a0\u5de5 = .[U42]\n\u52a0\u5de5\u4e1a\u5176\u4ed6 = .[V42]\n\n'\u519c\u4e1a\u670d\u52a1\u4e1a=.\n\u5316\u80a5 = .[Q43]\n\u519c\u836f = .[R43]\n\u79cd\u5b50 = .[S43]\n\u519c\u673a\u5177 = .[T43]\n\u519c\u4e1a\u670d\u52a1\u4e1a\u5176\u4ed6 = .[U43]\n\n'\u5546\u4e1a\u670d\u52a1\u4e1a=.\n\u6279\u53d1\u96f6\u552e = .[Q44]\n\u9910\u996e = .[R44]\n\u8fd0\u8f93 = .[S44]\n\u533b\u836f\u548c\u533b\u7597 = .[T44]\n\u519c\u526f\u4ea7\u54c1\u6536\u8d2d = .[U44]\n\u5546\u4e1a\u670d\u52a1\u4e1a\u5176\u4ed6 = .[V44]\n\n'\u52b3\u52a1\u4e1a\n\u957f\u671f\u96c7\u7528 = .[Q45]\n\u4e34\u65f6\u6253\u5de5 = .[R45]\n\n'\u5bb6\u5ead\u5176\u4ed6\u6210\u5458\u6536\u5165=.\n\u5de5\u8d44\u6536\u5165 = .[Q46]\n\u7ecf\u8425\u6536\u5165 = .[R46]\n\u52b3\u52a1\u6536\u5165 = .[S46]\n\u5176\u4ed6\u6536\u5165 = .[T46]\n\n\u5bcc\u79e6\u5361 = .[Q47]\n\u94bb\u77f3\u5361 = .[R47]\n\u91d1\u5361 = .[S47]\n\u7f51\u94f6\u53ca\u624b\u673a\u94f6\u884c = .[T47]\n\u8d44\u91d1\u9700\u6c42\u5176\u4ed6 = .[U47]\n\u8d44\u91d1\u9700\u6c42\u65e0 = .[V47]\n\nDim \u5efa\u6863\u4eba, \u590d\u6838\u4eba, \u5efa\u6863\u65e5\u671f\n\u5efa\u6863\u4eba = .[B62]\n\u590d\u6838\u4eba = .[F62]\n\u5efa\u6863\u65e5\u671f = .[J62]\n\nEnd With\n\nWorkbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Activate\n\nWith Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\")\n.[G5] = \u5e74\u9f84\n.[I13] = \u5065\u5eb7\u72b6\u51b5\n.[B7] = \u6587\u5316\u7a0b\u5ea6\n.[J7] = \u7ecf\u8425\u5e74\u9650\n.[B8] = \u65b0\u578b\u519c\u4e1a\u7ecf\u8425\u4e3b\u4f53\n.[I14] = \u4f4f\u623f\u6027\u8d28\n.[I6] = \u5a5a\u59fb\u72b6\u51b5\n.[G6] = \u5bb6\u5ead\u52b3\u529b\n.[E10] = \u9075\u7eaa\u5b88\u6cd5\n.[B10] = \u5bb6\u5ead\u548c\u7766\n.[I10] = \u6d89\u8bc9\u88ab\u6cd5\u9662\u6267\u884c\u60c5\u51b5\n.[G10] = \u6c11\u95f4\u503a\u52a1\u53ca\u6c11\u95f4\u4e0d\u826f\u4fe1\u8a89\n'=\u4e2a\u4eba\u94f6\u884c\u4fe1\u7528\u8bb0\u5f55\n.[B12] = \u6709\u501f\u6b3e\u4e14\u6709\u62c5\u4fdd\u65e0\u4e0d\u826f\u4fe1\u7528\u8bb0\u5f55\n.[E12] = \u65e0\u501f\u6b3e\u53ca\u62c5\u4fdd\u8bb0\u5f55\u4e14\u65e0\u4e0d\u826f\u4fe1\u7528\u8bb0\u5f55\n.[G12] = \u66fe\u6709\u4e0d\u826f\u501f\u6b3e\u53ca\u62c5\u4fdd\u4f46\u5df2\u6e05\u507f\n.[J12] = \u4ecd\u6709\u4e0d\u826f\u501f\u6b3e\u6216\u4e0d\u826f\u4fe1\u7528\u8bb0\u5f55\n'\u6237\u4e3b\u57fa\u672c\u4fe1\u606f\uff082\uff09\n.[B3] = \u5efa\u6863\u5355\u4f4d\n.[H3] = \u6751\u7ec4\u7f16\u7801\n.[B5] = \u59d3\u540d\n.[E5] = \u6027\u522b\n.[I5] = \u79fb\u52a8\u7535\u8bdd\n.[B6] = \u8eab\u4efd\u8bc1\u53f7\n.[E6] = \u5bb6\u5ead\u4eba\u53e3\n.[H7] = \u4ece\u4e8b\u884c\u4e1a\u6216\u5de5\u4f5c\u5355\u4f4d\n.[E13] = \u5c45\u4f4f\u5730\u5740\n.[E14] = \u6237\u7c4d\u5730\u5740\n.[L13] = \u662f\u5426\u8d2b\u82e6\u6237\n\n.[B38] = \u672c\u884c\u501f\u6b3e\n.[D38] = \u672c\u884c\u62c5\u4fdd\n.[J39] = \u4e0d\u826f\u501f\u6b3e\u4f59\u989d\n.[L39] = \u4e0d\u826f\u8d37\u6b3e\u4f59\u989d\n\n.[F38] = \u4ed6\u884c\u501f\u6b3e\n.[H38] = \u4ed6\u884c\u62c5\u4fdd\n.[J38] = \u793e\u4f1a\u501f\u6b3e\n.[L38] = \u5176\u4ed6\u8d1f\u503a\n\n.[B57] = \u5bb6\u5ead\u7ecf\u8425\u6027\u652f\u51fa\n.[D57] = \u751f\u6d3b\u652f\u51fa\n.[F57] = \u5176\u4ed6\u652f\u51fa\n'\u5bb6\u5ead\u7ecf\u8425\u90e8\u5206\n.[Q40] = \u7cae\u98df\n.[R40] = \u68c9\u82b1\n.[S40] = \u6cb9\u6599\n.[T40] = \u82f9\u679c\n.[U40] = \u6cb9\u6843\n.[V40] = \u674f\n.[W40] = \u6a31\u6843\n.[S40] = \u897f\u74dc\n.[Y40] = \u8461\u8404\n.[Z40] = \u7315\u7334\u6843\n.[AB40] = \u67a3\n.[AA40] = \u77f3\u69b4\n.[AC40] = \u68a8\n.[AD40] = \u836f\u6750\n.[AE40] = \u82b1\u5349\n.[AF40] = \u8336\u53f6\n.[AG40] = \u70e4\u70df\n.[AH40] = \u852c\u83dc\n.[AI40] = \u98df\u7528\u83cc\n.[AJ40] = \u82d7\u6728\n.[AK40] = \u79cd\u690d\u4e1a\u5176\u4ed6\n\n'.='\u517b\u6b96\u4e1a\n.[Q41] = \u732a\n.[R41] = \u7f8a\n.[S41] = \u725b\n.[T41] = \u9e21\n.[U41] = \u9e2d\n.[V41] = \u9e45\n.[W41] = \u9c7c\n.[X41] = \u5154\n.[Y41] = \u8695\u6851\n.[Z41] = \u517b\u6b96\u4e1a\u5176\u4ed6\n\n'.='\u52a0\u5de5\u4e1a\n.[Q42] = \u7cae\u68c9\u6cb9\u52a0\u5de5\n.[R42] = \u8336\u53f6\u52a0\u5de5\n.[S42] = \u98df\u54c1\u52a0\u5de5\n.[T42] = \u836f\u6750\u52a0\u5de5\n.[U42] = \u519c\u4ea7\u54c1\u52a0\u5de5\n.[V42] = \u52a0\u5de5\u4e1a\u5176\u4ed6\n\n'.='\u519c\u4e1a\u670d\u52a1\u4e1a\n.[Q43] = \u5316\u80a5\n.[R43] = \u519c\u836f\n.[S43] = \u79cd\u5b50\n.[T43] = \u519c\u673a\u5177\n.[U43] = \u519c\u4e1a\u670d\u52a1\u4e1a\u5176\u4ed6\n\n'.='\u5546\u4e1a\u670d\u52a1\u4e1a\n.[Q44] = \u6279\u53d1\u96f6\u552e\n.[R44] = \u9910\u996e\n.[S44] = \u8fd0\u8f93\n.[T44] = \u533b\u836f\u548c\u533b\u7597\n.[U44] = \u519c\u526f\u4ea7\u54c1\u6536\u8d2d\n.[V44] = \u5546\u4e1a\u670d\u52a1\u4e1a\u5176\u4ed6\n\n'.='\u52b3\u52a1\u4e1a\n.[Q45] = \u957f\u671f\u96c7\u7528\n.[R45] = \u4e34\u65f6\u6253\u5de5\n\n'.='\u5bb6\u5ead\u5176\u4ed6\u6210\u5458\u6536\u5165\n.[Q46] = \u5de5\u8d44\u6536\u5165\n.[R46] = \u7ecf\u8425\u6536\u5165\n.[S46] = \u52b3\u52a1\u6536\u5165\n.[T46] = \u5176\u4ed6\u6536\u5165\n\n.[Q47] = \u5bcc\u79e6\u5361\n.[R47] = \u94bb\u77f3\u5361\n.[S47] = \u91d1\u5361\n.[T47] = \u7f51\u94f6\u53ca\u624b\u673a\u94f6\u884c\n.[U47] = \u8d44\u91d1\u9700\u6c42\u5176\u4ed6\n.[V47] = \u8d44\u91d1\u9700\u6c42\u65e0\n\n.[B62] = \u5efa\u6863\u4eba\n.[F62] = \u590d\u6838\u4eba\n.[J62] = \u5efa\u6863\u65e5\u671f\n\n\nEnd With\n\nCall \u66f4\u6b63\u683c\u5f0f\u590d\u5236\u5355\u5143\u683c\nEnd Sub\nSub \u66f4\u6b63\u683c\u5f0f\u590d\u5236\u5355\u5143\u683c()\n' \u590d\u5236\u5355\u5143\u683c Macro\n'\n\n'\n     \n'   '====\u4e0b\u9762\u590d\u5236\u7167\u7247=======\u597d\u50cf\u4e0d\u80fd\u7528pastespecial\u90a3\u6837\u7684\u8bed\u53e5\uff0c\u53ef\u80fd\u662f\u7167\u7247\u5bf9\u8c61\u5728\u3002\u63d0\u793a\uff0c\u4e0d\u80fd\u5bf9\u5408\u5e76\u5355\u5143\u683c\u505a\u90e8\u5206\u66f4\u6539\u3002\n    'Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Activate\n    'Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"K4:L10\").Select 'windows(filename).sheets\u597d\u50cf\u6ca1\u6709\u8fd9\u4e2a\u5bf9\u8c61sheets\uff0c\u6240\u4ee5\u6362\u4e86workbooks\u5bf9\u8c61\uff0c\u7a97\u53e3\u4e0d\u7528\u6765\u56de\u5207\u6362\u3002\n   ' Selection.Copy\n    'Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"K4\").PasteSpecial (xlPasteAll)\n   ' Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"K4\").Select\n    \n    'Selection.Paste\n   ' Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"K4:L10\").Select\n    'ActiveSheet.Paste\n   ' Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"K4:L10\").Select\n    'ActiveSheet.Paste\n    \n    Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Activate\n    Range(\"K4:L10\").Select 'windows(filename).sheets\u597d\u50cf\u6ca1\u6709\u8fd9\u4e2a\u5bf9\u8c61sheets\uff0c\u6240\u4ee5\u6362\u4e86workbooks\u5bf9\u8c61\uff0c\u7a97\u53e3\u4e0d\u7528\u6765\u56de\u5207\u6362\u3002\n    Selection.Copy\n    Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Activate\n    Range(\"K4:L10\").Select\n    ActiveSheet.Paste\n    Application.CutCopyMode = False '\u53d1\u73b0\u56fe\u7247\u590d\u5236\u4e86\u4e24\u5f20\uff0c\u6709\u4e00\u5f20\u662f\u4e4b\u524d\u7684\u505a\u5b9e\u9a8c\u7684\u6587\u4ef6\u91cc\u9762\u7684\u52a0\u8fd9\u53e5\uff0c\u8bd5\u8bd5\u3002\u53d1\u73b0\u539f\u56e0\u4e0d\u662f\u8fd9\u4e2a\u3002\n        \n    'Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"K4:L10\").Copy '\u8fd9\u4e24\u53e5\u63d0\u53d6\u4e0d\u5230\u7167\u7247\u3002\u4f30\u8ba1\u4e0d\u80fd\u7528\u7f16\u7a0b\u7684\u65b9\u5f0f\u53ea\u80fd\u63d0\u53d6\u5355\u5143\u683c\u7684\u503c\uff08\u7f16\u7a0b\u5e94\u8be5\u662fshapes\u5bf9\u8c61\uff09\uff0c\u53ea\u80fd\u662f\u6a21\u62df\u7684\u65b9\u5f0f\u3002\n    'Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"K4:L10\").PasteSpecial (xlPasteValuesAndNumberFormats)\n    '========\u4e0b\u9762\u590d\u5236\u5355\u5143\u683c\u533a\u57df=====\n    Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"A16:L25\").Copy\n    Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"A16:L25\").PasteSpecial (xlPasteValuesAndNumberFormats)\n    '\u8fd9\u4e2a\u5982\u679c\u662f xlpastevalue\uff0c\u5c31\u4f1a\u63d0\u9192\u5355\u5143\u683c\u5927\u5c0f\u76f8\u540c\u3002\u8c03\u8bd5\u65f6\u6539\u6210xlPasteValuesAndNumberFormats\uff0c\u8fd8\u662f\u63d0\u9192\uff0c\u91cd\u65b0\u8fd0\u884c\u5c31\u597d\u4e86\u3002\u6a21\u62df\u5199\u6cd5\u7684\u8bed\u53e5\uff0c\u4e5f\u662f\u4f1a\u9047\u5230\u8fd9\u4e2a\u91cd\u65b0\u8fd0\u884c\u5c31\u597d\u4e86\u3002\n    '\u6709\u65f6\u95f4\uff0c\u597d\u597d\u6bd4\u8f83\u4e0b\u90a3\u4e2a\u7c98\u8d34\u9009\u9879\u7684\u6548\u679c\uff0c\u600e\u4e48\u7eaf\u6570\u503c\u7684\u65f6\u5019\u603b\u662f\u63d0\u9192\u5462\uff1f\n    '------------------------\u4e0b\u9762\u662f\u6a21\u62df\u5199\u6cd5\uff0c\u4ece\u5f55\u5236\u5b8f\u5f97\u5230-------------------------------------\n    'Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Activate '\u5bb6\u5ead\u6210\u5458\n    'Range(\"A16:L25\").Select 'windows(filename).sheets\u597d\u50cf\u6ca1\u6709\u8fd9\u4e2a\u5bf9\u8c61sheets\uff0c\u6240\u4ee5\u6362\u4e86workbooks\u5bf9\u8c61\uff0c\u7a97\u53e3\u4e0d\u7528\u6765\u56de\u5207\u6362\u3002\n    'Selection.Copy\n    'Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Activate\n    'Range(\"A16\").Select\n    'Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False\n    'Application.CutCopyMode = False\n    '------------------------\u4e0a\u9762\u662f\u6a21\u62df\u5199\u6cd5\uff0c\u4ece\u5f55\u5236\u5b8f\u5f97\u5230-------------------------------------\n    'Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"A28:L34\").Copy '\u5bb6\u5ead\u8d44\u4ea7\u90e8\u5206\n    'Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"A28:L34\").PasteSpecial (xlPasteValuesAndNumberFormats)\n    \n    '==================\u5bb6\u5ead\u8d44\u4ea7\u65b0\u65b9\u5f0f-\u907f\u514d\u4fdd\u7559\u65e0\u683c\u5f0f\u53ca\u8c03\u6574\u9879\u76ee\u987a\u5e8f==========================\n    Dim i\n    For i = 28 To 34\n       If Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"A\" & i) = \"01-\u623f\u4ea7\" Then\n       Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"A\" & i & \":\" & \"G\" & i).Copy '\u5bb6\u5ead\u8d44\u4ea7\u90e8\u5206\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"A28:G28\").PasteSpecial (xlPasteValuesAndNumberFormats)\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"H28\") = Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"H\" & i)\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"J28\") = Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"J\" & i)\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"K28\") = Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"K\" & i)\n       ElseIf Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"A\" & i) = \"02-\u4ea4\u901a\u5de5\u5177\" Then\n       Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"A\" & i & \":\" & \"G\" & i).Copy '\u5bb6\u5ead\u8d44\u4ea7\u90e8\u5206\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"A29:G29\").PasteSpecial (xlPasteValuesAndNumberFormats) '\u8fd9\u4e2a\u8bed\u53e5\u662f\u4e0d\u662f\u4e0d\u80fd\u53ea\u6307\u5b9a\u7b2c\u4e00\u4e2a\u5355\u5143\u683c\u7c98\u8d34\u5462\uff1f\u8fd8\u662f\u8ddf\u540e\u9762\u7684\u7c98\u8d34\u683c\u5f0f\u6709\u5173\u3002\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"H29\") = Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"H\" & i)\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"J29\") = Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"J\" & i)\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"K29\") = Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"K\" & i)\n       ElseIf Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"A\" & i) = \"03-\u5927\u578b\u519c\u673a\u5177\" Then\n       Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"A\" & i & \":\" & \"G\" & i).Copy '\u5bb6\u5ead\u8d44\u4ea7\u90e8\u5206\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"A30:G30\").PasteSpecial (xlPasteValuesAndNumberFormats)\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"H30\") = Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"H\" & i)\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"J30\") = Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"J\" & i)\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"K30\") = Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"K\" & i)\n       ElseIf Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"A\" & i) = \"04-\u7272\u755c\" Then\n       Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"A\" & i & \":\" & \"G\" & i).Copy '\u5bb6\u5ead\u8d44\u4ea7\u90e8\u5206\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"A31:G31\").PasteSpecial (xlPasteValuesAndNumberFormats)\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"H28\") = Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"H\" & i)\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"J28\") = Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"J\" & i)\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"K28\") = Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"K\" & i)\n       ElseIf Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"A\" & i) = \"05-\u571f\u5730\" Then\n       Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"A\" & i & \":\" & \"G\" & i).Copy '\u5bb6\u5ead\u8d44\u4ea7\u90e8\u5206\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"A32:G32\").PasteSpecial (xlPasteValuesAndNumberFormats)\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"H32\") = Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"H\" & i)\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"J32\") = Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"J\" & i)\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"K32\") = Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"K\" & i)\n       ElseIf Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"A\" & i) = \"06-\u6797\u6743\" Then\n       Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"A\" & i & \":\" & \"G\" & i).Copy '\u5bb6\u5ead\u8d44\u4ea7\u90e8\u5206\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"A33:G33\").PasteSpecial (xlPasteValuesAndNumberFormats)\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"H33\") = Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"H\" & i)\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"J33\") = Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"J\" & i)\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"K33\") = Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"K\" & i)\n       ElseIf Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"A\" & i) = \"07-\u5176\u4ed6\u8d44\u4ea7\" Then\n       Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"A\" & i & \":\" & \"G\" & i).Copy '\u5bb6\u5ead\u8d44\u4ea7\u90e8\u5206\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"A34:G34\").PasteSpecial (xlPasteValuesAndNumberFormats)\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"H34\") = Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"H\" & i)\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"J34\") = Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"J\" & i)\n       Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"K34\") = Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"K\" & i)\n       End If\n       \n       Next\n       \n    \n    Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"I42:L53\").Copy '\u7ecf\u8425\u53f3\u8fb9\u90e8\u5206\n    Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"I42:L53\").PasteSpecial (xlPasteValuesAndNumberFormats)\n    \n    Workbooks(filename).Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"C54:L55\").Copy '\u7ecf\u8425\u6700\u540e\u4e00\u884c\n    Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").Range(\"C54:L55\").PasteSpecial (xlPasteValuesAndNumberFormats)\n   \n    Call \u66f4\u6b63\u683c\u5f0f\u4fdd\u5b58\u4fee\u6539\u540e\u7684\u7528\u6237\u5de5\u4f5c\u7c3f\nEnd Sub\n\nSub \u66f4\u6b63\u683c\u5f0f\u4fdd\u5b58\u4fee\u6539\u540e\u7684\u7528\u6237\u5de5\u4f5c\u7c3f()\nwb.Close True '\u5148\u5173\u6389\u539f\u6587\u4ef6\uff0c\u518d\u4fdd\u5b58\uff0c\u4e0d\u7136\u63d0\u793a\u4e0d\u80fd\u4fdd\u5b58\u4e3a\u5df2\u7ecf\u6253\u5f00\u7684\u6587\u4ef6\u3002\u5982\u679c\u4ece xls\u5230xlsx\u7684\u8bdd\uff0c\u5c31\u4e0d\u7528\u4e86\u3002\u4e0b\u9762\u4e5f\u4e0d\u7528kill\u4e86\u3002\n        filenew = file 'kill\u4e86\uff0c\u5c31\u5f97\u5148\u7559\u4e0b\u3002\u4e0d\u77e5\u9053kill\u6587\u4ef6\uff0c\u4e3a\u4ec0\u4e48\u8fd8\u6709\u8fd9\u4e2a\u529f\u80fd\u3002\n        Kill file\n    If filename Like \"*.xls\" Then\n    filenew = filenew & \"x\"\n     'MsgBox file  '\u8fd9\u91cc\u7684\u8bdd\u5df2\u7ecf\u662f\u627e\u4e0d\u5230\u6587\u4ef6\u4e86\u3002\u63d0\u793a\u4e3a\u4ec0\u4e48\u8bf4\u662f\u6587\u4ef6\u5462\uff1f\n    file = filenew '\u8fd9\u91cc\u5c45\u7136\u8fd8\u53ef\u4ee5\u8d4b\u503c\u3002\u800c\u4e14\u56e0\u4e3a\u58f0\u660e\u7684\u65f6\u5019\u662fpublic\uff0c\u6240\u4ee5\u8bc4\u5206\u5728\u6a21\u57574\u91cc\u9762\u4e5f\u5feb\u6210\u7528\u5230\u3002\u5c31\u662f\u767b\u8bb0\u9700\u63d0\u5206\u90a3\u91cc\u52a0\u94fe\u63a5\u7684\u5730\u65b9\uff0c\u7528\u5230file\u3002\n    'MsgBox file\n    filename = filename & \"x\" '\u524d\u9762\u7ecf\u8fc7\u4e86\u66f4\u6b63\u683c\u5f0f\u8fc7\u7a0b\u7684\u8bdd\uff0c\u5c31\u6709\u503c\u4e86\u3002\u90a3\u4e48\u5982\u679c\u662fxls\u7684\u683c\u5f0f\uff0c\u5c31\u9700\u8981\u5728\u6587\u4ef6\u540d\u4e0a\u52a0x\u624d\u662f\u73b0\u5728\u7684\u6587\u4ef6\u540d\u3002\u8fd9\u91cc\u8981\u628a\u6700\u65b0\u7684file\u8d4b\u503c\uff0c\u4ee5\u540e\u597d\u7528\u3002\n    End If\n    \n    'ActiveWorkbook.SaveAs filename:=filenew & \"x\", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False\n    'Else\n    Workbooks(\"\u57fa\u7840\u6a21\u7248.xlsx\").SaveAs filename:=filenew, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False\n    'End If\n    'ActiveWorkbook.Close True '\u4e3a\u4e86\u540e\u7eed\u529f\u80fd\u5de5\u4f5c\uff0c\u5408\u5e76\u5230\u4e00\u8d77\u4e86\u5462\u3002\n    hsave = 1 '\u5982\u679chsave\u6709\u503c\u4e86\uff0c\u8bf4\u660e\u7ecf\u8fc7\u4e86\u8fd9\u4e2a\u66f4\u6b63\u683c\u5f0f\u7684\u8fc7\u7a0b\uff0c\u90a3\u4e48xls\u4e00\u5b9a\u662fxlsx\u4e86\u3002\u540e\u9762\u518d\u7528\u5230\u6fc0\u6d3b\u5de5\u4f5c\u7c3f\u7684\u65f6\u5019\uff0c\u597d\u5f15\u7528\u6b63\u786e\u7684\u540d\u5b57\u3002\nEnd Sub\n\n\n<\/code><\/pre>\n\n\n\n
\u6a21\u57573\uff1a<\/p>\n\n\n\n
Sub \u5feb\u6377\u65b9\u5f0f\u542f\u52a8()\nUnload UserForm1 '\u4e0d\u52a0\u8fd9\u53e5\uff0c\u7a97\u53e3.hide\u53ea\u662f\u9690\u85cf\u4e86\u3002\u590d\u9009\u6846\u7684\u52fe\u8fd8\u5728\u3002\u9690\u85cf\u4e86\uff0c\u5c31\u53ef\u4ee5\u53bb\u6253\u5f00\u5176\u4ed6\u7684\u5de5\u4f5c\u8868\u4e86\u3002\nUserForm1.Show\nEnd Sub\n\n\nSub \u586b\u624b\u673a\u53f7\u5f00\u59cb()\n\nEnd Sub\n\nSub \u8bb0\u5f55\u9700\u8981\u53e6\u5916\u52a0\u5206\u7684()\n\n If 18 <= Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").[G5] <= 60 And Sheets(\"\u519c\u6237\u4fe1\u7528\u7b49\u7ea7\u8bc4\u4f30-\u88682\").[F27] < 60 Then\n Workbooks(\"\u9700\u8981\u52a0\u5206\u519c\u6237\u8bb0\u5f55.xlsx\").Worksheets(1).[A1] = Active.Workbooks.Path\n End If\n \nEnd Sub\n\nSub \u5f00\u59cb()\n    UserForm1.Show\n    Dim shell As Variant\n    Set shell = CreateObject(\"Shell.Application\")\n    Set filePath = shell.BrowseForFolder(&O0, \"\u9009\u62e9\u6587\u4ef6\u5939\", &H1 + &H10, \"\")   '\u83b7\u53d6\u6587\u4ef6\u5939\u8def\u5f84\u5730\u5740\n    Set shell = Nothing\n  If filePath Is Nothing Then                 '\u68c0\u6d4b\u662f\u5426\u83b7\u5f97\u6709\u6548\u8def\u5f84\uff0c\u5982\u53d6\u6d88\u76f4\u63a5\u8df3\u51fa\u7a0b\u5e8f\n       Exit Sub\n    Else\n       gg = filePath.Items.Item.Path\n  End If\n    Call FileSearch(gg, \"*.xls?\")\nEnd Sub\n\nSub FileSearch(Path As String, Target As String)\n\n    Dim FSO As Object, Folder As Variant\n    Set FSO = CreateObject(\"Scripting.FileSystemObject\")\n    For Each Folder In FSO.GetFolder(Path).SubFolders\n        Call FileSearch(Folder.Path, Target)\n    Next Folder\n    For Each file In FSO.GetFolder(Path).Files\n    Dim filename\n    filename = Right(file, Len(file) - Len(Path) - 1)\n    If filename Like Target And Left(filename, 2) <> \"~$\" Then\n    Call \u6253\u5f00\u6587\u4ef6\n    End If\n    Next file\n    \nEnd Sub\n\nPublic Sub \u6253\u5f00\u6587\u4ef6()\n    Dim wb As Workbook\n    Set wb = Application.Workbooks.Open(file)\n    If wb Is Nothing Then MsgBox (\"\u6587\u4ef6\u6253\u5f00\u5931\u8d25\uff0c\u8bf7\u68c0\u67e5\" & pth & fn & \"\u662f\u5426\u5b58\u5728\uff01\"): Exit Sub\n    Call \u8bc4\u5206 '\u5728\u6b64\u6dfb\u52a0\u64cd\u4f5c\u4ee3\u7801\n    wb.Close True '\u5982\u679c\u65e0\u9700\u4fdd\u5b58\uff0c\u672c\u53c2\u6570\u7528false\nEnd Sub\n\n<\/code><\/pre>\n\n\n\n
\u6a21\u57574\uff1a<\/p>\n\n\n\n
Sub \u88682\u8bc4\u5206()\n    '=============\u4e0b\u9762\u90e8\u5206\u662f\u88682\u8bc4\u5206============\n\nWorkbooks(filename).Activate '\u4e0d\u77e5\u9053\u54ea\u4e00\u4e2a\u5de5\u4f5c\u8868\u5728\u6fc0\u6d3b\u72b6\u6001\u3002\u56e0\u4e3a\u590d\u9009\u6846\u9009\u4e2d\u54ea\u4e9b\u670d\u52a1\u4e0d\u77e5\u9053\u3002\n\nWith Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\")\nIf UserForm1.\u8bc4\u5206\u5c0f\u9879 = True Then\n\nDim \u5e74\u9f84, \u5065\u5eb7\u72b6\u51b5, \u6587\u5316\u7a0b\u5ea6, \u7ecf\u8425\u5e74\u9650, \u65b0\u578b\u519c\u4e1a\u7ecf\u8425\u4e3b\u4f53, \u4f4f\u623f\u6027\u8d28, \u5a5a\u59fb\u72b6\u51b5, \u5bb6\u5ead\u52b3\u529b, \u9075\u7eaa\u5b88\u6cd5, \u5bb6\u5ead\u548c\u7766, \u6d89\u8bc9\u88ab\u6cd5\u9662\u6267\u884c\u60c5\u51b5, \u6c11\u95f4\u503a\u52a1\u53ca\u6c11\u95f4\u4e0d\u826f\u4fe1\u8a89, \u4e2a\u4eba\u94f6\u884c\u4fe1\u7528\u8bb0\u5f55, \u5bb6\u5ead\u5e74\u51c0\u6536\u5165, \u5bb6\u5ead\u51c0\u8d44\u4ea7, \u8d44\u4ea7\u8d1f\u503a\u7387, \u652f\u51fa\u6536\u5165\u6bd4\u7387, \u5bb6\u5ead\u8d44\u4ea7\u53ef\u53d8\u73b0\u91d1\u989d\n\n\n    \u5e74\u9f84 = .[G5]\n    \u5065\u5eb7\u72b6\u51b5 = .[I13]\n    \u6587\u5316\u7a0b\u5ea6 = .[B7]\n    \u7ecf\u8425\u5e74\u9650 = .[J7]\n    \u65b0\u578b\u519c\u4e1a\u7ecf\u8425\u4e3b\u4f53 = .[B8]\n    \u4f4f\u623f\u6027\u8d28 = .[I14]\n    \u5a5a\u59fb\u72b6\u51b5 = .[I6]\n    \u5bb6\u5ead\u52b3\u529b = .[G6]\n    \u9075\u7eaa\u5b88\u6cd5 = .[E10]\n    \u5bb6\u5ead\u548c\u7766 = .[B10]\n    \u6d89\u8bc9\u88ab\u6cd5\u9662\u6267\u884c\u60c5\u51b5 = .[I10]\n    \u6c11\u95f4\u503a\u52a1\u53ca\u6c11\u95f4\u4e0d\u826f\u4fe1\u8a89 = .[G10]\n'    \u4e2a\u4eba\u94f6\u884c\u4fe1\u7528\u8bb0\u5f55 =\n      \u6709\u501f\u6b3e\u4e14\u6709\u62c5\u4fdd\u65e0\u4e0d\u826f\u4fe1\u7528\u8bb0\u5f55 = .[B12]\n      \u65e0\u501f\u6b3e\u53ca\u62c5\u4fdd\u8bb0\u5f55\u4e14\u65e0\u4e0d\u826f\u4fe1\u7528\u8bb0\u5f55 = .[E12]\n      \u66fe\u6709\u4e0d\u826f\u501f\u6b3e\u53ca\u62c5\u4fdd\u4f46\u5df2\u6e05\u507f = .[G12]\n      \u4ecd\u6709\u4e0d\u826f\u501f\u6b3e\u6216\u4e0d\u826f\u4fe1\u7528\u8bb0\u5f55 = .[J12]\n    \u5bb6\u5ead\u5e74\u51c0\u6536\u5165 = .[J57]\n    \u5bb6\u5ead\u51c0\u8d44\u4ea7 = .[F39]\n    \u8d44\u4ea7\u8d1f\u503a\u7387 = .[H39]\n    \u652f\u51fa\u6536\u5165\u6bd4\u7387 = .[L57]\n    \u5bb6\u5ead\u8d44\u4ea7\u53ef\u53d8\u73b0\u91d1\u989d = .[K36]\n\nWith Sheets(\"\u519c\u6237\u4fe1\u7528\u7b49\u7ea7\u8bc4\u4f30-\u88682\")\n    Dim \u5e74\u9f84\u5f97\u5206, \u5065\u5eb7\u72b6\u51b5\u5f97\u5206, \u6587\u5316\u7a0b\u5ea6\u5f97\u5206, \u7ecf\u8425\u5e74\u9650\u5f97\u5206, \u65b0\u578b\u519c\u4e1a\u7ecf\u8425\u4e3b\u4f53\u5f97\u5206, \u4f4f\u623f\u6027\u8d28\u5f97\u5206, \u5a5a\u59fb\u72b6\u51b5\u5f97\u5206, \u5bb6\u5ead\u52b3\u529b\u5f97\u5206, \u9075\u7eaa\u5b88\u6cd5\u5f97\u5206, \u5bb6\u5ead\u548c\u7766\u5f97\u5206, \u6d89\u8bc9\u88ab\u6cd5\u9662\u6267\u884c\u60c5\u51b5\u5f97\u5206, \u6c11\u95f4\u503a\u52a1\u53ca\u6c11\u95f4\u4e0d\u826f\u4fe1\u8a89\u5f97\u5206, \u4e2a\u4eba\u94f6\u884c\u4fe1\u7528\u8bb0\u5f55\u5f97\u5206, \u5bb6\u5ead\u5e74\u51c0\u6536\u5165\u5f97\u5206, \u5bb6\u5ead\u51c0\u8d44\u4ea7\u5f97\u5206, \u8d44\u4ea7\u8d1f\u503a\u7387\u5f97\u5206, \u652f\u51fa\u6536\u5165\u6bd4\u7387\u5f97\u5206, \u5bb6\u5ead\u8d44\u4ea7\u53ef\u53d8\u73b0\u91d1\u989d\u5f97\u5206\n\n   \n   Select Case \u5e74\u9f84\n   Case 30 To 44\n   \u5e74\u9f84\u5f97\u5206 = 3\n   Case 45 To 54, 22 To 29\n   \u5e74\u9f84\u5f97\u5206 = 2\n   Case 18 To 21, 55 To 64\n   \u5e74\u9f84\u5f97\u5206 = 1\n   Case Else\n   \u5e74\u9f84\u5f97\u5206 = 0\n   End Select\n   \n   Select Case \u5065\u5eb7\u72b6\u51b5\n   Case Is = \"01-\u5065\u5eb7\"\n   \u5065\u5eb7\u72b6\u51b5\u5f97\u5206 = 4\n   Case Is = \"02-\u4e00\u822c\"\n   \u5065\u5eb7\u72b6\u51b5\u5f97\u5206 = 2\n   Case Is = \"03-\u8f83\u5dee\"\n   \u5065\u5eb7\u72b6\u51b5\u5f97\u5206 = 1\n   End Select\n   \n   Select Case \u6587\u5316\u7a0b\u5ea6\n   Case Is = \"01-\u5927\u4e13\u53ca\u4ee5\u4e0a\"\n   \u6587\u5316\u7a0b\u5ea6\u5f97\u5206 = 3\n   Case Is = \"02-\u4e2d\u4e13\", Is = \"03-\u9ad8\u4e2d\"\n   \u6587\u5316\u7a0b\u5ea6\u5f97\u5206 = 2\n   Case Else\n   \u6587\u5316\u7a0b\u5ea6\u5f97\u5206 = 1\n   End Select\n\n   Select Case \u7ecf\u8425\u5e74\u9650\n   Case Is >= 5\n   \u7ecf\u8425\u5e74\u9650\u5f97\u5206 = 3\n   Case 3 To 4.5\n   \u7ecf\u8425\u5e74\u9650\u5f97\u5206 = 2\n   Case Is < 3\n   \u7ecf\u8425\u5e74\u9650\u5f97\u5206 = 1\n   End Select\n   \n   Select Case \u65b0\u578b\u519c\u4e1a\u7ecf\u8425\u4e3b\u4f53\n   Case Is = \"09-\u65e0\"\n   \u65b0\u578b\u519c\u4e1a\u7ecf\u8425\u4e3b\u4f53\u5f97\u5206 = 0\n   Case Else\n   \u65b0\u578b\u519c\u4e1a\u7ecf\u8425\u4e3b\u4f53\u5f97\u5206 = 3\n   End Select\n   \n   Select Case \u4f4f\u623f\u6027\u8d28\n   Case Is = \"01-\u81ea\u5efa\", Is = \"02-\u81ea\u8d2d\"\n   \u4f4f\u623f\u6027\u8d28\u5f97\u5206 = 2\n   Case Is = \"03-\u6309\u63ed\"\n   \u4f4f\u623f\u6027\u8d28\u5f97\u5206 = 1\n   Case Is = \"04-\u79df\u8d41\"\n   \u4f4f\u623f\u6027\u8d28\u5f97\u5206 = 0\n   Case Is = \"05-\u5176\u4ed6\"\n   \u4f4f\u623f\u6027\u8d28\u5f97\u5206 = 0\n   End Select\n   \n   Select Case \u5a5a\u59fb\u72b6\u51b5\n   Case Is = \"00-\u672a\u5a5a\"\n   \u5a5a\u59fb\u72b6\u51b5\u5f97\u5206 = 2\n   Case Is = \"01-\u5df2\u5a5a\"\n   \u5a5a\u59fb\u72b6\u51b5\u5f97\u5206 = 3\n   Case Is = \"02-\u4e27\u5076\", Is = \"03-\u79bb\u5f02\"\n   \u5a5a\u59fb\u72b6\u51b5\u5f97\u5206 = 1\n   End Select\n   \n   Select Case \u5bb6\u5ead\u52b3\u529b\n   Case Is >= 2\n   \u5bb6\u5ead\u52b3\u529b\u5f97\u5206 = 2\n   Case Is = 1\n   \u5bb6\u5ead\u52b3\u529b\u5f97\u5206 = 1\n   End Select\n   \n   Select Case \u9075\u7eaa\u5b88\u6cd5\n   Case Is = \"0-\u65e0\"\n   \u9075\u7eaa\u5b88\u6cd5\u5f97\u5206 = 3\n   Case Is = \"1-\u6709\"\n   \u9075\u7eaa\u5b88\u6cd5\u5f97\u5206 = 0\n   End Select\n   \n   Select Case \u5bb6\u5ead\u548c\u7766\n   Case Is = \"01-\u548c\u7766\"\n   \u5bb6\u5ead\u548c\u7766\u5f97\u5206 = 3\n   Case Is = \"02-\u4e00\u822c\"\n   \u5bb6\u5ead\u548c\u7766\u5f97\u5206 = 2\n   Case Is = \"03-\u4e0d\u548c\u7766\"\n   \u5bb6\u5ead\u548c\u7766\u5f97\u5206 = 0\n   End Select\n   \n   Select Case \u6d89\u8bc9\u88ab\u6cd5\u9662\u6267\u884c\u60c5\u51b5\n   Case Is = \"0-\u65e0\"\n   \u6d89\u8bc9\u88ab\u6cd5\u9662\u6267\u884c\u60c5\u51b5\u5f97\u5206 = 2\n   Case Is = \"1-\u6709\"\n   \u6d89\u8bc9\u88ab\u6cd5\u9662\u6267\u884c\u60c5\u51b5\u5f97\u5206 = 0\n   End Select\n   \n   Select Case \u6c11\u95f4\u503a\u52a1\u53ca\u6c11\u95f4\u4e0d\u826f\u4fe1\u8a89\n   Case Is = \"0-\u65e0\"\n   \u6c11\u95f4\u503a\u52a1\u53ca\u6c11\u95f4\u4e0d\u826f\u4fe1\u8a89\u5f97\u5206 = 2\n   Case Is = \"1-\u6709\"\n   \u6c11\u95f4\u503a\u52a1\u53ca\u6c11\u95f4\u4e0d\u826f\u4fe1\u8a89\u5f97\u5206 = 0\n   End Select\n   \n   If \u6709\u501f\u6b3e\u4e14\u6709\u62c5\u4fdd\u65e0\u4e0d\u826f\u4fe1\u7528\u8bb0\u5f55 = \"1-\u662f\" Then\n      \u4e2a\u4eba\u94f6\u884c\u4fe1\u7528\u8bb0\u5f55\u5f97\u5206 = 10\n   ElseIf \u65e0\u501f\u6b3e\u53ca\u62c5\u4fdd\u8bb0\u5f55\u4e14\u65e0\u4e0d\u826f\u4fe1\u7528\u8bb0\u5f55 = \"1-\u662f\" Then\n      \u4e2a\u4eba\u94f6\u884c\u4fe1\u7528\u8bb0\u5f55\u5f97\u5206 = 8\n   ElseIf \u66fe\u6709\u4e0d\u826f\u501f\u6b3e\u53ca\u62c5\u4fdd\u4f46\u5df2\u6e05\u507f = \"1-\u662f\" Then\n      \u4e2a\u4eba\u94f6\u884c\u4fe1\u7528\u8bb0\u5f55\u5f97\u5206 = 5\n   ElseIf \u4ecd\u6709\u4e0d\u826f\u501f\u6b3e\u6216\u4e0d\u826f\u4fe1\u7528\u8bb0\u5f55 = \"1-\u662f\" Then\n      \u4e2a\u4eba\u94f6\u884c\u4fe1\u7528\u8bb0\u5f55\u5f97\u5206 = 0\n   Else\n      MsgBox \"\u4e2a\u4eba\u94f6\u884c\u4fe1\u7528\u8bb0\u5f55\u672a\u6b63\u786e\u9009\u62e9\"\n      Stop\n      \n   End If\n   \n   \n   Select Case \u5bb6\u5ead\u5e74\u51c0\u6536\u5165\n   Case Is >= 80000\n   \u5bb6\u5ead\u5e74\u51c0\u6536\u5165\u5f97\u5206 = 20\n   Case Else\n   \u5bb6\u5ead\u5e74\u51c0\u6536\u5165\u5f97\u5206 = 20 - (Fix((80000 - \u5bb6\u5ead\u5e74\u51c0\u6536\u5165) \/ 10000) + 1) * 2.5 'fix\u53ea\u53d6\u6574\u6570\u4f4d\uff08\u4e0d\u662f\u56db\u820d\u4e94\u5165\uff094.5\u53ea\u53d64\n   End Select '\u8fd9\u91cc\u7684\u60c5\u51b5\u548c\u4e0b\u9762\u7c7b\u4f3c\uff0c\u4f46\u662f\u4e0d\u4f1a\u51fa\u73b0\u5c0f\u4e8e0\u7684\u60c5\u51b5\u3002fix(\u5bb6\u5ead\u5e74\u51c0\u6536\u5165\/10000)*2.5\n   \n   Select Case \u5bb6\u5ead\u51c0\u8d44\u4ea7\n   Case Is >= 200000\n   \u5bb6\u5ead\u51c0\u8d44\u4ea7\u5f97\u5206 = 20\n   Case Is <= 0 '\u8fd9\u6837\u7684\u5730\u65b9\uff0c\u600e\u4e48\u80fd\u591f\u77e5\u9053\u54ea\u4e9b\u533a\u95f4\u5462\uff1f\u4ec0\u4e48\u662f\u6700\u76f4\u63a5\u7684\u5173\u7cfb\u5462\uff1f\u800c\u4e0d\u662f\u7528\u8fd9\u79cd\u7b97\u5f0f\u7684\u5462\uff1f\n   \u5bb6\u5ead\u51c0\u8d44\u4ea7\u5f97\u5206 = 0\n   Case Else\n   \u5bb6\u5ead\u51c0\u8d44\u4ea7\u5f97\u5206 = 20 - (Fix((200000 - \u5bb6\u5ead\u51c0\u8d44\u4ea7) \/ 10000) + 1) * 1 'fix\u53ea\u53d6\u6574\u6570\u4f4d\uff08\u4e0d\u662f\u56db\u820d\u4e94\u5165\uff094.5\u53ea\u53d64\n   End Select '20-(20-fix(\u5bb6\u5ead\u51c0\u8d44\u4ea7\/10000)) \u4e5f\u5c31\u662ffix(\u5bb6\u5ead\u51c0\u8d44\u4ea7\/10000) '\u8fd9\u79cd\u7b97\u6cd5\u662f\u6700\u597d\u7684\u30020\u7684\u60c5\u51b5\u4e5f\u80fd\u5305\u542b\u5728\u5185\u3002\n   \n   'Select Case \u8d44\u4ea7\u8d1f\u503a\u7387\n   'Case Is >= 0.7\n   '\u8d44\u4ea7\u8d1f\u503a\u7387\u5f97\u5206 = 0\n   'Case 0.1 To 0.6999999\n   '\u8d44\u4ea7\u8d1f\u503a\u7387\u5f97\u5206 = (Fix((0.7 - \u8d44\u4ea7\u8d1f\u503a\u7387) \/ 0.1) + 1) * 1 'fix\u53ea\u53d6\u6574\u6570\u4f4d\uff08\u4e0d\u662f\u56db\u820d\u4e94\u5165\uff094.5\u53ea\u53d64\n   'Case Is < 0.1\n   '\u8d44\u4ea7\u8d1f\u503a\u7387\u5f97\u5206 = 6\n   'End Select\n   \n   If \u8d44\u4ea7\u8d1f\u503a\u7387 >= 0.7 Then\n   \u8d44\u4ea7\u8d1f\u503a\u7387\u5f97\u5206 = 0\n   ElseIf \u8d44\u4ea7\u8d1f\u503a\u7387 >= 0.1 And \u8d44\u4ea7\u8d1f\u503a\u7387 < 0.7 Then\n   \u8d44\u4ea7\u8d1f\u503a\u7387\u5f97\u5206 = (Fix((0.7 - \u8d44\u4ea7\u8d1f\u503a\u7387) \/ 0.1) + 1) * 1 'fix\u53ea\u53d6\u6574\u6570\u4f4d\uff08\u4e0d\u662f\u56db\u820d\u4e94\u5165\uff094.5\u53ea\u53d64\n   ElseIf \u8d44\u4ea7\u8d1f\u503a\u7387 < 0.1 Then\n   \u8d44\u4ea7\u8d1f\u503a\u7387\u5f97\u5206 = 6 '\u539f\u6765\u4e0a\u4e00\u6761\u5224\u65ad\u4f1a\u5bfc\u81f4\u5c0f\u4e8e0.1\u7684\u60c5\u51b5\u5f977\u5206\u3002\n   End If\n   \n   'Select Case \u652f\u51fa\u6536\u5165\u6bd4\u7387\n   'Case Is < 0.4\n   '\u652f\u51fa\u6536\u5165\u6bd4\u7387\u5f97\u5206 = 5\n   'Case 0.4 To 0.4999999\n   '\u652f\u51fa\u6536\u5165\u6bd4\u7387\u5f97\u5206 = 4\n   'Case 0.5 To 0.5999999\n   '\u652f\u51fa\u6536\u5165\u6bd4\u7387\u5f97\u5206 = 3\n   'Case 0.6 To 0.6999999\n   '\u652f\u51fa\u6536\u5165\u6bd4\u7387\u5f97\u5206 = 1\n   'Case Is >= 0.7\n   '\u652f\u51fa\u6536\u5165\u6bd4\u7387\u5f97\u5206 = 0\n   'End Select\n   \n   If \u652f\u51fa\u6536\u5165\u6bd4\u7387 < 0.4 Then\n   \u652f\u51fa\u6536\u5165\u6bd4\u7387\u5f97\u5206 = 5\n   ElseIf \u652f\u51fa\u6536\u5165\u6bd4\u7387 >= 0.4 And \u652f\u51fa\u6536\u5165\u6bd4\u7387 < 0.5 Then\n   \u652f\u51fa\u6536\u5165\u6bd4\u7387\u5f97\u5206 = 4\n   ElseIf \u652f\u51fa\u6536\u5165\u6bd4\u7387 >= 0.5 And \u652f\u51fa\u6536\u5165\u6bd4\u7387 < 0.6 Then\n   \u652f\u51fa\u6536\u5165\u6bd4\u7387\u5f97\u5206 = 3\n   ElseIf \u652f\u51fa\u6536\u5165\u6bd4\u7387 >= 0.6 And \u652f\u51fa\u6536\u5165\u6bd4\u7387 < 0.7 Then\n   \u652f\u51fa\u6536\u5165\u6bd4\u7387\u5f97\u5206 = 1\n   Else\n   \u652f\u51fa\u6536\u5165\u6bd4\u7387\u5f97\u5206 = 0\n   End If\n   \n   'Select Case \u5bb6\u5ead\u8d44\u4ea7\u53ef\u53d8\u73b0\u91d1\u989d\n   'Case Is < 50000\n   '\u5bb6\u5ead\u8d44\u4ea7\u53ef\u53d8\u73b0\u91d1\u989d\u5f97\u5206 = 0\n   'Case 50000 To 99999\n   '\u5bb6\u5ead\u8d44\u4ea7\u53ef\u53d8\u73b0\u91d1\u989d\u5f97\u5206 = 1\n   'Case 100000 To 149999\n   '\u5bb6\u5ead\u8d44\u4ea7\u53ef\u53d8\u73b0\u91d1\u989d\u5f97\u5206 = 2\n   'Case Is >= 150000\n   '\u5bb6\u5ead\u8d44\u4ea7\u53ef\u53d8\u73b0\u91d1\u989d\u5f97\u5206 = 3\n   'End Select\n   \n   If \u5bb6\u5ead\u8d44\u4ea7\u53ef\u53d8\u73b0\u91d1\u989d < 50000 Then\n   \u5bb6\u5ead\u8d44\u4ea7\u53ef\u53d8\u73b0\u91d1\u989d\u5f97\u5206 = 0\n   ElseIf \u5bb6\u5ead\u8d44\u4ea7\u53ef\u53d8\u73b0\u91d1\u989d >= 50000 And \u5bb6\u5ead\u8d44\u4ea7\u53ef\u53d8\u73b0\u91d1\u989d < 100000 Then\n   \u5bb6\u5ead\u8d44\u4ea7\u53ef\u53d8\u73b0\u91d1\u989d\u5f97\u5206 = 1\n   ElseIf \u5bb6\u5ead\u8d44\u4ea7\u53ef\u53d8\u73b0\u91d1\u989d >= 100000 And \u5bb6\u5ead\u8d44\u4ea7\u53ef\u53d8\u73b0\u91d1\u989d < 150000 Then\n   \u5bb6\u5ead\u8d44\u4ea7\u53ef\u53d8\u73b0\u91d1\u989d\u5f97\u5206 = 2\n   ElseIf \u5bb6\u5ead\u8d44\u4ea7\u53ef\u53d8\u73b0\u91d1\u989d >= 150000 Then\n   \u5bb6\u5ead\u8d44\u4ea7\u53ef\u53d8\u73b0\u91d1\u989d\u5f97\u5206 = 3\n   End If\n\n    .[F7] = \u5e74\u9f84\u5f97\u5206\n    .[F8] = \u5065\u5eb7\u72b6\u51b5\u5f97\u5206\n    .[F9] = \u6587\u5316\u7a0b\u5ea6\u5f97\u5206\n    .[F10] = \u7ecf\u8425\u5e74\u9650\u5f97\u5206\n    .[F11] = \u65b0\u578b\u519c\u4e1a\u7ecf\u8425\u4e3b\u4f53\u5f97\u5206\n    .[F12] = \u4f4f\u623f\u6027\u8d28\u5f97\u5206\n    .[F13] = \u5a5a\u59fb\u72b6\u51b5\u5f97\u5206\n    .[F14] = \u5bb6\u5ead\u52b3\u529b\u5f97\u5206\n    .[F15] = \u9075\u7eaa\u5b88\u6cd5\u5f97\u5206\n    .[F16] = \u5bb6\u5ead\u548c\u7766\u5f97\u5206\n    .[F17] = \u6d89\u8bc9\u88ab\u6cd5\u9662\u6267\u884c\u60c5\u51b5\u5f97\u5206\n    .[F18] = \u6c11\u95f4\u503a\u52a1\u53ca\u6c11\u95f4\u4e0d\u826f\u4fe1\u8a89\u5f97\u5206\n    .[F19] = \u4e2a\u4eba\u94f6\u884c\u4fe1\u7528\u8bb0\u5f55\u5f97\u5206\n    .[F20] = \u5bb6\u5ead\u5e74\u51c0\u6536\u5165\u5f97\u5206\n    .[F21] = \u5bb6\u5ead\u51c0\u8d44\u4ea7\u5f97\u5206\n    .[F22] = \u8d44\u4ea7\u8d1f\u503a\u7387\u5f97\u5206\n    .[F23] = \u652f\u51fa\u6536\u5165\u6bd4\u7387\u5f97\u5206\n    .[F24] = \u5bb6\u5ead\u8d44\u4ea7\u53ef\u53d8\u73b0\u91d1\u989d\u5f97\u5206\n    \nEnd With '\u8bc4\u5206\u91cc\u9762\u7684\u88682\u7684\uff0c\u5d4c\u5957\nEnd If '\u8bc4\u5206\u5c0f\u9879\u7684if\nEnd With '\u88684\u7684\n\nEnd Sub\n\nSub \u767b\u8bb0\u9700\u63d0\u5206\u5c0f\u9879()\n    Workbooks(filename).Activate '\u4e0d\u77e5\u9053\u54ea\u4e00\u4e2a\u5de5\u4f5c\u8868\u5728\u6fc0\u6d3b\u72b6\u6001\u3002\u56e0\u4e3a\u590d\u9009\u6846\u9009\u4e2d\u54ea\u4e9b\u670d\u52a1\u4e0d\u77e5\u9053\u3002\n\n    \u5e74\u9f84 = Sheets(\"\u519c\u6237\u4fe1\u606f\u7ecf\u6d4e\u6863\u6848-\u88684\").[G5]\n    \u5206\u6570 = Sheets(\"\u519c\u6237\u4fe1\u7528\u7b49\u7ea7\u8bc4\u4f30-\u88682\").[F27]\n'       If (\u5e74\u9f84 >= 18 & \u5e74\u9f84 <= 60) & \u5206\u6570 < 60 Then '\u8fd9\u91cc\u5e74\u9f84\u6700\u597d\u7528\u5355\u5143\u683c\u5f15\u7528\uff0c\u56e0\u4e3a\u4e0d\u77e5\u9053\u8bc4\u5206\u5c0f\u9879\u662f\u4e0d\u662f\u4e5f\u8fd0\u884c\u8d4b\u503c\u7ed9\u53d8\u91cf\u201c\u5e74\u9f84\u201d\u3002\n       If (\u5e74\u9f84 >= 18 And \u5e74\u9f84 <= 60) And \u5206\u6570 < 60 Then '\u8fd9\u91cc\u5e74\u9f84\u6700\u597d\u7528\u5355\u5143\u683c\u5f15\u7528\uff0c\u56e0\u4e3a\u4e0d\u77e5\u9053\u8bc4\u5206\u5c0f\u9879\u662f\u4e0d\u662f\u4e5f\u8fd0\u884c\u8d4b\u503c\u7ed9\u53d8\u91cf\u201c\u5e74\u9f84\u201d\u3002\nMsgBox file\n          Dim i2 As Integer\n          For i2 = 1 To Workbooks.Count\n            If Workbooks(i2).Name = \"\u767b\u8bb0\u9700\u63d0\u5206.xlsx\" Then\n            h2 = 1 '\u8fd9\u53e5\u8981\u52a0\u4e0a\uff0c\u5982\u679c\u7b2c\u4e00\u4e2a\u5c31\u9047\u5230\"\u767b\u8bb0\u9700\u63d0\u5206.xlsx\"\uff0c\u5c31\u4e0d\u4f1a\u8d4b\u503c\u7ed9h2 = 0\uff0ch2\u4e3a\u7a7a\u503c\uff0c\u4e5f\u5c31\u662f=0\u4e86\uff0c\u8fd8\u662f\u4f1a\u6253\u5f00\"\u767b\u8bb0\u9700\u63d0\u5206.xlsx\"\u3002\n            Exit For '\u5982\u679c\u9047\u5230 \"\u767b\u8bb0\u9700\u63d0\u5206.xlsx\"\u5c31\u8df3\u51fafor\u8bed\u53e5\n            Else\n            h2 = 0 '\u505a\u4e2a\u6807\u8bb0\uff0c\u8ba9\u5faa\u73af\u7ed3\u675f\u540e\u77e5\u9053\u662f\u4e0d\u662f\u6ca1\u6709\u53d1\u73b0\"\u767b\u8bb0\u9700\u63d0\u5206.xlsx\"\u3002\n            End If\n            Next\n            \n            If h2 = 0 Then '\u628a\u6253\u5f00  \"\/\u767b\u8bb0\u9700\u63d0\u5206.xlsx\" \u653e\u5230for\u5916\u9762\uff0c\u907f\u514dif\u8bed\u53e5\u6ca1\u5230\"\/\u767b\u8bb0\u9700\u63d0\u5206.xlsx\"\uff0c\u5c31\u6267\u884c\u4e86else\u8bed\u53e5\u6253\u5f00\u3002\u533a\u522b\u5728\u4e8e\uff0c\u5faa\u73af\u7ed3\u675f\u540e\u624d\u80fd\u77e5\u9053\u6709\u6ca1\u6709\u6253\u5f00\u3002\n            Dim wb3 As Workbook\n            Dim file3\n            file3 = ThisWorkbook.Path & \"\/\u767b\u8bb0\u9700\u63d0\u5206.xlsx\"\n            Set wb3 = Application.Workbooks.Open(file3)\n               If wb3 Is Nothing Then MsgBox (\"\u6587\u4ef6\u6253\u5f00\u5931\u8d25\uff0c\u8bf7\u68c0\u67e5\" & pth & fn & \"\u662f\u5426\u5b58\u5728\uff01\"): Exit Sub '\u8fd9\u91cc\u5c31\u4e0d\u7528end if\u4e86\uff0c\u56e0\u4e3a\u5199\u5728\u4e00\u884c\u91cc\u9762\u4e86\u3002\u4eca\u5929\u521a\u770b\u5230\u8fd9\u4e2a\u8d44\u6599\u3002\n            End If\n         \n            'xlrow = Workbooks(\"\u767b\u8bb0\u9700\u63d0\u5206.xlsx\").Worksheets(1).UsedRange.Rows.Count + 1\n            xlrow = Workbooks(\"\u767b\u8bb0\u9700\u63d0\u5206.xlsx\").Worksheets(1).Range(\"A20000\").End(xlUp).Row + 1 '\u4e0d\u77e5\u905307\u7248\u672c\u7684\u5176\u4ed6\u8bed\u53e5\u600e\u4e48\u5199\uff0c\u4e0d\u7528\u5199\u884c\u53f7\u7684\u90a3\u79cd\u300207\u6700\u5927\u884c\u53f7\u662f1048576\u3002\n            Workbooks(\"\u767b\u8bb0\u9700\u63d0\u5206.xlsx\").Worksheets(1).Range(\"A\" & xlrow) = filename\n            Workbooks(\"\u767b\u8bb0\u9700\u63d0\u5206.xlsx\").Worksheets(1).Hyperlinks.Add Anchor:=Workbooks(\"\u767b\u8bb0\u9700\u63d0\u5206.xlsx\").Worksheets(1).Range(\"A\" & xlrow), Address:=file\n        End If\n\nEnd Sub\n\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
\u4e00\u59574\u4e2a\u8868\u683c\u6587\u4ef6\uff0c\u5206\u4e3a\u811a\u672c.xlam\u6587\u4ef6\uff0c\u57fa\u7840\u6570\u636e.xlsx\uff0c\u8fd8\u6709\u7528\u6765\u767b\u8bb0\u811a\u672c\u6311\u9009\u51fa\u6570\u636e\u7684\u6587\u4ef6\u7535\u8bdd\u53f7\u7801.xls […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[58,23],"tags":[],"_links":{"self":[{"href":"http:\/\/www.henrycome.com\/wordpress\/wp-json\/wp\/v2\/posts\/523"}],"collection":[{"href":"http:\/\/www.henrycome.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.henrycome.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.henrycome.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.henrycome.com\/wordpress\/wp-json\/wp\/v2\/comments?post=523"}],"version-history":[{"count":4,"href":"http:\/\/www.henrycome.com\/wordpress\/wp-json\/wp\/v2\/posts\/523\/revisions"}],"predecessor-version":[{"id":668,"href":"http:\/\/www.henrycome.com\/wordpress\/wp-json\/wp\/v2\/posts\/523\/revisions\/668"}],"wp:attachment":[{"href":"http:\/\/www.henrycome.com\/wordpress\/wp-json\/wp\/v2\/media?parent=523"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.henrycome.com\/wordpress\/wp-json\/wp\/v2\/categories?post=523"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.henrycome.com\/wordpress\/wp-json\/wp\/v2\/tags?post=523"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}