Excel函数大全

excel 专栏收录该内容
2 篇文章 3 订阅

分享一篇Excel函数总结得很全面细致的文章,工作中常用的函数都写到了
原文作者是一位写文章很用心的博主,其博客地址为:https://blog.csdn.net/weixin_41261833
欢迎大家关注!

  你没有看错,这是一份很好的Excel常用函数大全,也是工作中经常使用的函数操作,学习Excel常用函数,不要求面面俱到,而是选择常用的函数进行学习,当再次碰到了其它函数,再单独去学习即可。

目录

  1、excel函数需要掌握的基础功能
  2、函数分类
  3、学习函数的建议
  4、绝对引用、相对引用与半绝对引用
   1)图示讲解含义
   2)利用九九乘法表讲述“绝对引用”和“相对引用”
     ① 最简单的一种
     ② 使用“&”连接符,显示较全的九九乘法表
     ③ 配合if()函数,显示上/下三角形式的九九乘法表
  5、函数讲解(最常用的个函数)
   1)逻辑函数
     ① and
     ② or
     ③ if
   2)字符串函数
     ① left
     ② right
     ③ mid
     ④ len(lenb自己下去学习)
     ⑤ lower
     ⑥ upper
     ⑦ proper
     ⑧ find
     ⑨ search
     ⑩ rept
     ⑪ replace
     ⑫ substitute
     ⑬ trim
   3)数学函数
     ① abs
     ② round
     ③ roundup
     ④ rounddown
     ⑤ even
     ⑥ odd
     ⑦ int
     ⑧ trunc
     ⑨ power
     ⑩ ^
     ⑪ product
     ⑫ mod
     ⑬ rand
     ⑭ randbetween
   3)统计函数
     ① sum_sumif_sumifs
     ② average_averageif_averageif_averagea
     ③ count_countif_countifs_counta_countblack
     ④ max_maxa_min_mina_median
     ⑤ sumproduct
     ⑥ subtotal
   4)时间函数
     ① today_now:返回系统时间
     ② year_month_day:提取某个日期中的年、月、日
     ③ days_datedif:计算两个日期之间的年、月、日
     ④ edate_eomonth:将时间前、后移动指定月份
     ⑤ weekday:返回星期几的函数
     ⑥ text:返回各种日期、时间格式的函数(强大)
     ⑦ networkdays_networkdays.inl:计算工作日的常用函数
   5)行数、列数统计函数
     ① column_columns_row_rows
   6)匹配查找函数
     ① vlookup
     ② lookup及其高级应用
     ③ index
     ④ match
     ⑤ offset
   7)错误处理函数
     ① iferror
  6、函数实战操作
   1)and_or_if_left_right实战演示
   2)find_lower_left_right实战演示
   3)abs_int_trunc_round_roundup_rounddown_even_odd实战演示
   4)rand_randbetween实战演示
   5)sum_sumif_sumifs实战演示
   6)average_averageif_averageif_averagea实战演示
   7)count_countif_countifs_counta_countblack实战演示
   8)max_maxa_min_mina_median实战演示
   9)sumproduct实战演示

1、excel函数需要掌握的基础功能

  下面都是在使用Excel过程中,所使用过的一些基础功能,限于篇幅这里就不详细介绍,大家可以自行下去操作一遍。

  • 相对引用
  • 绝对引用
  • 帮助功能
  • 显示公式
  • 自动/手动计算
  • 追踪引用单元格
  • 名称编辑器
  • 照相机

2、函数分类

在这里插入图片描述

3、学习函数的建议

  • 不要死记硬背,但是看着某些函数一定要眼熟。
  • 学会使用在线帮助功能(按F1键)。
  • 合理的逻辑思维,excel中函数的使用,和我们学习其他编程语言中使用函数一样,了解函数的功能、参数。

4、绝对引用、相对引用与半绝对引用

1)图示讲解含义

  注意:使用F4键,进行绝对引用和相对引用公式的切换。
在这里插入图片描述
  上图我们已经演示了“相对引用”和“绝对引用”的功能,那么到底什么是“相对引用”?什么又是“绝对引用”呢?下面我们分别对其下一个定义。
  在定义这两个概念之前,我们先来说明一下excel中的行与列。从上图可以看出,在excel中,行索引是一系列的数字(1,2,3…),列索引是一系列的大写字母(A,B,C…),。但是我们在表述某一个单元格时,常喜欢用类似“C2”的形式,表示某一个单元格,即把列写在前面,行写在后面。
  相对引用:针对某一单元格引用另外一个单元格的情况,不添加“ $ ”符号时,就表示相对引用。当我们将引用单元格,朝着右边、下边拖动填充的时候,该引用单元格会跟着被引用单元格变化,类似于图中的“A图”。
  绝对引用:也是针对某一单元格引用另外一个单元格的情况,行列都添加“ $ ”符号时,就表示绝对引用。当我们将引用单元格,朝着右边、下边拖动填充的时候,引用单元格不发生任何变化,类似于图中的“B图”。
  半绝对引用:同样是针对某一单元格引用另外一个单元格的情况,行或者列其中一个添加“ $ ”符号时,就表示半绝对引用。当仅针对行使用了“ $ ”符号,引用单元格朝下边拖拉填充的时候,引用单元格不会发生任何变化,类似于图中的“C图”。当仅针对列使用了“$”符号,引用单元格朝右边拖拉填充的时候,引用单元格不会发生任何变化,类似于图中的“D图”。

