一. 前言:
最近在準備一個證券公司專案的整測文件, 該專案主要係將 COBOL FILE 的資料, 轉到 DB. 在此狀況下, 需比對 2 個不同性質的資料來源 (一個是 COBOL FILE, 一個是 DB), 確定轉檔的資枓內容無誤, 但資料量又不少, 只能設計一些統計指標, 至少這些指標要能符合; 在這些指標符合的狀況下, 再隨機抽幾筆資料出來作比對.
因為找不到比較好的自動化比對方式, 只好用最笨的方式, 將執行結果的重要統計值, 輸入到 Excel 檔作比對. 但既然用了 Excel, 總不能還要人工去比對結果吧? 眼睛會花掉 ...
故製作了一些 Excel 檔案, 作為測試報告的文件之用.
範例文件, 請由此下載.
文件定義:
1.. 主文件: 即 Table 清單, 有建 Hyperlink 到子文件. 每個 Table 對應到一個子文件.
2.. 子文件: 即統計指標的測試記錄; 以 委託輸入主檔而言, 採用 '總委託張數' 作為統計指標. 內含至少 3 個 Worksheet:
(1) 說明: 該子文件的目的, 及一個日期的參數值
(2) SQL Script: 用以取得統計值的 SQL Script.
(3) 統計值測試記錄: 委託輸入主檔而言, 其 Worksheet 名稱為 '總計'
功能需求:
1.. 由主文件 Hyperlink 到子文件: 這個應該沒什麼太大問題, 反正就是 超連結. 如下圖:
2.. 子文件:
(1) 在統計指標的部份, 必須能參考 "說明" Worksheet 裡的日期欄位; 這樣要測試的日期 (例如; 委託日期), 就不用到每個 "統計值測試記錄" Worksheet 去改. 例如下圖的委託日期
--> 這個沒什麼太大的難度, 只要 =<WorksheetName>!<來源儲存格位址> 即可, 例如: =說明!$B$4
(2) 如上圖, 如果 DB 的統計值, 不等於 COBOL 的統計值, 要能夠呈現 紫色粗體字
--> 這個要用到 Conditional Formatting (設定格式化的條件)
(3) 如上圖, "驗測結果" 欄位, 必須能呈現 NG, OK 這 2 個狀況, 同時, 如果是 NG, 要能夠呈現 紅色粗體字.
--> 這個比較麻煩, 必須用到 IF 與 AND 函式, 同時, 也要用到 Conditional Formatting (設定格式化的條件)
二. 統計值呈現紫色粗體字實作:
1.. 選取要格式化的儲存格.
2.. 選取功能表.
3.. 如果沒有規則, 就新增一個, 本圖已設定規則; 故採用編輯規則作說明, 其實操作方式都相同
4.. 選取 "使用公式決定要格式化那些儲存格"; 並輸入公式; 最後, 選 [格式]
5.. 在格式的對話框裡, 就可以選擇符合上述公式時的文字顏色, 粗細 ... 等樣式.
6.. 操作心得: 該設定若要複製到一個方形區塊, 必須先完成一個 row 或 column, 再複製到其它 row 或 column; 不能一定複製到方形區塊; 否則, 規刖裡的公式, 不會跟著自動作調整.
三. 測試結果 呈現 紅色粗體字實作:
1.. 選擇儲存格, 並輸入公式, 例如: =IF(AND(C4=C10, D4=D10), "OK", "NG")
--> 採用 IF 與 AND 函式的結合
2.. 選取功能表.
3.. 如果沒有規則, 就新增一個, 本圖已設定規則; 故採用編輯規則作說明, 其實操作方式都相同
4.. 選取 "只格式化包含下列的儲存格"; 並輸入規則 最後, 選 [格式]
5.. 在格式的對話框裡, 就可以選擇符合上述公式時的文字顏色, 粗細 ... 等樣式.
四. 參考文件
1.. MS Excel 2010: Change the font color based on the value in the cell: 這篇主要在介紹如何利用 Conditional Formatting
2.. Create an external reference (link) to a cell range in another workbook: 這篇主要在介紹如何建立跨 Workbook 的儲存格參考.
例如: ='[00-主文件.xlsx]測試初始資料'!$B$2
沒有留言:
張貼留言