Excel函数基础（二）

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))