终极求和函数sumproduct详解:Excel、Python、SQL、Tableau中实现 |
您所在的位置:网站首页 › python的sum函数的运用 › 终极求和函数sumproduct详解:Excel、Python、SQL、Tableau中实现 |
本文介绍终极求和函数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计算出全部订单总销售额 SELECTsum(销售数量*产品单价) FROM sales; 2计算出姓名为令狐冲的订单总销售额 SELECT 姓名,sum(销售数量*产品单价) AS 总销售额 FROM sales GROUPBY 姓名 HAVING 姓名="令狐冲"; 3计算出姓名为令狐冲且销售产品为orange的订单总销售额 SELECT 姓名,销售产品,sum(销售数量*产品单价) AS 总销售额 FROM sales GROUPBY 姓名,销售产品 HAVING 姓名="令狐冲"AND 销售产品="orange"; 4计算出部门为华山派且性别为女且销售产品为apple的订单总销售额 SELECT 部门,性别,销售产品,sum(销售数量*产品单价) AS 总销售额 FROM sales GROUPBY 部门,性别,销售产品 HAVING 部门="华山派"AND 性别="女"AND 销售产品="apple"; 5计算出部门为华山派且性别为女且销售(产品为apple或者产品为orange)的订单总销售额 SELECTsum(销售数量*产品单价) AS 总销售额 FROM sales WHERE 销售产品="apple"OR 销售产品="orange" GROUPBY 部门,性别 HAVING 部门="华山派"AND 性别="女"; 6计算出姓名为令狐冲且单笔订单销售数量大于20的订单总销售额 SELECT 姓名,sum(销售数量*产品单价) AS 总销售额 FROM sales WHERE 销售数量>20 GROUPBY 姓名 HAVING 姓名="令狐冲"; 四、Tableau实现 1计算出全部订单总销售额 连接到工作表sales,创建计算字段销售额,公式: [销售数量]*[产品单价] 把销售额拖到文本上面,即可显示总金额 2计算出姓名为令狐冲的订单总销售额 把姓名报到行空格处: 3计算出姓名为令狐冲且销售产品为orange的订单总销售额 把销售产品拖到行空格处 4计算出部门为华山派且性别为女且销售产品为apple的订单总销售额 把需要的几项条件字段拖到行空格 5计算出部门为华山派且性别为女且销售(产品为apple或者产品为orange)的订单总销售额 从右边筛选需要计算的产品,然后把销售产品字段从行空格删掉,即可得到结果 5计算出姓名为令狐冲且单笔订单销售数量大于20的订单总销售额 把销售数量字段复制一个副本,属性改为维度 然后把销售数量(复制)这个拖到行空格处,可以看到表上已按每个订单的销售数量进行分类了 右键添加筛选器,条件写 销售数量(复制)>20 筛选好后,把这个字段移除 得到想要结果 至此,四种工具全部完成任务 如果觉得有用的朋友请点下关注,谢谢! |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |