为什么我的 ArrayFormula 会出错?我该如何纠正?(我不是在寻找另一个 Arrayformula 作为解决方案！) [英] Why my ArrayFormula is giving error? How do I correct it? (I'm not looking for another Arrayformula as solutions!)

问题描述

我想要 C1 处的 ArrayFormula，它可以给出如图所示的所需结果.

报名表:
(C列是我需要的列)

输入日期是将名称分配给一组的日期，即 a、b、c、d、e、f

标准:

1. count 的值完全基于输入的日期(如果 john 在最低日期(10-Jun)被分配 a，则计数值为 1，如果 Rose 被分配 a 在倒数第二个日期(6 月 17 日)然后计数值为 2.
2. 即使以任何方式对数据进行排序，count 的值也不会改变，因为 Date Entered 列值始终是永久的 &不会改变.
3. 新条目日期可以是不一定是最高日期的任何日期(如果一个名为 Rydu 的新条目在 9-Jun 被分配a，那么它的计数值将变为 1，那么 john's (10-Jun) 将变成 2 以此类推)

示例:

在我以任何随机顺序对数据进行排序后，像这样说:

随机排序表:
(计数值保持不变)

当我在(第 4 行和第 14 行)和最后一行(第 17 行)之间输入新条目时:

随机排序表:
(不管我在哪里)

---

我已经得到了一个 ArrayFormula，它给出了所需的结果:

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

---

我不是在寻找另一个 Arrayformula 作为解决方案.我想知道我的 ArrayFormula 有什么问题?我该如何纠正?

我试图找出我自己的 ArrayFormula 但它不起作用:

我得到了每个单元格的公式:
=RANK($D2,FILTER($D$2:$D, $B$2:$B=$B2),1)
我发现 Filter 不适用于 ArrayFormula，所以我不得不采取不同的方法.

我从之前的

我确定 VLOOKUP 可以与 ArrayFormula 一起使用，但我不明白我的公式哪里出错了！请帮我更正我的 ArrayFormula.

---

至于为什么你的公式不起作用......为了不让 vlookup 出现 #N/A 错误，你需要定义范围的结束行，但结果仍然不会像你期望的那样，因为公式不是适合这份工作.

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

I wanted a ArrayFormula at C1 which gives the required result as shown.

Entry sheet:
(Column C is my required column)

Date Entered is the date when the Name is Assigned a group i.e. a, b, c, d, e, f

Criteria:

1. The value of count is purely on basis of Date Entered (if john is assigned a on lowest date(10-Jun) then count value is 1, if rose is assigned a on 2nd lowest date(17-Jun) then count value is 2).
2. The value of count does not change even when the data is sorted in any manner because Date Entered column values is always permanent & does not change.
3. New entry date could be any date not necessarily highest date (If a new entry with name Rydu is assigned a on 9-Jun then the it's count value will become 1, then john's (10-Jun) will become 2 and so on)

Example:

After I sort the data in any random order say like this:

Random ordered sheet:
(Count value remains permanent)

And when I do New entries in between (Row 4th & 14th) and after last row (Row 17th):

Random Ordered sheet:
(Doesn't matter where I do)

---

I already got a ArrayFormula which gives the required result:

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

---

I'm not looking for another Arrayformula as solutions. What I want is to know what is wrong in my ArrayFormula? and how do I correct it?

I tried to figure my own ArrayFormula but it's not working:

I got Formula for each cell:
=RANK($D2,FILTER($D$2:$D, $B$2:$B=$B2),1)
I figured out Filter doesn't work with ArrayFormula so I had to take a different approach.

I took help from my previous question answer (Arrayformula at H3) which was similar since in both cases each cell FILTER formula returns more than 1 value. (It was actually answered by player0)

Using the same technique I came up with this Formula which works absolutely fine :

=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)

Now when I tried converting it to 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))

It gives me an error "Did not find value '' in VLOOKUP evaluation", I figured out that the problem is only in VLOOKUP when I change $B2 to $B2:$B.

I'm sure VLOOKUP works with ArrayFormula, I fail to understand where my formula is going wrong! Please help me correct my ArrayFormula.

Here is the editable sheet link

解决方案

if I understand correctly, you are trying to "rank" B column based on D column dates in such way that dates are in theoretical ascending order so if you randomize your dataset, the "rank" of each entry would stay same and not change based on the randomness you introduce.

therefore the correct formula would be:

={"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"; ...} array of 2 tables (header & actual table) under each other eg. ;

---

outer shorter INDEX or longer ARRAYFORMULA (doesnt matter which one) is needed coz we are processing an array

---

IFNA for removing possible #N/A errors from VLOOKUP function when VLOOKUP fails to find a match

---

we VLOOKUP joint B and D column B2:B&D2:D in our virtual table {} and returning second 2 column if there is an exact match 0

---

our virtual table {INDEX(SORT({B2:B&D2:D, D2:D}, 2, 1),,1), ...} we VLOOKUP from is constructed with 2 columns next to each other eg. ,

---

we are getting the first column by creating an array of 2 columns {B2:B&D2:D, D2:D} next to each other where we SORT this array by date/2nd column 2, in ascending order 1 but all we need after sorting is the 1st column so we use INDEX where we bring all rows ,, and the first column 1

---

now lets take a look on how we getting the 2nd column of our virtual table by using COUNTIFS which will mimic the "rank"

---

IFERROR(1/(1/ is used to remove all zero values from the output (all empty rows would have 0 in it as the "rank")

---

under COUNTIFS we put 2 pairs of arguments: "if column is qual to column" and "if row is larger or equal to next row increment it by 1" ROW(B2:B), "<="&ROW(B2:B))

---

for "if column is qual to column" we do this twice and use range B2:D and sort it by date/3rd column 3 in ascending order 1 and of this we again need only the 1st column so we INDEX it and return all rows ,, and first column 1

with this formula you can add, remove or randomize your dataset and you will always get the right value for each of your rows

---

as for why your formula doesnt work... to not get #N/A error for vlookup you would need to define the end row of the range but still, the result wont be as you would expect coz formula is not the right one for this job.

as mentioned there are functions that are not supported under AF like SUM,AND,OR and then there are also functions which work but in a different way like IFS or with some limitations like SPLIT,GOOGLEFINANCE,etc.

这篇关于为什么我的 ArrayFormula 会出错?我该如何纠正?(我不是在寻找另一个 Arrayformula 作为解决方案！)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！