大家都知道了,源数据表必须规范、一致,否则后续的分析会产生各种错误,往往还很难一下子找到错误原因,因此有经验的表哥表妹都会在创建原始数据表的时候注意各种细节。

但是,最怕的是表格数据要让很多人填写后返回,经常收回来的数据已经没法看了。怎么办?如何从源头上强制表格的规范输入?比如,不得输入多余的空格。

案例:

下图 1 是销售人员每个月的获客数列表,作为一个规范的原始数据表,相同的姓名单元格必须完全一致,不可以有空格,否则会影响数据透视表的结果。

为了避免手误,请将 B 列设置为不允许输入空格。万一不小心输入了空格,会弹出一个出错警示框,并且无法完成输入。

效果如下图 2 所示。

excel中怎么删除隔行的空格(收来的Excel表中多了个空格)(1)

excel中怎么删除隔行的空格(收来的Excel表中多了个空格)(2)

解决方案:

先创建数据透视表。

1. 选中数据表的任意单元格 --> 选择菜单栏的“插入”-->“数据透视表”

excel中怎么删除隔行的空格(收来的Excel表中多了个空格)(3)

2. 在弹出的对话框中选择“现有工作表”及所需上传至的位置 --> 点击“确定”

excel中怎么删除隔行的空格(收来的Excel表中多了个空格)(4)

3. 在右侧的数据透视表字段区域,按以下方式拖动:

excel中怎么删除隔行的空格(收来的Excel表中多了个空格)(5)

但是数据透视表里面有两个“王钢蛋”,咋回事?不出意外,应该是其中一个姓名前后多了空格。

excel中怎么删除隔行的空格(收来的Excel表中多了个空格)(6)

仔细一查,还真是。

excel中怎么删除隔行的空格(收来的Excel表中多了个空格)(7)

4. 删除 B5 单元格中的多余空格 --> 在数据透视表区域刷新一下,就只剩一个“王钢蛋”了。

excel中怎么删除隔行的空格(收来的Excel表中多了个空格)(8)

上面的步骤我只是为了给大家看一下数据规范的重要性,如果不小心多输了空格,会产生严重的后果,因此我们从源头上就应该杜绝发生。

下面就教大家设置公式来判断空格是否存在。

5. 在 G2 单元格中输入以下公式:

=len(B2)

公式释义:

excel中怎么删除隔行的空格(收来的Excel表中多了个空格)(9)

excel中怎么删除隔行的空格(收来的Excel表中多了个空格)(10)

6. 在 H2 单元格中输入以下公式:

=LEN(SUBSTITUTE(B2," ",""))

公式释义:

excel中怎么删除隔行的空格(收来的Excel表中多了个空格)(11)

excel中怎么删除隔行的空格(收来的Excel表中多了个空格)(12)

7. 在 I2 单元格中输入以下公式:

=G2=H2

excel中怎么删除隔行的空格(收来的Excel表中多了个空格)(13)

excel中怎么删除隔行的空格(收来的Excel表中多了个空格)(14)

8. 将 G2:I2 区域下拉,复制公式

excel中怎么删除隔行的空格(收来的Excel表中多了个空格)(15)

接下来我们再试试,如果在原数据表的任意单元格中添加空格,I 列的公式结果是否会变化。

excel中怎么删除隔行的空格(收来的Excel表中多了个空格)(16)

excel中怎么删除隔行的空格(收来的Excel表中多了个空格)(17)

excel中怎么删除隔行的空格(收来的Excel表中多了个空格)(18)

excel中怎么删除隔行的空格(收来的Excel表中多了个空格)(19)

从上述测试可以看出,B 列中凡是有空格的单元格,I 列的结果就为 false。

利用这个特性,我们就能把整段公式连起来,设置数据验证,从而限制输入空格。

9. 选中 B 列 --> 选择菜单栏的“数据”-->“数据验证”-->“数据验证”

excel中怎么删除隔行的空格(收来的Excel表中多了个空格)(20)

excel中怎么删除隔行的空格(收来的Excel表中多了个空格)(21)

10. 在弹出的对话框中选择“设置”选项卡 --> 按以下方式设置:

* 公式中的参数全部都要相对引用。

excel中怎么删除隔行的空格(收来的Excel表中多了个空格)(22)

11. 选择“出错警告”选项卡,输入所需的标题和出错信息 --> 点击“确定”:

excel中怎么删除隔行的空格(收来的Excel表中多了个空格)(23)

现在如果在 B 列的任意单元格中输入了空格,就会弹出一个出错警告框,要求重新输入直至正确为止。

excel中怎么删除隔行的空格(收来的Excel表中多了个空格)(24)

excel中怎么删除隔行的空格(收来的Excel表中多了个空格)(25)

很多同学会觉得 Excel 单个案例讲解有些碎片化,初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学,从最基础的概念开始,一步步由简入繁、从入门到精通,系统化地讲解 Excel 的各个知识点。

现在终于有了,以下专栏,从最基础的操作和概念讲起,用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本,你也能成为 Excel 高手。

,