SUM,SUMIFS详解：Excel+Python+SQL+Tableau四种工具实现方法

求和类函数SUM,SUMIFS详解：Excel+Python+SQL+Tableau四种工作实现方法：

摘要：求和函数是我们经常在用的，尤其是SUM,SUMIFS等函数，个人觉得SUMIFS的用法已经完整地包含了SUMIF函数，只学SUMIFS就可以，而这两种求和函数在EXCEL表格里,编程语言Python里,数据库语言SQL里，还有可视化软件Tableau里分别如何实现，我尽求详尽地写教程，演示数据只是极小的短短几行，实际中我们可能要处理百万，千万行甚至更多数据，而超过百万行以后EXCEL就明显力不从心了，因为首先行数在工作表里就装不下了，EXCEL格式.xlsx最多可以保存2的20次幂个行数（1048576行），2的14次幂个列数（16384列），超过这个量值的数据则会被EXCEL自动截断舍弃，所以在这种情况下我们处理大数据必然要用到后面的工具

本例要求：1求出每个人的总成绩

2按部门条件进行成绩汇总求和

3.按部门条件+性别条件进行成绩汇总求和

到这里有同学可能会说这也太简单了吧，是的，万事都是从简单开始，高手也要经过简单这个过程才能成为高手不是

一、Excel实现

1直接在I2单元格输入公式：=SUM(E2:H2) 向下填充即可（快捷键操作：选中I2到I19单元格，按ALT + = 可以快速生成结果）

SUM()函数说明：sum单词就是总和的意思

SUM(number1,[number2],...)

这个函数里面的参数可以是一个单元格，也可以是一个序列，或者一个区域，参数最多可以指定255个，每个参数的值可以包含多个单元格的值

这个函数参数里面如果是文本或者逻辑值，会自动忽略也就是不做运算

例：我们对1,a,2 三个值在单元格里用=sum（A1:C1)进行求和，这个函数自动忽略里面值为字母的单元格,结果为3

如果写成=SUM(1,"a",2)这种形式则会报错，因为此时第二个参数并不是单元格引用，无法忽视

而如果我们用加号进行求和则会报错，字母a不能进行相加

回到最前面：曾经我在一本比较有名的EXCEL书籍里看到作者曾见过有人这样写求和公式：=SUM(A1+B1+C1)，作者本人表示哭笑不得，其实我的观点倒是无可厚非，只要能达到正确的结果就行，毕竟我们不能要求每个职场人都成为EXCEL高手，可能没必要理解函数的原理等等，只要我们会用或者不咋会用但能得到想要的结果就可以

2单条件求和，有同学可能会想到用SUMIF函数，我的观点是直接PASS这个函数（不做介绍），主观上我认为这是一个鸡肋的函数，因为SUMIFS函数涵盖它并且更好用

在query1表B2单元格输入公式：=SUMIFS(成绩单!I:I,成绩单!C:C,A2)，下拉填充完成查询

SUMIFS多条件求和用法：

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

必填的是3个参数，后面可以选填更多条件

第一个参数：sum_range，要求和的单元格区域，这里我们直接选成绩表里的总成绩I列，有同学会问那假如原表里没有总成绩这列，可不可以选择E列到H列的整个数据区域，答案是不行的，这种的话需要用到另一个数组求和公式，后面会讲

第二个参数criteria_range1：criteria单词是条件的意思，这是条件区域1，也就是要查询的总成绩列所在表里面的条件列，我们是按部门进行求和，所以我们选择部门C列

第三个参数是criteria1，也就是条件1的意思，这里面我们选择query1表里面的A2列，也就是要查找求和的条件部门，其实这个条件是用的逻辑等于，B2单元格公式 = SUMIFS(成绩单!I:I,成绩单!C:C,A2) 等价于=SUMIFS(成绩单!I:I,成绩单!C:C,"=独立站")和=SUMIFS(成绩单!I:I,成绩单!C:C,"独立站")，这两种写法的结果是一样的，当然以此类推，还可以有不等于<>,大于条件>，小于条件=，小于等于条件

3多条件查询求和：我们在C2单元格输入公式：=SUMIFS(成绩单!I:I,成绩单!C:C,A2,成绩单!B:B,B2)向下拉填充即可

