Excel服務(wù)器認證工程師培訓教材

1、圖 21點擊【確定】，畫板關(guān)閉，回到模板的設(shè)計狀態(tài)，保存模板。在工作流畫板上，可以拖動任務(wù)圖形，改變其位置；也可以在選中一條連線后，拖動連線兩端的紅色連接點，改變連線與任務(wù)圖形的連接位置1.1 操作工作流本節(jié)以費用報銷流程為例，敘述表單如何按工作流的規(guī)定進行流轉(zhuǎn)，同時說明在定義了工作流的情況下，表單的填報、修改、刪除的操作方式發(fā)生了哪些變化。1.1.1 啟動工作流用銷售員小宋的帳戶xiaosong登錄，輸入一份費用報銷單，如下圖：圖 22在填制費用報銷單時，光標無法進入第5行以下的區(qū)域，因為這是工作流的第一步，此步驟的有效區(qū)域只包括了5個數(shù)據(jù)項：報銷單號、費用類別、報銷人、申請日期、事由。另外

2、，盡管我們設(shè)置了“部門領(lǐng)導簽字”的數(shù)據(jù)填寫規(guī)范是“當前用戶姓名”，但因為“部門領(lǐng)導簽字”不是本步驟的有效區(qū)域，所以當前用戶姓名“小宋”不會出現(xiàn)在“部門領(lǐng)導簽字”處。保存報銷單，打開“我的工作臺”，會看到這張報銷單的信息。請注意，選中這張報銷單后，在工具欄上有“新建”和“查看”，而沒有“修改”和“刪除”。 圖 23在有工作流的情況下，不能對表單數(shù)據(jù)直接進行修改和刪除，而是代之以“重做”和“撤銷”1.1.2 處理待辦事宜按照工作流的定義，剛才小宋填制的報銷單會直接提交到小宋的上級-銷售部肖部zha長那里審批。現(xiàn)在改用肖部長的帳戶xiaobz登錄（回顧第3章的練習，在那里我們建立了帳戶），在我的工作

3、臺中出現(xiàn)一條待辦事宜，如下圖：圖 24 在我的工作臺中查看待辦事宜點擊工具欄按鈕【辦理】，打開這張待審批的報銷單，報銷單內(nèi)容顯示在屏幕上，但不能改，只有“部門審批意見”、“部門領(lǐng)導簽字”兩處能輸入內(nèi)容。因為我們在定義工作流時，已指定“部門審批”任務(wù)的有效區(qū)域只有這兩個數(shù)據(jù)項。圖 25 辦理待辦事宜輸入審批意見后，保存，把銷單窗口關(guān)閉，再回到我的工作臺中，我們會發(fā)現(xiàn)待辦事宜列表中已經(jīng)沒有內(nèi)容了。處理待辦事宜的時候，可以使用報表工具欄上的兩個按鈕?！緯捍妗堪粹o：暫存和保存的區(qū)別是，暫存后，此項依然還在待辦事宜列表中?！就嘶亍堪粹o：可以直接退給交辦人。交辦人將在自己的待辦事宜列表中看到被退回的事項。

4、1.1.3 查看工作進度工作流是多人協(xié)同完成一件事情的典型例子。流程中的個人，除了完成自己份內(nèi)的工作外，還經(jīng)常要關(guān)心其他人的工作進行情況。在我們的費用報銷的例子中，如果報銷人提出申請之后，希望了解審批進行到了哪一步，可以這樣來做：1） 用小宋的帳戶登錄2） 打開我的工作臺3） 展開左側(cè)樹型中的“已處理工作”，選擇“費用報銷單_申請報銷”，右側(cè)列出所有本人提交的報銷單。選中要查看的那一條，右擊鼠標，彈出菜單，如下圖所示：圖 26 查看已處理工作的后續(xù)進展4） 選擇“查看進度”，彈出如下的對話框：圖 27 工作流進度可以看到肖部長已經(jīng)完成審批。報銷單已轉(zhuǎn)到財務(wù)部錢部長那里等待審核。1.1.4 重做

5、和撤銷在有工作流的情況下，一份表單要經(jīng)過多次流轉(zhuǎn)，最終在我的工作臺中顯示的數(shù)據(jù)是多個步驟綜合作用的結(jié)果。每個步驟一經(jīng)提交，在后續(xù)步驟還沒有辦理的情況下，是可以修改的，后續(xù)步驟一旦辦理，就不能再改動，除非請后續(xù)步驟的辦理人把這份表單“退回來”。也即，帶工作流的表單，不能按通常的方式做“修改”和“刪除”，只能是“逆”表單提交的順序，步步后退式地進行“重做”和“撤銷”。以上面的報銷單為例，假設(shè)小宋填報了報銷單之后，發(fā)現(xiàn)報銷金額寫錯了，要修改，可以這樣操作：1） 打開我的工作臺。2） 在已處理工作中找到提交的那張報銷單3） 選擇右鍵菜單“重做”，如下圖所示：4） 很遺憾，系統(tǒng)給出了這樣的提示：圖 28

