Power Query技巧之拆分与扩展
数据源：Sheet1 表中包含“品名”与“明细”两列数据，“品名”列里包含合并单元格，“明细”列分“原产地”“类别”和“美味等级”，纵向排列。目标：在不改变数据源和不添加辅助列的前提下，将表格结构转换为二维表，效果如图8-31所示。解决方案：利用【填充】【拆分列】【透视列】等功能实现。第 1 步 选取数据源表中数据区域的任意一个非合并的单元格（如B2），以【自表格/区域】的方式导入“异空间”。第 2 步 选取“品名”列后单击【转换】选项卡下的【填充】下拉按钮，并从下拉选项中选择【向下】，如图 8-32 所示。第 3 步 选取“明细”列，单击【转换】选项卡下的【拆分列】下拉按钮，选择对该列【按分隔符】全角冒号进行拆分，具体操作步骤如图 8-33 所示。第 4 步 选取“明细.1”列后，单击【转换】选项卡下的【透视列】按钮，在弹出的【透视列】对话框中以“明细.2”作为【值列】，并点开折叠的【高级选项】，将【聚合值函数】设置为【不要聚合】，单击【确定】按钮，如图 8-34 所示。第 5 步 单击【主页】选项卡下的【关闭并上载】按钮完成操作。结果如图 8-35 所示，数据被分为“品名”“原产地”“类别”和“美味等级”4 列，且数据源表中的内容有增、删、改时，可一键刷新。数据源：Sheet2 中的“超级表”命名为“表 2”，包含“品名”与“明细”两列数据，“明细”列分“原产地”“类别”和“美味等级”，纵向排列，同一品名的明细数据在一个单元格内强制换行。目标：在不改变数据源和不添加辅助列的前提下，将表格结构转换为二维表，效果如图8-36所示。第 1 步 选取数据源表中数据区域的任意一个单元格，以【自表格/区域】的方式进入“异空间”。第 2 步 选取“明细”列，单击【转换】选项卡下的【拆分列】下拉按钮，选择下拉选项中的【按分隔符】，在【按分隔符拆分列】对话框中展开【高级选项】后，设置数据【拆分为】“行”，勾选【使用特殊字符进行拆分】复选框，设置【插入特殊字符】为“换行”，单击【确定】按钮，如图 8-37 所示。第 3 步 再次用【按分隔符】全角冒号对“明细”列进行【拆分列】操作。第 4 步 选取“明细.1”列后，单击【转换】选项卡下的【透视列】按钮，在【透视列】对话框中以“明细.2”作为【值列】，并点开折叠的【高级选项】，将【聚合值函数】设置为【不要聚合】，单击【确定】按钮关闭对话框。第 5 步 单击【主页】选项卡下的【关闭并上载】按钮完成操作。数据被分为“品名”“原产地”“类别”和“美味等级”4 列，且数据源表中的内容有增、删、改时，可一键刷新。数据源：Sheet3 中的“超级表”命名为“表 3”，包含“品名”与“明细”两列数据，“明细”列分“原产地”“类别”和“美味等级”，横向排列，同一品名的明细数据在一个单元格内以数字分隔。目标：在不改变数据源和不添加辅助列的前提下，将表格结构转换为二维表，如图 8-38所示。第 1 步 选取数据源表中数据区域的任意一个单元格，以【自表格/区域】的方式进入“异空间”。第 2 步 选取“明细”列，单击【转换】选项卡下的【拆分列】下拉按钮，选择下拉选项中的【按照从非数字到数字的转换】，如图 8-39 所示。第 3 步 选取“品名”列，单击【转换】选项卡下的【逆透视列】下拉按钮，选择下拉选项中的【逆透视其他列】，将被拆分出来的“明细.1”“明细.2”和“明细.3”3列转换成1列，如图8-40所示。第 4 步 选取“属性”列，单击【主页】选项卡下的【删除列】按钮删除“属性”列。第 5 步 选取“值”列，单击【转换】选项卡下的【提取】下拉按钮，选择【范围】选项，在【提取文本范围】对话框中，设置【起始索引】为“1”，【字符数】为“99”，单击【确定】按钮，如图 8-41所示。如果“值”列中的数字不止１位，则需要借助Ｍ公式实现。单击【添加列】选项卡下的【自定义列】按钮，在【自定义列】对话框中输入新列名，写入如下M公式，单击【确定】按钮，如图 8-42所示，并在完成操作后删除“值”列。除了去除数字以外，利用M公式还可以去除其他内容，如下所示：第 7 步 使用【按分隔符】全角冒号对“明细”列进行【拆分列】操作。第 8 步 选取“明细.1”列后，单击【转换】选项卡下的【透视列】按钮，以“明细.2”作为【值列】，并点开折叠的【高级选项】，将【聚合值函数】设置为【不要聚合】。第 9 步 单击【主页】选项卡下的【关闭并上载】按钮完成操作。数据源：Sheet4 中的“超级表”命名为“表 4”，包含“金额”列。目标：在不改变数据源和不添加辅助列的前提下将金额列拆分成B:G列的效果（假设金额小于1000 元），如图 8-43 所示。解决方案：利用【乘法】【添加前缀】【提取结尾字符】【拆分列】等功能实现。第 1 步 选取数据源表中数据区域的任意一个单元格，以【自表格/区域】的方式进入“异空间”。第 2 步 选取“金额”列，单击【转换】选项卡下的【标准】下拉按钮，在下拉选项中选择【乘】，并在弹出的【乘】对话框中输入数字“100”，然后单击【确定】按钮，如图 8-44 所示。第 3 步 将“金额”列的【数据类型】改成【整数】，如图 8-45 所示。第 4 步 选取“金额”列，单击【转换】选项卡下的【格式】下拉按钮，选择下拉选项中的【添加前缀】，并在弹出的【前缀】对话框里输入“xx ￥”，然后单击【确定】按钮，如图 8-46 所示。此处的“x”相当于占位符，目的是给较小的数字增加长度，以便后续操作，因为这里的金额小于千元，所以用两个“x”就够了，现实中可以根据实际情况使用相应数量的“x”。第 5 步 选取“金额”列，单击【转换】选项卡下的【提取】下拉按钮，选择下拉选项中的【结尾字符】，并在弹出的【提取结尾字符】对话框里输入“6”，然后单击【确定】按钮，如图 8-47所示。第 6 步 选取“金额”列，单击【主页】选项卡下的【拆分列】下拉按钮，选择下拉选项中的【按字符数】，并在弹出的【按字符数拆分列】对话框里输入数字 1，然后单击【确定】按钮，如图 8-48 所示。第 7 步 选取所有列，单击【转换】选项卡下的【替换值】按钮，在弹出的【替换值】对话框中输入【要查找的值】为“x”，然后单击【确定】按钮即可，【替换为】的内容不需要填写，如图 8-49 所示。第8步将标题名从左至右依次改为“千”“百”“十”“元”“角”“分”。第 9 步 将所有列的【数据类型】都改成【文本】。第 10 步 单击【主页】选项卡下的【关闭并上载】下拉按钮，选择其中的【关闭并上载至…】选项，在【导入数据】对话框中将【数据的放置位置】调整到“现有工作表”的B1 单元格，然后单击【确定】按钮，如图 8-50 所示。
