在Excel中使用正規表示法(Regular Expression)進行資料篩選(一)

2010-09-23

37272
0
IT人日誌
2010-10-30

在Excel中使用正規表示法(Regular Expression)進行資料篩選

正規表示法(Regular Expression)在文字的過濾比對是一個強大的工具，筆者之前常用perl程式語言中提供的Regular Expression 進行一些伺服器管理與文字處理的工作，後來因為常用Excel進行資料處理，就想到如果可以在Excel中加入Regular Expression的功能，應該可以提高處理資料的效率，後來我寫了一兩個巨集試著使用微軟的VBScript Regular Expression 元件進行文字資料的處理，覺得還蠻好用的，就把它設計成一個增益集，後來我把它放在『資料處理過濾增益集』中，雖然 VBScript Regular Expression 與perl程式語言中提供的Regular Expression 並不相同，不過概念其實蠻類似的，後來的.net直接提供Regular Expression的語法，不過因為我已經習慣使用VBScript Regular Expression 提供的語法，所以我也沒再去修改它一直延用至今。

在『資料處理過濾增益集』我設計了幾個利用Regular Expression 進行資料篩選、處理、擷取的功能分別是『工作表資料篩選』、『工作表資料擷取』、『工作表資料內容處理』、『文字資料擷取』、『批次產生樣板資料檔』、『報表資料擷取』

有些功能使用者可能覺得很奇怪，為什麼要設計這樣的功能，似乎沒甚麼用，這些功能都是我在資訊組長任內，為了處理學校各個處室資料交換與整合所設計的功能，現實中學校中有太多特別的案例，雖然為一兩個特殊案例寫一支程式似乎不符合成本，不過當時還年輕有一點傻勁，把這個當成是練功，再者本校教職員生人數眾多，逐筆手動處理也要花費不少時間。

先把一些VBScript Regular Expression 提供的語法做一介紹，因原始英文的說明檔案我已經找不到，以下的資料是我很久以前為了學校教育訓練翻譯微軟英文的說明檔，因本人英文欠佳，所以翻譯不一定完全符合文章原意，如果有網友知道此說明檔之網址請告訴我，我會把原文連結於此，目前暫時以譯文進行說明，此譯文純粹作為『資料處理過濾增益集』的『正規比對資料過濾』功能說明，並非有意侵犯微軟註作權利：

正規表示法語法參考

正規表示法語法簡介： (參考來源微軟正規表示法說明)

正規表示法(Regular Express)是一種透過特定的字元組合成的一種字串比對規則，利用此比對規則可比對出特定的字串，我們將這個規則稱為樣版或模版(pattern)，習慣會以//來表示樣版，例如要比對某字串中是否有n這個英文字母，則比對的樣版可寫成/n/。

以下就本程式所支持的正規表示法的字元符號的象徵意義與操作方式進行說明。

