乐于分享
好东西不私藏
夜雨聆风 > > 办公文件 > 【Day2 进阶实操】Excel 数据透视表 5 大核心用法,高效搞定收益分析

【Day2 进阶实操】Excel 数据透视表 5 大核心用法,高效搞定收益分析

当前时间： 2026-08-17 17:41:32 分类：办公文件 评论(0)
【Day2 进阶实操】Excel 数据透视表 5 大核心用法,高效搞定收益分析
哈喽大家好，我是小梅子。
昨天我们搞定了数据源和数据核对，把收益分析的地基打牢了。今天直接上硬菜，教你怎么把那些躺在 PMS 里、散落在各个表格里的杂乱数据，变成能直接指导定价、调整库存的有用结论。
讲真的，我见过太多酒店人在数据分析上走弯路了。每天花两三个小时对着 Excel 表格，一行行复制粘贴，一个个手动求和，眼睛都看花了，结果做出来的报表还不准。更离谱的是，很多老板觉得做收益分析必须买几万块一年的专业系统，结果系统买回来了，没人会用，最后还是回到手动做表的老路上。
说白了，根本不用花那个冤枉钱。Excel 自带的数据透视表，就能搞定酒店 80% 的日常数据分析工作。不用写复杂公式，不用学编程，只要会拖鼠标，10 分钟就能做完别人半天的活。
今天我就把这些技巧全部教给你，保证你看完就能用，用了就见效。

PART 01

【新手汇总】透视表基础操作（零门槛）
先花 2 分钟，把最基础的操作学会。只要会这两步，后面所有的高级用法你都能举一反三。版本说明：微软 Office 2016 及以上版本、WPS 2019 及以上版本操作路径完全一致，仅菜单样式略有不同，下面的步骤两边通用。
1. 一键生成透视表
很多新手觉得透视表很复杂，其实生成一个透视表只需要点三下鼠标。但在生成之前，一定要先把原始数据整理好，这是最关键的一步，90% 的新手问题都出在这里。
前置要求（一个都不能少）：
  • 原始数据不能有合并单元格：很多人喜欢把相同日期或者相同客源的单元格合并，这会导致透视表无法正确识别数据
  • 不能有空行空列：哪怕只有一个单元格是空的，透视表也会自动截断数据
  • 第一行必须是清晰的表头：比如 “日期”“客源类型”“间夜数”“房价”，不能是 “第一列”“第二列” 这种模糊的名称
  • 所有数字不能带单位：“100 元” 要改成 “100”，“5 间” 要改成 “5”，否则透视表会把它们当成文本，无法求和
  • 日期必须是标准日期格式：要用 “2026/6/1” 或者 “2026-06-01”，不能是 “6 月 1 日”“6.1” 这种文本格式
操作步骤：
  1. 打开你昨天用《每日数据核对清单》整理好的订单原始数据
  2. 选中数据区域内任意一个单元格（不用全选，Excel 会自动识别整个数据区域）
  3. 点顶部菜单栏【插入】→【数据透视表】
  4. 弹出的窗口直接点【确定】，一秒钟就能生成一个空白透视表
新手小技巧：按 Ctrl+A 可以快速选中整个数据区域，比用鼠标拖动方便多了。
2. 基础字段拖拽
透视表的核心逻辑非常简单，就是 “分类汇总”。左边是你所有的字段，右边有四个区域，只要把字段拖到对应的区域，就能得到你想要的统计结果。
  • 【行】：把你想分类的字段拖到这里，比如按 “客源类型” 分类、按 “日期” 分类
  • 【值】：把你想统计的字段拖到这里，比如统计 “间夜数” 的总和、统计 “房价” 的平均值
  • 【列】：把你想横向对比的字段拖到这里，比如按 “星期几” 横向对比不同客源的入住情况
  • 【筛选器】：把你想筛选的字段拖到这里，比如只看 “OTA 散客” 的数据、只看 “6 月份” 的数据
