0. Excel公式函数介绍
Excel内置许多公式函数可以完成数值计算功能、逻辑判断功能、查找匹配功能等,在日常数据处理和报表分析中使用公式函数来提升工作效率。
1. 逻辑函数
- IF
功能:条件判断,满足条件时执行操作一,不满足条件时执行操作二。if函数可以结合下面的and,or,not判断条件IF(logical_test,[value_if_true],[value_if_false])
- AND
功能:逻辑与操作,当所有判断条件都成立时返回True,反之返回FalseAND(logical1,[logical2]...)
- OR
功能:逻辑或操作,当判断条件中有某一个成立时返回True,反之返回FalseOR(logical1,[logical2]...)
- NOT
功能:逻辑非操作,即翻转布尔值NOT(logical)
2. 查找函数
- match
功能:返回符合特定值特定顺序的项在数组中的相对位置MATCH(lookup_value,lookup_array,[match_type])
查找上海所在的位置
MATCH("上海",C2:C14) # 结果返回3
- index
功能:在给定单元格区域,根据行列索引值返回单元格的值index(array,row_num,[col_num]
查找表格中第2行第2列
=INDEX(A2:C14,2,2) # 返回菲亚特
- lookup
功能:从数组或向量中查找一个值lookup(lookup_value,array) lookup(lookup_value,lookup_vector)
查找销售李乐菱所销售品牌
LOOKUP("李乐菱",A2:B14) # 返回宝马
-
vlookup
功能:收搜表区域首列满足条件的元素,确定待检索单元格的行编号,再返回相应的值vlookup(lookup_value,table_array,row_index_num,[range_lookup])
根据销售额计算奖金
VLOOKUP(A2,$D$7:$E$18,2)*A2 # 返回219.3
计算奖金结果如下
- hlookup
功能:收搜表区域首列满足条件的元素,确定待检索单元格的列编号,再返回相应的值hlookup(lookup_value,table_array,row_index_num,[range_lookup])
计算奖金结果如下
3. 统计函数
- sum
功能:求和
*sumif
功能:条件求和
计算销售额大于5000的销售额总和SUMIF(A2:A6,">5000") # 返回8052
- sumifs
功能:多条件求和
计算销售额在3000-5000之间的销售额总和SUMIFS(A2:A6,A2:A6,">3000",A2:A6,"<5000") # 返回12272
- average
功能:求平均值 - max
功能:计算最大值 - min
功能:计算最小值 - count
功能:计数 - countif
功能:条件计数
计数上面销售额大于5000COUNTIF(A2:A6,">5000") # 返回1
- countifs
功能:多条件计数
计数销售额在3000-5000之间COUNTIFS(A2:A6,">3000",A2:A6,"<5000") # 返回3
- rank
功能:返回某一数值相对于其他数字的排名rank(number,ref,[order])
4. 引用函数
- indirect
功能:返回文本字符串指定的引用INDIRECT(ref_text,[a1])
制作全国地区和省份两级下拉菜单
(1)地区字段设置数据认证
(2)省份字段数据认证
最后效果如下
5. 数学函数
- round
功能:按照指定的位数进行四舍五入round(number,num_digits)
例如将圆周率取2位有效数值
ROUND(3.141592653,2) # 返回3.14
- roundup
功能:向上舍入数字roundup(number)
- rounddown
功能:向下舍入数字rounddown(number)
- int
功能:向下取整int(number)
- trunc
功能:将数字截为整数或指定位数小数trunc(number,[num_digits])
6. 文本函数
- find
功能:查找字符在单元格中的位置,返回位置索引find(find_text,within_text,[start_num]) start_num:开始查找文本的编号
对于地址"江苏省南京市宣武区新街口街道"需要提取省份信息,可以使用如下公式实现
=LEFT("江苏省南京市玄武区新街口街道",FIND("省","江苏省南京市玄武区新街口街道"))
- left
功能:从单元格中左侧提取若干长度文本left(text,[num_chars]) num_chars:提取字符个数,默认提取一个字符
对于上文中提取省份信息,可以直接用
LEFT("江苏省南京市玄武区新街口街道",3)
上文中先使用find查找"省"出现的位置,再用left查找省份信息。这样操作具有一般性,不是多有的省份字数都一般多,比如黑龙江省,就需要提取字符数是4
- right
功能:从单元格中右侧侧提取若干长度文本right(text,[num_chars]) num_chars:提取字符个数,默认提取一个字符
还以上面的地址信息为例,这次需要提取街道信息,可以使用公式
RIGHT("江苏省南京市玄武区新街口街道",LEN("江苏省南京市玄武区新街口街道")-FIND("区","江苏省南京市玄武区新街口街道"))
- trim
功能:用于把单元格内容前后的空格去掉,但并不去除字符之间的空格trim(text)
- concatenate
功能:拼接字符串concatenate(text1,text2...)
- upper
功能:将小写英文字母转化为大写英文字母upper(text)
- lower
功能:将大写英文字母转化为小写英文字母lower(text)
- replace
功能:将字符串中部分字符串用另一个字符串代替replace(old_text,start_num,num_chars,new_text)
例如将"江苏省南京市玄武区新街口街道"中"新街口街道"替换为"玄武湖街道"
=REPLACE("江苏省南京市玄武区新街口街道",FIND("区","江苏省南京市玄武区新街口街道")+1,LEN("江苏省南京市玄武区新街口街道")-FIND("区","江苏省南京市玄武区新街口街道"),"玄武门街道")
- substitute
功能:将字符串中部分字符串用新字符串代替substitute(text,old_text,new_text,[instance_num] instance_num:指明替换数目,默认全部替换
例如将"中国-北京-北京市区"替换为"中国-上海-上海市区",公式为
SUBSTITUTE("中国-北京-北京市区","北京","上海")
- clean
功能:删除文本中所有非打印字符clean(text)