为什么我的ArrayFormula会出错？我该怎么改正？(我不是在寻找另一个as公式作为解决方案！)

Question

I想在C1上得到一个ArrayFormula，给出所需的结果，如下图所示。

参赛表格：

(C栏是我所需的一栏)

输入的日期是指定名称的日期，即a，b，c，d，e，f。

条件：

1. 计数值纯粹基于输入的日期(如果约翰在最低日期(10-6月)被指定为a，则计数值为1，如果玫瑰在第二最低日期(17-6月)被指定为a，则计数值为2)。
2. 即使以任何方式对数据进行排序，计数的值也不会更改，因为输入的日期列值始终是永久的&不会更改。
3. 新的输入日期可以是任何日期，而不一定是最高日期(如果一个名为Rydu的新条目在9月-6月被指定为a，则它的计数值将变为1，然后john's (10-Jun)将变为2，依此类推)

示例：

在我按任意随机顺序对数据进行排序后，请这样说：

随机有序表：

(计数值保持不变)

​

​

当我在中间(第4排和第14行)和最后一行(第17排)之后做新条目时：

随机有序表：

(不管我在哪里做)

​

I已经得到了一个ArrayFormula，它提供了所需的结果：

={"AF Formula1"; ArrayFormula(IF(B2:B="", "", COUNTIFS(B$2:B, "="&B2:B, D$2:D, <"&D2:D)+1))}

我不是在寻找另一个as公式作为解决方案。我想知道我的ArrayFormula到底出了什么问题？我该怎么纠正呢？

我试图弄清楚我自己的ArrayFormula，但它不起作用：

我得到了每个细胞的方程式：

=RANK($D2,FILTER($D$2:$D, $B$2:$B=$B2),1)

我发现过滤器不适用于ArrayFormula，所以我不得不采取一种不同的方法。

我从以前的问题回答 (H3的at公式)那里得到了帮助，这是类似的，因为在这两种情况下，每个单元格过滤器公式返回的值都超过一个。(它实际上是由https://stackoverflow.com/users/5632629/player0回答的)

使用同样的技术，我想出了一个非常好的公式：

=RANK($D2, ARRAYFORMULA(TRANSPOSE(SPLIT(VLOOKUP($B2, SUBSTITUTE(TRIM(SPLIT(FLATTEN(QUERY(QUERY({$B:$B&"×", $D:$D}, "SELECT MAX(Col2) WHERE Col2 IS NOT NULL GROUP BY Col2 PIVOT Col1", 1),, 9^9)), "×")), " ", ","), 2, 0), ","))), 1)

现在，当我尝试将其转换为ArrayFormula：($D2 to $D2:$D & $B2 to $B2:$B)

=ARRAYFORMULA(RANK($D2:$D,TRANSPOSE(SPLIT(VLOOKUP($B2:$B, SUBSTITUTE(TRIM(SPLIT(FLATTEN(QUERY(QUERY({$B:$B&"×", $D:$D}, "SELECT MAX(Col2) WHERE Col2 IS NOT NULL GROUP BY Col2 PIVOT Col1", 1),, 9^9)), "×")), " ", ","), 2, 0), ",")), 1))

这给了我一个错误“在VLOOKUP评估中找不到值”，我发现只有当我将$B2更改为$B2:$B时，VLOOKUP才会出现问题。

​

​

我确信VLOOKUP与ArrayFormula一起工作，我不明白我的公式哪里出错了！请帮我改正我的ArrayFormula.

以下是可编辑的工作表链接

Answer 1

如果我正确理解，您正试图根据D列日期“排列”B列，使日期按理论升序排列，因此，如果您将数据集随机化，则每个条目的“排名”将保持不变，而不是根据您引入的随机性进行更改。

因此，正确的公式是：

={"fx"; INDEX(IFNA(VLOOKUP(B2:B&D2:D, 
 {INDEX(SORT({B2:B&D2:D, D2:D}, 2, 1),,1), 
 IFERROR(1/(1/COUNTIFS(
 INDEX(SORT(B2:D, 3, 1),,1), 
 INDEX(SORT(B2:D, 3, 1),,1), ROW(B2:B), "<="&ROW(B2:B))))}, 2, 0)))}

两个表的{"fx"; ...}数组(标题和实际表)在彼此下面，例如。;

外部较短的INDEX或更长的ARRAYFORMULA (不管哪一个)是需要的，因为我们正在处理一个数组

IFNA，用于在VLOOKUP函数找不到匹配时从VLOOKUP函数中删除可能的VLOOKUP错误。

我们在虚拟表VLOOKUP {}中联合B和D列B2:B&D2:D，如果有完全匹配的0，则返回第二个2列

我们的虚拟表{INDEX(SORT({B2:B&D2:D, D2:D}, 2, 1),,1), ...}，我们的VLOOKUP是由两列相邻的构造的，例如。,

我们是通过创建一个由2列组成的数组( {B2:B&D2:D, D2:D}相邻)来获得第一列的，在该数组中，我们按日期/第2列SORT (按日期/第2列2 )排列，但排序后所需要的只是第一列，因此我们使用INDEX，在这里我们将带所有行,,和第一列1。

现在让我们看看如何使用COUNTIFS获取虚拟表的第二列，它将模仿“排名”。

IFERROR(1/(1/用于从输出中移除所有零值(所有空行都将其中0作为“秩”)。

在COUNTIFS下，我们放置了2对参数："if列是qual to column“和”如果行更大或等于下一行增量1“，则为ROW(B2:B), "<="&ROW(B2:B))

对于“如果列是qual”，我们会这样做两次，并使用范围B2:D并按日期/第3列3按升序1对其进行排序，因此我们再次只需要第一列，因此我们将其INDEX并返回所有行,,和第一列1。

使用此公式，您可以添加、删除或随机化数据集，您将始终为您的每一行获得正确的值。

​

​

至于为什么你的公式不起作用。为了不获得vlookup的#N/A错误，您需要定义范围的最后一行，但是结果还是不会像您所期望的那样，因为这个公式并不适合这项工作。

​

​

如前所述，有些函数在AF下是不受支持的，比如SUM、AND、OR，还有一些函数可以工作，但是以不同的方式工作，比如IFS，或者有一些限制，比如SPLIT、GOOGLEFINANCE等等。

Answer 2

我已经在你们共同的工作表上回答了你们，这就是我的练习：

不能像在单元格CI2中尝试的那样拆分两列数组。这就是为什么你的公式不起作用。只能拆分一个列数组。

我知道你在努力学习，但是尝试使用像这样复杂的公式，恐怕会使它变得更难。