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班语文测验成绩表，如何根据语文成绩返回其字母等级？

方法：在H3:H13单元格区域中输入=VLOOKUP（G3, 3: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:
        ret