6、為什么？因為我們通過查看工作進度已經(jīng)看到了，后續(xù)步驟“部門審批”已被肖部長辦理完了。怎么辦？請肖部長這張報銷單退回來（撤消審批）。5） 現(xiàn)在斷開連接，用肖部長的帳戶登錄。6） 在我的工作臺中展開“費用報銷單”模板，找到這張報銷單，點擊鼠標右鍵，選擇彈出菜單“撤銷”圖 29 “撤銷”已處理工作7） 系統(tǒng)提示：“本步工作已被撤銷”。點擊“待辦事宜”，可看到這張報銷單重新回到待辦事宜列表中，等待再次被審批。8） 重新用小宋的帳戶登錄9） 如第3）步一般重做，這次系統(tǒng)會打開這張報銷單，修改報銷金額，保存。報銷單被再次提交審批。1.2 本章練習改進第6章建立的訂單模板，加上工作流，包含兩個任務(wù)，第一個任

7、務(wù)是制單，有效區(qū)域是除“審批人”、“審批日期”、“審批意見”外的所有字段；第二個任務(wù)是審批，有效區(qū)域是“審批人”、“審批日期”和“審批意見”，由“銷售部銷售部長”執(zhí)行。52 / 52第2章、 回寫公式表間公式還有一種特殊的類型，稱為回寫公式。普通的表間公式的作用是利用以前輸入的數(shù)據(jù)填充當前表單，而回寫公式的作用是利用當前表單所填的數(shù)據(jù)刷新已存在的數(shù)據(jù)，方向剛好相反。在前面的章節(jié)中，我們已經(jīng)建立了物料表模板，其中記錄著每種物料的當前庫存量，這一章，我們要建立一個出庫單模板，它根據(jù)訂單的內(nèi)容進行出庫，每次出庫之后，要根據(jù)出庫數(shù)量動態(tài)調(diào)整物料表上的當前庫存量，這樣才能保證庫存數(shù)據(jù)的正確。我們要在出庫

8、單模板上定義“回寫公式”實現(xiàn)數(shù)據(jù)的動態(tài)調(diào)整、同步更新。圖 21 回寫公式示意2.1 建立出庫單模板本節(jié)建立本章的例子模板-出庫單模板，同時復習前面章節(jié)的內(nèi)容。表樣建立出庫單模板，表樣如下圖所示圖 22 出庫單模板表樣輸入若干Excel公式：單元格/區(qū)域公式作用G7:G10=E5*F5金額數(shù)量×單價G9=SUM(G5:G8)計算合計金額數(shù)據(jù)表定義定義兩個數(shù)據(jù)表：“出庫單_主表”和“出庫單_明細”圖 23 “出庫單_主表”數(shù)據(jù)項定義圖 24 “出庫單_明細”數(shù)據(jù)表定義數(shù)據(jù)規(guī)范定義一個數(shù)據(jù)規(guī)范“訂單號下拉列表”，并設(shè)置其為“出庫單_主表”中“訂單號”字段的填寫規(guī)范。圖 25定義自動編號“出

9、庫單號”，并設(shè)置其為“出庫單_主表”中“單號”字段的填寫規(guī)范。表間公式定義定義一條表間公式，當輸入“訂單號”之后，自動把該訂單的明細信息查出來，填充到“出庫單_明細”上。圖 262.2 用回寫公式更新數(shù)據(jù)本節(jié)我們來定義回寫公式，實現(xiàn)根據(jù)出庫單的數(shù)據(jù)實時更新物料表的當前庫存信息。2.2.1 定義回寫公式按以下步驟操作1） 打開出庫單模板，進入設(shè)計狀態(tài)2） 選擇菜單【模板】à【定義表間公式】，打開“表間公式”窗口3） 點擊【新增】按鈕，打開“選擇表間公式類型”窗口，選擇第二項“回寫（修改）”，點擊【確定】。圖 27 選擇表間公式類型4） 彈出“回寫公式”對話框，公式名稱輸入“保存時減少庫

10、存量”，應用時機為“保存時”，如圖：圖 28 定義回寫公式5） 點擊【選擇數(shù)據(jù)表】按鈕，彈出“選擇數(shù)據(jù)表”窗口，選擇“物料表”，按【確定】回到“回寫公式”窗口。圖 29 選擇數(shù)據(jù)表6） 回到“回寫公式”窗口，點擊【篩選條件】按鈕，彈出“輸入表達式”對話框，輸入篩選條件：物料表.物料編碼 = 本報表.Sheet1:編碼，按【確定】回到“回寫公式”窗口。7） 點擊【回寫數(shù)據(jù)項】按鈕，彈出“定義回寫數(shù)據(jù)項”按鈕，在最后一行“當前庫存數(shù)量”的左側(cè)雙擊。雙擊此處輸入表達式 圖 2108） 彈出“輸入表達式”窗口，輸入表達式為：物料表.當前庫存數(shù)量 - 本報表.Sheet1:數(shù)量，按【確定】回到“定義回寫

