公式与函数

公式与函数

  • 概念
  • 运算符
  • 地址的引用
  • 逻辑函数
  • 文本函数
  • 统计函数
  • 查找与引用函数
  • 日期函数
  • 常见出错信息

概念

公式:Excel的核心功能,功能强大

如果要定义的话,可以说是 以=开头的,对地址进行引用的计算形式

说的高大上一点的话,它就是 确立数据之间的关联关系,实现的是一种算法, 通过其结果来描述这种关系。

函数:函数实际上是Excel 预定义的一种内置公式,它通过使用一些称为参数的特定数值来按 特定的顺序或结构执行计算。

运算符

算术运算符

关系运算符

地址的引用

相对引用:你变它就变,如影随形 A2:A5

绝对引用:以不变应万变 $A$2

混合引用:识时务者为俊杰,根据时局变 $A2 或者 A$2

$就像小别针一样,别在谁前面,谁就不能动了

F4键可以相互转换

逻辑函数

if

语法:if(logical_test,value_if_true,value_if_false)

参数:

  • logical_test 给定的判断条件
  • value_if_true 如果条件成立则返回的值
  • value_if_false 如果条件不成立则返回的值

功能:逻辑判断,根据真假返回对应的结果

实例:

1.单一条件判断

2.多条件判断

3.与and和or联合使用

and(逻辑判断1,逻辑判断2...) 这些判断都为真,返回True,否则False

or(逻辑判断1,逻辑判断2...) 这些判断只要有一个为真,则返回True,否则false

文本函数

len

语法:len(text)

参数:

  • text 文本内容

功能:返回文本字符串中字符的个数,也叫文本长度,不分中英文和数字,都是1个字符

实例:

left

语法:left(text,[num_chars])

参数:

  • text 文本内容
  • [num_chars] 可选参数,指定left提取字符的个数

注意:

num_chars 必须大于或等于0

如果num_chars大于文本长度,则left返回全部文本

如果省略num_chars,则默认值为1

功能:从文本字符串左边(开头)起第一个字符开始返回指定个数的字符

right

语法:right(text,[num_chars])

参数:

  • text 文本内容
  • [num_chars] 可选参数,指定right提取字符的个数

注意:

num_chars 必须大于或等于0

如果num_chars大于文本长度,则left返回全部文本

如果省略num_chars,则默认值为1

功能:从文本字符串右边(末尾)起第一个字符开始返回指定个数的字符

mid

语法:mid(text,start_num,num_chars)

参数:

  • text 文本内容
  • start_num:必须,从文本中哪个位置提取,1代表第一个位置,内容包含第1个的值
  • num_chars:必须,取多长,也就是取几个字符

功能:从文本字符串的指定位置提取指定长度的字符

left right mid 实例: 身份证号码提取案例

text

语法:text(内容,格式)

参数:

  • 内容:要进行格式转化的内容
  • 格式:转换成指定格式

功能:格式转换,类似于设置单元格格式中自定义模式的功能

replace

语法:replace(old_text, start_num, num_chars, new_text)

参数:

  • old_text 原来的文本
  • start_num:指定原文本的哪个位置开始
  • num_chars:取多长
  • new_text:把原文本截取的内容替换成新的内容

功能:根据指定的内容,将原文本部分内容替换成新的内容

实例:

find

语法:find(find_text, within_text, [start_num])

参数:

  • find_text: 要查找的文本
  • within_text:包含要查找文本的文本
  • start_num:可选,指定从哪开始找

功能:根据指定内容查找,返回要查找的文本所在位置的起始值

实例:

统计函数

int

语法:int(number)

参数:number一般是个小数,正负都可以

功能:将数字向下舍入取整

实例:

跟四舍五入不一样,取小的那个整数,注意负数的结果

round

语法:ROUND(number, num_digits)

参数:

  • number 要四舍五入的数字
  • num_digits 要进行四舍五入运算的位置,也就是保留到几位小数的意思

功能:将数字四舍五入到指定的小数位

实例:

mod

语法:ROUND(number, divisor)

