再論在Excel中使用『資料處理過濾增益集』進行資料合併

現在隨著校園資訊化的推展，現在台北縣政府直接請廠商寫了校務行政系統，把一些學校共同的資料集中統一管理，但是學校的事務繁雜，除了學生學習成績與資料外，還有很多業務資料待整合，現在縣府請校務行政系統負責的廠商一直在原有的校務行政系統統增加開發一些新功能，不過這種集中式管理的資料庫做到包山包海，只怕最後會出包，尤其廠商人力不足時，所以教育局將注意力聚焦在各級學校共同核心的資料，至於學校個別需求的功能，各校自行發展或找尋可用的方案解決。

早在縣府校務行政系統未發展時，其實有些學校各單位已經在進行資料數位化，很多學校希望透過電子檔案建立，把原本要反覆書寫建立的資料，變成可以流通與重複利用的資料，其實這樣的資訊化工作，對於學校這種層級的單位已經足夠，但是這樣看似簡單的任務，卻經常因為缺乏由統一的單位來主導而流於各行其事。很多單位在學校資訊單位成立之前，已經建置很多各種資料的電子檔案，可惜的是大部分沒有統一的資料格式，甚至出現不同單位在收集同樣的資料，卻用不同的表格或檔案格式呈現，導致資源的重複浪費。

理論上這個工作開始應該由學校的資訊單位來主導，偏偏現在中小學校的資訊單位幾乎是各校最後成立的單位，而且位階都很低，也沒甚麼發言權(很多資訊組長戲稱自己是資訊工友)。理想狀況當然是由資訊單位統一的訂定各種資料格式，然後將各種資料資料庫化。但是這在現實狀況是達不到，一方面現行的中小學中的資訊單位通常只是一個光桿組長，要管的事情太多不見得有時間與能力去做這樣的事。而且每一單位有其專業考量，同樣的業務表格與資料格式已經傳承許久，業務的承辦人可能還沒有熟悉業務，蕭規曹隨都來不及，遑論要創新，而且資訊單位不見得了解所有單位的業務，如果真的要做，就必須與各單位進行協商與討論，資訊單位在學校面對的不是上級單位，就是平行單位，誰要和你協商，在很多學校資訊單位是被定位為支援單位，不是政策擬訂與執行單位，我需要你，你就來支援，其他廢話少說!這是天才的中華民國文官體制所造成的，只要是必須由專業人員擔任的職務，職務位階就不會太高，所以出現外行領導內行，不行的領導行的現象並不奇怪。譬如政府宣導資安很重要，但是由位階低的資訊單位去負責計劃的訂定與執行，誰管你!做了一堆的管理方案與教育訓練，只有你自己當真!

大環境雖如此，還是得想辦法找一條自己生存之道，山不轉路轉。例如資料的交流利用方面，資訊組大概都需要一些學校各處室單位的資料，來建置學校網站，與其要求各單位把資料按照統一的規範分享出來，還不如自己想辦法把別的單位已經建立的資料，轉為自己所需的資料!

例如學校要在網站放置人員資訊查詢系統，基本至少需要有職銜、辦公室位置、分機、擔任導師班級、電子郵件、任教科目等資料，這些資料並不是由資訊單位建立的，都必須由其他單位提供，像教職員工辦公室位置的資料如何取得？本校教職員工有三百多人，每學年度除了留任的行政人員，大部分的教職員辦公室位置幾乎都會異動，安排座位的是總務單位，負責這項業務的同仁會把這個資料輸入在已經建立的一個Excel的檔，這個表格是這位同仁精心設計之後，最滿意的樣式(如下表一)。但是這樣的表格，利於視覺觀看但並不利於轉成資料庫，我們還必須手動處理，說實在的Excel的確是一個很好的資料處理的工具，尤其在資料的建立、輸入與重整，幾個複製貼上的指令，加上自動填滿，是很快就可以把資料重新安排成為行列的表格(如表二)，這樣就很方便轉成資料庫。

(表一) (表二)

