楊云媛 吳均 張大鵬

培訓教材是傳遞信息知識的重要載體，對農(nóng)民教育培訓具有重要的意義。教材征訂工作是農(nóng)廣校體系上下的一項日常任務，傳統(tǒng)的征訂方式不僅相對繁瑣，而且還容易出現(xiàn)重復征訂、計算不精準等問題。為提高工作效率，可巧用Excel函數(shù)制作動態(tài)教材征訂模板。

一、基本思路

（一）建立教材征訂地址庫，主要包括所屬區(qū)縣、目的地、收件人、收件電話、收件單位、收件地址、郵編。

（二）建立教材目錄庫，主要包括書代號、教材名稱、單價、出版時間等信息。

（三）建立教材征訂單，主要包括下單日期、區(qū)縣、目的地、郵編、收件人、收件電話、收件單位、收件地址和教材征訂清單。

二、功能設計

（一）輸入信息。在教材征訂單中僅需輸入下單日期、書代號、訂購數(shù)量，并在所屬區(qū)縣欄下拉列表中選擇所在區(qū)縣。

（二）自動生成。通過選擇區(qū)縣，自動生成相應的折扣；通過輸入圖書的代號，自動帶出書名、單價信息；通過輸入訂購數(shù)量，自動計算碼洋和實洋，同時，自動求和分別生成數(shù)量、碼洋、實洋的合計。

（三）自動提醒。當征訂的圖書出現(xiàn)重復時，自動在書名一欄下填充紅色進行提醒。

（四）防止修改。在教材征訂地址庫中，收件人、收件電話、收件單位、收件地址欄內(nèi)的信息支持編輯修改，其他區(qū)域鎖定加保護。在中央校教材目錄庫中，可以選擇復制，但不能對單元格內(nèi)容進行編輯修改，整個區(qū)域鎖定加保護。在教材征訂單中，僅支持編輯修改下單日期、書代號、數(shù)量信息，并能根據(jù)需要選擇所屬區(qū)縣，其他區(qū)域鎖定加保護。

三、Excel函數(shù)

訂單模板共用到VLOOKUP、IFERROR、IF、ROUND、SUM和COUNTIF六個函數(shù)，其具體意義和用法這里不再贅述。

四、制作過程

（一）創(chuàng)建教材征訂地址庫表格

1. 錄入信息。在桌面單擊鼠標右鍵，選擇新建Excel97-2003工作表，然后重命名工作表名稱為“天津市農(nóng)業(yè)廣播電視學校教材征訂單”。雙擊該工作表，在Sheet3中建立教材征訂地址庫（如圖1），按照所屬區(qū)縣、目的地、收件人、收件電話、收件單位、收件地址、郵編欄目，依次輸入相關信息，并設定字體為“宋體”，大小為“10”號，對齊方式為“居中左對齊”。設置完畢后，單擊“保存”按鈕進行保存，在后面的操作過程中要隨時進行保存。

2. 區(qū)域鎖定。按住鍵盤Ctrl，選中A1：G2和A3：B15區(qū)域，單擊鼠標右鍵選擇“設置單元格格式”，彈出“自定義序列”對話框，選擇“保護”選項卡，并選中“鎖定”（如圖2），最后單擊“確定”按鈕。

3. 保護工作表。選擇“審閱”菜單中“保護工作表”命令，彈出“保護工作表”對話框（如圖3），選中“保護工作表及鎖定的單元格內(nèi)容”，并在“取消工作表保護時使用的密碼”中輸入密碼進行保護，同時在“允許此工作表的所有用戶進行”下選中“選定鎖定單元格”、“選定未鎖定的單元格”、“編輯對象”、“編輯方案”，然后單擊“確定”按鈕，在“確認密碼”對話框中再次重新輸入密碼，最后點擊“確定”按鈕。

此時，已創(chuàng)建完成教材征訂地址庫表格，所屬區(qū)縣和目的地欄內(nèi)信息被鎖定并加以保護，而收件人、收件電話、收件單位、收件地址、郵編欄目信息可根據(jù)實際情況進行編輯修改。

（二）創(chuàng)建教材目錄庫表格

