9.2 設計查詢範本

通過我的工作臺只能完成比較簡單的資料查詢，更多的時候，我們需要設計查詢範本，通過表間公式來完成資料的查詢工作。

看這樣一個例子，假設您是銷售部的統計員，月底您要製作一張本月（2007年5月）銷售情況的匯總表，如下圖所示：

圖 9 ‑ 6 銷售台賬表樣

設想一下您手工製作這張表格時的情形：您把本月發生的訂單都拿到手邊來，拿過一張《訂單》，把上面的訂單日期抄寫到B4，訂單編號抄寫到C4，客戶名稱抄寫到D4，…，錄入人抄寫到I4，然後把這張《訂單》放到一邊；再拿過一張《訂單》，訂單日期抄寫到B5，訂單編號抄寫到C5，客戶名稱抄寫到D5，…，錄入人抄寫到I5，…，最後，每一張《訂單》上的資訊都已抄到台帳上，2007年5月的《銷售台賬》就做完了。當然，所謂的“抄寫”也可以是“拷貝/粘貼”。

把上面的製作台賬的過程用一句話敘述一下，就是：

找來2007年5月1日至2007年5月31日之間登記的所有的《訂單》，把其中的資訊“抄寫/拷貝粘貼”到台賬的對應欄目中，每一張《訂單》登記台帳中的一行。

注意到我們已經建立了《訂單》範本，所有的訂單資訊都已經輸入電腦了，所以這種“抄寫/拷貝粘貼”過程完全可以定義成一條表間公式，由電腦自動完成。

9.2.1 建立銷售台帳範本

下面我們嘗試用另一種建立範本----把一個普通的Excel檔“另存為範本”。

1.  按上圖表樣建立一個Excel檔，命名為“銷售台帳.xls”

2.  用Admin帳戶登錄

3.  打開文件“銷售台帳.xls”。

4.  選擇功能表“範本à另存為範本”

圖 9 ‑ 7 把一個普通的Excel檔另存為範本

5.  彈出“新建範本”視窗，選擇“建立新範本”。

      6.

圖7-3        設置某範本為“純查詢”

7.  設置“初始填報許可權”，按【確定】，進入範本設計狀態。

8.  在銷售台帳範本上定義兩個資料表：“銷售台帳_主表”和“銷售台帳_明細”

銷售台帳_主表：

銷售台帳_明細：

9.2.2 按時間查詢訂單資訊

表間公式的來源資料是兩個資料表

在銷售台帳範本上定義這樣一條表間公式：

圖 9 ‑ 8 表間公式的來源資料是兩個表

注意它們和我們以前定義的表間公式相比有了兩點不同：1）來源資料是兩個表；2）填充方式中有的欄位從訂單_主表中取值，有的從訂單_明細中取值。

手動應用表間公式

1.  新填一張《銷售台賬》，輸入起始日期和結束日期

2.  單擊工具欄上的“應用公式”按鈕。

圖 9 ‑ 9 手工應用表間公式

3.

圖 9 ‑ 10 選擇要應用的表間公式

4.  對話方塊關閉，訂單資訊自動填充到台賬上。

9.2.3 控制表間公式結果的排序

我們可以設置表間公式結果的排序方式。假設我們希望歸集到銷售台帳中的訂單資訊按訂單日期由早到晚排序，同一天的訂單按客戶名稱排序，可以這樣做：

1.  打開《銷售台帳》範本，進入設計狀態。

2.  選擇功能表“範本à定義表間公式”。

3.  彈出“表間公式窗口”，選中“簡單查詢”，點擊【修改】按鈕。

圖 9 ‑ 11 修改表間公式

4.  彈出“定義表間公式”視窗，點擊【填充方式】，彈出“填充方式”按鈕。

5.  選擇“訂單日期”的排序方式為“昇冪”，選擇“客戶名稱”的排序方式為昇冪。如圖。點擊【確定】

圖 9 ‑ 12 設置表間公式結果的排序

6.  回到“定義表間公式”視窗，公式內容顯示如圖，在填充方式的“訂單日期”和“客戶名稱”後面加上了排序方式。

圖 9 ‑ 13

7.  點擊【確定】，回到“表間公式”窗口，點擊【關閉】

8.  關閉範本。

好了，現在再去填報一下《銷售台帳》，應用“簡單查詢”表間公式，觀察一下排序的效果。

9.2.4 表間關聯

下面我們來對《銷售台帳》範本做一下改進：我們希望同時看到每份訂單的銷售是由哪個大區實現的。

向資料表中增加欄位

