函数法在Excel中实现按条件分类排序

电脑技巧 7个月前 muruoxi
1,016 0

在上一节课中,我们学习了一种小白也能学会的按条件分类排序的操作方法。

函数法在Excel中实现按条件分类排序

但有朋友觉得这种方法太麻烦了,尤其是数据量大的情况下,有没有更简单的方法能完成这个操作呢?

答案是有的。

需求分析

对A列同款号的货进行分类,然后对每一类货的库存,都按从大到小进行降序排列,排好的顺序填入F列,最后要求顺序不能打乱。

实际上这就是一道如何在Excel中实现按条件分类排序的问题。

函数法

本节课我们介绍函数法,适用于Excel基本功扎实的同学,运用在数据量较大的情况下。

案例文件

https://muruoxi.lanzouq.com/iMwd7zz5nle

认识SUMPRODUCT函数

SUMPRODUCT函数是Excel中的一个重要函数,学好它能给你的工作带来极大的便利。

它的语法为:

=SUMPRODUCT (array1, [array2], [array3], ...)

Excel中对它的解释为:返回相应范围或数组的乘积之和

函数法在Excel中实现按条件分类排序

如上图,要计算A列和B列的乘积之和,一般来说需要我们先计算A*B的积,然后使用SUM函数求和。

但如果你会使用SUMPRODUCT函数,你只需要一行公式。

函数法在Excel中实现按条件分类排序

这是Excel中SUMPRODUCT函数的基础用法,实际上SUMPRODUCT函数的真正强大之处并不在于默认的函数解释,而在于它的隐藏用法。

这些用法包括但不限制于:

  1. 单条件求和

  2. 多条件求和

  3. 单条件计数

  4. 多条件计数

  5. 单条件排序

  6. 多条件排序

  7. 单条件权重统计

  8. 多条件权重统计

  9. 执行其它算数运算后求和

本文中我将介绍多条件排序这个用法。

傻瓜用法

=SUMPRODUCT(--((条件1)*(条件2)*(条件3)))

比如本题中,可以使用公式:

=SUMPRODUCT(--(($A$2:$A$36=A2)*($D$2:$D$36>=D2)))

效果如下:

函数法在Excel中实现按条件分类排序

函数运用原理

先看我们的最终公式:

=SUMPRODUCT(--(($A$2:$A$36=A2)*($D$2:$D$36>=D2)))

咳咳咳,小问号,你是不是有很多朋友?

我们慢慢来解释这个公式。

先去掉绝对引用符号$,公式变成:

=SUMPRODUCT(--((A2:A36=A2)*(D2:D36>=D2)))

然后去掉--这个特殊用法,我们等会儿解释它。

=SUMPRODUCT((A2:A36=A2)*(D2:D36>=D2))

现在公式看起来只有一个参数,值为(A2:A36=A2)*(D2:D36>=D2)

实际上这也是SUMPRODUCT的隐藏用法⑨,即执行其它算数运算后求和

Excel对其的解释为:

执行其他算术运算

像往常一样使用 SUMPRODUCT,但请将分隔数组参数的逗号替换为所需的算术运算符 (*、/、+、-) 。 执行所有操作后,结果将像往常一样进行求和。

注意: 如果使用算术运算符,请考虑将数组参数括在括号中,并使用括号对数组参数进行分组以控制算术运算的顺序。

因此它的函数原型为:

=SUMPRODUCT(A2:A36=A2,D2:D36>=D2)

此时,它有两个参数:

  1. A2:A36=A2

  2. D2:D36>=D2

而在我们的需求中,包含了两个条件:

  1. 款号相同(当前款号:A2)

  2. 条件1成立时,根据库存排序(当前库存:D2)

它们一一对应。

不妨看一下计算过程:

函数法在Excel中实现按条件分类排序

此时返回的值为bool型,拿参数1解释,就是A列的每一个款号依此与A2对比,同款号时,返回真,不同款号时,返回假。

对于参数2而言,就是当D列的库存数量不小于D2时,返回真,否则返回假。

因此现在变成了两组bool数组进行运算。

函数法在Excel中实现按条件分类排序

实际上到这里我们还是迷茫的,SUMPRODUCT是个求和用的函数啊,怎么参数里填写的是条件呢?

毕竟,bool值无法直接被SUMPRODUCT函数求值。

那我们不妨把bool值转为数值,便于运算。

  • TRUE == 1

  • FALSE == 0

最简单的做法是给所有的bool值*1。

函数法在Excel中实现按条件分类排序

此时即可用SUMPRODUCT计数,当条件1和条件2都满足时,乘值为1,否则为0,最后加起来的结果就是满足要求的值。

函数法在Excel中实现按条件分类排序

像不像我们教程开始的那个基础用法~

剩下一个--的用法没说,其实它在Excel的作用也很大,可以把其它类型转为数值类型。

因此(A2:A36=A2)*1--(A2:A36=A2)的效果是一样的。

回到开头看去掉绝对引用的公式:

=SUMPRODUCT(--((A2:A36=A2)*(D2:D36>=D2)))

这个公式只用了一个--,因为(A2:A36=A2)的所有返回值都为bool,(D2:D36>=D2)的所有返回值也为bool,在括号的作用下,先进行布尔运算,bool值乘bool值的结果也是bool,因此只用了一次--来把最终结果转化为数值型,让SUMPRODUCT使用。

如上,希望你有所收获。

函数法在Excel中实现按条件分类排序

版权声明:muruoxi 发表于 2022年2月18日 pm2:45。
转载请注明:函数法在Excel中实现按条件分类排序 | 软件爱好者

相关文章

暂无评论

暂无评论...

关注公众号防失联!