2)利用九九乘法表讲述“绝对引用”和“相对引用”
① 最简单的一种

在这里插入图片描述
  从上述案例可以看出,我们在B41单元格输入了“=$A41*B$40”这个公式,这个公式表示B41单元格分别引用了“$A41和B$40”这两个单元格,但是为什么是在A和40前面加“$”符号呢?这个需要我们好好琢磨一下。
  我们可以先想象一下,当单元格B41从做左右拖拉填充的时候,是不是保持了“A41行不变,B40列变化”,因此40前面需要加一个“$”符号。当单元格B41从上往下拖拉填充的时候,是不是保持了“B40列不变,A41行变化”,因此40前面需要加一个“$”符号。

② 使用“&”连接符,显示较全的九九乘法表

在这里插入图片描述

③ 配合if()函数,显示上/下三角形式的九九乘法表

在这里插入图片描述

5、函数讲解(最常用的个函数)

  使用函数之前,学会下方的四个提示和一个注意:
  一:所有的函数均是以“=”开头;
  二:所有的函数都是在“英文”状态下输入;
  三:文本或日期等非单元格的引用,需要添加“双引号”;
  四:连接符是“&”;
  注意:在excel单元格中,数字和日期都是靠着单元格右侧,文本都是靠着单元格左侧。
在这里插入图片描述

1)逻辑函数
① and

在这里插入图片描述

② or

在这里插入图片描述

③ if

在这里插入图片描述
当使用了if()函数进行多层嵌套,很容易写错,那么怎么规避这个问题呢?
在这里插入图片描述

2)字符串函数
① left

在这里插入图片描述

② right

在这里插入图片描述

③ mid

在这里插入图片描述

④ 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

注意:该函数可以去掉字符串中所有的空格,但是会保留一个字符与字符之间的间隔空白符。
在这里插入图片描述

3)数学函数
① abs

在这里插入图片描述

② round

在这里插入图片描述

③ roundup

在这里插入图片描述

④ rounddown

在这里插入图片描述

⑤ even

注意:enen和odd都是朝着绝对值数字大的方向走的。
在这里插入图片描述

⑥ odd

注意:enen和odd都是朝着绝对值数字大的方向走的。
在这里插入图片描述

⑦ int

在这里插入图片描述

⑧ trunc

在这里插入图片描述

⑨ power

在这里插入图片描述

⑩ ^

在这里插入图片描述

⑪ product

在这里插入图片描述

⑫ mod

在这里插入图片描述

⑬ rand

在这里插入图片描述

⑭ randbetween

在这里插入图片描述

3)统计函数
① sum_sumif_sumifs

在这里插入图片描述

② average_averageif_averageif_averagea

注意:averagea()函数用法较为特殊,需要特别留意一下即可。
在这里插入图片描述

③ count_countif_countifs_counta_countblack

在这里插入图片描述

④ max_maxa_min_mina_median

在这里插入图片描述

⑤ sumproduct

在这里插入图片描述

⑥ subtotal

注意:这个函数可以实现我们之前学过的很多函数的功能,用起来也很方便,由于该函数涉及到的知识点太多,这里简单介绍一下。
我们在excel中输入该函数,会有这么多提示:
在这里插入图片描述

下面介绍一些操作展示:
在这里插入图片描述

4)时间函数
① 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
在这里插入图片描述

⑦ networkdays_networkdays.inl:计算工作日的常用函数

在这里插入图片描述

关于networkdays.inl的第4个参数,很有用,我们利用下图进行展示一下,从下图可以看出,每一个数字代表每一周的休息日是哪一天,例如“数字11”表示,休息日只有星期天,周六仍然需要上班,这就是我们所说的“单休”。
在这里插入图片描述

5)行数、列数统计函数
① column_columns_row_rows

在这里插入图片描述

6)匹配查找函数
① vlookup

注意:多条件查询,需要添加辅助列。
在这里插入图片描述

② lookup及其高级应用

注意:使用lookup()函数需要特别注意,“查找对象所在的列必须升序排列”,否则结果做出来都是错的。同时,这个进行多条件查询的时候,不需要添加辅助列。
在这里插入图片描述

假如,我不想改动源数据,仍然想要使用lookup()做查找,应该怎么办呢?那么就需要使用lookup()高级应用了。
关于lookup()高级应用,这里需要记住一句话:首先它会默认你的查找区域是升序排列,当查找值在查找区域中找不到目标值的时候,就会返回该区域中的“最大值”,这就是下面要讲述方法的“解题关键”。
在这里插入图片描述