这个查询里面我们多加了一个条件：性别，就是在前面三个参数的基础上，后面再加一个条件（两个参数），理论上这个可以无限进行增加条件

举几个多条件查询的例子:

（一）：查询总成绩在700分及以上的同学的成绩并按部门进行求和，公式为：SUMIFS(成绩单!I:I,成绩单!I:I,">=700",成绩单!C:C,A2)

只有华山派的岳灵珊考了721分，大于700，所以只有华山派有值，其他则为0

（二）查询成绩表籍贯里有“南”字的同学的成绩并按部门进行求和：=SUMIFS(成绩单!I:I,成绩单!D:D,"*南*",成绩单!C:C,A2)

这里查询条件用了通配符*，即只要在籍贯这列里面出现‘南’字，我们则选取再按部门进行求和

如衡山派一共两个人，籍贯里面都有南字，成绩合计等于1111

二、PYTHON实现：

完整代码：

import pandas as pd #1

df = pd.read_excel("c:/study_note/xiao.xlsx",sheet_name = "成绩单")#2

df['总成绩'] = df.sum(axis=1,numeric_only=True)#3

df1 = df.groupby(["部门"])["总成绩"].sum()#4

df2 = df[((df['部门']!='恒山派')&(df['性别']=='男') | (df['部门']=='恒山派')&(df['性别']=='女'))]#5

df2 = df2.groupby(['部门','性别'])['总成绩'].sum()#6

with pd.ExcelWriter("c:/study_note/xiao.xlsx",mode="a",engine="openpyxl") as writer:#7

df.to_excel(writer,sheet_name="成绩表2",index=False)#8

df1.to_excel(writer,sheet_name='query11')#9

df2.to_excel(writer,sheet_name='query22')#10

代码说明：这个代码还是用pandas库完成我们要查询的结果，以在原工作簿上新增加表的形式完成我们的工作，并没有修改原表中的数据

1.导入第三方模块pandas

2.读取成绩单表数据并保存在变量df中

3.进行求和并赋值操作，df.sum()函数表示对表进行求和，如果参数axis = 0，表示按列求和，求和的结果是把各科成绩向下相加，如下：

所以我们这里要按行进行横向求和，所以参数axis =1 (axis英文是坐标轴的意思）

参数 numeric_only = True表示只对行里面的数值格式进行求和，而不对其他类型进行求和

4.这行代码实现的是单条件求和，其中groupby函数叫作分组函数，这里相当于分组求和复合函数，先用groupby对整个数据进行按‘部门’进行分组，这时部门变为索引列，然后方括号["总成绩"]表示只取一列的数据，最后面是求和sum函数，表示以部门作为条件，取总成绩列的合计值，如下：

5这个条件有点复杂，因为前面我是用EXCEL操作直接引用，没想到后面给自己挖了个坑，因为多条件求和的时候，指定的条件里是恒山派只取性别为女的，其他部门只选性别为男的，这行代码的结果就相当于进行了多条件筛选，如下：

& 表示逻辑，|表求逻辑或，(df['部门']!='恒山派')&(df['性别']=='男') 表求部门不等于恒山派且性别为男，(df['部门']=='恒山派')&(df['性别']=='女'))]表示部门等于恒山派且性别为女，然后用|或这个条件把这两个条件进行’打包封装“传递给前面的变量df,所传递的参数值是布尔值，也就是TRUE 或者FALSE,如果是TRUE则进行选取，如果是FALSE则不选取

6表示对前一条进行多条件筛选后的数据进行多条件分组，第一个条件是部门，第二个条件是性别，除了在groupby()函数里面多了一个参数，其他的和前面单条件分组是完全一样的，结果如下：

7写入函数ExcelWriter,因为我们不改变原表，只是把新形成的表增加到原工作簿上，所以mode=”a”,增加模式，引擎用openpyxl

8-10:把用PYTHON得到的三个表数据增加写到EXCEL工作簿里面，sheet_name=””为新表的命名

运行结果：在xiao工作簿新增加3个表格（成绩表2，query11,query22)，即为我们要求的结果

到此python完成SUM求和，SUMIFS单条件求和，SUMIFS多条件求和

