單元格中指定字符間文本去除的研究

高楠 徐剛

摘? 要：Excel是使用非常廣泛的辦公軟件體系之一，自身帶有多種類(lèi)型、可實(shí)現(xiàn)多種功能的內(nèi)置函數(shù)，但仍不能完全滿足一些用戶的特殊功能使用要求?；诖?，Excel提供了宏功能，滿足用戶通過(guò)自定義的方式開(kāi)發(fā)獨(dú)特功能的使用需求。利用VBA的宏編程技術(shù)，通過(guò)自定義Excel函數(shù)，實(shí)現(xiàn)Excel單元格數(shù)據(jù)中指定字符間文本的批量去除功能，同時(shí)提供指定字符去除或保留兩種實(shí)現(xiàn)模式的解決方案。

關(guān)鍵詞：VBA;指定字符;文本去除

中圖分類(lèi)號(hào)：TP317? ? ? 文獻(xiàn)標(biāo)識(shí)碼： A 文章編號(hào)：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 users 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為普通用戶提供各式各樣的數(shù)據(jù)處理、數(shù)據(jù)分析、數(shù)據(jù)計(jì)算方法。但在一些需要獨(dú)特操作，使用小眾的運(yùn)算方面，利用Excel提供的各種函數(shù)可以分次組合計(jì)算實(shí)現(xiàn)獨(dú)特的功能，在實(shí)際操作中，雖然能夠滿足用戶的特殊需求，但重復(fù)性操作步驟多，影響實(shí)際工作效率，此時(shí)可考慮利用Excel的宏開(kāi)發(fā)功能，對(duì)重復(fù)性步驟進(jìn)行整合編程處理，以自定義運(yùn)算的方式簡(jiǎn)化工作步驟。

1? 問(wèn)題提出

在實(shí)際工作中，經(jīng)常遇到一些問(wèn)題，需要批量去除單元格數(shù)據(jù)中的括號(hào)及括號(hào)中的內(nèi)容，并且需要去除的內(nèi)容在單元格字符串中的位置并不固定。如在學(xué)生學(xué)籍管理工作中，規(guī)范的專(zhuān)業(yè)名稱不帶括號(hào)及括號(hào)中的專(zhuān)業(yè)方向，但多數(shù)原始數(shù)據(jù)中的專(zhuān)業(yè)名稱后均帶有括號(hào)及括號(hào)中的專(zhuān)業(yè)方向。對(duì)于此類(lèi)數(shù)據(jù)，在數(shù)據(jù)規(guī)范化處理時(shí)，需要批量去除專(zhuān)業(yè)名稱后面的括號(hào)及括號(hào)內(nèi)的專(zhuān)業(yè)方向名稱。

對(duì)于此類(lèi)問(wèn)題，可通過(guò)Excel的篩選，手動(dòng)小批量刪除同內(nèi)容數(shù)據(jù)的括號(hào)及括號(hào)中的文本，或者使用Excel提供的MID、LEFT、RIGHT等字符串函數(shù)根據(jù)原始數(shù)據(jù)的不同進(jìn)行分類(lèi)處理。不管采用哪種方式，在實(shí)際操作中均存在操作步驟多、使用煩瑣的問(wèn)題。如何利用Excel的宏功能將步驟多而煩瑣的操作進(jìn)行集成化處理，是本文將要著重討論和解決的問(wèn)題。

同時(shí)，可將此類(lèi)問(wèn)題引申為，對(duì)于給定的已知Excel單元格字符串，可通過(guò)指定開(kāi)始位置的字符和結(jié)束位置的字符來(lái)實(shí)現(xiàn)Excel單元格中字符串的特征化批量刪除。這尤其適用于需要批量去除的文本在整個(gè)字符串中的位置不固定的情形。

2? 問(wèn)題分析

以實(shí)際問(wèn)題為例進(jìn)行分析，如圖1所示Excel表格，已知A列，批量生成B列。

通過(guò)分析上圖可以得出，需要批量去除的字符串均在括號(hào)內(nèi)并且連同括號(hào)一同去除。同時(shí)，A列數(shù)據(jù)中需要去除的字符串缺少規(guī)律性且在原字符串中的位置并不固定。

Excel自帶的LEFT、RIGHT函數(shù)，具有從左往右和從右往左按字符數(shù)生成新字符串的功能。故解決問(wèn)題的核心可轉(zhuǎn)化為求A列單元格數(shù)據(jù)字符串中括號(hào)位置的問(wèn)題。

利用Excel自帶的函數(shù)實(shí)現(xiàn)功能的具體步驟分析為：

（1）利用Excel自帶的函數(shù)LEN實(shí)現(xiàn)字符串長(zhǎng)度的計(jì)算，該長(zhǎng)度可作為程序循環(huán)模塊中的循環(huán)次數(shù)控制變量。

（2）利用Excel自帶的函數(shù)MID實(shí)現(xiàn)字符串文本的逐個(gè)提取，通過(guò)將提取出的字符和指定字符相比對(duì)，可以確定是否到達(dá)刪除字符串的起始位置及結(jié)束位置。