③ index

在这里插入图片描述

关于index()函数显示某一行值,涉及到数组的操作,下面我们录制了一个视频。
在这里插入图片描述

④ match

在这里插入图片描述

注意:index()和match()进行搭配使用,进行多条件查找,相当有效,也特别好用。组合棋类的效果是这样的:index(查找区域,行号,列号)。

⑤ offset

在这里插入图片描述

7)错误处理函数
① iferror

在这里插入图片描述

6、函数实战操作

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,"良好","非常好"))))

 
  • 1

操作如下:
在这里插入图片描述

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

 
  • 1
  • 2
  • 3
  • 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)

 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

操作如下:
在这里插入图片描述

4)rand_randbetween实战演示
=RAND()
=TRUNC((RAND()*10))
=RANDBETWEEN(1,11)
=TEXT(RANDBETWEEN(1,TODAY()),"yyyy-mm-dd")

 
  • 1
  • 2
  • 3
  • 4

操作如下:
在这里插入图片描述

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,"酒水")

 
  • 1
  • 2
  • 3
  • 4

操作如下:
在这里插入图片描述

6)average_averageif_averageif_averagea实战演示
=AVERAGE(G:G)
=AVERAGEIF(H:H,2018,G:G)
=AVERAGEIFS(G:G,H:H,2019,F:F,"可乐")

 
  • 1
  • 2
  • 3

操作如下:
在这里插入图片描述

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,"可乐")

 
  • 1
  • 2
  • 3
  • 4

操作如下:
在这里插入图片描述

8)max_maxa_min_mina_median实战演示
=MAX(G2:G7327)
=MIN(G2:G7327)
=MEDIAN(G2:G7327)

 
  • 1
  • 2
  • 3

操作如下:
在这里插入图片描述

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)

 
  • 1
  • 2
  • 3
  • 4
  • 5

操作如下:
在这里插入图片描述

文章简单介绍到这里, 希望以上内容对大家有实际的帮助,后续将持续推出更多数据分析相关内容,欢迎留言你想了解的部分,一起探讨更多话题。如有其他相关问题也欢迎小伙伴们留言讨论或与我私信~
期待与你共同进步ヾ(◍°∇°◍)ノ゙


往期指引


版权声明:本文为CSDN博主「高雅_GaoYa」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/weixin_44390462/article/details/105883246

14.DEGREES 用途:将弧度转换为度。 语法:DEGREES(angle) 参数:angle是采用弧度单位的一个角度。 实例:公式“=DEGREES(1)返回57.29577951”, =DEGREES(PI()/3)返回60。 15.EVEN 用途:返回沿绝对值增大方向,将一个数值取整为最接近的偶数。 语法:EVEN(number) 参数:number是要取整的一个数值。实例:如果A1=-2.6 则公式“=EVEN(A1)”返回-4;=EVEN(-4.56+6.87)返回4。 16.EXP 用途:返回e的n次幂。语法:EXP(number) 参数:Number为底数e的指数。 注意:EXP函数是计算自然对数的LN 函数的反函数。 实例:如果A1=3,则公式“=EXP(A1)”返回20.085537 即e3。 17.FACT 用途:返回一个数的阶乘,即1*2*3*...*该数。 语法:FACT(number) 注意:Number是计算其阶乘的非负数。如果输入的Number 不是整数,则截去小数部分取整数。 实例:如果A1=3,则公式“=FACT(A1)”返回6;=FACT(5.5) 返回1*2*3*4*5.5 即120。 18.FACTDOUBLE 用途:返回参数Number 的半阶乘。 语法:FACTDOUBLE(number) Number 要计算其半阶乘的数值,如果参数Number 为非整数,则截尾取整。 注意:如果该函数不存在,应当运行“安装”程序加载“分析工具库”。 实例:公式“=FACTDOUBLE(4)”返回8。 19.FLOOR 用途:将参数Number 沿绝对值减小的方向去尾舍入,使其等于最接近的significance 的倍数。 语法:FLOOR(number,significance) 参数:Number为要舍入的某一数值,Significance为该数值的倍数。 实例:如果A1=22.5, 则公式“=FLOOR(A1,1)”返回22; =FLOOR(-2.5,-2)返回-2。 20.GCD 用途:返回两个或多个整数的最大公约数。语法:GCD(number1,number2,...) 参数:Number1,number2, ...为1 到29 个数值,如果数值为非整数,则截尾取整。说明:如果该函数不存在,必须运行“安装”程序加载“分析工具库”。 实例:如果A1=16、A2=28、A3=46,则公式“=GCD(A1:A3)”返回2。 21.INT 用途:将任意实数向下取整为最接近的整数。 语法:INT(number) 参数:Number为需要处理的任意一个实数。 实例:如果A1=16.24、A2=-28.389, 则公式“=INT(A1)”返回16,=INT(A2)返回-29。 ...
©️2022 CSDN 皮肤主题:精致技术 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值