新手必避坑：值字段默认的计算方式是 “计数”，而我们做收益分析 99% 的情况都需要 “求和”。所以每次拖完值字段，一定要检查一下计算方式对不对。如果不对，右键点击值字段→【值字段设置】→选 “求和” 即可。
举个最简单的例子：把 “客源类型” 拖到行，把 “间夜数” 拖到值，一秒钟就能算出每个客源类型的总间夜量。再把 “房价” 拖到值，就能同时算出每个客源的总收入。再把 “日期” 拖到筛选器，就能单独查看某一天或者某一周的客源结构。
就这么简单。没有任何技术含量，多拖两次，你就会发现透视表真的是个神器。

PART 02

【进阶落地】收益管理专属 5大透视表用法
搞定了基础操作，接下来就是今天的重头戏。这 5 个用法不是我凭空想出来的，是我在做收益的日常工作中总结出来的，专门为酒店收益量身定做的。学会了这 5 个，你日常所有的数据分析工作都能搞定，再也不用求别人帮你做表格了。
通用技巧（所有用法都适用）：所有透视表都可以把 “订单状态” 拖到筛选器，一键排除取消单、测试单、Noshow 单，不用手动删除原始数据。这样既能保证分析结果的准确性，又能保留完整的原始数据，方便以后回溯。
用法 1：渠道占比分析，精准定位高 / 低毛利渠道
这是最常用、也是最重要的一个用法，没有之一。每天早上花 1 分钟做这个分析，就能一眼看出哪个渠道在给你赚钱，哪个渠道在拖你的后腿，然后立刻采取行动。
所需字段：客源类型、间夜数、营收操作步骤：
  • 行：客源类型
  • 值：间夜数、营收
  • 右键点击任意一个营收数值→【值显示方式】→【列汇总的百分比】
这样你就能同时看到每个客源类型的间夜数、总营收和营收占比，一目了然。
怎么看结果（通用健康标准）：
  • 协议散客占比 25%-35%：健康，这是你最稳定的利润来源
  • 会员散客占比 20%-30%：健康，复购率最高，获客成本最低
  • OTA 散客占比 20%-30%：健康，作为补充流量，调节出租率
  • OTA 散客占比超过 35%：危险信号！说明你太依赖 OTA 了，平台会不断提高佣金，强制你参加低价活动，你的议价能力会越来越弱，利润会被一点点蚕食
  • 低价团队占比超过 10%：立刻停接！这些客人不仅房价低，还会占用大量房间，导致你无法接待高毛利的散客，满房也不赚钱
分析结论直接用：
  • 如果 OTA 占比太高，当天就把 OTA 价格上调 10-20 元，同时安排销售回访 3 个核心协议客户
  • 如果低价团队占比太高，立刻关闭团队房预留，把房间留给高毛利的散客
  • 如果会员占比太低，当天就在前台推出 “扫码注册会员立减 20 元” 的活动
用法 2：预订趋势拆解，查看每日时段预订节奏
很多人做收益，只会每天早上看一眼总预订量，然后拍脑袋调价。结果经常出现 “早上涨价，下午卖不动，晚上又降价” 的情况，白白损失了很多营收。其实，掌握了客人的预订节奏，你就能在正确的时间调价，收益至少能提升 10%。
所需字段：预订日期、预订小时、预订间夜数操作步骤：
  • 行：预订日期、预订小时
  • 值：预订间夜数
  • 右键点击日期→【组合】→勾选 “小时”，确定
这样你就能看到每天每个小时的预订量，清晰地找出你的预订高峰。
新手救急：如果 “组合” 按钮是灰色的，说明你的日期字段不是标准日期格式。选中原始数据的 “预订日期” 列→右键→设置单元格格式→选 “日期” 即可。
怎么看结果：
  • 如果你发现每天下午 2-4 点是预订高峰，那就在 1 点半的时候把价格上调 10 元，这时候客人的预订意愿最强，对价格的敏感度最低
  • 如果你发现每天晚上 8-10 点还有一波小高峰，那就在 7 点半的时候检查房态：如果空房超过 30%，就降价 10-15 元冲量；如果空房不到 20%，就涨价 10 元惜售
  • 如果你发现上午几乎没有预订，那上午就不用盯着价格，该干嘛干嘛，把精力放在下午和晚上的高峰时段
