Python吊打Excel？7年Excel的数据分析师不服

有人说Python吊打Excel,作为一个有着7年Excel的数据分析师，我表示不服。

看完我这个回答我相信你不用再看别人的回答了！

昨天猫双11成交额破3723亿、京东破2000亿…你贡献多少？

一个数据分析师能从这些数据中分析出来什么问题？

 

不要告诉我消费能力是女人，小孩，老人，狗和男人。这个大家都知道啊。

干货预警！Excel用的好，下班会很早，高效的选出双11最优惠的产品，利益最大化，作为一个Excel的深度使用者，Excel的功能实在是太多了，函数都有471个，彻底精通Excel的方方面面可以说几乎是不可能，但是能熟练运用Excel技巧，会大大提高你的工作效率。

要想成为Excel数据处理大神，你首先要掌握公式和数据透视，甚至熟练使用VBA会了这些之后你还要学会PowerQuery快速查询，因为VBA的强项并不是数据处理。

（1）方便的快捷键操作

你是不是羡慕那些大佬上班不带鼠标？每天背着超薄本，很有范是不是。掌握一些快捷键你可以哦。

• Ctrl+A：直接选中并选择整个表格区域，复制数据的时候是不是再不用按着鼠标左键一直拉到手软了；
• Ctrl+Shift+上/下：选中光标所在的列所有的数据；
• Ctrl+Shift+左/右：选中光标所在的行所有的数据；
• Ctrl+Fn+”pg up”/”pg down”：瞬间切换至上一个或下一个工作sheet，这几乎是甩掉鼠标的高频快捷键；
• Alt+F1：可以立即创建当前数据范围内的图表；
• F2：编辑活动单元格并将插入点放在单元格内容的结尾，再也不用双击单元格去编辑了；
• Shift+F3：显示插入函数对话框，不用每次需要插入函数就用鼠标去工具栏一个一个找了；
• F4：重复上一个命令或操作，一个变态般存在的快捷键；
• Ctrl+F4：关闭当前的工作簿，连叉号都不用点了有没有。

 

(2)最常使用的公式

最常用函数

求和：sum

平均值：average

计数：count

最大值：max

最小值：min

