巧妙拆分单元格中的数据

张辛

【摘要】

我们工作中经常要进行文档登记、统计和处理，如果日常明细表设计的不合理，日后统计会非常麻烦，如何快速整理表格呢？很多Excel工具可以帮到我们，比如：分列、查找替换、函数公式等等。其实表格的处理不局限于Excel，有时候借助Word中表格与文本的互换功能，会有意外惊喜，即简单又便捷，一起来看看吧。

【正文】

一     应用场景

行政人事部的同事，工作中每天都需要进行文具领用的登记，有人一次领多样文具时，就顺手填写成一条记录，如下图所示：

填写明细时方便了，但月底统计信息时就很麻烦，如果数据表是一条数据对应一种文具就方便很多，利用数据透视表拖拽几下就可以了，那么如何整理成如下图所示的标准数据源呢？

二     解决方法

借助word中表格与文本的互换功能，转换的同时调整行列的布局，达到拆分整理数据的目的。

2.1      将表格导入word文档中

选中Excel中的表格，进行Ctrl+C复制。在word文档中，进行Ctrl+V的粘贴。

【表格工具】-【布局】-【转换为文本】-【其他字符】，在其中输入空格。单击确定后，可将此表格转换为文本形式保存在word中。

2.2      通过查找替换更改文本布局

我们现在需要把“领用物品”中的顿号替换成一个回车符+两个空格，方便将同一行的物品分配到不同行中。

选中文字，单击【开始】-【替换】，打开查找替换对话框，查找内容中填写“、”，单击【更多】，光标定位到【替换为】，在其中选择【特殊格式】中的【段落标记】，再输入两个空格。单击全部替换即可。

 

2.3      在word中将文本转换为表格

选中所有文字，单击【表格】-【文本转换成表格】

 

在对话框中将列数设置为3，【文本分隔位置】中选择【其他字符】，在其中输入空格键。单击确定后，文本将转换为3列21行的表格。

2.4      在Excel中将表格整理为标准格式

将word中的表格复制粘贴到Excel中，我们希望将所有空白单元格进行填充。

选择所有表格数据，单击【文件】-【查找与选择】-【定位条件】。

 

在其中选择【空值】，确定后就选中所有空白单元格，直接在单元格中录入公式“=A2”，如图所示，按Ctrl + Enter 同步录入公式至所有空白单元格。

 

 

2.5      领用物品中将物品与数量分开两列

目前【领用物品】栏中物品与数量是放在一起的，由于长度不一，之间也没有分隔符号，所以不适合利用分列来拆分此列，我们可以应用函数来实现。

【领用物品】：=LEFT(C2,LENB(C2)-LEN(C2))

【数量】：=RIGHT(C2,LEN(C2)-LEN(D2))

其中：

【领用物品】中LENB(C2)-LEN(C2) 计算的是汉字的个数

【数量】中 LEN(C2)-LEN(D2) 计算的是数字的个数

函数介绍：

Len（文本对象）计算文本对象的长度，汉字、数字、英文都是1位。

Lenb（文本对象）按字节计算文本对象的长度，汉字为2位，数字和英文为1位。

Left（文本对象，截取位数）从左向右截取文本对象

Right（文本对象，截取位数）从右向左截取文本对象

2.6      删除公式保留数值

由于表格中有很多公式，为了防止单元格位置变动而发生的数据显示错误问题，我们把整张表复制并选择性粘贴成数值形式。

2.7      后续简单调整数据格式

选择性粘贴为数值后，【数量】列中数字是以文本方式存储的，需要选中所有数字，在智能标签中选择“转换为数字”。

最后删除多余列，调整行高列宽，套用表格格式后，最终整理的效果如图所示，我们可以做任意数据分析，非常方便。