10.4 交叉表

10.4.1 交叉表舉例:銷售日報

如下圖所示的地區銷售匯總表是一種常見的統計報表樣式。它有一個特點:右下方的矩形區域C4:G11中的每個單格,都是同樣的資料類型,都表達一個意思----某地區某產品的銷售額。至於哪個單格堛獐あ鴐O哪種產品和哪個地區的銷售額,則由該單格所處的行(對應產品)和列(對應地區)決定。

8-16      交叉表的例子

這種樣式的統計表,稱為“交叉表”。

在上面的例子中,包含資料的區域是B3:G11,其最上面一行的內容表示地區,最左側的列表示產品名稱,從C4:G11的矩形區域表示銷售額。定義成資料表就是:

產品名稱

大區

銷售額

對應單格

白鐵皮

東北

8500

C4

白鐵皮

華南

40000

F4

薄板

東北

20200

C5

薄板

華北

150000

D5

花紋鋼板

東北

10000

C6

角鋼

華北

320000

D7

 

10.4.2 定義交叉表

新建範本《地區銷售匯總》,表樣如圖8-16所示。在其上定義單一資料項目的表“地區銷售匯總_主表”:

主鍵

區域

欄位名

資料類型

必填

填寫規範

其他

C2

年月

日期

 

 

按以下步驟操作,定義明細表:

1.  選中區域B4:G11,右擊滑鼠,選擇彈出功能表“定義重復資料項”。

2.  彈出對話方塊,如圖,單擊【交叉表】,選中“資料區域可向下擴展”,其他預設值不變,按【下一步】。

8-17-1   定義交叉表

3.  選擇“組成新資料表”,命名為“地區銷售匯總_明細”,按【下一步】。

4.  對話方塊顯示如下:

8-17-2   定義交叉表

5.更改欄位名與資料類型,結果如下:

8-17-3   定義交叉表

6.  點擊【下一步】,點擊【完成】。

 

交叉表中的資料項目分三種:1)列標欄位,是上方的若干行;2)行標欄位,是左側的若干列;3)資料欄位,是右下方的矩形區域。其中行標欄位和列標欄位可以是一個或多個,但資料欄位只能有一個。

10.4.3 交叉表產生的資料

針對交叉表所定義的明細資料表,其中欄位分三類,一是上方標題行對應的欄位,稱為列標欄位(因為其決定了列);二是左側標題列對應的欄位,稱為行標欄位(因為其決定了行);三是右下方的矩形區域對應的欄位,成為資料欄位。資料欄位只能有一個,列標或行標欄位可以有多個。在本例中,“大區”是列標欄位, “產品名稱”是行標欄位,“銷售額”是數據欄位。

手工填制一張地區銷售匯總,保存後,到我的工作臺中查看所產生的資料,如下圖所示:

8-18

由此可見,填制交叉表時,其資料欄位對應區域內的每個單格,都會產生一條記錄。

填報時,一張交叉表的資料欄位的區域中有多少個單格,就會產生多少條記錄。

 

10.4.4 用表間公式填充交叉表

下面我們來定義表間公式,完成地區銷售匯總表的自動統計。

先分析一下問題。我們的目的是統計銷售額。銷售額的發生是源自訂單,所以表間公式的來源資料應該是訂單。因為要按地區分別統計銷售額,所以還要確定每份訂單應歸屬哪個大區。訂單上並未直接指明地區,但指明了訂貨的客戶,而哪個客戶歸屬哪個大區,在“客戶登記表”中可以查到的,所以為了正確地統計出各地區的銷售額,需要把“訂單”和“客戶登記表”結合起來,需要用到表間關聯。

最後定義的表間公式如下:

第一條表間公式,提取所有的產品名稱,填充到交叉表的左側,注意此表間公式的執行方式是“打開後自動執行(初始填報)”

8-19

第二條表間公式,提取指定月份的訂單資訊,訂單表和客戶登記做關聯,得到的結果分別對“地區”和“產品名稱”做按行列匹配,把合計的金額填充到交叉表的資料區域中。

8-20

如果一條表間公式是用來填充交叉表資料欄位的,那麼在該公式的填充方式中要包含所有的列標和行標欄位,並且對它們做按行列匹配。