11、數(shù)據(jù)項”窗口。9） 按【確定】回到“回寫公式”窗口，完整的回寫公式如下：圖 21110） 按【保存】回到“表間公式”窗口，點擊【關(guān)閉】這條回寫公式的意思是：當出庫保存的時候，按照出庫單上的編碼在物料表中找到該物料的信息，修改其當前庫存數(shù)量：在原來的基礎(chǔ)（物料表.當前庫存數(shù)量）上，減去本出庫單上的出庫數(shù)量（- 本報表.Sheet1:數(shù)量）。2.2.2 回寫公式成對出現(xiàn)因為表單有保存也會有刪除，待辦事宜有提交也會有撤銷，所以回寫公式通常要成對出現(xiàn)。下面我們再定義一條表間公式，說明當出庫單一旦刪除，如何把相應物料的出庫數(shù)量恢復為原值?；貙懝蕉x如下圖：圖 212 刪除或撤銷時執(zhí)行的回寫公式2.2.

12、3 應用回寫公式請讀者自行練習，輸入一份出庫單，保存，然后到我的工作臺中查看物料表，看其當前庫存數(shù)量的變化；隨后刪除此出庫單，再次觀察物料的當前庫存數(shù)量發(fā)生了什么變化。2.3 回寫公式練習在出庫單模板上，我們對“訂單號”字段設(shè)置了數(shù)據(jù)規(guī)范，那里是一個下拉列表，包含所有訂單號。這里有一個問題，假設(shè)我們針對訂單號“001”已經(jīng)填寫了出庫單，再次填寫下一張出庫單時，“001”還是會出現(xiàn)在訂單號下拉列表中，這就有可能導致重復，一份訂單做了兩次出庫。如果能做到每填一張出庫單，訂單號下拉列表中自動就能減少一項，也就是說，讓訂單號下拉列表中只包含未填過出庫單的訂單信息，就可以解決這個問題。本節(jié)我們以此問題為

13、例，復習回寫公式的應用訂單模板增加“狀態(tài)”字段打開訂單模板，進入設(shè)計狀態(tài)，在表樣上增加“狀態(tài)”，如圖，按以下步驟操作：圖 213 訂單模板增加“狀態(tài)”字段1） 選中C14單元格，點擊鼠標右鍵，選擇菜單【定義單一數(shù)據(jù)項】2） 彈出“定義單一數(shù)據(jù)項”窗口，點擊【左側(cè)單元命名】，點擊【下一步】3） 選中“添加到以下數(shù)據(jù)表”，下拉選擇“訂單_主表”，如圖，點擊【下一步】圖 2144） 選擇“狀態(tài)”的數(shù)據(jù)類型為“文字(20字以內(nèi))”，點擊【完成】圖 215用回寫公式動態(tài)刷新訂單狀態(tài)下面請讀者自行練習，在出庫單模板上增加兩條回寫公式：保存出庫單時把訂單的狀態(tài)改為“已發(fā)貨”；刪除出庫單時把訂單的狀態(tài)置為空白

14、。圖 216圖 217在表達式中，如果需要用到字符型的常量，需要用單引號括起來。用表間公式構(gòu)造下拉列表我們在“出庫單”模板上通過一個數(shù)據(jù)規(guī)范構(gòu)造了訂單號的下拉列表，填寫出庫單的時候，可以選擇需要的訂單號。這里存在一個問題，假如針對一份訂單，例如001號，已經(jīng)開具了出庫單，那么繼續(xù)開具出庫單的時候，就不能再選擇001號訂單了，否則發(fā)生了重復出庫，所以，確切地說，我們需要的不是“訂單號”的下拉列表，而是“未發(fā)貨的訂單號”的下拉列表?，F(xiàn)在，我們來學習另一種構(gòu)造下拉列表的方式-通過表間公式來構(gòu)造下拉列表1） 在出庫單模板上定義表間公式，數(shù)據(jù)源選擇“訂單_主表”，篩選條件輸入：訂單_主表.狀態(tài)<&

15、gt;已發(fā)貨。2） 構(gòu)造填充方式如下，注意“操作”要選成“構(gòu)造下拉選項”圖 2183） 完整定義的表間公式如下，注意我們選擇的填充方式是“打開后自動執(zhí)行（初始填報）”圖 2194） 關(guān)閉模板請讀者自行練習，連續(xù)填兩張出庫單，注意觀察訂單號下拉列表中內(nèi)容的變化通過表間公式構(gòu)造下拉選項的操作有兩種選擇，一是“構(gòu)造下拉選項”，二是“構(gòu)造可編輯下拉”，它們的區(qū)別是：如果選擇了“構(gòu)造可編輯下拉”，則填報時，不僅可以從下拉列表中選擇，還可以手工輸入下拉選項中不存在的內(nèi)容。思考題假如我們讓銷售員來填寫訂單，我們希望“客戶名稱”下拉列表中出現(xiàn)的都是銷售員本人負責的客戶，不出現(xiàn)他人的客戶，該如何改進訂單模板？2

