如何在Google Sheets中按月份计算公寓年度入住率（含跨月预订）

针对跨月预订的晚数拆分统计问题，以下两种方法比直接用数据透视表更高效准确：

方法一：辅助列拆分月份天数，再用透视表汇总 #

适合熟悉Excel公式的用户，无需复杂工具：

• 转换日期格式：将原始的dd.mm.yy格式转为Excel可识别的日期。假设A列是Check in，B列是Check out，在C2、D2分别输入：

  =DATE(RIGHT(A2,2), MID(A2,4,2), LEFT(A2,2))  // 转换入住日期
  =DATE(RIGHT(B2,2), MID(B2,4,2), LEFT(B2,2))  // 转换退房日期
  

  下拉填充完成所有日期转换。

• 计算分月晚数：

  • 在E2判断是否跨月：=MONTH(C2)<>MONTH(D2)
  • 在F2计算入住月的晚数：=IF(E2, EOMONTH(C2,0)-C2, D2-C2)（跨月时取当月剩余天数，非跨月取总晚数）
  • 在G2计算退房月的晚数（仅跨月记录）：=IF(E2, D2-EOMONTH(C2,0), 0)

• 重构数据源：对跨月的记录，复制一行，将原行的晚数改为F列值、年月改为入住年月，复制行的晚数改为G列值、年月改为退房年月；非跨月记录直接保留。

• 透视统计：用重构后的数据源做透视表，行字段选“年月”（可通过=TEXT(C2,"yyyy-mm")生成），值字段选“晚数”求和。

方法二：Power Query生成每日记录，自动汇总到月 #

适合数据量较大的场景，全程自动化：

• 导入数据到Power Query：选中原始数据区域，点击「数据」→「从表格/区域」，加载到Power Query编辑器。先将Check in和Check out转换为日期类型（「转换」→「数据类型」→「日期」）。

• 生成每日入住序列：添加自定义列，输入公式：

  = List.Dates([Check in], Duration.Days([Check out]-[Check in]), #duration(1,0,0,0))
  

  这个公式会生成从入住日到退房前一天的所有日期（每一行对应一晚）。

• 展开并汇总：点击自定义列右侧的展开按钮，选择「展开到新行」；再添加年月列：

  = Date.ToText([自定义列], "yyyy-MM")
  

  最后点击「转换」→「分组依据」，分组字段选“年月”，操作选“计数行”，结果就是各月的入住晚数，加载回Excel即可。

关键提示 #

• 日期转换时注意两位年份的识别（DATE函数默认将00-29识别为2000-2029，30-99识别为1930-1999），若年份范围特殊需手动调整。
• Power Query方法无需手动拆分记录，2年的数据处理起来效率极高。

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