导读
今天给大家分享一份合同信息到期提醒的表格,本表格在制作过程中主要用到时间函数(TODAY、YEAR、MONTH、DAY)、NUMBERSTRING(数字转换大小写)、IF等函数以及条件格式的应用!
EXCEL表格服务合同到期提醒
下面详细讲解一下操作步骤:
一、到期时间的计算
通过签约时间,和年限来计算到期时间,如第一条记录2018-08-06,我的思路是月份:MONTH(C3)+D3*12,签约时间的基础上加36个月
函数:=DATESTRING(DATE(YEAR(C3),MONTH(C3)+D3*12,DAY(C3))) 显示结果:21年08月06日 (datestring函数转换)
=DATE(YEAR(C3),MONTH(C3)+D3*12,DAY(C3)) 显示结果:2021-08-06
二、大写金额
函数:
=IF(F3="","",NUMBERSTRING(F3,2)&"元整") 显示结果:壹万贰仟元整
=IF(F3="","",NUMBERSTRING(F3,2)) 显示结果:壹万贰仟
=NUMBERSTRING(F3,2) 显示结果:壹万贰仟
以上三种其实意思是一样,只是用IF函数来修饰一下显示的结果。如IF(F3="","",NUMBERSTRING(F3,2)&"元整"),如F3单元格没有内容,结果显示空白,反之则显示大写金额+"元整"
这里说明一下NUMBERSTRING函数为EXCEL的隐藏函数,在公式框里不显示语法结构。
语法结构:NUMBERSTRING(数字,转换形式),该函数的主要作用是:将阿拉伯数字转换为大写形式。
方法:在目标单元格中输入公式:=NUMBERSTRING(a2,1或2或3)。
如:
=NUMBERSTRING(1205,1) 结果为:一千二百0五
=NUMBERSTRING(1205,2) 结果为:壹仟贰佰零伍
=NUMBERSTRING(1205,3) 结果为:一二0五
三、到期提醒
看起来是不是很有感觉,其实用函数很简单就能现实。
函数:
=IF(D3=TODAY(),"今天到期",IF(D3
显示结果:未到期
思路:到期时间为当天,则显示"今天到期",超过时间,显示"已过期",30天内到期的,显示"**天后到期",大于30天到期的,显示"未到期"
用多重IF函数即可。
IF函数的中文表达式:如果(条件,条件为真时执行的操作,条件为假时执行的操作)
四、条件格式:在到期提醒栏前面加上一个小图标
1、在I3单元格中先计算当前是否过期:函数:=D3-TODAY()
2、选择区域:I3:I8,然后单击顶部"开始"按钮——"条件格式"下拉箭头——"新建规则";
3、选择"基于各自值设置所有单元格的格式",格式样式中选择"图标集",选择图标,分别设置当前值大于等于31天,大于等0且小于31,小于0;
本案例表格中还应用了另外二个条件格式:"到期30天"内的记录,单元格加背景色,"已过期"的记录加删除线;
领取专属 10元无门槛券
私享最新 技术干货