（3）當(dāng)判斷出MID函數(shù)提取的字符等于指定開(kāi)始字符時(shí)，可記錄當(dāng)前字符在字符串中的位置數(shù)。

（4）當(dāng)判斷出MID函數(shù)提取的字符等于指定結(jié)束字符時(shí)，可記錄當(dāng)前字符在字符串中的位置數(shù)。

（5）通過(guò)記錄的位置數(shù)，綜合利用Excel自帶的LEFT、RIGHT函數(shù)，獲取所需的頭尾字符串，使用Execl提供的字符串連接運(yùn)算符“&”連接形成最終需要的字符串。

上述步驟中，為了實(shí)現(xiàn)指定字符的去除與否控制，在自定義Excel函數(shù)時(shí)，通過(guò)附加一個(gè)判斷參數(shù)，進(jìn)一步拓展自定義函數(shù)的功能，使自定義函數(shù)具有按照用戶要求實(shí)現(xiàn)是否保留指定字符的功能。

綜上所述，利用Excel自身支持的VBA宏編程功能，通過(guò)自定義實(shí)現(xiàn)符合用戶特殊需求的函數(shù)，依賴該函數(shù)讓Excel具有一次性解決上述問(wèn)題的能力，簡(jiǎn)單直接的解決同類(lèi)問(wèn)題。

3? 問(wèn)題解決

3.1? VBA簡(jiǎn)介

Visual Basic for Applications（VBA）是Visual Basic的一種宏語(yǔ)言，主要用來(lái)擴(kuò)展Windows的應(yīng)用程式功能，特別是Microsoft Office軟件，也可說(shuō)是一種應(yīng)用程式視覺(jué)化的Basic Script。1994年發(fā)行的Excel 5.0版本中，即具備了VBA的宏功能。

3.2? 基本算法描述

首先，設(shè)需要處理的目標(biāo)單元格為S，其次，利用LEN求源字符串的字符個(gè)數(shù)，即長(zhǎng)度，再次，以長(zhǎng)度為循環(huán)依據(jù)，使用判斷語(yǔ)句逐字符判斷是否為指定開(kāi)始字符或指定結(jié)束字符，如是，記錄指定開(kāi)始字符或指定結(jié)束字符位置數(shù)，最后，利用已記錄位置及Excel自帶函數(shù)LEFT和RIGHT綜合生成用戶需要的單元格內(nèi)容。

3.3? 基本算法實(shí)現(xiàn)

設(shè)有字符串s為a1，a2，…，an，b1，b2，…，bm，c1，c2，…cp，其中n，m，p均為隨機(jī)自然數(shù)，需求a1，a2，…，an，c1，c2，…cp或a1，a2，…，an，x，y，c1，c2，…cp。

設(shè)已知字符串長(zhǎng)度為c則，c=n+1+m+1+p;

設(shè)字符x位置為x1，則x=n+1;

設(shè)字符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函數(shù)的程序基本流程圖如圖2所示。

流程圖的主要內(nèi)容為：

（1）前期數(shù)據(jù)處理包括函數(shù)定義、變量定義，求目標(biāo)字符串長(zhǎng)度并以該長(zhǎng)度為依據(jù)設(shè)立循環(huán)。

（2）每取到字符串中的一個(gè)字符，便利用指定開(kāi)始字符、指定結(jié)束字符做判斷。如相等，則記錄該字符在字符串中的位置，如不相等，則繼續(xù)取下一個(gè)字符，循環(huán)往復(fù)，直到遍歷字符串中的所有字符。

（3）利用函數(shù)定義中指定的功能控制參數(shù)進(jìn)行分支選擇，結(jié)合上一步中記錄的指定開(kāi)始字符和指定結(jié)束字符的位置數(shù)，按照用戶需求計(jì)算保留指定字符或不保留指定字符的目標(biāo)字符串。

3.5? 功能實(shí)現(xiàn)代碼

利用Excel的VBA宏編輯器，新建一個(gè)模塊并輸入以下代碼：

Function tqnr（mb As Range， fh1 As String， fh2 As String， hf As Boolean） As String

//函數(shù)定義，函數(shù)名tqnr，數(shù)據(jù)類(lèi)型字符串型。該函數(shù)同時(shí)定義4個(gè)參數(shù)，第一個(gè)參數(shù)為mb，用于單元格引用控制，數(shù)據(jù)類(lèi)型單元格型;第二個(gè)參數(shù)為字符型變量fh1，用于接收用戶指定的開(kāi)始字符;第三個(gè)參數(shù)為字符型變量fh2用于接收用戶指定的結(jié)束字符;第四個(gè)參數(shù)為布爾型功能控制參數(shù)，用于接收用戶對(duì)功能的選擇要求，如為T(mén)RUE，則表示用戶需要去除指定的字符，如為FALSE，則表示用戶不需要去除指定的字符。

Dim c As Integer? ? //定義整型變量c，用于存放需處理字符串的長(zhǎng)度數(shù)值。