但是這樣的資料我們要整合至全校教職員工的系統中我們會遇到一些問題，例如辦公室座位資料並不是全部同仁都有，例如留職停薪的同仁並不會出現在上面，也無法用排序對應複製貼上，因為資料數目不等，如何讓這些辦公室座位資料與人員資料對應，變成一個水磨工夫，只有一項資料慢慢對可能還好，如果再加上任教科目(非教師則無此資料)、擔任導師班級(不是每一個教師都擔任)，等幾項資料的匯整，這個工作就變成災難，剛開始接資訊組第一個月都在對資料建資料。

後來我第一個使用的方法是轉到Access上用資料表關聯的方法去處理，這個方法是可以很快的將兩個部分資料相同的工作表進行對應去產生一個合併的資料表，不過還是要幾個工作表與主資料表進行關聯產生新工作表之後，再處理再合併。後來我自己弄一個可以直接在Excel進行資料對應擷取的巨集，直接在Excel上面處理，後來把這個巨集放在『資料處理過濾增益集』中的『工作表資料擷取』。

我放一個範例的檔，讀者可以嘗試將此檔案中科目工作表與職稱工作表合併至員工資料工作表。範例檔下載

操作步驟影片

 讀者可以參考如何快速將Excel2007 兩個資料部分相同的工作簿合併這篇文章，道理是一樣的。

再論在Excel中使用『資料處理過濾增益集』進行資料合併

現在隨著校園資訊化的推展，現在台北縣政府直接請廠商寫了校務行政系統，把一些學校共同的資料集中統一管理，但是學校的事務繁雜，除了學生學習成績與資料外，還有很多業務資料待整合，現在縣府請校務行政系統負責的廠商一直在原有的校務行政系統統增加開發一些新功能，不過這種集中式管理的資料庫做到包山包海，只怕最後會出包，尤其廠商人力不足時，所以教育局將注意力聚焦在各級學校共同核心的資料，至於學校個別需求的功能，各校自行發展或找尋可用的方案解決。

早在縣府校務行政系統未發展時，其實有些學校各單位已經在進行資料數位化，很多學校希望透過電子檔案建立，把原本要反覆書寫建立的資料，變成可以流通與重複利用的資料，其實這樣的資訊化工作，對於學校這種層級的單位已經足夠，但是這樣看似簡單的任務，卻經常因為缺乏由統一的單位來主導而流於各行其事。很多單位在學校資訊單位成立之前，已經建置很多各種資料的電子檔案，可惜的是大部分沒有統一的資料格式，甚至出現不同單位在收集同樣的資料，卻用不同的表格或檔案格式呈現，導致資源的重複浪費。

理論上這個工作開始應該由學校的資訊單位來主導，偏偏現在中小學校的資訊單位幾乎是各校最後成立的單位，而且位階都很低，也沒甚麼發言權(很多資訊組長戲稱自己是資訊工友)。理想狀況當然是由資訊單位統一的訂定各種資料格式，然後將各種資料資料庫化。但是這在現實狀況是達不到，一方面現行的中小學中的資訊單位通常只是一個光桿組長，要管的事情太多不見得有時間與能力去做這樣的事。而且每一單位有其專業考量，同樣的業務表格與資料格式已經傳承許久，業務的承辦人可能還沒有熟悉業務，蕭規曹隨都來不及，遑論要創新，而且資訊單位不見得了解所有單位的業務，如果真的要做，就必須與各單位進行協商與討論，資訊單位在學校面對的不是上級單位，就是平行單位，誰要和你協商，在很多學校資訊單位是被定位為支援單位，不是政策擬訂與執行單位，我需要你，你就來支援，其他廢話少說!這是天才的中華民國文官體制所造成的，只要是必須由專業人員擔任的職務，職務位階就不會太高，所以出現外行領導內行，不行的領導行的現象並不奇怪。譬如政府宣導資安很重要，但是由位階低的資訊單位去負責計劃的訂定與執行，誰管你!做了一堆的管理方案與教育訓練，只有你自己當真!

