如何按多列分组并执行差异运算并匹配差异中列的最近值

Question

我试图获得一个由 3 行分组组成的结果，并尝试用一列减去特定值，然后尝试从与特定列的差异最小中提取一个列值。例如：

Answer 1

总之，您可以：

1. 将您的范围转换为表格
2. 为包含“值”的单元格创建一个名称
3. 添加一列，从 C 列中减去值
4. 添加一列，比较 A 列和 B 列定义的每一组步骤 3 的最小值，并标记预期结果
5. 添加一列，如果标志为 TRUE，则返回 D 列的值
6. 将步骤 3、4 和 5 合并为一个公式:)

因此，Ctrl+T转换为表格：

为该值创建一个命名单元格：

添加一列来计算 C 和 the_value 之间的绝对差：

=ABS([@C]-the_value)

添加一列，将每组 D 的最小值与当前行 D 进行比较：

=MINIFS([Diff],[A],[@A],[B],[@B])=[@Diff]

添加一列，如果标志为 TRUE，则返回 D 列的值：

=IF([@[Min Diff in Group]],[@D],"")

将步骤 3、4 和 5 合并为一个公式：

=IF(MINIFS([Diff],[A],[@A],[B],[@B])=ABS([@C]-the_value),[@D],"")

编辑：如果您使用的是 2019 之前的 Excel 版本，则不会有 MINIFS。您可以使用数组公式实现相同的效果。在这种情况下，不使用 Excel 表格（步骤 1）。使用正常范围。

在这种情况下，上面提到的“组中最小差异”列将使用以下公式：

{=MIN(IF((Sheet1!$A$5:$A$10=Sheet1!$A5)*(Sheet1!$B$5:$B$10=Sheet1!$B5),Sheet1!$E$5:$E$10))=Sheet1!$E5}

请注意，使用普通公式时，您需要Enter在单元格中输入时按下 ，而使用数组公式时，您必须使用Ctrl+完成输入ShiftEnter。这就是您告诉早期版本的 Excel 您的公式包含数组或单元格区域的方式。

G 列的公式与上述公式相比保持不变。

“总计”列的公式将变为如下形式：

{=IF(MIN(IF((Sheet1!$A$5:$A$10=Sheet1!$A5)*(Sheet1!$B$5:$B$10=Sheet1!$B5),Sheet1!$E$5:$E$10))=Sheet1!$E5,Sheet1!$D5,"")}

再次，必须将其作为数组公式输入，使用Ctrl+ ShiftEnter。