APP下载

巧用Excel函数制作动态教材征订模板

2018-11-12杨云媛吴均张大鹏

农民科技培训 2018年6期
关键词:征订单收件单元格

杨云媛 吴均 张大鹏

培训教材是传递信息知识的重要载体,对农民教育培训具有重要的意义。教材征订工作是农广校体系上下的一项日常任务,传统的征订方式不仅相对繁琐,而且还容易出现重复征订、计算不精准等问题。为提高工作效率,可巧用Excel函数制作动态教材征订模板。

一、基本思路

(一)建立教材征订地址库,主要包括所属区县、目的地、收件人、收件电话、收件单位、收件地址、邮编。

(二)建立教材目录库,主要包括书代号、教材名称、单价、出版时间等信息。

(三)建立教材征订单,主要包括下单日期、区县、目的地、邮编、收件人、收件电话、收件单位、收件地址和教材征订清单。

二、功能设计

(一)输入信息。在教材征订单中仅需输入下单日期、书代号、订购数量,并在所属区县栏下拉列表中选择所在区县。

(二)自动生成。通过选择区县,自动生成相应的折扣;通过输入图书的代号,自动带出书名、单价信息;通过输入订购数量,自动计算码洋和实洋,同时,自动求和分别生成数量、码洋、实洋的合计。

(三)自动提醒。当征订的图书出现重复时,自动在书名一栏下填充红色进行提醒。

(四)防止修改。在教材征订地址库中,收件人、收件电话、收件单位、收件地址栏内的信息支持编辑修改,其他区域锁定加保护。在中央校教材目录库中,可以选择复制,但不能对单元格内容进行编辑修改,整个区域锁定加保护。在教材征订单中,仅支持编辑修改下单日期、书代号、数量信息,并能根据需要选择所属区县,其他区域锁定加保护。

三、Excel函数

订单模板共用到VLOOKUP、IFERROR、IF、ROUND、SUM和COUNTIF六个函数,其具体意义和用法这里不再赘述。

四、制作过程

(一)创建教材征订地址库表格

1. 录入信息。在桌面单击鼠标右键,选择新建Excel97-2003工作表,然后重命名工作表名称为“天津市农业广播电视学校教材征订单”。双击该工作表,在Sheet3中建立教材征订地址库(如图1),按照所属区县、目的地、收件人、收件电话、收件单位、收件地址、邮编栏目,依次输入相关信息,并设定字体为“宋体”,大小为“10”号,对齐方式为“居中左对齐”。设置完毕后,单击“保存”按钮进行保存,在后面的操作过程中要随时进行保存。

2. 区域锁定。按住键盘Ctrl,选中A1:G2和A3:B15区域,单击鼠标右键选择“设置单元格格式”,弹出“自定义序列”对话框,选择“保护”选项卡,并选中“锁定”(如图2),最后单击“确定”按钮。

3. 保护工作表。选择“审阅”菜单中“保护工作表”命令,弹出“保护工作表”对话框(如图3),选中“保护工作表及锁定的单元格内容”,并在“取消工作表保护时使用的密码”中输入密码进行保护,同时在“允许此工作表的所有用户进行”下选中“选定锁定单元格”、“选定未锁定的单元格”、“编辑对象”、“编辑方案”,然后单击“确定”按钮,在“确认密码”对话框中再次重新输入密码,最后点击“确定”按钮。

此时,已创建完成教材征订地址库表格,所属区县和目的地栏内信息被锁定并加以保护,而收件人、收件电话、收件单位、收件地址、邮编栏目信息可根据实际情况进行编辑修改。

(二)创建教材目录库表格

1. 录入信息。在Sheet2中建立中央校教材目录库(如图4),按照序号、书代号、教材名称、单价、出版时间、教材来源栏目,依次输入教材目录相关信息,并设定字体为“宋体”,大小为“10”号,对齐方式为“居中左对齐”。

2. 锁定并保护。由于教材目录库信息不允许编辑修改,所以要将Sheet2中所有单元格加以锁定并保护。首先,选择所有单元格,参照上述“区域锁定”操作步骤,对教材目录库信息进行锁定;然后,参照上述“保护工作表”操作步骤,对Sheet2中信息加以保护。

