夜雨聆风可能某一条设备是前几天录入的，另外一条是后来从别的 Sheet 复制过来的； 也可能设备价格调整以后，只改了其中一个系统，另外一个系统忘了改； 甚至技术参数本身没有区别，只是两处复制的版本不一样，顺序、标点或者某一处文字存在差异。
同型号，技术参数内容不一致； 同型号，技术参数存在空值（空值就是单元格内没有内容）； 同型号，综合单价不一致； 同型号，综合单价存在空值。
这三条设备型号完全一样，技术参数也一样，但是综合单价出现了350元和420元两个价格。我希望 Power Query 能把这个型号单独筛出来，并直接告诉我：综合单价不一致。
然后把这三条原始记录全部列出来。这样我不用再去原来的二十多个 Sheet里搜索型号，直接就能看到具体是哪几条设备的价格不一样。技术参数也是同样的逻辑。假如：
那么就提示：技术参数存在空值。
如果一个型号同时存在多个问题，也全部显示出来。
【第一步：引用原来的内部汇总查询】
上一篇已经通过Power Query，把各个系统 Sheet 的清单汇总成了一张“内部版”总表。这次不需要重新读取那二十多个超级表，直接在原来的内部汇总查询基础上继续做就行了。
打开Excel，进入：数据 → 查询和连接
找到上一篇建立的内部汇总查询。
我这个文件里的查询名称是：
清单-自己看版本
右键这个查询，选择：
引用
【截图1：在“查询和连接”中右键内部汇总查询，选择“引用”】
这里我用的是“引用”，而不是“复制”。
因为这张新的核查表，本来就是建立在内部汇总表基础上的。以后各个系统的源清单发生变化，Power Query 刷新时，数据关系就是：
各系统源清单 → 内部汇总查询 → 型号一致性核查
这样不需要再单独维护一套数据源。
引用以后，把新建立的查询名称改成：型号一致性核查
这里还有一个我这次实际碰到的小问题。
我的 Excel 工作表名称是：清单-一张表-内部版
但是 Power Query 左侧真正的查询名称是：清单-自己看版本
这两个不是一回事。查询名称可以自行修改，但是如果修改了，后面代码中的查询名称也必须同步修改。
后面的代码需要引用的是 Power Query 查询名称，而不是 Excel 下方的 Sheet名称。我第一次就把这两个名称弄混了，结果运行代码以后直接报错。这个问题后面讲到代码时再具体说。
【第二步：进入高级编辑器】
双击“型号一致性核查”，进入 Power Query 编辑器。然后点击：主页 → 高级编辑器
【截图2：Power Query 高级编辑器位置】
把里面原来的代码全部删除，粘贴下面这一段：
let
//引用现有内部汇总查询
源 = #"清单-自己看版本",
//排除型号本身为空的记录
过滤空型号 = Table.SelectRows(
源,
each [型号] <> null
and Text.Trim(Text.From([型号])) <> ""
),
//统一型号格式，清除前后空格及不可见字符
标准化型号 = Table.TransformColumns(
过滤空型号,
{
{
"型号",
each Text.Trim(Text.Clean(Text.From(_))),
type text
}
}
),
//生成技术参数核查值
添加技术参数核查值 = Table.AddColumn(
标准化型号,
"技术参数_核查",
each
if [技术参数] = null then
null
else
let
参数文本 = Text.Trim(Text.Clean(Text.From([技术参数])))
in
if 参数文本 = "" then null else 参数文本,
type text
),
//生成综合单价核查值
//按两位小数进行比较
添加综合单价核查值 = Table.AddColumn(
添加技术参数核查值,
"综合单价_核查",
each
if [综合单价] = null then
null
else
try Number.Round(Number.From([综合单价]), 2)
otherwise null,
type number
),
//按型号分组
按型号分组 = Table.Group(
添加综合单价核查值,
{"型号"},
{
{
"明细",
each _,
type table
},
//同型号一共有多少条记录
{
"记录数",
each Table.RowCount(_),
Int64.Type
},
//非空技术参数共有多少种
{
"技术参数种类数",
each
List.Count(
List.Distinct(
List.RemoveNulls([技术参数_核查])
)
),
Int64.Type
},
//技术参数空值数量
{
"技术参数空值数",
each
List.Count([技术参数_核查])
-
List.Count(List.RemoveNulls([技术参数_核查])),
Int64.Type
},
//非空综合单价共有多少种
{
"综合单价种类数",
each
List.Count(
List.Distinct(
List.RemoveNulls([综合单价_核查])
)
),
Int64.Type
},
//综合单价空值数量
{
"综合单价空值数",
each
List.Count([综合单价_核查])
-
List.Count(List.RemoveNulls([综合单价_核查])),
Int64.Type
}
}
),
//技术参数内容是否不一致
添加参数内容核查 = Table.AddColumn(
按型号分组,
"技术参数内容核查",
each
if [技术参数种类数] > 1 then
"技术参数内容不一致"
else
null,
type text
),
//技术参数是否存在空值
添加参数空值核查 = Table.AddColumn(
添加参数内容核查,
"技术参数空值核查",
each
if [技术参数空值数] > 0 then
"技术参数存在空值"
else
null,
type text
),
//综合单价是否不一致
添加单价内容核查 = Table.AddColumn(
添加参数空值核查,
"综合单价内容核查",
each
if [综合单价种类数] > 1 then
"综合单价不一致"
else
null,
type text
),
//综合单价是否存在空值
添加单价空值核查 = Table.AddColumn(
添加单价内容核查,
"综合单价空值核查",
each
if [综合单价空值数] > 0 then
"综合单价存在空值"
else
null,
type text
),
//增加一个汇总后的“异常类型”字段
添加异常类型 = Table.AddColumn(
添加单价空值核查,
"异常类型",
each
Text.Combine(
List.RemoveNulls(
{
[技术参数内容核查],
[技术参数空值核查],
[综合单价内容核查],
[综合单价空值核查]
}
),
"；"
),
type text
),
//只检查重复出现的型号，并且至少存在一种异常
筛选异常型号 = Table.SelectRows(
添加异常类型,
each
[记录数] > 1
and [异常类型] <> ""
),
//展开原始明细，方便查找具体是哪几条记录有问题
展开明细 = Table.ExpandTableColumn(
筛选异常型号,
"明细",
{
"所属系统",
"序号",
"设备名称",
"品牌",
"技术参数",
"单位",
"数量",
"综合单价"
},
{
"所属系统",
"序号",
"设备名称",
"品牌",
"技术参数",
"单位",
"数量",
"综合单价"
}
),
//调整最终显示顺序
调整列顺序 = Table.ReorderColumns(
展开明细,
{
"型号",
"记录数",
"异常类型",
"技术参数内容核查",
"技术参数空值核查",
"综合单价内容核查",
"综合单价空值核查",
"技术参数种类数",
"技术参数空值数",
"综合单价种类数",
"综合单价空值数",
"所属系统",
"序号",
"设备名称",
"品牌",
"技术参数",
"单位",
"数量",
"综合单价"
}
)
in
调整列顺序
粘贴完成以后，点击“完成”。如果没有报错，就可以看到核查结果。
【截图3：Power Query 中的核查结果】
【这里我第一次就报错了】
我第一次粘贴完代码以后，Power Query 直接报错：
Expression.Error：导入“清单-一张表-内部版”没有匹配的导出。是否缺少模块引用？
一开始我还以为后面的代码哪里有问题。后来才发现，问题出在第一行。我写的是：
源 = #"清单-一张表-内部版",
但是“清单-一张表-内部版”其实是 Excel 的 Sheet 名称。Power Query 左侧真正的查询名称是“清单-自己看版本”。
所以正确的写法应该是：
源 = #"清单-自己看版本",
改完以后就正常了。这个问题很容易混淆，所以记录一下。以后如果再碰到：导入XXX没有匹配的导出
先看看代码第一行引用的是不是Power Query 查询名称。
【截图4：左侧查询名称与 Excel Sheet 名称的区别】
【这段代码其实做了什么】
代码看起来比较长，但逻辑没有那么复杂。
第一步，先把型号为空的设备排除，再对型号做简单清理，去掉前后的空格和部分不可见字符。
然后把相同型号的设备放到一起。
例如某个型号在整张清单里出现了5次，Power Query 就会去看这5条记录：
技术参数一共有几种？
技术参数有没有空白？
综合单价一共有几种？
综合单价有没有空白？
这里的技术参数会先做简单清理，空白参数单独统计，不参与“参数种类数”的计算。
如果5条非空技术参数完全一样，那么：技术参数种类数 = 1，正常。
如果出现两个不同版本：
技术参数种类数 = 2
那么就提示：技术参数内容不一致
如果其中有一条技术参数没有填写，则另外提示：技术参数存在空值
综合单价也是一样，只不过在比较以前会先统一保留两位小数。
例如：
350
350
350
420
那么综合单价就有两种，Power Query 会提示：综合单价不一致。
最后，只保留重复出现并且存在异常的型号。
所以以后打开这张核查表，如果一条数据都没有，其实是好事。说明按照目前这套规则，没有发现重复型号存在技术参数或综合单价方面的异常。
【实际检查以后，我又发现一个有意思的问题】
核查表做完以后，我就真的跑了一遍。结果发现有一个综合布线的设备，Power Query 提示：技术参数内容不一致。
我把对应的两条技术参数放在一起看。第一眼看上去，我觉得两段技术参数完全一样。品牌一样，型号一样，技术参数也几乎一模一样。我还以为Power Query 又把什么看不见的空格识别成差异了。
后来仔细一条一条对比，才发现第7条的顺序不一样。其中一条写的是：
进线方式：180°进线；线缆保护盖：PC材料；
另外一条写的是：
线缆保护盖：PC材料；进线方式：180°进线；
技术含义其实没有区别。只是两个参数的前后顺序调换了。
那这个算不算异常？我觉得算。
因为我做这张核查表，本来也不是为了让 Power Query 去理解技术参数的“语义”。
我要检查的是：同一个型号的设备，技术参数是否完全一致。如果是同一个型号，理论上直接复制同一份技术参数就行。现在顺序不一样，至少说明两条参数不是来自完全相同的一份内容。
所以目前我没有继续修改代码，让Power Query 去识别参数的技术含义。除了代码本身会清理的前后空格和部分不可见字符以外，只要同型号设备清理后的技术参数文本不一致，就先报出来。比如：
参数顺序不同； 标点不同； 正文内容不同； 正文中的空格位置不同。
然后我再人工看一下。
如果只是顺序不一样，就把两条参数统一。如果确实是某一项参数不一样，那就要进一步核对到底哪一条是对的。我觉得这样更适合做最终清单的标准化检查。
然后看看它到底在哪几个系统里出现。所以代码最后又把对应的原始记录展开了。核查表里会继续显示：
所属系统； 序号； 设备名称； 品牌； 型号； 技术参数； 单位； 数量； 综合单价。 
【截图5：核查表的最终效果】
【最后关闭并上载】
Power Query 中确认结果正常以后：主页 → 关闭并上载至 → 表 → 新工作表
我把这张 Sheet 命名为：型号一致性核查
这样以后整份清单就多了一道自动检查。原来的工作流程是：
修改各系统清单↓全部刷新↓看内部汇总表
现在则变成：
修改各系统清单↓数据 → 全部刷新↓看内部汇总表↓再看“型号一致性核查”
如果核查表为空，说明没有发现异常。如果有内容，就顺着它检查。
【这张表目前能检查哪些问题】
目前我把它设置成四种：
技术参数内容不一致； 技术参数存在空值； 综合单价不一致； 综合单价存在空值。
第二步：进入：数据 → 查询和连接
找到内部汇总查询。注意看的是查询名称，不是 Excel Sheet 名称。
第三步：右键内部汇总查询：引用
把新查询命名为：型号一致性核查
第四步：进入：主页 → 高级编辑器
粘贴本文代码。
第五步：检查代码第一行：
源 = #"清单-自己看版本",
这里必须和自己文件里的 Power Query 查询名称一致。
第六步：点击完成，检查结果。
第七步：关闭并上载至 → 表 → 新工作表
Sheet 可以命名为：型号一致性核查
第八步：以后修改完任何一个系统的源清单，只需要：数据 → 全部刷新
然后看一下核查表。有数据就检查，没有数据就结束。
【写在最后】
上一篇把多个 Sheet 汇总到一张表，主要解决的是“怎么方便看”的问题。这次继续利用这张汇总表，又解决了一个“怎么方便查”的问题。以前这类检查不是不能做。Excel 里筛选型号、排序、查找，都能做。
问题是清单有几百行以后，我得先知道“哪个型号可能有问题”，才知道应该查什么。PowerQuery现在做的，就是先把这些可能有问题的型号找出来。至于最后到底哪一条参数是对的，哪个价格应该采用，还是要自己判断。它替代不了人工核对。但是它可以让我不用拿着几百行清单，从第一行看到最后一行。
我觉得这个功能比较适合放到以后每个项目清单的固定检查流程里。
