夜雨聆风表里有1000行车型/产品数据，你却一眼看不出谁最保值？更崩溃的是：老板问“Top10是谁、哪个品牌更稳、今年跌得最狠的是谁”，你只能手动翻、手动算、手动对比。
这篇文章就手把手教你用Excel搭一张可排名、可筛选、可对比的保值率分析表：从底层字段设计到函数公式、条件格式，再到数据透视表一键出结论。
适用场景不止二手车：数码产品保值、资产折旧、门店商品周转、设备残值评估都能直接套用。核心方法提前预告：基础数据整理 + 函数公式 + 条件格式 + 数据透视表。
先把保值率分析表的底层逻辑搭好：字段设计与基础公式
先统一口径：最常用、最直观的计算方式是——
保值率 = 当前价格 ÷ 原始价格
举个例子：某车型新车价 20 万，当前二手价 14 万，保值率 = 14/20 = 70%。同理，手机、电脑、设备残值都一样算。
建议字段（够用且好扩展）：
• 品牌/类别（如：Toyota、Apple / SUV、手机）
• 型号（如：RAV4、iPhone 14）
• 上市时间（或购入时间/年份）
• 原价（新车价/首发价）
• 当前价（二手价/现价）
• 保值率
• 排名
• 备注（车况、配置、渠道等影响因素）
关键一步：数据清洗（决定你后面算得准不准）
• 统一单位：价格全用“元”或“万元”，别混着来（20万 vs 200000）。
• 去空值：原价或当前价为空的行，先补齐或剔除。
• 处理异常值：比如原价=0、当前价远高于原价（特殊改装/录入错），建议标记后单独核对。
• 文本数字转数值：出现“左对齐、算不动”的，选中列 → 数据 → 分列 → 直接完成，或用 VALUE() 转换。
保值率公式怎么写？
假设 E 列是“原价”，F 列是“当前价”，G 列算“保值率”，在 G2 输入：
=F2/E2
然后把 G 列设置为百分比（开始 → 数字 → 百分比）。
实用细节（强烈建议做）：
• 视图 → 冻结首行（数据多时不迷路）。
• Ctrl + T 把数据区域转换为超级表（Excel Table）：自动扩展、自动带筛选、公式自动填充。
• 给表命名（表设计 → 表名称），例如：tbl_resale，后面写函数更稳。
排名怎么做更清晰：函数公式与条件格式一起上
有了保值率，下一步就是排名。通常我们要“保值率越高，排名越靠前”，也就是降序。
1）基础排名公式（推荐）
假设保值率在 G 列，H 列做排名，在 H2 输入：
=RANK.EQ(G2,$G$2:$G$1001,0)
• 最后一个参数 0 代表降序（保值率高 = 排名小）。
• $G$2:$G$1001 记得用绝对引用，避免拖动错位。
并列排名怎么处理？
RANK.EQ 遇到相同保值率会给同样名次。若你需要“并列时按当前价更高者靠前”，做法是加一个辅助排序字段：例如先按保值率降序，再按当前价降序排序即可（数据 → 排序 → 添加条件）。
2）条件格式：让高低一眼看出来
只靠数字不直观，条件格式能把“结论”直接画在表里：
• 色阶：开始 → 条件格式 → 色阶（绿=高，红=低）
• 数据条：用条形长度表示保值率大小
• 前10项：突出 Top10 / Bottom10（条件格式 → 前/后规则）
3）进阶：按品牌/品类内排名（更贴近业务）
很多时候你想看的是“同品牌内谁最保值”，比如 Toyota 内部 Top3，而不是全表大乱斗。
做法：新增一列“品牌内排名”。假设 B 列是品牌，G 列是保值率，在 I2 输入（365 可用）：
=1+COUNTIFS($B$2:$B$1001,B2,$G$2:$G$1001,">"&G2)
逻辑：同品牌里，保值率比我高的有几个，我的名次就是“它们数量 + 1”。
常见错误提醒（踩一次就记住）：
• 看着是百分比，其实值是 70 而不是 0.7（多半是录入时把“70%”当成“70”）。
• 排序后排名“不更新”：检查排名公式的引用区域是否固定正确。
• 公式拖动错位：关键范围一定要加 $。
筛选和对比分析怎么做：快速找出最值得关注的数据
排名只是“谁更强”，筛选和对比才是“我该关注谁”。
1）基础筛选：3秒锁定目标
• 按品牌筛：只看某品牌/某类别
• 按价格区间：比如当前价在 10-15 万
• 按年份：只看 2023-2025 上市产品
• 按保值率区间：例如 ≥ 75% 的“保值优等生”
2）高级筛选思路：直接筛出“业务问题”的答案
• 保值率大于阈值：例如 ≥80%（适合“推荐入手”清单）
• 近一年跌幅最小：新增“去年价”字段，算跌幅 = (当前价-去年价)/去年价
• 某类别 Top N：配合“类别内排名”列，筛选排名 ≤10
3）对比分析：3种最常用的对比口径
• 同品牌对比：看品牌内部哪些型号更稳
• 同价位对比：同样 15 万预算，谁更保值
• 不同年份横向对比：同型号不同年份，折旧速度差多少
常用函数（让对比“可量化”）：
• AVERAGEIFS：算某品牌平均保值率（用于“品牌整体是否保值”）
• MAX/MIN：找某类别最高/最低保值率
• XLOOKUP/VLOOKUP：按型号拉取对应原价/当前价/年份，快速做对比表
• IF：做标签，比如保值率≥75% 标记“保值”，否则“一般”
你最终要输出的结论形式（最对老板胃口）：
• 谁排名靠前：Top10 型号清单
• 谁掉价最快：Bottom10 或跌幅最大清单
• 哪些更值得入手：高保值率 + 目标价位 + 目标年份的交集
用数据透视表做保值率分析：一张表看排名、结构和趋势
当你的数据从几十行变成几千行，并且需要频繁切换口径（按品牌/类别/年份/价位）时，就该上数据透视表了。
什么时候用透视表最爽？
• 数据量大，需要多维汇总
• 同一份数据要给不同部门看（品牌、门店、财务口径都不一样）
• 要快速做结构对比、Top10、趋势汇报
透视表搭建步骤（按这个做就行）：
1. 选中超级表任意单元格 → 插入 → 数据透视表
2. 行字段：放品牌、型号
3. 值字段：放保值率，汇总方式改为平均值（也可加最大值/最小值）
4. 需要按年份看：把“上市时间/年份”放到筛选或列字段
切片器：一键筛选（效率提升非常明显）
透视表分析 → 插入切片器：选择“品牌/年份/类别”。之后你点一下切片器，就能立刻切换分析对象，特别适合做周报月报。
透视表里的排名/Top10怎么做？
• 对“平均保值率”列降序排序
• 值筛选 → 前10项（按平均保值率取 Top10）
建议配透视图做汇报：
• 柱形图：看 Top10 排名最直观
• 折线图：看不同年份/月份的保值率变化趋势
写在最后：把这套方法变成你的通用分析模板
整套流程其实就四步：先整理数据 → 算保值率 → 做排名筛选 → 用数据透视表做多维对比。一旦你会做Excel 保值率分析，同样的方法可以迁移到利润率、折旧率、回报率等任何“比率类”的数据分析表。
行动建议：先照着本文搭一个“基础模板”（字段+保值率+排名），跑通后再加条件格式和透视分析，你会明显感觉办公效率提升。
CTA：如果你想直接套用模板，留言回复“保值率模板”（或私信我），我把可编辑的 Excel 模板发你；觉得有用也欢迎收藏、转发给同事朋友，后续我再出一篇“自动生成Top10看板（透视图+切片器）”的进阶版。
