Google Sheet借助PIVOT TABLE统计跨期租赁月度入住率问题咨询

前置说明 #

你现有的数据表字段对应翻译：

• Lease number：租约编号
• Tenant name：租户姓名
• unit number：单元号
• checkin date：入住日期
• checkout date：退房日期
  计算入住率前请先确认你持有的可对外出租的总单元数量，这个数值将作为入住率计算的分母。

步骤1：拆分跨月/跨年租期（Power Query可视化操作，无需写代码） #

这个步骤的作用是把一条跨多月的租约记录，拆成多条对应不同统计月份的记录，解决跨周期统计的问题：

• 选中你整个原始数据区域，点击顶部菜单栏「数据」选项卡 → 选择「从表格/区域」，系统会自动打开Power Query编辑器
• 按住Ctrl键同时选中入住日期和退房日期两列，点击顶部「添加列」选项卡 → 选择「自定义列」，在弹出的输入框中直接粘贴以下公式：
  = List.Dates([入住日期], Duration.Days([退房日期] - [入住日期]) +1, #duration(1,0,0,0))
  点击确定后会生成一个新列，每一行是对应租约覆盖的所有入住日期的列表
• 点击新生成列右上角的扩展按钮（双向箭头图标），选择「扩展到新行」，此时表格每行就对应1个租约1天的入住记录
• 再依次添加两个统计维度列：
  • 点击「添加列」→「日期」→「年」→「年」，生成「统计年份」列
  • 点击「添加列」→「日期」→「月」→「月份名称」，生成月份列后右键该列→「重命名」为「统计月份」，如果需要jan/feb这种简写格式，可替换操作为添加自定义列，输入公式= Date.MonthName([入住日期], true)
• 完成后点击Power Query编辑器左上角「关闭并上载」，处理好的明细数据会自动导出到Excel新工作表中

步骤2：插入数据透视表生成你需要的统计格式 #

• 选中上一步导出的新表格，点击顶部菜单栏「插入」→「数据透视表」
• 右侧字段面板按以下规则设置：
  • 行：拖入「统计月份」字段，调整行顺序为1月到12月的正常顺序，显示为jan/feb/mar等
  • 列：拖入「统计年份」字段
  • 值：拖入「单元号」字段，点击值字段的下拉箭头→「值字段设置」→计算类型选择「非重复计数」，统计每个月实际入住的不同单元数量
• 最后计算入住率：选中透视表所有数值区域的单元格，输入公式= 非重复计数的单元数 / 总可出租单元数，右键选中区域→「设置单元格格式」→选择「百分比」，小数位数设置为0，最终生成的表格就和你预期的格式完全一致：

| 2019 | 2020 | 2021
jan | 0%   | 58%  | 67%
feb | 67%  | 21%  | 42%
mar | ...  | ...  | ...

简化版操作提示 #

如果不需要精确到天的入住率，只要租约覆盖该月就算1个入住单元，可把步骤1中自定义列的公式替换为：
= List.Distinct(List.Transform(List.Dates([入住日期], Duration.Days([退房日期] - [入住日期]) +1, #duration(1,0,0,0)), each Date.StartOfMonth(_)))
后续扩展后每个租约每个覆盖月份仅生成1行，数据量更小运算更快，值字段设置直接选择「计数」即可。

内容的提问来源于stack exchange，提问作者A.B.