excel常用函数
一、常用基础函数
1.1 截取字符串
left(字符串,字符个数) 从左边截取
mid(字符串,开始位置,字符个数) 从中间截取
right(字符串,字符个数) 从右边截取
1.2 and()和or()
AND()函数语法是这样的:AND(条件1=标准1,条件2=标准2……),每个条件和标准都去判断是否相等,如果等于返回TRUE,否则返回FALSE。
只有所有的条件和判断均为真时返回TRUE,也就是所有条件都满足时AND()函数才会返回TRUE。
OR()函数语法是这样的:OR(条件1=标准1,条件2=标准2……),和AND一样,每个条件和标准判断返回TRUE或者FALSE,
但是只要所有判断中有一个返回TRUE,OR()函数即返回TRUE。
1.3 if(测试条件,真值,假值)
1.3.1 if单条件判断示例(业绩大于2500视为合格)
1.3.2 if嵌套多条件使用(业绩2300以下为差,2300-2500为良,2500以上为优)
1.3.3 if函数用AND与OR组合多个条件使用方法
1.3.4 使用if合并AB两列的内容,如果A列不为空使用A列的数据,否则使用B列的数据。
1.4 match(查找值,查找区域,匹配条件)
查找值:查找依据,需要查找的数据。
查找区域:包含目标值的所有连续单元格区域,查找区域只能为一列或一行。
匹配条件:match_type=0(精确查找),查找精确等于lookup_value的第一个数值,lookup_array按任意顺序排列。一般只使用精确查找。
match_type=1,查找小于或等于lookup_value(目标值)的最大数值在lookup_array(查找区域)中的位置,lookup_array必须按升序排列。
match_type=-1,查找大于或等于lookup_value(目标值)的最小数值在lookup_array(查找区域)中的位置,lookup_array必须按降序排列。
1.4.1 查找小红在表中的位置。
1.4.2 查找小于或等于(大于或等于)目标值的最大数值的位置实例
1.5 index(数组区域,行序数,列序数,[区域序数])
二 、vlookup(查找值,数据表,序列数,匹配条件)
查找值:根据什么查找,查找值必须位于数据表的第一列;
数据表:查找的区域,如果查找到多个值,只返回查找到的第一个所对应的数据;
列序数:返回数据表中第几列的数据(查找结果所对应的);
匹配条件:可选,如果为0表示精确查找;1或省略表示模糊查找。模糊查找返回精确匹配值或近似匹配值,如果找不到精确匹配值,
则返回小于lookup_value的最大值。
2.1 正向匹配(数据表中加$是为了固定查询的范围)
2.2 逆向匹配(列交换位置或使用vlookup函数+if{1,0})
注:{}在EXCEL中用来定义数组的。举例:假如a1中内容为abc=mid(a1,{1,2,3},1) 这个函数的意思是分三次将abc中的内容取出。第一次取大括号中的1,
就是取第1个。接着第取第2个,第3个。如果这个函数写成=mid(a1,{1,2},1),那就只取两个数字了。就是第一个和第二个。也就是说公式会从{}中取值,依次运算。
如果不是用这个{},那公式一次只能取一个值了。
2.3 多条件查找
2.3.1 结合数组if({1,0},...)使用
2.3.2 添加辅助列
2.4 查找返回多列数据
注:行参照使用函数row(),列参照使用函数column(),可拖动