利用excel VBA进行自动化数据分析，数据汇总，网页表单自动提交等功能

　　在制造业公司的生产管理，经营管理，采购管理，财务管理等工作中，都有大量的数据处理的任务，通过繁复的excel手工运算获取结果。通过员工培训和自我提升，掌握和使用excel数组公式和VBA自动化，能为员工节省巨大的时间和精力，提高工作附加值。同时作为公司效率化和系统化改善的一部分，为公司效益带来显著提升。以下通过一些案例，展示利用excel公式和VBA进行自动化数据分析，数据汇总，网页表单自动提交在实际场景中的典型应用。相关的文件和代码可以在github下载。

• 自动化数据分析

　　以下是通过VBA自动化数据分析来计算预计在手和在途库存的流程。

 

 

　　以下是预计在手和在途库存的代码。

 

  1 Sub 预计在手和在途()
  2 '
  3 ' 预计在手和在途 宏
  4 '
  5     SCH_IDITEM_NO (7)
  6     SCH_IDITEM_NO (11)
  7     SCH_IDITEM_NO (21)
  8     
  9     P = ActiveWorkbook.Path
 10     Columns("C:C").Select
 11     Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
 12     Range("C1").Select
 13     ActiveCell.FormulaR1C1 = "=RC[-2]&RC[-1]"
 14     Range("C1").Select
 15     Selection.AutoFill Destination:=Range("C1:C138750")
 16     Columns("C:C").Select
 17     Selection.Copy
 18     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 19         :=False, Transpose:=False
 20         
 21     For Each cel In Range("c2:c160000")
 22         If IsNumeric(cel) And cel <> "" Then
 23             cel.Value = Val(cel.Value)
 24         End If
 25     Next
 26     
 27     Range("A1").Select
 28     Range(Selection, Selection.End(xlDown)).Select
 29     Range(Selection, Selection.End(xlToRight)).Select
 30     Selection.Copy
 31     Workbooks.Open ("C:\Users\5106002125\Desktop\企划管理\静态参考资料\套用公式\在库试算.xlsx")
 32     Sheets.Add After:=Sheets(Sheets.Count)
 33     Range("A1").Select
 34     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 35         :=False, Transpose:=False
 36     Rows("1:1").Select
 37     Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 38     
 39     Sheets("7").Select
 40     ActiveSheet.UsedRange.Select
 41     Selection.Clear
 42     Sheets("11").Select
 43     ActiveSheet.UsedRange.Select
 44     Selection.Clear
 45     Sheets("21").Select
 46     ActiveSheet.UsedRange.Select
 47     Selection.Clear
 48     
 49     Set book1 = Workbooks.Open("C:\Users\5106002125\Desktop\企划管理\过期\7.csv")
 50     Set book2 = Workbooks.Open("C:\Users\5106002125\Desktop\企划管理\过期\11.csv")
 51     Set book3 = Workbooks.Open("C:\Users\5106002125\Desktop\企划管理\过期\21.csv")
 52     
 53     Windows("7.csv").Activate
 54     Range("A1").Select
 55     Range(Selection, Selection.End(xlDown)).Select
 56     Range(Selection, Selection.End(xlToRight)).Select
 57     Selection.Copy
 58     Windows("在库试算.xlsx").Activate
 59     Sheets("7").Select
 60     Range("A1").Select
 61     ActiveSheet.Paste
 62     
 63     Windows("11.csv").Activate
 64     Range("A1").Select
 65     Range(Selection, Selection.End(xlDown)).Select
 66     Range(Selection, Selection.End(xlToRight)).Select
 67     Selection.Copy
 68     Windows("在库试算.xlsx").Activate
 69     Sheets("11").Select
 70     Range("A1").Select
 71     ActiveSheet.Paste
 72         
 73     Windows("21.csv").Activate
 74     Range("A1").Select
 75     Range(Selection, Selection.End(xlDown)).Select
 76     Range(Selection, Selection.End(xlToRight)).Select
 77     Selection.Copy
 78     Windows("在库试算.xlsx").Activate
 79     Sheets("21").Select
 80     Range("A1").Select
 81     ActiveSheet.Paste
 82     
 83     
 84     For col = 20 To 41
 85     
 86     Sheets("公式").Select
 87     Range(Cells(2, col), Cells(3, col)).Select
 88     Application.CutCopyMode = False
 89     Selection.Copy
 90     Sheets("Sheet2").Select
 91     Range(Cells(2, col), Cells(3, col)).Select
 92     Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
 93         SkipBlanks:=False, Transpose:=False
 94     
 95     Range(Cells(3, col), Cells(3, col)).Select
 96     Application.CutCopyMode = False
 97     Selection.AutoFill Destination:=Range(Cells(3, col), Cells(200000, col))
 98 
 99     Range(Cells(3, col), Cells(200000, col)).Copy
100     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
101         :=False, Transpose:=False
102 
103     Next
104 
105 
106     Sheets("公式").Select
107     Range(Cells(1, 1), Cells(1, 41)).Select
108     Application.CutCopyMode = False
109     Selection.Copy
110     Sheets("Sheet2").Select
111     Range(Cells(1, 1), Cells(1, 41)).Select
112     Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
113         SkipBlanks:=False, Transpose:=False
114 
115     Dim r As Integer
116     Range("a2").Select
117     Selection.End(xlDown).Select
118     r = Selection.row
119     Range(Cells(1, 1), Cells(r, 41)).Copy
120     Workbooks.Add
121     Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
122         :=False, Transpose:=False
123     Application.CutCopyMode = False
124     Range("AC1:AO1").Style = "Comma"
125 
126     Range("AM2:AO2").Select
127     Range("AO2").Activate
128     Range(Selection, Selection.End(xlDown)).Select
129     Sheets.Add
130     ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
131         "Sheet1!R2C39:R138210C41", Version:=xlPivotTableVersion14).CreatePivotTable _
132         TableDestination:="Sheet4!R3C1", TableName:="数据透视表1", DefaultVersion:= _
133         xlPivotTableVersion14
134     Sheets("Sheet4").Select
135     Cells(3, 1).Select
136     With ActiveSheet.PivotTables("数据透视表1").PivotFields("库位2")
137         .Orientation = xlRowField
138         .Position = 1
139     End With
140     ActiveSheet.PivotTables("数据透视表1").AddDataField ActiveSheet.PivotTables("数据透视表1" _
141         ).PivotFields("在手"), "求和项:在手", xlSum
142     ActiveSheet.PivotTables("数据透视表1").AddDataField ActiveSheet.PivotTables("数据透视表1" _
143         ).PivotFields("在途"), "计数项:在途", xlCount
144     With ActiveSheet.PivotTables("数据透视表1").PivotFields("计数项:在途")
145         .Caption = "求和项:在途"
146         .Function = xlSum
147     End With
148     Cells.Select
149     Selection.Style = "Comma"
150     
151     ActiveWorkbook.SaveAs Filename:=P & "\在库试算结果" & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
152 
153     book1.Close savechanges