python与excel-用Python完成Excel的常用操作

在以前，商业分析对应的英文单词是Business Analysis，大家用的分析工具是Excel，后来数据量大了，Excel应付不过来了（Excel最大支持行数为1048576行），人们开始转向python和R这样的分析工具了，这时候商业分析对应的单词是Business Analytics。

其实python和Excel的使用准则一样，都是[We don't repeat ourselves]，都是尽可能用更方便的操作替代机械操作和纯体力劳动。用python做数据分析，离不开著名的pandas包，经过了很多版本的迭代优化，pandas现在的生态圈已经相当完整了，官网还给出了它和其他分析工具的对比：

本文用的主要也是pandas，绘图用的库是plotly，实现的Excel的常用功能有：

Python和Excel的交互

vlookup函数

数据透视表

绘图

以后如果发掘了更多Excel的功能，会回来继续更新和补充。开始之前，首先按照惯例加载pandas包：

import numpy as np

import pandas as pd

pd.set_option('max_columns', 10)

pd.set_option('max_rows', 20)

pd.set_option('display.float_format', lambda x: '%.2f' % x) # 禁用科学计数法

Python和Excel的交互

pandas里最常用的和Excel I/O有关的四个函数是read_csv/ read_excel/ to_csv/ to_excel，它们都有特定的参数设置，可以定制想要的读取和导出效果。比如说想要读取这样一张表的左上部分：

可以用pd.read_excel("test.xlsx", header=1, nrows=17, usecols=3)，返回结果：

df

Out[]:

工号 姓名 性别 部门

0 A0001 张伟 男 工程

1 A0002 王秀英 女 人事

2 A0003 王芳 女 行政

3 A0004 郑勇 男 市场

4 A0005 张丽 女 研发

5 A0006 王艳 女 后勤

6 A0007 李勇 男 市场

7 A0008 李娟 女 工程

8 A0009 张静 女 人事

9 A0010 王磊 男 行政

10 A0011 李娜 女 市场

11 A0012 刘诗雯 女 研发

12 A0013 王刚 男 后勤

13 A0014 叶倩 女 后勤

14 A0015 金雯雯 女 市场

15 A0016 王超杰 男 工程

16 A0017 李军 男 人事

输出函数也同理，使用多少列，要不要index，标题怎么放，都可以控制。

vlookup函数

vlookup号称是Excel里的神器之一，用途很广泛，下面的例子来自豆瓣，VLOOKUP函数最常用的10种用法，你会几种?

案例一

问题：A3:B7单元格区域为字母等级查询表，表示60分以下为E级、60~69分为D级、70~79分为C级、80~89分为B级、90分以上为A级。D:G列为初二年级1班语文测验成绩表，如何根据语文成绩返回其字母等级？

image.png

方法：在H3:H13单元格区域中输入=VLOOKUP（G3, $A$3:$B$7, 2）

python实现：

df = pd.read_excel("test.xlsx", sheet_name=0)

def grade_to_point(x):

if x >= 90:

return 'A'

elif x >= 80:

return 'B'

elif x >= 70:

return 'C'

elif x >= 60:

return 'D'

else:

return 'E'

df['等级'] = df