使用 FILTER 筛选出完全匹配的美术资源类别，然后使用 XLOOKUP 返回不超过当前数量的最大阈值所对应的等级。本指南将通过合成标签和边界测试，构建一个完整的 Excel for Mac 示例。

哪些 Excel for Mac 版本支持此公式？

下面的公式使用 LET、FILTER 和 XLOOKUP。Microsoft 文档列出了这三个函数在 Excel for Microsoft 365 for Mac、Excel 2024 for Mac 和 Excel 2021 for Mac 中的支持情况。Microsoft 还在 Excel 2021 for Mac 的新增功能概述中介绍了这些函数。

Excel 2016 和 Excel 2019 无法计算 XLOOKUP；Microsoft 明确记录了这一限制。要使用此特定公式，请使用上述任一 Mac 版本。在较旧版本中打开包含该函数的工作簿，并不会因此获得对该函数的支持。请参阅 Microsoft 的 XLOOKUP 文档。

如果不确定安装的是哪个版本，请在应用程序的“关于”对话框中查看版本和许可证类型。Microsoft 在关于 Office：我使用的是哪个版本的 Office？中说明了如何查看这些详细信息。

设置按类别划分的最低数量

假设你要记录可重复使用的图形资源数量，并希望为每个类别指定描述性的库存等级。这项任务有两条规则：类别必须匹配，数量必须达到某个等级的最低值。以下所有标签和数量均为示例。

在空白工作表的 A1:C7 中输入此表。标题位于第 1 行，六条数据位于第 2 至第 7 行。最低数量应输入为数字，并填写每个类别和等级单元格。

类别 最低数量 等级标签
纹理平铺 5 精简
纹理平铺 20 扩展
纹理平铺 50 丰富
形状印章 5 精简
形状印章 30 扩展
形状印章 80 丰富

接下来，在 E1 中输入“待检查类别”，在 F1 中输入“数量”，在 G1 中输入“库存等级”。在 E2 中输入 纹理平铺，并在 F2 中输入数字 34。将 D 列留空，以分隔参考数据和输入内容。

将最低数量视为包含边界值的下限。对于纹理平铺，精简从 5 开始，扩展从 20 开始，丰富从 50 开始。因此，数量为 34 时属于扩展。对于形状印章，相同标签对应的起始数量不同：数量为 20 时仍属于精简。

编写公式前，请先确定你自己的边界表示最低值还是最高值。本示例始终使用最低值。低于 5 的数量不属于任何等级，而最后一个等级适用于所有高于其起始数量的数量。这些是本示例明确采用的设计选择；如果你的规则不同，请相应调整参考数据。

输入完整的类别和数量公式

选择 G2，将以下完整公式粘贴到公式栏，然后按 Return。该公式使用上述确切的工作表布局；不需要命名区域或辅助列。

=LET(
  category,$E2,
  quantity,$F2,
  categories,$A$2:$A$7,
  minimums,$B$2:$B$7,
  labels,$C$2:$C$7,
  keep,categories=category,
  IF(
    OR(category="",NOT(ISNUMBER(quantity))),
    "Enter category and numeric quantity",
    IF(
      quantity<0,
      "Use zero or more",
      IF(
        SUM(--keep)=0,
        "Category not found",
        XLOOKUP(
          quantity,
          FILTER(minimums,keep),
          FILTER(labels,keep),
          "Below first tier",
          -1
        )
      )
    )
  )
)

对于 纹理平铺 和 34，预期结果为 扩展。尽管中间计算使用了数组，公式仍只返回一个标签。现代动态数组公式可直接输入；Microsoft 在动态数组公式和溢出数组行为中说明，按 Enter 即可确认公式。

类别筛选如何保持数组对应

表达式 categories=category 会创建名为 keep 的包含数组。对于此示例输入，它会选中三条纹理平铺数据。两个 FILTER 调用使用同一个数组，分别返回最低数量 5、20 和 50，以及与之对应的精简、扩展和丰富标签。

Microsoft 将 FILTER 说明为：使用维度与源区域相匹配的布尔包含数组来选择数据。对两列使用同一个包含数组，可以保持两列数据之间的对应关系。请参阅 FILTER 函数。

