如何用Excel计算含首尾机票的N天连续行程最低总成本？

需求说明 #

基于1月1日至31日的每日酒店、机票价格数据集，以及行程天数参数（如4天），计算包含连续N天酒店费用+行程首日、尾日机票费用的最低总成本。

原有连续N天酒店最低费用公式 #

用于单独计算连续N天酒店住宿的最低费用：

=LET(range,B2:AF2,
length,B5,
Running_Total,SCAN(0,range,LAMBDA(a,b,a+b)),
Sequence_1,SEQUENCE(1,COLUMNS(range)-length+1,length),
Sequence_2,SEQUENCE(1,COLUMNS(range)-length+1,0),
difference,INDEX(Running_Total,Sequence_1)-IF(Sequence_2,INDEX(Running_Total,Sequence_2),0),
MIN(difference))

扩展后的最低总成本计算公式（感谢@JvdV） #

整合酒店费用与首尾日机票费用，计算整体最低总成本：

=MIN(BYCOL(FILTER(B1:AF1,B1:AF1<=(AF1-B5+1)),LAMBDA(Col_Num,SUMIFS(B2:AF2,B1:AF1,">="&Col_Num,B1:AF1,"<="&Col_Num+(B5-1))+SUMIF(B1:AF1,Col_Num,B3:AF3)+SUMIF(B1:AF1,Col_Num+(B5-1),B3:AF3))))

对应行程首日日期查找公式 #

用于定位产生最低总成本的行程首日日期（需配合最低总成本单元格引用，示例中为B7）：

=XLOOKUP(B7,BYCOL(FILTER(B1:AF1,B1:AF1<=(AF1-B5+1)),LAMBDA(Col_Num,SUMIFS(B2:AF2,B1:AF1,">="&Col_Num,B1:AF1,"<="&Col_Num+(B5-1))+SUMIF(B1:AF1,Col_Num,B3:AF3)+SUMIF(B1:AF1,Col_Num+(B5-1),B3:AF3))),FILTER(B1:AF1,B1:AF1<=(AF1-B5+1)))

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