论文部分内容阅读
摘 要：Excel是使用非常广泛的办公软件体系之一，自身带有多种类型、可实现多种功能的内置函数，但仍不能完全满足一些用户的特殊功能使用要求。基于此，Excel提供了宏功能，满足用户通过自定义的方式开发独特功能的使用需求。利用VBA的宏编程技术，通过自定义Excel函数，实现Excel单元格数据中指定字符间文本的批量去除功能，同时提供指定字符去除或保留两种实现模式的解决方案。
关键词：VBA;指定字符;文本去除
中图分类号：TP317 文献标识码： A 文章编号：2096-4706（2021）02-0032-03
Abstract：Excel is one of the more widely used office software systems. It has many types of built-in functions to realize many functions，but it still cannot fully meet the usage requirements of special functions of some users. In view of this，Excel provides macro functions to satisfy user’s usage requirements that develop unique functions through self-definition way. Using the macro programming technology of VBA and through self-definition of Excel function，the batch removal function of the text between the specified characters in the Excel cell data is realized. At the same time，two implementation modes of solutions removing or retaining of the specified characters are provided.
Keywords：VBA;specified character;text removal
0 引 言
Excel是微软公司Office办公软件体系中的主要模块之一，Excel为普通用户提供各式各样的数据处理、数据分析、数据计算方法。但在一些需要独特操作，使用小众的运算方面，利用Excel提供的各种函数可以分次组合计算实现独特的功能，在实际操作中，虽然能够满足用户的特殊需求，但重复性操作步骤多，影响实际工作效率，此时可考虑利用Excel的宏开发功能，对重复性步骤进行整合编程处理，以自定义运算的方式简化工作步骤。
1 问题提出
在实际工作中，经常遇到一些问题，需要批量去除单元格数据中的括号及括号中的内容，并且需要去除的内容在单元格字符串中的位置并不固定。如在学生学籍管理工作中，规范的专业名称不带括号及括号中的专业方向，但多数原始数据中的专业名称后均带有括号及括号中的专业方向。对于此类数据，在数据规范化处理时，需要批量去除专业名称后面的括号及括号内的专业方向名称。
对于此类问题，可通过Excel的筛选，手动小批量删除同内容数据的括号及括号中的文本，或者使用Excel提供的MID、LEFT、RIGHT等字符串函数根据原始数据的不同进行分类处理。不管采用哪种方式，在实际操作中均存在操作步骤多、使用烦琐的问题。如何利用Excel的宏功能将步骤多而烦琐的操作进行集成化处理，是本文将要着重讨论和解决的问题。
同时，可将此类问题引申为，对于给定的已知Excel单元格字符串，可通过指定开始位置的字符和结束位置的字符来实现Excel单元格中字符串的特征化批量删除。这尤其适用于需要批量去除的文本在整个字符串中的位置不固定的情形。
2 问题分析
以实际问题为例进行分析，如图1所示Excel表格，已知A列，批量生成B列。
通過分析上图可以得出，需要批量去除的字符串均在括号内并且连同括号一同去除。同时，A列数据中需要去除的字符串缺少规律性且在原字符串中的位置并不固定。
Excel自带的LEFT、RIGHT函数，具有从左往右和从右往左按字符数生成新字符串的功能。故解决问题的核心可转化为求A列单元格数据字符串中括号位置的问题。
利用Excel自带的函数实现功能的具体步骤分析为：
（1）利用Excel自带的函数LEN实现字符串长度的计算，该长度可作为程序循环模块中的循环次数控制变量。
（2）利用Excel自带的函数MID实现字符串文本的逐个提取，通过将提取出的字符和指定字符相比对，可以确定是否到达删除字符串的起始位置及结束位置。
（3）当判断出MID函数提取的字符等于指定开始字符时，可记录当前字符在字符串中的位置数。
（4）当判断出MID函数提取的字符等于指定结束字符时，可记录当前字符在字符串中的位置数。
（5）通过记录的位置数，综合利用Excel自带的LEFT、RIGHT函数，获取所需的头尾字符串，使用Execl提供的字符串连接运算符“&”连接形成最终需要的字符串。
上述步骤中，为了实现指定字符的去除与否控制，在自定义Excel函数时，通过附加一个判断参数，进一步拓展自定义函数的功能，使自定义函数具有按照用户要求实现是否保留指定字符的功能。 综上所述，利用Excel自身支持的VBA宏编程功能，通过自定义实现符合用户特殊需求的函数，依赖该函数让Excel具有一次性解决上述问题的能力，简单直接的解决同类问题。
3 问题解决
3.1 VBA简介
Visual Basic for Applications（VBA）是Visual Basic的一种宏语言，主要用来扩展Windows的应用程式功能，特别是Microsoft Office软件，也可说是一种应用程式视觉化的Basic Script。1994年发行的Excel 5.0版本中，即具备了VBA的宏功能。
3.2 基本算法描述
首先，设需要处理的目标单元格为S，其次，利用LEN求源字符串的字符个数，即长度，再次，以长度为循环依据，使用判断语句逐字符判断是否为指定开始字符或指定结束字符，如是，记录指定开始字符或指定结束字符位置数，最后，利用已记录位置及Excel自带函数LEFT和RIGHT综合生成用户需要的单元格内容。
3.3 基本算法实现
设有字符串s为a1，a2，…，an，b1，b2，…，bm，c1，c2，…cp，其中n，m，p均为随机自然数，需求a1，a2，…，an，c1，c2，…cp或a1，a2，…，an，x，y，c1，c2，…cp。
设已知字符串长度为c则，c=n+1+m+1+p;
设字符x位置为x1，则x=n+1;
设字符y位置为y1，则y1=n+1+m+1;
即a1，a2，…，an，c1，c2，…，cp=LEFT（s，x1-1） & RIGHT（s，y1+1），a1，a2，…，an，x，y，c1，c2，…cp=LEFT（s，x1） & RIGHT（s，y1）。
3.4 程序基本流程图
自定义Excel函数的程序基本流程图如图2所示。
流程图的主要内容为：
（1）前期数据处理包括函数定义、变量定义，求目标字符串长度并以该长度为依据设立循环。
（2）每取到字符串中的一个字符，便利用指定开始字符、指定结束字符做判断。如相等，则记录该字符在字符串中的位置，如不相等，则继续取下一个字符，循环往复，直到遍历字符串中的所有字符。
（3）利用函数定义中指定的功能控制参数进行分支选择，结合上一步中记录的指定开始字符和指定结束字符的位置数，按照用户需求计算保留指定字符或不保留指定字符的目标字符串。
3.5 功能实现代码
利用Excel的VBA宏编辑器，新建一个模块并输入以下代码：
Function tqnr（mb As Range， fh1 As String， fh2 As String， hf As Boolean） As String
//函数定义，函数名tqnr，数据类型字符串型。该函数同时定义4个参数，第一个参数为mb，用于单元格引用控制，数据类型单元格型;第二个参数为字符型变量fh1，用于接收用户指定的开始字符;第三个参数为字符型变量fh2用于接收用户指定的结束字符;第四个参数为布尔型功能控制参数，用于接收用户对功能的選择要求，如为TRUE，则表示用户需要去除指定的字符，如为FALSE，则表示用户不需要去除指定的字符。
Dim c As Integer //定义整型变量c，用于存放需处理字符串的长度数值。
If hf = True Then //通过参数hf控制是否需要保留指定字符的功能选择。
c = Len（mb.Text） //求需处理字符串的长度数值。
d1 = 1
d2 = 0//定义整形变量d1和d2，用于存放指定开始字符和指定结束字符在字符串中的位置数，同时对d1和d2赋初值做初始化处理。
For i = 1 To c//循环开始，通过循环遍历需处理字符串中的每个字符。
txt = Mid（mb， i， 1） //定义字符型变量txt用于临时存放需处理字符串中的每个字符。
If txt = fh1 Then d1 = i //确定指定开始字符在字符串中的位置。
If txt = fh2 Then d2 = i //确定指定结束字符在字符串中的位置。
Next i
tqnr = Left（mb， d1 - 1） & Right（mb， c - d2） //输出函数值，不保留指定字符及字符间内容的字符串。
End If
If hf = False Then
c = Len（mb.Text）
d1 = 1
d2 = 0
For i = 1 To c
txt = Mid（mb， i， 1）
If txt = fh1 Then d1 = i
If txt = fh2 Then d2 = i
Next i
If d1 <> 1 Then
tqnr = Left（mb， d1 - 1） & fh1 & fh2 & Right（mb， c - d2） Else： tqnr = Left（mb， d1 - 1） & Right（mb， c - d2） //输出函数值，保留指定字符，但去除指定字符间内容的字符串。
End If
End If
End Function
4 自定义函数使用说明
利用Excel的宏编程功能，自定义了函数tqnr，该函数同时带有四个参数。第一个参数为函数处理目标的指定（单元格型）;第二个参数为去除文本开始字符的指定（字符型）;第三个参数为去除文本结束字符的指定（字符型）;第四个参数为是否保留指定字符的控制（布尔型）。
在如图3所示的Excel工作表中，通过在B2单元格中输入自定义的函数tqnr，使用填充句柄进行填充后，可以快速地得到如图4所示的结果。
5 结 论
在分析Excel宏开发功能的基础上，对于本文提出的具体问题，即从多种类字符串单元格中，对指定字符及字符间的内容批量去除功能，进行了步骤分解和分析。在此基础上，提出了功能实现的算法描述，并通过VBA的宏编程功能开发了功能实现代码。
在实际工作中，面对几十个不同的专业名称，成千上万条学生学籍信息记录，相较以往的逐专业筛选，逐专业批量复制的处理方式。该方法不受专业类型多少，数据记录条数多少的影响，一次性、批量化地得出用户所需的数据。极大地提高了工作效率，并且，用户处理的数据量越大，该方法的效率提升越明显，在实际工作中具有极高的使用价值。
参考文献：
[1] 高楠，李紅霞.利用Excel的宏功能实现多数据类型单元格中纯数字的提取 [J].计算机产品与流通，2017（9）：197.
[2] 李晓玫，杨小平.Excel中的VBA程序设计 [J].四川师范大学学报（自然科学版），2004（4）：96-99.
[3] 杨天生.VBA在高效办公中的应用 [J].机械工程师，2017（9）：145-146.
[4] 张怡芳.Excel自定义函数的编制与实例 [J].计算机与现代化，2001（2）：130-135.
[5] 孔德宏.在EXCEL中基于VBA的学生基本情况调查表的设计与实现 [J].企业导报，2011（6）：254-255.
[6] 高楠，石蕊，李红霞.根据身份证号直接计算年龄的EXCEL实现 [J].计算机产品与流通，2017（10）：37.
作者简介：高楠（1982—），女，汉族，河北定州人，讲师，毕业于兰州交通大学，硕士研究生，主要研究方向：计算机应用、通信工程;徐刚（1980—），男，汉族，河南洛阳人，讲师，毕业于兰州交通大学，硕士，主要研究方向：计算机应用、网络通信技术。
关键词：VBA;指定字符;文本去除
中图分类号：TP317 文献标识码： A 文章编号：2096-4706（2021）02-0032-03
Abstract：Excel is one of the more widely used office software systems. It has many types of built-in functions to realize many functions，but it still cannot fully meet the usage requirements of special functions of some users. In view of this，Excel provides macro functions to satisfy user’s usage requirements that develop unique functions through self-definition way. Using the macro programming technology of VBA and through self-definition of Excel function，the batch removal function of the text between the specified characters in the Excel cell data is realized. At the same time，two implementation modes of solutions removing or retaining of the specified characters are provided.
Keywords：VBA;specified character;text removal
0 引 言
Excel是微软公司Office办公软件体系中的主要模块之一，Excel为普通用户提供各式各样的数据处理、数据分析、数据计算方法。但在一些需要独特操作，使用小众的运算方面，利用Excel提供的各种函数可以分次组合计算实现独特的功能，在实际操作中，虽然能够满足用户的特殊需求，但重复性操作步骤多，影响实际工作效率，此时可考虑利用Excel的宏开发功能，对重复性步骤进行整合编程处理，以自定义运算的方式简化工作步骤。
1 问题提出
在实际工作中，经常遇到一些问题，需要批量去除单元格数据中的括号及括号中的内容，并且需要去除的内容在单元格字符串中的位置并不固定。如在学生学籍管理工作中，规范的专业名称不带括号及括号中的专业方向，但多数原始数据中的专业名称后均带有括号及括号中的专业方向。对于此类数据，在数据规范化处理时，需要批量去除专业名称后面的括号及括号内的专业方向名称。
对于此类问题，可通过Excel的筛选，手动小批量删除同内容数据的括号及括号中的文本，或者使用Excel提供的MID、LEFT、RIGHT等字符串函数根据原始数据的不同进行分类处理。不管采用哪种方式，在实际操作中均存在操作步骤多、使用烦琐的问题。如何利用Excel的宏功能将步骤多而烦琐的操作进行集成化处理，是本文将要着重讨论和解决的问题。
同时，可将此类问题引申为，对于给定的已知Excel单元格字符串，可通过指定开始位置的字符和结束位置的字符来实现Excel单元格中字符串的特征化批量删除。这尤其适用于需要批量去除的文本在整个字符串中的位置不固定的情形。
2 问题分析
以实际问题为例进行分析，如图1所示Excel表格，已知A列，批量生成B列。
通過分析上图可以得出，需要批量去除的字符串均在括号内并且连同括号一同去除。同时，A列数据中需要去除的字符串缺少规律性且在原字符串中的位置并不固定。
Excel自带的LEFT、RIGHT函数，具有从左往右和从右往左按字符数生成新字符串的功能。故解决问题的核心可转化为求A列单元格数据字符串中括号位置的问题。
利用Excel自带的函数实现功能的具体步骤分析为：
（1）利用Excel自带的函数LEN实现字符串长度的计算，该长度可作为程序循环模块中的循环次数控制变量。
（2）利用Excel自带的函数MID实现字符串文本的逐个提取，通过将提取出的字符和指定字符相比对，可以确定是否到达删除字符串的起始位置及结束位置。
（3）当判断出MID函数提取的字符等于指定开始字符时，可记录当前字符在字符串中的位置数。
（4）当判断出MID函数提取的字符等于指定结束字符时，可记录当前字符在字符串中的位置数。
（5）通过记录的位置数，综合利用Excel自带的LEFT、RIGHT函数，获取所需的头尾字符串，使用Execl提供的字符串连接运算符“&”连接形成最终需要的字符串。
上述步骤中，为了实现指定字符的去除与否控制，在自定义Excel函数时，通过附加一个判断参数，进一步拓展自定义函数的功能，使自定义函数具有按照用户要求实现是否保留指定字符的功能。 综上所述，利用Excel自身支持的VBA宏编程功能，通过自定义实现符合用户特殊需求的函数，依赖该函数让Excel具有一次性解决上述问题的能力，简单直接的解决同类问题。
3 问题解决
3.1 VBA简介
Visual Basic for Applications（VBA）是Visual Basic的一种宏语言，主要用来扩展Windows的应用程式功能，特别是Microsoft Office软件，也可说是一种应用程式视觉化的Basic Script。1994年发行的Excel 5.0版本中，即具备了VBA的宏功能。
3.2 基本算法描述
首先，设需要处理的目标单元格为S，其次，利用LEN求源字符串的字符个数，即长度，再次，以长度为循环依据，使用判断语句逐字符判断是否为指定开始字符或指定结束字符，如是，记录指定开始字符或指定结束字符位置数，最后，利用已记录位置及Excel自带函数LEFT和RIGHT综合生成用户需要的单元格内容。
3.3 基本算法实现
设有字符串s为a1，a2，…，an，b1，b2，…，bm，c1，c2，…cp，其中n，m，p均为随机自然数，需求a1，a2，…，an，c1，c2，…cp或a1，a2，…，an，x，y，c1，c2，…cp。
设已知字符串长度为c则，c=n+1+m+1+p;
设字符x位置为x1，则x=n+1;
设字符y位置为y1，则y1=n+1+m+1;
即a1，a2，…，an，c1，c2，…，cp=LEFT（s，x1-1） & RIGHT（s，y1+1），a1，a2，…，an，x，y，c1，c2，…cp=LEFT（s，x1） & RIGHT（s，y1）。
3.4 程序基本流程图
自定义Excel函数的程序基本流程图如图2所示。
流程图的主要内容为：
（1）前期数据处理包括函数定义、变量定义，求目标字符串长度并以该长度为依据设立循环。
（2）每取到字符串中的一个字符，便利用指定开始字符、指定结束字符做判断。如相等，则记录该字符在字符串中的位置，如不相等，则继续取下一个字符，循环往复，直到遍历字符串中的所有字符。
（3）利用函数定义中指定的功能控制参数进行分支选择，结合上一步中记录的指定开始字符和指定结束字符的位置数，按照用户需求计算保留指定字符或不保留指定字符的目标字符串。
3.5 功能实现代码
利用Excel的VBA宏编辑器，新建一个模块并输入以下代码：
Function tqnr（mb As Range， fh1 As String， fh2 As String， hf As Boolean） As String
//函数定义，函数名tqnr，数据类型字符串型。该函数同时定义4个参数，第一个参数为mb，用于单元格引用控制，数据类型单元格型;第二个参数为字符型变量fh1，用于接收用户指定的开始字符;第三个参数为字符型变量fh2用于接收用户指定的结束字符;第四个参数为布尔型功能控制参数，用于接收用户对功能的選择要求，如为TRUE，则表示用户需要去除指定的字符，如为FALSE，则表示用户不需要去除指定的字符。
Dim c As Integer //定义整型变量c，用于存放需处理字符串的长度数值。
If hf = True Then //通过参数hf控制是否需要保留指定字符的功能选择。
c = Len（mb.Text） //求需处理字符串的长度数值。
d1 = 1
d2 = 0//定义整形变量d1和d2，用于存放指定开始字符和指定结束字符在字符串中的位置数，同时对d1和d2赋初值做初始化处理。
For i = 1 To c//循环开始，通过循环遍历需处理字符串中的每个字符。
txt = Mid（mb， i， 1） //定义字符型变量txt用于临时存放需处理字符串中的每个字符。
If txt = fh1 Then d1 = i //确定指定开始字符在字符串中的位置。
If txt = fh2 Then d2 = i //确定指定结束字符在字符串中的位置。
Next i
tqnr = Left（mb， d1 - 1） & Right（mb， c - d2） //输出函数值，不保留指定字符及字符间内容的字符串。
End If
If hf = False Then
c = Len（mb.Text）
d1 = 1
d2 = 0
For i = 1 To c
txt = Mid（mb， i， 1）
If txt = fh1 Then d1 = i
If txt = fh2 Then d2 = i
Next i
If d1 <> 1 Then
tqnr = Left（mb， d1 - 1） & fh1 & fh2 & Right（mb， c - d2） Else： tqnr = Left（mb， d1 - 1） & Right（mb， c - d2） //输出函数值，保留指定字符，但去除指定字符间内容的字符串。
End If
End If
End Function
4 自定义函数使用说明
利用Excel的宏编程功能，自定义了函数tqnr，该函数同时带有四个参数。第一个参数为函数处理目标的指定（单元格型）;第二个参数为去除文本开始字符的指定（字符型）;第三个参数为去除文本结束字符的指定（字符型）;第四个参数为是否保留指定字符的控制（布尔型）。
在如图3所示的Excel工作表中，通过在B2单元格中输入自定义的函数tqnr，使用填充句柄进行填充后，可以快速地得到如图4所示的结果。
5 结 论
在分析Excel宏开发功能的基础上，对于本文提出的具体问题，即从多种类字符串单元格中，对指定字符及字符间的内容批量去除功能，进行了步骤分解和分析。在此基础上，提出了功能实现的算法描述，并通过VBA的宏编程功能开发了功能实现代码。
在实际工作中，面对几十个不同的专业名称，成千上万条学生学籍信息记录，相较以往的逐专业筛选，逐专业批量复制的处理方式。该方法不受专业类型多少，数据记录条数多少的影响，一次性、批量化地得出用户所需的数据。极大地提高了工作效率，并且，用户处理的数据量越大，该方法的效率提升越明显，在实际工作中具有极高的使用价值。
参考文献：
[1] 高楠，李紅霞.利用Excel的宏功能实现多数据类型单元格中纯数字的提取 [J].计算机产品与流通，2017（9）：197.
[2] 李晓玫，杨小平.Excel中的VBA程序设计 [J].四川师范大学学报（自然科学版），2004（4）：96-99.
[3] 杨天生.VBA在高效办公中的应用 [J].机械工程师，2017（9）：145-146.
[4] 张怡芳.Excel自定义函数的编制与实例 [J].计算机与现代化，2001（2）：130-135.
[5] 孔德宏.在EXCEL中基于VBA的学生基本情况调查表的设计与实现 [J].企业导报，2011（6）：254-255.
[6] 高楠，石蕊，李红霞.根据身份证号直接计算年龄的EXCEL实现 [J].计算机产品与流通，2017（10）：37.
作者简介：高楠（1982—），女，汉族，河北定州人，讲师，毕业于兰州交通大学，硕士研究生，主要研究方向：计算机应用、通信工程;徐刚（1980—），男，汉族，河南洛阳人，讲师，毕业于兰州交通大学，硕士，主要研究方向：计算机应用、网络通信技术。