1.2　数据分列

所谓数据分列，就是不同类型的数据保存在同一个单元格，这样的表格我们是无法对不同类型数据进行分类汇总和分析的，此时需要对单元格的数据进行分列，也就是把一个单元格的不同类型数据分成几列保存。下面我们结合实际工作中经常遇到的几个问题，介绍数据分列的实用技能和技巧。

1.2.1　利用分隔符对数据分列

在很多情况下，数据之间是有分隔符来分隔的，这些分隔符可以是制表符，逗号，分号，空格，以及其他的符号，甚至可以是特殊的文字。此时，我们可以使用“分列”工具快速对某列数据进行分列。

案例1-4

图1-23是从系统里导入的科目名称列表，现在要把这个列表分成科目编码和各级的明细项目名称，结果如图1-24所示。具体方法和步骤如下。

01　选择A列。

02　执行“数据”选项卡中的“分列”命令按钮，打开“文本分列向导-第1步”对话框，选择“分隔符号”选项按钮，如图1-25所示。这里之所以选择“分隔符号”选项按钮，是因为原始数据中，每列数据之间是使用符号“\”隔开的。

03　单击“下一步”，打开“文本分列向导-第2步”对话框，选择“其他”复选框，并在右侧的小文本框里输入符号“\”，可以看到数据已经被分成几列了，如图1-26所示。

04　单击“下一步”按钮，打开“文本分列向导-第3步”对话框，在底部的“数据预览”表格中选择第一列数据，然后在列数据格式中选择“文本”选项按钮，如图1-27所示。之所以进行这个选择和设置，是因为第一列数据是科目编码，是文本型数据，在分列的同时就需要把数字转换成文本。

05　单击“完成”按钮，关闭对话框。

06　为数据区域添加标题。

案例1-5

图1-28是从系统里导出的管理费用预算执行情况汇总表，现在要把这个表格转换成如图1-29所示的表格，这样就可很清楚地查看各个费用项目的构成了。

仔细观察A列的数据特征，费用项目前面是向右顶格保存的，而部门名称的右边则有2个空格，那么这个空格就可以作为分隔符进行分列了，也就是在“文本分列向导-第2步”对话框中，选择“空格”复选框。需要注意的是，要事先在B列前面插入一个空列，以免分列后把原有的B列“预算数”覆盖掉。

1.2.2　利用固定宽度对数据分列

有时候，各个类别的数据之间没有明显的分隔符号，但宽度是一样的，此时可以在“分列”对话框中使用固定宽度来分列。

案例1-6

图1-30是一个示例数据，A列是产品编码名称，其中左边的7位是产品编码，右侧框中文字品名称。现在要求把A列的编码和名称分成两列。结果如图1-31所示。

主要步骤如下。

01　在A列后插入一列。

02　选择A列，执行“分列”命令，在“文本分列向导-第1步”对话框中选择“固定宽度”选项按钮，单击“下一步”，进入步骤之2。

03　确定好分列位置，然后单击鼠标，建立分列线，如图1-32所示。如果分界线位置不对，可以按住分列线拖动到指定位置。

04　单击“下一步”，根据需要，设置列数据格式。

05　单击“完成”。

06　因为固定宽度分列会破坏原标题，所以最后需重新修改标题。

1.2.3　利用文本函数对数据分列

案例1-6是利用固定宽度进行数据分列。其实，对于这样的问题，我们还可以使用文本函数（LEFT、RIGHT、MID）来分列，此时可以使用下面的公式：

产品编码：=LEFT（A2，7）

产品名称：=MID（A2，8，100）

案例1-7

下面是如何从员工的身份证号码中提取员工基本信息，比如生日、性别，这也是一种数据分列，如图1-33所示，此时公式如下：

出生日期：=1*TEXT（MID（B2，7，8），"0000-00-00"）

性别：=IF（ISEVEN（MID（B2，17，1）），"女"，"男"）

1.2.4　利用全角字符和半角字符特征对数据分列

全角字符和半角字符的本质区别，就是所占字节的不同：一个全角字符占两个字节，一个半角字符占一个字节，这样，如果遇到了全角字符和半角字符混在一起，但它们各占半边的情况，就可以使用文本函数进行分列了。

案例1-8

如图1-34所示的A列左边产品名称是汉字，右边是规格为数字和乘号（*），现在要把产品名称和规格分开，如何创建公式呢？注意产品名称和规格的长度不是固定的。

这个问题实质上是要分列全角字符和半角字符，汉字是全角字符，每个汉字占2个字节；数字和乘号是半角字符，每个占1个字节，这样汉字相对数字来说，每个汉字就多了一个字节，如果能计算出字符串的字节数和字符数，两者相减的差值，正好就是汉字的个数。

如图1-35所示，单元格公式如下。

单元格B2：=LEFT（A2，LENB（A2）-LEN（A2））

单元格C2：=RIGHT（A2，LEN（A2）-LEN（B2））

1.2.5　利用文本和数字特征对数据分列

在实际工作中，也会遇到更复杂的情况，就是文本和数字混杂的字符串，这里的文本不见得是函数，也可以是字母、符号等，此时的分列就变得更为复杂了。

案例1-9

图1-36就是一个例子，要从批次中把产品的数量和单位规格分开，但是单位规格不仅仅是汉字，还有数字、括号和字母。

显然，这样的问题不是一个简单的公式所能解决的，需要使用数组公式。

要将批次中的数量和单位分开，需要了解批次数据中从左边开始哪些字符是数字，到哪个字符就由数字变为了文本（汉字或字母），这样才能利用LEFT函数把数量取出来，利用MID函数把批次取出来。

这个问题的基本思路是这样的：先用MID函数把批次的每个字符取出来，生成一个数组，将这个数组的每个元素都乘以1，如果结果是错误值，表明是文字，如果结果是数字，表明是数字，这样可以用MATCH函数判断出现错误的开始位置，该位置左边的都是数量，右边的就都是单位规格了。

单元格D2的公式比较复杂，它是一个数组公式，需要按“Ctrl+Shift+Enter”组合键：

=1*LEFT（C2，MATCH（TRUE，ISERROR（1*MID（C2，ROW（INDIRECT（"1："&LEN（C2））），1）），0）-1）

单元格E2的公式就简单了，如下：

=RIGHT（C2，LEN（C2）-LEN（D2））

最后的结果如图1-37所示。

1.2.6　利用关键词对数据分列

所谓关键词，就是在字符串中的某个位置，有一个明显的字符，这个字符的左边是一类数据，右边是另外一类数据，此时利用文本函数分列是非常方便的。

案例1-10

图1-38是一个原材料数据和成品数据，现在要求从F列的成品下料尺寸中，提取3列数据：规格、数量和单位，比如第2行，规格是0.7*180*860数量是1，单位是件。

仔细分析成品下料尺寸数据，规格的后面有一个符号“/”，而单位都是一个汉字，这样我们就可以先使用FIND函数找出符号“/”的位置，使用LEFT函数取出符号“/”左边的规格，使用MID函数取出中间的数量，再用RIGHT函数取出右边的单位。第2行单元格公式如下：

单元格G2：=LEFT（F2，FIND（"/"，F2）-1）

单元格H2：=1*MID（F2，LEN（G2）+2，LEN（F2）-LEN（G2）-2）

单元格I2：=RIGHT（F2，1）

最后的结果如图1-39所示。