基于Excel起止时间戳生成活动日历列表的技术需求

我懂你这种头疼——用IF、VLOOKUP这类常规函数搞不定跨天活动的拆分统计太正常了，毕竟要把一个跨多天的活动拆到每一天，还要按地点分栏显示活动和时长，确实不是简单查找能解决的。下面给你两种靠谱方案，优先推荐Power Query，操作直观不容易出错；要是非得用函数，也给你数组公式的写法。

方案一：用Power Query（强烈推荐） #

Power Query就是专门处理这类数据拆分、重塑需求的，几步就能搞定：

1. 把数据导入Power Query
   选中你的活动数据区域（要包含表头哦），点击「数据」选项卡 → 「从表格/区域」，确认弹窗里的「我的表格有标题」是勾选状态，然后就能进入Power Query编辑器了。

2. 把跨天活动拆成单独的日期行
   点击「添加列」选项卡 → 「自定义列」，在弹出的对话框里输入这个公式：

   List.Dates([开始时间], Duration.Days([结束时间]-[开始时间])+1, #duration(1,0,0,0))
   

   这个公式会生成从活动开始到结束的所有日期的列表。输完点确定，然后点击这个新列右上角的「展开到新行」按钮，就能把每个日期拆成单独的一行啦。

3. 计算每个活动当天的实际时长
   再添加一个自定义列，用来算当天的时长，公式如下：

   let
       currentDate = [自定义],
       startOfDay = DateTime.Date(currentDate) & #time(0,0,0),
       endOfDay = DateTime.Date(currentDate) & #time(23,59,59),
       actualStart = List.Max({[开始时间], startOfDay}),
       actualEnd = List.Min({[结束时间], endOfDay}),
       hours = Duration.TotalHours(actualEnd - actualStart)
   in
       hours
   

   这个公式会自动判断当天是活动的第一天、中间天还是最后一天，算出对应的时长（比如最后一天到中午12点，就会算出12小时）。

4. 按日期+地点重塑成你要的表格格式
   选中「自定义」（拆分后的日期）、「地点」、「活动名称」、「自定义.1」（刚算的时长）这几列，点击「转换」选项卡 → 「透视列」。

   • 第一次透视：「透视列」选「地点」，「值列」选「活动名称」，「聚合函数」选「不要聚合」；
   • 第二次透视：再选一次「透视列」，「值列」选「自定义.1」，「聚合函数」还是「不要聚合」；
     之后把列名改成你想要的，比如「Loc1_活动」、「Loc1_时长」、「Loc2_活动」、「Loc2_时长」就行。

5. 补全整个7月的日期（包括无活动的日子）
   如果你需要7月1日到31日的所有日期，哪怕当天没活动，也可以在Power Query里生成一个完整的日期序列：

   List.Dates(#date(2019,7,1), 31, #duration(1,0,0,0))
   

   把这个序列转换成表格，然后和之前处理好的活动表格做「左连接」，就能把空日期补全，没活动的单元格留空就好。

6. 加载回Excel
   点击「关闭并上载」，就能得到你想要的日历列表了，和你给出的示例完全匹配。

方案二：用数组公式（适合坚持用函数的场景） #

如果不想用Power Query，也可以用数组公式实现，但要注意Excel版本：365/2021支持动态数组，直接回车就行；旧版本需要按「Ctrl+Shift+Enter」触发数组公式。

假设你的原始数据在A2:D4（A列=活动名称，B列=地点，C列=开始时间，D列=结束时间），F列是7月的完整日期（F2=7/1/2019，F3=F2+1，一直拉到F31）：

1. Loc1_活动列（G列）
   在G2输入公式：

   =TEXTJOIN(", ", TRUE, IF((B$2:B$4="Loc1")*(C$2:C$4<=F2+1)*(D$2:D$4>=F2), A$2:A$4, ""))
   

   这个公式会找出当天在Loc1的所有活动，用逗号分隔显示。

2. Loc1_时长列（H列）
   在H2输入公式：

   =SUM(IF((B$2:B$4="Loc1")*(C$2:C$4<=F2+1)*(D$2:D$4>=F2), MAX(0, MIN(D$2:D$4, F2+1)-MAX(C$2:C$4, F2))*24, 0))
   

   这个公式会计算当天Loc1所有活动的总时长（单位：小时）。

3. Loc2_活动列（I列）和时长列（J列）
   把上面的公式里的"Loc1"改成"Loc2"就行：

   • I列公式：

     =TEXTJOIN(", ", TRUE, IF((B$2:B$4="Loc2")*(C$2:C$4<=F2+1)*(D$2:D$4>=F2), A$2:A$4, ""))
     

   • J列公式：

     =SUM(IF((B$2:B$4="Loc2")*(C$2:C$4<=F2+1)*(D$2:D$4>=F2), MAX(0, MIN(D$2:D$4, F2+1)-MAX(C$2:C$4, F2))*24, 0))
     

   把这些公式下拉到F31，就能得到完整的日历列表了。

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