表格WPS进度条效果分享

显示效果

1，直接使用公式：（可使用txt 替换B2和C2)

进度条

=IF(DATEDIF(B2,TODAY(),"d")/IF(DATEDIF(B2,C2,"d")=0,DATEDIF(B2,TODAY(),"d"),DATEDIF(B2,C2,"d"))>0,REPT("█",(IF(DATEDIF(B2,TODAY(),"d")/IF(DATEDIF(B2,C2,"d")=0,DATEDIF(B2,TODAY(),"d"),DATEDIF(B2,C2,"d"))>=1,1,DATEDIF(B2,TODAY(),"d")/IF(DATEDIF(B2,C2,"d")=0,DATEDIF(B2,TODAY(),"d"),DATEDIF(B2,C2,"d"))))*50)&" "&TEXT(IF(DATEDIF(B2,TODAY(),"d")/IF(DATEDIF(B2,C2,"d")=0,DATEDIF(B2,TODAY(),"d"),DATEDIF(B2,C2,"d"))>=1,1,DATEDIF(B2,TODAY(),"d")/IF(DATEDIF(B2,C2,"d")=0,DATEDIF(B2,TODAY(),"d"),DATEDIF(B2,C2,"d"))),"0.0%"),"未开始")

进度60%以下条件样式

点击查看代码

=IF(IF(DATEDIF(J2,TODAY(),"d")/IF(DATEDIF(J2,K2,"d")=0,DATEDIF(J2,TODAY(),"d"),DATEDIF(J2,K2,"d"))>=1,1,DATEDIF(J2,TODAY(),"d")/IF(DATEDIF(J2,K2,"d")=0,DATEDIF(J2,TODAY(),"d"),DATEDIF(J2,K2,"d")))<=60%,1,0)

1.1 演示结果如下：

项目	项目开始日期	终止日期	项目进度
项目1	22/12/7	22/12/22	未开始
项目2	22/12/2	22/12/22	未开始
项目3	22/11/25	22/12/15	未开始
项目4	22/11/30	22/12/15	未开始
项目5	22/11/20	22/12/8	未开始
项目6	22/11/16	22/12/1	未开始
项目7	22/11/11	22/12/1	未开始
项目8	22/11/29	22/12/1	未开始
项目9	22/11/15	22/11/25	未开始
项目10	22/11/22	22/11/25	未开始
项目11	22/11/5	22/11/25	███████ 15.0%
项目12	22/11/20	22/11/25	未开始
项目13	22/11/15	22/11/25	未开始
项目14	22/11/17	22/11/25	未开始
项目15	22/11/10	22/11/25	未开始
项目16	22/11/19	22/11/25	未开始
项目17	22/11/8	22/11/18	未开始
项目18	22/11/3	22/11/18	████████████████ 33.3%
项目19	22/11/8	22/11/18	未开始
项目20	22/11/13	22/11/18	未开始
项目21	22/11/13	22/11/18	未开始
项目22	22/11/1	22/11/11	███████████████████████████████████ 70.0%
项目23	22/11/6	22/11/11	████████████████████ 40.0%
项目24	22/11/6	22/11/11	████████████████████ 40.0%
项目25	22/11/6	22/11/11	████████████████████ 40.0%

1.2规则详情：

类型	区域	项目开始日期	终止日期	项目进度
测试数据	北美	11月5日	2022/11/30	██████ 12.0%
测试数据	亚洲	11月12日	2022/11/30	未开始
测试数据	中东	10月5日	2022/12/4	████████████████████████████ 56.7%

名称	公式	结果
基础公式	REPT("█",(DATEDIF(E2,TODAY(),"d")/DATEDIF(E2,F2,"d"))*50)&" "&TEXT(DATEDIF(E2,TODAY(),"d")/DATEDIF(E2,F2,"d"),"0.0%")	*
设置颜色	IF(DATEDIF(E2,TODAY(),"d")/DATEDIF(E2,F2,"d")<=60%,1,0)	*
开始天数	DATEDIF(E2,TODAY(),"d")	项目开始到距离现在差3『天』
计划天数	DATEDIF(E2,F2,"d")	计划项目开始到结束的时间 25『天』
百分比数	IF(DATEDIF(E2,TODAY(),"d")/IF(DATEDIF(E2,F2,"d")=0,DATEDIF(E2,TODAY(),"d"),DATEDIF(E2,F2,"d"))>=1,1,DATEDIF(E2,TODAY(),"d")/IF(DATEDIF(E2,F2,"d")=0,DATEDIF(E2,TODAY(),"d"),DATEDIF(E2,F2,"d")))	公式结果是: 0.12
rept数据	IF(DATEDIF(E2,TODAY(),"d")/IF(DATEDIF(E2,F2,"d")=0,DATEDIF(E2,TODAY(),"d"),DATEDIF(E2,F2,"d"))>=1,1,DATEDIF(E2,TODAY(),"d")/IF(DATEDIF(E2,F2,"d")=0,DATEDIF(E2,TODAY(),"d"),DATEDIF(E2,F2,"d")))*50	6
rept字符	REPT("█",IF(DATEDIF(E2,TODAY(),"d")/IF(DATEDIF(E2,F2,"d")=0,DATEDIF(E2,TODAY(),"d"),DATEDIF(E2,F2,"d"))>=1,1,DATEDIF(E2,TODAY(),"d")/IF(DATEDIF(E2,F2,"d")=0,DATEDIF(E2,TODAY(),"d"),DATEDIF(E2,F2,"d")))*50)	██████
百分格式	TEXT(IF(DATEDIF(E2,TODAY(),"d")/IF(DATEDIF(E2,F2,"d")=0,DATEDIF(E2,TODAY(),"d"),DATEDIF(E2,F2,"d"))>=1,1,DATEDIF(E2,TODAY(),"d")/IF(DATEDIF(E2,F2,"d")=0,DATEDIF(E2,TODAY(),"d"),DATEDIF(E2,F2,"d"))),"0.0%")	12.0%
未开始与进行中	IF(DATEDIF(E2,TODAY(),"d")/DATEDIF(E2,F2,"d")>0,"进行中进度函数","未开始")	进行中进度函数

附件：

点击查看代码

https://files.cnblogs.com/files/q1104460935/时下进度表模板.xlsx.zip?t=1667844198