Excel甘特图进度着色公式异常：百分比对应工作日计算错误

需求 #

• 给甘特图添加「显示进度」开关，将粉色任务条替换为深蓝色
• 涉及字段：start_date、end_date、workdays、percentage_complete

现有配置 #

单元格引用 #

• start_date: =Gantt!$I18
• end_date: =Gantt!$J18
• workdays: =Gantt!$H14
• percentage_complete: =Gantt!$K18

失效的命名公式（item_in_complete） #

=AND(Gantt!date>=Gantt!start_date, 
     Gantt!date<=WORKDAY(Gantt!start_date,    
     Gantt!percentage_complete*Gantt!workdays-1))

条件格式规则 #

=AND(show_progress="Yes", item_in_complete)

问题诊断 #

基础时间轴着色逻辑正常，但进度计算逻辑错误：当前公式误将百分比的数值（如100%对应数值100、50%对应数值50）直接与工作日数相乘，导致计算出的进度天数不符合实际需求。实际需要按「完成百分比 × 总工作日数」得出应着色的工作日数量，且仅覆盖周一至周五的工作日。

修复方案 #

替换item_in_complete命名公式为以下内容：

=AND(
    Gantt!date>=Gantt!start_date,
    Gantt!date<=WORKDAY(Gantt!start_date, (Gantt!percentage_complete/100)*Gantt!workdays - 1),
    WEEKDAY(Gantt!date, 2)<=5
)

修正说明 #

1. 百分比转小数：添加/100将整数形式的百分比（如100代表100%）转换为小数，确保与工作日数相乘后得到正确的进度天数
2. 工作日过滤：新增WEEKDAY(Gantt!date,2)<=5校验，确保仅对周一至周五的日期着色，排除周末
3. 保留基础逻辑：保留原有的日期范围判断与WORKDAY()函数，确保进度日期仅计算工作日

验证用例 #

• 5个工作日、100%完成的任务：应从start_date开始连续5个工作日全部着色
• 6个工作日、50%完成的任务：应从start_date开始连续3个工作日着色
• 切换show_progress为"Yes"/"No"，确认进度着色开关功能正常

内容的提问来源于stack exchange，提问作者sbreslin14