办公自动化/无比有趣的在公司抓小人

去社区提问

简介:月末各部门的周报表格需要合并汇总,根据周报内容,得到部门和个人的任务完成情况。本节内容教你用Python批量处理Excel文件,并按要求统计表格数据。

无比有趣的在公司抓小人

令人头大的周报

震惊!!!好不容易下班轻松的周五夜晚,小张为何深夜惊醒?又是什么让一直兢兢业业的小张在周一晨会遭到领导的批评?

原来…是周报忘交了。

是它,就是它,让职场人闻风丧胆,提起就头大,却又不得不乖乖按时提交的周报。

它就像前女友,你们之间的关系微妙得像最熟悉的陌生人,明明不爱了,提起来的时候却又没法割舍。

如果说,不光要写周报,还要定期整理汇总全公司的周报,是一种怎样的体验?

我的好朋友胖丫,用实力验证了一句话“写周报难,整理周报更是难上加难。”

每周每月几十个存储各部门周报的表格汇总,还要从中整理出每人的任务完成情况和部门整体进度,搞得胖丫心力交瘁。

作为胖丫的小伙伴我是看在眼里,疼在心间。

各种铺天盖地的广告都说Python可以实现办公自动化,那是不是也能解决胖丫的问题,带她逃离苦海?

今天就跟我一起给胖丫减个负,把原来一下午才能搞定的工作,用代码高效解决。

首先我们来梳理一下胖丫要完成的工作:

这里汇总了6月以来12个部门每周的周报,按照提交日期分开,共48个Excel表格。表格的内容是这样的:

每张表格包含了八个字段:

  • 部门
  • 日期
  • 工作内容
  • 是否完成
  • 延期天数
  • 责任人
  • 本周进度
  • 状态

胖丫需要做的就是开一瓶雪花啤酒,然后独自开始勇闯天涯。

哦说错了,她的工作非常严肃:

需要分别按照部门汇总合并周报表格,以做留存整理。统计每人月度完成情况,部门整体完成率,以及公司本月目标完成情况。

别说统计数据,光看着这几十个表格就够让人心累。

现在开始今天的精彩表演~胖丫同学也已经跃跃欲试了。

特别提示:如果暂时不理解代码的含义也没有关系~只通过这些案例了解Python用途就达到了目标。12课时开始将进入详细的Python语法学习,要继续加油呀。

查看文件

在这里我为你介绍一下Python中函数的定义和使用。前面几个案例中,都提到过“调用方法”或“调用函数”。当然在编程中也可以自由定义你的函数,实现想要的功能,之后直接拿来使用。

就好比你想吃鱼香肉丝,又不想费劲去网上找食谱调味,现成的鱼香肉丝调料包就很好地解决这个问题,每次做的时候只用拿出来丢进锅里用就好,省去搜菜谱,找调料配比的时间。

在对周报进行操作前,我们想先查看一下共有哪些表格。定义一个函数,实现的功能是扫描指定路径下的表格文件。

给函数起个名字叫做get_allfile_msg。

  1. import os
  2. '''
  3. 获取文件夹的路径,该路径下的所有文件夹,以及所有文件
  4. root #当前目录路径
  5. dirs #当前路径下所有子目录
  6. files #当前路径下所有非目录子文件
  7. '''
  8. def get_allfile_msg(file_dir):
  9. for _, _, files in os.walk(file_dir):
  10. return [file for file in files if file.endswith('.xls') or file.endswith('.xlsx')]

接下来就能直接用鱼香肉丝调料包了,调用这个方法,就可以得到所有文件。

代码练习:

编程区

  1. files = get_allfile_msg(r'excel/input')
  2. files

终端区

导入后续操作所要使用的我们的老朋友:pandas模块。查看一下财务部门6月7日周报的前五行内容。

代码练习:

编程区

  1. import pandas as pd
  2. df = pd.read_excel('excel/input/2020-6-7财务部周报.xlsx', index=False)
  3. df.head()

