Excel中利用宏编辑的VBA提取有用数据并对数据自动处理和制图.docx
- 文档编号:5295379
- 上传时间:2023-05-08
- 格式:DOCX
- 页数:9
- 大小:15.31KB
Excel中利用宏编辑的VBA提取有用数据并对数据自动处理和制图.docx
《Excel中利用宏编辑的VBA提取有用数据并对数据自动处理和制图.docx》由会员分享,可在线阅读,更多相关《Excel中利用宏编辑的VBA提取有用数据并对数据自动处理和制图.docx(9页珍藏版)》请在冰点文库上搜索。
Excel中利用宏编辑的VBA提取有用数据并对数据自动处理和制图
Excel中利用宏编辑的VBA提取有用数据并对数据自动处理和制图
Sub整体数据处理()
‘工作表“HistroryData”提取8:
30数据
Sheets("HistroryData").Select
Range("E2").Select
ActiveCell.FormulaR1C1="=RC[-1]"
Selection.AutoFillDestination:
=Range("E2:
E2100"),Type:
=xlFillDefault
Range("E2:
E2100").Select
Selection.NumberFormatLocal="0.00_);[红色](0.00)"
Range("F2").Select
ActiveCell.FormulaR1C1="=INT(RC[-1])"
Selection.AutoFillDestination:
=Range("F2:
F2100"),Type:
=xlFillDefault
Range("G2").Select
ActiveCell.FormulaR1C1="=INT((RC[-2]-RC[-1])*100)"
Selection.AutoFillDestination:
=Range("G2:
G2100"),Type:
=xlFillDefault
DimiAsInteger
Fori=Range("g65536").End(xlUp).RowTo2Step-1
IfRange("g"&i).Value<>35Then
Rows(i).Deleteshift:
=xlUp
EndIf
Next
Columns("E:
G").Select
Selection.ClearContents
‘工作表“HistroryData
(2)”提取12:
30数据
Sheets("HistroryData
(2)").Select
Range("E2").Select
ActiveCell.FormulaR1C1="=RC[-1]"
Selection.AutoFillDestination:
=Range("E2:
E2100"),Type:
=xlFillDefault
Range("E2:
E2100").Select
Selection.NumberFormatLocal="0.00_);[红色](0.00)"
Range("F2").Select
ActiveCell.FormulaR1C1="=INT(RC[-1])"
Selection.AutoFillDestination:
=Range("F2:
F2100"),Type:
=xlFillDefault
Range("G2").Select
ActiveCell.FormulaR1C1="=INT((RC[-2]-RC[-1])*100)"
Selection.AutoFillDestination:
=Range("G2:
G2100"),Type:
=xlFillDefault
DimjAsInteger
Forj=Range("g65536").End(xlUp).RowTo2Step-1
IfRange("g"&j).Value<>52Then
Rows(j).Deleteshift:
=xlUp
EndIf
Next
Columns("E:
G").Select
Selection.ClearContents
‘工作表“HistroryData(3)”提取4:
30数据
1
Sheets("HistroryData(3)").Select
Range("E2").Select
ActiveCell.FormulaR1C1="=RC[-1]"
Selection.AutoFillDestination:
=Range("E2:
E2100"),Type:
=xlFillDefault
Range("E2:
E2100").Select
Selection.NumberFormatLocal="0.00_);[红色](0.00)"
Range("F2").Select
ActiveCell.FormulaR1C1="=INT(RC[-1])"
Selection.AutoFillDestination:
=Range("F2:
F2100"),Type:
=xlFillDefault
Range("G2").Select
ActiveCell.FormulaR1C1="=INT((RC[-2]-RC[-1])*100)"
Selection.AutoFillDestination:
=Range("G2:
G2100"),Type:
=xlFillDefault
DimkAsInteger
Fork=Range("g65536").End(xlUp).RowTo2Step-1
IfRange("g"&k).Value<>68Then
Rows(k).Deleteshift:
=xlUp
EndIf
Next
Columns("E:
G").Select
Selection.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)").Select
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
WithSelection.Font
.Size=14
EndWith
Range("C3").Select
ActiveCell.FormulaR1C1="8:
30AM"
Range("D3").Select
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.AutoFillDestination:
=Range("B4:
B150"),Type:
=xlFillDefault
‘调整表格边框
Range("B2:
E150").Select
WithSelection
.HorizontalAlignment=xlCenter
.VerticalAlignment=xlBottom
.WrapText=False
.Orientation=0
.AddIndent=False
.IndentLevel=0
.ShrinkToFit=False
.ReadingOrder=xlContext
EndWith
Selection.Borders(xlDiagonalDown).LineStyle=xlNone
Selection.Borders(xlDiagonalUp).LineStyle=xlNone
WithSelection.Borders(xlEdgeLeft)
.LineStyle=xlContinuous
.Weight=xlThin
.ColorIndex=xlAutomatic
EndWith
WithSelection.Borders(xlEdgeTop)
.LineStyle=xlContinuous
.Weight=xlThin
.ColorIndex=xlAutomatic
EndWith
WithSelection.Borders(xlEdgeBottom)
.LineStyle=xlContinuous
.Weight=xlThin
3
.ColorIndex=xlAutomatic
EndWith
WithSelection.Borders(xlEdgeRight)
.LineStyle=xlContinuous
.Weight=xlThin
.ColorIndex=xlAutomatic
EndWith
WithSelection.Borders(xlInsideVertical)
.LineStyle=xlContinuous
.Weight=xlThin
.ColorIndex=xlAutomatic
EndWith
WithSelection.Borders(xlInsideHorizontal)
.LineStyle=xlContinuous
.Weight=xlThin
.ColorIndex=xlAutomatic
EndWith
Range("B2:
B3").Select
Selection.Merge
Columns("B:
B").ColumnWidth=15.71
Range("B2:
B3").Select
WithSelection.Font
.Size=14
EndWith
‘用量计算
Range("F2").Select
ActiveCell.FormulaR1C1="用量(mm)"
Range("F2:
H2").Select
Selection.Merge
WithSelection.Font
.Size=14
EndWith
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.FormulaR1C1="=R[1]C[-3]-RC[-3]"
Range("F5").Select
ActiveCell.FormulaR1C1="=R[1]C[-3]-RC[-3]"
Range("F4:
F5").Select
4
Selection.AutoFillDestination:
=Range("F4:
F150"),Type:
=xlFillDefault
‘12:
30AM用量计算
Range("G4").Select
ActiveCell.FormulaR1C1="=R[1]C[-3]-RC[-3]"
Range("G5").Select
ActiveCell.FormulaR1C1="=R[1]C[-3]-RC[-3]"
Range("G4:
G5").Select
Selection.AutoFillDestination:
=Range("G4:
G150"),Type:
=xlFillDefault
‘4:
30PM用量计算
Range("H4").Select
ActiveCell.FormulaR1C1="=R[1]C[-3]-RC[-3]"
Range("H5").Select
ActiveCell.FormulaR1C1="=R[1]C[-3]-RC[-3]"
Range("H4:
H5").Select
Selection.AutoFillDestination:
=Range("H4:
H150"),Type:
=xlFillDefault
‘画日用量图
Range("F3:
H34").Select
Charts.Add
ActiveChart.ChartType=xlLineMarkers
ActiveChart.SetSourceDataSource:
=Sheets("数据处理").Range("F3:
H20")
ActiveChart.LocationWhere:
=xlLocationAsObject,Name:
="数据处理"
ActiveChart.HasTitle=True
ActiveChart.ChartTitle.Text="XX"
EndSub
5
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 利用 编辑 VBA 提取 有用 数据 自动 处理 制图