参数:

  • number 计算余数的被除数
  • divisor 除数

功能:计算两数相除的余数

实例:

average 计算均值

max 计算最大值

min 计算最小值

sum 求和

count 计数

语法:(number1, [number2], ...) 都是一样

参数:

  • 这些是需要参与平均计算的数字,单元格引用或者单元格区域,最多可包含255个

功能:用于计算

实例:

sumif

语法:SUMIF(range, criteria, [sum_range])

参数:

  • range:条件所在的数据区域
  • criteria:给定求和的筛选条件
  • [sum_range]:求和区域,若省略,则代表求和区域与条件所在区域是一样的

功能:根据条件求和

实例

1.计算员工级别是A类的员工的销售额总和

sumifs

语法:SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2]……)。

参数:

  • sum_range:求和范围。
  • criteria_range1:条件范围。
  • criteria1:条件。

后面的条件范围和条件可以根据实际需要增加。

功能:多条件求和

实例:

计算工作年限大于5年,员工级别是A类的销售额总数

countif

语法:countif(range,criteria)

参数:

  • range:要计算其中非空单元格数目的区域
  • criteria:给定的条件

功能:条件计数

实例:

计算以上有多少个人是A类员工

countifs

语法:COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)

参数:

  • criteria_range1 :条件区域1
  • criteria1:条件1

功能:跟countif用法差不都,就是可以多条件求和

实例:

计算以上有多少个人符合工作年限大于等于10,销售额大于2000

查找与引用函数

vlookup

语法:VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

参数:

  • lookup_value:你想要查找的内容
  • table_array:要查找的区域
  • col_index_num:包含要返回的值的区域中的列号
  • range_lookup:返回近似或精确匹配-表示为 1/TRUE 或 0/假

注意:查找内容必须在要查找的区域的第一列

功能:查找匹配数据

实例:

match

语法:MATCH(lookup_value, lookup_array, [match_type])

参数:

  • lookup_value:查找的值
  • lookup_array:查找区域
  • [match_type]:查找方式

功能:返回查找值所在区域的位置

实例:

row:返回行

column:返回列

补充内容

offset

index

indirect

sumproduct

日期函数

today() 返回当前的系统日期 例如:2020-2-17

now() 返回当前系统的日期和时间 例如:2020-2-17 13:14:25

year(日期) 返回日期所在的年份 year(2020-2-17) --> 2020

month(日期) 返回日期所在的月份 month(2020-2-17) --> 2

day(日期) 返回日期所在的日 day(2020-2-17) --> 17

date

语法:DATE(year,month,day)

参数:

  • year:年
  • month:月
  • day:日

功能:拼接日期格式

实例:

datedif

语法:DATEDIF(start_date,end_date,unit)

参数:

  • start_date:开始时间
  • end_date:结束时间
  • unit:计算单位

功能:计算日期之间的差值

实例:

常见出错信息

  • DIV/0!
    零作除数
  • NAME?
    在公式中使用了不能识别的名称
    删除了公式中使用的名称,或者使用了不存在的名称 函数名的拼写错误
  • VALUE!
    使用了不正确的参数或运算符
    在需要数字或逻辑值时输入了文本
  • REF!
    引用了无效的单元格地址
    删除了由其它公式引用的单元格

将移动单元格粘贴到由其它公式引用的单元格中

  • NULL!

指定了两个并不相交的区域,故无效

使用了不正确的区域运算符或不正确的单元格引用

  • N/A

当在函数或公式中引用了无法使用的数值

内部函数或自定义工作表函数中缺少一个或多个参数

使用的自定义工作表函数不存

VLOOKUP( )函数中的查找值lookup_value、FALSE/TRUE参数指定了不正确的 值域

  • NUM!

数字类型不正确

在需要数字参数的函数中使用了不能接受的参数

由公式产生的数字太大或太小

  • ######

列宽设置问题,不是错误值 ,输入到单元格中的数值太长,在单元格中显示不下

posted @ 2020-09-11 14:17  silencio。  阅读(388)  评论(0编辑  收藏  举报