Excel函数精简，助你成功掌握基本函数

最新推荐文章于 2021-07-23 20:59:57 发布

叶流光

最新推荐文章于 2021-07-23 20:59:57 发布

阅读量1.1k

点赞数 1

分类专栏：实施

原文链接：https://blog.csdn.net/weixin_41261833/article/details/105882486

版权

实施专栏收录该内容

3 篇文章 0 订阅

订阅专栏

你没有看错，这是一份很好的Excel常用函数大全，也是工作中经常使用的函数操作，学习Excel常用函数，不要求面面俱到，而是选择常用的函数进行学习，当再次碰到了其它函数，再单独去学习即可。转载自一位很用心的博主，原文地址：https://blog.csdn.net/weixin_41261833/article/details/105882486

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