Power Query技巧之计算带文本的数据

计算文本型数字

数据源：Sheet1 中的“超级表”命名为“表 1”，包含“品名”和“数量”两列，“数量”列里的数据为文本型数字（某些系统导出的数据均为文本型数字）。

目标：在不改变数据源的前提下，对“数量”列的数据进行求和，使结果不受文本型数据影响而正确计算。图 8-1 所示为数据源及直接使用SUM函数计算出的结果。

解决方案：由自动步骤“更改的类型”自行解决。

第 1 步 选取数据源表中数据区域的任意一个单元格，在【数据】选项卡下以【自表格/区域】的方式进入“异空间”。自动步骤“更改的类型”将文本型数字直接改成了与这一列中其他数据一致的整数型，如图 8-2 所示。

第 2 步 选取“数量”列，单击【转换】选项卡下的【统计信息】下拉按钮，选择下拉选项中的【求和】，得出“数量”列的总计。

第 3 步 单击【数值工具转换】选项卡下的【到表】按钮，将其转换成查询表。

第 4 步 将标题名由“Column1”修改为“数量总计”。

上述操作步骤如图 8-3 所示。

第 5 步 单击【主页】选项卡下的【关闭并上载】下拉按钮，在下拉选项中选择【关闭并上载至…】，在【导入数据】对话框中设置将数据以【表】的形式放置在【现有工作表】的D2 单元格里，单击【确定】按钮完成操作，如图 8-4 所示。

最终效果 数据源表中的“数量”列新增任意类型的数字后，可一键刷新“数量总计”结果。如图 8-5 所示，在第 12 行添加数据，单击【数据】选项卡下的【全部刷新】按钮，D3 单元格里的数量总计就由原来的“658”变成了“757”。

忽略纯文本的计算

数据源：Sheet2 中的“超级表”命名为“表 2”，包含“品名”“数量”和“单价”3 列，B7 单元格里是纯文本“未知”，C3 单元格里是纯文本“不明”。

目标：在不改变数据源的前提下，由“数量”列乘“单价”列获得“金额”列，结果不受文本型数据影响而正确计算。图 8-6 所示为数据源直接进行乘法运算得出的结果，以及对D列进行求和的结果。

解决方案：利用【替换错误】等功能解决。

第 1 步 选取数据源表中数据区域的任意一个单元格，以【自表格/区域】的方式进入“异空间”。

第 2 步 分别对“数量”列和“单价”列的【数据类型】进行修改，改成与之相匹配的【整数】类型和【货币】类型（亦可将这两列都改成【小数】类型）。修改以后，原来内容是“未知”和“不明”的文本内容单元格，会因为数据类型不匹配而变成错误值“Error”，如图 8-7 所示。

第 3 步 选取“数量”和“单价”两列，单击【转换】选项卡下的【替换值】下拉按钮，选择其中的【替换错误】，在弹出的【替换错误】对话框里将【值】填写为“0”，然后单击【确定】按钮关闭对话框，如图 8-8 所示。

第 4 步 选取“数量”和“单价”两列，单击【添加列】选项卡下的【标准】下拉按钮，在下拉选项中选择【乘】，再将新添加的列名由“乘法”改为“金额”，如图 8-9 所示。

第 5 步 选取除“金额”列以外的其他列，利用【主页】选项卡下的【删除列】功能，将选取的列全部删除，只保留“金额”列，如图 8-10 所示。

第 6 步 单击【主页】选项卡下的【关闭并上载】下拉按钮，在下拉选项中选择【关闭并上载至…】，在弹出的对话框里设置数据放置位置，将数据放置在现有工作表的D1单元格，如图8-11所示。

最终效果 “金额”列为“数量”列与“单价”列相乘的结果，不会因为出现文本而得出错误值，且数据源表中的内容有增、删、改时，可一键刷新，不需要再重复操作。