终端区

下面我们把48个Excel表格合并在一张大表中,方便统计每个人的任务完成情况。胖丫以往需要一个个打开表格复制粘贴,还生怕不小心漏掉或重复复制了哪一张表。

现在有我就不一样了,看下面的几行代码:

代码练习:

编程区

  1. df_all = pd.DataFrame()
  2. for file in files:
  3. df_all = pd.concat([df_all, pd.read_excel('./excel/input/' + file)])
  4. df_all

终端区

搞定~真是羡慕胖丫有我这样的好朋友。

下面来查看一下这张大表中都包含哪些日期和部门:

代码练习:

编程区

  1. df_all['日期'].value_counts().index

终端区

代码练习:

编程区

  1. df_all['部门'].value_counts().index

终端区

表格信息搜集完毕,是时候展现真正的技术了~

个人任务情况

第一步先根据周报内容帮胖丫统计每个员工的任务完成情况。根据领导的指示,需要统计出6月的四周中,每周都按时完成任务的员工名单,以及超过三周未完成任务名单。

按部门+责任人排序

刚才合并好的表格中,直接把各部门不同日期的表格拼接在一起,没有做数据顺序上的处理。

现在要统计个人任务完成状况,需要按照‘部门’和‘责任人’排序,将同一员工四周的周报内容放在相邻的几行中,就能非常清晰地查看每个人的周报内容和任务情况。

代码练习:

编程区

  1. df_sort = df_all.sort_values(by=['部门', '责任人']).reset_index(drop=True)
  2. df_sort

终端区

现在得到我们想要的效果了。将以上结果生成新的表格,命名为“个人任务完成情况”。

  1. df_sort.to_excel('./excel/个人任务完成情况.xlsx', index=False)

未完成次数大于等于3的员工统计

按部门和责任人排序处理好表格,就可以帮胖丫统计员工工作情况了~

要把大象放冰箱,总共分三步。统计这条数据也一样跟我三步走。

1.用bool值换掉’是’/‘否’,打开bool值的冰箱门

根据‘是否完成’一列的数据,在原表格中新增一列,命名为‘是否完成bool’。

我知道到这里你可能和胖丫一样有些摸不着头脑,没关系,我们先来看下效果。

代码练习:

编程区

  1. df_sort['是否完成bool'] = df_sort['是否完成'].apply(lambda x:True if x=='是' else False)
  2. df_sort

终端区

机智的你一定发现了,新加入的字段中,‘True’对应着’是否完成‘这列的‘是’,’False’对应‘否’。

毕竟你眼前的这台冰冷的计算机不认识汉字,看不明白‘是’和‘否’的含义。

这是Python中一种数据类型:布尔(bool)型,它只有True和False两个值,常用于判断和比较数据。

根据字段中‘True’和‘False’的数量可以得到任务完成的次数和状况。

2.分组后计算延期数据

按照部门和责任人分组,统计每个员工‘是否完成bool’一列值为‘False’的个数(4-True的数量),就可以得到6月中未完成任务次数。

代码练习:

编程区

  1. df_delay = 4 - df_sort.groupby(['部门', '责任人'])['是否完成bool'].sum()
  2. df_delay

终端区

这样就统计到了所有员工的未完成任务的次数。

3.筛选数据,成功关门

得到了所有员工的任务完成情况,接下来只需要做一个简单的筛选,就能得到胖丫想要的结果啦~

下面一行代码帮我们筛选出延期次数大于等于3的员工名单。

代码练习:

编程区

  1. df_delay[df_delay>=3]

终端区

哦莫,一目了然,上面几位同学,你们可能要被领导谈话了,请提前想好借口。

看来胖丫的同事们还是很有必要和她搞好关系的,预防针工作也能提前安排上。

四次均按时完成的人

小红花表扬时间到,让我们来康康,是谁6月以来,一直坚持勤奋工作呢?

筛选出延期次数为0的员工名单就可以看到结果。

代码练习:

编程区

  1. df_ontime = df_delay[df_delay==0]
  2. df_ontime

终端区

由于人比较多,我们把名单写到文件里:

  1. df_ontime.to_excel('./excel/按时完成名单.xlsx')

现在为止,员工任务完成情况统计就完成了,Python帮助我们用更短的时间解决了更多问题。胖丫的办公速度也即刻起飞。

部门任务完成情况

统计完了员工工作进度状况,那么哪个部门又是6月的mvp?

下面就和我一起,放眼各部门的任务完成情况,帮胖丫统计统计数据。

首先来看看每周各部门任务完成率。

1.每周完成率

统计员工任务完成情况时我们是按照‘部门’和‘责任人’分组。有奖竞猜:

现在想要统计部门每周任务完成率应该如何给数据分组呢?

来看下面的代码:

代码练习:

编程区

  1. department_group = df_sort.groupby(['部门', '日期'])['是否完成bool']
  2. df_department_complete_rate = department_group.sum()/department_group.count()
  3. df_department_complete_rate

终端区

按照‘部门’和‘日期’来分组就ok啦,机智的你一定也答对了,稍后别忘记添加小助手微信,领取我今天为你准备的精美礼包哦~

2.总完成率

再来计算部门月度整体完成率:

代码练习:

编程区

  1. department_group = df_sort.groupby(['部门'])['是否完成bool']
  2. df_department_complete_rate = department_group.sum()/department_group.count()
  3. df_department_complete_rate

终端区

人事,兼职,研发,财务,转化部门都100%完成了6月计划。看起来,产品,管理部门的下个月可还得继续加油哇。

公司任务完成情况

每周完成率

最后来帮胖丫统计公司整体任务完成情况。胜利的曙光就在前方~

按照日期来划分,计算每周完成率:

代码练习:

编程区

  1. week_group = df_sort.groupby(['日期'])['是否完成bool']
  2. df_week_complete_rate = week_group.sum()/week_group.count()
  3. df_week_complete_rate

终端区

可以看到6月公司每周任务完成率大概稳定在80%左右。

整体完成率

下面再计算一下公司6月整体的任务完成率:

代码练习:

编程区

  1. df_complete_rate = df_sort['是否完成bool'].sum()/df_sort['是否完成bool'].count()
  2. df_complete_rate

终端区

按部门生成工作表页

为了让每月周报能更有序地留存,胖丫的最后一项工作是要把所有周报按照部门生成到同一Excel的不同工作表中。

只需要按照‘部门’划分数据,再放置到相应的工作表中就大功告成了~别忘记留存周报时删除掉我们刚才新增的‘是否完成bool’一列的数据。

  1. departments = df_sort['部门'].value_counts().index
  2. writer = pd.ExcelWriter('./excel/按部门分页.xlsx')
  3. for department in departments:
  4. df_dep = df_sort[df_sort['部门']==department].copy()
  5. df_dep.drop('是否完成bool', inplace=True, axis=1)
  6. df_dep.to_excel(writer, department, index=False)
  7. writer.save()

来看一下最终效果:

到这里,我们就帮助胖丫搞定了这份枯燥差事的全部内容,速度很快对不对,而且不用担心出错。

我们一起完成了周报的批量合并,分页整理,还统计了个人,部门,公司的任务完成情况,导出生成了相应名单。

比胖丫一个个复制粘贴不知道快了多少倍。也许你在工作中也常遇到和胖丫同学相似的麻烦,不知道如何解决。类似周报合并这样的重复性工作非常适合用Python解决,通过今天的小例子相信你也可以感受到~

如果你还想继续了解更多Python知识相关知识,欢迎扫描下方二维码?添加班主任微信,我和一群可爱的朋友们等着你,希望与你一起交流,一起进步。

扫描下方二维码,添加班主任微信

办公自动化 1/3

无比有趣的在公司抓小人

1.0x

恭喜完成本课时的学习!
坚持学习

行动力超过

累计学习

学习下一课时