If hf = True Then? ? //通過(guò)參數(shù)hf控制是否需要保留指定字符的功能選擇。

c = Len（mb.Text）? ?//求需處理字符串的長(zhǎng)度數(shù)值。

d1 = 1

d2 = 0//定義整形變量d1和d2，用于存放指定開(kāi)始字符和指定結(jié)束字符在字符串中的位置數(shù)，同時(shí)對(duì)d1和d2賦初值做初始化處理。

For i = 1 To c//循環(huán)開(kāi)始，通過(guò)循環(huán)遍歷需處理字符串中的每個(gè)字符。

txt = Mid（mb， i， 1）? ? //定義字符型變量txt用于臨時(shí)存放需處理字符串中的每個(gè)字符。

If txt = fh1 Then d1 = i? ? ? ?//確定指定開(kāi)始字符在字符串中的位置。

If txt = fh2 Then d2 = i? ? ? ?//確定指定結(jié)束字符在字符串中的位置。

Next i

tqnr = Left（mb， d1 - 1） & Right（mb， c - d2）? //輸出函數(shù)值，不保留指定字符及字符間內(nèi)容的字符串。

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）? //輸出函數(shù)值，保留指定字符，但去除指定字符間內(nèi)容的字符串。

End If

End If

End Function

4? 自定義函數(shù)使用說(shuō)明

利用Excel的宏編程功能，自定義了函數(shù)tqnr，該函數(shù)同時(shí)帶有四個(gè)參數(shù)。第一個(gè)參數(shù)為函數(shù)處理目標(biāo)的指定（單元格型）;第二個(gè)參數(shù)為去除文本開(kāi)始字符的指定（字符型）;第三個(gè)參數(shù)為去除文本結(jié)束字符的指定（字符型）;第四個(gè)參數(shù)為是否保留指定字符的控制（布爾型）。

在如圖3所示的Excel工作表中，通過(guò)在B2單元格中輸入自定義的函數(shù)tqnr，使用填充句柄進(jìn)行填充后，可以快速地得到如圖4所示的結(jié)果。

5? 結(jié)? 論

在分析Excel宏開(kāi)發(fā)功能的基礎(chǔ)上，對(duì)于本文提出的具體問(wèn)題，即從多種類(lèi)字符串單元格中，對(duì)指定字符及字符間的內(nèi)容批量去除功能，進(jìn)行了步驟分解和分析。在此基礎(chǔ)上，提出了功能實(shí)現(xiàn)的算法描述，并通過(guò)VBA的宏編程功能開(kāi)發(fā)了功能實(shí)現(xiàn)代碼。

在實(shí)際工作中，面對(duì)幾十個(gè)不同的專(zhuān)業(yè)名稱，成千上萬(wàn)條學(xué)生學(xué)籍信息記錄，相較以往的逐專(zhuān)業(yè)篩選，逐專(zhuān)業(yè)批量復(fù)制的處理方式。該方法不受專(zhuān)業(yè)類(lèi)型多少，數(shù)據(jù)記錄條數(shù)多少的影響，一次性、批量化地得出用戶所需的數(shù)據(jù)。極大地提高了工作效率，并且，用戶處理的數(shù)據(jù)量越大，該方法的效率提升越明顯，在實(shí)際工作中具有極高的使用價(jià)值。

參考文獻(xiàn)：

[1] 高楠，李紅霞.利用Excel的宏功能實(shí)現(xiàn)多數(shù)據(jù)類(lèi)型單元格中純數(shù)字的提取 [J].計(jì)算機(jī)產(chǎn)品與流通，2017（9）：197.

[2] 李曉玫，楊小平.Excel中的VBA程序設(shè)計(jì) [J].四川師范大學(xué)學(xué)報(bào)（自然科學(xué)版），2004（4）：96-99.

[3] 楊天生.VBA在高效辦公中的應(yīng)用 [J].機(jī)械工程師，2017（9）：145-146.

[4] 張怡芳.Excel自定義函數(shù)的編制與實(shí)例 [J].計(jì)算機(jī)與現(xiàn)代化，2001（2）：130-135.

[5] 孔德宏.在EXCEL中基于VBA的學(xué)生基本情況調(diào)查表的設(shè)計(jì)與實(shí)現(xiàn) [J].企業(yè)導(dǎo)報(bào)，2011（6）：254-255.

[6] 高楠，石蕊，李紅霞.根據(jù)身份證號(hào)直接計(jì)算年齡的EXCEL實(shí)現(xiàn) [J].計(jì)算機(jī)產(chǎn)品與流通，2017（10）：37.

作者簡(jiǎn)介：高楠（1982—），女，漢族，河北定州人，講師，畢業(yè)于蘭州交通大學(xué)，碩士研究生，主要研究方向：計(jì)算機(jī)應(yīng)用、通信工程;徐剛（1980—），男，漢族，河南洛陽(yáng)人，講師，畢業(yè)于蘭州交通大學(xué)，碩士，主要研究方向：計(jì)算機(jī)應(yīng)用、網(wǎng)絡(luò)通信技術(shù)。