附：如果直接在原表上修改，如何用代码实现，个人是不建议初学者直接更改原来，因为难免”代码一乱，损失百万”，一定要事先做好表格备份，不然如果代码出现一点偏差，可能运行后原表数据就没了

本例代码如下：

import pandas as pd

import xlwings as xw

df = pd.read_excel("c:/study_note/xiao.xlsx",sheet_name = "成绩单")

df['总成绩'] = df.sum(axis=1,numeric_only=True)

df1 = df.groupby(["部门"])["总成绩"].sum()

df2 = df[((df['部门']!='恒山派')&(df['性别']=='男') | (df['部门']=='恒山派')&(df['性别']=='女'))]

df2 = df2.groupby(['部门','性别'])['总成绩'].sum()

app = xw.App(visible=False,add_book=False)

wb = app.books.open("c:/study_note/xiao.xlsx")

wb.sheets[1].range("I1").options(index=False).value = df['总成绩']

wb.sheets[2].range("A1").options(index=True).value = df1

wb.sheets[3].range("A1").options(index=True).value = df2

wb.save("c:/study_note/xiao.xlsx")

wb.close()

app.quit()

代码说明：这里面是用第三方模块xlwings实现修改原表，它是一个专门处理EXCEL的python第三方库，wings是翅膀的意思，xl代表表格，这个库名本义可能就是让表格飞起来，看这个官方logo很明显是这个意思

这个库的作者还写了一本excel + python的书籍，非常推荐大家可以看一看

我们直接从第8行代码说：

8表示启动EXCEL程序，visible=False,表示在后台运行，不在前台显示，,add_book=False表示不新建工作簿

9打开工作簿xiao.xlsx,并把这个打开的工作簿对象赋值给变量wb，如下图，可以看到这个变量代表什么：

10.wb.sheets[1] 这个表示工作簿wb里面的第二个表格，从左到右，我们这里取成绩单表，排在工作表里面第二个，同理后面两行代码表示第3个，第4个的意思，range("I1")表示要修改表数据的区域，如果改的是一个区域，则从最左上角向下和向右进行填充，这里我们选I1单元格，是对应取总成绩那列的首个单元格，options(index=False)这个选项参数是忽略索引的意思，如果不忽略的话，修改进来就会把数据里面的默认索引编号也带进来，如下图，最后.value 就是默认赋值语法，最终的结果是把df['总成绩'] 这一列值写入到第二个表里的从I1单元格开始向下的列里面

11.代码同第10行的用法一样，唯一不同的是这里参数options(index=True)表示取索引的意思，因为这个赋值的数据源是由函数GROUPBY生成的数据，前面的条件会作为索引列显示，如下图，所以我们需要这个索引列作为我们的查询结果，还有一种方法就是参数依旧不选索引，先把数据源里的索引进行还原为普通列，麻烦一点，实现结果一样

12代码作用同第11行

13保存工作簿到指定路径和指定名称，相当于在EXCEL界面点保存按钮

14关闭工作簿

15退出EXCEL程序

三、SQL实现方法：

首先我们把成绩单这个表在数据库里进行创建：

CREATETABLE score

(

姓名 VARCHAR(5),

性别 CHAR(1),

部门 VARCHAR(4),

籍贯 VARCHAR(8),

语文 INT,

数学 INT,

综合 INT,

英语 INT,

总成绩 INT

);

然后用图形化界面把EXCEL里的原始数据导入进数据库里，得到如下结果：

总成绩即为我们要求和的区域

然后再创建两个条件求和的表格，分别命名为query2,query3,代码如下：

CREATETABLE query2

(

部门 VARCHAR(4),

总成绩 INT

);

INSERTINTO query2 部门 SELECTDISTINCT 部门 FROM score;

CREATETABLE query3

(

部门 VARCHAR(4),

性别 CHAR(1),

总成绩 INT

);

INSERTINTO query3 (部门,性别)

SELECTDISTINCT 部门,性别 FROM score WHERE (部门='恒山派'AND 性别='女' )OR ( 部门!='恒山派'AND 性别="男" );

得到如下两张表：

空白区域即为我们要填入的条件求和区域

1按行求和代码：完成总成绩求和

