2020- 70 -
  摘　要：办公软件OFFICE/WPS 是目前使用频率最高的软件之一，在高强度使用者当中，大多数的工作负荷主要集中在复制、粘贴、筛选、求和等基础工作上，这些工作均具有重复性、机械性等特点，占据了使用者大量的时间和精力。如果利用办公软件中内置的宏程序接口，针对性地处理，可一键进行文件的汇总、统计、生成带有直接打开文件的超链接等功能操作，节约操作者大量的时间和精力，实现办公智能化。  关键词：办公软件；函数；VBA
The Realization of Office Intelligent Application by Using Functions and VBA
Niu Shigang
(Technology Center of Jiuquan Iron and Steel (Group) Corporation, Jiayuguan, Gansu,735100)
  Abstract: Office software OFFICE/WPS is one of the most frequently used software currently. Among high-intensity users, most of the workload is mainly concentrated on basic tasks such as copying, pasting, screening, and summing. These tasks are repetitive and mechanical and occupy a lot of time and energy for users. If you use the built-in macro program interface in the office software to perform targeted processing, you can perform functions such as file summary, statistics, and generation of hyperli
nks with directly opened files with one click, saving the operator a lot of time and energy and realizing office intelligence.
  Key words:  office software; function; VBA
1  前  言
  在办公软件系列中，重复性的工作尽可以使用脚本代码进行操作，例如把Word 文件中的图片按顺序全部拷贝至一个新文档中，而Excel 相较于Word、PPT 等软件，数据处理量和操作多样化等方面都是最多的，不同的使用强度和使用方法，软件的表现和使用效果也不尽相同。以
利用函数和VBA 实现办公智能化应用
牛世刚
（酒钢集团技术中心，甘肃，嘉峪关，735100）
2020- 71 -
Excel 软件使用为例，利用好软件自带“条件格式”和“表格样式”，可以瞬间使表格面貌一新，并规避一些发生错误的风险[1]。
  在业务统计和财务管理人员高强度使用人中，若能熟练掌握Excel 内嵌函数，如Sum、Sumif（s）、Counta、Countif、V（H）lookup 等函数，将会让工作效率成倍提高，工作心情也随之舒畅很多。当然在实际使用过程中，真正遇到的问题非常复杂，单一函数解决问题显得异常困难，需要使用两个及以上函数的叠加使用，如Index 函数和Match 函数的组合、If 函数+Find 函数+Lookup 函数的组合等。如果有了函数的基本用法为基础，用VBA 代码作为另一项辅助工具，原本很多棘手问题也就可以迎刃而解了。“数据录入”和“数据处理”是办公软件应用两个基本面，如果在刚开始数据录入的过程中，智能化的操作和处理就开始了，不仅会节约大量的时间，而且最大限度避免出现错误，节省后期检查的时间，使工作变得简单愉快。
2  数据录入
2.1 函数法操作
  在数据录入过程中，序号的输入是基本项，对应的序号可以使表格内容一一对应，更简洁明了。录入好的表格在后期整理过程中，难免增删修改，已经排好的序号会发生错位。采用可变动的序号，无论发生任何修改，软件均为其所在位置重新正确编号，且不易发生错误。操作者录入过程中，如果被打断没有一次性录入完毕，或者录入过程本身就是不连续的，会出现重复录入的情况，办公软件中，操作者可以使用函数和条件格式两种方法，对相应的情况进行提示。2.1.1 函数法填充序列号
  “序号”栏的填写，常见的操作一般为先填入1，然后待鼠标变成十字光标后使用下拉方式填充，或者选定需要填充的单元格，依次按下
