13.2 多範本對應到相同的資料表

13.2.1 資料是如何存儲的

通常的Excel檔,用戶對資料的操作與資料的存儲是一體的。檔本身就是用來保存資料的,下圖就是一個典型的例子:用一個檔來保存公司所有的客戶資訊,為了方便檢索,把不同辦事處的客戶分別存放在不同的工作表中。

136

Excel伺服器中,資料的錄入計算與資料的存儲是分離的。模版是資料的錄入介面,資料實際存儲在資料庫中。資料庫由一個個的“表”組成,資料存放在表中。每個表都有表頭/列標題,也就是我們所定義的資料項目(欄位)。表中存放的資料稱為記錄。

比如我們前面定義的《客戶登記表》模版,它實際上提供了一個錄入客戶資訊的操作介面,我們輸入三個客戶的資訊,並非意味著我們保存了三個檔,不是這樣的,我們沒有保存任何Excel檔,只是“通過”Excel的錄入介面,把這三個客戶的資訊保存到資料庫中一個名為“客戶登記表”的表中去,成為其中的三條記錄。請看下圖的示意:

137

熟悉SQL Server資料庫的用戶可以看到,在伺服器上名為ESApp的資料庫中有一個表,名為“客戶表登記表”,其中存放著我們通過《客戶登記表》模版輸入的所有客戶的資訊。

這樣做的好處是:

1.  沒有存儲容量的限制。一個Excel工作表,最多有65535行,更多的資訊就無法存放,資料存儲在資料庫中,可以不受檔大小的限制。

2.  既能對資料進行集中統一管理,又能實現精細的許可權劃分。

3.  可以充分利用資料庫技術的優點。例如:用主鍵來保證資料的唯一性。資料庫是對大量業務資料進行管理和操縱的成熟技術,讀者可參看有關資料庫概念和原理的書籍。

4.  便於對各種相關業務資料的綜合利用。

13.2.2 多範本“對應到”同一資料表的意義

企業中,往往存在大量十分相似的單據,比如:採購入庫單、生產入庫單、領料單、發貨單,它們都是庫存相關的單據,單據的內容和格式也很類似,可能只有個別欄位不同。再如:銀行收款單和現金收款單,它們的格式完全一樣,區別只在於:1)許可權不同,銀行收款單由銀行出納錄入,現金收款單由現金出納錄入;2)錄入時的資料規範不同,銀行收款單的收款方式包括“電匯、支票、匯票”等等。現金收款單的收款方式只有“現金”。

當我們用Excel伺服器來管理這些相似的單據時,需要考慮到以下幾方面問題:

首先,它們需要被定義成不同的範本,因為它們或者格式不同,或者許可權不同。

其次,它們經常要被統計報表同時引用,比如收款清單,要同時包含“銀行收款單”和“現金收款單”;庫存台帳,要同時從“採購入庫單”、“生產入庫單”、“領料單”、“退料單”中提取資料。

第三,它們可能要整體考慮資料的唯一性等,比如,如果一個客戶的付款資訊在“銀行收款單”中已經錄入了,就不能在“現金收款單”中重複錄入。

為了同時兼顧以上幾種要求,Excel伺服器中提供了“多範本對應到同一資料表”的功能,也就是說,我們針對不同的單據,建立不同的範本,但是在範本上定義的資料項目,都對應到資料庫中同一個資料表的欄位上,這樣一來:

l         因為是不同的範本,所以可以分別設置許可權、資料規範。

l         因為是同一個(ESApp資料庫中的)資料表,所以主鍵可以跨範本起作用。

l         因為是同一個(ESApp資料庫中的)資料表,所以可以用一條表間公式就提取出所有這些相似單據中的資料

 

下文以《銀行收款單》和《現金收款單》為例,說明如何使多範本“對應”到同一資料表。

13.2.3 多範本對應到同一資料表舉例

建立《銀行收款單》範本

1.  新建“銀行收款單”範本,表樣及屬性如下:

項目

內容

報表編號

 

報表名稱

銀行收款單

初始填報許可權

部門:A公司、財務部

角色:Administrators、銀行出納

最終查閱許可權

部門:A公司、總經辦、財務部

角色:Administrator、總經理、財務部經理、銀行出納

其他

接受預設值

表樣:

Excel公式

單元位址

公式

 

 

 

 

 

2.  在《銀行收款單》上定義資料表如下:

3.  定義適當的資料規範:

a)         “收款單號”用自動編號,

b)        “客戶名稱”用列表選擇,同時填入客戶名稱和客戶編號。

c)        付款方式為固定取值的下拉清單,可選項為:支票、匯票、電匯、

建立《現金收款單》範本

新建“現金收款單”範本,表樣及屬性如下:

項目

內容

報表編號

 

報表名稱

現金收款單

初始填報許可權

部門:A公司、財務部

角色:Administrators、現金出納

最終查閱許可權

部門:A公司、總經辦、財務部

角色:Administrator、總經理、財務部經理、現金出納

其他

接受預設值

表樣:

Excel公式

單元位址

公式

C5

=”現金

 

 

 

在《現金收款單》上定義資料項目,對應到“收款單”資料表

1.  在“現金收款單”範本的設計狀態下,選中要定義為資料項目的單格,選擇功能表【範本】à【定義單一資料項目】。

2.  彈出“定義單一資料項目”視窗,點擊【左側單元命名】,點擊【下一步】

3.  選擇“對應到以下資料表中欄位”,下拉選擇“收款單”,如圖,點擊【下一步】

139 對應到已有資料表

4.  依次單擊每行的欄位名,在出現的下拉清單中選擇該欄位要對應到“收款單”表中的哪個已有欄位,選擇後資料類型自動顯示,且不能修改。

1310 設置當前範本的資料項目如何對應到已有資料表的欄位

5.  點擊【完成】

定義“收款單查詢”

前面定義了兩個範本,“銀行收款單”和“現金收款單”,現在可以分別以銀行出納和現金出納的帳戶登錄,各自輸入幾張。從我的工作臺上看,兩種收款單是分別管理的,各自由不同的用戶使用。但它們的資料都寫入同一個資料表“收款單”中。

下面我們建立一個查詢,查詢全部收款單資訊,查詢定義如下圖所示。設置Administrator、財務部經理對此查詢有查閱許可權。

1311 收款查詢

關於建立自定義查詢的方法,可回顧第9章的相關內容。

請讀者用Admin帳戶登錄,到我的工作臺中分別查看“銀行收款單”、“新建收款單”、“收款查詢”的內容,以實際體會多範本對應到同一資料表的含義。