字元符號	說明
\	如欲比對的部分包括正規表示法中特殊的字元符號必須則必須加上\符號，例如要比對某字串中是否有換行符號，樣版比對的寫法為/\n/，如要比對字串中是否有\這個符號，則樣版必須寫成/\\/。
^	這個符號代表從一行文字的開頭進行比對，例如要知道那一行文字開頭是否為a，樣版可寫成/^a/。
$	這個符號代表從一行文字的結束進行比對，例如要知道那一行文字結尾是否為a，樣版可寫成/a$/。
*	*加入這個符號代表進行加入比對時前面的字元可以0到多個，例如樣版寫成/zo/，符合的字串有 "z" 或 "zo" 或 "zoooo".*
+	*加入這個符號代表進行加入比對時前面的字元至少1到多個，例如樣版寫成/zo/，符合的字串有"zo"或"zoooo"，但是z是不符合的。*
?	*加入這個符號代表進行加入比對時前面的字元0或1個，例如樣版寫成 /z?oom/，符合的字串有zoom 或 oom。**
.	加入這個符號代表進行加入比對時任何單一字元都符合，除了換行字元。
( pattern )	在樣版外加上( )除了進行樣版比對，還可以將比對符合的子字串存入陣列中提供使用，如果要比對(),比須寫成。
x \| y	要比對字串中有x或y可用此比對方式，例如 /g\|food/ 符合的字串good 或 food.
{ n }	加入這個符號代表進行加入比對時{n}前面的字元必須為n個，n 必須為正整數. 例如/e{2}/符合的字串如feed或feel，但key不符合。
{ n ,}	加入這個符號代表進行加入比對時{n,}前面的字元至少n個，n 必須為正整數. 例如/e{2}/符合的字串如feed或feel或geee，但key不符合。
{ n , m }	加入這個符號代表進行加入比對時{n,m}前面的字元至少n個最多m個，n、m必須為正整數. 例如/e{1,2}/符合的字串如feed或key或feel，但geee不符合。
[ xyz ]	這個樣版表示只要有[]中的任一字元都算符合，例如[abc]則符合的字串有after或book,不符合的如dog。
[^ xyz ]	這個樣版表示只要有[]中的任一字元以外都算符合，例如[abc]則符合的字串有dog或food,不符合的如after或book。
\b	這個樣版表示必須包括一個英文字的邊界，英文字已空格為邊界，例如在"cook cake"中符合/k\b/ 這個樣版的是cook的k而不是cake的k。
\B	這個樣版表示不包括一個英文字的邊界，英文字已空格為邊界，例如在"cook cake"中符合/k\B/ 這個樣版的是cake的k而不是cook的k。
\d	這個樣版表示必須是數字. 也可以表示為[0-9].如要比對是否為八碼的數可寫成/\d{8}/
\D	這個樣版表示必須是非數字. 也可以表示為[^0-9]，^在[]中為否定作用而非一行之開頭。
\f	比對 form-feed 符號.
\n	比對換行符號.
\r	比對carriage return 換行符號.
\s	比對任何空白字元包括space, tab, form-feed等等. 也可以寫成[ \f\n\r\t\v]。
\S	比對任何非空白字元. 也可以寫成 [^ \f\n\r\t\v]。
\t	比對 tab 符號.
\v	比對vertical tab符號.
\w	比對任何字元. 也可以寫成 [A-Za-z0-9_].
\W	比對任何非字元. 也可以寫成 [^A-Za-z0-9_].

範例：

1.篩選出座號為11~19的資料：輸入的樣版為/^1\d{1}/

2.篩選出座號為偶數的資料：輸入的樣版為/[02468]\b/

3.篩選出座號為奇數的資料：輸入的樣版為/[13579]\b/

舉例在以下資料中我們要把座號中為偶數者資料列篩選出來，要如何做？

座號	姓名	第1次小考	第2次小考	第3次小考
1	蕭遠山	93	80	94
2	蕭峰	30	90	90
3	段正淳	14	0	95
4	段譽	0	0	90
5	虛竹	30	76	0
6	慕容博	90	0	80
7	慕容復	98	98	88
8	耶律阿保機	99.2	90	80
9	完顏阿骨打	92	90	0
10	段正明	95	0	26
11	包不同	92	26	92
12	段延慶	91.2	80	95
13	楊鐵心	60	76	98
14	楊康	0	42	92
15	楊過	86	98	0
16	郭嘯天	92	82	0
17	郭靖	0	60	98
18	張三豐	0	98	95
19	張翠山	95	92	0
20	宋遠橋	90	90	14
21	俞蓮舟	26	12	0
22	莫聲谷	92	80	90
24	殷梨亭	90	30	0
25	殷天正	0	0	0
26	殷野王	0	92	98
27	朱元璋	90	0	95
28	徐達	30	95	42
29	常遇春	90	14	0
31	冷謙	90	90	72
32	韋一笑	88	88	98
33	袁崇煥	80	0	90
34	袁承志	92	90	0
35	顧人清	92	95	0
36	胡一刀	95	0	91.2
37	胡斐	90	93	0

點選『工作表資料篩選』叫出『選取符合篩選條件的資料』表單，『篩選的資料範圍』選取座號資料範圍$A$2:$A$36，總共35個座號資料，正規表示比對樣版輸入[02468]\b，此樣版表示數字最後一個字必須是0、2、4、6、8中任何一個，即為座號偶數之資料，資料選取的對象設為為資料格所在列位，再按『篩選』按鈕，即可選出符合條件之資料列。