Excel函数基础(二)

avatar
作者
筋斗云
阅读量:0

1、单元格名称

框选单元格(不含标题),公式选项卡->定义名称

或者框选单元格后,在左上角的栏位直接输入要定义的名字,按下enter键即可

这样在使用函数时,数据范围参数可以用定义的名称来替代

比如:=SUM(金额)

=COUNTIF(金额,">1000") 注意:金额不需要加双引号,而条件>1000必须加上双引号

=COUNTIF(球,"篮球")

公式->名称管理器->编辑->修改范围的名字以及其所包含的单元格

也在这里删除名称
 

2、日期和时间的计算

计算工作时长:单元格数值相减,然后将结果单元格的格式调整为:自定义->时分秒

获取今天的日期:=today()

计算年资(日):=TODAY()-G2 再将日期格式调整为常规即可

计算年资(年、月或日):DATEDIF(开始日期,结束日期,计算单位)计算两日之间的天数、月数或年数

注意:这个是隐藏函数,搜索不到

计算单位:"Y" "M" "D"

计算轮班天数(含节假日的影响,不能直接相减):

NETWORKDAYS(起始日期,结束日期,节假日)

注意:将节假日表格框选出来时,不能带小标题,而且要锁定单元格区域

3、从单元格内取得自己所需的数据

5个文字函数

left函数:从单元格的左侧截取资料 LEFT(截取范围,截取字数)

注意截取范围理应只为一个单元格(大的或小的),对应仅一个文本字符串

right函数:从单元格的右侧截取资料

mid函数:MID(截取范围(文本单元格),准备提取的第一个字符的位置(从1开始),要提取出来的字符串长度) 即 MID(Text,Start_num,Num_chars)

find函数:找出特定字符在文字中的顺位,如果有多个则只显示第一个所在位置  FIND(要查找的字符,文本所在单元格,[ 从第几个字符开始搜寻 ] )

len函数: 计算单元格内的字母个数和 =LEN(文本所在单元格)

对于:短袖-女-M

如何用上面的方法分割数据呢?(也可以用数据分隔符号分列的方法

商品名称:=LEFT(A2,FIND("-",A2)-1

性别:=MID(A2,FIND("-",A2)+1,1)

尺寸:=RIGHT(A2,LEN(A2)-FIND("-",A2,FIND("-",A2)+2))  

FIND("-",A2,FIND("-",A2)+2)是第二条-线所在顺位(加一个搜索的起始位点就可以了)


4、FILTER函数

VLOOKUP函数无法返回多笔资料,且如果查询条件不在表格最左侧,或者表格横向排列的话,

就无法使用该函数

可以使用filter(筛选)来解决

FILTER(筛选范围,筛选条件)

注意:第一个参数筛选范围不包括筛选条件列(否则会重复出现信息),

第二个参数是条件列=条件

比如:根据文具名,得到该文具的所有信息

返回单一资料: 筛选范围只选择想要知道的那一列就可以了

5、INDEX+MATCH函数

INDEX(查询范围,查询顺位),返回该顺位的资料

查询的范围可以是一行或者一列

或者是一个表格,查询顺位就应该是:第几行,第几列

MATCH(查询条件,查询范围,匹配方式),返回顺位(第几号)

注意:MATCH的范围必须是单行或者单列,而不能是整个表格

匹配方式是指精准比对0或者模糊比对1

比如:=MATCH("冰冰",C11:C13,0)

find返回的是特定字符在文字中的顺位,而match返回的是资料在行或列中的顺位

6、设计一个查询表格

第一步:

首先设计一个下拉单选,加速使用者的查询流程:

数据->数据验证->设置验证条件为序列,然后再在表格中圈选姓名列

第二步:match获取查询的资料位置

第几行:比如“小明”所在的是第几行 =MATCH(F23,C23:C31,0) 

        C23:C31是姓名列,但是不包括小标题

第几列:比如“工资”所在的是第几列 =MATCH(H22,B22:D22,0)

        B22:D22是小标题行

第三步:index传回资料

=INDEX(B23:D31,MATCH(F23,C23:C31,0),MATCH(H22,B22:D22,0))

广告一刻

为您即时展示最新活动产品广告消息,让您随时掌握产品活动新动态!