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

2010-10-21

6188
0
資料處理過濾增益集
2010-10-30

在擔任資訊組長的某一天，看到別的學校的首頁放著一個課表查詢系統覺得還蠻不錯的，可以線上查詢老師與班級的課表，就想在校網上也加入同樣的功能，詢問教學組所使用的排課系統是否有網路查詢的功能，結果並沒有這樣的功能。想要自己開發這樣的功能，卡在廠商的排課系統並沒有將資料庫開放，只能得到PDF檔案的課表或是Excel格式的報表。報表格式如下

(範例檔案)

這樣的樣式無法直接得到行列的資料表，要拿來利用必須重新處理，要如何處理？

要進行報表資料轉換最重要的關鍵是：找出報表資料對應的欄位與列位，因為報表還是從資料表產生，如果能夠找出報表中資料對應欄位與列位的關係，便有機會將報表轉為資料表。

首先我們來分析這個報表是否有規律性？如果完全沒有規律性，就不要浪費時間，直接放棄從此得到資料的念頭，要另尋他徑。

這個報表仔細的看了一下，大部分是有規律性，但是有一些例外在其中，不知道是廠商故意的，還是程式的bug，在教師的課表報表中的格式出現一些狀況，例如按理教師授課的班級與科目應該在欄位星期一~星期五中星期之下，可是有些是出現出現在星期欄位的隔壁欄位，例如上表範例中教師的星期三課表中，應該是連兩節在404班授國文課的狀況，可是資料卻是以下這種狀況，有一節是正確出現在星期三這一欄，但另一節課卻出現在星期三前面一欄，而且不是每一個人的課表都是如此。

不過觀察之後，這種情況還好處理，只要將兩個欄位複製進行合併在一個欄位，再貼回原來星期欄位，還是可以得到一個正確的星期欄位對授課科目節數班級資料。可以利用『資料處理過濾增益集』/『資料型態轉換』/『資料格以字串形式合併』可以快速將兩個欄位的資料合併成一個欄位。

經過上述的處理原則上欄位對應沒有太大問題，但是把它拿來用程式進行轉換才發現還有問題!因為除了欄位對應有問題，列位的對應也有問題，例如大部分老師的第4節至下午第5節，1200與1310之間隔2格空白，如下表。

但是某些老師中間間隔的空格只有1格空白，如下表。

這樣會造成利用程式進行轉換時對應錯誤!要如何處理？第一次的時候是手動逐一比對各個老師的課表進行修改，後來每學期都要這樣逐一修改實在有點煩，於是在『資料處理過濾增益集』/『正規比對資料過濾』/『工作表資料篩選』的功能我新增一個『特殊範圍篩選方式』，在這個功能中提供一個範圍篩選的方式。

以上述的狀況可以在表單進行這樣設定篩選，篩選的資料範圍選取B欄資料所在範圍($B$1:$B$5290)，再勾選『啟用特殊範圍篩選』，選擇『選取資料格所在列位』，在『特殊範圍篩選方式』方塊中選取『文字比對起始範』然後『範圍開始值』輸入1200，『範圍結束值』輸入1310，然後選擇『選取範圍中某幾個資料格』在輸入方快中輸入2，表示篩選1200~1310之間的第2格，再按下『篩選』鈕。