16、.4 模板鎖定通過表間公式和回寫公式的學習，我們始終在認識和強化一個觀念-“系統(tǒng)”的觀念，我們要做的是一個信息“系統(tǒng)”，信息系統(tǒng)不同于單純的數(shù)據(jù)處理，系統(tǒng)中的各類信息不是孤立存在的，實際的業(yè)務(wù)流程決定了不同種類的信息之間的關(guān)系，它們要彼此參照，彼此同步，相互制約?；谏瞎?jié)的例子-根據(jù)出庫單動態(tài)刷新訂單的狀態(tài)，我們再舉一個例子-限制一份訂單一旦發(fā)貨，便不可以修改和刪除。1） 用設(shè)計者的身份登錄到客戶端，選擇菜單【模板】à【打開模板】2） 選擇“訂單”模板，點擊【更改屬性】按鈕，打開“模板屬性”窗口。3） 切換到“版本”標簽，勾選“滿足下列表達式的報表將被鎖定，不可修改和刪除”，點擊【表

17、達式】按鈕。圖 2204） 輸入表達式，結(jié)果如圖：圖 2215） 點擊【確定】現(xiàn)在去填寫一份出庫單，保存，然后到我的工作臺中查看訂單信息，我們會發(fā)現(xiàn)，出庫的訂單的狀態(tài)已變成“已發(fā)貨”，并標志了鎖定。此時若選中該訂單，工具欄上只有【查看】，沒有【修改】和【刪除】圖 222第3章、 數(shù)據(jù)查詢本章學習數(shù)據(jù)查詢的各種方法，包括l 在我的工作臺中查詢數(shù)據(jù)l 自定義查詢l 定義查詢模板，通過表間公式做綜合查詢3.1 我的工作臺中查詢數(shù)據(jù)對單一模板的數(shù)據(jù)進行查詢，最簡單的方式是通過我的工作臺提供的功能完成在我的工作臺中分頁顯示數(shù)據(jù)當模板數(shù)據(jù)比較多時，可以在我的工作臺中分頁顯示，如圖，在右側(cè)數(shù)據(jù)表格的上方，可

18、輸入每頁的記錄條數(shù)，以及進行翻頁和跳轉(zhuǎn)到指定頁的操作。圖 31 在我的工作臺中分頁顯示數(shù)據(jù)在我的工作臺中進行查找在左側(cè)樹型中點中模板名稱，該模板已填寫的所有數(shù)據(jù)列示在右側(cè)的列表中。點擊窗口上方的“查找”工具欄按鈕，彈出以下窗口，可輸入查找條件：圖 32 輸入查找條件窗口中列出了所有可用作查找條件的數(shù)據(jù)項。如果您需要按某數(shù)據(jù)項進行查找，點擊此項旁邊的“比較”欄，可從下拉列表中選取某個比較運算符，再輸入比較值?？赏瑫r針對多個數(shù)據(jù)項指定比較條件，這些條件必須同時成立。所有條件都輸入完成后，按【確定】，窗口關(guān)閉，右側(cè)列表中顯示所有符合條件的數(shù)據(jù)。我的工作臺中分類顯示數(shù)據(jù)數(shù)據(jù)較多時，還可以在我的工作臺左

19、側(cè)的模板名稱之下，區(qū)分子文件夾，分類顯示數(shù)據(jù)，如下圖所示。分類方法需要在“管理數(shù)據(jù)表”窗口中定義，具體操作請參見第6章。圖 33 在我的工作臺中分類顯示數(shù)據(jù)3.1.2 從我的工作臺中導出數(shù)據(jù)某些時候，我們可能需要把我的工作臺中看到的數(shù)據(jù)導入到一個本地文件中，進行再加工，或發(fā)送給他人。Excel服務(wù)器提供了導出我的工作臺中數(shù)據(jù)的功能。是否允許用戶導出數(shù)據(jù)，需要在模板的“高級查閱權(quán)限”中設(shè)置。設(shè)置導出權(quán)限1） 打開“模板屬性”窗口，切換到“最終查閱權(quán)限”標簽2） 點擊【高級查閱權(quán)限】按鈕，打開“高級查閱權(quán)限”窗口3） 在“允許導出”欄打勾，指定哪些角色可以導出數(shù)據(jù)圖 344） 點擊【確定】，回到上