UPDATE score SET 总成绩 = 语文+数学+综合+英语;

第二问题：单条件求和代码：SELECT 部门,SUM(总成绩) FROM score GROUPBY 部门;

先将这个查询结果保存到一个临时表，然后用代码更新到我们的查询表query2里面

CREATETABLE temp1

(

部门 VARCHAR(4),

总成绩 INT

);

SELECT * FROM temp1 ;

INSERTINTO temp1 SELECT 部门,SUM(总成绩) FROM score GROUPBY 部门;

UPDATE query2,temp1 SET query2.总成绩 = temp1.总成绩 WHERE query2.部门 = temp1.部门;

第三个问题：多条件求和,代码：

SELECT 部门,性别,SUM(总成绩) FROM score GROUPBY 部门,性别;

我们把这个结果保存到query3表里面，用取巧的办法可以把其他两个门派里面的性别为女的行删除即可，或者把查询结果保存到一个临时表，然后用UPDATE把值更新到query3表

代码：

CREATETABLE temp2

(

部门 VARCHAR(4),

性别 CHAR(1),

总成绩 INT

);

INSERTINTO temp2 SELECT 部门,性别,SUM(总成绩) FROM score GROUPBY 部门,性别;

UPDATE query3,temp2 SET query3.总成绩 = temp2.总成绩 WHERE query3.部门 = temp2.部门 AND query3.性别 = temp2.性别;

SELECT * FROM query3;

至此，SQL完成任务

四、Tableau软件实现：

Tableau是一个非常有名的可视化软件，可对海量数据进行可视化输出，有的同学可能会有疑问，这个软件不是生成可视化图表的吗，还能进行数据处理？答案是可以的，用星爷电影里的台词讲就是“表面上是一个吹风机，实际上是剃须刀”，只有想不到，没有做不到，Tableau完全可以对表格数据处理并输出结果

先到官网下载 Tableau Desktop软件，这个有15天的免费试用期，如果是在校学生或者有在职学历教育的同学可以申请免费使用一年，上传学生证及身份证等，届时官方会用邮件发过来一个KEY码，激活即可用一年，如果过了试用期还想用，那就上某宝找卖家

找到官网地址，下载DESKTOP，如果有想进一步学习的同学可以顺手把Prep也一起下载，这是一个为Tableau先预处理数据源的工具，安装好后打开软件：那个白色的图标软件

选择左边连接- 到文件 - EXCEL 那项，然后在电脑里找到我们要处理的成绩单表格(注：要先把原始数据里面要求和的总成绩字段删掉，我们后面在Tableau里面用创建计算字段的形式进行实现）

建立好连接，我们把要处理的成绩单拖到右边空白区域

下面是数据预览，连接模式选实时即可，然后用鼠标点左下角“工作表1”，生成空白主界面，左边是数据字段项，分为度量名称（维度），和度量值（数值）

因为这里没有总成绩字段，我们需要在分析- 创建计算字段，创建一个总成绩字段

新字段命名为总成绩，然后把左边字段里的数值字段名用鼠标拖到右边的计算框里，用加号相连，下面显示计算有效，点应用

我们把新生成的总成绩字段拖到正上方的列空格里，把部门拖到行空格里

因为上面生成的是自动匹配的可视化图表类型-条形图，我们需要让它显示为表格形式，鼠标点右上角‘智能推荐’，然后选左上角那个表格形式

最终生成的图像是

这个第二列虽然是我们的求和结果，但是并没有显示字段名，效果不佳，我们改善一下，左下角的度量值拖到列空白处，然后再选表格形式

我们把左下角方框里面的成绩单删除，然后用鼠标拖动里面的名称改变一下顺序，最终结果如下：

最后一步导出到EXCEL表格，选工作表 - 导出 - 交叉到EXCEL 即可实现导出

第三个问题多条件查询,就是把左边性别字段移到行空格部门后面，即可实现，然后导出，直接在表格里进行筛选操作即可

补充一下前面第一个问题，其实就是把里面的名称字段全都拖到行空格处，如下图，这个表就是我们第一张对每个同学进行成绩求和的表，然后导出为表格即可完成

返回搜狐，查看更多

责任编辑：