自动化轻松提高Excel数据处理效率

小文龙

一键合并Excel工作簿

学校要求以班为单位，汇总学生的家庭成员信息（图1），由于表格中的信息牵扯到个人隐私，为避免泄密，以往都是学校印发相关表格，让家长手工填好，然后老师收齐，手工输入到Excel表格中。工作量大、累人不说，关键是容易出错。如果直接让家长填写Excel表格并核对，再通过QQ或邮箱发给老师，老师只需一键完成相关Excel工作簿的合并，即可轻松搞定学生家庭成员信息的汇总工作。

运行Excel（本文以2016版为例），首先需要修改宏安全等级。单击“文件→选项→信任中心”，在弹出的“Excel选项”对话框中单击“信任中心设置”按钮，打开“信任中心”并选中左侧的“宏设置”选项（图2），勾选右侧的“启用所有宏”单选框并单击“确定”按钮即可。

接下来从https：//dwz.cn/OD8NDF1k下载“模板.xlsm”并保存到D盘根目录，然后在Excel中打开，将图1所示的表头内容复制进来（图3）。按下Ctrl+A快捷键，选中所有单元格并右击之，在弹出的右键菜单中选择“设置单元格格式”，打开与之同名对话框的“数字”选项卡，选中“文本”，将所有单元格的数据类型设置为文本并单击“确定”。

老师将学生家长发来的学生信息表全部保存到D盘根目录下，然后单击图3中所示的“汇总”按钮，稍等片刻，就会完成该目录下所有学生家庭成员信息的合并操作（图4）。全选图4中所示的所有单元格，复制并粘贴到一个新的Excel文档中，并以“某年级某班学生信息表.XLSX”保存之，最后把这个Excel文档提交给学校主管部门就可以了。

自定义公式让计算更快

为了解学生的考试情况，以及对教师工作进行量化考核，每次月考结束后，笔者所在学校都会以“外比成绩”为标准，将老师的成绩与全县最好的进行排名。“外比成绩”的计算方法（图5），如果靠计算器来算，工作量无疑是相当大的。其实，我们完全可以将该繁琐的公式转换成Excel所能“听懂”的公式，就可以快速计算出每位老师的“外比成绩”了。

根据图5所示的“外比成绩”计算公式，可知表格中至少要有个人的及格率、平均分、优秀率，以及全县最好的及格率、平均分、优秀率等不同的数据。据此设计出表格（图6），然后从考试成绩明细中，将对应的数据复制过来备用。

对比图5所示的“外比成绩”计算公式，以及图6所示与之相关的数据，先在日4单元格中输入公式“=D4/G4*20*55%*25%+B4/E4*20*55%*25%+C4/F4*20*55%*15%”（不包括外侧弓}号），然后按下回车键，就可以计算出第一位老师的外比成绩了。最后将光标定位到H4單元格，往下拖动填充手柄至最后一位老师“外比成绩”所对应的日列单元格（如日22单元格），即可算出所有老师的外比成绩（图7）。

选中图7所示Excel表格的任一单元格，然后按下Ctrl+G，陕捷键，并根据提示单击“定位条件”按钮，打开“定位条件”对话框（图8），再选中该对话框中的“常量”单选框并单击“确定”。按下Ctrl+1快捷键，打开“设置单元格格式”对话框的“保护”选项卡，取消勾选“锁定”复选框并单击“确定”，最后在“审阅”选项卡中，单击“保护工作表”按钮，根据提示输入密码就可以了。

经过以上步骤的操作，以后将鼠标定位到日列的包含公式的某个单元格，如果无意中修改了公式，则会提示输入密码，公式的修改才会生效，这就避免了公式被误修改的可能性。而下次再计算“外比成绩”时，只需把与个人以及全县最好成绩有关的及格率、平均分、优秀率等数据复制到图7所示的B列至G列单元格中（无须输入密码），则会自动计算出每位老师新的外比成绩，真的是非常方便。