20、一級窗口，再次點擊【確定】5） 關(guān)閉模板。導出數(shù)據(jù)清單在我的工作臺左側(cè)選中要導出數(shù)據(jù)的模板，點擊右鍵，選擇彈出窗口【導出.】à【導出成Excel文件】，在彈出窗口中選擇目錄，輸入文件名，則把右側(cè)顯示的客戶信息導出到指定的Excel文件中。圖 35 導出數(shù)據(jù)批量打印（或?qū)С觯┍韱挝募承r候，我們需要把查詢到的數(shù)據(jù)按照模板的表樣批量打印或批量導出表單文件，方法是：1） 在右側(cè)數(shù)據(jù)表格的上方勾選“多選”，則每條記錄的左側(cè)出現(xiàn)復選框。圖 36 設(shè)置多選2） 在“選擇”列選中要打印或?qū)С龅臄?shù)據(jù)，然后在左側(cè)模板名稱節(jié)點上點右鍵，選擇彈出菜單【導出或打印Excel文件】，按提示操作即可。3.2

21、設(shè)計查詢模板通過我的工作臺只能完成比較簡單的數(shù)據(jù)查詢，更多的時候，我們需要設(shè)計查詢模板，通過表間公式來完成數(shù)據(jù)的查詢工作?？催@樣一個例子，假設(shè)您是銷售部的統(tǒng)計員，月底您要制作一張本月（2007年5月）銷售情況的匯總表，如下圖所示：圖 37 銷售臺賬表樣設(shè)想一下您手工制作這張表格時的情形：您把本月發(fā)生的訂單都拿到手邊來，拿過一張訂單，把上面的訂單日期抄寫到B4，訂單編號抄寫到C4，客戶名稱抄寫到D4，錄入人抄寫到I4，然后把這張訂單放到一邊；再拿過一張訂單，訂單日期抄寫到B5，訂單編號抄寫到C5，客戶名稱抄寫到D5，錄入人抄寫到I5，最后，每一張訂單上的信息都已抄到臺帳上，2007年5月的銷售臺

22、賬就做完了。當然，所謂的“抄寫”也可以是“拷貝/粘貼”。把上面的制作臺賬的過程用一句話敘述一下，就是：找來2007年5月1日至2007年5月31日之間登記的所有的訂單，把其中的信息“抄寫/拷貝粘貼”到臺賬的對應欄目中，每一張訂單登記臺帳中的一行。注意到我們已經(jīng)建立了訂單模板，所有的訂單信息都已經(jīng)輸入計算機了，所以這種“抄寫/拷貝粘貼”過程完全可以定義成一條表間公式，由計算機自動完成。3.2.1 建立銷售臺帳模板下面我們嘗試用另一種建立模板-把一個普通的Excel文件“另存為模板”。1） 按上圖表樣建立一個Excel文件，命名為“銷售臺帳.xls”2） 用Admin帳戶登錄3） 打開文件“銷售臺

23、帳.xls”。4） 選擇菜單“模板à另存為模板”圖 38 把一個普通的Excel文件另存為模板5） 彈出“新建模板”窗口，選擇“建立新模板”。6） 彈出“模板屬性”窗口，輸入模板的編號、名稱，注意要選中“本模板為純查詢報表”。圖7-3設(shè)置某模板為“純查詢”7） 設(shè)置“初始填報權(quán)限”，按【確定】，進入模板設(shè)計狀態(tài)。8） 在銷售臺帳模板上定義兩個數(shù)據(jù)表：“銷售臺帳_主表”和“銷售臺帳_明細”銷售臺帳_主表：主鍵區(qū)域字段名數(shù)據(jù)類型必填填寫規(guī)范其它C2起始日期日期E2:F2截止日期日期當前日期銷售臺帳_明細：主鍵區(qū)域字段名數(shù)據(jù)類型必填填寫規(guī)范其它B4:B10訂單日期日期C4:C10訂單號文字

24、(20字以內(nèi))D4:D10客戶名稱名稱或地址E4:E10產(chǎn)品名稱名稱或地址F4:F10規(guī)格型號文字(100字以內(nèi))G4:G10訂貨數(shù)量小數(shù)H4:H10訂貨金額金額I4:I10銷售員文字(20字以內(nèi))3.2.2 按時間查詢訂單信息表間公式的來源數(shù)據(jù)是兩個數(shù)據(jù)表在銷售臺帳模板上定義這樣一條表間公式：圖 39 表間公式的來源數(shù)據(jù)是兩個表注意它們和我們以前定義的表間公式相比有了兩點不同：1）來源數(shù)據(jù)是兩個表；2）填充方式中有的字段從訂單_主表中取值，有的從訂單_明細中取值。手動應用表間公式1） 新填一張銷售臺賬，輸入起始日期和結(jié)束日期2） 單擊工具欄上的“應用公式”按鈕。圖 310 手工應用表間公式3

25、） 彈出應用公式對話框，選中“簡單查詢”左側(cè)的復選框，按【應用】，如圖：圖 311 選擇要應用的表間公式4） 對話框關(guān)閉，訂單信息自動填充到臺賬上。顧名思義，“純查詢”模板只是用來查看數(shù)據(jù)，不能保存。若填報銷售臺帳時點擊，系統(tǒng)沒有任何響應。3.2.3 控制表間公式結(jié)果的排序我們可以設(shè)置表間公式結(jié)果的排序方式。假設(shè)我們希望歸集到銷售臺帳中的訂單信息按訂單日期由早到晚排序，同一天的訂單按客戶名稱排序，可以這樣做：1） 打開銷售臺帳模板，進入設(shè)計狀態(tài)。2） 選擇菜單“模板à定義表間公式”。3） 彈出“表間公式窗口”，選中“簡單查詢”，點擊【修改】按鈕。圖 312 修改表間公式4） 彈出“定

