Excel - 根据相邻单元格值选择单元格

Question

我有以下 Excel 电子表格，我正在尝试弄清楚如何编写公式以提供 D 列中的值。

每一行都有一个测试日期，我试图计算每个测试日期到主要日期的天差，具体到每个科目。

我认为这将涉及根据 C 列中的值选择主要测试日期，但我不确定如何做到这一点。

如有任何帮助，我们将不胜感激。

A            B           C                 D
Subject      TestDate    Principal date    Day difference from Principal  date
Subject 1    01/12/2014                    -3
Subject 1    02/12/2014                    -2
Subject 1    03/12/2014                    -1 
Subject 1    04/12/2014   Yes               0
Subject 2    07/12/2014                    -1
Subject 2    08/12/2014   Yes               0
Subject 2    11/12/2014                     3
Subject 3    17/12/2014                    -1
Subject 3    18/12/2014   Yes               0
Subject 3    24/12/2014                     6 

Answer 1

这里的逻辑是:(1)找到每个主题的主要日期的日期，并为每一行返回它； (2) 从 col B 中的当前日期减去该日期。 (2) 很简单，但 (1) 需要一种方法来匹配 Subject 和 Principal Date 上的 B 中的值。您可以使用具有多个 MATCH 标准的 INDEX-MATCH 函数来完成此操作。

根据 A2:C11 中的数据和 第 1 行 中的列标题，在 D2 中输入此公式并填写:

{=B2-INDEX($A$2:$C$11,MATCH(1,($A$2:$A$11=$A2)*($C$2:$C$11="是") ,0), 2)}

请注意，您需要使用 CtrlShiftEnter 将其输入为数组公式。

<小时/>

INDEX-MATCH 函数背后的逻辑是:

• A2:C11 是您的整个原始数据表；该函数查看整个表。
  • 请注意，如果需要，您可以包含标题，这对于通过匹配的列标题名称定义查找列很有用。如果这样做，您需要确保所有数组具有相同的维度(即，如果您的数据表为 A1:C11，则 MATCH 函数也需要从第 1 行 开始)。
• MATCH 函数从多个逻辑条件相乘提供的查找数组中查找 1 值。它将评估是否 A2=A2、A3=A2、A4=A2 等，并创建一列 TRUE/FALSE值。然后，它会对 C2="Yes"、C3="Yes" 等执行相同操作。逻辑数组的乘积将为 1 > 任何时候两个条件都满足。 (0 告诉 MATCH 寻找完全匹配。)
• 2 告诉 INDEX 函数查找行中第二列 (B) 中的值由 MATCH 函数指定 - 即，其中两个条件都满足。

然后从 B2 中的值减去该值即可得到日期差。

<小时/>

正如评论中所指出的，这个公式也可以简化为仅索引所需的查找列:

=B2-INDEX($B$2:$B$11,MATCH(1,($A$2:$A$11=$A2)*($C$2:$C$11="是"), 0))