EXCEL公式与函数

EXCEL公式与函数

1.概概念

• 公式：公式是Excel的核心功能，功能强大。以=开头，对地址进行引用的计算形式，它是确立数据之间的关联关系，实现的是一种算法，通过其结果来描述这种关系
• 函数：函数实际上是Excel预定义的一种内置公式，他通过使用一些称为参数的特定数值来按照特定的顺序或结构执行计算

2.运算符

• 算术运算符

  • +(加号)
  • -(减号）
  • *(乘)
  • /(除)
  • %(百分比)
  • ^(乘方）

• 关系运算符

  • =(等号)
  • >(大于号)
  • <(小于号)
  • >=(大于等于号)
  • <=(小于等于号)
  • <>(不等于)

3.地址的引用

• 相对引用:你变他就变，如影随形，A2:A5
• 绝对引用，以不变应万变,不变， $A$2
• 混合引用：根据情况变,$A2或a$2

$就像小别针一样，别在谁的前面，谁就不能动

F4键可以互相转换

4.逻辑函数

• if(logical_test,value_if_true,value_if_false)

  参数
  logical_test:给定的判断条件
  value_if_true：条件成立返回值
  value_if_false：条件不成立返回值
  
  功能：逻辑判断，根据真假返回对应的结果
  
  例如：
  单一条件判断:if(B2>=1500,0.1,0.08)
  多条件判断:if(F2>=4,5000,IF(f2=3,3000,0))
  与and和or联合使用:if(and(E2='A类'，D2>=10),5000,0)
  

  • 注意

    and(逻辑判断1，逻辑判断2...),判断都为真，返回True,否则返回false
    or(逻辑判断1，逻辑判断2...),判断有一个为真，返回True,否则返回false
    

5.文本函数

• len

  语法：len(text)
  参数：text文本内容
  功能：返回文本字符串中字符的个数，也叫文本长度，不分中英文和数字，都是1个字符
  

• left

  语法：left(text,[num_chars])
  参数：text文本内容
  	 [num_chars]可选参数，指定left提取字符的个数
  注意：
  	num_chars必须大于或等于0
  	如果num_chars大于文本长度，则返回全部文本
  	如果省略，默认值为1
  功能：从文本字符串左边(开头)起第一个字符开始返回指定个数的字符
  

• right

  语法：right(text,[num_chars])
  参数：text文本内容
  	 [num_chars]可选参数，指定right提取字符的个数
  注意：
  	num_chars必须大于或等于0
  	如果num_chars大于文本长度，则返回全部文本
  	如果省略，默认值为1
  功能：从文本字符串右边(结尾)起第一个字符开始返回指定个数的字符
  

• mid

  语法：mid(text,start_num,num_chars)
  参数：text文本内容
  	 start_num:必选参数，从文本中哪个位置提取，1代表第一个位置，内容包含第一个值
  	 num_chars：必选参数，取多长，也就是去几个字符
  
  功能：从文本字符串的指定位置提取指定长度的字符
  

• left,right,mid实例，身份证提取案例

  生日：mid(D2,7,8)
  后6位：right(D2,6)
  前3位：left(D2,3)
  

• text

  语法：text(内容，格式)
  参数：内容：要进行格式转换的内容
  	 格式：转换指定格式
  	 
  功能：格式转换，类似于设置单元格格式中自定义模式的功能
  
  例如：text(A2,"yyyy-mm-dd")
  

• replace

  语法：replace(old_text，start_num,num_chars,new_text)
  参数：old_text:原来文本
  	 start_num:指定原文本的哪个位置开始,1是第一个
  	 num_chars:取多长
  	 new_text:把原来截取的内容替换成新的内容
  	 
  功能：根据指定的内容，将原文本部分内容替换为新的内容
  例如：replace(A2,8,3,"***")
  

• find

  返回查到的内容的起始位置
  语法：find(find_text，within_text,[start_num])
  参数：find_text:要查找的文本
  	 within_text:包含要查找文本的文本
  	 start_num:可选参数，指定从哪开始找
  	 
  功能：根据指定内容查找，返回要查找的文本所在位置的起始值
  例如：find("EXCEL",A1)
  

6.统计函数

• int

  取整
  语法：int(number)
  参数：number:一般是个小数，正负都可以
  	 
  功能：将数字向下舍入取整(取比这个数小的最大整数)
  例如：int(A1)
  

• round

  四舍五入
  语法：round(number,num_digits)
  参数：number:要四舍五入的数字
  	 num_dihits:要留几位小数
  	 
  功能：将数字四舍五入到指定的小数位
  例如：round(A8,2)
  

• mod

  求余数
  语法：mod(number,divisor)
  参数：number:计算余数的被除数(分子)
  	 divisor:除数(分母)
  	 
  功能：计算两数相处的余数
  例如：mod(A12,B12)
  

• average，求均值

• max,求最大值

• min,最小值

• sum,求和

• count，计数

  语法：average(number1,[number2],[number3]...)
  参数：numer都是要参数计算的数字，单元格引用或单元格区域，最多可包含255个
  	 
  功能：用于计算
  

• sumif

  条件求和
  语法：sumif(range,criteria,[sum_range])
  参数：range:条件所在的数据区域(数据范围)
  	 criteria:给定求和的筛选条件(条件)
  	 sum_range:求和区域，若省略，则代表求和区域与条件所在区域是一样的
  	 
  功能：根据条件求和
  例如：sumif(C2:C40,"A类",D2:D40)
  

• sumifs

  条件求和
  语法：sumifs(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2]...)
  参数：sum_range:求和数据范围
  	 criteria_range1:条件数据范围
  	 criteria1:条件
  	 
  功能：多条件求和
  例如：sumifs(D2:D40,C2:C40,"A类",B2:B40,">5")
  

• countif

  条件求和
  语法：counif(range,criteria)
  参数：range:要计算其中非空单元格数目的区域   
  	criteria:给定的条件
  	
  功能：条件计数
  例如：countif(C2:C40,"A类")
  

• countifs

  多条件求和
  语法：countifs(criteria_range1,criteria1,[criteria_range2,criteria2],...)
  参数：criteria_range1:条件范围
  	 criteria1:条件
  	 
  功能：多条件求和
  例如：countifs(B2:B40,">=10",D2:D40,">=2000")
  

7.查找与引用函数

• vlookup

  多条件求和
  语法：vlookup(lookup_value,table_arry,col_index_num,range_lookup)
  参数：lookup_value:要查找的内容
  	 table_arry:要查找的区域
  	 col_index_num：包含要返回的值的区域中的列号
  	 range_lookup：返回近似或精准匹配--表示为1/True或0/False
  	 
  注意：要查找内容必须在要查找的区域的第一列
  
  功能：查找匹配数据
  

• match

  多条件求和
  语法：match(lookup_value,lookup_arry,[match_type])
  参数：lookup_value:要查找的内容
  	 lookup_arry:要查找的区域
  	 match_type：查找方式
  
  功能：返回查找值所在区域的位置
  row:返回行
  column；返回列
  例如：MATCH(F1,$A$1:$A$18,0)
  

补充内容

offset
index
indirect
sumproduct

8.日期函数

• today(),返回当前的系统日期，

  today()   2022-1-17
  

• now(),返回当前系统的日期和时间，

  now() 2022-1-17 13:14:15
  

• year(日期),返回日期所在的年份

  year(2022-1-17)    2022
  

• month(日期),返回日期所在的月份

  month(2022-1-17)  1
  

• day(日期),返回日期所在月份的天数

  day(2022-1-17)   17
  

• date

  语法：date(year,month,day)
  参数：year:年
  	 month:月
  	 day：日
  
  功能：拼接日期格式
  

• dateif

  语法：dateif(start_date,end_date,unit)
  参数：start_date:开始时间
  	 end_date:结束时间
  	 unit：计算单位(D天，N月，Y年)
  
  功能：计算日期之间的差值
  

9.常见错误信息

• div/0!

  0做除数
  

• NAME?

  在公式中使用了不能识别的名称
  删除了公式中使用的名称，或者使用了不存在的名称，函数名拼写错误
  

• VALUE!

  使用了不正确的参数或运算符
  在需要数字或逻辑值是输入了文本
  

• REF!

  引用了无效的单元格地址
  删除了由其他公式引用的单元格
  将移动单元格粘贴到由其它公式引用的单元格中
  

• NULL!

  指定了两个并不相交的区域，故无效
  使用了不正确的区域运算符或不正确的单元格引用
  

• N/A

  在函数或公式中引用了无法使用的数值
  内部函数或自定义工作表函数中缺少一个或多个参数
  使用的自定义工作表函数不存在
  vlookup()函数中的查找值，lookup_value,False/True指定了不正确的值域
  

• NUM!

  数字类型不正确
  在需要数字参数的函数中使用了不能接受的参数
  由公式产生的数字太大或太小
  

• #####

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