1.  修改一下《銷售台帳》的表樣，在右方增加一列：大區

圖 9 ‑ 14 在《銷售台帳》的表樣中增加一列

2.  選中區域J4:J10，然後選擇功能表“範本à定義重復資料項”

3.  出現“定義重復資料項視窗”，選擇【按行】，上方標題行數1，選中“資料區域可向下擴展”，點擊【下一步】

4.  在下一步出現的視窗中，選擇“添加到以下資料表”，表名選擇“銷售台帳_明細”，點擊【下一步】

圖 9 ‑ 15 新資料項目添加到數據表

5.  設置“大區”的資料類型，然後點【完成】

圖 9 ‑ 16 設置新資料項目的資料類型

為什麼要做表間關聯

現在來分析一下問題。

我們看到，除了“大區”以外，銷售台帳上的其他內容都可以從“訂單”得到。但是“訂單”上偏偏沒有大區的資訊。

怎麼辦呢？訂單上雖然沒有大區的資訊，但是有客戶的資訊，根據客戶的資訊找到對應的“客戶登記表”，就可以查到該客戶所屬的大區。請看圖7-13的示意：

銷售台帳的資料來自兩個不同的範本----“訂單”和“客戶登記表”，這兩個範本上有共同的資料項目“客戶編號”，通過這個共同的資料項目，我們說兩個範本之間有了一種“關聯”，同一個客戶的“訂單”和“客戶登記表”的資料可以組合起來，形成銷售台帳中的一條資訊。

圖 9 ‑ 17 表間關聯的示意

定義帶表間關聯的表間公式

1.  在《銷售台帳》範本上新建表間公式，輸入公式名稱“表間關聯查詢”，應用方式選擇“手動執行”。

2.  點擊【來源資料】，彈出“選擇資料表”視窗。

3.  選擇三個資料表“訂單_主表”、“訂單_明細”和“客戶登記表”，在視窗下半部分的表格中選擇輸入表間關聯條件

圖 9 ‑ 18 輸入表間關聯條件

4.  點擊【確定】，回到“定義表間公式”窗口。

5.  繼續資料篩選條件和填充方式，最後完成的表間公式如下：

圖 9 ‑ 19 帶表間關聯條件的表間公式

9.2.5 組合條件查詢

現在進一步改進《銷售台賬範本》，在表頭再增加兩個條件：產品名稱和客戶名稱，

我們希望對四個條件做組合查詢：起止時間必須給定，如果給出了產品名稱，則查詢起止時間段內該產品的訂單，否則查詢起止時間段內全部產品的訂單；客戶名稱條件也是一樣，若給出客戶名稱，則查該客戶的，否則，查全部客戶的。下面我們僅用一條表間公式，來涵蓋所有可能的組合情況。

1.  如下圖所示，在“銷售台賬_主表”中增加兩個欄位：品名、客戶名

圖 9 ‑ 20 表頭增加條件欄位

2.  把已經定義的表間公式“表間關聯查詢”另存為一條新公式“組合條件查詢”。

3.  修改“組合條件查詢”表間公式，更改其篩選條件，最終結果為：

圖 9 ‑ 21 組合條件查詢表間公式

下面解釋一下修改後的篩選條件。我們在篩選條件中增加了兩項，首先是：

本報表.Sheet1:品名=’’ 或者 訂單_明細.產品名稱=本報表.Sheet1:品名

假設全部訂單中包含有p1和p2兩個品名，我們看看這個條件會得到什麼結果，如果我們不輸入品名條件，則無論訂單的品名是p1還是p2，上述條件均滿足（因為此時，本報表.Sheet1:品名=’’，“或者”的前半部分滿足），所以能查出所有品名的訂單；當我們輸入品名條件，比如p1，此時本報表.Sheet1:品名≠’’，“或者”的前半部分不滿足，對於品名為p1的訂單，“或者”的後半部分滿足，所以只能查詢出品名為p1的訂單。

增加的第二個條件，關於客戶名的篩選，原理是一樣的，只是其中用到了“形如”比較運算符。“形如”是一個字串比較運算符，我們說A形如B，意思是A包含了B的內容，例如：

“中國北京”形如“北京”，這是成立的，因為“中國北京”中包含了“北京”二字，但是“中國北京”形如“京都”，就不成立了。

利用“形如”比較，用戶可以不必記得客戶的完整名稱，只記得幾個字，比如，“客戶名”條件處輸入“山東”，則可以查詢處所有“客戶名稱”中包含“山東”字樣的客戶的訂單資訊。