Excel文本分列和快速填充方法，拆分数据成多列

在导入数据时，你可能会发现多个值被导入到一个列中。图8显示了关于这类导入问题的一个例子。

提示：图8中使用固定宽度字体（宋体）来显示数据。使用默认字体时，并不能清楚地看到数据在固定宽度的列中很好地对齐。

图8　导入的数据被放置在一列而非多列中

如果文本的长度都相同，你也可以编写一组公式，用于将信息提取到单独的列中。可使用LEFT，RIGHT和MID函数完成此任务。

Excel中提供了两种非公式化方法用于协助拆分数据以使其占用多列：文本分列和快速填充。

使用文本分列

文本分列命令可将字符串解析为各个组成部分。

首先，确保含有要拆分的数据的列在右侧具有足够多空列来容纳所提取的数据。然后选择要分析的数据，并选择“数据”“数据工具”“分列”。Excel将显示“文本分列向导”，其中包含一系列对话框，用于引导你完成将单列数据转换成多列的过程。图9显示了用于选择数据类型的第一个步骤：

图9 “文本分列向导”中的第一个对话框

·分隔符号：要拆分的数据由分隔符号（如逗号、空格、斜杠或其他字符）分隔。

·固定宽度：每个组成部分占用完全相同的字符数。

选择相应的选项，然后单击“下一步”按钮继续执行步骤2，该步骤取决于在步骤1中所做的选择。

如果使用经过分隔的数据，可指定分隔字符。你会看到结果预览。如果使用固定宽度的数据，可直接在预览窗口中指定分列线，见图10、图11所示。

图10 “文本分列向导”中选择“分隔符号”的第二步对话框

图11 “文本分列向导”中选择“固定宽度”的第二步对话框

当你对分列线感到满意时，单击“下一步”按钮继续执行步骤3，在此步骤中，可以在预览窗口中单击一列，并指定该列的常规格式（包括手动调整分裂线的位置）。单击“完成”按钮，Excel将按照你的指定拆分数据，见图12。

图12　图8所示的数据进行分列后的效果(www.xing528.com)

使用快速填充

“文本分列向导”适用于许多数据类型。但有时你会遇到不能由该向导分析的数据。例如，如果数据的宽度可变且没有分隔符号，“文本分列向导”是没有用的。在这样的情况下，“快速填充”功能可完成相应任务。但请记住，只有当数据十分一致时，才可以成功执行“快速填充”功能。

“快速填充”功能采用模式识别来提取数据（和连接数据）。只需要在与数据相邻的一列中输入几个示例，并选择“数据”“数据工具”“快速填充”（或按Ctrl＋E）即可。Excel将分析示例，并尝试填充其余单元格。如果Excel末识别出你设想的模式，可按Ctrl＋Z键，再添加两个样例，然后再试一次。

图13显示了一个工作表，其中的单个列中包含一些文本。我们的目标是提取每个文本字符串中的数字，并将其放入一个单独的单元格。“文本分列向导”无法完成此操作，因为空格分隔符不一致。可编写一个数组公式，但该公式非常复杂。

图13　目标是提取A列中的数字

要尝试使用“快速填充”功能，可激活单元格B1，然后键入第一个数字（20）。移到B2，然后键入第二个数字（6）。选择“数据”“数据工具”“快速填充”（或按Ctrl＋E），Excel将在一瞬间填充其余单元格。图14显示了结果。

图14　使用在B1和B2中手动输入的示例，Excel的快速填充功能会进行一些不正确的猜测

如你所见，Excel会确定大部分值。如果提供更多示例，准确度将提高。例如，提供一个小数示例。删除建议的值，在单元格B6中输入3.12，然后按Ctrl＋E。此时，Excel会得到正确的结果（参见图15）

图15　在输入小数示例之后，Excel将生成正确结果

这个简单示例演示了两个要点：

·使用“快速填充”功能后必须仔细检查数据。因为只有前几行是正确的，你不能假定“快速填充”功能可对所有行生成正确的结果。

·当提供更多示例之后，“快速填充”功能可提高准确度。

图16显示了另一个示例，即在A列中的名称。我们的目标是提取名字、姓氏和中间名（如果有）。在B列中，Excel仅使用两个示例（Mark和Tim）成功得到所有的名字。此外，它通过使用Russell和Colman成功提取了所有姓氏（C列）。提取中间名或缩写形式（D列）时难倒了作者，直到作者提供的示例在中间名的两侧都包括空格。

图16　使用“快速填充”功能拆分姓名

总之，Excel的“快速填充”功能只能在数据非常一致时使用才可靠。即使当你认为它正常工作时，仍要仔细检查结果，并在将其用于重要数据前三思。

注意：也可以使用“快速填充”功能从多个列中创建新数据。只需要提供你所需的关于如何结合数据的几个示例，Excel就能计算出模式，并填充列。使用“快速填充”功能创建数据的准确性似乎远高于将其用于提取数据的准确性。