英文:
How do I display the brand that has the biggest value, if it equals the unique identifier?
问题
表格是 A1:D11
如何找回每个唯一标识符的具有最高“数量”总和的“饮料”类别的“品牌”？
答案应为：
9929 = 柠檬水
5567 = 蔓越莓
英文:
Table is A1:D11
How do I bring back the "Brand" with the highest sum of "quantity" for the "drink" "category", for each unique identifier?
Answers should be:
9929 = Lemonade
5567 = Cranberry
答案1
得分: 1
以下是您要翻译的内容：
• Formula used in cell F2
=CHOOSECOLS(FILTER(A2:D11,
ISNUMBER(XMATCH(D2:D11,
MAXIFS(D2:D11,A2:A11,UNIQUE(A2:A11),B2:B11,"Drink")))),1,3)
Using REDUCE()
• Formula used in cell F1
=REDUCE({"Unique Identifier","Brand"},TAKE(UNIQUE(A2:B11),2,1),LAMBDA(x,y,
VSTACK(x,CHOOSECOLS(FILTER(A2:D11,ISNUMBER(XMATCH(D2:D11,
MAXIFS(D2:D11,A2:A11,y)))),1,3))))
英文:
You could try:
• Formula used in cell F2
=CHOOSECOLS(FILTER(A2:D11,
ISNUMBER(XMATCH(D2:D11,
MAXIFS(D2:D11,A2:A11,UNIQUE(A2:A11),B2:B11,"Drink")))),1,3)
Using REDUCE()
• Formula used in cell F1
=REDUCE({"Unique Identifier","Brand"},TAKE(UNIQUE(A2:B11),2,1),LAMBDA(x,y,
VSTACK(x,CHOOSECOLS(FILTER(A2:D11,ISNUMBER(XMATCH(D2:D11,
MAXIFS(D2:D11,A2:A11,y)))),1,3))))
答案2
得分: 1
Get Maximum Using FILTER and SORT
- 在
HSTACK(uc,bc,qc)中，您可以选择要返回哪些列。 - 您可以使用单元格引用而不是硬编码的
"Drink"。该单元格可以具有下拉列表。
=LET(data,A2:D20,uc,1,cc,2,bc,3,qc,4,cr,"Drink",
d,SORT(FILTER(data,INDEX(data,,cc)=cr),qc,-1),
du,INDEX(d,,uc),u,UNIQUE(du),
INDEX(d,XMATCH(u,du),HSTACK(uc,bc,qc)))
英文:
Get Maximum Using FILTER and SORT
- In
HSTACK(uc,bc,qc)you can choose which columns to return. - Instead of the hard-coded
"Drink"you could use a cell reference. The cell could have a dropdown.
=LET(data,A2:D20,uc,1,cc,2,bc,3,qc,4,cr,"Drink",
d,SORT(FILTER(data,INDEX(data,,cc)=cr),qc,-1),
du,INDEX(d,,uc),u,UNIQUE(du),
INDEX(d,XMATCH(u,du),HSTACK(uc,bc,qc)))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论