英文:
Google Sheets - Using MAXIFS within ArrayFormula
问题
我联系你是因为我在Google表格中遇到了一个问题，我找不到解决办法，我确信这里的某人可能有答案可以帮助我。
情况实际上很简单：
- 在我的表格的D列，我有一个电子邮件地址列表；
- 在H列，对于这些地址中的每一个，我有一些订单数量；
- 在I列，我试图得到，在D列中指定的电子邮件地址，H列中指定的订单数量的最大值。
当然，这可以通过MAXIFS()公式非常简单地完成，如下所示：
=maxifs(H:H;D:D;DX)
其中X是行号。
现在，当我尝试将此公式转换为"ArrayFormula"时出现问题。
当尝试转换时，我尝试输入：
=arrayformula(maxifs(H:H;D:D;D:D))
但它不会“扩展”到以下行。该公式适用于键入的行，但我无法让它“复制”。
这里有人有任何解决这个问题的想法吗？
祝大家一周愉快！
英文:
I'm reaching out because I'm facing an issue in Google Sheets for which I can't find a solution and I'm certain someone here might have an answer to save me.
The situation is actually quite simple :
- In the D column of my sheet, I have a list of email addresses ;
- In the H column, for each of these addresses, I have a number of orders ;
- In the I column, I am trying to get, for the email address indicated in the D column, the maximum number of orders such as indicated in the H column.
See a screenshot of the file here
Of course, this can really simply be done with the MAXIFS() formula such as follows :
=maxifs(H:H;D:D;DX)
where X is the n° of the lign.
Now, the issue appears when I try to transform this formula to an "ArrayFormula".
When trying to transform it, I tried typing :
=arrayformula(maxifs(H:H;D:D;D:D))
But it doesn't "expand" to the following lines.
The formula works for the line in which it is typed, but I can't get it to "replicate".
Does anyone here have an idea of how to solve this issue?
I wish you all a very pleasant week!
答案1
得分: 1
=map(D3:D,lambda(z,if(z="",,vlookup(z,sort({D3:D,H3:H},2,0),2,))))
英文:
You may try:
=map(D3:D,lambda(z,if(z="",,vlookup(z,sort({D3:D,H3:H},2,0),2,))))
答案2
得分: 1
使用MAXIFS()和BYROW()使其动态。尝试以下公式：
=BYROW(A3:A9,LAMBDA(x,MAXIFS(B3:B,A3:A,x)))
要将整列作为输入参数，请使用以下公式：
=BYROW(A3:INDEX(A3:A,COUNTA(A3:A)),LAMBDA(x,MAXIFS(B3:B,A3:A,x)))
英文:
Use MAXIFS() with BYROW() to make it dynamic. Try-
=BYROW(A3:A9,LAMBDA(x,MAXIFS(B3:B,A3:A,x)))
To refer full column as input parameter, use-
=BYROW(A3:INDEX(A3:A,COUNTA(A3:A)),LAMBDA(x,MAXIFS(B3:B,A3:A,x)))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论