使用精确匹配查找，为每个库存行添加资产元数据，包括重复的资产 ID。在 Mac 版 Excel 中，你可以使用 HSTACK 和 XLOOKUP 生成一个溢出结果，也可以在 Excel 2021 中使用单独的查找列。
本指南适用于使用 Mac 桌面版、拥有大量艺术资产库存条目且每项资产对应一行元数据的用户。任务是为每项资产添加标题、格式和调色板，同时保留库存原有的顺序、数量和重复条目。以下所有示例数据均为虚构数据。
哪个版本的 Mac 版 Excel 支持此方法？
通过 Excel → 关于 Microsoft Excel 检查你的安装版本。Microsoft 的 Mac 使用说明指出，应用程序的“关于”对话框会显示版本号和许可证类型。选择公式时请同时确认这两项。关于 Office：我使用的是哪个版本的 Office？
| Mac 版 Excel | 本文中的方法 |
|---|---|
| Microsoft 365 Mac 版 Excel | 使用 HSTACK 和 XLOOKUP 生成一个合并结果 |
| Excel 2024 for Mac | 使用 HSTACK 和 XLOOKUP 生成一个合并结果 |
| Excel 2021 for Mac | 使用三个单独的 XLOOKUP 溢出列 |
| Excel 2019 或 2016 | 这些公式需要较新的版本 |
Microsoft 在HSTACK文档中列出了 Microsoft 365 Mac 版 Excel 和 Excel 2024 for Mac。Excel 2021 for Mac 功能文档确认支持 XLOOKUP 和动态数组。XLOOKUP文档明确指出 Excel 2016 和 2019 不受支持。
如果你使用的是 Excel 2021，请使用下面的单独列方法。无需 HSTACK，也能达到相同的丰富数据效果。
设置使用相同资产 ID 的库存和元数据
在同一工作簿中创建两个工作表，分别命名为 Inventory 和 Metadata。在 Inventory!A1:B6 中输入以下库存数据。本示例中，请将这些单元格保留为普通工作表区域。
| AssetID | Quantity |
|---|---|
| ARC-01 | 3 |
| TILE-02 | 5 |
| ARC-01 | 2 |
| GRID-03 | 4 |
| TILE-02 | 1 |
在 Metadata!A1:D4 中输入元数据：
| AssetID | Title | Format | Palette |
|---|---|---|---|
| ARC-01 | Arc Study | Vector | Teal |
| TILE-02 | Tile Study | Raster | Amber |
| GRID-03 | Grid Study | Vector | Slate |
库存中重复的 ID 是有意设置的：两个条目可以分别记录同一资产的不同数量。元数据工作表只需为该资产提供一次通用描述。因此，即使元数据中只有三项资产，输出也应包含五行数据。
请使用能在两个工作表中始终标识同一资产的资产 ID。本示例中的每个 ID 和元数据字段都已填写。在你自己的工作簿中，请明确规定如何表示未知字段，例如使用文本 Unknown，以便将其与未匹配到资产的情况区分开来。
检查每个库存 ID 是否恰好匹配一条元数据
在 Inventory 中的 C1 输入 MetadataMatches。在 C2 中输入此公式，然后将其复制到 C6：
=COUNTIF(Metadata!$A$2:$A$4,A2)
在生成输出之前解读结果：
0：元数据工作表中缺少该资产 ID。1：该资产只有一行元数据，符合此工作流程的预期。- 大于
1的任何数值：元数据中有重复 ID，需要先处理。
COUNTIF 用于统计符合条件的单元格。对于所提供的数据，每次检查都返回 1。请使用此处所示的简单 ID：COUNTIF 会将星号和问号视为通配符，因此包含这些字符的 ID 需要额外处理。在 Microsoft Excel 中使用 COUNTIF 函数
继续操作前，请先解决重复的元数据 ID。XLOOKUP 默认返回第一个匹配项，因此即使结果已填充，也无法据此确认元数据键是唯一的。XLOOKUP 函数
在 Microsoft 365 或 Excel 2024 中生成一个丰富数据数组
创建名为 Enriched 的工作表。在 A1:E1 中输入以下五个标题：AssetID、Quantity、Title、Format、Palette。将 A2:E6 留空。
选择 Enriched!A2，输入下面的完整公式，然后按 Return：
=HSTACK(
Inventory!A2:B6,
XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!B2:B4,"MISSING METADATA",0),
XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!C2:C4,"MISSING METADATA",0),
XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!D2:D4,"MISSING METADATA",0)
)
此公式将原库存中的两列与三个查找结果列合并。每次查找都使用相同的五个库存 ID，使新增字段与原条目保持对应。HSTACK 会按指定顺序将数组水平拼接。HSTACK 函数
每个查找公式中的最后一个 0 都用于请求精确匹配。未匹配的 ID 会返回文本 MISSING METADATA。保留这个醒目的提示有助于你找到不完整的匹配项，而不是留下无法解释的空单元格。XLOOKUP 函数
预期输出位于 Enriched!A2:E6：
| AssetID | Quantity | Title | Format | Palette |
|---|---|---|---|---|
| ARC-01 | 3 | Arc Study | Vector | Teal |
| TILE-02 | 5 | Tile Study | Raster | Amber |
| ARC-01 | 2 | Arc Study | Vector | Teal |
| GRID-03 | 4 | Grid Study | Vector | Slate |
| TILE-02 | 1 | Tile Study | Raster | Amber |
请注意，两条 ARC-01 条目都保留了下来，对应数量分别为 3 和 2。两条记录都获得 Arc Study、Vector 和 Teal。元数据丰富了这些条目，但没有将它们合并。
此公式有意逐个查找元数据字段，然后再合并结果。这样可以明确输出结构：五行库存数据、两列原始数据和三列新增数据。
在 Excel 2021 for Mac 中使用三个查找列
对于 Excel 2021，请直接在原库存旁操作。将匹配计数检查保留在第 C 列，然后在 D1:F1 中输入 Title、Format 和 Palette。将 D2:F6 留空。
在 Inventory!D2 中输入此公式：
=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!B2:B4,"MISSING METADATA",0)
在 Inventory!E2 中输入此公式：
=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!C2:C4,"MISSING METADATA",0)
在 Inventory!F2 中输入此公式：
=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!D2:D4,"MISSING METADATA",0)
每输入一个公式后按 Return。每个结果都会从起始单元格向下填充五行。资产 ID 和数量仍位于 A:B 列，诊断计数仍位于 C，新增的元数据位于 D:F。Excel 2021 for Mac 包含此方法所需的动态数组功能。Excel 2021 for Mac 的新增功能
将工作簿扩展到更多库存行
示例使用固定区域，以便清楚显示每个引用。库存增加时，请将每个库存引用都扩展到相同的末行。例如，如果库存到第 501 行结束，合并公式需要使用 Inventory!A2:B501，并将 Inventory!A2:A501 用于三个查找公式。匹配计数检查也要扩展到相同的行。
元数据增加时，请同时扩展所有元数据引用。如果其末行变为 101，请将 Metadata!A2:A101 用作键区域，并相应使用 B2:B101、C2:C101 和 D2:D101 作为返回区域。请包含实际记录，不要添加大量未使用的行。
如果工作簿会定期扩展，可以考虑将源区域转换为 Excel 表格，并使用结构化引用。Microsoft 的文档指出，添加或删除源行时，这些引用会自动调整。请将溢出输出放在表格外的工作表网格中。动态数组公式和溢出数组行为
如果需要在 Excel 表格内丰富数据，请为每个元数据列和库存行使用单独的单值公式。表格支持计算列，可将公式应用于整列；多单元格溢出区域应放在表格外。Excel 表格概述
诊断问题并核验最终结果
如果 Excel 显示 #SPILL!，请检查预期目标单元格。先移动你需要保留的现有内容，并确保公式位于 Excel 表格外。只有溢出结果左上角的单元格可编辑；请在该单元格中更改公式。Microsoft 文档说明了这些溢出边界和阻塞错误。动态数组公式和溢出数组行为
如果某行显示 MISSING METADATA，请查看该行的匹配计数检查。结果为零表示你需要添加缺少的元数据记录或更正资产 ID。如果视觉上相同的标签表现异常，请检查是否有意外的前导或尾随空格；Microsoft 指出，这些空格可能导致意外的 COUNTIF 结果。在 Microsoft Excel 中使用 COUNTIF 函数
在此设置中，请将两个源工作表保留在同一工作簿内。如果之后将源移到另一个工作簿，请注意 Microsoft 文档所述的限制：链接的动态数组要求两个工作簿都处于打开状态；源工作簿关闭时刷新可能会产生 #REF!。动态数组公式和溢出数组行为
使用以下三项具体检查核验合成示例：
- 按原库存顺序生成五行输出数据。
- 数量仍为
3、5、2、4、1，合计15。 - 重复 ID 获得相同的元数据，且每项匹配计数检查均为
1。
这些检查可以确认预期结果：每个库存条目在保留原有顺序和数量的同时，都获得与其资产 ID 对应的描述。对于更大的工作簿，在扩展区域或添加元数据记录后，请重复这些检查。
来源
- 关于 Office：我使用的是哪个版本的 Office？ — Microsoft 支持。
- XLOOKUP 函数 — Microsoft 支持。
- HSTACK 函数 — Microsoft 支持。
- Excel 2021 for Mac 的新增功能 — Microsoft 支持。
- 在 Microsoft Excel 中使用 COUNTIF 函数 — Microsoft 支持。
- 动态数组公式和溢出数组行为 — Microsoft 支持。
- Excel 表格概述 — Microsoft 支持。