英文:
Excel - group and sort by condition
问题
抱歉,你的请求有点模糊,可能需要更多的上下文来提供更精确的翻译。你是在问如何在Excel中找出一个组中有多少产品吗?如果是的话,你可以尝试使用COUNTIF函数来实现这一目标。如果有其他需要,请提供更多详细信息。
英文:
Hi
I have an inventory list with a lot of products/IDS etc
I want to find out how many products is in one group only.
In the picture above there will be products 455 and 700 - those are the only one that have one group only - the other products have multiple groups.
I am new to excel formulas and have tried countif and conditional formatting but I dont get the desired result.
Any suggestions?
I am using Excel 2016
/Henrik
答案1
得分: 1
使用Excel 2021及更高版本,使用UNIQUE
函数并添加条件 "only once":
=UNIQUE(A2:A9,,TRUE)
要计算数量,将其包装在COUNTA
函数中:
=COUNTA(UNIQUE(A2:A9,,TRUE))
结果:
英文:
With Excel 2021 and above use UNIQUE
with condition "only once":
=UNIQUE(A2:A9,,TRUE)
For count, wrap it in COUNTA
:
=COUNTA(UNIQUE(A2:A9,,TRUE))
Result:
答案2
得分: 1
可以在单元格`D1`中使用以下数组公式,一次性生成整个结果:
```excel
=LET(A, A2:A9,B,B2:B9,Aux,UNIQUE(A),Bux,UNIQUE(B),ones,SEQUENCE(ROWS(Bux),,1,0),
cnts,MMULT(COUNTIFS(A, Aux,B, TOROW(Bux)),ones),
f,FILTER(HSTACK(Aux, cnts),cnts=1), VSTACK({"产品","计数"}, f))
我们正在通过计数的数量进行筛选,但如果您移除筛选,可以获取所有产品的计数。
奖励
回应 @HenrikRosqvist 在 @user11222393 回答中的评论,查找属于特定组的产品。假设我们有一个以逗号(,
)分隔的组作为查找,放在单元格H2
中,您可以使用以下公式:
=LET(A, A2:A9,B,B2:B9, grs, TEXTSPLIT(H2,","),
UNIQUE(FILTER(A, ISNUMBER(XMATCH(B, grs)))))
<details>
<summary>英文:</summary>
You can use the following array formula in `D1` cell, that generates the entire result at once:
=LET(A, A2:A9,B,B2:B9,Aux,UNIQUE(A),Bux,UNIQUE(B),ones,SEQUENCE(ROWS(Bux),,1,0),
cnts,MMULT(COUNTIFS(A, Aux,B, TOROW(Bux)),ones),
f,FILTER(HSTACK(Aux, cnts),cnts=1), VSTACK({"Product","Counts"}, f))
Here is the output:
[![excel output1][1]][1]
We are filtering by the number of counts, but if you remove the filter you can get counts for all products.
## Bonus ##
Responding to @HenrikRosqvist comment in @user11222393's answer to find products that belong to specific group. Let's say we have as lookup the groups delimited by comma (`,`) in cell: `H2`, you can use the following formula:
=LET(A, A2:A9,B,B2:B9, grs, TEXTSPLIT(H2,","),
UNIQUE(FILTER(A, ISNUMBER(XMATCH(B, grs)))))
Here is the output:
[![excel output2][2]][2]
[1]: https://i.stack.imgur.com/uOl4q.png
[2]: https://i.stack.imgur.com/0LQXI.png
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论