26、義表間公式”窗口，點擊【填充方式】，彈出“填充方式”窗口。5） 選擇“訂單日期”的排序方式為“升序”，選擇“客戶名稱”的排序方式為升序。如圖。點擊【確定】圖 313 設(shè)置表間公式結(jié)果的排序6） 回到“定義表間公式”窗口，公式內(nèi)容顯示如圖，在填充方式的“訂單日期”和“客戶名稱”后面加上了排序方式。圖 3147） 點擊【確定】，回到“表間公式”窗口，點擊【關(guān)閉】8） 關(guān)閉模板。好了，現(xiàn)在再去填報一下銷售臺帳，應用“簡單查詢”表間公式，觀察一下排序的效果。假如我們希望的排序順序是：先按照客戶名稱排，客戶名稱相同的再按訂單日期排，那么不僅需要設(shè)置“訂單日期”和“客戶名稱”的排序方式，還需要點擊“填充方

27、式”窗口上的【上移】或【下移】按鈕，把“客戶名稱”移到“訂單日期”之上。3.2.4 表間關(guān)聯(lián)下面我們來對銷售臺帳模板做一下改進：我們希望同時看到每份訂單的銷售是由哪個大區(qū)實現(xiàn)的。向數(shù)據(jù)表中增加字段1） 修改一下銷售臺帳的表樣，在右方增加一列：大區(qū)圖 315 在銷售臺帳的表樣中增加一列2） 選中區(qū)域J4:J10，然后選擇菜單“模板à定義重復數(shù)據(jù)項”3） 出現(xiàn)“定義重復數(shù)據(jù)項窗口”，選擇【按行】，上方標題行數(shù)1，選中“數(shù)據(jù)區(qū)域可向下擴展”，點擊【下一步】4） 在下一步出現(xiàn)的窗口中，選擇“添加到以下數(shù)據(jù)表”，表名選擇“銷售臺帳_明細”，點擊【下一步】圖 316 新數(shù)據(jù)項添加到數(shù)據(jù)表5） 設(shè)

28、置“大區(qū)”的數(shù)據(jù)類型，然后點【完成】圖 317 設(shè)置新數(shù)據(jù)項的數(shù)據(jù)類型為什么要做表間關(guān)聯(lián)現(xiàn)在來分析一下問題。我們看到，除了“大區(qū)”以外，銷售臺帳上的其它內(nèi)容都可以從“訂單”得到。但是“訂單”上偏偏沒有大區(qū)的信息。怎么辦呢？訂單上雖然沒有大區(qū)的信息，但是有客戶的信息，根據(jù)客戶的信息找到對應的“客戶登記表”，就可以查到該客戶所屬的大區(qū)。請看圖7-13的示意：銷售臺帳的數(shù)據(jù)來自兩個不同的模板-“訂單”和“客戶登記表”，這兩個模板上有共同的數(shù)據(jù)項“客戶編號”，通過這個共同的數(shù)據(jù)項，我們說兩個模板之間有了一種“關(guān)聯(lián)”，同一個客戶的“訂單”和“客戶登記表”的數(shù)據(jù)可以組合起來，形成銷售臺帳中的一條信息。圖

29、318 表間關(guān)聯(lián)的示意定義帶表間關(guān)聯(lián)的表間公式1） 在銷售臺帳模板上新建表間公式，輸入公式名稱“表間關(guān)聯(lián)查詢”，應用方式選擇“手動執(zhí)行”。2） 點擊【來源數(shù)據(jù)】，彈出“選擇數(shù)據(jù)表”窗口。3） 選擇三個數(shù)據(jù)表“訂單_主表”、“訂單_明細”和“客戶登記表”，在窗口下半部分的表格中選擇輸入表間關(guān)聯(lián)條件圖 319 輸入表間關(guān)聯(lián)條件4） 點擊【確定】，回到“定義表間公式”窗口。5） 繼續(xù)數(shù)據(jù)篩選條件和填充方式，最后完成的表間公式如下：圖 320 帶表間關(guān)聯(lián)條件的表間公式3.2.5 組合條件查詢現(xiàn)在進一步改進銷售臺賬模板，在表頭再增加兩個條件：產(chǎn)品名稱和客戶名稱，我們希望對四個條件做組合查詢：起止時間必須