Ctrl + E 使用快捷键进行填充。这两种方式虽可以达到使用目的，但在填写过程中，如果中间行的项目被删除或移位，相应的序号就会发生改变，最终成稿时序号排列就会混乱。
  函数法：序号栏下空格内，先键入“=”（英文状态下），然后键入“ROW()-1”后按下Enter 键，序号就此生成，公式中“1”就是标题栏所占的位置。按照这种方式再采用下拉填充的方式将剩余的序号栏全部填入，就可以得到我们所需要的的行号了，而且不会因为删除行等操作改变序号的值[2]。
  可这种方式还是不够智能，如果“填报内容”栏有内容，序号就自动生成，“填报内容”栏没有内容，就不显示序号，表格生成就更方便、更节省时间了。方法仍然使用函数，但需要两个函数叠加使用，即函数迭代法。
2.1.2 函数迭代法：序号栏下空格内，先键入“=”，然后键入“IF(E2<>"",ROW()-1,"")”，在这个函数表达式中，如果（if）E2单元格有填入的内容，那么就显示相应的序号（“ROW()-1”）；如果E2单元格是空格，那么什么也不显示（“”）。2.2 在“条件格式”中使用函数公式resize函数vba
  利用条件格式，可以轻松实现录入序列号的功能，而且条件格式的功能更加强大，能实现的功能更多，
如填入固定值，发现录入的重复项，对满足触发条件的数据进行颜填充、弹出提示框等，在数据录入初期就帮助操作者及时发现错误进行修正[3]。利用条件格式，也可以实现表格边框的自动生成，若填入数据后，表格边框自动生成，未输入数据或已填入的数据删除后，表格边框也随之去除，可以帮助操作者迅速发现未填入项和节省设置表格边框的时间。2.2.1 实现自动添加边框
  实现目的：在一张空白的表格当中，如果我们在第一个单元格输入内容，则本行自动自动边框，如果删除第一个单元格内容，则本行的边框自动清除。
2020- 72 -操作方法：在Excel 界面的开始栏，点击“条件格式”，选择“新建规则”，在弹出的对话框中，选择最后一项“利用公式确定要设置格式的单元格”。在“只为满足以下条件的单元格设置格式（O）”下方的输入框中输入相应的条件，设置相应的触发条件和实现的方式。
  以自动添加相应的表格边框为例，假设在
Excel 表中第2、3、5行有内容，表框自动生成，第4行没有内容，边框自动清除。在条件格式中选择“新建规则”，选择最后一项“使用公式确定要设置格式的单元格”，在下面的输入框中输入“=$A2:$E2<>""”，点击“格式”标签，选择边框的样式后点击确定即可，操作过程如图1所示。
2.2.2 自动检查重复录入项
  利用条件格式，检查已录入的表格内容有否有重复录入的情况，选择“重复值”，再按照操作者的习惯选择将设置为不同的颜的文本颜来进行区分。如果新录入的文本有颜的变化则说明此条文本与已录入的文本发生了重复，以此提醒操作者。
3  数据汇总和统计
  在数据汇总过程中，如果表格的格式是固定的，那么可以使用相应的函数进行处理。但实际运用中，汇总工作需要将各部门或各子部门分别报送的文件分别打开，然后进行复制和粘贴，如果相应部门的数
量较多，工作量虽然简单重复，但工作量还是很大。单表函数处理这些问题力不能及，使用链接更新的方式或者使用“合并表格”的方式固然可行，但仅限于格式、内容均相同且较简单的文件的操作[4]。对于个性化的操作，使用VBA 代码效率更高。3.1 汇总
  汇总数据时，数据分散于各个子工作表中，汇总统计工作的紧前工作，需要合并当前目录下所有工作簿的全部工作表数据。如果只有两、三个字表，工作量手动完成无压力实现，但如果有十几个、几十个字表，而每个字表又分为“sheet1”、“sheet2”、“sheet3”，此时的工作量无疑是巨大的。
  假设数据汇总工作，需要合并A 公司、B
公司、C 公司分别上报的三个子工作簿，而每个子工作簿内又均有“sheet1”、“sheet2”等不同数量的工作表。程序运行首先需要遍历整个文件夹，自动将文件夹下所有文件顺序显示，打开各个文件，对相应的数据内容复制粘贴入“数据汇总工作簿”中的指定位置。程序执行结束后，对操作者给出相应的完成提示“共处理了几个工作簿”和工作簿的名称。汇总演示见图
2。
图1  利用条件格式自动添加边框
2020- 73 -
  假设每个表格的格式和内容标题均一致，代码如下：
