利用自定義函數(shù)獲取混合內(nèi)容單元格中的指定字符串

2019-08-24 02:24:26谷城縣審計(jì)局

審計(jì)月刊 2019年7期

◆吳爭(zhēng) 劉璐/谷城縣審計(jì)局

由于原始數(shù)據(jù)錄入不規(guī)范，經(jīng)常會(huì)造成數(shù)據(jù)分析人員在前期數(shù)據(jù)結(jié)構(gòu)化整理工作上花費(fèi)較長(zhǎng)的時(shí)間和較多的精力。近日，筆者在某審計(jì)項(xiàng)目中遇到此類(lèi)情況，較多基礎(chǔ)數(shù)據(jù)全部錄入在一個(gè)單元格內(nèi)，且沒(méi)有較明顯的規(guī)則來(lái)提取，因?yàn)樾枰矸葑C號(hào)碼和手機(jī)號(hào)碼等關(guān)鍵字段，所以必須要對(duì)基礎(chǔ)數(shù)據(jù)開(kāi)展清洗工作，轉(zhuǎn)換成標(biāo)準(zhǔn)格式以滿(mǎn)足審計(jì)需要。

部分?jǐn)?shù)據(jù)（以下所有截屏數(shù)據(jù)均為演示數(shù)據(jù)）如圖1所示。

圖1

從圖中可以看到，C列單元格中包含了人員的社區(qū)信息、身份證號(hào)碼、性別、手機(jī)號(hào)碼、戶(hù)籍屬性。

一、利用VLOOKUP函數(shù)探索

起初考慮用VLOOKUP函數(shù)加入數(shù)組計(jì)算方式來(lái)解決，設(shè)置要輸出身份證號(hào)碼的單元格D2=VLOOKUP(0，MID(C2，ROW($1:$99)，18)*{0，1}，2，0)。思路是 MID 函數(shù)依次從C2的第1、2、3、4……直至99個(gè)位置，提取長(zhǎng)度為18位的字符，然后分別乘以0和1，即常量數(shù)組{0，1}。如果MID函數(shù)的結(jié)果為文本，那么乘以{0，1}后結(jié)果為錯(cuò)誤值{#VALUE!，#VALUE!}；如果MID函數(shù)的結(jié)果為數(shù)值，結(jié)果即為所需提取的18位身份證號(hào)碼。

實(shí)際運(yùn)算后發(fā)現(xiàn)函數(shù)提取超過(guò)11位顯示為科學(xué)計(jì)數(shù)，如圖2所示。

圖2

于是考慮用英文引號(hào)拼接函數(shù)來(lái)調(diào)整顯示格式，修改單元格 D2="'"&VLOOKUP(0，MID(C3，ROW($1:$99)，18)*{0，1}，2，0)，運(yùn)行結(jié)果如圖3。

圖3

觀察發(fā)現(xiàn)，計(jì)算結(jié)果與實(shí)際不符。看來(lái)利用VLOOK?UP函數(shù)加入數(shù)組計(jì)算提取18位的身份證號(hào)碼行不通，只能另辟蹊徑。

二、運(yùn)用VBA正則運(yùn)算解決問(wèn)題

VBA正則表達(dá)式是一種特殊的字符串模式，用于匹配字符串排列的一套規(guī)則。我們可以用這個(gè)規(guī)則去匹配查找可以匹配上的字符串（即單元格中任意想要的信息）。簡(jiǎn)單來(lái)說(shuō)，就是單元格中存在一個(gè)文本信息，這個(gè)信息中有一些我們需要的內(nèi)容，也有很多不需要的內(nèi)容，通過(guò)正則表達(dá)式幫助我們從文本中提取想要的內(nèi)容。

如上例中因?yàn)樯矸葑C號(hào)碼出現(xiàn)位置不固定，我們無(wú)法使用函數(shù)LEFT或者M(jìn)ID或者RIGHT來(lái)獲取身份證號(hào)碼，使用正則表達(dá)式可以快速獲取身份證號(hào)碼。

在表格中按下Alt+F11進(jìn)入設(shè)計(jì)模式，插入模塊，編寫(xiě)語(yǔ)句后保存。語(yǔ)句及注釋如下：

Function GetCardID(rng As Range，i As Integer)'格式=GetCardID(A1，1)，A1代表單元格，1代表從里面提取第幾組，必須在字符之間，但加"|$"可以在字符之后

Dim Reg

Dim Mhs

Set Reg=CreateObject("vbscript.regexp")'創(chuàng)建正則表達(dá)式對(duì)象

With Reg

.Pattern="(?:^|D)(d{18}|d{15})(?=^|D)"'匹配正則表達(dá)式，加"|$"可以提取在字符串之后

Dim Reg

.Global=True

Set Mhs=Reg.Execute(rng.Value)'將所有匹配的字符串賦值給Mhs

End With

If i-1＜0 Or i＞Mhs.Count Then'如果自定義函數(shù)的第二參數(shù)小于0或者大于匹配字符串組數(shù)就繼續(xù)執(zhí)行

GetCardID="#VALUE"'結(jié)果顯示"#VALUE"錯(cuò)誤值

Exit Function'退出過(guò)程

End If

GetCardID=Mhs(i-1).submatches(0)'結(jié)果等于匹配字符串的指定組數(shù)

End Function

其中With語(yǔ)句塊作用是匹配15位或18位的字符串賦值給Mhs，IF語(yǔ)句塊作用是判斷自定義函數(shù)的第二參數(shù)是否在計(jì)算范圍，兩個(gè)語(yǔ)句塊之后就是從0開(kāi)始進(jìn)行匹配計(jì)算，成功匹配的字符串賦值給GetCardID函數(shù)。