python:Excel

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:是需要求和的实际单元格

几种基本用法:

  1. 以“文胸”开头的任意文本的销量

    =SUMIF($B$4:$B$14,"文胸*",$C$4:$C$14)

    注意:如果是“文胸~*”,则此时的“”就是字符,不是通配符,需要准确查找文本为“文胸”的销量合计

  2. “文胸”后面一定是三个字符的文本的销量

    =SUMIF($B$4:$B$4,"文胸???",$C$4:$C$4)

    注意:如果是“文胸~???”,则此时的“?”就是字符,不是通配符,需要准确查找文本为“文胸???”的销量合计

  3. 销售大于等于5件的销售合计

    =SUMIF($C$4:$C$14,">=5",$C$4:$C$14)

  4. 查找内容为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的所有记录数

例子:

公式:

  1. 指定项目的有效支出

    =DCOUNT(B3:E9,E3,G3:G4)

    这里的结果是2,返回的是10月8日和10月10日的值。

  2. 指定项目的支出

    =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(开始日期,结束日期,参数)

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值