Excel VBA在沉降观测资料整理中的应用.doc

Excel在沉降观测资料整理中的应用摘要2一、认识Excel2二、沉降观测概述3三、成果表的汇总41、移动工作表42、录制宏5四、汇总表71、本期沉降量的计算72、完成汇总表103、沉降量曲线图的绘制15五、结语20摘要Microsoft Excel是微软公司的办公软件Microsoft office的组件之一，是由Microsoft为Windows和Apple Macintosh操作系统的电脑而编写和运行的一款试算表软件。Excel 是微软办公套装软件的一个重要的组成部分，它可以进行各种数据的处理、统计分析和辅助决策操作，广泛地应用于管理、统计、财经、金融等众多领域。微软不可能仅仅给我们一个只能处理一些简单数据的产品。小到记录文本，大到一个公司的管理，都可以用Excel来完成，只要源数据足够优秀，Excel完全可以应付很多企业的数据挖掘工作，甚至相比于新兴的商业智能系统Excel也有其优势的地方。关键词：Excel 沉降观测 资料整理一、认识ExcelMicrosoft Excel是微软公司的办公软件Microsoft office的组件之一，是由Microsoft为Windows和Apple Macintosh操作系统的电脑而编写和运行的一款试算表软件。Excel 是微软办公套装软件的一个重要的组成部分，它可以进行各种数据的处理、统计分析和辅助决策操作，广泛地应用于管理、统计、财经、金融等众多领域。1985年第一款Excel软件诞生，但当时只支持Mac系统，第一款适用于Windows系统的Excel诞生于1987年，此后Excel不断升级，直至今天的Excel2013，但是现在较为常用的还是Excel2003和Excel2007。自诞生之日Excel就已注定成为电子表格世界的霸主，Excel自带功能和函数在各个领域发挥着重要作用，它也被设计为能满足各种不同人群需要的办公必备工具，无论我们是做一个简单的摘要、制作销售趋势图，还是执行高级分析，无论我们正在做什么工作，Microsoft Excel都能按照我们希望的方式帮助我们完成工作。但是，Excel也不是万能的，比如要计算超过15位的数字，或者处理重复性较大的工作，这个时候Excel就表现出其自身的局限性，当然，Excel设计人员为我们提供了弥补这些局限的功能-VBA，VBA是Visual Basic for Applications的缩写，VBA是VB的子集，但又不同于VB，它寄生于已有的应用程序，比如Excel，它和Excel的关系就好比生物学上的互利共生，Excel为VBA提供生存环境，而VBA可以更大限度的扩展Excel的功能，使其在各领域发挥更大的作用。当然，并不只是Excel支持VBA，Office家族的其他成员，还有我们常用的Auto CAD也都支持VBA，有了VBA就可以实现这些应用程序的无缝连接，使很多工作的效率得以成几何倍数的提高，我们也可以用VBA为自己定制个性化的功能，真正实现办公的自动化。二、沉降观测概述随着高速铁路的建设，也给沉降观测工作提出了更高的要求，施工单位要投入更多的人力物力来实施这项工作，而对于沉降观测资料，还要通过评估单位的评估才能进行后续施工，所以沉降观测工作的顺利进行是保证施工工期的必要条件。评估单位又会对沉降观测资料的整理提出硬性的格式要求，这就需要沉降观测人员不但要熟练掌握专业技能还要能够熟练掌握办公软件的操作。沉降观测是一项繁重、枯燥而又重复性大的工作，这就决定了其资料在整理过程中会存在很大困难，而换一种角度看，沉降观测资料整理的重复性大又决定了工作效率会有很大的提升空间，这就要求我们在日常的工作和学习中去探索。笔者有幸接触到沉降观测资料的整理工作，在不断的学习中总结了一些方法和经验，在自己得到提升的同时也保证了工作的顺利完成。对沉降观测资料的整理无非是要求将原始数据由平差软件生成的数据整理汇总，可以直观的、清晰的反应工程主体的沉降变形情况，主要有将高程文件汇总到一个工作簿中，由每一个监测点、每一期的高程计算得到累计沉降量汇总表，再由累计沉降量汇总表生成沉降量曲线图，这些工作都要由Excel来完成。以津秦客专滨海北站路基段为例，本段路基共设80个监测断面，每一监测断面3个监测点，还有11座涵洞，每座涵洞至少4个监测点，这样计算本段路基至少284个监测点，每一个监测点都要生成一个汇总表和曲线图，如果手动来做这项工作将会使我们浪费很多的时间和精力，并且还容易出现错误。虽然刚开始接触的时候，笔者也并没有找到更多的捷径，但是经过不断的学习，摸索出了适合于这项工作的方法，这在以后的工作中为我节省了很多的时间，可能之前几个小时的工作现在几分钟就能完成，这就使一个人完成别人几个人才能完成的工作得以实现。周而复始就形成了一个良性循环，在工作中节省的时间能够去做一些其他的工作或者学习更多的知识，而这些又能促使我们把工作做得更好，那么如果不能做到这些，就会进入另一种极端。下面就开始探讨笔者在整理工作中总结的经验和方法。对于原始数据和工作手簿的汇总笔者就不再赘述，这里笔者只介绍应用到Excel的整理工作，Excel版本是Excel2003。三、成果表的汇总我们要做的是由平差软件生成的高程成果表汇总到名称为成果表的工作簿，可能我们首先想到的就是直接复制粘贴，如果仅仅是几个表当然无可厚非，然而，每期沉降观测数据都有一个这样的表，要复制多达几十个表可不是一件容易的工作，那么笔者就想到能不能有一种方法可以省却其中的一些繁琐而重复的步骤，从而达到提高工作效率的目的。1、移动工作表笔者找到一种方法：打开名称为成果表的工作簿和由平差软件生成的高程成果表工作簿，激活含有高程数据的工作表，选择标签，点击右键，在弹出的菜单中选择“移动或复制工作表项”，点击后我们可以看如下图所示的窗口。在“工作簿：”下面的下拉列表项选择“成果表.xls”，选择“移至最后”点选“建立副本”的复选框，点击“确定”，我们会发现在成果表工作簿中多了一个工作表，然后将标签名称改为当期观测日期，这样，就完成了一期数据的汇总工作。在以后的汇总过程中我们还想能不能有更快的方法呢？或者可以省去一些步骤，取而代之的是一个快捷键或执行一项功能就可以完成工作，那样的话不就能提高更多的工作效率，答案是肯定的，微软的设计人员为我们提供了VBA，这就为我们实现想法提供了帮助。2、录制宏Excel的优势就是可以录制宏，可能有些人对宏不太了解，笔者理解为，宏就是一个过程，一个要完成某项工作而实施的过程。我们对宏有了了解之后就开始录制工作，选择“工具”“宏”“录制宏”，会弹出如下窗口，选择宏名称，我们可以取一个比较直观反映这个过程的名字，Excel也提供了汉语名称，本过程名称设为“移动”；设定快捷键，这里不能与Excel自身快捷键冲突，否则Excel会默认自身的快捷键而导致宏快捷键无法执行，并且宏快捷键也只能为字母，那我们就选择一个合适的字母键，本过程快捷键设为“Ctrl+y”；选择宏需要保存的工作簿位置，Excel提供了三个选项供我们选择，“当前工作簿”、“个人宏工作簿”、“新工作簿”，笔者建议选择“当前工作簿”，这样宏就可以保存在当前工作簿；对录制的宏做一个说明，这一步可以省略，因为它并不会对我们的录制宏的运行带来影响。点击确定，重复“步骤1”的操作，选择 “工具”“宏”“停止录制”。选择 “工具”“宏”“Visual Basic编辑器”，或者按“Alt+F11”快捷键，进入Visual Basic编辑器，如下图所示：由于篇幅有限，笔者不再赘述Visual Basic编辑器各个部分的名称和功能，Visual Basic编辑器代码窗口可以看到录制的宏代码：Sub 移动() 移动 Macro 宏由 USER 录制，时间: 2013-5-8 快捷键: Ctrl+y Windows(高程成果表.xls).Activate Sheets(Sheet2).Select Sheets(Sheet2).Copy After:=Workbooks(成果表.xls).Sheets(5) Application.WindowState = xlMinimizedEnd SubSub后面为过程名称，过程名称下面三行为注释，前面都有撇号，在Visual Basic编辑器代码窗口为蓝色字体，并不参与Sub过程的执行，显示了过程名称，录制人、录制时间及快捷键。以下几行代码可以解释为：激活“高程成果表”“选择sheets2工作表”“移动复制到成果表的sheets5工作表后”“最小化活动工作簿窗口”。执行“Ctrl+y“快捷键运行此过程我们就得到和”步骤1“一样的结果，这样，本来几步要完成的工作我们仅仅一步就可完成，如果要移动大量的工作表的话将会为我们节省大量的时间。但是，录制的代码往往存在或多或少的局限性，比如要移动的工作簿名称不对，执行过程就会导致错误，并且存在不必要的代码而使过程执行变长，这就需要我们进行一些修改，才能提高其适应性。以上过程修改如下：Sub 移动() Sheets(Sheet2).Copy After:=Workbooks(成果表.xls). _ Sheets(Workbooks(成果表.xls).Sheets.Count)End Sub笔者删除了一些容易引起错误和冗余代码，将Sheets(5)修改为Sheets(Workbooks(成果表.xls).Sheets.Count),Workbooks(成果表.xls)。Sheets(Workbooks(成果表.xls).Sheets.Count)返回成果表中最后一个工作表,这就避免了因工作表不同而修改代码的麻烦,修改后的代码较录制的宏代码看起来更加的美观和简洁。在成果表工作簿打开的情况下，每次打开高程成果表后执行以上过程，高程成果表就轻而易举的汇总的成果表中了。当然，并不是所有的动作都可以被录制宏录制下来，我们还必须掌握一定的VBA编程语言，才能更好的让VBA为我们工作。四、汇总表汇总表是整套资料的核心部分，包括沉降量曲线图都是由汇总表数据生成，有了汇总表和曲线图就可以让我们对数据和沉降趋势有更直观的认识，我们需要去做的就是清楚这个表需要哪几种数据就是。由汇总表内容可以知道需要以下数据：(1)、观测日期；(2)、路基填筑高度；(3)，本期沉降量。观测日期和沉降量只能手动输入，那么，就只有本期沉降量需要我们计算得出。1、本期沉降量的计算前面我们已经完成了成果表的汇总，成果表中包括了每一期、每一监测点的高程，由此我们就可以由上期高程减去本期高程得到本期沉降量，然后在新工作表中将日期和本期沉降量呈现出来，这样就得到了汇总表所需要的数据。计算思路已经有了，如何来实现呢？笔者想到了VBA，用VBA可以很轻松的实现此过程。主要分为三步：(1)、在成果表中插入新工作表，名称改为“汇总”，从A2单元格开始将观测日期输入A列，将所有工作表中高程数据汇总到“汇总”工作表中，由于格式不同，本过程通过数组实现数据的转置；(2)、在每一监测点高程数据后面插入一空行；(3)，在插入的空行中计算出本期沉降量。由于观测期数较多，笔者只以后面几期数据为例。过程代码如下：Sub 汇总() Dim i As Long, j As Long, a As Long, b As Long, arr() a = Worksheets.Count - 1 b = Sheets(1).a65536.End(xlUp).Row ReDim arr(1 To b) For i = 1 To a For j = 5 To b arr(j) = Sheets(i).Cells(j, 3) Sheets(汇总).Cells(i + 1, j - 3) = arr(j) Next j Next i Call 插入行 Call 计算End SubSub 插入行() Dim i As Long, j As Long j = Sheets(汇总).Cells(2, Columns.Count).End(xlToLeft).Column For i = j To 3 Step -1 Sheets(汇总).Cells(2, i).EntireColumn.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Next iEnd SubSub 计算() Dim i As Long, j As Long, a As Long, b As Long, sl As Long b = Sheets(汇总).a65536.End(xlUp).Row a = Sheets(汇总).Cells(2, Columns.Count).End(xlToLeft).Column + 2 For j = 3 To a Step 2 For i = 3 To b Sheets(汇总).Cells(2, j) = 0 Sheets(汇总).Cells(i, j) = (Cells(i - 1, j - 1) - Cells(i, j - 1) * 1000 Next i Next j For sl = a To 2 Step -2 Columns(sl).Delete Next slEnd Sub在“汇总”过程中，用到了“Call 插入行”和“ Call 计算”语句，这两句表示在“汇总”过程运行后引用“插入行”和“计算”两个过程，这样我们就可以只运行“汇总”过程，而不必每一个过程都要运行一次。篇幅有限，其他语句不做过多解释。要运行“计算”过程，我们选择“工具”“宏”“宏”，也可以按“ALT+F8”快捷键，弹出如下窗口：选择“计算”“执行”。我们也可以插入一个窗体按钮，将窗体名称改为“汇总”，将“计算”过程指定给此窗体，每次只要点击此窗体就可以运行“计算”过程。运行后的结果如下图：2、完成汇总表本期沉降量已经计算完成，接下来就要完成汇总表的汇总工作，由于汇总表中前期数据已经汇总完成，首先要判断表格中的空行是否足够后期数据的使用，不够的话就要插入一定的空行，VBA可以很好的代替我们来自动判断空白行，并在不足的情况下插入空行，过程代码如下：Sub 选择() Dim i As Integer, j As Integer, a As Integer, b As Integer, c As Integer Dim num As Integer, shnum As Integer, r As Integer, cnt As Integer Application.Windows(成果表.xls).Activate a = Sheets(汇总).a65536.End(xlUp).Row - 1 Application.Windows(路基汇总表.xls).Activate shnum = Worksheets.Count For j = 1 To shnum b = Sheets(j).a65536.End(xlUp).Row c = Sheets(j).b65536.End(xlUp).Row If b - c - 1 = a Then Exit Sub End If If b - c - 1 a Then For i = 1 To (a - (b - c) Sheets(j).Select Rows(b).Insert Shift:=xlShiftDown Next i End If cnt = Sheets(j).a65536.End(xlUp).Row - 1 Rows( & c & : & c & ).Select Selection.Copy Rows( & c & : & cnt & ).Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Next jEnd Sub执行此过程，VBA很好的完成了任务。复制成果表中的日期，选择成果表中输入好的日期，然后按住Shift键，点击汇总表中的第一个工作表和最后一个工作表，就将汇总表中全部工作表选中，选择要复制到的单元格，粘贴即可复制到所有的工作表相同位置的单元格中。将本期沉降复制到汇总表的过程如下：Sub 沉降量() Dim cjnum As Long, jhnum As Long, ljshnum As Integer, ljh As Integer, arr() Application.Windows(成果表.xls).Activate cjnum = Sheets(汇总).a65536.End(xlUp).Row Application.Windows(路基汇总表.xls).Activate jhnum = Sheets(1).b65536.End(xlUp).Row ljshnum = Worksheets.Count ReDim arr(3 To cjnum) For ljh = 1 To ljshnum Application.Windows(成果表.xls).Activate Cells(3, ljh + 1).Resize(3, 1).Copy Application.Windows(路基汇总表.xls).Activate Sheets(ljh).Cells(jhnum + 1, 5).Resize(3, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets(Array(ljh).Select Range(B33:D34).Select Selection.AutoFill Destination:=Range(B33:D37), Type:=xlFillDefault Sheets(Array(ljh).Select Range(F33:G34).Select Selection.AutoFill Destination:=Range(F33:G37), Type:=xlFillDefault Sheets(Array(ljh).Select Range(M33:O34).Select Selection.AutoFill Destination:=Range(M33:O37), Type:=xlFillDefault Next ljhEnd Sub本过程“Application.Windows(路基汇总表.xls).Activate”下面几句代码将把其他列中数据行中的值和公式自动填充到含本期沉降量的最后一行中。这样，一个完整的汇总表就轻松完成了。既然可以将数据复制到所有选择的工作表中，那么Excel是否也可以复制所有已选择的工作表数据到相应的工作表中呢？在实践过程中笔者发现这是可行的，那么我们就想到能不能把成果表中的汇总数据按监测点的不同分配到不同数量的工作表中？数据透视表可以实现这样的功能。新建一个工作表，将汇总工作表中的数据通过“选择性粘贴”功能复制到新工作表中，形式如下：任意选择新工作表中数据区域的单元格，选择“数据”“数据透视表”，步骤如下：(1)、在“数据透视表和数据透视图向导”项选择默认值，点击“下一步”；(2)、将数据区域全部选择，点击下一步；(3)、将“数据透视表列字段”中“工作表名称项” 拖至页字段拖至处，将所有日期项拖至数据项拖至处，关闭“数据透视表列字段”；(3)、选择“数组透视表”菜单栏右上角的倒三角下拉菜单，选择“数据透视表”，在弹出的下拉菜单中将“分页显示”前打勾； (4)、任意选择一个汇总数据中的单元格，点击“数据透视表”菜单栏右边的分页显示；所有步骤执行完成之后，就能发现在成果表中生成了以所有监测点名称命名的新工作表，每个工作表中只包含了本监测点的监测数据，这样我们选择所有以监测点命名的工作表，复制数据区域，然后选择汇总表工作簿中相应数量的工作表，将复制好的数据粘贴到已选择的数据区域，然后将其他列的数据自动填充，就完成了汇总表的汇总工作。在工作中我们会不断的遇到新问题，然后去想办法解决，而问题的解决往往会有很多种方法。可能根据不同的环境我们选择不同的解决方法，其实笔者更倾向于这两种方法中的数据透视表法，因为此方法灵活性较强，当然也不能一概而论，对于已经编制好了过程代码的话，VBA要效率更高一些。3、沉降量曲线图的绘制图表的存在就是为了更加生动和形象的反映数据，想要制作图表，必须有和图表相对应的数据，这样的数据在汇总表中已经存在了，传统的方法是选择“插入”菜单，选择“图表”，然后再选择插入的图表类型，选择数据，设置图表选项，这么繁琐的步骤，而且在有大量不同的工作表的情况下，任务就显得艰巨的多，那么我们是否可以利用VBA来自动生成图表工作表，并且判断工作表的数量生成相应数量的图表工作表呢？答案是肯定的。计算机语言是一个系统性和程序化很强的语言，在写较为复杂的过程代码前，我们必须要有一个明确的思路，如果思路不清晰，可能后期会需要大量的时间来调试运行，甚至导致结果的失败。如何遍历所有工作表生成对应的图表工作表是本过程的难点所在，问题在于每生成一个图表工作表，工作簿中的工作表数量就相应增加一个，如果采用循环语句从第一个工作表开始，那么过程就会因为图表工作表的生成而导致错误。那么该如何解决呢？笔者想到了一种方法，可以使用For i = b To 1 Step -1(b为工作表数量)语句从最后一个工作表往前循环遍历所有工作表，这样就不会因为图表工作表的生成而影响过程的运行。过程如下：Sub 生成曲线图() Dim i As Long, b As Long, a, rng As Range, mychart As Chart Sheets(1).Select b = Worksheets.Count For i = b To 1 Step -1 On Error Resume Next 忽略错误 Set mychart = Charts.Add mychart.ChartType = xlLineMarkers 选择图表类型 a = Sheets(b + 1).a65536.End(xlUp).Row - 1 Set rng = Sheets(b + 1).Range(A5:A & a & ,D5:D & a & ,G5:G & a & ,O5:O & a & ) mychart.SetSourceData Source:=rng, PlotBy:=xlColumns 选择源数据 mychart.Location Where:=xlLocationAsNewSheet 位置为新工作表 With mychart .HasLegend = False 不显示图例 .HasTitle = True 图标标题显示 .ChartTitle.Characters.Text = 荷载-时间-沉降量/沉降速率图 .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = 日期(t)d .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = 沉降量(mm)/沉降速率(mm)/d 填土高度(m) End With With mychart.ChartTitle.Characters(Start:=1, Length:=15).Font .Name = 宋体 .FontStyle = 常规 .Size = 14 End With With mychart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Font .Name = 宋体 .FontStyle = 常规 .Size = 12 End With With mychart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Font .Name = 宋体 .FontStyle = 常规 .Size = 12 End With With mychart.Axes(xlCategory) .MajorUnit = 30 .MajorUnitScale = xlDays .MinorUnit = 15 .MinorUnitScale = xlDays .Crosses = xlAutomatic .AxisBetweenCategories = True .ReversePlotOrder = False .TickLabels.AutoScaleFont = True .TickLabels.Orientation = xlHorizontal .TickLabels.NumberFormatLocal = yyyy-m-d; .TickLabels.Alignment = xlCenter .TickLabels.Offset = 100 .TickLabels.ReadingOrder = xlContext .TickLabels.Orientation = xlUpward 显示顺序 .HasMajorGridlines = True 主要网格线显示 .HasMinorGridlines = False End With With mychart.Axes(xlValue) .MinimumScale = -10 .MaximumScaleIsAuto = True 默认由excel自动选择 .MinorUnit = 0.5 .MajorUnit = 2 .Crosses = xlAutomatic .ReversePlotOrder = True Y轴次序翻转 .ScaleType = xlLinear 刻度单位类型 .HasMajorGridlines = True .HasMinorGridlines = False .TickLabels.NumberFormatLocal = 0.0;red0.0 End With mychart.Axes(xlCategory).MajorGridlines.Select With Selection.Border .ColorIndex = 57 .Weight = xlHairline .LineStyle = xlDot End With mychart.Axes(xlValue).MajorGridlines.Select With Selection.Border .ColorIndex = 57 .Weight = xlHairline .LineStyle = xlDot 虚线显示 End With mychart.PlotArea.Select 选择图标绘图区 Selection.Left = 20 Selection.Top = 50 Active