工作中经常需要对某个分类字段进行分组,再提取每组中对应的最大最小值。如果使用SQL,通过group by可以轻松实现,那么也可以通过Excel函数实现。
方法一:此函数WPS中有,office中没有此函数
需求:提取每个类别的最大最小值
(1)源数据如下,每个类别对应多个完成率
类别 | 完成率 |
a | 113% |
a | 166% |
a | 119% |
a | 121% |
a | 106% |
a | 99% |
b | 94% |
b | 90% |
b | 412% |
b | 0% |
b | 133% |
c | 127% |
c | 128% |
c | 44% |
c | 66% |
c | 7% |
c | 148% |
(2)实现:
通过Excel的minifs/maxifs函数,可以轻松实现
#最小值
=MINIFS($B$1:$B$18,$A$1:$A$18,$E3)
#最大值同理,只需将minifs更改为maxifs
(3)结果如下
类别 | 最小完成率 |
a | 99% |
b | 0% |
c | 7% |
方法二:
(1)源数据:
名称 | 值 |
a | 82 |
a | 97 |
a | 97 |
a | 95 |
a | 96 |
a | 85 |
b | 71 |
b | 86 |
b | 86 |
b | 79 |
c | 75 |
d | 64 |
d | 75 |
d | 72 |
(2)实现:
最大值:{=MAX(IF($D$2:$D$15=D2,$E$2:BE$15.""))}
最小值:{=MAX(IF($D$2:$D$15=D2,$E$2:BE$15.""))}
此方法主要是通过添加花括号{},进行分组,花括号的添加方式,双击写完公式的单元格,按Ctrl+shift+enter键,不可手动添加,手动添加,公式无法计算。
(3)结果
扩展:
同理计算其他聚合函数时,也可以使用,如:分组后求和sum、中位数median、平均值average替换相应的max或min即可。