Excel的分列方法两则

最近大家都在讨论分列的操作，示例数据如下（红色的部分），有一组数据省市全部显示在一个单元格之中，而这样不利于我们进行一些数据分析，需要把省和市分割到两列中。对于以上的分列方法有很多种，这里就说说两种比较简单的分列这种数据的方法。

方法一：使用直接分列法进行数据分割

在数据标签下，有分列选项如下图所示。

选中我们要分列的数据，然后点击分列，弹出如下的对话框：

选择分隔符号，点击下一步，如下所示：

此处选择分隔符号为其他，并输入分隔符合为省，如上图设置所示。之后点击下一步，如下图所示：

列数据格式选择文本，目标区域就是输出的区域，可以看到数据预览区域已经给出了分列后的形式，点击完成，出现以下的数据内容。

按照这样的操作可以继续分列下去，不过有一点不好的是分列的字符会在分列的过程中被省掉了，因此如果还需要“省”，还要使用连接符号，再把“省”找回，如下图所示：

方法二：公式法（left、mid、find）

首先我们增加一个辅助列，如下图蓝色区域所示：

首先先解释一下函数使用说明信息：

LEFT是根据所指定的字符数，LEFT 返回文本字符串中第一个字符或前几个字符。

LEFT(text,num_chars)

Text   是包含要提取的字符的文本字符串。

Num_chars   指定要由 LEFT 提取的字符的数量。

 

FIND返回一个字符串在另一个字符串中出现的起始位置（区分大小写）

FIND(find_text,within_text,start_num)

Find_text   要查找的文本。

Within_text   包含要查找文本的文本。

Start_num   指定要从其开始搜索的字符。within_text 中的首字符是编号为 1 的字符。如果省略 start_num，则假设其值为 1。

 

MID 返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。

MID(text,start_num,num_chars)

Text   是包含要提取字符的文本字符串。

Start_num   是文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为 1，以此类推。

Num_chars   指定希望 MID 从文本中返回字符的个数。

我们最终要达到的效果是在C列和D列将省市分离出来，通过以下的操作即可实现。

分列一我们要分出来省级名称，在C2的位置输入公式：

=LEFT(A2,FIND($B$2,A2))

分列二我们要分出来市级名称，在D2的位置输入公式：

=MID(A2,FIND($B$2,A2)+1,FIND($B$3,A2)-FIND($B$2,A2)+1)

公式计算

省名称计算公式说明

首先以C2单元格公式计算为例解释如何将省名称信息分列出来。这里通过公式计算来理解，如下图所示：

 

单击求值，首先求出来A2的信息，如下图所示：

 

点击求值，find函数将从A2中（注意不是left取出的信息）找到“省”的位置。

 

Find函数最终取出省的位置，返回一个数值，此数值是用于计算left应该取出的字符个数，进而帮助left计算截取字符。

 

最后求出结果，如下图所示。

市名称计算说明

原始公式如下所示，点击求值。

首先mid函数取出A2信息，便于最后进行计算。

 

之后第一个find开始计算截取字符的起始位置，此处需要注意的是在取出省的位置后要加1，否则最后会把 “省”也取出来。

 

继续求值，此时第二个find计算“市”出现的位置，如下图所示。

 

继续求值，第三个find开始计算，其实就是“省”的位置，第二个find计算结果减去第三个find计算结果就是市的名称信息。如下图所示。

 

即从以上字符串中的第4个位置开始取3个字符，此时正好输出市的信息。

以上为两种比较简单的针对不同长度且有关键字的数据进行分列的方法，当然也有其他的方法，比如通过处理文本在省和市之后加上分隔符，在进行分列，其实原理都是相同的。