终极求和函数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
筛选好后,把这个字段移除
得到想要结果
至此,四种工具全部完成任务
如果觉得有用的朋友请点下关注,谢谢!
KVM线缆是什么?在音频视频领域,除了VGADVIHDMI线缆外,还有一些较少人熟知的连接线缆。本文将围绕一种比较特殊的连接线缆KVM线进行介绍和应用解析。一KVM是什么?既然是KVM的连接线,我们
击败小米和iQOO,冲上新机流畅榜第一名,好评率高达98流畅是很多消费者对一款手机最低的要求,站在消费者的角度来看,先不说一款手机能否拥有多么强大的性能和影像能力,最起码基础流畅性得有保证,不然使用体验一定不好,但是站在手机厂商的角度来
安卓次旗舰排行天玑8000系列霸榜8个月!进入2023年1月份,安兔兔按惯例发布了2022年12月份的安卓手机性能排行榜,其中次旗舰平台非常有趣。这个榜单继续被联发科天玑平台完全统治,TOP10名额全部拿下,无一例外,相当
是兄弟就来砍我,想在魔域手游2当一个好的社长,请记住这几点在各种类型的网游中,有一个游戏元素基本做到了全网统一,那就是由玩家建立的团体,比如公会社团联盟等等,不同游戏的叫法不同,但性质都一样,玩家可以在这里找到更稳定的好友。依据游戏类型不
靠游戏主播带货翻红,冲上热搜的鹅鸭杀还能火多久?继羊了个羊之后,又一款小游戏goosegooseduck(鹅鸭杀)成为了近期热搜榜的常客。这是一款类似狼人杀的派对类游戏,由国外小团队GaggleStudios制作,可以支持416
潜龙阵无用?那是你不会用近期在玩三国志战略版,玩过的小伙伴们都知道,潜龙阵这个阵法出了有一段时间了,但鲜有人用,原因是没有找到牛逼的队伍组合,今天给大家推荐一队,很强势的一队,成型快,多穿能力强,尤其适用
冬季怎么给新生儿洗澡?囟门头垢脐带怎么办?新手爸妈收藏大家好,我是两个孩子的妈妈卿晨。有的新手妈妈怕宝宝感冒,冬天里不敢给新生儿洗头,结果宝宝满满洗头胎脂,都卡住推发器!我家蛋糕妹妹也出生于冬天,所以我非常能理解那些冬天不给小宝宝洗澡
有声绘本猪先生去野餐做自己才是最好的!乐爸导读今天分享的绘本故事叫猪先生去野餐,故事讲述了一个有关自信的故事,猪先生做好了充分的准备去约猪小姐野餐,虽然他看起来帅极了,但是在好朋友的建议下,他还是借了很多装扮来美化自己
十日谈应彩云岁月的童话39年前,作为年轻的幼儿教师,我常常和班上孩子讲故事聊趣事,所用的画本便是娃娃画报,她成了我初步了解孩子的桥梁。5年后,我参加了朱庆坪老师组织的童话写作班,就当年的交通条件而言,我
宝宝在吃益生菌时需要注意什么呢?宝宝在吃益生菌时需要注意什么呢?1不要长时间服用。事实上,自婴儿出生以来,身体的各个系统都在慢慢适应环境,肠道也会慢慢形成自己的菌群。因此,如果婴儿的消化功能正常,建议不要给婴儿长
阳了后,要比坐月子更像坐月子文图肖君1hr身体最诚实。它不会说谎。从复学复课两天的娃被感染后再传染给我,是12月17日,那天开始发烧,至今已过去十几天。还是咳嗽,每天的状态是这样子的可能有10分钟一点都不咳,