将Google Sheets跳过周末节假日自增公式转换为Excel可用公式

核心不兼容点说明 #

原Google Sheets公式有三处Excel原生不支持的逻辑：

• ArrayFormula隐式数组计算规则
• REGEXREPLACE正则替换提取大写字母
• COUNTIFS直接引用数组作为筛选条件的语法

适配后公式 #

1. Excel 365/2021及以上版本（自动溢出整列，无需下拉） #

在A2单元格输入以下公式即可自动向下适配所有有数据的行：

=BYROW(SEQUENCE(COUNTA(B:B)-1,1,2),LAMBDA(r,LET(
curr_J,INDEX(J:J,r),
curr_B,INDEX(B:B,r),
wd,WEEKDAY(curr_B,1),
IF(curr_J<>"",
   CONCAT(IF(AND(CODE(MID(UPPER(curr_J),SEQUENCE(LEN(curr_J)),1))>=65,CODE(MID(UPPER(curr_J),SEQUENCE(LEN(curr_J)),1))<=90),MID(UPPER(curr_J),SEQUENCE(LEN(curr_J)),1),"")),
   IF(OR(wd=1,wd=7),
      "",
      SUMPRODUCT((WEEKDAY(B2:INDEX(B:B,r),1)>1)*(WEEKDAY(B2:INDEX(B:B,r),1)<7)*(J2:INDEX(J:J,r)=""))
))))

如果仅需要提取J列备注的首字母大写（符合你示例中Holiday返回H的需求），可以把大写提取部分简化为LEFT(UPPER(curr_J),1)，公式执行效率更高。

2. Excel 2016/2019及更低版本（需下拉填充） #

在A2单元格输入以下公式，按Ctrl+Shift+Enter以数组公式模式确认，之后下拉到所有需要计算的行即可：

=IF(J2<>"",
   CONCAT(IF(AND(CODE(MID(UPPER(J2),ROW($1:$99),1))>=65,CODE(MID(UPPER(J2),ROW($1:$99),1))<=90),MID(UPPER(J2),ROW($1:$99),1),"")),
   IF(OR(WEEKDAY(B2,1)=1,WEEKDAY(B2,1)=7),
      "",
      SUMPRODUCT((WEEKDAY(B$2:B2,1)>1)*(WEEKDAY(B$2:B2,1)<7)*(J$2:J2=""))
))

逻辑对齐说明 #

• 大写字母提取：先将J列内容转全大写，遍历每个字符判断ASCII码是否在A-Z（65-90）范围内，拼接符合要求的字符，完全匹配原REGEXREPLACE("[^A-Z]","")的效果
• 周末判断：保持原WEEKDAY规则（1代表周日，7代表周六），周末返回空值不计数
• 工作日计数：用SUMPRODUCT统计当前行往上所有满足「工作日、J列为空」的行数量，实现自增计数，完全匹配原COUNTIFS的统计逻辑

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