Q1:有哪些有用的Excel公式?
多图预警!!
预期交给你几个固定的公式,不如学会职场中最常用的二十个函数,由这些函数,你可以在实际问题中组合、嵌套成实现各种功能的公式。
函数应该是职场中最应该掌握的Excel技能之一。今天星爷就带大家串讲职场中最常用到的函数,制作了60张PPT高清图,直接下载下来就可以当做课件使用。
01 - 函数基础知识函数知识点
函数知识图谱
①高效输入函数的秘籍
高效输入函数
请务必掌握这两个操作快捷键,Ctrl+Shift+A和Ctrl+A,对你输入函数的参数大有帮助。
②单元格引用不可乱
③破解函数嵌套
02- 文本变形计①清洗与连接
②文本提取
③百变大咖
03- 逻辑专家①IF函数的妙用
②错误值终结者
04- 超级计算器①SUM求和
②SUMIF
③COUNTIF
05- 最强大脑①INDIRECT函数
②VLOOKUP
③INDEX+MATCH
好了,掌握这些函数,基本上在学习函数的路上算是入门了,Excel总计有400个左右的函数,这里面其中的多数都不需要掌握,只要遇到函数的时候能够通过F1帮助文件使用即可。
当然,本文交给你的只是函数的冰山一角,通常来说,对于一般的职场应用,掌握80个作用的函数就足以应对绝大多数的工作场景。剩下的那些函数,就交给你了学习了。
Q3:excel公式不会用怎么办?
理解公式的基本功能和参数,是掌握Excel公式的基本功。
首先要知道Excel公式的要素:等号开头,参数用括号括起来,参数之间用逗号隔开,所有符号英文输入。举例
1.将日期标注为星期几(自定义——yyyy年m月d日)
2.or("条件一","条件二"……)
例如:通过星期几计算是否加班:=if(or(weekday(A2,2)=6,weekday(A2,2)=7),"是","否")【A2为要求日期】
3.求和函数
①求和函数SUM
=SUM(数值,单元格,单元格区域,另一个函数)
适用场景:最简单形式的求和
②条件求和函数SUMIF
=SUMIF(条件所在的单元格区域,条件,实际求和的单元格区域)
适用场景:具有单个条件的求和
③sumifs(求和区域,"条件区域一""条件一","条件区域二""条件二"……)
适用场景:两个及以上条件的求和
④积和函数SUMPRODUCT
=SUMPRODUCT(第一组数据,第二组数据。。。)
适用场景:求销售总额
4.将单元格中1——001:首先选中所有单元格,设置单元格格式,文本,再输入001即可。
5.在做完一系列题目后,若题目要求分类汇总,而选项卡下不能点分类汇总则:选择设计——工具——转化为区域
6.rank(要排名的单元格,在哪排名,排名方式0降序/1升序)注意:在哪排名的区域需要绝对引用
7.text将数值转化为自己想要的文本
例如十五套从身份证号中提取出生年月日格式为×年×月×日:=text(mid(f3,7,8),"0000年00月00日")
8多条件if
例如十五套通过学号求班级,3,4位位班级,01=一班,02=2班…:100286即:=if(mid("学号",3,2)="01","一班",if(mid("学号",3,2)="02","二班","三班"))
9.int取整函数
例如十八套根据月份求季度:=int((month(A3)-1)/3+1)
10.=now()
=today()
计算一个日期距离现在有几年:=int((today()-f3)/365)
=year()
=weekday()
=Datedif(起始日期,结束日期,差值类型)
"D/M/Y"
注意事项:日期函数是可以进行加减运算的
11.平均值函数(可通过其他函数转化)
①平均值函数AVERAGE
②条件平均函数AVERAGEIF
③多条件平均函数AVERAGEIFS
12.VLOOKUP函数(必考)
=vlookup(通过谁来查找,在哪查找的绝对区域,返回值在所选中区域的列数,0)
适用场景:多表对照
★注意事项:第二个参数必须绝对引用
例如:通过图书名称填充图书编号:=VLOOKUP("图书名称",既有图书名称又有图书编号的对照表,图书编号在你所选区域第几列,。
13.计数函数
①Count函数,适用场景:数值计数
②Counta函数,适用场景:非空计数
③ Countif函数,=countif(区域,条件),适用场景:单条件计数
④Countifs函数,=countifs(区域,条件,区域,条件)
14.字符串截取函数
①左侧截取函数LEFT
=LEFT(要截取的字符串,从左侧开始截取几位)
适用场景:左侧规则数量字符截取
②右侧截取RIGHT
③中间截取字符串MID
=MID(要截取的字符串,从第几位开始,截取几位)
wWw.baz@HiShI.COm
Q4:Excel公式看不懂,求指教?
Excel公式看不懂,通常有以下几种情况及解决办法:
一、公式涉及计算逻辑较为复杂这种情况下,可以使用公式求值的功能一步步查看公式每次的关键计算结果,这样就很容易理解公式的计算过程了,如下图所示:
二、公式较长,进一步加大了对公式的计算逻辑理解难度这种情况,可以考虑使用Alt+Enter快捷键对公式进行换行处理——这个快捷键不仅适用于一般的内容换行,在公式内同样适用,然后在每行的开头添加一定数量的空格实现缩进,达到公式层次结构分明的目的,如下图所示:
三、公式内省略或省写了参数,以至于公式看不懂这种情况其实非常不好,所以我一般建议不要在公式里省略参数,能写明确的一定要写明确,能省的也不要省!公式不仅是自己用的,很多时候是要给别人看的!
当然,有时候是因为别人写了这样的公式,那我们也要学会看得懂。
以这个问题里的公式为例,match函数省略了两个参数(严格来说这种不叫省略,或者应该叫省写,后面再说省略和省写的区别),结果很多人一看就蒙圈了,实际上,match函数的参数有3个,其中第3个参数可以省略。问题中的公式有2个逗号,说明3个参数都是存在的,只是没有把参数的具体值写出来而已,像这种情况,参数存在而没有写具体值的,这个参数的值实际就是0。如下图所示:
接下来,下面再对比一下省略和省写参数的差别,方便大家加深印象:
以上是对一些复杂公式的解读办法,同时也说明了一些写公式时应该注意的问题,希望大家以后写公式时都能遵守一定的规范,不仅方便自己,更能方便别人。
欢迎关注【Excel到PowerBI】私信我即可下载60+基础函数及新功能Power系列功能汇总训练材料我是大海,微软认证Excel专家,企业签约Power BI技术顾问让我们一起学习,共同进步