此时,已创建完成教材目录库表格,表内信息均被锁定并加以保护,用户只能选定单元格并进行复制,但不能对单元格内容进行编辑修改。

(三)创建教材征订单

1. 设计教材征订单样式。一份完整的订单主要包括下单日期、所属区县、目的地、邮编、收件人、收件电话、收件单位、收件地址、序号、书代号、书名、数量、单价、码洋、折扣、实洋、教材来源、合计、说明等信息,结合实际,设计制作了“天津市农业广播电视学校教材征订单”样式(如图5)。标题“天津市农业广播电视学校教材征订单”居中,字体字号分别为宋体18号。为更加醒目,下单日期、所属区县、书代号、数量对应栏内的信息可编辑修改,因此将上述栏背景色填充为绿色,而动态自动生成的其他栏背景色填充为灰色;底部一栏合并单元格,填入教材征订单的操作说明,并设置字体颜色为红色。设置表中字体为“宋体”,大小为“9号”,数量、单价、码洋、折扣、实洋对应栏填入信息均为“居中右对齐”,且单价、码洋、实洋均为2位小数,折扣一栏信息按“百分比”样式显示,其他栏信息均为“居中左对齐”。选择“页面布局”菜单栏,单击显示“页面设置”对话框,在“页面”选项卡,设置方向为“纵向”,纸张大小为“A4”;单击“页边距”选项卡,设置上下页边距为1厘米,左右页边距为1.2厘米,“居中方式”为水平和垂直居中,最后单击“确定”按钮。选择“视图”菜单栏中“分页预览”,对表格栏目进行微调整,将订单信息设置显示在A4一页纸上;然后选中第7行,单击“冻结窗格”下“冻结拆分窗格”;接着在“序号”栏中从1开始填充序列,一直填充到40;最后在合计行,书代号、书名、单价、折扣、教材来源列对应的单元格内输入“—”。至此,教材訂单样式设计完成。

2. 建立动态关联。

(1)通过下拉菜单实现选择所属区县。选中C3单元格,选择“数据”菜单栏中“数据有效性”命令,出现“数据有效性”对话框,在“设置”选项卡上,有效性条件“允许”中选择“序列”(如图6),“来源”中输入“=Sheet3!A3:A16”,实现下拉菜单选择相应的所属区县。

(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”,接着单击“格式”按钮,在“设置单元格格式”对话框“填充”选项卡“背景色”中选择红色,单击“确定”按钮,关闭所有对话框。设置完毕后,再使用格式刷将这一单元格的条件格式复制到“书名”列的其他单元格。

6. 锁定并保护。选中下单日期、所属区县、书代号、数量对应需要输入信息的区域,按快捷键“CTRL+1”,打开“自定义序列”对话框“保護”选项卡,“锁定”和“隐藏”复选框设置为未选中,然后单击“确定”按钮;选中订单中的其他区域,按快捷键“CTRL+1”,打开“自定义序列”对话框,设置“保护”选项卡中“锁定”和“隐藏”复选框为全选中。然后参照上述“保护工作表”中操作步骤,对sheet1表格进行保护。

五、使用与维护

(一)使用方法。本动态教材征订模板主要适用于区县分校,在教材征订单中输入下单日期、书代号、数量,并选择所属区县即可。教材征订信息比较全面,一目了然,更加直观,不易出错,提高了工作人员的效率。

(二)维护方法。市校教材工作人员要及时对教材目录库进行维护,确保将最新最全面的教材信息录入教材目录库。征订地址库信息既可以由省校工作人员维护,也可以由区县分校教材征订工作人员维护,相对灵活,需要注意的是一定要确保征订地址库信息准确无误。

(作者单位:天津市农业广播电视学校塘沽分校、天津市农业广播电视学校西青区分校、天津市农业广播电视学校)

猜你喜欢

征订单收件单元格
“轰炸式”营销等
流水账分类统计巧实现
玩转方格
玩转方格
2019年《人民调解》征订单
2019年《人民调解》征订单
2019年《人民调解》征订单
2019年《人民调解》征订单
浅谈Excel中常见统计个数函数的用法
一款基于微信小程序的快递个人信息加密平台Leakill