Excel学习05-CSDN博客

本文链接：https://blog.csdn.net/RacheloLi/article/details/148836001

参考视频: 北风网教学视频

相关数据: https://pan.baidu.com/s/1fiyqOjsa04lnU6HW7xPJIQ?pwd=dur2 提取码: dur2

函数公式02

1.引用函数（indirect）
在这里插入图片描述
用例子说明了indirect是文本引用，不能直接在函数中写单元格，除非是写了文本引用的单元格
练习下indirect：

D列是汽车集团，后面列是旗下的品牌，而要填充的是AB列

用数据验证做“汽车集团”下拉列表选择：（比较简单就不演示了）

不知道怎么做的可参考之前的文章excel学习02：https://blog.csdn.net/RacheloLi/article/details/148806673?spm=1011.2124.3001.6209

用数据验证+indirect函数做“旗下品牌”下拉列表选择：
先定位条件选择“常量”去除空值——然后创建内容：
内容创建勾选最左列作为值的名称，在名称管理器中可查看创建的结果
接着就可以和上面一样，选中列表-选择数据验证-序列-来源处写上indirect函数：

最终效果就是可一一对应用下拉列表选择:

2.convert函数（单位数值转换）
函数格式convert(number,from_unit, to_unit)=convert(要转换的数值，原本的单位，要转换的结果单位)
在这里插入图片描述函数内单位要用引号，会有提示可选择

3.mod函数
是用来求余数的函数,返回两数相除的余数。一般不单独使用,经常和其他函数组合起来使用。
函数格式mod (number, divisor)=mod(被除数,除数)

场景应用(从身份证号中识别性别): 通过mid函数截取身份证的倒数第二位, 如果是偶数为女性, 奇数为男性, 所以判断奇偶就可以用mod函数求余数来确定了.
在这里插入图片描述先截取身份证倒数第二位用mid函数
用mod函数取余，嵌套在if函数中判断返回性别结果

4.rand和randbetween函数 (返回随机数)
函数格式Rand ()返回0-1间的随机数;
RANDBETWEEN (bottom, top)=RANDBETWEEN (返回的最小整数, 返回的最大整数)返回随机整数.
每次刷新或计算工作表时都会随机返回一个新值.

对于问题二randbetween比较简单，这里演示下rand如何返回比1大的随机数:
在这里插入图片描述

对于问题三返回日期随机数，可以用randbetween后，更改单元格式为日期:
在这里插入图片描述

5.round函数(返回保留小数位后的结果)
Round函数返回一个数值,该数值是按照指定的小数位数进行四舍五入运算的结果。
函数格式round(number,digits)=round(要四舍五入的数, 小数点后保留的位数)

练习:
在这里插入图片描述先根据已知数据求出黄色部分，用自动求和求出合计部分

当对数据增加小数位后会发现小数位后不止2位，如果对两个合计进行相加则返回的结果会有误差:
在这里插入图片描述

此时可以先对结果用round函数取出小数点后两位，再进行最终合计:
在这里插入图片描述

6.日期函数
Today函数表示返回当前日期。公式=today()。
NOW函数,用于返回电脑设置的当前日期和时间的序列号。公式 = NOW().
在这里插入图片描述

对于单元格式是常规或数值的，在输入日期后，可以在数字栏位下选择单元格类型为日期：
在这里插入图片描述

日期可识别的格式：xxxx/xx/xx或xxxx-xx-xx，不可识别的：xxxx.xx.xx
日期可以做加减运算，计算天数：
在这里插入图片描述

提取日期和时间函数：公式=Year()；公式=month()；公式=day()；公式=hour()；公式=minute()；公式=second()；Date函数拼接日期；Time函数拼接时间
在这里插入图片描述

WEEKDAY函数：返回值为一个数字,表示星期几。常使用WEEKDAY函数计算某天为星期几。
公式:WEEKDAY(serial_number,return_type)=WEEKDAY(要返回日期数的日期,确定返回值类型的数字)
在这里插入图片描述
第二个参数要选2或11，代表的是一周的开始从周一开始（国内说法），而1代表的是一周从周日开始（国外说法）

DATEDIF函数：是个隐藏函数（即敲前面字母后不会有提示跳出来这个函数），但功能非常强大的日期函数,主要用于计算两个日期之间的天数、月数或年数。
公式DATEDIF(start_date,end date,unit)=DATEDIF(起始日期,结束日期,所需信息的返时间单位代码)
unit各代码含义如下:“y"返回时间段中的整年数；"m”返回时间段中的整月数；"d"返回时间段中的天数；"md”参数1和2的天数之差,忽略年和月；"ym“参数1和2的月数之差,忽略年和日；"yd”参数1和2的天数之差,忽略年，即按照月、日计算天数。
在这里插入图片描述
这里返回3而不是4的原因是，因为结束日期为2月份而不是4月份即构不成一个完整的年，所以不计算第4年

注意区别md（忽略年月）和yd（忽略年）
在这里插入图片描述

7.文本函数
FIND函数：FIND(find_text,within_text,start_num)=FIND（要查找的字符串，包含要查找关键字的单元格，指定开始进行查找的字符数）
如果 within_text中没有find_text,则FIND返回错误值#VALUE !.
在这里插入图片描述
FIND函数查找的字符串是区分大小写的

search函数与find函数的区别：
search函数查找字符串不区分大小写
在这里插入图片描述

Trim()函数:去掉首尾空格（若中间有多个空格会保留一个空格）。
Clean()函数:删除其中含有的当前操作系统无法打印的字符。
在这里插入图片描述

CONCATENATE函数：CONCATENATE(text1, [text2], … )（Text1为必需要连接的第一个文本项。）
也可以使用连接符号(&)计算运算符代替CONCATENATE函数来连接文本项。
在这里插入图片描述
&比较简单就不演示了（=A38&“-”&B38）

LEFT和RIGHT函数：LEFT(text, num_chars)=left（要截取的内容，截取的字符的数量）（right函数也一样）
在这里插入图片描述

MID函数：是从一个字符串中截取出指定数量的字符。
公式MID(text, start_num, num_chars)=MID(被截取的字符, 从左起第几位开始截取, 从左起向右截取的长度)。
Len函数公式:Len(string | varname)功能是返回文本串的字符数。
在这里插入图片描述

TEXT函数：text(value,format_text)=text(数字值,设置单元格格式中自己所要选用的文本格式)
在这里插入图片描述
为了让数字前面的0显示出来可以用text设置单元格式，或者也可以用单引号让其显示（如’0001）

练习：

left+find查找省份字段
在这里插入图片描述

mid+len+find查找市字段，或者=MID(A2,FIND(“省”,A2)+1, FIND(“市”,A2)-FIND(“省”,A2))
在这里插入图片描述

right+len+find查找具体地址
在这里插入图片描述

8.信息函数
IS系列函数：是一个逻辑函数,可以用来判断一些特定的内容。
Istext判断单元格是否是文本。lsnumber判断单元格是否为数值。
在这里插入图片描述

表示为空的方式可以是<>“”，也可以是=“”

IFERROR函数：用来为断某些内容的正确与否, 正确则返回正确结果,错误则返回需要显示的信息。
IFERROR(value, value_if error)表示判断value的正确性,如果value正确则返回正确结果,否则返回value if_error。
其中value的错误格式有#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?、#NULL等。
在这里插入图片描述