☰

Excel 根据多个条件匹配，包括基于特定条件的动态范围

tag-icon • tag-icon microsoft-excel vlookup
Excel 根据多个条件匹配，包括基于特定条件的动态范围

我正在尝试找到以下最佳公式。我需要引入一个值（我想要的百分比数）基于 3 个标准：前两个是群组查找和姓名查询。第三个条件是数量。这个第三个条件需要首先匹配组名和名称，然后引用该 Quantity_Min 列。它需要从数据选项卡比较数量，并找到 Quantity_Min 不大于数量但小于或等于数量的行。

例如，数据选项卡中的第一个记录有 Europe、Joe、1800。我需要一个公式来搜索查找选项卡以查找 Joe 和 Europe 的匹配项（有 3 条记录），然后使用数量 1800 将搜索范围缩小到一条记录。Joe、Europe 组合有 3 个 Quantity_Min 值：0、500、1000。由于 1800 大于所有值，因此我想要 1000 条记录，因为这是最小值（**如果数量是 502，我想要 500 条记录）。这对应于字段我想要的百分比数其值为 25.3%。这是我需要的最终数字。我已在列中手动添加了正确的百分比手动输入正确的百分比用于数据验证目的。

我可以使用索引匹配来匹配两个名称条件，但数量条件却让我困惑。此外，我可能会以不同的方式构造数据，但这就是它目前存在的方式。

数据表

查找表

答案1

尝试一下百分比列中的数据表格并填写。

=INDEX(Lookup!D:D, AGGREGATE(15, 7, ROW($2:$99)/((Lookup!A$2:A$99=A2)*(Lookup!B$2:B$99=B2)*(Lookup!C$2:C$99<=C2)), 1))

在此处输入图片描述

答案2

在此处输入图片描述

  • 单元格中的数组（CSE）公式R2：

    {=IF(MIN(IF(Lookup!J$2:J$20=O2,IF(Lookup!K$2:K$20=P2,IF(Lookup!L$2:L$20<=Q2,Lookup!M$2:M$20))))=0,"",(MIN(IF(Lookup!J$2:J$20=O2,IF(Lookup2!K$2:K$20=P2,IF(Lookup!L$2:L$20<=Q2,Lookup!M$2:M$20))))))}

  • 完成配方Ctrl+Shift+Enter& 向下填充。

  • 根据需要调整公式中的单元格引用。

相关内容