Excel常用函数培训讲义(超全)

RANK( 数值,范围,数值)
1-升序,0-降序
AB C 1 100 20 1000 2 200 40 2000 3 300 60 3000 =RANK(A1,A1:A3,1)=1 =RANK(A1,A1:A3,0)=3
14
(二)日期函数:
返回日期序列年份YEAR 返回日期序列月份MONTH 返回日期序列特定天DATE 返回系统今天日期TODAY 返回系统今天日期与时间NOW 返回对应日期的星期数WEEKDAY
1 100 20 1000 2 200 40 2000 3 300 60 3000 =COUNTIF(A1:A3,“>=200”）=2 =COUNTIF(A1:A3, “>200” ）=1
11
5.求算书平均数:AVERAGE
AVERAGE( 数值1,数值2,…) AB C
1 100 20 1000 2 200 40 2000 3 300 60 3000 =AVERAGE（A1：A3）=200 =AVERAGE（A1：A3，B1）=105
=OR（ 5>3,2>1 ）= TRUE =OR（ 1>3,2>1 ）= TRUE =OR（ 1>3,2>5 ）= FALSE
27
3.将条件值反过来函数NOT
NOT( 条件) 括号中条件值为FALSE，则为TRUE ；如 果为TRUE，则为FALSE
=NOT（ 5>3 ）= FALSE =NOT（ 1>3 ）= TRUE
18
4.返回系统今天日期TODAY
TODAY( ) A1=2003-07-29 星期二 =TODAY（）= 2003-07-29
19
5.返回系统今天日期与时间NOW

Value_if_true logical_test 为 TRUE 时返回de值. Value_if_false logical_test 为 FALSE 时返回de值.
三 逻辑函数
三.二 AND函数—判断多个条件同时为真
功 能:所有参数de逻辑值为真时,返回 TRUE；只要一个参数de 逻辑值为假, 即返回 FLASE.
F四
F九 Ctrl+； Ctrl+Shift+: Ctrl+D Ctrl+R Ctrl+一
一 EXCEL快捷键操作
撤销上步操作
恢复上步操作 重复上步操作 剪切 复制 粘贴 查找 替换 定位 插入批注 打开“插入函数”对话框 定义名称
Ctrl+Z
Ctrl+Y F四
Ctrl+X Ctrl+C Ctrl+V Ctrl+F Ctrl+H Ctrl+G Shift+F二 Shift+F三 Ctrl+ F三
五 查找与引用函数 VLOOKUP
④Range_lookup 为逻辑值,指定希望 VLOOKUP 查找精确de匹配值 还是近似匹配值: 一）如果为 FALSE,VLOOKUP 将只寻找精确匹配值.在此情况 下, table_array 第一列de值不需要排序.如果 table_array 第一列 中有两个或多个值与 lookup_value 匹配,则使用第一个找到de值. 如果找不到精确匹配值,则返回错误值 #N/A. 二）如果为 TRUE 或省略,则返回精确匹配值或近似匹配值.也就是 说,如果找不到精确匹配值,则返回小于 lookup_value de最大数 值. table_array 第一列中de值必须以升序排序；否则 VLOOKUP 可 能无法返回正确de值.

财务函数
如PMT、FV、RATE等，用于执行财务计算和分析。
REPORT
CATALOG
DATE
ANALYSIS
SUMMAR Y
02
文本处理函数
文本连接与拆分
文本连接
使用`&`或`CONCATENATE`函数将多 个文本字符串连接成一个字符串。例 如，`=A1&" "&B1`将A1和B1单元格 的内容用空格连接。
日期与时间组合
使用`DATE`函数结合年、 月和日数值创建日期，或 使用`TIME`函数结合小时、 分钟和秒数创建时间。
日期时间函数应用案例
计算年龄 结合`TODAY`函数和出生日期，利用 日期间隔计算函数计算年龄。
计算到期日期
根据开始日期和期限，使用日期加减 运算计算到期日期。
提取出生日期中的年月日
日期序列数转换
使用`TEXT`函数结合日期 格式代码，将日期序列数 转换为指定格式的日期文 本。
日期间隔计算与时间提取
日期间隔计算
通过`-`运算符或 `DATEDIF`函数计算两个日 期之间的天数、月数或年 数间隔。
时间提取
利用`HOUR`、`MINUTE` 和`SECOND`函数从时间 值中提取小时、分钟和秒 数。
REPORT
CATALOG
DATE
ANALYSIS
SUMMARY
EXCEL函数培训课件
目录
CONTENTS
• EXCEL函数基础 • 文本处理函数 • 数学计算函数 • 逻辑判断函数 • 日期与时间函数 • 数据统计与分析函数 • 自定义函数与宏应用
REPORT
CATALOG
DATE
ANALYSIS

参数说明
函数的参数指定了函数执行所需的数据或条件。参 数可以是数字、文本、单元格引用或其他函数。有 些参数是必需的，有些则是可选的。
常用函数介绍
SUM函数
用于计算指定单元格区域或数组中所 有数值的和。例如， `=SUM(A1:A10)`将计算A1到A10单 元格中所有数值的和。
AVERAGE函数
用于计算指定单元格区域或数组中所 有数值的平均值。例如， `=AVERAGE(B1:B5)`将计算B1到B5 单元格中所有数值的平均值。
用于处理日期和时间数据 ，如计算日期差、提取日 期部分（年、月、日）等 。
函数定义与分类
查找和引用函数
用于在数据表中查找特定值或引 用单元格，如VLOOKUP、 HLOOKUP等。
逻辑函数
用于进行逻辑判断，如IF、AND 、OR等。
函数语法及参数
函数语法
每个Excel函数都有其特定的语法结构，通常包括 函数名、括号和参数。例如，SUM函数的语法为 `SUM(number1,[number2],...)`。
通过SWITCH函数，根据不同的订单 状态（如待处理、已发货、已完成等 ）自动更新订单处理进度。
04
文本处理与格式设置函数
Chapter
文本输入与编辑
文本输入
直接在单元格中输入文本，或使用 公式栏编辑。
文本连接
使用`&`符号或`CONCATENATE` 函数连接多个文本字符串。
文本截取
使用`LEFT`、`RIGHT`、`MID`等函 数截取文本字符串中的部分字符。
02
数据处理与统计函数
Chapter
数据清洗与整理
运用`FILTER`函数，根据特定条 件筛选数据，只显示符合条件的 记录。

据等。
案例三
结合文本处理和日期时间函数， 实现复杂的数据整理任务，例如 根据特定格式要求重新组织数据 、识别并处理特定时间段内的文
本信息等。
05 查找引用与数据库函数
查找引用函数
VLOOKUP函数
用于在数据表的首列查找指定的值，并返回 数据表当前行中指定列处的值。
MATCH函数
返回指定项在数组中的相对位置。
调试数组公式
提供数组公式调试方法，如分步计算、查看中间结果等，帮助用 户快速定位并解决问题。
名称管理器使用技巧
名称管理器概述
简要介绍名称管理器的功能及作用。
创建和编辑名称
详细讲解如何创建和编辑名称，包括定义名称、 指定引用位置等。
使用名称简化公式
通过实例演示如何使用名称简化复杂公式，提高 公式可读性和维护性。
法：选择数据区域，点击“插入”选项卡中的“饼图”按钮，选择合适
的饼图样式。
图表编辑和美化技巧
修改图表标题和坐标轴标签
双击图表标题或坐标轴标签进行修改，使其更具描述性和可读性 。
调整图表颜色和样式
通过选择图表元素（如柱形、线条等），在“格式”选项卡中调整 颜色、边框、填充等样式，使图表更加美观和易于理解。
02
03
04
05
TODAY, NOW DATE, TIME
DAY, MONTH, YEAR
DATEDIF
WORKDAY, WO…
返回当前日期或日期时间 。TODAY返回当前日期， NOW返回当前日期和时间 。
用于构建日期或时间。 DATE根据指定的年、月、 日构建日期，TIME则根据 指定的小时、分钟、秒构 建时间。
06 财务金融与数学计算函数
财务金融函数

数据整合
使用`VLOOKUP`、`INDEX`和`MATCH`等函 数整合多个数据源。
日期处理
运用`DATE`、`YEAR`、`MONTH`等函数处 理日期数据，便于透视表分析。
利用函数进行复杂条件筛选和排序操作
多条件筛选
结合`IF`、`AND`、`OR`等函数实现复杂条件的筛选。
排序操作
利用`SMALL`、`LARGE`、`RANK`等函数对数据进行排序。
单元格引用参数
引用单元格中的值或公式
名称参数
使用定义的名称代替复杂的引用 或常量
数组参数
传递一组值或单元格区域作为参 数
函数的返回值及意义
数值
进行计算后返回的数值结果
逻辑值
返回TRUE或FALSE，用于条件判断
文本
返回文本字符串或格式化后的文本
错误值
表示函数执行过程中出现的错误，如#N/A、 #VALUE!等
替换文本字符串中的一部分 内容。
数值计算类函数
01
02
03
04
05
SUM函数
AVERAGE函数
MAX/MIN函数 COUNT/COUN ROUND函数 T…
计算指定单元格区域中所有 数值的和。
计算指定单元格区域中所有 数值的平均值。
返回指定单元格区域中的最 大值/最小值。
计算指定单元格区域中包含 数值的单元格数量/非空单 元格数量。
应用REPLACE、SUBSTITUTE 函数实现文本替换操作。
数值计算类函数实战案例
利用SUM、AVERAGE函数进行求和 与平均值计算。
结合IF、COUNTIF函数进行条件计 数和求和。
使用MAX、MIN函数查找最大值和 最小值。

本字符串的位置。
REPLACE和SUBSTITUTE函 数：用于替换文本字符串中的
特定部分。
日期和时间函数
01
NOW和TODAY函数： 分别返回当前日期年、 月、日组合成日期。
03
YEAR、MONTH和DAY 函数：分别用于提取日 期中的年、月、日部分 。
04
DATEDIF函数：用于计 算两个日期之间的天数 、月数或年数差异。
自定义函数编写步骤
确定函数功能
明确自定义函数需要实现 的具体功能，以及输入参 数和输出结果的类型。
编写函数代码
根据功能需求，使用VBA 编写函数代码，包括定义 函数名、参数列表、函数 体等部分。
测试函数效果
在Excel中使用自定义函数 ，验证其功能和效果是否 符合预期。
调试与优化技巧
调试工具介绍
实践项目
参与实际数据分析项目，提升函数公式的应 用能力和解决实际问题的能力
THANKS
感谢观看
用于计数，可以统计选定单元格区域中包 含数值的单元格个数和包含任何内容的单 元格个数。
文本处理函数
01
02
03
04
CONCATENATE函数：用于 将多个文本字符串合并成一个
字符串。
LEFT、RIGHT和MID函数： 分别用于提取文本字符串的左 侧、右侧和中间部分的字符。
FIND和SEARCH函数：用于 在文本字符串中查找另一个文
公式
用户自定义的计算表达式，可以包 含函数、单元格引用、运算符等。
函数与公式的区别
函数是预定义的，而公式是用户自 定义的。
单元格引用方式
01
02
03
相对引用
公式中引用的单元格地址 会随着公式所在单元格位 置的变化而变化。

软件协同
通过与其他软件的结合，可以实现更高效的数据处理和自动化办公 。
应用场景
例如，与数据库软件结合使用，可以从数据库中提取数据并在 Excel中进行分析和处理。
THANKS
感谢观看
使用快捷键
如使用F2键可以编辑当前 选定的单元格， Ctrl+Shift+Enter组合键 可以输入数组函数。
函数的参数设置
必需参数
有些函数的参数是必须设 置的，如SUM函数的数值 参数。
可选参数
有些参数是可选的，如IF 函数的第三个参数（默认 值为“”）。
参数顺序
确保按照函数说明书的顺 序输入参数，否则函数可 能无法正常工作。
总结词
检查函数语法和参数
详细描述
确保函数名称拼写正确，并按照函数说明中的语法规则输 入参数。注意参数的数据类型和顺序，避免因参数不匹配 或缺失导致的错误。
总结词
使用公式审核工具
详细描述
利用Excel提供的公式审核工具，如“公式求值”、“公 式审核”等功能，逐步检查公式的计算过程，定位到出错 的具体位置，以便针对性地解决问题。
如何提高函数的执行效率
总结词
减少函数嵌套层级
VS
详细描述
过多的函数嵌套会导致计算复杂度增加， 降低执行效率。因此，在编写函数公式时 ，应尽量减少嵌套层级，简化计算过程。 同时，合理使用单元格的引用方式（相对 引用、绝对引用和混合引用），避免不必 要的重复计算和嵌套。
如何提高函数的执行效率
总结词
利用Excel的内置功能优化性能
详细描述
Excel函数是预先编写好的公式，用于执行各种计算、数据处理和分析任务。它 们可以在单元格中输入，帮助用户快速处理数据。

（四）文本函数
MID(): 返回文本串中从指定位置开始de特定数目de字符. 语法:MID(text,start_num, num_chars) 参数:Text 是包含要提取字符de文本串 .Start_num 是文本中要提取de第一个字符de位置,Num_chars指定希望MID 从文本中 返回字符de个数.
一、相对引用和绝对引用
混合引用是有绝对de行或相对de列.绝对引用列是采用$A一、$B一等形式.绝对引用行是采 用A$一、B$一等形式.如果公式所在单元格de位置改变,则相对引用de改变而绝对引用de不变 .如果多行或多列de复制公式,相对引用自动调整而绝对引用将不做调整.例如,下图公式中绝对 引用了A列和二三行,公式复制过程中只对相对引用de部分进行调整.
（二）统计函数
COUNTIFS（）用于计算区域中满足给定多条件de单元格de个数. 语法: COUNTIFS(range一,criteria一,range二,criteria二, …) Rang一为需要计算其中
满足条件一de单元格数目de单元格区域.Criteria一 为确定哪些单元格将被计算在内de第 一个条件, Rang二为需要计算其中满足条件二de单元格数目de单元格区域.Criteria二为 确定哪些单元格将被计算在内de第二个条件,以此类推,每一对range和criteria 之间用逗 号隔开.
（三）查找和引用函数
OFFSET（）,以指定de引用为参照系,通过给定偏移量得到新de引用.返回de引用可 以是一个单元格或单元格区域,并可以指定返回de行数或列数. 如下图使用OFFSET函数 实现动态图表,OFFSET最少包含了三个参数,其中第一个参数表示从该栏位进行偏移（ 起点）,第二个参数表示行偏移数,第三个参数表示列偏移数,所以当第二、第三参数值 变化时,对结果区域引用并插入图表,则该图表就是会随参数值de变化而变化.

（四）文本函数
MID(): 返回文本串中从指定位置开始特定数目字符 语法:MID(text,start_num, num_chars) 参数:Text 是包含要提取字符文本串 Start_num 是文本中要提取第一个字符位置,Num_chars指定希望MID 从文本中返回 字符个数
数据分列:可以对单元格内容进行拆分,采用数据分列—固定宽度或分隔符进行拆分,拆分 过程中注意相应格式
（一）日期与时间函数
DATEDIF（）返回两个日期之间年\月\日间隔数 语法:DATEDIF(start_date,end_date,unit) ,Start_date 代表时间段内第一个日期或起始
日期 End_date 它代表时间段内最后一个日期或结束日期 Unit 为所需信息返回类型 Unit 返回
（五）逻辑函数
IF():执行逻辑判断,它可以根据逻辑表达式真假,返回不同结果,从而执行数值或公式条件 检测 语法:IF(logical_test,value_if_true,value_if_false) 参数:Logical_test 计算结果为TRUE 或FALSE 任何数值或表达式；Value_if_true 是Logical_test 为TRUE 时函数返回值而 且Value_if_true 可以是一个表达式；Value_if_false 是Logical_test 为FALSE 时函数返 回值Value_if_false 也可以是一个表达式
二.模糊匹配时第一列一定要升序排列
（三）查找和引用函数
精确查找案例,下图中要查找AB-S伍销售额,G三为要查找数值,A:D为要查找区域,四表示 要查找值在区域第四列,零表示精确查找,公式如下图所示:
模糊查找案例,下图中要查找收入$二一,五六六对应税率,B三为要查找数值,D三:F九为要 查找区域,三表示要查找值在区域第三列,一表示模糊查找,公式如下图所示:

日期函数应用举例——编制下周计划表
编制下周计划表
可以在本周的任何一天制作下周的计划表
注意：任何一天的日期减去本身的星期数，就一定等于上一个星 期天的日期。
日期函数综合应用——设计考勤表
时间函数
输入时间
输入22:00与输入10:00 PM是一样的。
如何计算跨午夜零时的时间间隔？
输入：=B2+(B2<A2)-A2 或者：=B2+IF(B2<A2,1,0)-A2 思路：如果下班时间小于上班时间，就表示已经过了1天，因此 要加1。否则，如果下班时间大于上班时间，就表示还在当前， 因此不需要加1天。
= DAY(日期序列号) 例如 DAY("2006-12-22")=22
DATE函数：返回代表特定日期的序列号。
= DATE(年,月,日) 例如 DATE(2006,12,23)= "2006-12-23"
DAYS360函数：按照一年 360 天计（每个月以 30 天， 一年共计 12 个月），返回两个日期间相差的天数。
求和函数: 应用举例 —— 隔栏加总计算
某预算表如图所示，每个月分为“预算数”和“实际 数”，现要求计算年度“预算数”和“实际数”。这实 际上就是隔栏加总问题。如果一个一个单元格相加是很 繁琐的。
解决方法：巧妙应用第3行的标题。
求和函数: 应用举例 —— 往下（或往右）累加汇总
往下（或往右）累加汇总是常见的实务问题。
= OR(条件1,条件2,…条件N)
说明：这3个函数常常联合使用。
IF函数应用举例 之一 ——计算奖金
销售人员 销售额 奖金
AAAA
654 26.16
BBBB

使用场景
自定义函数适用于处理复杂的数据计算、数据转换和数据处理任务。例如，当内 置函数无法满足特定需求时，可以使用自定义函数来处理特定的数据格式、执行 复杂的计算逻辑或实现特定的业务规则。
27
复杂问题解决方案探讨
2024/1/29
处理大量数据
当处理大量数据时，可以使用数组公式和自定义函数来提高计算效率。通过合理的数据组 织和公式设计，可以减少计算时间并简化数据处理过程。
多条件筛选与汇总
针对多条件筛选和汇总问题，可以使用数组公式结合内置函数（如SUMIFS、COUNTIFS 等）来实现。通过构建适当的条件数组和计算逻辑，可以方便地对数据进行筛选和汇总操 作。
复杂数据转换与处理
对于复杂的数据转换和处理任务，可以使用自定义函数结合VBA编程来实现。通过编写自 定义函数，可以实现特定的数据转换规则、处理逻辑和数据验证等功能，以满足复杂数据 处理的需求。
文本连接
使用`&`或`CONCATENATE`函数将多个文本字符串连接成 一个字符串。
示例
=A1 & " " & B1或 =CONCATENATE(A1, " ", B1)
文本拆分
使用`LEFT`、`RIGHT`、`MID`等函数提取文本字符串中的特 定部分。
8
字符串查找与替换
查找字符串
使用`FIND`或`SEARCH`函数在 文本字符串中查找子字符串的位
置。
示例
查找单元格A1中"abc"的位置： `=FIND("abc", A1)`
2024/1/29
替换字符串
使用`SUBSTITUTE`或 `REPLACE`函数替换文本字符串

3. OR函数
OR在英文里是“或，或者”的意思，只要满足任一条件就 返回ture(真)，否则返回false(假),一般要与其它函数套 用，比如IF函数。 语法：OR(条件1，条件2，……条件30) 举例： or(A1>60,B2<100,C3>80),当A1>60,B2<100,C3>80 这三个条件有一个满足时，返回TRUE,否则FALSE.
Unit 2 逻辑函数的应用
1. IF函数 2. AND函数 3. OR函数 4. NOT函数
1. IF函数
IF在英文里是“假如，如果”的意思，顾名思义就是如果满足某个条件（为真） 则返回前面的一个值，不满足条件（为假），返回后面的一个值。 语法：IF(逻辑值，返回值1，返回值2)，其中逻辑值类型为真或假，另外返 回值是文本格式时，要用双引号，如果为数字，可以不用双引号。 举例：如何判断考试成绩是否及格？ IF(A1<60,“不及格”,“及格”)，如果单元格A1中的成绩小于60，则 返回不及格，反之，返回及格。 IF(A1<60 ,“不及格”,IF(A1>84,“优秀”,“及格”))，这是IF函数 套用IF函数，如果A1小于60，返回不及格，另外如果大于等于85，则返回 优秀，否则返回及格。
Unit 5 数学与统计函数 1. Sum、average、abs、mod函数 2. sumif、countif函数 3. max、min、small、large、rank函数 4. value、isodd、iseven函数
1.Sum、average、abs、mod、power函数
Sum函数作用是求和
Power(数值1，数值2)，其中数值1是数，数值2是多少次方
以上函数返回值皆为数字

用于提取日期中的天数，例如 `=DAY("2023/7/5")`将返回5。
YEAR函数
用于提取日期中的年份，例如 `=YEAR("2023/7/5")`将返回2023。
MONTH函数
用于提取日期中的月份，例如 `=MONTH("2023/7/5")`将返回7。
日期和时间格式化函数FORMAT
日期格式化
图表样式和布局
自定义图表的样式和布局，包括颜色、字体、数据标签、图例等， 使图表更加美观和易于理解。
数据透视表
利用数据透视表对数据进行多维度分析，可以通过拖拽字段、设置值 字段等方式定制报表，同时支持数据的筛选、排序和分组。
实践操作：使用常用函数解决实际问题
日期和时间函数
运用DATE、TODAY、YEAR等日期和时间 函数进行日期和时间的计算和处理，如计
功能
从文本字符串的左侧开始截取指定数量的字符。
语法
LEFT(text, [num_chars])
文本截取函数LEFT、RIGHT和MID
• 示例：=LEFT("Hello World", 5) 结果为 "Hello"
文本截取函数LEFT、RIGHT和MID
功能
从文本字符串的右侧开始截取指定数量的字符。
• 示例：=FIND("World", "Hello World") 结果为 7
文本查找和替换函数FIND和REPLACE
功能
在文本字符串中替换另一 个文本字符串。
语法
REPLACE(old_text, start_num, num_chars, new_text)

26
3.将条件值反过来函数NOT

NOT( 条件) 括号中条件值为FALSE，则为TRUE ； 如果为TRUE，则为FALSE
=NOT（ 5>3 ）= FALSE =NOT（ 1>3 ）= TRUE
27
4.执行条件函数IF

IF( 条件, 执行条件真, 执行条件假) 可以执行七层嵌套.
=IF（ 5>3 ,2,3）= 2 =IF（ 5<3 ,2,3）=3 =IF (5>3, IF ( 1>2,6,7) ,0)=7
目
1、函数相关概念: 2、常用函数 3、常用函数组合 4、EXCEL中宏的运用
录
1
第一部分:相关概念
1.函数语法: 由函数名+括号+参数组成 例: 求和函数: SUM(A1,B2,…) 参数与参数之间用英文 逗号“ , ” 表示
2
2、运算符

公式运算符: + - * / % ^
比较运算符: = > < >= <= <> 引用运算符: , :

WEEKDAY(日期 )
A1=2003-07-29 星期二
=WEEKDAY（A1）= 3
20
(三)信息函数:


测试是否为错误值函数ISERROR 测试是否为错误值#N/A函数 ISNA
21
1.测试是否为错误值函数ISERROR

ISERROR( ) 括号中为：#N/A、#VALUE、#REF、 #DIV/0、#NUM、#NAME？或#NULL时为 TRUE =ISERROR（ #N/A ）= TRUE 说明结果 错误 =ISERROR（ #N/A ）= FALSE 说明结果 正确