EXCEL常用的五类函数
2021-10-22 12:23:17 4 举报AI智能生成
EXCEL常用函数，提升工作效率
作者其他创作
大纲/内容
基础知识
基础函数
求和
函数
SUM
快捷键
ALT+=
求平均值
AVERAGE
求个数
COUNT
求最大值
MAX
求最小值
MIN
单元格引用
分类
相对引用
相对位置不变
绝对引用
绝对位置不变
混合引用
只保持行或列的绝对位置不变
记忆口诀
列号是字母。$锁在字母前，列不变
行号是数字。$锁在数字前，行不变
行列都不变，挂上双锁头$F$4
EXCEL 函数公式
Llogical
判断式
Value
计算的值或结果
Array
区域
Number
数字
Text
文本
常用函数
逻辑函数
IF函数
功能
判断指定的值,如果满足条件时则…,如果不满足条件时则….
公式可以理解为:=IF(条件,符合条件时的计算方式,不符合条件时的计算方式.)
格式
IF(Logical,Value_if_true,Value_if_false)
Logical
代表逻辑判断表达式
Value_if_true
表示当判断条件为逻辑“真（TRUE）”时的显示内容，如果忽略返回“TRUE”
Value_if_false
表示当判断条件为逻辑“假（FALSE）”时的显示内容，如果忽略返回“FALSE”
IF(判断某个条件是否成立，成立的结果，不成立的结果）
AND函数
功能
检查多个判断条件（"Logical"(逻辑表达式)）是否为真(TRUE)
如果所有条件都为(TRUE)，返回值为TRUE(真);如果任意一项条件为假(FALSE)，则返回FALSE(假)
即所有条件都满足，才为满足【全票通过】
格式
AND(logical1,[logical2],[logical3],…)
logical1
代表逻辑判断表达式1
logical2
代表逻辑判断表达式2
logical3
代表逻辑判断表达式3
AND(判断条件1,判断条件2,判断条件3,…)
OR函数
功能
检查多个判断条件（"Logical"(逻辑表达式)）是否为真(TRUE)
如果任意一项条件都为(TRUE)，返回值为TRUE(真);如果所有的条件为假(FALSE)，则返回FALSE(假)
如果任意一项条件都为(TRUE)，返回值为TRUE(真);如果所有的条件为假(FALSE)，则返回FALSE(假)
格式
OR(logical1,[logical2],[logical3],…)
logical1
代表逻辑判断表达式1
logical2
代表逻辑判断表达式2
logical3
代表逻辑判断表达式3
OR(判断条件1,判断条件2,判断条件3,…)
NOT函数
功能
对参数（逻辑表达式）的逻辑值求反
当逻辑表达式的结果为TRUE(真)时，结果为FALSE(假)
当逻辑表达式的结果为FALSE(假)时，结果为TRUE(真)
格式
NOT(logical)
logical
代表逻辑判断表达式
NOT(判断条件)
IFERROR 函数
功能
错误美化
原来错误会显示成空白
格式
IFERROR(公式，“”)
公式BUG的修改
写错了
不计算
文本函数
LEN函数
功能
快速查出单元格有多少数字
如身份证位数验证
格式
LEN(指定单元格)
RIGHT函数
功能
一个文本字符串从右到左拆解
格式
=RIGHT(text,num_chars）
text
文本字符串
num_chars
拆的位数
LEFT函数
功能
一个文本字符串从左到右拆解
格式
=LEFT(text,num_chars）
text
文本字符串
num_chars
拆的位数
FIND函数
功能
拆分文本，拆分到分隔符号
格式
=FIND(find text,within_text,start_num）
find text
符号
Within_text
目标字符串
start_num
从第几位开始查询
一般不输入第3个参数
默认为1
MID函数
功能
拆分文本
格式
=MID(text,start_num,num_chars）
text
符号
start_num
从第几位开始查询
num_chars
取几位
应用
截取出姓名
TEXT函数
功能
设置一个值，显示指定格式
指定格式两边需要加英文字符的双引号
格式
=TEXT(value,format_text）
Value
指定值
format_text
指定格式
应用
将文本格式的日期转化为真日期
文本型数字的转化
DATEDIF
功能
计算两个日期之间相隔的年数、月数和天数
格式
=DATEDIF (开始日期，结束日期，计算类型）
应用
计算年龄
补充应用
连接符&
功能
将需要连接的部分连接起来
应用
将不同列的名字和身份证号结合在一起
MOD函数
功能
求余函数
快捷键 Ctrl+E
功能
快速填充
条件
2013以上版本
数据要有规律，第一行手写规律
去除空格
TRIM函数
日期函数
基础日期函数
TODAY 函数
功能
显示计算机系统当天的日期
NOW函数
功能
自动计算你的计算机系统当前显示的日期及具体时间
按F9时可自动刷新
YEAR函数
功能
自动计算某一日期的年份
MONTH函数
功能
自动计算某一日期的月份
DAY函数
功能
可以计算某一日期具体的天数
DATE函数
功能
可以组合一个完整的年月日格式的真日期
DATEIF函数
功能
计算2个日期相隔的年数、月数或者是天数
格式
=DATEDIF (开始日期，结束日期，计算类型）
提示
计算类型里面的内容需要用 英文键盘下的引号去引起来
NETWORKDAY函数
功能
计算两个日期之间相隔的净工作日
应用
项目时间进度表
倒计时
条件格式与函数结合 项目进度管理
函数
IF
AND
TEXT
条件格式
突出显示单元格规则
使用公式
确定要设置格式的单元格
快速录入日期时间
快捷键
ctrl+;
快捷键输入的只是数据，不会随日期、时间变化而变化
统计函数
基础统计函数
SUMIFS函数
功能
对某区域进行条件求和
格式
=SUMIFS(sum_range,criteria_range 1,criteria1)
sum_range
求和区域
criteria_range 1
条件区域
criteria1
条件
COUNTIFS函数
功能
对某区域满足条件的个数进行计算
格式
COUNTIFS(criteria_range 1,criteria1)
criteria_range 1
条件区域
criteria1
条件区域
AVERAGEIFS函数
功能
对指定区域进行条件平均值计算
格式
=AVERAGEIFS (average_range,criteria_range1,criteria1）
average_range
求值区域
criteria_range1
平均值区域
criteria1
条件
SUMIFS函数模糊条件求和
通配符
*
表示通配符标识任意字符串
？
通配符一个字符串
注：文本与符号两端都要用英文状态下的”“括上
利用名称管理器快速写公式
步骤
选中全部数据
点击公式选项卡，在定义的名称中点击根据所选内容创建
只需要勾选首行前面的“√”，将最左列前面的“√”取消掉，然后点击确定
选中单元格，在编辑区输入函数
查找函数
VLOOKUP函数
功能
根据查找依据，找到目标函数
公式
=VLOOKUP(lookup_value,table_arry,col_index_nubm,range_lookup)
lookup_value
查找依据
查找东西的依据
table_arry
数据表
数据源所在的表格，也就是去哪里找
col_index_nubm
序列数
目标要查哪些数据
range_lookup
匹配条件
0代表精确匹配，1代表模糊匹配
一对一查询
=VLOOKUP(lookup_value,table_arry,col_index_nubm,range_lookup)
多对一查询
添加辅助列
将需要查询的条件用&连接起来
LOOKUP阶梯查询
功能
各种阶梯找匹配
前提条件
LOOKUP在做阶梯判定前，需要先建立起步线
起步线必须是从小到大的升序序列
公式
=LOOKUP(lookup_value, lookup_vector, result_vector)
lookup_value
查找目标
lookup_vector
起步线标准
result_vector
结果区域
彩蛋—宏功能
文件—选项—自定义功能区—开发工具。点击确定
开发工具选项卡—点击录制宏
修改宏名，点击确定
这时在EXEL中做任何操作，都会被EXCEL录制记录下来
操作结束后，点击停止录制
为录制的宏插入一个小按钮，实现一键点击，就可以完成数据复制的过程
开发工具选项卡—插入—窗体—点击按钮
利用鼠标在EXCEL内拖拽的方式绘制一个小按钮
击鼠标在空白处拖拽一个小方块，松手后出现一个"指定宏"弹窗
在宏名输入框中输入宏名
INDEX+MATCH 二维查找
MATCH函数
功能
确认查找值所在的位置
格式
=MATCH(lookup_value,lookup_array,lookup_type)
lookup_value
查找值
lookup_array
查找区域
type
0
INDEX函数
功能
返回查找结果
格式
=INDEX(查找范围，行号，列号)
彩蛋：打造二维查找的聚光灯
行聚光灯
新建规则
根据公式设置条件
设置填充颜色
列聚光灯
新建规则
根据公式设置条件
设置填充颜色
交叉区域聚光灯
新建规则
根据公式设置条件
设置填充颜色
COUNTIFS函数是满足条件的个数计算；只需要写判定的条件区域和条件，不需要求和区域
0 条评论
下一页
