Excel表格之道 学习笔记（三）

第五章 字段调整

5-1 函数简介

函数的主要作用：根据现有数据，新增一列字段
函数的目的：

• 计算工作
• 分析准备

5-2 学习路径

5-3 单元格的命名

可以给单个或者一个区域的单元格进行命名，方便之后的引用。
可以采用手工命名的方式或者批量命名

命名方式	操作	演示
手工命名	1.选中要命名的单元格 2.在左上角更改名称
批量命名	1.选中要命名的区域 2.点击根据所选内容创建，即可批量创建

单元格命名后，有什么好处呢？
比如将分数区域命名为“分数”后，就可以直接使用求和函数sum(分数)，而无需去选中

5-4 引用

引用类型	功能	示例
相对引用	行和列都不固定	=A5
混合引用	只固定行或列	=$A5
绝对引用	行和列都固定	=$A$5

5-5 统计函数

常见的统计函数	功能	演示
power	求n次方
sum
average
count
max
min
sumif	条件求和	例如加和数学成绩
countif	条件计数	例如统计有多少个90分以上的成绩
averageif	条件求平均	例如统计数学的平均分
sumifs	多条件求和
countifs	多条件计数	建议在使用多条件函数的时候，用公式编辑器
averageifs	多条件求平均

5-6 日期函数

日期函数名称	功能
now()	返回现在时刻
today()	返回今天日期
year()	返回年份
month()	返回月份
day()	返回日子
weekday()	计算周几
weeknum()	计算是一年中的第几周
date(year,month,day)	返回对应的日期

⚠️注意：weekday和weeknum这两个函数都需要填写一个return_type参数，保证按照中国历法进行计算。

5-7 文本函数

功能类别	函数
拆	left(text,char_nums) right(text,charnums) mid(text,startnum,len) 分列
合	concatenate() &
转	text trim
量	len
找	find(find_char,text,startnum)

“快速填充”功能几乎能够代替left、right和mid函数，使用情形有限制，需字符长度相同或者有逗号的情况

5-8 随机函数

函数类别	函数
随机	rand()产生0-1的小数 randbetween(A,B)产生A-B之间的整数 norm.inv(probability,mean,std)产生符合正态分布的数字
取整	round(num,digit_num)保留几位小数
绝对值	abs()

5-9 逻辑判断

if(logical_text,value_if_right,value_if_false)
在进行if嵌套时，为了方便编辑公式内容，可以使用alt+enter来进行换行
[实例] 员工生日天数提醒

[思路]因为存在已经过了今年的生日的情况，所以，如果直接用今天-今年生日的那天，会出现负值。为了避免这种情况，所以加了一个if判断

5-10 查找引用

主要介绍函数vlookup,index,match,indirect的使用

vlookup

vlookup的应用场景：

• 一般有2张表
• 这两张表有共通字段
• A表缺少某字段而B表有该字段

vlookup精确查找应用举例

例如我们已知考生类型以及对应的加分政策，我们想将加分项这一列补充完整。
你可以使用if函数，但更简单的方式是使用vlookup函数，就类似于查字典一样，在第二个表格中查找信息，填入第一个表格中。

vlookup(lookup_value,tabel_array,col_index,num)
其中num=0为精确匹配，num=1为近似匹配

两件需要注意的是：
1共通字段一定要位于table_array的第一列
2在进行tabel_array的选择时，注意进行绝对引用。

vlookup模糊查找应用举例

vlookup的模糊查找往往是用在分段计算中

比如我们可能要根据学生的成绩，进行等级上的划分。比如：
[0,60)是不及格
[60,75)是及格
[75,85)是良好
[85,100)是优秀
[100,100+)是满分

index与match函数

match(lookup_value,lookup_array,match_type)：返回坐标
index(array,row_num,col_num)：返回值

index与match应用举例

比如我们制作了一个信息查询表

输入属性和姓名，就可以查询到对应的值
这时候就无法使用vlookup这样的单值查询了，而是需要定位。
首先我们需要用match函数定位到单元格，再用index函数返回单元格内容

操作	演示
1.定位行的位置
2.定位列的位置
3.使用index返回单元格内容

熟练后可以采用函数嵌套的方式来写。

indirect函数

indirect函数主要是用来制作数据的多级验证
首先来理解indirect函数的本质
indirect(ref_text)

以上图为例
B3内存储的内容为"D3"
所以indirect(B3)=indirect(“D3”),获得D3这个位置后，indirect函数返回D3单元格中的内容。

indirect函数制作多级验证

什么是多级验证呢？

比如我们想查看品牌下对应的型号
如果按照我们之前的一级数据验证的操作是不符合要求的。因为它会将所有型号都展现出来。但其实我们是需要它满足一定的筛选能力，只筛选出苹果的型号。

所以我们要做的是多级数据验证，在验证是苹果之后，再去验证苹果下的型号。
这时候就可以用到indirect()函数

具体操作	演示
1.将各品牌手机的型号单独列一列，并依次命名
2.进行数据验证时，注意套用indirect函数

效果就是这样

原理的话，就是使用indirect后，indirect会从E6单元格取型号名称，例如当E6中是"苹果"时，就等价于indirect(“苹果”)，而苹果这个名称所对应的单元格之前已经命名过，indirect就会返回"苹果"所对应的序列。