30、給定，如果給出了產(chǎn)品名稱，則查詢起止時間段內(nèi)該產(chǎn)品的訂單，否則查詢起止時間段內(nèi)全部產(chǎn)品的訂單；客戶名稱條件也是一樣，若給出客戶名稱，則查該客戶的，否則，查全部客戶的。下面我們僅用一條表間公式，來涵蓋所有可能的組合情況。1） 如下圖所示，在“銷售臺賬_主表”中增加兩個字段：品名、客戶名圖 321 表頭增加條件字段2） 把已經(jīng)定義的表間公式“表間關(guān)聯(lián)查詢”另存為一條新公式“組合條件查詢”。3） 修改“組合條件查詢”表間公式，更改其篩選條件，最終結(jié)果為：圖 322 組合條件查詢表間公式下面解釋一下修改后的篩選條件。我們在篩選條件中增加了兩項，首先是：本報表.Sheet1:品名= 或者 訂單_明細.產(chǎn)

31、品名稱=本報表.Sheet1:品名假設(shè)全部訂單中包含有p1和p2兩個品名，我們看看這個條件會得到什么結(jié)果，如果我們不輸入品名條件，則無論訂單的品名是p1還是p2，上述條件均滿足（因為此時，本報表.Sheet1:品名=，“或者”的前半部分滿足），所以能查出所有品名的訂單；當我們輸入品名條件，比如p1，此時本報表.Sheet1:品名，“或者”的前半部分不滿足，對于品名為p1的訂單，“或者”的后半部分滿足，所以只能查詢出品名為p1的訂單。增加的第二個條件，關(guān)于客戶名的篩選，原理是一樣的，只是其中用到了“形如”比較運算符?！靶稳纭笔且粋€字符串比較運算符，我們說A形如B，意思是A包含了B的內(nèi)容，例如：“

32、中國北京”形如“北京”，這是成立的，因為“中國北京”中包含了“北京”二字，但是“中國北京”形如“京都”，就不成立了。利用“形如”比較，用戶可以不必記得客戶的完整名稱，只記得幾個字，比如，“客戶名”條件處輸入“山東”，則可以查詢處所有“客戶名稱”中包含“山東”字樣的客戶的訂單信息。3.2.6 鏈接到相關(guān)表單我們在“銷售臺帳”模板上，通過定義表間公式，可以查詢出符合條件的訂單列表，但是如果要了解每份訂單的完整信息，還需要到我的工作臺中具體找到這張訂單，打開它，才能看到。下面我們學習一種在查詢模板上，方便地查看具體訂單的方法：我們在銷售臺帳上的每個訂單號下面加一個超級鏈接，點擊這個鏈接，直接就可以打

33、開這份訂單。如圖：圖 323為了達到上述目的，我們需要對“銷售臺帳_明細”中的“訂單號”字段進行設(shè)置，指定在該字段上構(gòu)造一個超級鏈接，鏈接到對應的訂單。按以下步驟操作：1） 打開“銷售臺帳”模板，進入設(shè)計狀態(tài)2） 打開“管理數(shù)據(jù)表”窗口，選擇“銷售臺帳_明細”表，點擊【字段定義】3） 選中“訂單號”字段，在窗口下半部分選擇“鏈接”標簽，勾選“鏈接到以下報表”，選擇鏈接模板為“訂單”，鏈接字段為“訂單_主表.訂單編號”，如圖：圖 3244） 點擊【修改字段】，點擊【返回】5） 回到上一級窗口，再次點擊【返回】。6） 關(guān)閉模板。3.3 自定義查詢通過設(shè)計查詢模板，定義表間公式，可以實現(xiàn)各種復雜的數(shù)

34、據(jù)查詢需求。如果不要求打印查詢結(jié)果，還可以直接在我的工作臺中定義“查詢”，“查詢”實質(zhì)上就是表間公式，只不過它的結(jié)果不需要填充到模板上，而是可以在我的工作臺中直接顯示。下面定義一個查詢，直接顯示每個客戶的訂單信息。1） 用設(shè)計者身份登錄2） 選擇菜單【模板】à【新建查詢】，彈出“定義查詢”窗口，輸入查詢名稱，選擇所屬分類。圖 325 定義查詢3） 就像定義一條表間公式一樣，選擇數(shù)據(jù)源為“客戶表”、“訂單_主表”和“訂單_明細”，并指定表間關(guān)聯(lián)條件“客戶表.客戶編號=訂單_主表.客戶編號”4） 點擊【定義數(shù)據(jù)項】，輸入數(shù)據(jù)項，結(jié)果如下圖：圖 3265） 切換到“查閱權(quán)限”標簽，設(shè)置查閱

35、權(quán)限，如圖：圖 327 設(shè)置自定義查詢的查閱權(quán)限6） 點擊【保存】現(xiàn)在打開我的工作臺，我們看到左側(cè)菜單中指定的分類之下，出現(xiàn)了新建立的查詢名稱，點擊此名稱，右側(cè)顯示查詢信息，如圖：圖 328針對自定義查詢中顯示的信息，還可以進行【查找】、【導出到Excel文件】等操作。自定義查詢也是可以定義篩選條件的，但是和表間公式不同，自定義查詢的條件中沒有變量（即所謂的本報表字段），但是可以出現(xiàn)系統(tǒng)變量，如當前用戶姓名、當前日期等）練習修改本節(jié)建立的自定義查詢，加入篩選條件：訂單_主表.錄入人=系統(tǒng)變量.當前用戶姓名，然后分別用Admin、小宋、老李的帳戶登錄，觀察“客戶訂單信息”查詢中顯示的內(nèi)容有什么不