不同类型酒店的预订节奏差异：
  • 商务酒店：预订高峰一般在工作日的上午 9-11 点和下午 2-4 点，客人一般当天预订当天入住
  • 景区酒店：预订高峰一般在周末的下午 3-5 点和晚上 7-9 点，客人一般提前 1-3 天预订
  • 会展酒店：预订高峰一般在展会前 3-7 天，越临近展会，预订量越大
用法 3：客群特征分析，筛选高价值客群
帕累托法则大家都知道：80% 的利润来自 20% 的客人。但很多酒店人根本不知道自己的 20% 高价值客人是谁，更别说重点维护了。用透视表，你只需要 30 秒就能找出这些高价值客人，然后把 80% 的精力放在他们身上，收益会事半功倍。
所需字段：客人姓名、公司名称、入住间夜数、总消费金额操作步骤：
  • 行：客人姓名、公司名称
  • 值：入住间夜数、总消费金额
  • 点击值字段旁边的下拉箭头→【值筛选】→【大于或等于】
怎么筛选高价值客群：
  • 个人客人：筛选 “年入住间夜数≥10”，这些是你的忠实个人会员，复购率非常高
  • 企业客户：筛选 “月入住间夜数≥5”，这些是你的核心协议客户，是你稳定的收入来源
  • 长住客：筛选 “单次入住天数≥3”，这些客人不仅稳定，而且对价格不敏感，还能带动餐饮、洗衣等其他消费
怎么维护高价值客群：
  • 把筛选出来的高价值客人拉一个专属名单，每个月回访一次，了解他们的需求和建议
  • 生日的时候送一张免费房券或者一份小礼物，成本不高，但能让客人感受到被重视
  • 入住的时候给个免费升级，或者送一份欢迎水果，提升客人的入住体验
  • 建立专属的预订通道，给他们预留最好的房间，不用和其他客人抢房
用法 4：均价对比分析，排查渠道低价倾销问题
很多时候你的平均房价上不去，不是因为市场不好，也不是因为你的服务差，是因为某个渠道在偷偷卖低价。你可能自己都不知道，同一个房型，携程卖 200 元，美团卖 180 元，飞猪卖 170 元。这样不仅会拉低你的整体均价，还会导致客人投诉，影响你的品牌形象。用透视表一对比，问题立刻暴露。
所需字段：客源类型、房型、间夜数、房价操作步骤：
  • 行：客源类型、房型
  • 值：间夜数、房价
  • 右键点击房价→【值字段设置】→【计算类型】选 “平均值”
这样你就能看到每个渠道、每个房型的平均房价，哪个渠道卖低了，一目了然。
怎么看结果：
  • 对比各个 OTA 平台的平均房价，正常情况下，不同平台的均价差不应该超过 10 元
  • 对比协议客户的实际均价和协议价，看有没有低于协议价的订单
  • 对比不同房型的均价，看有没有房型定价不合理的情况
分析结论直接用：
  • 如果发现某个 OTA 平台的均价比其他平台低 20 元以上，立刻联系平台经理，要求统一价格。如果平台不同意，就暂时关闭这个渠道的低价房型
  • 如果发现某个协议客户的订单均价低于协议价，立刻和对方对接人沟通，要求按协议价执行。如果对方不同意，就终止合作
  • 如果发现某个房型的均价明显低于其他房型，就调整这个房型的定价，或者减少这个房型的库存
