在实际的生活或工作中,我们经常会去计算两个日期之间的差值,比如自己多少岁了,距离哪一天过了多少天了,就是这么简单的问题,也往往困扰着我们,同样的问题,也在困扰着强大的电子表格处理工具Excel,在excel中我们应该怎么样精确的计算两个日期之间相差多少年,多少月,多少日呢?
当然,两个日期相减,再经过一些调整是可以实现的,有没有更为简单方便的方法呢?是有的,那就是日期函数DATEDIF。
DATEDIF在Excel中是隐藏的,就是我们插入公式时,是找不到这个函数的,excel的一些版本甚至是没有帮助提示的,类似的函数还有NUMBERSTRING函数,至于为什么会被隐藏,有些人说不常用,其实不是的。
既然在公式提示中没有,那么我们在使用过程中,就需要在单元格中直接写入,这就要去我们对函数的语法非常精确,因为在excel中,公式输入稍有错误便不会得到正确的返回值。
参数Start_date 用于表示时间段的第一个(即起始)日期的日期。 日期值有多种输入方式:带引号的文本字符串(例如 "")、序列号(例如 36921,在商用 1900 日期系统时表示 2001 年 1 月 30 日)或其他公式或函数的结果(例如 DATEVALUE(""))。
参数End_date 用于表示时间段的最后一个(即结束)日期的日期。
参数unit 要返回的差值类型,对照表如下:
【1】 我们将光标定位在需要求出工龄的单元格内
【3】 在括号内填写参数:第一个参数鼠标点选I
【4】 3单元格,第二个参数输入函数today()(对于today函数不了解的同学,参考:),第三个参数输入"Y"
这里需要注意一下两个问题:
第1, 注意第一个参数和第二个参数的顺序
第2, 第三个参数必须加上英文状态的双引号
实例是求得年份差,可以根据第三个参数对照表,求出月份差,天数差等。
VLOOKUP函数(列查找),如果是模糊查询,那么被查询的列数据必须是升序排列。而如果是精确查询,那么被查询的数据可以是无序的。
HLOOKUP函数(行查找),四个参数与VLOOKUP函数相同。
MATCH函数第一个参数是查询的值,第二个参数是查询的区域,只能是一行或者是一列。第三个参数是返回的值的类型。(精确)。这个函数是可以返回所查询值在查询区域的相对位置。
INDEX函数,返回指定位置的内容。第一个参数是查询区域。第二个函数是查询值所在的行,第三个参数是查询值所在的列。
INDEX函数与MATCH函数的嵌套可以向左查询数据。而不是VLOOKUP函数只能向右查询函数。 MATCH函数返回查询值所在的行号,而INDEX函数通过对应的查询值对应的行号查找到相应的数据。
LOOKUP函数有两种查询方式,一种是向量查询,另一种是数组查询。前者是在一列或者一行中进行查询。这种井底方式的查询区域必须要升序排列。否则容易出错。
而在数组查询中,数组区域内,在区域的首列中查找,而返回区域的最后一列。前提是选择查找区域的行数要大于等于列数。
CHOOSE函数,根据序号从列表中选择对应的内容。
count函数计算参数中包含数字(数值型,不包含文本型)的个数
countblank函数计算区域中空白单元格的个数
countif函数,第一个参数是计数的条件区域,第二个参数(criteria)是进行判断的条件。计数
sumif函数,第一个参数是计数的条件区域,第二个参数(criteria)是进行判断的条件。第三个参数是条件求和的区域。注意绝对引用与相对引用。
averageif函数,这个函数是条件求平均值。参数与sumif函数是一样的。
sumifs函数,按多个条件对指定单元格求和。第一个参数是实际求和的区域,第二个参数是第一个条件判断的区域,第三个参数是第一个条件的表达式。(后面依此类推,第二个条件判断的区域,第二个条件的表达式。)
averageifs函数与sumifs函数结构相同,不过它是对指定单元格求平均值。
rank函数,第一个参数是要进行排位的数字。第二个参数是排位的数字区域,第三个参数是排位的方式。
这三个函数结构相同,前两者返回的结果相同。而后者是返回结果的平均值。比如说,23,24,24,25.这四个数的排列。前两个函数的排列顺序是1,2,2,4。而第三个函数的排列顺序是1,2.5,2.5,4
large 和 small函数。返回数据集中的第K个最大(小)值。
offset函数:根据给定的偏移量返回新的引用区域。第一个参数是参照起点,第二个参数是偏移行数。第三个参数是偏移列数。第四个参数是可选参数,返回引用区域的行数。第五个参数也是可选参数。返回引用区域的列数。
indirect函数,返回由文本字符串指定的引用。 第一个参数是引用的文本,第二个参数是引用的类型。(一般第二个参数不填)
这个函数用到了数据有效性,以及公式中的根据所选内容创建(定义的名称)
row函数表示单元格所在的行数。返回引用的行号。row函数的参数可以填也可以不填。
column函数表示单元格所在的列数。返回引用的列号。
隔行插入一空行,隔列插入一空列。前者可以通过定位常量的方法,后者可以通过ctrl+点选的方法。
round函数:按指定位数对数字进行四舍五入。第一个参数是要四舍五入的数字,第二个参数是要四舍五入的位数。
int函数:取整。(将数字向下舍入到最接近的整数)
mod函数:返回两数相除的余数,结果的正负号与除数相同。
可以使用mod函数对单元格进行相应的调整。条件格式,新建规则,使用公式确定要设置格式的单元格。=mod(row(a1),5)=0
rand函数:随机数函数。返回0-1之间的随机数。
randbetween函数:返回两个数(一个区间)之间的随机数。也可以返回两个日期之间的随机日期。
convert函数:度量转换函数,将数字从一个度量系统转换到另一个度量系统中。第一个参数是要转换的数字,第二个参数是原单位,第三个参数是要转换的单位。
在EXCEL中将设为1,用整数表示天,而用小数表示小时(1/24),分钟和秒钟依此类推。所以在EXCEL中日期与时间的本质就是数值。
date函数:返回指定年、月、日组合日期的序列号。(把拆开的年、月、日组合在一起)
time函数:返回指定时、分、秒组合时间的序列号。(把拆开的时、分、秒组合在一起)
自定义日期格式的时候,可以选择在yyyy-mm-dd 后加上aaaa,返回一个带上星期数的天数。
也可以使用weekday函数。这种函数可以直接得出某日期具体是星期几。
networkdays函数:计算日期间所有的工作日。第一个参数表示开始日期。第二个参数表示结束日期。第三个参数表示要剔除的法定节假日。
workdays函数:计算某日期经过X个工作日之后的日期。(扣除周末的两天)
eonmonth函数:返回指定月份的最后一天。两个参数,第一个参数是日期,第二个参数是指定的月份,当该参数为0时,代表返回当月的最后一天。
edate函数:返回指定日期向前或向后几个月的日期。
datedif函数:返回计算开始与结束日期之间的日期间隔。(在excel是隐藏函数)
datedif(开始日期,结束日期,日期间隔单位(Y/M/D))
lendb函数:返回一个文本的字节数。字母、数字、英文的标点符号占一个字节,而一个中文、一个中文符号占两个字节。
find函数:以字符为单元并区分大小写查找指定字符位置。
search函数:以字符为单元不区分大小写查找指定字符位置。
实际上可以将这find函数和mid函数联合使用。先用find函数找到指定位置。而后用mid函数提取指定位数的文本。
left和right函数:表示分别从文本的左边和右边提取指定的字符个数。
trim函数:删除多余空格。但是这个函数只适用于一个空格的时候,如果是“李 驰”这种情况,=trim(“李 驰”)=李 驰。中间还有一个空格。因此这种情况下,可以使用查找替换功能。查找空格,替换为无即可。
删除无法打印的字符,可以减小工作簿的大小,加快打开工作簿的速度。
concatenate函数:将多个文本合并在一起。与&字符的作用是一样的。
英文处理函数:Proper:将文本中每个单词的首字母转换成大写
SUBSTITUTE函数:根据指定的文本进行替换。
第一个参数是要在其中替换字符的文本,第二个参数是要替换后的文本,第三个参数是替换后的新文本。第四个参数是指替换第几次出现的文本。
第一个参数是要设置的文本,第二个参数是要设置的格式代码。
Istext函数:用于判断单元格的内容是不是文本,是就返回真,不是就返回假。
Isnontext函数:用于判断单元格是否为非文本。
Iserr:判断除错误值为NA值以外的所有错误值。
Iferror:第一个参数是公式,第二个参数是当公式为错误时,要返回的值。