Python和Excel的完美结合:常用操作汇总
人型机器少女
11
超级无敌干货，第一时间送达！！！
在以前，商业分析对应的英文单词是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 npimport pandas as pdpd.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)，返回结果：
dfOut[]: 工号姓名 性别 部门0A0001 张伟 男 工程1A0002 王秀英 女 人事2A0003 王芳 女 行政3A0004 郑勇 男 市场4A0005 张丽 女 研发5A0006 王艳 女 后勤6A0007 李勇 男 市场7A0008 李娟 女 工程8A0009 张静 女 人事9A0010 王磊 男 行政10A0011 李娜 女 市场11A0012 刘诗雯 女 研发12A0013 王刚 男 后勤13A0014 叶倩 女 后勤14A0015 金雯雯 女 市场15A0016 王超杰 男 工程16A0017 李军 男 人事输出函数也同理，使用多少列，要不要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, $A$3:$B$7, 2）
python实现：
df = pd.read_excel("test.xlsx", sheet_name=0)defgrade_to_point(x):ifx >= 90:return'A'elifx >= 80:return'B'elifx >= 70:return'C'elifx >= 60:return'D'else:return'E'
df['等级'] = df['语文'].apply(grade_to_point)df
Out[]: 学号姓名 性别 语文 等级0101 王小丽 女 69 D1102 王宝勤 男 85 B2103 杨玉萍 女 49 E3104 田东会 女 90 A4105 陈雪蛟 女 73 C5106 杨建丰 男 42 E6107 黎梅佳 女 79 C7108 张兴 男 91 A8109 马进春 女 48 E9110 魏改娟 女 100 A10111 王冰研 女 64 D案例二
问题：在Sheet1里面如何查找折旧明细表中对应编号下的月折旧额？（跨表查询）
方法：在Sheet1里面的C2:C4单元格输入 =VLOOKUP(A2, 折旧明细表!A$2:$G$12, 7, 0)
python实现：使用merge将两个表按照编号连接起来就行
df1 = pd.read_excel("test.xlsx", sheet_name='折旧明细表')df2 = pd.read_excel("test.xlsx", sheet_name=1) #题目里的sheet1df2.merge(df1[['编号', '月折旧额']], how='left', on='编号')Out[]: 编号 资产名称 月折旧额0 YT001 电动门 13991 YT005 桑塔纳轿车 11472 YT008 打印机 51案例三
问题：类似于案例二，但此时需要使用近似查找
方法：在B2:B7区域中输入公式=VLOOKUP(A2&"*", 折旧明细表!$B$2:$G$12, 6, 0)
python实现：这个比起上一个要麻烦一些，需要用到一些pandas的使用技巧
df1 = pd.read_excel("test.xlsx", sheet_name='折旧明细表') df3 = pd.read_excel("test.xlsx", sheet_name=3) #含有资产名称简写的表df3['月折旧额'] = 0for i in range(len(df3['资产名称'])): df3['月折旧额'][i] = df1[df1['资产名称'].map(lambda x:df3['资产名称'][i] in x)]['月折旧额']
df3Out[]: 资产名称 月折旧额0 电动 13991 货车 24382 惠普 1323 交联 101334 桑塔纳 11475 春兰 230案例四
问题：在Excel中录入数据信息时，为了提高工作效率，用户希望通过输入数据的关键字后，自动显示该记录的其余信息，例如，输入员工工号自动显示该员工的信命，输入物料号就能自动显示该物料的品名、单价等。
如图所示为某单位所有员工基本信息的数据源表，在"2010年3月员工请假统计表"工作表中，当在A列输入员工工号时，如何实现对应员工的姓名、身份证号、部门、职务、入职日期等信息的自动录入？
方法：使用VLOOKUP+MATCH函数，在"2010年3月员工请假统计表"工作表中选择B3:F8单元格区域，输入下列公式=IF($A3="","",VLOOKUP($A3,员工基本信息!$A:$H,MATCH(B$2,员工基本信息!$2:$2,0),0))，按下【Ctrl+Enter】组合键结束。
python实现：上面的Excel的方法用得很灵活，但是pandas的想法和操作更简单方便些
df4 = pd.read_excel("test.xlsx", sheet_name='员工基本信息表')df5 = pd.read_excel("test.xlsx", sheet_name='请假统计表')df5.merge(df4[['工号', '姓名', '部门', '职务', '入职日期']], on='工号')Out[]: 工号 姓名 部门 职务 入职日期0 A0004 龚梦娟 后勤 主管 2006-11-201 A0003 赵敏 行政 文员 2007-02-162 A0005 黄凌 研发 工程师 2009-01-143 A0007 王维 人事 经理 2006-07-244 A0016 张君宝 市场 工程师 2007-08-145 A0017 秦羽 人事 副经理 2008-03-06案例五
问题：用VLOOKUP函数实现批量查找，VLOOKUP函数一般情况下只能查找一个，那么多项应该怎么查找呢？如下图，如何把张一的消费额全部列出？
方法：在C9：C11单元格里面输入公式=VLOOKUP(B$9&ROW(A1),IF({1,0},$B$2:$B$6&COUNTIF(INDIRECT("b2:b"&ROW($2:$6)),B$9),$C$2:$C$6),2,)，按SHIFT+CTRL+ENTER键结束。
python实现：vlookup函数有两个不足（或者算是特点吧），一个是被查找的值一定要在区域里的第一列，另一个是只能查找一个值，剩余的即便能匹配也不去查找了，这两点都能通过灵活应用if和indirect函数来解决，不过pandas能做得更直白一些。
相关文章
-
现状揭秘：Python岗位大厂50K起？程序员：心态崩了！ 屠杀各种榜单，拿下语言排行榜的Python，薪酬真的如同网传开挂了吗？笔者在脉脉上发现了这样的一个信息： 但Python真的这么火？笔者准备去全网帮你们"打探"一下，让大家了解真实的Python市场。 （数据来自看准网2019年11.4日数据）从上图看，P
饯莸抟
9 2
0 条评论
-
一些科学家开始使用比Python性能更强大的新编程语言了，它和Python一样能够"立等可取"，而且计算能力还更强。 原文作者：JeffreyM.Perkel2015年，生物信息学家JohannesK?ster还是（用他自己的话说）"差不多全职写Python的"。他当时已经用Python写过一个备受欢迎的工具--流程管
乱世知己琪哪
8 1
0 条评论
-
为什么Python不是未来的编程语言？ 作者|RheaMoutafis 译者|弯月，责编|郭芮 出品|CSDN（ID：CSDNnews） 以下是译文：Python经过了几十年的努力才得到了编程社区的赏识。自2010年以来，Python得到了蓬勃发展，并最终超越了C、C#、Java和JavaScript。 但是，这种趋
天王地虎happy
4 0
0 条评论
-
Python之父，现在成了微软的打工人。 没错，全世界程序员们最喜欢的编程语言的创造者，自述耐不住退休生活的寂寞，重返岗位发光发热。 GuidoVanRossum，打算去微软做些什么？又为什么选择微软？ Python之父加入微软干什么？ 几个小时前，GuidoVanRossum本人发推宣布了这个消息，他说："退休生
路茫之间
5 2
0 条评论
-
大数据文摘出品 来源：medium 编译：陈之炎、coolboy 自从20世纪90年代初发布以来，Python一直相当火爆，在这二十多年里，它的流行程度远远超过了C、C#、Java甚至Javascript。 虽然Python在数据科学和机器学习领域占主导地位，甚至是科学和数学计算领域的主角，但与Julia、Swift
ahdhtang
5 0
0 条评论
-
Bottle是一个超轻量级的python库。说是库，其本身只由一个4000行左右的文件构成，并且不需要任何依赖，只靠python标准库即可运作。 和它本身的轻便一样，Bottle库的使用也十分简单。相信在看到本文前，读者对python也已经有了简单的了解。那么究竟何种神秘的操作，才能用百行代码完成一个服务器的功能？让我
超级超级
4 3
0 条评论
-
终于，Python3.11正式版发布了！ 2020年1月1日，Python官方结束了对Python2的维护，这意味着Python2已完全退休，进入了Python3时代。打从进入3版本以来，Python官方已经发布了众多修改分支，现在来到了最新的版本Python3.11。 其实研究界有个不公开的秘密，那就是Python
書与南柯RD
6 0
0 条评论
-
据澎湃新闻近日消息，山东省在其最新出版的小学信息技术六年级教材中，加入了Python的内容。在此之前，编程界也一直有传言，称浙江省将对中学信息技术教材进行改动，弃VB（VisualBasic6.0）而选用Python。 事情真的会如程序员们期待的那样发展吗？Python语言又为什么突然受到了格外的重视呢？ VB做错了什
高数次
4 1
0 条评论
-
模拟键盘操作执行自动化任务，我们常用的有pyautowin等自动化操作模块。但是这些模块有一个很大的缺点，编译的时候非常依赖windows的C语言底层模块。 今天介绍的这个模块叫做keyboard它有一个最大的优点：纯Python原生开发，编译时完全不需要依赖C语言模块。一行命令就能完成安装，非常方便。 1.准备 首
爱在你身边白羊
6 8
0 条评论
-
来源：内容由半导体行业观察（ID：icbank）编译自hpcwire，谢谢。 近日，加州大学洛杉矶分校引入了一种新的硬件描述语言PyGears，以实现基于可重用组件和高级Python构造的敏捷芯片设计理念。PyGears是对快速发展的软件世界的回应，这要求硬件设计与可扩展和智能未来的需求保持同步。 据官网介绍PyGea
文仔230
7 0
0 条评论