外层的 LET 为源区域、输入内容和包含数组指定易读的名称。这些名称仅在此公式内有效；无需在工作簿中单独创建。Microsoft 在其 LET 函数文档中说明了这一行为。

数量如何决定等级

第五个 XLOOKUP 参数 -1 指定精确匹配数量，或匹配下一个较小的值。在筛选后的纹理平铺数据中，34 会匹配最低数量 20，并返回扩展。数量正好为 50 时，则会匹配丰富。Microsoft 在 XLOOKUP 函数文档中定义了这些匹配模式。

公式还会区分未知类别和低于首个等级的数量。SUM(--keep) 会统计匹配的类别行数。如果没有匹配项，公式会在尝试筛选查找之前返回“未找到类别”。如果类别存在，但其所有最低数量都超过当前数量，查找结果则为“低于首个等级”。

测试边界和类别隔离

每次将 E2 和 F2 替换为以下一组类别与数量。这些检查比只测试一个中间值更有用：它们涵盖首个阈值、精确边界、类别差异和缺失类别。

E2 中的类别 F2 中的数量 预期结果
纹理平铺 0 低于首个等级
纹理平铺 5 精简
纹理平铺 19 精简
纹理平铺 20 扩展
纹理平铺 34 扩展
纹理平铺 50 丰富
形状印章 20 精简
形状印章 34 扩展
形状印章 100 丰富
色板 34 未找到类别

两个数量为 20 的测试尤其能说明问题。纹理平铺必须返回扩展，而形状印章必须返回精简。如果两者都返回扩展，请检查数量查找是否使用了筛选后的类别数据。

这些预期结果共十项，已通过一个实现所述最低阈值规则的简短 Python 计算进行核对。Excel 公式本身已对照 Microsoft 记录的语法进行检查，但未在 Excel 中运行。

还要检查输入提示：类别为空或数量以文本形式输入时，应返回 Enter category and numeric quantity；负数数量应返回 Use zero or more。资源数量请使用整数。公式会检查数量是否为数字且非负，但不会强制要求输入整数。

诊断意外结果并扩展工作表

按顺序进行以下检查，避免格式问题导致等级规则被改动。

  1. 检查类别文本。 将输入内容与参考标签进行比较，包括空格。Microsoft 在其查找问题排查指南中指出，意外字符和隐藏空格是常见的查找问题。
  2. 检查数值单元格。 数量输入和最低数量应为数字，而不是看起来像数字的文本。Microsoft 的同一指南还指出，数据类型不一致也会造成查找问题。
  3. 检查区域是否对齐。 类别、最低数量和标签区域必须覆盖相同的数据行。在此示例中，每个区域都从第 2 行开始，到第 7 行结束。
  4. 检查重复规则。 每个类别和最低数量组合都应对应一个等级标签。如果两行将不同标签分配给同一边界值，请在参考数据中消除这一歧义。
  5. 检查边界约定。 如果结果看起来高出或低出一个等级，可能是因为你的参考值表示最高值，而此公式要求最低值。

如需直观检查，请在未使用的单元格（例如 I2）中输入以下诊断公式。当 E2 中为 纹理平铺 时，公式应显示完整的三条纹理平铺数据。此公式是单独的诊断公式，不能替代 G2。

=FILTER($A$2:$C$7,$A$2:$A$7=$E2,"Category not found")

请为诊断结果预留足够空间，使其能向右扩展三列、向下扩展三行。独立数组结果会溢出到相邻单元格；如果这些单元格已被占用，可能会出现 #SPILL!。Microsoft 在动态数组公式和溢出数组行为中对此进行了说明。

添加更多参考数据行时，请在主公式中一并扩展三个源区域。例如，如果最后一行数据变为第 10 行，请将定义改为 categories,$A$2:$A$10、minimums,$B$2:$B$10 和 labels,$C$2:$C$10。然后对新增类别重复边界测试。

如需处理多行输入，请在 E2:F2 下方输入更多类别和数量对，然后向下复制 G2。源区域引用包含美元符号，因此会保持固定；输入行号则会递增。请明确设置各类别的边界，并使用成对的类别测试，确认每个返回标签都符合预期的库存规则。

来源