Excel 怎么用数据透视表算出占比
汇总出总额之后,更想看每一行占多少。讲清值显示方式在该改哪里、行占比和列占比有什么区别,以及筛选之后百分比为什么会变。
入口在哪
占比不是靠新加一列公式算的,而是改透视表里那个值字段的「显示方式」。入口有两个:在透视表的任意数值单元格上右键,选「值字段设置」;或者在右侧字段列表里点「值」区域中的那个字段,再选「值字段设置」。弹出的窗口下方有「值显示方式」标签,占比就在这一页里。
桌面版 Excel 和 WPS 表格的菜单叫法略有差别,WPS 里可能写作「显示方式为」;Mac 版 Excel 同样是右键进值字段设置。找不到时把整个右键菜单从头看一遍,那个入口一直都在,只是位置随版本变动。
具体步骤
- 先检查三个区域放对了没有:行放分类,列放第二个维度(不需要就空着),值放要汇总的数字。
- 确认值是「求和」而不是「计数」。「计数」算出来的是条数占比,不是金额占比。
- 右键任意一个数值 > 值字段设置 > 值显示方式。
- 要「每个分类占全部的多少」,选「总计的百分比」。
- 要「每一行里各列各占多少」,选「行汇总的百分比」;反过来看列内各行,选「列汇总的百分比」。
- 同一页的「数字格式」里设成百分比并保留两位小数,否则显示成 0.35 这种小数看不出是占比。
- 想同时保留原值和占比,把同一个字段再拖一次进「值」区域,做成两个值字段,一个照常求和、一个按百分比显示。
几个坑
- 分母是哪个要分清。总计的百分比永远拿整张表的总计当分母,一旦筛选或点切片器,分母跟着变,同一个值两次看到的结果会不同。
- 只有行没有列的时候,「行汇总的百分比」和「总计的百分比」结果完全一样,容易误以为设置没生效。
- 空白单元格不计入分母,但显式写进去的 0 会计入。数据里混着 0 和空白,占比会和你手算的对不上。
- 百分比复制到别处粘出来的是显示文本,不是数值,排序和再计算都会出错。要参与计算就复制原值再自己除。
- 给值字段改名、或者删掉重加一遍,显示方式会回到默认。改完先刷新一次透视表,确认设置还在。
- 字段被拖到别的区域后,占比重算的基准跟着变,看着像数值整体漂移,其实是分母换了。
怎么验证
- 手算一组对照。挑占比最大的那一行,用它的小计除以总计,和透视表显示的数字比,允许四舍五入差 0.01%。
- 可见的占比加起来应该接近 100%。差得远说明有行被折叠、隐藏或被筛选掉了。
- 反向还原。用某一行的占比乘以总计,能还原出原来的金额,说明显示方式作用在正确的字段上。
第一项最容易发现问题,尤其当汇总是计数而不是求和的时候。
常见问题
为什么占比那一列显示的是 0.32 而不是百分比
值显示方式已经生效,只是数字格式没改。回到值字段设置的「数字格式」,选百分比并设好小数位;也可以直接选中这列在开始选项卡里点百分号按钮。
筛选之后占比变了是不是算错了
不是。百分比的分母默认跟着当前可见的数据走。要固定分母,就改用「列汇总的百分比」并保证列维度不被筛掉,或者单独做一张不筛选的透视表专门算全量总计。
想看和去年同期的差异怎么办
用「差异百分比」,把基本字段设成时间字段、基本项选到具体的年或月。前提是透视表里的时间字段已经正确分组成年、季、月,否则基本项里根本选不到。
一句话总结:占比不改公式,改值字段的「值显示方式」,先想清楚分母是行汇总、列汇总还是总计,改完刷新一次并手算一行核对。