相信大家总能在朋友圈刷到下图这种教育广告,python很强,但总是这么贬低Excel就没必要了吧。
直到我看见了下面的回复,笑喷!
实话实说,从数据分析的角度来看,excel的可视化要差一些,数据采集也不能跟python相比,这都不是excel的专业,但是excel在数理统计上的表现还是很不错的
所以当数据分析量小、想要快速出结果、逻辑关系简单的情况下,excel很香![1]
而python就是给你一堆原材料,水泥、砖头、钢筋等等,你的工作就是自己要从头摘,一步步把高楼给建造出来。
Excel虽然人人都会,但如果想要用来数据分析,要掌握的函数操作还真不少。
下文是Excel常用函数大全,建议收藏,不要吃灰!
文末三连不要忘了哈
(阅读、在看、分享)
这是一份很好的Excel常用函数大全,也是工作中经常使用的函数操作,学习Excel常用函数,不要求面面俱到,而是选择常用的函数进行学习,当再次碰到了其它函数,再单独去学习即可。 ——黄同学
下面都是在使用Excel过程中,所使用过的一些基础功能,限于篇幅这里就不详细介绍,大家可以自行下去操作一遍。
1)图示讲解含义
注意:使用F4键,进行绝对引用和相对引用公式的切换。
上图我们已经演示了“相对引用”和“绝对引用”的功能,那么到底什么是“相对引用”?什么又是“绝对引用”呢?下面我们分别对其下一个定义。
在定义这两个概念之前,我们先来说明一下excel中的行与列。从上图可以看出,在excel中,行索引是一系列的数字(1,2,3...),列索引是一系列的大写字母(A,B,C...),。但是我们在表述某一个单元格时,常喜欢用类似“C2”的形式,表示某一个单元格,即把列写在前面,行写在后面。
相对引用:针对某一单元格引用另外一个单元格的情况,不添加“ $ ”符号时,就表示相对引用。当我们将引用单元格,朝着右边、下边拖动填充的时候,该引用单元格会跟着被引用单元格变化,类似于图中的“A图”。
绝对引用:也是针对某一单元格引用另外一个单元格的情况,行列都添加“ $ ”符号时,就表示绝对引用。当我们将引用单元格,朝着右边、下边拖动填充的时候,引用单元格不发生任何变化,类似于图中的“B图”。
半绝对引用:同样是针对某一单元格引用另外一个单元格的情况,行或者列其中一个添加“ $ ”符号时,就表示半绝对引用。当仅针对行使用了“ $ ”符号,引用单元格朝下边拖拉填充的时候,引用单元格不会发生任何变化,类似于图中的“C图”。当仅针对列使用了“$”符号,引用单元格朝右边拖拉填充的时候,引用单元格不会发生任何变化,类似于图中的“D图”。
① 最简单的一种
从上述案例可以看出,我们在B41单元格输入了“=$A41*B$40”这个公式,这个公式表示B41单元格分别引用了“$A41和B$40”这两个单元格,但是为什么是在A和40前面加“$”符号呢?这个需要我们好好琢磨一下。
我们可以先想象一下,当单元格B41从左右拖拉填充的时候,是不是保持了“A41行不变,B40列变化”,因此40前面需要加一个“$”符号。当单元格B41从上往下拖拉填充的时候,是不是保持了“B40列不变,A41行变化”,因此40前面需要加一个“$”符号。
② 使用“&”连接符,显示较全的九九乘法表
③ 配合if()函数,显示上/下三角形式的九九乘法表
使用函数之前,学会下方的四个提示和一个注意: 一:所有的函数均是以“=”开头; 二:所有的函数都是在“英文”状态下输入; 三:文本或日期等非单元格的引用,需要添加“双引号”; 四:连接符是“&”; 注意:在excel单元格中,数字和日期都是靠着单元格右侧,文本都是靠着单元格左侧。
① and
② or
③ if
当使用了if()函数进行多层嵌套,很容易写错,那么怎么规避这个问题呢?
① left
② right
④ len(lenb自己下去学习)
注意:len = 1中文 + 1英文;lenb = 2中文 + 1英文;这两个函数可以完成中英文的分离。len - lenb表示的是中文个数;2len - lenb表示的是英文个数。
⑤ lower
⑥ upper
⑦ proper
⑧ find
注意:find()函数区分大小写,search()函数不区分大小写。
⑨ search
注意:find()函数区分大小写,search()函数不区分大小写。
⑩ rept
⑪ replace
⑫ substitute
注意:这个函数的最后一个参数很有用。如果一个文本中有几个重复的内容,最后一个参数可以指定,从第几个重复内容开始起,进行替换。
⑬ trim
注意:该函数可以去掉字符串中所有的空格,但是会保留一个字符与字符之间的间隔空白符。
① abs
② round
③ roundup
④ rounddown
⑤ even
注意:enen和odd都是朝着绝对值数字大的方向走的。
⑥ odd
注意:enen和odd都是朝着绝对值数字大的方向走的。
⑦ int
⑧ trunc
⑨ power
⑩ ^
⑪ product
⑫ mod
⑬ rand
⑭ randbetween
① sum_sumif_sumifs
② average_averageif_averageif_averagea
注意:averagea()函数用法较为特殊,需要特别留意一下即可。
③ count_countif_countifs_counta_countblack
④ max_maxa_min_mina_median
⑤ sumproduct
⑥ subtotal
注意:这个函数可以实现我们之前学过的很多函数的功能,用起来也很方便,由于该函数涉及到的知识点太多,这里简单介绍一下。我们在excel中输入该函数,会有这么多提示:
下面介绍一些操作展示:
① today_now:返回系统时间
② year_month_day:提取某个日期中的年、月、日
③ days_datedif:计算两个日期之间的年、月、日
④ edate_eomonth:将时间前、后移动指定月份
⑤ weekday:返回星期几的函数
这个函数需要特别注意一下,默认情况下,显示的是国外的时间,也就是说星期天显示的是1。但是我们国家希望星期天显示的是7,星期一显示的是1,怎么办呢?我猜你肯定不知道,weekday()函数还有第二个参数,如下图所示。
今天是2020年5月1号,按照国外默认来说,今天应该是周六,即显示结果为6。如果使用了第2个参数后,可以改成我们习惯的“星期”显示方式。
⑥ text:返回各种日期、时间格式的函数(强大)
text()函数相当强大、有用,涉及到的知识点也是众多的,因此这里提供一个学习链接给大家,就不详细演示了:https://baijiahao.baidu.com/s?id=1615357210194597308&wfr=spider&for=pc[2]
⑦ networkdays_networkdays.inl:计算工作日的常用函数
关于networkdays.inl的第4个参数,很有用,我们利用下图进行展示一下,从下图可以看出,每一个数字代表每一周的休息日是哪一天,例如“数字11”表示,休息日只有星期天,周六仍然需要上班,这就是我们所说的“单休”。
① column_columns_row_rows
① vlookup
注意:多条件查询,需要添加辅助列。
② lookup及其高级应用
注意:使用lookup()函数需要特别注意,“查找对象所在的列必须升序排列”,否则结果做出来都是错的。同时,这个进行多条件查询的时候,不需要添加辅助列。
假如,我不想改动源数据,仍然想要使用lookup()做查找,应该怎么办呢?那么就需要使用lookup()高级应用了。关于lookup()高级应用,这里需要记住一句话:首先它会默认你的查找区域是升序排列,当查找值在查找区域中找不到目标值的时候,就会返回该区域中的“最大值”,这就是下面要讲述方法的“解题关键”。
③ index
关于index()函数显示某一行值,涉及到数组的操作,下面我们录制了一个视频。
④ match
注意:index()和match()进行搭配使用,进行多条件查找,相当有效,也特别好用。组合棋类的效果是这样的:index(查找区域,行号,列号)。
⑤ offset
① iferror
1)and_or_if_left_right实战演示
=IF(D3>100000,'是','否') ----1 =IF(OR(B3='北京',B3='上海'),'是','否') ----2 =IF(AND(E3>30000,E3<100000),'是','否') ----3 =B3&'--'&A3&'--'&E3 ----4 =LEFT(C3,7)&'' ----5 =LEFT(C3,3)&''&RIGHT(C3,4) ----6
操作如下:
if嵌套函数演示如下:
=IF(B51<60,'不及格',IF(B51<70,'及格',IF(B51<80,'不错',IF(B51<90,'良好','非常好'))))
操作如下:
2)find_lower_left_right实战演示
=LOWER(B3) ---1=RIGHT(B3,LENB(B3)-LEN(B3)) ---2=FIND(RIGHT(B3,LENB(B3)-LEN(B3)),B3) ---3=LEFT(B3,2*LEN(B3)-LENB(B3)-1) ---4
操作如下:
3)abs_int_trunc_round_roundup_rounddown_even_odd实战演示
=ABS(A2)=INT(A2)=TRUNC(A2,1)=ROUND(A2,1)=ROUNDUP(A2,1)=ROUNDDOWN(A2,1)=EVEN(A2)=IF(A3>0,EVEN(A3),EVEN(A3)+2)=ODD(A2)=IF(A2>0,ODD(A2),ODD(A2)+2)
操作如下:
4)rand_randbetween实战演示
=RAND()=TRUNC((RAND()*10))=RANDBETWEEN(1,11)=TEXT(RANDBETWEEN(1,TODAY()),'yyyy-mm-dd')
操作如下:
5)sum_sumif_sumifs实战演示
=SUM(P2:P7326)=SUMIF(Q:Q,2018,P:P)=SUMIFS(P:P,Q:Q,2019,M:M,'上海')=SUMIFS(P:P,Q:Q,2019,M:M,'上海',N:N,'酒水')
操作如下:
6)average_averageif_averageif_averagea实战演示
=AVERAGE(G:G)=AVERAGEIF(H:H,2018,G:G)=AVERAGEIFS(G:G,H:H,2019,F:F,'可乐')
操作如下:
7)count_countif_countifs_counta_countblack实战演示
=COUNT(G:G)=COUNTIF(H:H,2019)=COUNTIFS(H:H,2019,D:D,'上海')'=COUNTIFS(H:H,2019,D:D,'上海',F:F,'可乐')
操作如下:
8)max_maxa_min_mina_median实战演示
=MAX(G2:G7327)=MIN(G2:G7327)=MEDIAN(G2:G7327)
操作如下:
9)sumproduct实战演示
=SUMPRODUCT(G:G,J:J)=SUMPRODUCT((H:H='2018')*1,G:G,J:J)=SUMPRODUCT((H2:H7326='2018')*G2:G7326*J2:J7326)=SUMPRODUCT((H:H='2019')*1,(D:D='上海')*1,G:G,J:J)=SUMPRODUCT((H:H='2019')*1,(D:D='上海')*1,(E:E='酒水')*1,G:G,J:J)
操作如下:
联系客服