|
编按：在日常办公中，我们难免会收到不按规范录入的数据表，所以拆分单元格重置数据源就成了Excel人必会的办公技能之一，其中分列法更是最受欢迎的技巧之一。可是，今天小E讲的这个数据案例，分列法反而成了拖累，连这个PQ技巧的25%效率都不如…… 有小伙伴问了这样一个问题：有一组数据，数据中含有多种分隔符，想要一次性把所有的数据拆分出来，并且纵向显示，有没有实现这种拆分的方法？数据如下图所示。A列是组别，B列是人物姓名，在B列的人物姓名之间，有各种各样的分隔符——空格、中文逗号（“，”）、中文顿号（“、”）、斜杠（“/”）、中文分号（“；”）、连接号（“&”）、星号（“*”）和下划线（“_”）。今天，大家就学习一个用PQ快速拆分含有多种分隔符数据的方法。点击A2:B7区域中任意一个单元格，如B3，然后依次用鼠标点击“数据”－“自表格/区域”，弹出“创建表”对话框。在弹出的“创建表”对话框中，“表的数据来源”被EXCEL自动判断成“=$A$1:$B$7”，即本例中的数据区域，此处保持不变，再勾选“表包含标题”（若“表包含标题”已被勾选，则保持勾选即可）。点击“确定”，就可以进入PQ编辑器的界面，如下图所示。依次点击“添加列”－“自定义列”后，弹出“自定义列”对话框，如下图所示。在“自定义列”对话框中，将“新列名”保持为“自定义”不变即可。“自定义列公式”下面的函数框，是需要输入函数的区域，大家在等于号（“=”）后面输入“Text.SplitAny([人物]," ，、/；&*_")”，如下图所示。1.在这个公式中，Text.SplitAny函数的作用是对字段中满足任意一个条件的数据进行拆分。它一共有两个参数，第一参数是字段，第二字段是拆分符号。在本例中，第一参数是“人物”字段，根据PQ中M函数的语法规则，需要用一对中括号将它括起来；第二参数是本例中实际涉及到的各种分隔符号，根据PQ中M函数的语法规则，需要用一对双引号将它括起来；第一参数和第二参数之间，用逗号分隔。2.特别要注意的是，本例中，B2单元格内的数据是用空格进行分隔的，所以大家在写第二参数的时候，千万别忘了输入一个空格，不然空格就无法被拆分了！公式输入完成之后，点击“确定”，得到的结果如下图所示。可以看到，PQ为我们生成了一个新的叫做“自定义”的列，其中的数据均为List。List是PQ中的一种数据类型，大家可以把它理解成一组数据或者一个数组。（如果小伙伴们感兴趣，可以点击任意一个List，则在数据下方会出现一个预览的窗格，可以查看其中的内容，此处从略。）接下来，大家点击“自定义”旁边的“展开”按钮，选择“扩展到新行”，如下图所示。把鼠标放在“人物”这一列上，单击鼠标右键，选择“删除”，将此列删去；在“自定义”这个字段名称上，双击鼠标左键，将其修改为“人物”，得到的结果如下图所示。这时，数据已经达到了最后的基本要求了。大家依次点击“主页”－“关闭并上载”－“关闭并上载”，即可将数据上载到Excel中，如下图所示。上载之后的数据如下图所示。此处，Sheet2就是用PQ加工过之后，上载到Excel中的数据结果。有的小伙伴可能会问，如果我不想对数据进行纵向显示，而只想把数据中的分隔符统一替换成某种分隔符，该如何操作？这个也比较简单。假设现在需要把所有的分隔符号替换成英文状态下的逗号，大家来学习一下。生成“自定义”这一列之前的所有步骤与前文所述是一样的。在生成“自定义”这一列数据之后，大家点击其后的“展开”按钮，选择“提取值”功能，如下图所示。点击“提取值”之后，在弹出的“从列表提取值”对话框中，通过下拉菜单，将“选择串联列表值所使用的分隔符”从“无”更改为“逗号”，如下图所示。可以看到，在“自定义”这一列中，所有的分隔符号都变成了英文状态下的逗号。接下来，只需对此表稍做修改：删除“人物”这一列，将“自定义”改为“人物”，就得到如下结果。最后，依然点击“主页”－“关闭并上载”－“关闭并上载”，将数据上载到Excel中。得到的结果如下图所示。
|
