大家都知道了,源数据表必须规范、一致,否则后续的分析会产生各种错误,往往还很难一下子找到错误原因,因此有经验的表哥表妹都会在创建原始数据表的时候注意各种细节。 但是,最怕的是表格数据要让很多人填写后返回,经常收回来的数据已经没法看了。怎么办?如何从源头上强制表格的规范输入?比如,不得输入多余的空格。 案例: 下图 1 是销售人员每个月的获客数列表,作为一个规范的原始数据表,相同的姓名单元格必须完全一致,不可以有空格,否则会影响数据透视表的结果。 为了避免手误,请将 B 列设置为不允许输入空格。万一不小心输入了空格,会弹出一个出错警示框,并且无法完成输入。 效果如下图 2 所示。 解决方案: 先创建数据透视表。 1. 选中数据表的任意单元格 --> 选择菜单栏的“插入”-->“数据透视表” 2. 在弹出的对话框中选择“现有工作表”及所需上传至的位置 --> 点击“确定” 3. 在右侧的数据透视表字段区域,按以下方式拖动:
但是数据透视表里面有两个“王钢蛋”,咋回事?不出意外,应该是其中一个姓名前后多了空格。 仔细一查,还真是。 4. 删除 B5 单元格中的多余空格 --> 在数据透视表区域刷新一下,就只剩一个“王钢蛋”了。 上面的步骤我只是为了给大家看一下数据规范的重要性,如果不小心多输了空格,会产生严重的后果,因此我们从源头上就应该杜绝发生。 下面就教大家设置公式来判断空格是否存在。 5. 在 G2 单元格中输入以下公式: =LEN(B2) 公式释义:
6. 在 H2 单元格中输入以下公式: =LEN(SUBSTITUTE(B2,' ','')) 公式释义:
7. 在 I2 单元格中输入以下公式: =G2=H2
8. 将 G2:I2 区域下拉,复制公式 接下来我们再试试,如果在原数据表的任意单元格中添加空格,I 列的公式结果是否会变化。 从上述测试可以看出,B 列中凡是有空格的单元格,I 列的结果就为 false。 利用这个特性,我们就能把整段公式连起来,设置数据验证,从而限制输入空格。 9. 选中 B 列 --> 选择菜单栏的“数据”-->“数据验证”-->“数据验证” 10. 在弹出的对话框中选择“设置”选项卡 --> 按以下方式设置:
* 公式中的参数全部都要相对引用。 11. 选择“出错警告”选项卡,输入所需的标题和出错信息 --> 点击“确定”: 现在如果在 B 列的任意单元格中输入了空格,就会弹出一个出错警告框,要求重新输入直至正确为止。 转发、在看也是爱! |
|