尝试理解 INDEX；MATCH；MAX

Question 1

希望您拥有 Office 365！

=LET(x, FILTER(F9:I9,(F$5:I$5="A")*(F9:I9) <> 0,""), INDEX(x, COLUMNS(x)))

解释：

(F5:I5="A")*(F9:I9) 给出整行四，但在没有包含“A”的列中将其归零。

接下来，我们对所有非零列进行过滤。过滤后的数组中的最后一项就是您想要的值。为了获取最后一项，我们使用 LET 将整个数组“保存”为 x，然后 COLUMNS(x) 是最后一项的索引，INDEX(x, COLUMNS(x)) 返回该项。

扩展以达到日期

=LET(x, FILTER(F4:Z9,(F5:Z5="A")*(F9:Z9) <> 0,""), INDEX(x, 1, COLUMNS(x)))

基本上，我们扩展了 FILTER 数组以包含第 4 行，现在将返回一个 2D 数组。索引到第 1 行以获取日期。

Boss 版本

=LET(x, FILTER(F4:Z9,(F5:Z5="A")*(F9:Z9) <> 0,""), INDEX(x, {6,1}, COLUMNS(x)))

老板版本非常高效，将值和日期作为单个数组返回。

进一步扩展以包括查找 A 列

=LET(y, MATCH(A9,E6:E9,0), x, FILTER(F4:K9,((F5:K5="A")*INDEX(F4:K9,y+2,)) <> 0,""), INDEX(x, y+2, COLUMNS(x)))

以及日期

=LET(y, MATCH(A9,E6:E9,0), x, FILTER(F4:K9,((F5:K5="A")*INDEX(F4:K9,y+2,)) <> 0,""), INDEX(x, 1, COLUMNS(x)))

不幸的是，没有“boss”版本，因为 {y+1, 1} 不是静态数组。Excel 中的数组必须仅包含常量。

Answer

希望您拥有 Office 365！

=LET(x, FILTER(F9:I9,(F$5:I$5="A")*(F9:I9) <> 0,""), INDEX(x, COLUMNS(x)))

解释：

(F5:I5="A")*(F9:I9) 给出整行四，但在没有包含“A”的列中将其归零。

接下来，我们对所有非零列进行过滤。过滤后的数组中的最后一项就是您想要的值。为了获取最后一项，我们使用 LET 将整个数组“保存”为 x，然后 COLUMNS(x) 是最后一项的索引，INDEX(x, COLUMNS(x)) 返回该项。

扩展以达到日期

=LET(x, FILTER(F4:Z9,(F5:Z5="A")*(F9:Z9) <> 0,""), INDEX(x, 1, COLUMNS(x)))

基本上，我们扩展了 FILTER 数组以包含第 4 行，现在将返回一个 2D 数组。索引到第 1 行以获取日期。

Boss 版本

=LET(x, FILTER(F4:Z9,(F5:Z5="A")*(F9:Z9) <> 0,""), INDEX(x, {6,1}, COLUMNS(x)))

老板版本非常高效，将值和日期作为单个数组返回。

进一步扩展以包括查找 A 列

=LET(y, MATCH(A9,E6:E9,0), x, FILTER(F4:K9,((F5:K5="A")*INDEX(F4:K9,y+2,)) <> 0,""), INDEX(x, y+2, COLUMNS(x)))

以及日期

=LET(y, MATCH(A9,E6:E9,0), x, FILTER(F4:K9,((F5:K5="A")*INDEX(F4:K9,y+2,)) <> 0,""), INDEX(x, 1, COLUMNS(x)))

不幸的是，没有“boss”版本，因为 {y+1, 1} 不是静态数组。Excel 中的数组必须仅包含常量。

Question 2

我使用该LET()函数来定义变量名，这样我就可以更轻松地跟踪正在发生的事情。

=LET(dateRow,$F$4:$I$4,letter,B$4,letterRow,$F$5:$I$5,number,$A6,numberColumn,$E$6:$E$9,dataRow,OFFSET(letterRow,MATCH(number,numberColumn,0),0),dataColumn,MATCH(MAX(dateRow*(letterRow=letter)*(dataRow<>0))&letter,dateRow&letterRow,0),IFERROR(INDEX(dataRow,1,dataColumn),""))

我知道这看起来很庞大，但很容易分解。首先，我们定义几个变量，以便以后可以通过名称引用它们。您不必这样做，可以直接在公式的后面部分引用范围，但它可以帮助我了解正在发生的事情。

dateRow,$F$4:$I$4
letter,B$4
letterRow,$F$5:$I$5
number,$A6
numberColumn,$E$6:$E$9

接下来，我们将找到数据中哪一行的第一列有正确的数字（一、二、三、四）。

dataRow,OFFSET(letterRow,MATCH(number,numberColumn,0),0)

然后我们需要我们想要的列。这有点复杂，而且这是您当前公式不适合您的地方。这里的关键是在函数中添加更多条件MAX()。
dateRow*(letterRow=letter)*(dataRow<>0)
相当于
IF(AND(letterRow=letter,dataRow<>0),dateRow,0)。

有了这样的理解，我们可以看一下这部分的简短版本：
dataColumn,MATCH(MAX(~)&letter,dateRow&letterRow,0)

我们连接letter和，letterRow以便在日期出现多次且下方字母不同的情况下找到正确的列。现在我们知道数据在哪一行 ( dataRow) 和哪一列 ( dataColumn)，我们可以提取所需的值：
IFERROR(INDEX(dataRow,1,dataColumn),"")

我们将其包装在里面IFERROR()以覆盖未找到值的情况。例如，如果您想要 TWO-A 的值，它会返回错误，因为 TWO-A 没有任何非空值。

对于列中的日期公式C，除一个字母外，其余均相同，因为我们想从而dateRow不是中提取值dataRow：

=LET(dateRow,$F$4:$I$4,letter,B$4,letterRow,$F$5:$I$5,number,$A6,numberColumn,$E$6:$E$9,dataRow,OFFSET(letterRow,MATCH(number,numberColumn,0),0),dataColumn,MATCH(MAX(dateRow*(letterRow=letter)*(dataRow<>0))&letter,dateRow&letterRow,0),IFERROR(INDEX(datRow,1,dataColumn),""))

Answer