表格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
posted @ 2022-11-08 01:46  徐锅  阅读(373)  评论(0)  编辑  收藏  举报