大環境雖如此，還是得想辦法找一條自己生存之道，山不轉路轉。例如資料的交流利用方面，資訊組大概都需要一些學校各處室單位的資料，來建置學校網站，與其要求各單位把資料按照統一的規範分享出來，還不如自己想辦法把別的單位已經建立的資料，轉為自己所需的資料!

例如學校要在網站放置人員資訊查詢系統，基本至少需要有職銜、辦公室位置、分機、擔任導師班級、電子郵件、任教科目等資料，這些資料並不是由資訊單位建立的，都必須由其他單位提供，像教職員工辦公室位置的資料如何取得？本校教職員工有三百多人，每學年度除了留任的行政人員，大部分的教職員辦公室位置幾乎都會異動，安排座位的是總務單位，負責這項業務的同仁會把這個資料輸入在已經建立的一個Excel的檔，這個表格是這位同仁精心設計之後，最滿意的樣式(如下表一)。但是這樣的表格，利於視覺觀看但並不利於轉成資料庫，我們還必須手動處理，說實在的Excel的確是一個很好的資料處理的工具，尤其在資料的建立、輸入與重整，幾個複製貼上的指令，加上自動填滿，是很快就可以把資料重新安排成為行列的表格(如表二)，這樣就很方便轉成資料庫。

(表一) (表二)

但是這樣的資料我們要整合至全校教職員工的系統中我們會遇到一些問題，例如辦公室座位資料並不是全部同仁都有，例如留職停薪的同仁並不會出現在上面，也無法用排序對應複製貼上，因為資料數目不等，如何讓這些辦公室座位資料與人員資料對應，變成一個水磨工夫，只有一項資料慢慢對可能還好，如果再加上任教科目(非教師則無此資料)、擔任導師班級(不是每一個教師都擔任)，等幾項資料的匯整，這個工作就變成災難，剛開始接資訊組第一個月都在對資料建資料。

後來我第一個使用的方法是轉到Access上用資料表關聯的方法去處理，這個方法是可以很快的將兩個部分資料相同的工作表進行對應去產生一個合併的資料表，不過還是要幾個工作表與主資料表進行關聯產生新工作表之後，再處理再合併。後來我自己弄一個可以直接在Excel進行資料對應擷取的巨集，直接在Excel上面處理，後來把這個巨集放在『資料處理過濾增益集』中的『工作表資料擷取』。

我放一個範例的檔，讀者可以嘗試將此檔案中科目工作表與職稱工作表合併至員工資料工作表。範例檔下載

操作步驟影片

 讀者可以參考如何快速將Excel2007 兩個資料部分相同的工作簿合併這篇文章，道理是一樣的。

數學化學繪圖工具增益集：水流管線動畫製作

在動畫範例2:如何在Powerpoint2010繪製沿管線流動的動畫 中示範如何利用數學化學繪圖工具增益集製作沿管線流動的動畫，在那個範例中我們讓一個小方塊順著管線流動，但是如果真正要模擬水在管線中流動，必須建立很多個這樣的小方塊，讓小方塊依序流動形成一個如下圖的影像

讀者可下載範例檔案參考。在powerpoint2007以上的版本播放。

這個動畫最大要克服的是如何將每一塊方塊的動畫動作依序排列，在『數學化學繪圖工具增益集』『幾何繪圖』『轉成動畫路徑』，我設計一個可以讓多個物件沿同樣的路徑移動轉動的功能。步驟示範如以下影片所示

