Excel相关数据校验方法

使用Excel来收集数据是经常使用的功能。公司开发了一个固定资产系统，IT部门需要给用户初始化一些数据。虽然Excel表格的说明已经写得很清楚并且对用户进行了培训。但是用户在提交这些数据时还是出现了一堆的问题。细看这些问题除了个别用户对数据列进行增删之外，很多都是一些很细节的东西，比如日期格式错误，百分比的位置应该是填小数却填整数。

分析了一下除了实施过程中用户的不可控因素之外，其实还是我们的实施人员在定义Excel时不太了解Excel的一些特性，其实Excel也提供了一些有效的数据校验的方法。下面就来分享一下。

1. 数据有效性验证

     数据有效性是最常用的数据验证手段，如列表、日期、数字、字符串长度等。使用办法是点击在数据区域的数据有效性，在弹出的对话框中选择允许的数据类型，用户输入时的提示信息，错误提示。

这里特别需要提一下的是使用引用数据的时候如果直接选择目标表格区域，这样在分享给别的同事的时候会出现路径问题。因此需要先在目标区域里定义名称。然后在引用时直接输入名称。

虽然数据有交效性验证对于复制粘贴的操作不能及时反馈，但是可以通过后续的校验操作来提示出错的区域（图中圆圈部分表示未通过验证的部分，包括不能为空的部分）。

2. 条件格式

条件格式是一种实时的反馈手段，使用办法是在主页->样式->条件格式中选择新建样式

然后在弹出的格式中配置相应的规则及样式效果。条件格式对于复制粘贴有效。

3.电子表格保护

更严格一点的就是电子表格保护了，首先在审查选项可以看到电子表格相关的选项

点击允许用户编辑的区域，创建一个区域及相应的保护密码（如需要）

创建完区域之后，点击电子表格保护按钮，选择允许的操作及相应保护的密码（如需要）

创建完之后，保存该文档，用户如果不知道密码的情况是不能操作权限以外的区域的。

4. VBA保护

对于更高要求的操作来说应该就只剩下VBA了，使用ALT+F11可以打开VBA编辑器。如下图所示解决数据校验在复制粘贴中失效的问题。当然有点Bug，而且调试起来也麻烦，适合于调于高手用户。

 

总结

以上介绍的是Excel在数据校验及保护中的一些技巧。但由于Excel中在用户电脑中运行的再高明的技巧都不可能解决2B客户，因此如果技术不行就靠人上。