巧用 Excel 函数制作动态教材征订模板
一、基本思路
（一）建立教材征订地址库，主要包括所属区县、目的地、收件人、收件电话、收件单位、收件地址、邮编。
（二）建立教材目录库，主要包括书代号、教材名称、单价、出版时间等信息。
（三）建立教材征订单，主要包括下单日期、区县、目的地、邮编、收件人、收件电话、收件单位、收件地址和教材征订清单。
二、功能设计
（一）输入信息。在教材征订单中仅需输入下单日期、书代号、订购数量，并在所属区县栏下拉列表中选择所在区县。
（二）自动生成。通过选择区县，自动生成相应的折扣；通过输入图书的代号，自动带出书名、单价信息；通过输入订购数量，自动计算码洋和实洋，同时，自动求和分别生成数量、码洋、实洋的合计。
（三）自动提醒。当征订的图书出现重复时，自动在书名一栏下填充红色进行提醒。
（四）防止修改。在教材征订地址库中，收件人、收件电话、收件单位、收件地址栏内的信息支持编辑修改，其他区域锁定加保护。在中央校教材目录库中，可以选择复制，但不能对单元格内容进行编辑修改，整个区域锁定加保护。在教材征订单中，仅支持编辑修改下单日期、书代号、数量信息，并能根据需要选择所属区县，其他区域锁定加保护。
三、Excel函数
订单模板共用到 VLOOKUP、IFERROR、IF、ROUND、SUM和COUNTIF六个函数，其具体意义和用法这里不再赘述。
四、制作过程
（一）创建教材征订地址库表格
1. 录入信息。在桌面单击鼠标右键，选择新建Excel97-2003工作表，然后重命名工作表名称为“天津市农业广播电视学校教材征订单”。双击该工作表，在Sheet3中建立教材征订地址库（如图1），按照所属区县、目的地、收件人、收件电话、收件单位、收件地址、邮编栏目，依次输入相关信息，并设定字体为“宋体”，大小为“10”号，对齐方式为“居中左对齐”。设置完毕后，单击“保存”按钮进行保存，在后面的操作过程中要随时进行保存。
图1 教材征订地址库2.区域锁定。按住键盘Ctrl，选中A1：G2和A3：B15区域，单击鼠标右键选择“设置单元格格式”，弹出“自定义序列”对话框，选择“保护”选项卡，并选中“锁定”（如图2），最后单击“确定”按钮。
图2“自定义序列”对话框“保护”选项卡3.保护工作表。选择“审阅”菜单中“保护工作表”命令，弹出“保护工作表”对话框（如图3），选中“保护工作表及锁定的单元格内容”，并在“取消工作表保护时使用的密码”中输入密码进行保护，同时在“允许此工作表的所有用户进行”下选中“选定锁定单元格”、“选定未锁定的单元格”、“编辑对象”、“编辑方案”，然后单击“确定”按钮，在“确认密码”对话框中再次重新输入密码，最后点击“确定”按钮。
图3“保护工作表”对话框此时，已创建完成教材征订地址库表格，所属区县和目的地栏内信息被锁定并加以保护，而收件人、收件电话、收件单位、收件地址、邮编栏目信息可根据实际情况进行编辑修改。
（二）创建教材目录库表格
1.录入信息。在Sheet2中建立中央校教材目录库（如图4），按照序号、书代号、教材名称、单价、出版时间、教材来源栏目，依次输入教材目录相关信息，并设定字体为“宋体”，大小为“10”号，对齐方式为“居中左对齐”。
图4 中央校教材目录库2.锁定并保护。由于教材目录库信息不允许编辑修改，所以要将Sheet2中所有单元格加以锁定并保护。首先，选择所有单元格，参照上述“区域锁定”操作步骤，对教材目录库信息进行锁定；然后，参照上述“保护工作表”操作步骤，对Sheet2中信息加以保护。
此时，已创建完成教材目录库表格，表内信息均被锁定并加以保护，用户只能选定单元格并进行复制，但不能对单元格内容进行编辑修改。
（三）创建教材征订单
1.设计教材征订单样式。一份完整的订单主要包括下单日期、所属区县、目的地、邮编、收件人、收件电话、收件单位、收件地址、序号、书代号、书名、数量、单价、码洋、折扣、实洋、教材来源、合计、说明等信息，结合实际，设计制作了“天津市农业广播电视学校教材征订单”样式（如图5）。标题“天津市农业广播电视学校教材征订单”居中，字体字号分别为宋体18号。为更加醒目，下单日期、所属区县、书代号、数量对应栏内的信息可编辑修改，因此将上述栏背景色填充为绿色，而动态自动生成的其他栏背景色填充为灰色；底部一栏合并单元格，填入教材征订单的操作说明，并设置字体颜色为红色。设置表中字体为“宋体”，大小为“9号”，数量、单价、码洋、折扣、实洋对应栏填入信息均为“居中右对齐”，且单价、码洋、实洋均为2位小数，折扣一栏信息按“百分比”样式显示，其他栏信息均为“居中左对齐”。选择“页面布局”菜单栏，单击显示“页面设置”对话框，在“页面”选项卡，设置方向为“纵向”，纸张大小为“A4”；单击“页边距”选项卡，设置上下页边距为1厘米，左右页边距为1.2厘米，“居中方式”为水平和垂直居中，最后单击“确定”按钮。选择“视图”菜单栏中“分页预览”，对表格栏目进行微调整，将订单信息设置显示在A4一页纸上；然后选中第7行，单击“冻结窗格”下“冻结拆分窗格”；接着在“序号”栏中从1开始填充序列，一直填充到40；最后在合计行，书代号、书名、单价、折扣、教材来源列对应的单元格内输入“—”。至此，教材订单样式设计完成。
图5 教材征订单样式2.建立动态关联。
（1）通过下拉菜单实现选择所属区县。选中C3单元格，选择“数据”菜单栏中“数据有效性”命令，出现“数据有效性”对话框，在“设置”选项卡上，有效性条件“允许”中选择“序列”（如图6），“来源”中输入“=Sheet3！A3：A16”，实现下拉菜单选择相应的所属区县。
图6“数据有效性”对话框“设置”选项卡（2）通过VLOOKUP函数自动带出地址库信息。在D3 单元格输入公式“=IFERROR（VLOOKUP（C3，Sheet3！$A：$G，2，0），""）”，实现自动带出“目的地”信息。同理，在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函数自动带出图书信息。在C7单元格输入公式“=IFERROR（VLOOKUP（$B7，Sheet2！$B：$F，2，0），""）”，实现通过输入书代号，自动带出书名信息；然后在本列其他单元格填充公式，选中C7单元格纵向拖动填充柄到终止单元格。同理，在E7单元格输入公式“=IFERROR（VLOOKUP（$B7，Sheet2！$B：$F，3，0），""）”，在I7单元格输入公式“=IFERROR（VLOOKUP（$B7，Sheet2！$B：$F，5，0），""）”；然后分别对应这两列的其他单元格填充公式。
3.自动显示相应折扣。教材征订过程中一般对不同用户会有相应的折扣，假设发往市校教材为7折，发往区县分校教材为8折。在G7单元格输入公式“=IF（I7="中央校教材处"，IF（$C$3="天津市校"，0.7，0.8），""）”，实现只有教材目录库中图书才能生成相应的折扣；然后对应本列其他单元格填充公式。
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单元格，选择“开始”菜单“条件格式”下“新建规则”命令，在“编辑格式规则”对话框“选择规则类型”中单击选择“使用公式确定要设置格式的单元格”（见图7），然后在“为符合此公式的值设置格式”中输入公式“=COUNTIF（B：B，B7）>1”，接着单击“格式”按钮，在“设置单元格格式”对话框“填充”选项卡“背景色”中选择红色，单击“确定”按钮，关闭所有对话框。设置完毕后，再使用格式刷将这一单元格的条件格式复制到“书名”列的其他单元格。
图7 编辑格式规则对话框6.锁定并保护。选中下单日期、所属区县、书代号、数量对应需要输入信息的区域，按快捷键“CTRL+1”，打开“自定义序列”对话框“保护”选项卡，“锁定”和“隐藏”复选框设置为未选中，然后单击“确定”按钮；选中订单中的其他区域，按快捷键“CTRL+1”，打开“自定义序列”对话框，设置“保护”选项卡中“锁定”和“隐藏”复选框为全选中。然后参照上述“保护工作表”中操作步骤，对sheet1表格进行保护。
五、使用与维护
（一）使用方法。本动态教材征订模板主要适用于区县分校，在教材征订单中输入下单日期、书代号、数量，并选择所属区县即可。教材征订信息比较全面，一目了然，更加直观，不易出错，提高了工作人员的效率。
（二）维护方法。市校教材工作人员要及时对教材目录库进行维护，确保将最新最全面的教材信息录入教材目录库。征订地址库信息既可以由省校工作人员维护，也可以由区县分校教材征订工作人员维护，相对灵活，需要注意的是一定要确保征订地址库信息准确无误。
（作者单位：天津市农业广播电视学校塘沽分校、天津市农业广播电视学校西青区分校、天津市农业广播电视学校）