用法 5：客源毛利统计，匹配 Day5 客源管控体系
最后这个用法，直接对接我们 Day5 要讲的客源管控体系，是从 “看数据” 到 “做决策” 的关键一步。很多人只看营收和出租率，不看毛利。结果看起来满房了，实际上是在亏钱。用透视表算出每个客源的真实毛利，你才能知道哪些客人该留，哪些客人该淘汰。
所需字段：客源类型、间夜数、总营收、单房变动成本操作步骤：
  1. 行：客源类型
  2. 值：间夜数、总营收
  3. 点击【值字段设置】→【插入计算字段】
  4. 输入公式：毛利 = 总营收 - 间夜数 * 单房变动成本，确定
统一统计口径（全行业通用）：单房变动成本 = 水电 5 元 + 布草洗涤 12 元 + 一次性用品 3 元 + OTA 佣金（按实际比例）注意：变动成本不包含房租、固定人工、折旧这些固定成本，因为不管你有没有卖出这个房间，这些成本都是要花的。我们做收益决策的时候，只需要考虑边际成本。
怎么看结果：
  • 毛利最高的客源：重点维护，给更多的资源倾斜，优先满足他们的需求
  • 毛利为正但很低的客源：适当控制占比，不要让它们成为主力，只能作为补充流量
  • 毛利为负的客源：连续 3 个月都是负的，直接淘汰，不要可惜
  • 综合毛利分析：有些客人虽然房费毛利低，但能带来大量的餐饮、会议、娱乐等其他收入，综合毛利其实很高。这时候你可以在透视表里把其他收入也加进去，计算综合毛利。比如一个会议团队，房费可能只赚了 5000 元，但餐饮赚了 2 万元，综合毛利非常高，这样的团队就是优质团队。
你懂的，不是所有的客人都是好客人。有些客人看起来给你带来了出租率，实际上是在亏钱。用数据说话，该淘汰的果断淘汰，你的利润才会提升。

PART 03

【高阶体系】自动化数据分析台账搭建
学会了上面 5 个用法，你已经能搞定日常的数据分析了。但我们还可以更进一步，把它做成自动化模板。每天只要粘贴新的数据，所有分析结果自动生成，真正实现一劳永逸。
1. 固定模板设置
  1. 按照上面的 5 个用法，做好 5 个透视表，分别放在 5 个不同的工作表里，命名为 “渠道占比”“预订趋势”“客群分析”“均价对比”“毛利统计”
  2. 新建一个工作表，命名为 “原始数据”，专门用来存放每日的订单原始数据
    设置动态数据源（一劳永逸）：
  3. 点击顶部菜单栏【公式】→【定义名称】
  4. 名称输入 “全部数据”
  5. 引用位置输入：
    =OFFSET(原始数据!$A$1,0,0,COUNTA(原始数据!$A:$A),COUNTA(原始数据!$1:$1))
  6. 点击确定
  7. 把所有透视表的数据源都改成 “全部数据”
为什么要做动态数据源：普通的透视表数据源是固定的范围，你粘贴新数据之后，需要手动更改数据源范围，非常麻烦。用这个动态数据源公式，Excel 会自动识别原始数据的行数和列数，以后粘贴新数据不用再手动更改，直接刷新就行，彻底解决 “粘贴后刷新还是旧数据” 的问题。
2. 每日更新方法
第二天要更新数据的时候，只要三步，整个过程不超过 1 分钟：
  1. 打开模板文件
  2. 把前一天的订单数据复制粘贴到 “原始数据” 工作表的最后一行
  3. 右键点击任意一个透视表→【刷新】
