最新

Excel 怎么用数据透视表算出占比

汇总出总额之后,更想看每一行占多少。讲清值显示方式在该改哪里、行占比和列占比有什么区别,以及筛选之后百分比为什么会变。

入口在哪

占比不是靠新加一列公式算的,而是改透视表里那个值字段的「显示方式」。入口有两个:在透视表的任意数值单元格上右键,选「值字段设置」;或者在右侧字段列表里点「值」区域中的那个字段,再选「值字段设置」。弹出的窗口下方有「值显示方式」标签,占比就在这一页里。

桌面版 Excel 和 WPS 表格的菜单叫法略有差别,WPS 里可能写作「显示方式为」;Mac 版 Excel 同样是右键进值字段设置。找不到时把整个右键菜单从头看一遍,那个入口一直都在,只是位置随版本变动。

具体步骤

  1. 先检查三个区域放对了没有:行放分类,列放第二个维度(不需要就空着),值放要汇总的数字。
  2. 确认值是「求和」而不是「计数」。「计数」算出来的是条数占比,不是金额占比。
  3. 右键任意一个数值 > 值字段设置 > 值显示方式。
  4. 要「每个分类占全部的多少」,选「总计的百分比」。
  5. 要「每一行里各列各占多少」,选「行汇总的百分比」;反过来看列内各行,选「列汇总的百分比」。
  6. 同一页的「数字格式」里设成百分比并保留两位小数,否则显示成 0.35 这种小数看不出是占比。
  7. 想同时保留原值和占比,把同一个字段再拖一次进「值」区域,做成两个值字段,一个照常求和、一个按百分比显示。

几个坑

  • 分母是哪个要分清。总计的百分比永远拿整张表的总计当分母,一旦筛选或点切片器,分母跟着变,同一个值两次看到的结果会不同。
  • 只有行没有列的时候,「行汇总的百分比」和「总计的百分比」结果完全一样,容易误以为设置没生效。
  • 空白单元格不计入分母,但显式写进去的 0 会计入。数据里混着 0 和空白,占比会和你手算的对不上。
  • 百分比复制到别处粘出来的是显示文本,不是数值,排序和再计算都会出错。要参与计算就复制原值再自己除。
  • 给值字段改名、或者删掉重加一遍,显示方式会回到默认。改完先刷新一次透视表,确认设置还在。
  • 字段被拖到别的区域后,占比重算的基准跟着变,看着像数值整体漂移,其实是分母换了。

怎么验证

  1. 手算一组对照。挑占比最大的那一行,用它的小计除以总计,和透视表显示的数字比,允许四舍五入差 0.01%。
  2. 可见的占比加起来应该接近 100%。差得远说明有行被折叠、隐藏或被筛选掉了。
  3. 反向还原。用某一行的占比乘以总计,能还原出原来的金额,说明显示方式作用在正确的字段上。

第一项最容易发现问题,尤其当汇总是计数而不是求和的时候。

常见问题

为什么占比那一列显示的是 0.32 而不是百分比

值显示方式已经生效,只是数字格式没改。回到值字段设置的「数字格式」,选百分比并设好小数位;也可以直接选中这列在开始选项卡里点百分号按钮。

筛选之后占比变了是不是算错了

不是。百分比的分母默认跟着当前可见的数据走。要固定分母,就改用「列汇总的百分比」并保证列维度不被筛掉,或者单独做一张不筛选的透视表专门算全量总计。

想看和去年同期的差异怎么办

用「差异百分比」,把基本字段设成时间字段、基本项选到具体的年或月。前提是透视表里的时间字段已经正确分组成年、季、月,否则基本项里根本选不到。

一句话总结:占比不改公式,改值字段的「值显示方式」,先想清楚分母是行汇总、列汇总还是总计,改完刷新一次并手算一行核对。