用两份 ID 清单建立对账工作簿
本文目录
你手上有两份设备 ID 导出清单，需要取出同时出现在两份清单中、只在第一份中出现，以及只在第二份中出现的实际记录。一份有用的对账结果应该给出这些行，保留原始数据，并说明怎样处理重复 ID 和空 ID。
这个完整示例提供了成品工作簿、保留数据类型的原始输入、完整结果 CSV和可复用的完整请求。设备清单是原创虚构教学数据，不描述客户设备，也不能证明库存状态。
先确定需要怎样比较
这里要回答的是：每条源记录的精确 ID，是否至少在另一份清单中出现一次？ 每一条原始记录都会得到一个结果。如果 B 中存在一个相等的 ID，A 中两条记录都可以标为 Matched。这不表示把 B 的同一条记录分配了两次，也不表示已经把两台实体设备配成一对。
如果任务是把单条记录一对一分配，你需要另一套规则，还需要足够的识别字段来选择对应记录。这些输入只有 ID。本例只比较 ID 是否存在于另一份清单，不凭空指定配对。
保留两份原始清单
每份清单各有十行。稳定的源行标识 rowID 可以定位每条记录，即使设备 ID 重复或缺失也不例外。下表采用 JSON 风格的引号："Q-7 " 在结束引号前包含一个空格。<blank> 表示源 ID 单元格确实为空。引号和 <blank> 都只是显示记号，不是存进原始 ID 的字符。
RawA
| 源行 rowID | 精确 ID 字面值 |
|---|---|
| A001 | "0012" |
| A002 | "12" |
| A003 | "D-10" |
| A004 | "D-10" |
| A005 | <blank> |
| A006 | "0" |
| A007 | "ABC" |
| A008 | "Q-7 " |
| A009 | "LEFT-9" |
| A010 | "E-2" |
RawB
| 源行 rowID | 精确 ID 字面值 |
|---|---|
| B001 | "12" |
| B002 | "0012" |
| B003 | "D-10" |
| B004 | <blank> |
| B005 | "0" |
| B006 | "abc" |
| B007 | "Q-7" |
| B008 | "RIGHT-8" |
| B009 | "E-2" |
| B010 | "E-2" |
A 清单 CSV和B 清单 CSV包含相同记录。仅在 CSV 中加引号，并不能确定电子表格列的数据类型。导入这些文件时，应把 ID 列保留为文本，并在比较前核对 0012。不要重新打开 CSV 后，就假定看起来像数字的 ID 没有被转换。
工作簿中的 RawA 和 RawB 在 A5:B14 保留源记录。A 列是 rowID，B 列是文本 ID 或空单元格。C 列提供 ID 字面值预览，让 "0012" 和 "Q-7 " 清楚可辨，而不修改原单元格。
生成结果之前，先写清规则
| 问题 | 本工作簿的规则 |
|---|---|
| 怎样才算相等？ | 区分大小写的精确文本相等，空格也参与比较。不做规范化。 |
| 前导零有意义吗？ | 有。0012 与 12 是不同的键。 |
| 大小写有影响吗？ | 有。ABC 与 abc 保持不同。 |
| 零是什么？ | 0 是有效的文本标识符，参与匹配。 |
| 空白是什么？ | 为空的源 ID。保留这一行，标为 Blank ID，并排除在匹配和独立键计数之外。两个空白不互相匹配。 |
| 怎样处理重复 ID？ | 保留每一行，统计出现次数，并标记所有受影响的行。判断是否存在只要求至少有一条对应记录。不去重，也不做笛卡尔连接。 |
| 行怎样排序？ | 先保留 A 的原始顺序，再接上 B 的原始顺序。源行 rowID 始终跟随记录。 |
| 没找到意味着什么？ | 只表示这个精确键没有出现在另一份所提供的清单中，不能据此判断停用或作出删除决定。 |
还有一条规则没有确定：A008 的 "Q-7 " 和 B007 的 "Q-7" 是否应代表同一身份？ 源材料没有回答。工作簿按精确相等规则，把它们分别列为 Left-only 和 Right-only，并把两行都标记为待复核。这些是暂定的精确字符串比较结果，不是对背后两台设备是否不同的裁定。
已给定的大小写规则解决了 ABC 与 abc 的问题，前导零规则也解决了 0012 与 12 的问题。它们不是额外的未决问题。
使用工作簿
- 打开 Results。汇总位于完整的20行结果上方。每一行都带源行 rowID，因此不会只靠 ID 定位记录。
- 用 Classification 列提取 Matched（匹配）、Left-only（仅 A 有）、Right-only（仅 B 有）或 Blank ID（空 ID）记录。
correct-results.csv提供这份完整结果，保留原始 ID。 - 打开 Exceptions，在 Attention 列筛掉空项。这个视图保留全部源记录，让公式能响应输入修改。本例有八行带提示：两个空白行、重复组中的四行，以及两个空格候选行。
- 请负责标识符规则的人确认这个具体的空格问题，并把答案记在 Results B42。这个字段只记录决定。选择 Confirm alias 不会重写 ID，也不会自动改变匹配规则。
如果答案是 Keep distinct，就保留精确比较，并记录理由后结束这项复核。如果答案是 Confirm alias，则应保留 RawA 和 RawB，另外定义获准采用的比较键或别名映射，再重新生成比较结果和汇总。不要悄悄修改原始 ID。交付的工作簿没有别名映射，决定仍为 Unresolved。
沿着一条源记录查看计算
Results 第15行代表 A001。其源行 rowID 链接到 RawA A5，ID 来自 RawA B5。显示的 ID 字面值由以下公式计算：
=IF(LEN('RawA'!B5)=0,"",CHAR(34)&'RawA'!B5&CHAR(34))
LEN 检查会把 0 保留为非空 ID。引号只属于这个显示单元格。计数公式读取原始文本单元格。
同清单和另一清单的出现次数，都是把逐项精确比较的结果相加。例如，A001 与 B 的第一条记录比较时，其中一项是：
=--EXACT("ID:"&'RawA'!B5,"ID:"&'RawB'!B5)
这一项贡献0，因为 0012 不等于 12。与 RawB B6 比较时贡献1，因为那里存的是 0012。公式把 B5:B14 中各项的结果相加。计算时会给两个比较对象都加上固定的 ID: 前缀，防止看起来像数字的字符串被强制转换。它既不改变原始 ID，也不会让两个不同字符串变得相等。逐项比较也避免了所用计算引擎把 EXACT 当成不支持的数组运算。
第15行的分类公式是：
=IF(LEN(C15)=0,"Blank ID",IF(E15>0,"Matched",IF(A15="A","Left-only","Right-only")))
First in source 只在每个非空键首次出现在其所属清单时取1。把它相加，就得到该清单的独立键数。First overall 则覆盖 Results 的全部二十行，因此其总和给出并集，而不是把两份清单各自的独立键数直接相加。
Attention 检查缺失 ID、同清单出现次数大于1，以及可能只差空格的对应记录。空格候选行没有精确对应项，但经过公式中的 TRIM 后能找到对应项。TRIM 只用于提出复核，绝不用于确定匹配。 这套示例数据涉及的是一个尾部 ASCII 空格。候选检查不能普遍检测所有 Unicode 空白或规范化问题。
工作簿是固定的十加十行示例。修改现有 ID 单元格会重新计算公式。用于更大清单时，需要扩展两份原始数据范围、结果行、首次出现计数所用的前缀范围、异常链接和汇总范围，然后重新执行边界检查。只添加一行原始数据，并不会扩展这个模型。替换教学输入前，应先另存一份新的源数据副本。
一份完整的错误结果
下面是一种特意编写的错误方法：删除空 ID，去掉首尾空格，把文本转成小写，把全由数字构成的字符串转成数值，每个转换后的键只保留第一行，再比较这些键。它看起来整齐，返回八行：
| 规范化后的键 | A 行 | A 原始 ID | B 行 | B 原始 ID | 错误结果 |
|---|---|---|---|---|---|
| 12 | A001 | "0012" |
B001 | "12" |
Matched |
| d-10 | A003 | "D-10" |
B003 | "D-10" |
Matched |
| 0 | A006 | "0" |
B005 | "0" |
Matched |
| abc | A007 | "ABC" |
B006 | "abc" |
Matched |
| q-7 | A008 | "Q-7 " |
B007 | "Q-7" |
Matched |
| left-9 | A009 | "LEFT-9" |
— | 无源行 | Left-only |
| e-2 | A010 | "E-2" |
B009 | "E-2" |
Matched |
| right-8 | — | 无源行 | B008 | "RIGHT-8" |
Right-only |
完整文件见 wrong-results.csv。这是构造的教学错例，不是归于某个受测模型或个人的输出。
其中三组所谓匹配不符合已声明的规则：
- A001 的
"0012"经过数值转换后，与 B001 的"12"配在一起。A001 真正精确对应的是 B002，A002 才是 B001 的精确对应项。 - A007 的
"ABC"经过大小写折叠后，与 B006 的"abc"配在一起，违反了明确的大小写规则。 - A008 的
"Q-7 "经过 TRIM 后，与 B007 的"Q-7"配在一起。这悄悄替那个尚未解决的业务问题作了决定。
六条原始记录完全消失了：
| 丢失的源行 | 错误方法为什么丢失这一行 |
|---|---|
| A002 | 规范化后的 12 键已经被 A001 占用。 |
| A004 | 第二条 D-10 记录被丢弃。 |
| A005 | 生成报告前就删除了空白行。 |
| B002 | 规范化后的 12 键已经被 B001 占用。 |
| B004 | 生成报告前就删除了空白行。 |
| B010 | 第二条 E-2 记录被丢弃。 |
把它叫作“六组匹配、两台未匹配设备”，会混淆规范化后的键、源记录行和实体设备。这份八行输出只代表十四个源行 rowID。整齐的计数并不能补回丢失的记录。
修复方法，并返回每一行
重新加载未改动的原始数据，去掉未经批准的规范化和只留首条的去重方法，逐条独立分类。保留空白行，先增加出现次数，再做汇总。完整修复结果如下：
| 源行 rowID | 精确 ID 字面值 | 同清单行数 | 另一清单行数 | 结果 | 注意事项 |
|---|---|---|---|---|---|
| A001 | "0012" |
1 | 1 | Matched | |
| A002 | "12" |
1 | 1 | Matched | |
| A003 | "D-10" |
2 | 1 | Matched | Duplicate in source |
| A004 | "D-10" |
2 | 1 | Matched | Duplicate in source |
| A005 | <blank> |
n.a. | n.a. | Blank ID | Missing ID |
| A006 | "0" |
1 | 1 | Matched | |
| A007 | "ABC" |
1 | 0 | Left-only | |
| A008 | "Q-7 " |
1 | 0 | Left-only | Whitespace review |
| A009 | "LEFT-9" |
1 | 0 | Left-only | |
| A010 | "E-2" |
1 | 2 | Matched | |
| B001 | "12" |
1 | 1 | Matched | |
| B002 | "0012" |
1 | 1 | Matched | |
| B003 | "D-10" |
1 | 2 | Matched | |
| B004 | <blank> |
n.a. | n.a. | Blank ID | Missing ID |
| B005 | "0" |
1 | 1 | Matched | |
| B006 | "abc" |
1 | 0 | Right-only | |
| B007 | "Q-7" |
1 | 0 | Right-only | Whitespace review |
| B008 | "RIGHT-8" |
1 | 0 | Right-only | |
| B009 | "E-2" |
2 | 1 | Matched | Duplicate in source |
| B010 | "E-2" |
2 | 1 | Matched | Duplicate in source |
空 ID 行的空计数显示为 n.a.。计数0则表示对一个实际非空 ID 做了比较，但没有找到精确对应项。这两种状态含义不同。
A003 和 A004 都保持 Matched，各自在另一清单中有一条对应记录。B003 则看到 A 中有两次出现。A010 看到 B 中有两次出现，而 B009 和 B010 各自看到 A 中有一次出现。这些是判断是否存在时的出现次数，并不是已经选定的配对。
分别检查行数和独立键数
| 指标 | A | B | 两份清单合看 |
|---|---|---|---|
| 原始行数 | 10 | 10 | 20 |
| 空 ID 行数 | 1 | 1 | 2 |
| 非空 ID 行数 | 9 | 9 | 18 |
| 独立非空键数 | 8 | 8 | 并集11个 |
| 匹配的源行数 | 6 | 6 | 12 |
| 仅左侧或仅右侧有的源行数 | 3 | 3 | 6 |
| 匹配的独立键数 | 5 | 5 | 交集5个 |
共享键是 0012、12、D-10、0 和 E-2。按精确相等规则，每份清单各有三个不同的键没有出现在另一份中。并集为 8 + 8 − 5 = 11。行数对账为：12行匹配 + 3行仅左侧有 + 3行仅右侧有 + 2行空白 = 20。十二条匹配源记录，不代表十二组配对，也不代表十二台不同的共享设备。
完整请求
附上 source-lists.json，里面包含上面展示的两份完整清单。两份 CSV 是可选副本。把以下请求连同这些输入一起提供，不要只说“找出不同”：
用附件 source-lists.json 建立对账工作簿。RawA 和 RawB 各包含十条原创虚构设备 ID 记录。JSON 字符串是文本标识符；null 表示为空的 ID 单元格。raw-a.csv 和 raw-b.csv 是等值的 CSV 副本，但预期数据类型以 JSON 为准。
返回一个实际的 .xlsx，包含 RawA、RawB、Results 和 Exceptions，以及完整的逐行结果 CSV。保留全部源行 rowID、顺序、原始文本、前导零、重复项和空 ID 单元格。保持 RawA 和 RawB 完整不变。在单独的显示字段中，用引号显示 ID 字面值，让尾部空格可见。不要把引号存入原始 ID 单元格。
采用区分大小写的精确文本相等规则。"0012" 与 "12" 不同，"ABC" 与 "abc" 不同。"0" 是有效的文本 ID，不是空白。不要去掉首尾空格、强制转为数值、折叠大小写、模糊合并，或悄悄对标识符去重。
这是按源行判断 ID 是否存在于另一清单的对账，不是一对一分配。每条原始记录都必须对应一条 Results 结果。非空 ID 只要在另一份清单中至少存在一个完全相等的 ID，就标为 Matched；否则，A 中标为 Left-only，B 中标为 Right-only。空 ID 行保留为 Blank ID，并排除在匹配和独立键计数之外。报告每条非空源记录在本清单及另一清单中的出现次数。标记所有参与同源重复组的行，而不只是多出来的那一次。不要丢弃任何源记录，也不要把重复键展开成多对多连接。
分别展示行数和独立非空键数。合并后的独立键数是并集，匹配的独立键数是交集。解释为什么匹配的源行数不等于匹配对数。
有一条业务规则尚未确定：A008 是 "Q-7 "，尾部有一个 ASCII 空格；B007 是 "Q-7"。暂按精确字符串报告是否匹配，把两行都标记为空格复核，并询问它们是否代表同一身份。不要猜测答案。记录一项决定，不能冒充已经应用尚未实现的别名规则。
示例必须使用能重新计算的公式，包括来源链接、出现次数、分类、提示和汇总计数。写明固定范围，以及新增行时需要怎样扩展。异常视图应能通过源行 rowID 追踪，并方便筛出带提示的行。保持零、空白和重复是不同状态。
返回那份完整的、特意编写的只留首条／规范化／去重错误输出，解释每条丢失的源记录和每组错误匹配。然后返回完整修复结果。根据所提供的源记录核验实际工作簿，包括把零改为空白、把一个重复项改成新 ID，以及改变大小写，随后恢复原始数据。说明测试使用了哪个计算引擎。
未出现在另一份清单中，只能证明它不在所提供的那份快照里。不要推断停用状态、库存数量、所有权或删除权限。不要修改真实库存、发送消息或采取外部行动。即使技术修复成功，仍要提出那个空格问题。
如果口述有助于你把规则说清楚，也可以口述这份比较说明，再在使用前核对输入的 ID 和异常。本例没有测量语音效率，也不假定口述的标识符被正确捕获。
使用自己的数据前，检查下载的结果
确认 A001 和 B002 仍包含文本 0012，A006 和 B005 包含文本 0，两个空白源单元格确实为空。核对两个重复组及全部二十个源行 rowID。把五个不同的共享键与十二条匹配源记录分开检查。
工作簿公式已在 Artifact Tool 中重新计算，并与另一份采用精确字符串相等规则的 JavaScript 计算逐行对照。临时修改测试了零变为空白、一个重复项变成新键，以及大小写改变，之后恢复了原始数据。保存后的工作簿文件包也检查了源文本、空单元格和公式。没有在原生 Microsoft Excel 中执行，也没有进行模型或真人可用性试验。
技术修复不能决定空格规则。在问题得到回答前，保留这两行的可见提示。这里的任何结果都不授权从源系统删除数据。