巧用Excel函數(shù)制作動(dòng)態(tài)教材征訂模板

楊云媛 吳均 張大鵬

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

一、基本思路

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

（二）建立教材目錄庫(kù)，主要包括書代號(hào)、教材名稱、單價(jià)、出版時(shí)間等信息。

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

二、功能設(shè)計(jì)

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

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

（三）自動(dòng)提醒。當(dāng)征訂的圖書出現(xiàn)重復(fù)時(shí)，自動(dòng)在書名一欄下填充紅色進(jìn)行提醒。

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

三、Excel函數(shù)

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

四、制作過程

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

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

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

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

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

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

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

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

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

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

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

2. 建立動(dòng)態(tài)關(guān)聯(lián)。

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

（2）通過VLOOKUP函數(shù)自動(dòng)帶出地址庫(kù)信息。在D3單元格輸入公式“=IFERROR（VLOOKUP（C3，Sheet3！$A：$G，2，0），""）”，實(shí)現(xiàn)自動(dòng)帶出“目的地”信息。同理，在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ù)自動(dòng)帶出圖書信息。在C7單元格輸入公式“=IFERROR（VLOOKUP（$B7，Sheet2！$B：$F，2，0），""）”，實(shí)現(xiàn)通過輸入書代號(hào)，自動(dòng)帶出書名信息；然后在本列其他單元格填充公式，選中C7單元格縱向拖動(dòng)填充柄到終止單元格。同理，在E7單元格輸入公式“=IFERROR（VLOOKUP（$B7，Sheet2！$B：$F，3，0），""）”，在I7單元格輸入公式“=IFERROR（VLOOKUP（$B7，Sheet2！$B：$F，5，0），""）”；然后分別對(duì)應(yīng)這兩列的其他單元格填充公式。

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

4. 自動(dòng)計(jì)算。通過自動(dòng)計(jì)算得出碼洋、實(shí)洋、合計(jì)相關(guān)信息。在F7單元格輸入公式“=IFERROR（D7*E7，""）”，在H7單元格輸入公式“=IFERROR（ROUND（E7*D7*G7，2），""）”；然后分別對(duì)應(yīng)這兩列其他單元格填充公式。在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. 自動(dòng)提醒重復(fù)錄入。在輸入“書代號(hào)”過程中，難免會(huì)有重復(fù)錄入的情形，此時(shí)就需要自動(dòng)提醒，引起用戶注意。因此，設(shè)計(jì)當(dāng)輸入重復(fù)的書代號(hào)時(shí)，書名重復(fù)一欄背景色自動(dòng)填充紅色。選中C7單元格，選擇“開始”菜單“條件格式”下“新建規(guī)則”命令，在“編輯格式規(guī)則”對(duì)話框“選擇規(guī)則類型”中單擊選擇“使用公式確定要設(shè)置格式的單元格”（見圖7），然后在“為符合此公式的值設(shè)置格式”中輸入公式“=COUNTIF（B：B，B7）>1”，接著單擊“格式”按鈕，在“設(shè)置單元格格式”對(duì)話框“填充”選項(xiàng)卡“背景色”中選擇紅色，單擊“確定”按鈕，關(guān)閉所有對(duì)話框。設(shè)置完畢后，再使用格式刷將這一單元格的條件格式復(fù)制到“書名”列的其他單元格。

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

五、使用與維護(hù)

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

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

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