1. 錄入信息。在Sheet2中建立中央校教材目錄庫（如圖4），按照序號、書代號、教材名稱、單價、出版時間、教材來源欄目，依次輸入教材目錄相關信息，并設定字體為“宋體”，大小為“10”號，對齊方式為“居中左對齊”。

2. 鎖定并保護。由于教材目錄庫信息不允許編輯修改，所以要將Sheet2中所有單元格加以鎖定并保護。首先，選擇所有單元格，參照上述“區(qū)域鎖定”操作步驟，對教材目錄庫信息進行鎖定；然后，參照上述“保護工作表”操作步驟，對Sheet2中信息加以保護。

此時，已創(chuàng)建完成教材目錄庫表格，表內(nèi)信息均被鎖定并加以保護，用戶只能選定單元格并進行復制，但不能對單元格內(nèi)容進行編輯修改。

（三）創(chuàng)建教材征訂單

1. 設計教材征訂單樣式。一份完整的訂單主要包括下單日期、所屬區(qū)縣、目的地、郵編、收件人、收件電話、收件單位、收件地址、序號、書代號、書名、數(shù)量、單價、碼洋、折扣、實洋、教材來源、合計、說明等信息，結(jié)合實際，設計制作了“天津市農(nóng)業(yè)廣播電視學校教材征訂單”樣式（如圖5）。標題“天津市農(nóng)業(yè)廣播電視學校教材征訂單”居中，字體字號分別為宋體18號。為更加醒目，下單日期、所屬區(qū)縣、書代號、數(shù)量對應欄內(nèi)的信息可編輯修改，因此將上述欄背景色填充為綠色，而動態(tài)自動生成的其他欄背景色填充為灰色；底部一欄合并單元格，填入教材征訂單的操作說明，并設置字體顏色為紅色。設置表中字體為“宋體”，大小為“9號”，數(shù)量、單價、碼洋、折扣、實洋對應欄填入信息均為“居中右對齊”，且單價、碼洋、實洋均為2位小數(shù)，折扣一欄信息按“百分比”樣式顯示，其他欄信息均為“居中左對齊”。選擇“頁面布局”菜單欄，單擊顯示“頁面設置”對話框，在“頁面”選項卡，設置方向為“縱向”，紙張大小為“A4”；單擊“頁邊距”選項卡，設置上下頁邊距為1厘米，左右頁邊距為1.2厘米，“居中方式”為水平和垂直居中，最后單擊“確定”按鈕。選擇“視圖”菜單欄中“分頁預覽”，對表格欄目進行微調(diào)整，將訂單信息設置顯示在A4一頁紙上；然后選中第7行，單擊“凍結(jié)窗格”下“凍結(jié)拆分窗格”；接著在“序號”欄中從1開始填充序列，一直填充到40；最后在合計行，書代號、書名、單價、折扣、教材來源列對應的單元格內(nèi)輸入“—”。至此，教材訂單樣式設計完成。

2. 建立動態(tài)關聯(lián)。

（1）通過下拉菜單實現(xiàn)選擇所屬區(qū)縣。選中C3單元格，選擇“數(shù)據(jù)”菜單欄中“數(shù)據(jù)有效性”命令，出現(xiàn)“數(shù)據(jù)有效性”對話框，在“設置”選項卡上，有效性條件“允許”中選擇“序列”（如圖6），“來源”中輸入“=Sheet3！A3：A16”，實現(xiàn)下拉菜單選擇相應的所屬區(qū)縣。

（2）通過VLOOKUP函數(shù)自動帶出地址庫信息。在D3單元格輸入公式“=IFERROR（VLOOKUP（C3，Sheet3！$A：$G，2，0），""）”，實現(xiàn)自動帶出“目的地”信息。同理，在F3單元格輸入公式“=IFERROR（VLOOKUP（C3，Sheet3！$A：$G，7，0），""）”；在A5單元格輸入公式“=IFERROR（VLOOKUP（C3，Sheet3！$A：$G，3，0），""）”；在C5單元格輸入公式“=IFERROR（VLOOKUP（C3，Sheet3！$A：$G，4，0），""）”；在D5單元格輸入公式“=IFERROR（VLOOKUP（C3，Sheet3！$A：$G，5，0），""）”；在G5單元格輸入公式“=IFERROR（VLOOKUP（C3，Sheet3！$A：$G，6，0），""）”。