　dim mypath$, myname$, awbname$,wbn$,box$　dim wb as workbook,  　dim g&,num&
　application.screenupdating = false  mypath = activeworkbook.path  myname = dir(mypath & "\" & "*.xls")  awbname = activeworkbook.name  num = 0
　do while myname <> ""  　if myname <> awbname then
  set wb = workbooks.open(mypath & "\" & myname)
  num = num + 1
  with workbooks(1).activesheet
    .cells(.range("a65536").end(xlup).row + 2, 1) =  　left(myname, len(myname) - 4)    for g = 1 unt
  w b.s h e e t s (g ).u s e d r a n g e.c o p y  .c e l l s (.range("a65536").end(xlup).row + 1, 1)  next
  wbn = wbn & chr(13) & wb.name  　wb.close false  end with end if myname = dir loop
range("a1").select
application.screenupdating = true
msgbox "共合并了" & num & "个工作薄下的全部工作表。如下：" & chr(13) & wbn, vbinformation, "提示" end sub
  处理结果见图3
所示。
图2  数据汇总工作量演示图
图３  数据汇总图示
2020- 74 -
3.2 统计
  在数据汇总工作完成后，汇总完毕的数据往往非常杂乱，后续的统计工作中，不可避免会出现重复值，例如“产量”等。为使汇总结果清晰明了，在最终成表时，需要将重复项目的名称保留，统计过程，需要对重复值出现的次数进行累加，并把其值进行累加计算，这种情况，就需要使用数据字典的类型代码。为避免文件在传递过程中发生的问题，对应采取后期绑定的方式进行处理[5]。相应的代码如下：  Sub 字典示例（）  Dim dic
    Set dic = CreateObject("scripting.dictionary")  Dim arr
  　arr = Sheet1.Range("a1").CurrentRegion  　Dim NewI%
  　For NewI = 1 To UBound(arr)    If arr(Newl, 1) <> "" Then
    dic(arr(Newl, 4)) = dic2(arr(NewJ, 4)) + 1    End If    Next
  Sheet1.Range("l11").Resize(dic2.Count, 1) = Application.Transpose(dic.Keys)
  Sheet1.Range("m11").Resize(dic2.Count, 1) = Application.Transpose(dic.Items)End Sub
4  结束语
  在Excel 表格实际使用中，使用函数和代码的目的，是使重度数据处理者能从繁重的简单重复性工作中提高效率，把节约的时间投入到更有意义的工作当中去。函数和代码可以轮动使用也可以结合使用，拘泥于某一种方式，增加了解决问题的时间，与提高效率的初衷背道而驰。如果表格格式固定，且表格内容处理属于共性化的问题，使用办公软件内置函数进行统计及和计算更具效率；个性化的操作，例如根据不同类别进行文件汇总，或去除已有内容的重复性，使用本文中“数据字典”代码进行处理，则更加灵活和方便。
参考文献
[1] 中文版Excel 2003宝典[M].北京：电子工业出版社.2004.[2] Excel 2002从入门到精通[M].北京：电子工业出版.2002.
[3]  郭坤.基于Excel 和VBA 编程的院系人员管理信息系统设计[J].河北软件职业技术学院学报,2019,03. [4] 刘柱鸿，黎鑫，彭元辉.基于Excel VBA 的教学秩序督查报表管理系统设计与实现[J].软件工程,2018,04.[5] 苏进.探究如何在EXCEL 中使用VBA 编程处理数据[J].数字技术与应用,2016,01.
作者简介：牛世刚（1976-）男，汉，高级工程师，1999年毕业于武汉科技大学大学冶金机械专业，现在酒钢集团
技术中心主要从事论文外发管理工作，邮箱：**********************。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。