1.读写excel
安装 Excel 读取库 xlrd
想用 Python 处理 Excel 数据,但是 Python 标准库中没有处理 Excel 文件的库,这时就需要安装第三方库。xlrd 就是处理 Excel 文件的第三方库。下面介绍如何安装第三方库 xlrd。
不同的 IDE 和环境中有不同的安装方法,这个要根据环境的不同区别对待。
pip install xlrd==1.2.0
1.读取 Excel 工作表
import xlrd #导入 xlrd
filePath = r'1-Scores.xlsx'
# 读取工作簿对象。
wb = xlrd.open_workbook(filePath)
# 读取工作簿下的所有工作表对象
all_ws1 = wb.sheets()
# 读取工作簿下的所有工作表对象的名称
all_ws2 = wb.sheet_names()
# 用索引值读取工作表 - 方法 1
ws1 = wb.sheet_by_index(0)
# 用索引值读取工作表 - 方法 2
ws2 = wb.sheets()[1]
# 用索引值读取工作表 - 方法 3
ws3 = wb.sheet_by_name('雪豹队')
# 直接通过工作簿读取工作表对象
ws4 = xlrd.open_workbook(filePath).sheet_by_name('飞龙队')
2.读取 Excel 行、列、单元格信息
import xlrd # 导入 xlrd
# 读取工作簿对象。
filePath = r'1-Scores.xlsx'
wb = xlrd.open_workbook(filePath)
# 读取工作表对象。
ws = wb.sheet_by_name('飞龙队')
# 返回工作表中已使用的行数。
row_count = ws.nrows
# 返回工作表中已使用的列数。
col_count = ws.ncols
# 返回工作表中指定行已使用的单元格对象。
row_obj = ws.row(1)
# 返回工作表中指定行已使用的单元格的值。
row_val = ws.row_values(1)
# 返回工作表中指定列已使用的单元格对象。
col_obj = ws.col(0)
# 返回工作表中指定列已使用的单元格的值。
col_val = ws.col_values(0)
# 返回工作表中指定行、列交叉单元格对象。
cell_obj = ws.cell(3,1)
# 返回工作表中指定行、列交叉单元格的值。
cell_val = ws.cell_value(3,1)
安装 Excel 写入库 xlwt
前面介绍的 xlrd 库只能读取 Excel 文件中的数据,如果要往 Excel 文件中写入数据,xlrd 库则不具备这个功能,需要安装 xlwt 库。xlwt 库具有创建工作簿、工作表,以及将数据写入单元格的功能。
pip install xlwt
3.新建工作簿、新建工作表和写入数据
Python 需要有写入文件权限,特别是 Linux 和 Mac 中
import xlwt # 导入 xlwt
# 新建工作簿
nwb = xlwt.Workbook('utf-8')
# 在新建工作簿中创建工作表
nws = nwb.add_sheet('工资表')
# 在工作表的指定单元格写入值
nws.write(0,0,'张三:9000 元')
# 保存工作簿
nwb.save('工资表.xls')
安装 Excel 修改库 xlutils
xlrd 库只能用于读取已经存在的 Excel 工作簿、工作表、单元格等相关信息,而 xlwt 库只能新建工作簿、新建工作表、将数据写入单元格等,没有办法对现有的工作簿进行修改。要实现修改功能,就需要安装 xlutils 库,相当于在 xlrd 库和 xlwt 库之间建立起一座桥梁。但要注意,使用 xlutils 库一定要先安装 xlrd 库和 xlwt 库,否则安装 xlutils 库没有意义。
pip install xlutils
import xlrd # 导入 xlrd
from xlutils.copy import copy # 导入 xlutils 下 copy 模块下的 copy 函数
filePath = r'2-Salary.xls'
# 读取工作簿
wb = xlrd.open_workbook(filePath)
# 复制工作簿
nwb = copy(wb)
# 用索引号读取工作簿中的工作表
ws1 = nwb.get_sheet(0)
# 用名称读取工作簿中的工作表
ws2 = nwb.get_sheet('工资表')
# 在工作簿中新建工作表
ws3 = nwb.add_sheet('汇总表')
# 将数据写入单元格
ws3.write(0,0,'总计')
# 将数据写入单元格
ws3.write(0,1,12000)
# 保存工作簿
nwb.save('2-Salary-2.xls')
2.assert 关键词
assert
这个关键词我们称之为“断言”,当这个关键词后边的条件为 False 时,程序自动崩溃并抛出AssertionError
的异常。
【例子】
my_list = ['lsgogroup']
my_list.pop(0)
assert len(my_list) > 0
# AssertionError
【例子】在进行单元测试时,可以用来在程序中置入检查点,只有条件为 True 才能让程序正常工作。
assert 3 > 7
# AssertionError
3.enumerate()函数
enumerate(sequence, [start=0])
- sequence:一个序列、迭代器或其他支持迭代对象。
- start:下标起始位置。
- 返回 enumerate(枚举) 对象
【例子】
seasons = ['Spring', 'Summer', 'Fall', 'Winter']
lst = list(enumerate(seasons))
print(lst)
# [(0, 'Spring'), (1, 'Summer'), (2, 'Fall'), (3, 'Winter')]
lst = list(enumerate(seasons, start=1)) # 下标从 1 开始
print(lst)
[(1, 'Spring'), (2, 'Summer'), (3, 'Fall'), (4, 'Winter')]
# enumerate() 与 for 循环的结合使用。
for i, a in enumerate(A)
do something with a
用 enumerate(A)
不仅返回了 A
中的元素,还顺便给该元素一个索引值 (默认从 0 开始)。此外,用 enumerate(A, j)
还可以确定索引起始值为 j
。
【例子】
languages = ['Python', 'R', 'Matlab', 'C++']
for language in languages:
print('I love', language)
print('Done!')
# I love Python
# I love R
# I love Matlab
# I love C++
# Done!
for i, language in enumerate(languages, 2):
print(i, 'I love', language)
print('Done!')
# 2 I love Python
# 3 I love R
# 4 I love Matlab
# 5 I love C++
# Done!
4.推导式
1.列表推导式
[expr for value in collection [if condition] ]
#【例子】
x = [-4, -2, 0, 2, 4]
y = [a * 2 for a in x]
print(y)
# [-8, -4, 0, 4, 8]
x = [i ** 2 for i in range(1, 10)]
print(x)
# [1, 4, 9, 16, 25, 36, 49, 64, 81]
x = [(i, i ** 2) for i in range(6)]
print(x)
# [(0, 0), (1, 1), (2, 4), (3, 9), (4, 16), (5, 25)]
x = [i for i in range(100) if (i % 2) != 0 and (i % 3) == 0]
print(x)
# [3, 9, 15, 21, 27, 33, 39, 45, 51, 57, 63, 69, 75, 81, 87, 93, 99]
a = [(i, j) for i in range(0, 3) for j in range(0, 3)]
print(a)
# [(0, 0), (0, 1), (0, 2), (1, 0), (1, 1), (1, 2), (2, 0), (2, 1), (2, 2)]
x = [[i, j] for i in range(0, 3) for j in range(0, 3)]
print(x)
# [[0, 0], [0, 1], [0, 2], [1, 0], [1, 1], [1, 2], [2, 0], [2, 1], [2, 2]]
x[0][0] = 10
print(x)
# [[10, 0], [0, 1], [0, 2], [1, 0], [1, 1], [1, 2], [2, 0], [2, 1], [2, 2]]
a = [(i, j) for i in range(0, 3) if i < 1 for j in range(0, 3) if j > 1]
print(a)
# [(0, 2)]
2.元组推导式
(expr for value in collection [if condition] )
a = (x for x in range(10))
print(a)
# <generator object <genexpr> at 0x0000025BE511CC48>
print(tuple(a))
# (0, 1, 2, 3, 4, 5, 6, 7, 8, 9)
<generator object <genexpr> at 0x0000014CEC2E28B8>
(0, 1, 2, 3, 4, 5, 6, 7, 8, 9)
3.字典推导式
{key_expr: value_expr for value in collection [if condition] }
b = {i: i % 2 == 0 for i in range(10) if i % 3 == 0}
print(b)
# {0: True, 3: False, 6: True, 9: False}
4.集合推导式
{expr for value in collection [if condition] }
c = {i for i in [1, 2, 3, 4, 5, 5, 6, 4, 3, 2, 1]}
print(c)
# {1, 2, 3, 4, 5, 6}
e = (i for i in range(10))
print(e)
# <generator object <genexpr> at 0x0000007A0B8D01B0>
print(next(e)) # 0
print(next(e)) # 1
for each in e:
print(each, end=' ')
# 2 3 4 5 6 7 8 9
s = sum([i for i in range(101)])
print(s) # 5050
5.Excel常用函数
01. MID函数
函数定义:从一个文本字符串的指定位置开始,截取指定数目的字符
使用格式:MID(text, start_num, num_chars)
例子:
02. CONCATENATE函数
函数定义:将多个字符文本或单元格中的数据连接在一起,显示在一个单元格中
使用格式:CONCATENATE(text1,text2,……)
重点:也可以用&(和号)运算符代替函数CONCATENATE实现文本项的合并
例子:
03. AND函数
函数定义:检测所有的条件是否为真
使用格式:AND(logical1,logical2,……logical30)
注意事项: 如果指定的区域中不包含逻辑值或数值时,函数AND返回错误值#VALUE!. Logical1,logical2,……logical30表示待检测的1到30个条件值,各条件值可为TRUE或FALSE
例子:
可以与IF函数连用
04. IF函数
函数定义:根据条件满足与否返回不同的值 使用格式: IF(logical_test,value_if_true,value_if_false) 白话:IF(条件,与条件一样时运算这个,与条件不同时运算这个)
公式如下:
'=IF(C13>=$H$10,$I$10,IF(C13>=$H$9,$I$9,IF(C13>=$H$8,$I$8,IF(C13>=$H$7,$I$7,IF(C13>=$H$6,$I$6,IF(C13>=$H$5,$I$5,0))))))
05. DATEDIF函数
函数定义:计算期间内的年数、月数、天数
使用格式:
- =DATEDIF(start_date,end_date,"y")
- =DATEDIF(start_date,end_date,"m")
- =DATEDIF(start_date,end_date,"d")
- =DATEDIF(start_date,end_date,"ym")
- =DATEDIF(start_date,end_date,"yd")
- =DATEDIF(date1,date2,"md")
白话:DATEDIF(开始日期,结束日期,要计算的单位)
注意:
- y:计算满年数,返回值为0以上的整数
- m:计算满月数,返回值为0以上的整数;d:计算满日数,返回值为0以上的整数
- ym:计算不满一年的月数,返回值为1~11之间的整数
- yd计算不满一年的天数,返回值为0~365之间的整数
- md:计算不满意一个月的天数,返回值为0~30之间的整数
例子:
06. COUNTIF函数
函数定义:计算满足条件的单元格计数 使用格式:COUNTIF(range,criteria)
白话:COUNTIF(要找的内容所在的区域,要找的内容)
注意事项:
指定的条件必须用 " " (双引号括起来),如 ">=100、"男" 等.但,当指定条件为引用单元格时无需双引号括住.通配符使用参看SUMIF函数中的通配符说明
例子:
COUNTIF函数几种用法:
- 求包含值139的单元格数量 '=COUNTIF($D$4:$D$14,139)
- 求包含负值的单元格数量 '=COUNTIF($C$4:$C$14,"<0")
- 求不等于0 的单元格数量 '=COUNTIF($C$4:$C$14,"<>0")
- 求大于等于5的单元格数量 '=COUNTIF($C$4:$C$14,">=5")
- 求等于单元格B45中内容的单元格数量 '=COUNTIF($B$4:$B$14,B4)
- 求大于单元格E45中内容的单元格数量 '=COUNTIF($E$4:$E$14,">"&E4)
- 求包含文本内容的单元格数量 '=COUNTIF($B$4:$B$14,"*")
- 求包含六个字符内容的单元格数量 '=COUNTIF($B$4:$B$14,"??????")
- 求在文本中任何位置包含单词"文胸"字符内容的单元格数量 '=COUNTIF($B$4:$B$14,"文胸")
- 求包含以英文"D"(不分大小写)开头内容的单元格数量 '=COUNTIF($B$4:$B$14,"D*")
- 求包含当前日期的单元格数量 '=COUNTIF($E$4:$E$14,TODAY())
- 求大于平均值的单元格数量 '=COUNTIF($D$4:$D$14,">"&AVERAGE($D$5:$D$14))
统计区域内不重复数据数
公式:'{=SUM(1/COUNTIF(D5:D14,D5:D14))}
这里其实输入的是=SUM(1/COUNTIF(D5:D14,D5:D14)),然后按ctrl+shift+enter三键结束。
下面公式加了IF判断是否是空格的嵌套,避免出现#DIV/0!错误.
'{{=SUM(IF(D5:D14<>"",1/COUNTIF(D5:D14,D5:D14)))}}
或者可以用
=SUM(IF(ISBLANK(D5:D14),"",1/COUNTIF(D5:D14,D5:D14))),然后按ctrl+shift+enter三键结束。
07. SUMIF函数
函数定义:对满足条件的单元格的数值求和
使用格式:SUMIF(range,criteria,sum_range)
参数解释:
range:为用于条件判断的单元格区域.指定作为搜索对象的单元格区域 Criteria:为确定哪些单元格将被相加求和的条件,其形式可以为数字、表达式、文本或通配符 Sum_range:是需要求和的实际单元格
几种基本用法:
-
以“文胸”开头的任意文本的销量
=SUMIF($B$4:$B$14,"文胸*",$C$4:$C$14)
注意:如果是“文胸~*”,则此时的“”就是字符,不是通配符,需要准确查找文本为“文胸”的销量合计
-
“文胸”后面一定是三个字符的文本的销量
=SUMIF($B$4:$B$4,"文胸???",$C$4:$C$4)
注意:如果是“文胸~???”,则此时的“?”就是字符,不是通配符,需要准确查找文本为“文胸???”的销量合计
-
销售大于等于5件的销售合计
=SUMIF($C$4:$C$14,">=5",$C$4:$C$14)
-
查找内容为c20的销售合计
=SUMIF($B$4:$B$14,C20,$C$4:$C$14)
08. DCOUNT函数
函数定义:计算满足条件的数值的个数
使用格式:DCOUNT(database,field,criteria)
参数定义:
-
database: 构成列表或数据库的单元格区域.数据库是包含一组相关数据的列表,其中包含相关信息的行为记录,而包含数据的列为字段.列表的第一行包含着每一列的标志项.
-
Field: 指定函数所使用的数据列.列表中的数据列必须在第一行具有标志项.Field可以是文本,即两端带引号的标志项,如"使用年数"或"产量";此外,Field也可以是代表列表中数据列位置的数字:1表示第一列,2表示第二列,等等.
-
Criteria: 为一组包含给定条件的单元格区域.可以为参数criteria指定任意区域,只要它至少包含一个列标志和列标志下方用于设定条件的单元格.
注意:
参数field为可选项,如果省略,函数DCOUNT返回数据库中满足条件criteria的所有记录数
例子:
公式:
-
指定项目的有效支出
=DCOUNT(B3:E9,E3,G3:G4)
这里的结果是2,返回的是10月8日和10月10日的值。
-
指定项目的支出
=DCOUNT(B3:E9,,G3:G4)
参数field为可选项,如果省略,函数DCOUNT返回数据库中满足条件criteria的所有记录数
这里的结果是3,返回的是10月8日,10月10日和10月15日的值。
6.Excel高阶函数
一、关联匹配类
经常性的,需要的资料不在同一个excel表或同一个excel表不同sheet中,资料太多,copy麻烦也不准确,该如何整合呢?这类函数就是用于多表关联或者列栏比对时的场景,而且表越複杂,用得越多。
函数HLOOKUP和VLOOKUP都是用来在表格中查找资料。
1、VLOOKUP
功能:用于查找首列满足条件的元素。 语法:=VLOOKUP(要查找的值,要在其中查找值的区域,区域中包含返回值的列号,精确匹配或近似匹配 — 指定为 0/FALSE 或 1/TRUE)。
2、HLOOKUP
功能:搜索表的顶行或值的阵列中的值,并在表格或阵列中指定的栏的同一lan中返回一个值。 语法:=VLOOKUP(要查找的值,要在其中查找值的区域,区域中包含返回值的行号,精确匹配或近似匹配 — 指定为 0/FALSE 或 1/TRUE)。 区别:HLOOKUP返回的值与需要查找的值在同一列上,而VLOOKUP返回的值与需要查找的值在同一行上。
3、INDEX
功能:返回表格或区域中的值或引用该值。 语法:= INDEX(要返回值的储存格区域或阵列,所在列,所在栏)
4、MATCH
功能:用于返回指定内容在指定区域(某列或者某栏)的位置。 语法:= MATCH (要返回值的储存格区域或阵列,查找的区域,查找方式)
5、RANK
功能:求某一个数值在某一区域内一组数值中的排名。 语法:=RANK(参与排名的数值, 排名的数值区域, 排名方式-0是降序-1是升序-默认为0)。
6、Row
功能:返回储存格所在的列
7、Column
功能:返回储存格所在的栏
8、Offset
功能:从指定的基准位置按列栏偏移量返回指定的引用 语法:=Offset(指定点,偏移多少列,偏移多少栏,返回多少列,返回多少栏)
二、清理处理类
资料处理之前,需要对提取的资料进行初步清理,如清除字串空格,合并储存格、替换、截取字串、查找字串出现的位置等。
清除字串空格:使用Trim/Ltrim/Rtrim 合并储存格:使用concatenate 截取字串:使用Left/Right/Mid 替换储存格中内容:Replace/Substitute 查找文本在储存格中的位置:Find/Search
9、Trim
功能:清除掉字串两边的空格
10、Ltrim
功能:清除储存格右边的空格
11、Rtrim
功能:清除储存格左边的空格
12、Concatenate
语法:=Concatenate(储存格1,储存格2……) 合并储存格中的内容,还有另一种合并方式是&,需要合并的内容过多时,concatenate效率更快。
13、Left
功能:从左截取字串 语法:=Left(值所在储存格,截取长度)
14、Right
功能:从右截取字串 语法:= Right (值所在储存格,截取长度)
15、Mid
功能:从中间截取字串 语法:= Mid(指定字串,开始位置,截取长度)
16、Replace
功能:替换掉储存格的字串 语法:=Replace(指定字串,哪个位置开始替换,替换几个字元,替换成什麽)
17、Substitute
和replace接近,不同在于Replace根据位置实现替换,需要提供从第几位开始替换,替换几位,替换后的新的文本;而Substitute根据文本内容替换,需要提供替换的旧文本和新文本,以及替换第几个旧文本等。因此Replace实现固定位置的文本替换,Substitute实现固定文本替换。
18、Find
功能:查找文本位置 语法:=Find(要查找字元,指定字串,第几个字元)
19、Search
功能:返回一个指定字元或文本字串在字串中第一次出现的位置 ,从左到右查找 语法:=search(要查找的字元,字元所在的文本,从第几个字元开始查找) Find和Search这两个函数功能几乎相同,实现查找字元所在的位置,区别在于Find函数精确查找,区分大小写;Search函数模糊查找,不区分大小写。
20、Len
功能:文本字串的字元个数
21、Lenb
功能:返迴文本中所包含的字元数
三、逻辑运算类
22、IF
功能:使用逻辑函数 IF 函数时,如果条件为真,该函数将返回一个值;如果条件为假,函数将返回另一个值。 语法:=IF(条件, true时返回值, false返回值)
23、AND
功能:逻辑判断,相当于“并”。 语法:全部参数为True,则返回True,经常用于多条件判断。
24、OR
功能:逻辑判断,相当于“或”。 语法:只要参数有一个True,则返回Ture,经常用于多条件判断。
四、计算统计类
在利用excel表格统计资料时,常常需要使用各种excel自带的公式,也是最常使用的一类,重要性不言而喻,不过excel都自带快捷功能。
MIN函数:找到某区域中的最小值 MAX函数:找到某区域中的最大值 AVERAGE函数:计算某区域中的平均值 COUNT函数: 计算某区域中包含数字的储存格的数目 COUNTIF函数:计算某个区域中满足给定条件的储存格数目 COUNTIFS函数:统计一组给定条件所指定的储存格数 SUM函数:计算单元格区域中所有数值的和 SUMIF函数:对满足条件的储存格求和 SUMIFS函数:对一组满足条件指定的储存格求和 SUMPRODUCT函数:返回相应的阵列或区域乘积的和
25、MIN
功能:找到某区域中的最小值
26、MAX函数
功能:找到某区域中的最大值
27、AVERAGE
功能:计算某区域中的平均值
28、COUNT
功能:计算含有数字的储存格的个数。
29、COUNTIF
功能:计算某个区域中满足给定条件的储存格数目 语法:=COUNTIF(储存格1: 储存格2 ,条件) 比如=COUNTIF(Table1!A1:Table1!C100, “YES” ) 计算Table1中A1到C100区域储存格中值为”YES”的储存格个数
30、COUNTIFS
功能:统计一组给定条件所指定的储存格数 语法:=COUNTIFS(第一个条件区域,第一个对应的条件,第二个条件区域,第二个对应的条件,第N个条件区域,第N个对应的条件) 比如:=COUNTIFS(Table1!A1: Table1!A100, “YES”,Table1!C1: Table1!C100, “NO” ) 计算Table1中A1到A100区域储存格中值为”YES”,而且同时C区域值为”NO”的储存格个数
31、SUM
计算储存格区域中所有数值的和
32、SUMIF
功能:求满足条件的储存格和 语法:=SUMIF(储存格1: 储存格2 ,条件,储存格3: 储存格4)
32、SUMIFS
功能:对一组满足条件指定的储存格求和 语法:=SUMIFS(实际求和区域,第一个条件区域,第一个对应的求和条件,第二个条件区域,第二个对应的求和条件,第N个条件区域,第N个对应的求和条件) 比如=SUMIFS(Table1!C1:Table1!C100,Table1!A1: Table1!A100, “YES” ,Table1!B1:Table1B100, “NO” ) 计算Table1中C1到C100区域,同时相应行A列值为”YES”,而且对应B列值为”NO”的储存格的和。
33、SUMPRODUCT
功能:返回相应的阵列或区域乘积的和 语法: =SUMPRODUCT(储存格1: 储存格2 ,储存格3: 储存格4) 比如:=SUMPRODUCT(Table1!A1:Table1!A100, Table2!B1Table2!B100) 计算表格1的A1到A100与表格2的B1到B100的乘积和,即A1B1+A2B2+A3*B3+…
34、Stdev
统计型函数,求标准差。
35、Substotal
语法:=Substotal(引用区域,参数) 汇总型函数,将平均值、计数、最大最小、相乘、标准差、求和、方差等参数化,换言之,只要会了这个函数,上面的都可以抛弃掉了。
36、Int/Round
取整函数,int向下取整,round按小数位取数。 round(3.1415,2)=3.14 ; round(3.1415,1)=3.1
五、时间序列类
专门用于处理时间格式以及转换。
37、TODAY
返回今天的日期,动态函数。
38、NOW
返回当前的时间,动态函数。
39、YEAR
功能:返回日期的年份。
40、MONTH
功能:返回日期的月份。
41、DAY
功能:返回以序列数表示的某日期的天数。
42、WEEKDAY
功能:返回对应于某个日期的一周中的第几天。 默认情况下,天数是 1(星期日)到 7(星期六)范围内的整数。 语法:=Weekday(指定时间,参数)
43、Datedif
功能:计算两个日期之间相隔的天数、月数或年数。 语法:=Datedif(开始日期,结束日期,参数)