终极求和函数sumproduct详解ExcelPythonSQLTableau使用教程

　　本文介绍终极求和函数sumproduct：
　　普通的求和需求只需要用SUM或者SUMIFS函数即可满足，但是如果对数组进行求和，会发现就不灵了，这里就该轮到终极求和王函数sumproduct登场了，sum单词是总和的意思，product单词有＂乘积＂的意思，函如其名，是把多个数进行乘积再汇总求和
　　应用场景举例，现在有一个销售表如下：
　　要求不借助辅助列的条件下完成以下问题：
　　1计算出全部订单总销售额
　　2计算出姓名为令狐冲的订单总销售额
　　3计算出姓名为令狐冲且销售产品为orange的订单总销售额
　　4计算出部门为华山派且性别为女且销售产品为apple的订单总销售额
　　5计算出部门为华山派且性别为女且销售(产品为apple或者产品为orange)的订单总销售额
　　6计算出姓名为令狐冲且单笔订单销售数量大于20的订单总销售额
　　很多同学想到的是先在销售表加一列先计算出每行的销售额，再用SUMIFS函数进行取值 ，如下，这是一种办法，而且相对较常用一些
　　但是现实中可能会在一些特定情况下不能再增加辅助列，此时这种办法就不灵了，于是我们开始使用sumproduct函数：
　　用法：=SUMPRODUCT (array1， [array2]， [array3]， ...)最多有255个参数
　　SUMPRODUCT函数返回相应范围或数组的个数之和，也就是数组1的每行与数组2的对应行与数组3的对应行进行相乘，然后再把每行的乘积进行相加：
　　这里需要注意几点：
　　1每个参数的范围应该是一致的，也就是行数要相同，
　　2如果里面有非数值，比如进行乘积的第二个数组里面的值是文本值，则默认为是0
　　3范围要有明确边界 ，这里不能像vlookup那样直接取整个一列，如果是这样的话，则数组会进行超大量的计算，比如=SUMPRODUCT(A:A,B:B)，这里会进行104万多行计算相乘，会很占用资源的
　　4参数里面可以嵌套条件进行条件取数，再进行乘积求和，这项也就是解决我们任务的方法
　　1计算出全部订单总销售额
　　输入公式：=SUMPRODUCT(sales!F2:F19,sales!G2:G19)
　　其实这里也可以写成这样=SUMPRODUCT(sales!F1:F19,sales!G1:G19) ，第一行虽然并不是数值，但是会默认将文本值视为0，0乘以0还是等0，加里面也不影响结果
　　2计算出姓名为令狐冲的订单总销售额
　　公式=SUMPRODUCT((sales!B2:B19=＂令狐冲＂)*sales!F2:F19*sales!G2:G19)
　　这里面（sales!B2:B19=＂令狐冲＂)是条件判断 ，返回逻辑值0或者1，如果是0则后面相乘等0，所以这里只有姓名令狐冲的行，才能取到值为1 ，然后用1乘以后面两个数组对应的行再进行相加
　　3计算出姓名为令狐冲且销售产品为orange的订单总销售额
　　公式=SUMPRODUCT((sales!B2:B19=＂令狐冲＂)*(sales!E2:E19=＂orange＂)*sales!F2:F19*sales!G2:G19)
　　只是比上面那个多了一个判断条件，用乘号*进行连接即可
　　4计算出部门为华山派且性别为女且销售产品为apple的订单总销售额
　　=SUMPRODUCT((sales!D2:D19=＂华山派＂)*(sales!C2:C19=＂女＂)*(sales!E2:E19=＂apple＂)*sales!F2:F19*sales!G2:G19)
　　5计算出部门为华山派且性别为女且销售(产品为apple或者产品为orange)的订单总销售额
　　公式如下：=SUMPRODUCT((sales!D2:D19=＂华山派＂)*(sales!C2:C19=＂女＂)
　　*(sales!E2:E19={＂apple＂,＂orange＂})*sales!F2:F19*sales!G2:G19)
　　这里只是在第三个条件里面加了一个数组{} 里面的表示，只要前面的范围的值在{}里，则返回逻辑值1
　　6计算出姓名为令狐冲且单笔订单销售数量大于20的订单总销售额
　　Python实现
　　1计算出全部订单总销售额
　　代码如下
　　df = pd.read_excel(＂c:/study_note/xiao_subtotal.xlsx＂,sheet_name=＂sales＂)
　　df[＂销售额＂] = df[＂销售数量＂] * df[＂产品单价＂]
　　df[＂销售额＂].sum()
　　这里很明显借助了辅助列，相当于新建了一个销售额列，值为每行数量和单价相乘的结果，至此，后面几个问题的Python实现方法就是相当于SUMIFS函数了
　　2计算出姓名为令狐冲的订单总销售额
　　代码： df.groupby(＂姓名＂)[＂销售额＂].sum()[＂令狐冲＂]
　　3计算出姓名为令狐冲且销售产品为orange的订单总销售额
　　代码：
　　a = df.groupby([＂姓名＂,＂销售产品＂])[＂销售额＂].sum()
　　a[＂令狐冲＂, ＂orange＂]
　　4计算出部门为华山派且性别为女且销售产品为apple的订单总销售额
　　a = df.groupby([＂部门＂,＂性别＂,＂销售产品＂])[＂销售额＂].sum()
　　a[＂华山派＂,＂女＂, ＂apple＂]
　　5计算出部门为华山派且性别为女且销售(产品为apple或者产品为orange)的订单总销售额
　　a = df.groupby([＂部门＂,＂性别＂,＂销售产品＂])[＂销售额＂].sum()
　　a[＂华山派＂,＂女＂, ＂apple＂]+a[＂华山派＂,＂女＂, ＂orange＂]
　　6计算出姓名为令狐冲且单笔订单销售数量大于20的订单总销售额
　　a = df[df[＂销售数量＂]>20].groupby(＂姓名＂)[＂销售额＂].sum()
　　a[＂令狐冲＂]
　　SQL实现
　　1计算出全部订单总销售额
　　SELECT   sum (销售数量*产品单价)  FROM  sales ;
　　2计算出姓名为令狐冲的订单总销售额
　　SELECT  姓名, sum (销售数量*产品单价)  AS  总销售额  FROM  sales  GROUP   BY  姓名  HAVING  姓名= ＂令狐冲＂ ;
　　3计算出姓名为令狐冲且销售产品为orange的订单总销售额
　　SELECT  姓名,销售产品, sum (销售数量*产品单价)  AS  总销售额  FROM  sales  GROUP   BY  姓名,销售产品
　　HAVING  姓名= ＂令狐冲＂ AND  销售产品= ＂orange＂ ;
　　4计算出部门为华山派且性别为女且销售产品为apple的订单总销售额
　　SELECT  部门,性别,销售产品, sum (销售数量*产品单价)  AS  总销售额  FROM  sales  GROUP   BY  部门,性别,销售产品
　　HAVING  部门= ＂华山派＂   AND  性别= ＂女＂   AND  销售产品= ＂apple＂ ;
　　5计算出部门为华山派且性别为女且销售(产品为apple或者产品为orange)的订单总销售额
　　SELECT   sum (销售数量*产品单价)  AS  总销售额  FROM  sales  WHERE  销售产品= ＂apple＂   OR  销售产品= ＂orange＂
　　GROUP   BY  部门,性别  HAVING  部门= ＂华山派＂   AND  性别= ＂女＂ ;
　　6计算出姓名为令狐冲且单笔订单销售数量大于20的订单总销售额
　　SELECT  姓名, sum (销售数量*产品单价)  AS  总销售额  FROM  sales  WHERE  销售数量> 20
　　GROUP   BY  姓名  HAVING  姓名= ＂令狐冲＂   ;
　　Tableau实现
　　1计算出全部订单总销售额
　　连接到工作表sales,创建计算字段销售额，公式：
　　[销售数量]*[产品单价]
　　把销售额拖到文本上面，即可显示总金额
　　2计算出姓名为令狐冲的订单总销售额
　　把姓名报到行空格处：
　　3计算出姓名为令狐冲且销售产品为orange的订单总销售额
　　把销售产品拖到行空格处
　　4计算出部门为华山派且性别为女且销售产品为apple的订单总销售额
　　把需要的几项条件字段拖到行空格
　　5计算出部门为华山派且性别为女且销售(产品为apple或者产品为orange)的订单总销售额
　　从右边筛选需要计算的产品，然后把销售产品字段从行空格删掉，即可得到结果
　　5计算出姓名为令狐冲且单笔订单销售数量大于20的订单总销售额
　　把销售数量字段复制一个副本，属性改为维度
　　然后把销售数量（复制）这个拖到行空格处，可以看到表上已按每个订单的销售数量进行分类了
　　右键添加筛选器，条件写 销售数量（复制）>20
　　筛选好后，把这个字段移除
　　得到想要结果
　　至此，四种工具全部完成任务
　　如果觉得有用的朋友请点下关注，谢谢！