（3）通過VLOOKUP函數(shù)自動帶出圖書信息。在C7單元格輸入公式“=IFERROR（VLOOKUP（$B7，Sheet2！$B：$F，2，0），""）”，實現(xiàn)通過輸入書代號，自動帶出書名信息；然后在本列其他單元格填充公式，選中C7單元格縱向拖動填充柄到終止單元格。同理，在E7單元格輸入公式“=IFERROR（VLOOKUP（$B7，Sheet2！$B：$F，3，0），""）”，在I7單元格輸入公式“=IFERROR（VLOOKUP（$B7，Sheet2！$B：$F，5，0），""）”；然后分別對應這兩列的其他單元格填充公式。

3. 自動顯示相應折扣。教材征訂過程中一般對不同用戶會有相應的折扣，假設發(fā)往市校教材為7折，發(fā)往區(qū)縣分校教材為8折。在G7單元格輸入公式“= IF（I7="中央校教材處"，IF（$C$3="天津市校"，0.7，0.8），""）”，實現(xiàn)只有教材目錄庫中圖書才能生成相應的折扣；然后對應本列其他單元格填充公式。

4. 自動計算。通過自動計算得出碼洋、實洋、合計相關信息。在F7單元格輸入公式“=IFERROR（D7*E7，""）”，在H7單元格輸入公式“=IFERROR（ROUND（E7*D7*G7，2），""）”；然后分別對應這兩列其他單元格填充公式。在D47中輸入公式“=IF（SUM（D7：D46）=0，""，SUM（D7：D46））”；在F47中輸入公式“=IF（SUM（F7：F46）=0，""，SUM（F7：F46））”；在H47中輸入公式“=IF（SUM（H7：H46）=0，""，SUM（H7：H46））”。

5. 自動提醒重復錄入。在輸入“書代號”過程中，難免會有重復錄入的情形，此時就需要自動提醒，引起用戶注意。因此，設計當輸入重復的書代號時，書名重復一欄背景色自動填充紅色。選中C7單元格，選擇“開始”菜單“條件格式”下“新建規(guī)則”命令，在“編輯格式規(guī)則”對話框“選擇規(guī)則類型”中單擊選擇“使用公式確定要設置格式的單元格”（見圖7），然后在“為符合此公式的值設置格式”中輸入公式“=COUNTIF（B：B，B7）>1”，接著單擊“格式”按鈕，在“設置單元格格式”對話框“填充”選項卡“背景色”中選擇紅色，單擊“確定”按鈕，關閉所有對話框。設置完畢后，再使用格式刷將這一單元格的條件格式復制到“書名”列的其他單元格。

6. 鎖定并保護。選中下單日期、所屬區(qū)縣、書代號、數(shù)量對應需要輸入信息的區(qū)域，按快捷鍵“CTRL+1”，打開“自定義序列”對話框“保護”選項卡，“鎖定”和“隱藏”復選框設置為未選中，然后單擊“確定”按鈕；選中訂單中的其他區(qū)域，按快捷鍵“CTRL+1”，打開“自定義序列”對話框，設置“保護”選項卡中“鎖定”和“隱藏”復選框為全選中。然后參照上述“保護工作表”中操作步驟，對sheet1表格進行保護。

五、使用與維護

（一）使用方法。本動態(tài)教材征訂模板主要適用于區(qū)縣分校，在教材征訂單中輸入下單日期、書代號、數(shù)量，并選擇所屬區(qū)縣即可。教材征訂信息比較全面，一目了然，更加直觀，不易出錯，提高了工作人員的效率。

（二）維護方法。市校教材工作人員要及時對教材目錄庫進行維護，確保將最新最全面的教材信息錄入教材目錄庫。征訂地址庫信息既可以由省校工作人員維護，也可以由區(qū)縣分校教材征訂工作人員維護，相對靈活，需要注意的是一定要確保征訂地址庫信息準確無誤。

（作者單位：天津市農(nóng)業(yè)廣播電視學校塘沽分校、天津市農(nóng)業(yè)廣播電視學校西青區(qū)分校、天津市農(nóng)業(yè)廣播電視學校）