二维码 购物车
部落窝在线教育欢迎您!

Excel教程:如何通过起始时间,周期,自动计算结束日期和还有多少天结束?

 

作者:E图表述来源:部落窝教育发布时间:2022-05-23 15:29:54点击:1288

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

EXCEL日常工作中,如果需求中带有“自动”字样,往往都会用到VBA,今天就来给大家讲一讲,如果不会VBA那该如何处理呢?比如下面这个通过起始时间、周期,来计算结束日期,以及还有多少天结束的情况!

 

最近看到一份有意思的需求,拿出来给大家分享一下。

 

 

需求如下:

1、在A列、B列填入内容后,C列自动计算,但是“日”要是A列“日”的前一天。例如:A2=2021-08-06B2=10,那么C2=2022-06-05

2、根据C列内容,自动计算从今天算起还有几天到结束日期,今天也算1天。例如:C2=2022-06-05,那么D2=52

3D列字体需要自动有“变色功能”,大于30天为【黑色】,小于等于30天为【红色】;

4、按照D列的天数,把ABC三列自动填充背景色,7天以内(含7天)为【红色】,815天(含15天)为【黄色】,1630天(含30天)为【蓝色】,3160天(含60天)为【绿色】,60天以上不标记颜色;

5、自动按照D列的天数升序排列。

 

初看需求以为是个“青铜”,细一看才发现原来是个“王者”。

基本每一个需求都有【自动】两个字,那我们先给大家展示一下,如何在不使用VBA的情况下实现“自动”?

 

C列“预埋”函数,因为不知道要输入多少行内容,10行?100行?….10000行?好吧,预埋到20000行也就是了。

首先,在名称框中输入单元格区域C2:C20000,然后按回车键;

 

 

此时就选中了C2:C20000单元格区域,然后输入函数

=IF(AND(A2<>"",B2<>""),EDATE(A2,B2)-1,"")

 

 

CTRL+ENTER组合键结束录入,此时C2:C20000就有了“预埋”函数。

D2单元格输入函数=IF(C2="","",DATEDIF(TODAY(),C2,"d"))

 

 

注意一点:需求中没有说结束日期小于当前日期的情况,所以我们可以给个提示询问一下,或者直接用IFERROR函数容错也可以。

然后选中D2单元格,使用“条件格式”设置字体颜色,如下图所示:

 

 

再双击D2单元格的填充柄,填充至D20000单元格。

选中A2:C2单元格区域,根据需求使用“条件格式”依次新增4个条件格式规则,为每个规则设置不同的填充背景色。注意公式中需要锁定列,如下图所示:

 


 

然后我们再把“条件格式”中的【应用于】参数的单元格区域改成【=$A$2:$C$20000】。

 

 

及此,“可以自动的需求部分”就给大家分享完了,我们来看看效果吧!

 

图形用户界面, 应用程序, 表格, Excel
描述已自动生成

 

至于最后一个需求:自动排序,常规的操作下,排序是没有“自动”功能的。

今天我们分享个简单的VBA代码,可以实现一键排序,如下:

Sub 排序()

  With Sheets(1)

    Set rg = .Range("A1:D" & .[B65000].End(3).Row)

    rg.Sort key1:="还有几天结束", order1:=xlAscending, Header:=xlYes

  End With

End Sub

 

ALT+F11,打开VBE界面,在左侧工程栏,鼠标右键——插入——模块,然后在代码区,输入上面的代码。

 

 

回到工作表界面,在工具栏——“开发工具”——插入——表单控件——按钮控件,插入一个按钮控件,按下图设置内容。

 


 

及此,此需求就全部完成了,我们看看排序的效果吧!

 

表格
描述已自动生成

 

特别提醒:使用VBA后,一定要把文件另存为.XLSM格式的文件哟!

 

 

好啦,以上就是今天的所有内容,感谢你的观看!

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

如何提取唯一值?试试TEXTJOIN函数搭配VBA自定义!

别怕,VBA入门级教程来了,条件语句很简单!

如何在特定位置批量插入空行等12种实用办公技巧

工资表转工资条,VLOOKUP有绝招!

版权申明:

本文作者E图表述;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。