在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"中符合/kb/ 這個樣版的是cook的k而不是cake的k。
B	這個樣版表示不包括一個英文字的邊界，英文字已空格為邊界，例如在"cook cake"中符合/kB/ 這個樣版的是cake的k而不是cook的k。
d	這個樣版表示必須是數字. 也可以表示為[0-9].如要比對是否為八碼的數可寫成/d{8}/
D	這個樣版表示必須是非數字. 也可以表示為[^0-9]，^在[]中為否定作用而非一行之開頭。
f	比對 form-feed 符號.
n	比對換行符號.
r	比對carriage return 換行符號.
s	比對任何空白字元包括space, tab, form-feed等等. 也可以寫成[ fnrtv]。
S	比對任何非空白字元. 也可以寫成 [^ fnrtv]。
t	比對 tab 符號.
v	比對vertical tab符號.
w	比對任何字元. 也可以寫成 [A-Za-z0-9_].
W	比對任何非字元. 也可以寫成 [^A-Za-z0-9_].

 

範例：

1.篩選出座號為11~19的資料：輸入的樣版為/^1d{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中任何一個，即為座號偶數之資料，資料選取的對象設為為資料格所在列位，再按『篩選』按鈕，即可選出符合條件之資料列。

 

篩選結果如下圖

 

問題一：如果我們要篩選10~30號之間奇數的座號，我們的比對樣版應該如何寫？

答案是：^[12]{1}[13579]b

你答對了嗎？(答案內容文字為白色，可以用滑鼠選取)

篩選結果

問題二：如果我們改針對姓名資料進行篩選，即『篩選的資料範圍』改選取姓名資料範圍$B$2:$B$36，要找出本班中楊姓、殷姓、張姓的同學的資料列，我們的比對樣版應該如何寫？

答案是：^[楊殷張]

你答對了嗎？(答案內容文字為白色，可以用滑鼠選取)

篩選結果

問題三：如果我們對姓名資料進行篩選，要找出本班中單姓單名的同學的資料列，我們的比對樣版應該如何寫？

答案是：^W{2}$

你答對了嗎？(答案內容文字為白色，可以用滑鼠選取)

篩選結果

利用『資料處理過濾增益集』在Excel中建立帳號密碼資料

在學校每年都要建立學生的電子郵件帳號密碼，很多郵件伺服器提供匯入資料批次建立帳號資料，有很多人寫了一些批次產生帳號密碼檔案的小程式或script，在這裡介紹一下我自己寫的一個產生密碼檔案的巨集程式，很多伺服器支援csv(逗號隔開的文字檔)檔案格式匯入帳號，利用Excel可以很方便建立這種檔案格式，所以我寫了一個產生亂數密碼的巨集，放在『資料處理過濾增益集』/『資料型態轉換』/『產生亂數密碼』中。

以我們使用的google app 教育版的電子郵件帳號格式為例，我要大量建置帳號可以在Excel中先建立好下表這樣的欄位資料，學生的姓名資料可以由學校的校務行政系統取得放入欄位中，username可以自定規則用Excel自動填滿的功能快速建立，password則可以利用『產生亂數密碼』巨集來產生

username	first name	last name	password
W000101	遠山	蕭
W000102	峰	蕭
W000103	正淳	段
W000104	譽	段
W000105	竹	虛
W000106	容博	慕

 

只要將要建立password的帳號的password欄位資料格選取起來，點選『資料處理過濾增益集』/『資料型態轉換』/『產生亂數密碼』叫出『亂數密碼產生器』表單，輸入要作為密碼的字元，字元之間以逗點隔開，再設定要產生密碼的字數，例如以下設定我們會幫要建立密的帳號以亂數的方式在1,2,3,4,5,6,7,8,9,0,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z

這些字元中亂數挑選出5個字元產生密碼。

username	first name	last name	password
W000101	遠山	蕭	3wi9v
W000102	峰	蕭	k3Ets
W000103	正淳	段	hRxOd
W000104	譽	段	iMhQV
W000105	竹	虛	1libx
W000106	容博	慕	tLlEL
W000107	容復	慕	HVYqG
W000108	律阿保機	耶	HTeY9
W000109	顏阿骨打	完	QmBXQ
W000110	正明	段	Ep22y

完成之後再另存為csv的檔案格式上傳匯入至google即可!

如何快速進行資料選擇性統計？

學生平時小考成績一學期會有很多次，考慮學生可能會出缺席或表現失常，通常老師不會全部的小考成績都採計，最常採取的方式是擇優選取幾次成績計算，合理的採計方式應該是採計每個人最高分的幾次成績，而不是全班一起特定採計某幾次成績。

這樣的採計方式，必須先將每個人所有成績進行排序，再挑選出高分的幾次進行計算，把這個動作設計一個巨集，此巨集我放在『資料處理過濾增益集』中的『資料輸入統計』/『條件處理』，在此功能使用者可以在處理的資料中選取最高或最低幾次成績進行加總或平均計算。

 

以下表為例有六次小考，我要選擇每一個學生最好的4次成績平均，我可以這樣做：先將1~6次的成績選起來作為『資料處理的範圍』，因為每個學生的成績為一列，所以以預設的橫向處理即可，公式選擇AVERAGE』(平均)，處理條件設為『最高』，處理數目為4，設定之後按確定鈕。

 

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

運算結果會出現在最右一欄

班級	座號	姓名	第1次小考	第2次小考	第3次小考	第4次小考	第5次小考	第6次小考
205	1	蕭遠山	12	76	23	5	88	90	69.25
205	2	蕭峰	94	5	14	98	91.2	0	74.3
205	3	段正淳	82	0	2	56	90	95	80.75
205	4	段譽	12	55	36	90	0	90	67.75
205	5	虛竹	92	60	95	90	98	90	93.75
205	6	慕容博	12	100	100	1	0	0	53.25
205	7	慕容復	90	95	5	24	0	0	53.5
205	8	耶律阿保機	90	12	1	21	0	98	55.25
205	9	完顏阿骨打	90	12	90	0	12	95	71.75
205	10	段正明	12	12	2	0	76	99.2	49.8
205	11	包不同	12	5	10	90	80	12	48.5
205	12	段延慶	30	5	21	92	94	60	69
205	13	楊鐵心	21	0	90	95	95	12	75.25
205	14	楊康	12	21	12	21	23	95	40
205	15	楊過	60	82	30	100	0	92	83.5
205	16	郭嘯天	98	30	0	90	12	86	76
205	17	郭靖	76	92	30	90	91.2	12	87.3
205	18	張三豐	99.2	90	12	92	12	0	73.3
205	19	張翠山	100	26	12	56	94	1	69
205	20	宋遠橋	72	90	90	92	21	90	90.5
205	21	俞蓮舟	24	0	5	98	26	90	59.5
205	22	莫聲谷	98	98	100	33	55	5	87.75
205	24	殷梨亭	90	0	21	90	0	90	72.75
205	25	殷天正	80	21	21	60	93	2	63.5
205	26	殷野王	90	0	0	60	12	10	43
205	27	朱元璋	93	90	100	0	30	12	78.25
205	28	徐達	2	80	94	88	5	0	66.75
205	29	常遇春	21	76	92	36	12	95	74.75
205	31	冷謙	12	12	92	95	0	0	52.75
205	32	韋一笑	90	21	80	88	92	90	90
205	33	袁崇煥	72	30	90	0	21	12	53.25
205	34	袁承志	0	0	90	92	1	0	45.75
205	35	顧人清	0	0	90	12	60	14	44
205	36	胡一刀	12	100	90	98	86	90	94.5
205	37	胡斐	95	0	88	100	33	90	93.25

如何快速進行資料分組統計？

在學生考試成績出來，最常做的就是組距人數統計，例如100~90有多少人，設計一個巨集方便統計，此巨集我放在『資料處理過濾增益集』中的『資料輸入統計』/『範圍統計』，在數學上我們以[]表示包括端點，()表示不包括端點。以下表說明：

[0,10]   代表0~10分

(10,20] 代表11~20分

(20,30] 代表21~30分

(30,40] 代表31~40分

(40,50] 代表41~50分

(50,60] 代表51~60分

(60,70] 代表61~70分

(70,80] 代表71~80分

(80,90] 代表81~90分

(90,100]  代表91~100分

以下excel成績表為例，我要統計學期成績組距人數，然後以上表之分數間距進行人數統計。

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

如何做？

只要將學期成績選取起來，點選『資料輸入統計』/『範圍統計』，叫出『範圍統計』表單，在『資料處理的範圍』『選取』點選確定統計資料範圍，再按確定即可!

 

統計所得會加在資料欄旁。

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

使用者只要調整範圍參數中[]或()中的值，即可設計不同的統計間距範圍，例如要統計不及格與及格人數，只要將範圍參數設定為[0,60)[60,100] 即可統計出來。

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

利用Excel和『資料處理過濾增益集』進行檔案管理!

在學校會舉辦一些學生美術作品比賽，為了讓全校的師生能夠從網路上欣賞到同學的作品，必須將這些作品拍照數位化，因為本校的班級數非常多，一個年級將近三十個班，學生參加的作品總數有時超過百件，如何管理這些作品拍攝的數位照片，變成一個煩人的工作。

我們的作法會將學生的作品依班級座號先排好依序拍照，利用數位相機做簡單的檔案命名，但是要將照片資料放在網路上讓人欣賞，勢必要把照片檔案與作者資料進行連結，讓使用者可搜尋作者的作品，所以數位相機的檔案命名功能不足以應付這樣的功能，我希望直接將檔案名稱命名為學生的年級、班級、座號與姓名，這樣在網路上可以簡單利用檔名來查詢學生的作品。

所以我寫了一個巨集給教學組，後來把這個功能放在『資料處理過濾增益集』『WORD表單統計工具』中共有三個功能，分別是『讀取檔案目錄資料』、『更改檔案名稱』、『刪除檔案』。

 

1.『讀取檔案目錄資料』這個功能是將檔案的檔名目錄名稱寫入Excel工作表中，舉例我要將文件夾中學生的作品資料檔案目錄名稱讀至工作表中，點選『讀取檔案目錄資料』會執行下列檔案選取檔案的對話框，將所有檔案選取按『開啟』鈕。

程式會在工作表寫入選取的檔案的檔案目錄名稱，如下所示。

 2.『更改檔案名稱』的功能可以在工作表中進行更改檔案名稱的功能，使用者只要在前面讀取資料後一欄對應建立新的檔案目錄名稱，選取此兩欄資料，按下『更改檔案名稱』之即可進行更名。注意新的檔名目錄資料不可有重複的。

執行時程式會出現提醒的對話框

按『是』即完成更名動作

執行前的檔案名稱

 

執行更名後，有選到更名的檔案資料被變更名稱

3.『刪除檔案』：只要選取讀入檔案的目錄檔案名稱資料，點選『刪除檔案』即可。

例如在前例中我們要把沒有更名的檔案刪除，我們可以這樣做，選取未執行更改的資料。

點選『刪除檔案』

出現程式對話框提醒，刪除檔案無法復原!

刪除之後的目錄資料!

 注意此操作無法復原請謹慎使用，先做備份。

如何快速建立與刪除Excel的註解

在Excel註解這個功能可以幫助使用者得到資料格額外的資料，以下圖為例；我在學期成績這一欄每一個資料格的值，加上對應學生的名字作為註解，這樣不管姓名欄位距離有多遠，我可以輕易的看出這個成績是那一位同學的。

要如何才能做到上述效果，明顯不可能手動去一個個輸入加入，我在『資料處理過濾增益集』的『資料型態轉換』加入『加註解』與『刪除註解』這兩個功能。

功能說明：

『加註解』：先將要加註解的資料(如上圖中學期成績為要加註解的資料範圍)用滑鼠選取起來，在『加入註解』表單中『要加入註解資料範圍』按『選取』鈕，再選取要駐解的資料來源欄位(即上圖中學生姓名)，在『加入註解』表單中『註解資料來源欄位』按『選取』鈕，再按下表單上『確定』鈕，如果資料已經有註解資料程式會停止執行，請先將資料的註解刪除再執行。

『刪除註解』：要刪除註解只要將欲刪除註解的資料選起來，再按『刪除註解』即可全部刪除。

提高在Excel中輸入資料效率的小工具

因為授課的班級有七個班級，每次要輸入學生各種成績就是一個大工程，尤其計算平時成績時，雖然Excel已經蠻人性化的，不過隨著年紀漸長手腳眼力大不如前，經常不留意就輸入錯誤。

錯誤主要發生在使用Excel的表格輸入資料時，因為要在資料與Excel軟體視窗之間移動，在視覺上容易產生錯誤，尤其當資料欄位很多時，以往要降低錯誤就是請學生幫忙唸，自己一邊輸入。這樣不需要一邊看著試卷或成績表，一邊Key in輸入的速度與準確性的確會高一些，不過有時候所在的辦公室距離教室比較遠，實在不方便請學生大老遠來辦公室幫忙。

以下圖為例，雖然Excel可以透過『分割視窗』與『凍結窗格』功能，降低『輸入資料欄位』與『資料對應欄位』之間的距離，但是視線的範圍仍然是整個視窗，還是要拉橫向捲軸，才能降低『輸入資料欄位』與『資料對應欄位』之間的距離。

所以針對這樣的問題利用一點時間，設計一個小工具降低輸入的錯誤，提高工作效率，這個工具我放在『資料處理過濾增益集』的『資料輸入統計』中，這個工具分兩個部分，『輸入設定』與『資料輸入』。

『輸入設定』的部分，主要就是設定一些方便輸入的設定，以下簡單說明。

1.設定輸入資料的區域範圍，使用者可限制輸入區域的範圍，當輸入之資料區域超過設定的範圍，程式會自動換行，使用者若希望到邊界時程式發出提醒，可以開啟邊界提醒，程式在輸入邊界時會出現強制回應表單。

2.設定資料對應的欄位，也就是索引欄位，因為是設計在輸入成績使用，通常索引欄位不是座號，就是姓名，所以我保留兩個索引欄位設定。

3.資料輸入時按Enter資料或超過資料長度時，游標移動的方向，預設是縱向向下移動。

4.可以指定資料必須為數字，如果輸入不是數字會出現錯誤訊息的強制回應表單。

5.如果設定資料必須為數字，可以設定數字的範圍條件，以x代表輸入值，若希望輸入值介於0~100，則條件為 x>=0 and x<=100

6.可以限定資料長度，輸入超過長度的資料，游標自動移至下一個資料格

7.可以用符號替代特定值，例如用*代替100，則在符號對應中輸入 * 100 ，符號和對應值之間以空白分隔，可以輸入多組符號對應，同應以空白方隔。

 

『資料輸入』部分：使用者設定『輸入設定』之後，必須透過此功能的表單才會作用，以下簡單說明此功能的特性。

1.在『資料輸入查詢表單』可以進行資料查詢與輸入，如果我們手邊資料是已經排序好了，例如依座號資料大小排序，只要依序在輸入欄位輸入資料即可，不用輸入索引值，如果手邊要輸入的資料是無排序的，可以採取輸入索引值讓游標移動至所要輸入的位置，這樣可以省掉資料排序的時間，例如我經常拿一堆試卷要輸入成績，如果沒有事先將試卷按照順序排好，在輸入的時候就很麻煩，要找用眼睛去找試卷座號所在位置的資料區輸入，這很耗時間又容易出錯，另外如果索引有重複的情形，可以加入第二個索引以做判斷， 例如座號有重複者，可以輸入班級作為第二個索引。

2.為了評分方便，我把『資料輸入查詢表單』設定為所有試窗之上，所以老師可以不用在Excel的視窗下進行評分工作，舉例我在評學生影像軟體學習成果時，我可以不用在影像與Excel之間不斷切換，我可以透過檔案總管或影像呈現軟體的視窗中，一邊看學生作業一邊輸入，不用管Excel視窗，這樣在班級數很多時非常好用，尤其電腦課每班一週只有一節課，電腦老師的授課班級，有時高達20個班。