Google表格Arrayformula for maxifs [英] Google Sheets Arrayformula for maxifs

问题描述

G'day人，

我是长期的支持者/读者，但这是我第一次在Stackoverflow上发帖，所以请耐心等待.

I am a long time supporter/reader but this is my first time posting on Stackoverflow so please bear with me.

我正在处理的工作表示例

=if(E2="",,If(maxifs($E:$E,$K:$K,K2)=E2,K2,""))

在转换上面的maxifs公式时，我有些沮丧，我试图将其转换为Google表格中的arrayformula.此公式将检查以确保E2(时间戳)不为空，然后进行比较，然后在时间戳列中查找用于唯一标识符的较新时间戳(列K示例:"43909Ben Johns"(参考编号和客户的CONCAT)) )，以查看该标识符是否有新版本.如果找不到它，它将使用同一行中K列中的相同标识符填充L列(公式所在的位置)中最新的行.如果找到较新的版本，它将填充".

I am a little stumped on converting this maxifs formula above that I am trying to convert to an arrayformula in Google Sheets. This formula checks to ensure E2 (timestamp) is not blank and then compares then looks all over the timestamp column for a newer timestamp for a distinct identifier (Column K example: '43909Ben Johns' (a CONCAT of a reference number and a client)) to see if there is a new version of that identifier. if it cannot find it, it will populate the row with the latest in column L (where the formula is situated) with the same identifier that is in Column K on the same row. If it does find a newer version it will populate with "".

我尝试设置一个arrayformula，但是我看到它在尝试转换时不起作用.它计算了第一行，但将所有其他行(约10,000个)留空，我怀疑这是因为它试图将E2和K2上的数据与E和K列中的其他行进行比较.下面是我尝试过的数组公式:

I tried setting up an arrayformula however I could see that it wouldn't work as I was trying to convert it. It calculated the first row but left all other rows (about 10,000) blank and I suspect that is because it is trying to compare data on E2 and K2 with other rows in column E and K. Below is the array formula I tried:

=arrayformula(if(E2:E="",,If(maxifs(E2:E,K2:K,K2:K)=E2:E,K2:K,"")))

任何帮助您解决此问题的方法将不胜感激，如果您需要我进一步解释，请告诉我.

Any help to untangle this would be appreciated and if you need me to explain further, please let me know.

添加了工作表示例 https://docs.google.com/spreadsheets/d/1k1qK2BuYOwDPCtHedg7zV72JmE3_TQYu9EBwZVogQbU/

亲切的问候， 本

推荐答案

以下是可用于您的公式，可以将其放在标头单元格(L1)中:

Here is a formula for you that can be placed in header cell (L1):

={
  "Valid Check (Most Recent)";
  ARRAYFORMULA(IF(E2:E = "",, IF(E2:E = VLOOKUP(K2:K, SORT({K2:K, E2:E}, 2, False), 2, 0), K2:K, "")))
}

这篇关于Google表格Arrayformula for maxifs的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！