Excel中利用宏编辑的VBA提取有用数据并对数据自动处理和制图.docx

1、Excel中利用宏编辑的VBA提取有用数据并对数据自动处理和制图Excel中利用宏编辑的VBA提取有用数据并对数据自动处理和制图Sub 整体数据处理() 工作表“HistroryData”提取8:30数据 Sheets(HistroryData).Select Range(E2).Select ActiveCell.FormulaR1C1 = =RC-1 Selection.AutoFill Destination:=Range(E2:E2100 ), Type:=xlFillDefault Range(E2:E2100 ).Select Selection.NumberFormatLocal

2、 = 0.00_);红色(0.00) Range(F2).Select ActiveCell.FormulaR1C1 = =INT(RC-1) Selection.AutoFill Destination:=Range(F2:F2100), Type:=xlFillDefault Range(G2).Select ActiveCell.FormulaR1C1 = =INT(RC-2-RC-1)*100) Selection.AutoFill Destination:=Range(G2:G2100), Type:=xlFillDefault Dim i As Integer For i = Ra

3、nge(g65536).End(xlUp).Row To 2 Step -1 If Range(g & i).Value 35 Then Rows(i).Delete shift:=xlUp End If Next Columns(E:G).Select Selection.ClearContents 工作表“HistroryData (2)”提取12:30数据 Sheets(HistroryData (2).Select Range(E2).Select ActiveCell.FormulaR1C1 = =RC-1 Selection.AutoFill Destination:=Range(

4、E2:E2100), Type:=xlFillDefault Range(E2:E2100).Select Selection.NumberFormatLocal = 0.00_);红色(0.00) Range(F2).Select ActiveCell.FormulaR1C1 = =INT(RC-1) Selection.AutoFill Destination:=Range(F2:F2100), Type:=xlFillDefault Range(G2).Select ActiveCell.FormulaR1C1 = =INT(RC-2-RC-1)*100) Selection.AutoF

5、ill Destination:=Range(G2:G2100), Type:=xlFillDefault Dim j As Integer For j = Range(g65536).End(xlUp).Row To 2 Step -1 If Range(g & j).Value 52 Then Rows(j).Delete shift:=xlUp End If Next Columns(E:G).Select Selection.ClearContents 工作表“HistroryData (3)”提取4:30数据 1 Sheets(HistroryData (3).Select Rang

6、e(E2).Select ActiveCell.FormulaR1C1 = =RC-1 Selection.AutoFill Destination:=Range(E2:E2100), Type:=xlFillDefault Range(E2:E2100).Select Selection.NumberFormatLocal = 0.00_);红色(0.00) Range(F2).Select ActiveCell.FormulaR1C1 = =INT(RC-1) Selection.AutoFill Destination:=Range(F2:F2100), Type:=xlFillDefa

7、ult Range(G2).Select ActiveCell.FormulaR1C1 = =INT(RC-2-RC-1)*100) Selection.AutoFill Destination:=Range(G2:G2100), Type:=xlFillDefault Dim k As Integer For k = Range(g65536).End(xlUp).Row To 2 Step -1 If Range(g & k).Value 68 Then Rows(k).Delete shift:=xlUp End If Next Columns(E:G).Select Selection

8、.ClearContents 数据汇总到工作表“数据处理”上 Sheets(HistroryData).Select Range(C2:C34).Select Selection.Copy Sheets(数据处理).Select Range(C4).Select ActiveSheet.Paste Sheets(HistroryData (2).Select Range(c2:c34).Select Selection.Copy Sheets(数据处理).Select Range(D4).Select ActiveSheet.Paste Sheets(HistroryData (3).Sele

9、ct Range(c2:c34).Select Selection.Copy Sheets(数据处理).Select Range(E4).Select ActiveSheet.Paste Range(C2).Select ActiveCell.FormulaR1C1 = 液位状态 Range(C2:E2).Select Selection.Merge Range(C2:E2).Select 2 With Selection.Font .Size = 14 End With Range(C3).Select ActiveCell.FormulaR1C1 = 8:30AM Range(D3).Se

10、lect ActiveCell.FormulaR1C1 = 12:30AM Range(E3).Select ActiveCell.FormulaR1C1 = 4:30PM Range(B3).Select ActiveCell.FormulaR1C1 = 距今天天数 Range(B4).Select ActiveCell.FormulaR1C1 = 1 Range(B5).Select ActiveCell.FormulaR1C1 = 2 Range(B4:B5).Select Selection.AutoFill Destination:=Range(B4:B150), Type:=xlF

11、illDefault 调整表格边框 Range(B2:E150).Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext End With Selection.Borders(xlDiagonalDown).LineStyle = xlNone Sel

12、ection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEd

13、geBottom) .LineStyle = xlContinuous .Weight = xlThin 3 .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex

14、= xlAutomatic End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Range(B2:B3).Select Selection.Merge Columns(B:B).ColumnWidth = 15.71 Range(B2:B3).Select With Selection.Font .Size = 14 End With 用量计算 Range(F2).Select Activ

15、eCell.FormulaR1C1 = 用量(mm) Range(F2:H2).Select Selection.Merge With Selection.Font .Size = 14 End With Range(F3).Select ActiveCell.FormulaR1C1 = 8:30AM Range(G3).Select ActiveCell.FormulaR1C1 = 12:30AM Range(H3).Select ActiveCell.FormulaR1C1 = 4:30PM 8:30AM用量计算 Range(F4).Select ActiveCell.FormulaR1C

16、1 = =R1C-3-RC-3 Range(F5).Select ActiveCell.FormulaR1C1 = =R1C-3-RC-3 Range(F4:F5).Select 4 Selection.AutoFill Destination:=Range(F4:F150), Type:=xlFillDefault 12:30AM用量计算 Range(G4).Select ActiveCell.FormulaR1C1 = =R1C-3-RC-3 Range(G5).Select ActiveCell.FormulaR1C1 = =R1C-3-RC-3 Range(G4:G5).Select

17、Selection.AutoFill Destination:=Range(G4:G150), Type:=xlFillDefault 4:30PM用量计算 Range(H4).Select ActiveCell.FormulaR1C1 = =R1C-3-RC-3 Range(H5).Select ActiveCell.FormulaR1C1 = =R1C-3-RC-3 Range(H4:H5).Select Selection.AutoFill Destination:=Range(H4:H150), Type:=xlFillDefault 画日用量图 Range(F3:H34).Select Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=Sheets(数据处理).Range(F3:H20) ActiveChart.Location Where:=xlLocationAsObject, Name:=数据处理 ActiveChart.HasTitle = True ActiveChart.ChartTitle.Text = XX End Sub 5