36、同，并解釋為什么不同。第4章、 統(tǒng)計報表本章針對幾種常見的統(tǒng)計報表的模式，學習表間公式在不同情況下的具體運用。4.1 普通匯總4.1.1 建立銷售日報模板1） 建立如下圖所示的銷售日報模板圖 41 銷售日報模板表樣2） 定義兩個數(shù)據(jù)表日報1_主表主鍵區(qū)域字段名數(shù)據(jù)類型必填填寫規(guī)范其它C2日期日期當前日期日報1_明細主鍵區(qū)域字段名數(shù)據(jù)類型必填填寫規(guī)范其它B4:B9產(chǎn)品名稱名稱或地址C4:C9當日數(shù)量小數(shù)D4:D9當日金額金額4.1.2 用表間公式匯總數(shù)據(jù)在銷售日報模板上定義一條表間公式1） 新建表間公式，公式名稱為“匯總”，應用方式選中“篩選條件改變后自動執(zhí)行”和“手動執(zhí)行”。2） 選擇來源數(shù)據(jù)

37、為“訂單_主表”和“訂單_明細”3） 篩選條件輸入“訂單_主表.訂單日期 = 本報表.Sheet1:日期”4） 輸入“填充方式”，注意輸入“當日數(shù)量”的填充表達式時，要展開選項列表中“訂單_明細.數(shù)量”左側(cè)的小加號，然后雙擊其下的“合計(訂單_明細.數(shù)量)”，如圖8-2所示圖 42 使用統(tǒng)計函數(shù)“合計”、“平均”、“最大值”、“最小值”等統(tǒng)稱為統(tǒng)計函數(shù)，可單擊“輸入表達式”窗口的分類列表中的“統(tǒng)計函數(shù)”，察看所有可用的統(tǒng)計函數(shù)。5） 最后完成的表間公式如下：圖 43 帶統(tǒng)計函數(shù)的表間公式6） 關(guān)閉模板。7） 現(xiàn)在填報一張銷售日報，輸入日期，我們會看到該日的各種產(chǎn)品的合計訂單數(shù)量各訂單金額自動統(tǒng)

38、計到日報上。4.2 本期累計上節(jié)的銷售日報過于簡單，假設(shè)我們希望在日報上不僅要體現(xiàn)當天的銷售情況，還要體現(xiàn)從月初到日報當天的累積銷售情況，就像下圖所示的這樣：圖 44 帶本期累計的銷售日報4.2.1 問題分析來分析一下這個問題的解決思路：當日數(shù)據(jù)的統(tǒng)計很容易，我們在8.1節(jié)已經(jīng)實現(xiàn)了，主要的問題的本月累計怎么做。注意一點，日報是要每天都要做的，也就是說，當我們作5日的日報時，4日的日報已存在了。這就給我們提供了解決問題的思路：今天的“本月累計”= 今天的當日數(shù)據(jù)+昨天日報上的“本月累計”數(shù)具體來說，在模板上增加幾個字段：在G列增加“昨天累計數(shù)量”，H列增加昨天累計金額，模板全部設(shè)計好之后，可以

39、把G列和H列隱藏。圖 45 在日報上增加幾個隱藏字段用表間公式去查詢“昨天日報的本月累計”用Excel公式計算今天的本月累計：E5=C5+G5，余類推。另外，因為包含了“本月累計”，所以不能像上節(jié)那樣，把產(chǎn)品名稱和當日數(shù)量、當日金額一次性提取出來，這樣會導致某產(chǎn)品由于當天沒有訂單就沒有被包含在當天的日報上，但該產(chǎn)品可能當月的前些日子是有訂單的，這樣的日報無法正確地反映每種產(chǎn)品的本月累計。分析就到這里，下面實際地把這張日報做出來。4.2.2 建立模板新日報1） 按圖10-5所示表樣建立模板新日報2） 定義單一數(shù)據(jù)項：新日報_主表主鍵區(qū)域字段名數(shù)據(jù)類型必填填寫規(guī)范其它C2日期日期當前日期3） 選中B5:H14，定義重復數(shù)據(jù)項。注意第一步的上方標題行數(shù)要輸入2。 圖 464） 最后定義好的數(shù)據(jù)表如下：圖8-74.2.3 第一條公式-提取所有產(chǎn)品在新日報模板上新建如下的表間公式圖8-8注意兩處：1）應用方式我們選擇了“打開后自動執(zhí)行（初始填報）”，效果就是，當我們填報的時候，空白日報一打開，這條表間公式馬上就執(zhí)行。2）在顯示方式處