查找重复内容公式：=IF(COUNTIF(A:A,A2）>1，"重复",")。

用出生年月来计算年龄公式：=TRUNC((DAYS360(H6，"2008/8/25“，FALSE))/360，O)/。

从输人的18位身份证号的出生年月计算公式：

=CONCATENATE(MID(E2,7,4）,"/"，MID（E2，11，2),"/"，MID

（E2,13,2))。

从输人的身份证号码内让系统自动提取性别，可以输入以下公式：

=IF（LEN（C2）=15,lF（MOD（MID（C2，15，1），2）=1，"男"，"女"),

IF（MOD(MID(C2,17,1),2）=1，"男"，"女"))公式内的“C2"代表的是输人身份证号码的单元格。

等级：=IF（K2>=85，“优",IF（K2>=74，"良"，IF（K2>=60，"及格"，"不及格")))

学期总评：=K2*3+M2*0.3+N2*0.4一一一假设K列、M列和N列分别存放着学生的"平时总评"、“期中“、“期末"三项成绩；

最高分：=MAX(K2:K56）一一一求K2到K56区域（55名学生》的最高分；

最低分：=MIN(K2：K56）——求K2到K56区域(55名学生)的最低分；

分数段人数统计：

（1）=COUNTlF(K2:K56,"100"）一一求K2到K56区域100分的人数；假设把结果存放于K57单元格；

（2）=COUNTIF(K2:K56,">=95“）一K57一一求K2到K56区域95～99.5分的人数；假设把结果存放于K58单元格；

（3）=COUNTIF(K2：K56,“>=90"）—SUM(K57：K58）一一求K2到K56区域90一94.5分的人数；假设把结果存放于K59单元格;

(4)=COUNTIF(K2：K56,“>=85"）一SUM（K57：K59）一一求K2到K56区域85一89.5分的人数；假设把结果存放于K60单元格;

(5)=COUNTIF(K2：K56，“>=70"）-SUM（K57：K60）一一求K2到K56区域70一84.5分的人数；假设把结果存放于K61单元格;

(6)=COUNTIF(K2：K56,“>=60"）一SUM（K57：K61）一一求K2到K56区域60一69.5分的人数；假设把结果存放于K62单元格;

(7)=COUNTlF(K2：K56，“<60"）一一求K2到K56区域60分以下的人数；假设把结果存放于K63单元格;

说明：COUNTIF函数也可计算某一区域男、女生人数

如：=COUNTIF(C2:C351，“男"）一一求C2到C351区域(共350人)男性人数；

优秀率:=SUM(K57：K60)/55*100

及格率：=SUM(K57:K62)/55*100

标准差：=STDEV(K2:K56）一一求K2到K56区域(55人)的成绩波动情况(数值越小,说明该班学生间的成绩差异较小,反之,说明该班存在两极分化)；

条件求和：=SUMlF(B2:B56,"男",K2:K56）一一假设B列存放学生的性别，K列存放学生的分数,则此函数返回的结果表示求该班男生的成绩之和；

多条件求和：{=SUM(lF(C3:C322="男",IF(G3:G322=1,1,0)))}一一假设c列(C3:322区域)存放学生的性别,G列(G3:G322区域)存放学生所在班级代码(1、2、3、4、5），则此函数返回的结果表示求一班的男生人数；这是一个数组函数，输完后要按Ctrl+Sh+Enter组合键（产生"{}"')。"{}"不能手工输人，只能用组合键产生。

根据出生日期自动计算周：=TRUNC((DAYS360(D3,NOW()))/360,0）

 

整理了若干个小时，看到这里如果你感觉到干可以喝口水继续点赞、收藏继续观看。

（3）数据透视图

你做出来的Excel是不是这样？

 

而同事们做出来的都是这样的。

 

 

 

 

是不是有不小的差距呢？

做出这样的图表就需要以下步骤了：

01、首先，将光标放在表格数据单元格中，按Ctrl+A选中全部表格（有数据内容的表格）；

在表格右下角可以找到一个小方块，切换到【图表】选项卡；

选择你需要的图表类型就可以自动生成图表了。

 

02、如何复制图表格式

光标选中已经生成的图表绘图区并复制；

选中想要复制的图表绘图区域；

点击【选择性粘贴】-【格式】，就可以将复制的图表格式粘贴到指定图表中啦。

 

03、如何修改图例

当我们想要修改图例时，请先选中图例，然后在顶部编辑栏，修改并编辑需要修改的内容即可。

 

04、如何创建动态图表

选中已经生成图例的表格；

点击【插入】-【筛选器】-【切片器】；

生成切片栏后，点击相关内容，图表即可随着切换。

 

05、如何添加平均参考线

在Excel工作表，在数据表格中添加一个平均值的辅助列；

在辅助系列单元格中输入average函数计算出平均值，下拉平均值单元格得到数据；

然后选中全部数据，插入柱状图，在图表中选择“平均值系列”；

选择“更改图表类型”，将辅助列图形改为折线图，点击“确定”就可以完成绘制啦！

 

06、如何调整标签位置

在Excel软件中打开制作好图表的工作簿；

右键调出【设置数据标签格式】选项卡，然后调整数据标签所在的位置即可。

 

07、Excel折线转为平滑线

选中折线图，鼠标右键调出菜单栏，然后选择【设置数据系列格式】；

在弹出的对话框中，左侧勾选【线型】；

右侧勾选【平滑线】；

点击【关闭】窗口，图表的折现样式就转变为了平滑线样式。

 

08、Excel如何快速添加标题

选中图表标题，显示标题；

在单元格内修改标题，图表内的标题也会随之改变；

按住Ctrl键，选中图表和图表标题，点击【图片工具】-【组合】，将图表标题与图表组合在一起就好了。

 

Excel是必须要精通的，注意是精通，起码要学会用VBA；

(4)VBA的使用

虽然以上提到的功能，已经基本能够满足大多数人工作的需求了，但即使你把所有的快捷键和函数都牢记于心，仍然需要重复的操作一些功能，对于一些需要自动化实现的操作，Excel还提供了VBA的编程功能，让你可以根据自己的需要去编写代码或录制宏文件。

比如：让你统计100份财务报表中收益的总额，你可能会去打开每一张表统计后再求和，这样做虽然没毛病，但会耗费大量的时间和精力，而如果用VBA编程功能，只需一段精悍的代码就能完美解决问题，电脑配置好点1分钟就能全部计算完毕。

一位高盛分析师曾经这样表达对VBA的感情：“学会了VBA以后，你简直觉得Excel模型可以帮你煮咖啡，做晚饭，甚至帮你遛狗。”

VBA真的这么有魔力？不信的话我们就随便欣赏几个VBA大神们的“奇技淫巧”吧。

有位BCG的顾问需要按照顺序打印100张表格，于是用Excel实现了一键自动打印

 

有位分析师为了治好自己的重度拖延症，于是做了一个任务管理器，一键自动安排好每天的生活：

 

还有动漫爱好者用Excel做起了动画：

 

毫不夸张的说，学会了VBA 以后，你会情不自禁的产生一种“上可摘星揽月，下可五洋捉鳖”的快感，天大的问题到你手里也能解决。

（6）快速查询工具

终极大招，学习Excel自身的数据分析插件——Power Query（数据查询）、Power Pivot（数据建模）、Power View（数据可视化）；

它现在安静的躺在Excel的【数据】选项卡下面，可能很多人压根没有注意过

 

点击进去，你就会发现另有一番天地

 

简直就是另外一个软件，有木有！

其实不用被这些吓到了，它使用起来十分简单，至少大部分常用的功能都通过点点鼠标就可以完整的。

那么它到底有什么用呢？为什么要从Excel界面来到这个奇怪的地方？

PowerQuery主要用于大数据清洗，就是将各种杂乱的数据整理成我们想要的样子，它的特点：

可能说到这里大家还是不知道它到底有什么用，怎么用，下面以几个实例来看看它的用法。

如何将多个工作簿的数据合并到一张表上？

可能不同的人有不同的做法？

普通青年用万能的复制粘贴
二逼青年网上百度VBA代码一键汇总
文艺青年找个崇拜自己的实习小MM帮忙

其实都不必这么麻烦，Power Query来了。

下面来看一下PQ是如何汇总多文件的数据的:

假设有一个连锁型零售商店，有北京、广州、杭州三个城市门店，总部每月需要汇总每个城市门店销售明细数据，现在需要汇总2016年1-3月的销售明细，共9个工作簿，保存在一个文件夹内，结构如下：

 

由于只是数据处理的过程，下面的演示就在Excel2016进行，使用Excel2010、Excel2013的插件以及在Power BI Desktop中的操作也都是一样的。

首先我们新建一张空白Excel工作簿，点击"数据"选项卡下"新建查询"，从文件夹获取数据：

 

浏览找到该文件夹的路径，确认后出现这个界面，

 

点击"编辑",进入查询编辑器：

 

数据就储存在[Content]列,其他列都是每个工作簿的信息，现在要做的就是把Content的内容提取出来，点击"添加列"选项卡，添加自定义列，

 

自定义列中输入公式=Excel.Workbook([Content])，这里要注意严格区分大小写，不能写错了，这就是提取Excel格式数据的M函数（关于M函数后面会单独介绍）。

数据整理完成后：

步骤一：获取数据

随便新建一个Excel工作簿，点击数据>获取数据>自文件>从工作簿

步骤二：选择一个表，加载入PowerQuery

选择需要合并的工作簿，然后出现如下界面，

 

随便选择一个表，点击编辑。

步骤三：删除系统步骤

进入powerquery编辑器后，找到右边步骤面板，把【源】之后的步骤全部删除

步骤四：展开Data

点击Data列右侧的双箭头，如下图，点击确定。

 

步骤五：数据整理

到这里已经合并完成，

然后提升标题，删除需要的列。

步骤六：上载数据

然后数据就可以在Excel工作表中看到了。

仅仅点击几下鼠标，没有输入任何公式和代码，合并多个sheet的功能就完成了。

最后说一下，我是一个有着7年工作经验的Excel数据分析师，关于Excel常用的百个快捷技巧，学习干货和视频。需要的伙伴可以滴滴我，找我拿干货，分享给大家。对于学习Excel有任何问题（学习方法，学习效率，如何进阶）都可以问我。希望你也能凭自己的努力，成为下一个优秀的Excel高手！

本着高效学习的目的，帮助各个专业需求的同学进阶成Excel高手
整理出一份史上最权威的Excel学习礼包，这份大礼包有什么：
这份大礼包有什么？
· Office+WPS表格零基础教程
· 46个Excel表格制作高级教程
· Excel图表实战技巧精粹教程
· 世界500强Excel经典图表模板
· Excel使用思维电子参考书PDF
· Excel高级设计案例参考书PDF
请按需下载
▼
01
Office+WPS表格零基础教程
Office2013-2016零基础表格制作教程
纯MP4格式WPS零基础表格制作教程
▼

 

来自：Excel小技巧