干货预警,作为一名数据分析师,Excel就像我的右手一样,无时无刻不在用!熟悉到不能再熟悉!我把这几年的 Excel 经验整理了出来,分享给各位,希望能帮助到你们!
这是一个过来人的经验:“刚入职场,觉得Excel就是一张表,很简单;后来,发现Excel是一本书,需要学;现在惊觉,Excel是一套系统,功能强大;希望未来,Excel不是全世界”
所以,恭喜你,不管你身处哪个阶段,能意识到Excel的重要性,那就为时未晚!
那,如何系统学Excel?
今天给 Excel 新手们讲解一下 Excel 学习的顺序,让大家学习 Excel 少走弯路,事半功倍。
建议把Excel 上菜单栏按钮全都熟悉一遍。这能让你快速熟悉excel,了解excel具备哪些功能。其实大部分人对excel的功能并不熟悉,想要执行某些操作时总是在菜单栏中翻来覆去,非常浪费时间。因此这一步非常必要。完成之后下一步就是了解excel中的一些基础知识以及基本操作方法。
对于EXCEL的新手,首先买一本excel的入门教程,不过看书是一个很累的事情,如果不想看书可以看一个系列课程,系统的学习excel,这样可以学的快一些,在这个过程里面,学习者大致了解到excel的基本操作方法和常用功能,诸如输入数据,查找替换,设置单元格格式,排序,筛选,删除重复项等等...这个过程相对简单,学习进度很快。
对于初学者,我的建议是跟着老师学习,最好是同时有长期教课经验和牛逼工作经验的老师,保证他确实是一个实战数据分析大佬,又确实能教会别人,两者缺一,要不然就是把你教成书呆子,要不就是大肚茶壶倒饺子——有货说不出。
我个人推荐知乎的一个课程,教程制作人是前IBM数据分析大佬,知乎顶级大 V猴子,课程是结合国内互联网一线大厂的案例,从基础讲起,用案例讲知识点,带练数据分析工具的同时,也构建数据分析思维,能让大家先有系统扎实的基础,对小白来说是很友好的选择。带你轻轻松松学习 Excel !
你还需要掌握以下几个快捷键
不要以为掌握了所有的菜单功能和快捷键就是EXCEL高手了。现在要开始学习一些基础函数了,比如sum , sumif , mid , left , right,vlookup这些函数学会后就能整理数据源了,然后再学习数据透视表,能做出一张简单的表格,再画个曲线图什么的,这就已经到达初级水平了。
下面是大家见得最多的 10 个函数。
此函数用于对单元格中的值求和。
此函数用于在条件为真时返回一个值,条件为假时返回另一个值。 下面是 IF 函数的用法视频。
需要查询一行或一列并查找另一行或列中的相同位置的值时,请使用此函数。
如果需要按行查找表或区域中的内容,请使用此函数。 例如,按员工号查找某位员工的姓氏,或通过查找员工的姓氏查找该员工的电话号码(就像使用电话簿)。 请观看这个 VLOOKUP 用法视频。
使用此函数搜索单元格区域中的项,然后返回该项在该区域中的相对位置。 例如,如果区域 A1:A3 包含值 5、7 和 38,则公式 =MATCH (7,A1:A3,0) 返回数字 2,因为 7 是该区域中的第二个项目。
此函数用于根据索引号从最多 254 个数值中选择一个。 例如,如果 value1 到 value7 表示一周的 7 天,那么将 1 到 7 之间的数字用作 index_num 时,CHOOSE 将返回其中的某一天。
此函数用于返回代表特定日期的连续序列号。 此函数在公式,而非单元格引用提供年、月和日的情况中非常有用。 例如,可能有一个工作表所包含的日期使用了 Excel 无法识别的格式(如 YYYYMMDD)。
DATEDIF 函数用于计算两个日期之间的天数、月数或年数。
此函数用于返回两个日期之间的天数。
函数 FIND 和 FINDB 用于在第二个文本串中定位第一个文本串。 这两个函数返回第一个文本串的起始位置的值,该值从第二个文本串的第一个字符算起。
此函数用于返回表格或区域中的值或值的引用。
此函数用于对单元格中的值求和。
|
此函数用于在条件为真时返回一个值,条件为假时返回另一个值。 下面是 IF 函数的用法视频。
|
需要查询一行或一列并查找另一行或列中的相同位置的值时,请使用此函数。
|
如果需要按行查找表或区域中的内容,请使用此函数。 例如,按员工号查找某位员工的姓氏,或通过查找员工的姓氏查找该员工的电话号码(就像使用电话簿)。 请观看这个 VLOOKUP 用法视频。
|
使用此函数搜索单元格区域中的项,然后返回该项在该区域中的相对位置。 例如,如果区域 A1:A3 包含值 5、7 和 38,则公式 =MATCH (7,A1:A3,0) 返回数字 2,因为 7 是该区域中的第二个项目。
|
此函数用于根据索引号从最多 254 个数值中选择一个。 例如,如果 value1 到 value7 表示一周的 7 天,那么将 1 到 7 之间的数字用作 index_num 时,CHOOSE 将返回其中的某一天。
|
此函数用于返回代表特定日期的连续序列号。 此函数在公式,而非单元格引用提供年、月和日的情况中非常有用。 例如,可能有一个工作表所包含的日期使用了 Excel 无法识别的格式(如 YYYYMMDD)。 DATEDIF 函数用于计算两个日期之间的天数、月数或年数。
|
此函数用于返回两个日期之间的天数。
|
函数 FIND 和 FINDB 用于在第二个文本串中定位第一个文本串。 这两个函数返回第一个文本串的起始位置的值,该值从第二个文本串的第一个字符算起。
|
此函数用于返回表格或区域中的值或值的引用。
|
三、常用函数的多层嵌套
当你已经熟练掌握了excel菜单功能,熟练使用数据透视表,至少掌握几十个常用函数并且能熟练的运用,各种函数组合使用。
介绍12 个常用的 Excel 函数组合,务必收藏!
功能:计算指定条件下的最小值
例:要计算人力部的最低分数。
说明:先用IF函数判断A列的部门是否等于F3指定的部门,如果条件成立,则返回D列对应的分数,否则返回逻辑值FALSE;
接下来再使用MIN函数计算出其中的最小值。MIN函数有一个特性,就是可以自动忽略逻辑值,所以只会对数值部分计算,最终得到指定部门的最低分数。
例:在C列设置公式,如果A列值小于500且B列值为未到期,则返回”补款“,否则显示为空。
注:两个条件同时成立用 AND,任一个成立用 OR 函数。
例:根据月份和费用项目,查找金额。
说明:先用MATCH函数查找3月在第一行中的位置
再用MATCH函数查找费用项目在A列的位置
最后用INDEX根据行数和列数提取数值
功能:用于不确定列数的数据查询
例:根据B13单元格的姓名,在数据表中查询对应的项目。
功能:当 VLOOKUP 查找不到时屏蔽查错误值
例:根据产品名称在上表中查找单价,如果产品不存在则显示为空白。
功能:用于日期字符串的提取和转换
例:根据B列身份证号码提取出生年月。
说明:MID 函数用于从字符串的指定位置开始,提取特定数目的字符串。
功能:根据条件截取字符串
例:在个人信里截取出年龄
注:find 查找分隔符的位置,MID 负责截取字符
功能:计算一个单元格内有几个项目
例:计算每个部门的人数。
注:加上一个判断B2单元格是否为空格的判定,否则如果为空格就会返回错误的结果1
说明:先用LEN函数计算出 B 列单元格的字符长度,然后再用 SUBSTITUTE 函数将顿号全部替换掉之后,计算替换后的字符长度。
用字符长度减去替换后的字符长度,就是单元格内顿号的个数。
功能:分离汉字、数字和字母
注:带B的函数是按字节计数,而一个汉字占2个字节,数字和字母则占1个。所以用 LENB(A2)-LEN(A2) 可以倒推出汉字的个数,然后用 left 或 mid 函数截取。
功能:计算不重复值个数
例:统计B列的客户数量
注:Countif 函数统计出每个客户的出现次数,Sumprodcut 对 1/出现次数进行求和。每个客户无论出现多少次,求和的结果都是1,求和后正好是不重复个数。
例:在D2单元格返回B列最近7天的销量
注:Counta 负责统计 B 列非空值个数,offset 负责生成动态最后 N 天区域,SUM 负责求和
例:在F列查找“张丽”的个人消费记录
- Row(a1):是返回A1的行号1,如果向下复制会变为 Row(a2),返回2,其实用它的目的是当公式向下复制时可以生成序号:1,2,3...然后取符合条件的第1个行号,第2个行号...
- SMALL: 从符合条件的行号中从小到大,逐个提取符合条件的行
- INDEX :根据取得的行号从C列提取值
- { }:数组公式(含有逐一运算的公式)需要按 ctrl+shift+enter 输入大括号(一定要是自动生成的,不能手输入大括号)。
要说的是,Excel只是工具,要把这个工具用好,数据分析的指导是必须的,再次推荐知乎的【数据分析入门训练营】,一线互联网大厂大牛带队,直播互动讲解互联网数据分析工作流+常见分析模型运作原理,还有大厂真实案例带练实操,打破简历0项目经验。想转行数分升职加薪的建议领取:
要成为一个高级用户,首先在掌握以上所有的前提下,还要熟练掌握运用数组公式,并且当遇到excel本身自带的功能都没办法解决的问题可以通过编写VBA代码开发拓展excel功能从而解决问题,一旦进入这两个领域,你会发现突然进入了另一个天空,以前许多无法解决的问题,现在都可以迎刃而解,那是相当痛快的事情。
掌握了VBA,可以发挥以下作用:
1.规范用户的操作,控制用户的操作行为;
2.操作界面人性化,方便用户的操作;
3.多个步骤的手工操作通过执行VBA代码可以迅速的实现;
4.实现一些VB无法实现的功能。
6.利用VBA可以Excel内轻松开发出功能强大的自动化程序。
方法一:从功能区打开。点击功能区—开发工具—Visual Basic 命令打开。如果你为开启开发工具选项卡,请阅读这篇推送,学习如何开启。
方法二:快捷键打开。按「Alt + F11」打开。
方法三:从工作表查看代码命令打开。鼠标移动到任意一工作表名称处,点击右键,在菜单中选择「查看代码」打开。
工具栏:编辑器命令栏,与 Excel 功能区域,编辑器所有命令在工具栏里可以找到。
VBA工程:通常,一个工作簿就是一个 VBA 工程,其中包括 Excel 对象、工作表对象、模块。
属性窗口:查看和设置选中对象的属性的窗口。
代码编辑窗口:实际编写代码的位置。编写、修改、保存代码,都在这里进行。
立即窗口:代码运行过程中,打印出的内容,在立即窗口中显示。通用用于调试代码
将下面的代码复制到上一步打开的编辑窗口。
那什么人能称为专家呢?其实没有什么评判的标准了,如果把excel的功能一一列出,能全部掌握的人寥寥无几,其实也没必要全部掌握,由于excel博大精深,涵盖各行各业,比如你不是财务人员,你可能一辈子也不会用到fv,pv,irr这样的函数。在我的心中能称为专家的人,第一在软件操作上达到高级用户水平、第二并自身所在的行业有着深刻的了解,能把excel功能发挥到极致来服务自己所在的行业。
在专业的excel网站上,那些受人尊敬的,被称为excel专家的,无一不是各行各业的佼佼者,所以如果想成为excel专家不仅仅是学习excel那么简单了,要走的路很长。可能有些小伙伴看到这感觉道路漫长遥遥无期,信心大减,其实我并不是这么想,这反而能给你一个奔头,是一种价值的体现。
既然说到这里,依然建议大家报名知乎站内的几乎免费的数据分析入门课程,涉及职场汇报的图表展示;数据分析思维讲解以及相关的数据分析进阶方法,是入门数据分析性价比较高的一个选择。
我是透视哥,如果你想要转行数据分析,想要深入学习 Excel,那么点个关注,我会定期分享自己的学习感悟,和数据分析的要领。
码字不易,也希望各位,点赞收藏分享三连!