所有分析结果自动更新，你直接看结论就行
数据量优化技巧：如果你的酒店订单量很大，超过 1 万行之后透视表会卡顿。这时候你可以把历史数据按月份拆分，只保留最近 3 个月的数据在模板里，老数据单独存档。这样既能保证模板运行流畅，又不影响日常分析。
3. 多维度交叉对比
基础的单维度分析只能看到表面问题，多维度交叉分析才能发现深层次的规律。你可以把多个字段组合起来，做更深入的交叉分析：
  • 行：客源类型，列：星期几，值：间夜数 → 看不同客源在不同星期的入住规律决策逻辑：如果发现周末 OTA 散客占比达 60%，工作日协议客占比达 50%，就周末 OTA 价格上调 15-20 元，工作日协议客户预留房增加 20%
  • 行：房型，列：客源类型，值：平均房价 → 看不同房型在不同渠道的售价差异决策逻辑：如果发现双床房在携程的销量最好，大床房在美团的销量最好，就把携程的双床房价格上调 10 元，把美团的大床房价格上调 10 元
  • 行：月份，列：客源类型，值：毛利 → 看不同客源的月度毛利变化决策逻辑：如果发现每年 3 月和 11 月长住客的毛利最高，就在这两个月推出长住优惠活动，吸引更多长住客
交叉分析能帮你发现很多单一分析看不到的问题，让你的决策更精准，收益提升更明显。
4. 区域多店模板搭建方案（高阶专属）
如果你是区域收益总监，管理着多家门店，你可以把这个单店模板升级为区域汇总版，实现所有门店数据的统一管理和对比分析。
  1. 统一标准：首先要统一全区域所有门店的原始数据字段名和统计口径，比如都叫 “客源类型”，不能有的叫 “客源分类”，有的叫 “客户类型”
  2. 批量导入：用 Excel 自带的 Power Query 工具，批量导入所有门店的每日数据，自动合并成一个总表
  3. 交互式分析：插入切片器和日程表，实现单店、区域、时间段的一键切换分析，想看哪个店的数据就点哪个店
  4. 权限管控：点击【审阅】→【保护工作表】，只允许编辑 “原始数据” 区域，防止员工误改公式和透视表。还可以给不同的人设置不同的权限，比如店长只能看自己门店的数据，区域总监能看所有门店的数据
这样你不用再让每个门店每天发报表，不用再手动汇总，所有数据自动更新，一键生成区域汇总报表，工作效率至少提升 10 倍。

PART 04

新手救急3问（必看）
我整理了新手用透视表最容易遇到的 3 个问题，以及 10 秒解决方法，遇到问题直接对照着看就行。
刷新后数据不对怎么办？
90% 的情况是原始数据有问题。检查原始数据有没有空行空列，有没有合并单元格，所有数字是不是纯数字格式，日期是不是标准日期格式。
字段丢失怎么办？
右键点击透视表→【更改数据源】→重新选中原始数据区域即可。
刷新后格式全乱了怎么办？
右键点击透视表→【数据透视表选项】→取消勾选 “更新时自动调整列宽”，这样刷新之后格式就不会变了。

PART 05

今日小结
今天的内容全是实操，没有一句空话。我知道很多人看到 Excel 就头疼，觉得自己学不会。但我可以保证，只要你跟着步骤做一遍，保证你能学会。
最后再跟大家强调一遍：工具只是载体，用数据找到问题才是目的。不要为了做表格而做表格。做透视表不是为了好看，不是为了给老板交差，是为了发现问题，解决问题，最终提升收益。
明天我们讲收益管理的核心仪表盘：Pace+Pickup 双指标。教你提前 7 天预判市场变化，不再被动应对，不再追着市场跑。
从日常琐碎的对账工作，到体系化的收益深耕，每一位酒店人都在为营收努力。希望这篇干货能帮你选对工具、少走弯路。

如果内容对你有帮助，别忘了点亮【赞 + 关注 + 转发】，分享给身边做酒店的朋友，一起避坑增收。关注我，后续带你从 0 到 1 搞定酒店收益运营，踏踏实实做增收、稳稳当当避坑。

愿每一位酒店人，经营顺遂、营收长虹，心怀热爱，终能追上属于自己的那束光✨

感谢❤

Chase your sunshine.

#酒店收益管理 #酒店数据分析 #Excel 教程 #酒店运营 #新手做收益

相关学习资料