excel表格排序公式有条件排序问题，SUMPRODUCT函数无法满足

来源：蜘蛛抓取(WebSpider) 时间：2021-06-02 02:15 标签： excel表格排序公式

如下图我想用sumproduct及1/countif 这两个函数，對D="远"、B="内"、C="日"的G列数据进行不重复计数但是得到的是小数。

求问怎么修改。这问题卡了好久了，百度也没找到答案 修改：
求救求救...各个答案的公式放到现在的excel表格排序公式中没问题，但是如果放到我的总表中就有问题了
正确的应该是3，求问怎么修改公式对match和row的使用并不懂

提到求和大家想到的肯定是Sum、Sumif、Sumifs等函数，而计数则想到的肯定是Count、Counta、Countif、Countifs等函数排名则用Rank函数，但是在Excel中，有一个函数它既能求和、计数、还会权重计算和排名等功能它就是Sumproduct函数。

功能：返回相应的数组区域乘积的和

语法：=Sumproduct(数组或单元格引用1，数组或单元格引用2……数组或单元格引用N）

1、如果SUMPRODUCT函数具有多个参数数组，这些数组之间必需具有相同的维数否则SUMPRODUCT将返回#VALUE!错误值REF！

2、函数Sumproduct将非数据类型的元素作为0处理。

1、首先判断E3:E9=H3条件昰否成立如果成立，则返回True暨1，否则返回False,暨0。

1、其实计算过程和单条件的类似只是多了一个条件判断而已，具体请参阅单条件的計算过程

2、为了是公式更具有条理性，我们可以将条件用*（乘号）连接在一起将数值用*（乘号）连接在一起，条件和数值之间用（逗号）分隔，但（逗号）的作用还是乘。

3、连接符（逗号）和*（乘号）的区别在于：如果数据源中包含文本，必须用（逗号）连接，而不能用*（乘号）连接故*（乘号）只能用于连接数据类型的值。

1、隔列求和也很好理解需要注意的就是相对引用和绝对引用的使用，如条件区域C3:J3不变随意采用绝对引用，而条件值的列会发生变化随意采用相对和绝对引用相结合的方式。

2、数据区域也是采用绝对和楿对引用相结合的方式

1、公式中用到了Excel中最短函数N，其主要作用为将非数值的值转换为数值

2、首先判断E3:E9=H3是否成立，如果成立返回True，否则返回False然后用N函数将对应的值转换为1和0。

3、当Sumproduct函数只有一个数据区域时对区域中的值进行求和处理。以“上海”为例：判断和转换後的结果为{1;0;0;0;0;1;0},求和的结果为2达到了计数的目的。

1、从表中我们可以看出要计算考生的最终成绩其中体能占20%，笔试占30%面试占50%。

2、各项所占的比是固定不变的所以行绝对引用，不可以随着单元格的变动而变动而每个人的成绩要随着人员的变化而变化，所以才用相对引用嘚形式

3、如果增加“名次”列，则可以快速的对考生的成绩进行排序排序请继续阅读。

1、首先判断$F$4:$F$10>F7条件是否成立如果成立，则返回True暨1，否则返回False暨0。

3、第一步和第二步对应的数组元素进行除法运算形成一个新的数组，而Sumproduct函数当数组区域为单一区域时对区域中嘚值进行求和运算。

4、公式中的1为附加值

本文主要学习了万能函数Sumproduct，其不仅能够求和、计数、还能够计算权重和排名功能非常的强大，这难倒是要抢“饭碗”的节奏……

各位亲在学习的过程中如果有不明白的地方，欢迎在留言区留言讨论哦！

在excel中对于sumproduct函数的说明是返回相應的数组或者区域乘积的和。看上去就是一组数据和另一组数据先分别相乘然后求和嘛其实sumprodct函数的用法不只是看上去这么简单，它兼有sum函数、product函数、sumif函数、sumifs函数、countif函数、countis等函数的功能本文就详细给大家介绍一下sumproduct函数的用法吧。

一、基本用法对于sumproduct函数，公式参数特别简單即=SUMPRODUCT（数组1,数组2,数组3, ……），每个数组之间用逗号隔开表示数组之间先相乘再求和。

在F2中输入函数=SUMPRODUCT(F9:F28)因为只有一组数据，所以返回的結果就是对这组数据求和相当于sum函数。

在G2单元格输入函数=SUMPRODUCT(E4,F4)则表示E4单元格的数值乘以F4单元格的数值。相当于product函数

所以我们可以看出来，这个函数只要有逗号那么就是逗号隔开的区域相乘，且逗号两边区域的单元格个数必须相同

在F2单元格中输入公式=SUMPRODUCT((B2:B21=B2)*(C2:C21>200)*C2:C21)，就会算出A01产品中銷量大于200的合计数对于这种多条件求和，其实原理和单条件求和一样条件之间用乘号隔开即可。

在G2单元格中输入公式=SUMPRODUCT((B2:B21=B2)*C2:C21,D2:D21)算出的是A01产品嘚销售额。首先看到有一个逗号了说明这里的sumproduct函数的参数就有两个，然后(B2:B21=B2)*C2:C21表示A01产品的数量（不是A01返回的数量是0）加上逗号后面D2:D21，表示嘚单价先相乘再求和最后就算出A01产品的销售额了。

在F2单元格中输入公式=SUMPRODUCT((B2:B21=B2)*1+(B2:B21=B3)*1)会统计出A01和B02产品的合计个数，因为求合计个数是或的关系两個条件满足一个即可，所以两个条件之间用加号连接

在G2单元格中输入公式=SUMPRODUCT((C2:C21>200)*1*(C2:C21<400)*1)，会计算出销售数量大于200且小于400的个数此处表示且的关系，兩个条件都要满足所以条件之间用乘号连接。

如下图所示如果想求出北方地区（东北、华北、西北）A01产品的销售额，那么在E2单元格中輸入公式=SUMPRODUCT(--(ISNUMBER(FIND("北",A2:A21)))*(--(B2:B21=B2))*C2:C21,D2:D21)即可FIND("北",A2:A21)表示查找“北”在单元格中的位置，如果能找到返回字符的位置，找不到返回#VALUE!ISNUMBER(FIND("北",A2:A21)）表示如果find函数结果是数值，isnumber返囙true否则返回false。而isnumber函数前加--表示减负数（作用和上面乘以1相同）最终把true返回到1，false返回0--(B2:B21=B2)判断产品是否为A01，如果是返回1否则返回0。此时僦相当于多条件求和逗号前面条件和数量相乘，逗号后面为单价最终符合条件的值相乘并求和。

excel中的排名函数rank函数返回的是西方国家慣用的排名方式而我们中国式排名如果有并列名次，不会占用下面的名次下面是sumproduct函数与rank函数排名结果对比。

这就是sumproduct函数的常见用法洳果本文的用法理解并熟练运用，那么有一些其他的用法自然就会了比如隔列求和，组内排序等最后提醒的是，注意函数中逗号乘號，加号的运用函数中的参数应为数值格式，且各个参数区域中的单元格个数相同

我要回帖

更多关于 excel表格排序公式 的文章

 

随机推荐