Excel – 按条件分组和排序

huangapple go评论69阅读模式
英文:

Excel - group and sort by condition

问题

抱歉,你的请求有点模糊,可能需要更多的上下文来提供更精确的翻译。你是在问如何在Excel中找出一个组中有多少产品吗?如果是的话,你可以尝试使用COUNTIF函数来实现这一目标。如果有其他需要,请提供更多详细信息。

英文:

Excel – 按条件分组和排序

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

结果:

Excel – 按条件分组和排序

英文:

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:

Excel – 按条件分组和排序

答案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))

这是输出:
Excel – 按条件分组和排序

我们正在通过计数的数量进行筛选,但如果您移除筛选,可以获取所有产品的计数。

奖励

回应 @HenrikRosqvist 在 @user11222393 回答中的评论,查找属于特定组的产品。假设我们有一个以逗号(,)分隔的组作为查找,放在单元格H2中,您可以使用以下公式:

=LET(A, A2:A9,B,B2:B9, grs, TEXTSPLIT(H2,","),
 UNIQUE(FILTER(A, ISNUMBER(XMATCH(B, grs)))))

这是输出:
Excel – 按条件分组和排序


<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&#39;s answer to find products that belong to specific group. Let&#39;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>



huangapple
  • 本文由 发表于 2023年3月1日 15:26:20
  • 转载请务必保留本文链接:https://go.coder-hub.com/75600637.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定