对Excel计算表中常见人为差错控制方法的研究

饶志欢

摘要：基于简单易用、所见即所得和丰富函数库等特性，Excel作为日常生活工作中最常用的办公软件之一，常用于数据统计和简单的计算工程，甚至作为一些小规模业务或者中小型企业的数据库使用。然而，界面非十分友好，若涉及大量数据存储输入、单元格移动、公式复制及粘贴等操作，常容易出现各类人为差错，使得计算结果有了极大的误差。本文拟分析常见差错产生的原因，采用Excel自带的各类功能给出尝试解决方案，设计差错控制和行为引导，尽可能减少人为误差的可能。

关键词：Excel表格设计  差錯控制  行为引导

一、总述

Excel作为最常用的工作软件之一，在石泉水电厂实际工程中也有了较广泛的应用，如水调年度数据统计、日常监测数值统计与计算、电量日报表统计等，在日常工作中发现，囿于个人Excel使用技能的高低，无论是计算表编制和使用人员，都存在各种人为差错，主要表现在：计算表格可视化设计不到位，导致数据查找输入麻烦且易错;计算公式被误改、复制错误、单元格异动导致配套计算公式变化等，使得计算结果出错;人工数据输入错误，但没有设置数据校验、误差分析等功能，无法及时校正数据等。

针对以上问题，本文基于Excel 2007及以上版本中，加强Excel计算表的编制方法和使用人行为引导，全部使用软件自带功能，逐一研究常见问题的解决方案。

二、Excel计算表中人为误差解决实例

1.Excel计算表格可视化

一个合理的软件或计算表格设计，应有提示用户使用的方法和基本的防误操作的设计，基于此原理，做如下安排：

（1）可将Excel按照其工作性质，划分为3个功能区域，即：数据输入区、中间计算过程区、计算结果区域。三个区域按照或横或纵的形式整齐排列，使用“设置单元格格式”中“边框”功能，将三个区域与其他空白区域区分开，便于视觉集中于功能区域。

第一行或第一列的表头应详细写明该列数据含义，并“填充”不同区域不同颜色以示区分。一般情况下，可将数据输入区浅色填充;将结算结果区域浅灰色系，中间计算过程区域应尽量详细，该区域可不做填充。数据较多的，可在另建一个工作表。

最后，使用“隐藏”功能，将不重要部分隐藏，显示效果更加清爽干净。

（2）若数据量大，可使用“冻结首行”或者“冻结首列”，避免数据输入行列错误;如果既需要查看列名，也需要查看行首的，亦可以选中冻结区域下方的第一行第一列，选用“冻结窗格”。

同时，对于多行或多列数据的串列、错行情况，可考虑按照如每五行或每个数据模块的等间距设置不同的填充颜色、或者加粗边框线条的方式，用于输入人员的视觉参考。

2.Excel公式误动导致计算错误

（1）Excel引用单元格计算有两种方式，即“相对引用”和“绝对引用”，选择合适的单元格引用方式，尤其是固定参数的引用采用“绝对引用”或者“混合引用”的方式，将尽可能的减少公式填充和误动单元格导致的公式计算错误。

（2）同时，选择“保护工作簿”，锁定表格并设置密码，有效保证Excel不被篡改，具体设置方式如下：首先，在“审阅”中设置数据输入区为“允许编辑区域”;使用“审阅”中“保护工作表”功能，仅允许“选定锁定单元格”和“选定未锁定的单元格”，并设置“取消工作表保护时使用的密码”，即可完成工作表的锁定功能。

3.Excel误差控制、快速定位和显示设置

（1）为避免输入错误，应设置有效性检验或者简单的误差分析，并且以明确的提示出来，便于人员及时修改，以石泉水电厂沉陷校核计算表为例，具体方式如下：

根据观规程知，沉陷计算主要需要控制两个参数，一个是符号验证，即表现“-1”方可，若为“1”就是往返测数据记录错误;另一个是往返测不符值不大于“0.0002m”，否则就是误差超限需重测。

首先，对需要符号验证区域，该值仅有两种选项，若为-1表示正常，若为1则表示往返测错误。选择“条件格式”功能，选择“突出显示单元格规则”中“等于”选项，并在结果中，填写“1”，选择设置为“浅红色背景”，即：若该单元格为“1”则该单元格自动变化为浅红色填充，从而实现了发现往返测异常数据的目的。

按照此类方法，我们同样可以选择“条件格式”功能中“大于”“小于”等选项，对往返测不符值大于0.0002的测值进行区别的告警。

（2）还应对人员输入的数据进行校验，如设置数字输入在0.5至2之间，使用“数据”选项卡中的“数据验证”功能，“设置”允许“小数”的“介于”最小值“0.5”和最大值“2”之间，并在“出错警告”中，设置“样式”为“警告”，标题为“测值超限！”，并提示“输入超限”的警告，提醒录入人员及时修改和检查测值情况。

三、结语

通过此类方法和思路设计的“石泉大坝日计划电量计算表”“石泉大坝泄洪计算表”等，自2016年编制使用以来，至今仍在用于日常工作中;编制的“石泉大坝沉陷校验计算表”“引张线计算表”等表格，将人为录入差错率从98%提升至99.9%以上，均减轻了人员劳动强度，提升工作效率

日常的工作较为繁杂，正确合理的使用Excel设计计算表格，并使用自带功能实现使用人差错控制和录入行为引导，在大幅度提升工作效率、减轻工作成本的同时，还能一定程度上实现误差的控制，使得工作更加的科学合理。

参考文献：

[1]季炼.Excel在企业不同场景中的应用.中国市场[J].2021.第27期

[2]Excel精英部落.Excel函数与公式速查宝典教程[M].中国水利水电出版社.2019年

[3]会计实操辅导教材研究院.excel数据处理与分析[M].广东人民出版社.2019年

[4]蒋青青. Excel宏在学校财务工作中的应用解析[J].行政事业资产与财务.2021.第20期