夜雨聆风学习资料网

ARTICLE · 1400518

电脑技术|Excel快速数据透视表,10000行数据1…

电脑技术|Excel快速数据透视表,10000行数据1…

上个月帮同事看一张销售明细表,一万多行数据,他要按地区、按业务员、按月份分别汇总销售额。他原来的做法是筛选加SUMIF,一个条件写一个公式,写了十几个,改一次条件全部推倒重来,改到第三遍人快崩溃了。我只用了一个功能,拖动几下字段,三分钟出结果,还顺手做了个占比。这个功能就是数据透视表。微软支持文档对它的定义是“计算、汇总和分析数据的强大工具,可助你了解数据中的对比、模式和趋势”,说白了,就是把一张流水账按你关心的维度重新排列组合。

先说最反常识、也是九成人透视表结果对不上的根源。微软支持文档里写得很清楚:默认情况下,放到“值”区域的数值字段显示为求和(SUM),如果 Excel 把这一列解释成了文本,就会显示成计数(COUNT)。所以你明明拖进去的是“金额”,结果出来一溜“计数项:金额”,别急着怀疑软件,八成是这列数字里混进了文本型数字——从网页或聊天软件复制粘贴带进来的不可见空格、单元格左上角的小绿三角、或者早期为了显示前导零加的英文单引号。这些“看着是数字其实不是”的格子,会让整列被判成文本。动手前先选中这一列看提示,或用“分列”统一转成数字,比事后排查省一半时间。

第一步,把数据源收拾干净。微软支持文档反复强调一条:数据要组织成“每列一个字段、顶部只有一行标题”的形式,中间不能有空行、空列,也不能有合并单元格。很多人一上来就框一大片区域,里面夹着几行备注、几列小计,透视表自然乱。如果你的原始数据是那种标题在第三行、下面还带说明的格式,先花两分钟把说明删掉、把标题提到第一行,这一步偷懒,后面每一步都得返工。

第二步,选中数据区域里任意一个单元格,按快捷键组合,或者点“插入—数据透视表”。如果你用的是 Microsoft 365,会看到微软提到的“推荐的数据透视表”,它自动帮你猜几个常用布局,订阅用户才能用;没有订阅也不用慌,选“创建自己的数据透视表”就行。接下来选放置位置,新建工作表最省心,别直接盖在原始数据上。

第三步是字段窗格,也是整个操作的灵魂。微软支持文档说明了默认归属规则:非数值字段自动进“行”,日期和时间字段进“列”,数值字段进“值”。你要做的就是按需拖动——把“地区”拖到行,把“月份”拖到列,把“销售额”拖到值,一张交叉汇总表瞬间成型。这里有个分支:如果你想看每个地区占总额的比例,不用另算,右键点“值”里的那个金额,选“值显示方式”,里面有“总计的百分比”。想把同一个字段既显示金额又显示占比,微软支持文档给的技巧是把它拖进“值”区域两次,然后分别设置汇总方式和显示方式。

第四步,改汇总方式。右键金额字段选“值字段设置”,默认的“求和”之外还有平均值、最大值、计数、去重计数等。这里提醒一个坑:微软支持文档建议,改完汇总方式再考虑重命名,因为改计算方式时 Excel 会自动把字段名改成“求和项:销售额”这类样子。批量去掉这个前缀不用一个个手敲,用查找替换(Ctrl+H),查找“求和项:”,替换成空白,一次全清。

第五步,刷新。这是最容易被忽略的一步。微软支持文档指出,只要源数据新增了行,基于它建的所有透视表都需要刷新,否则你看到的是上周的旧结果。单个表右键选“刷新”即可;工作簿里有好几个表,就选中任意一个单元格,到“数据透视表分析”选项卡里点“全部刷新”。如果你每次数据都是重新粘贴进来的,索性把数据源做成“表格”(Ctrl+T),新增行会自动纳入范围,能少刷新一次是一次。

如果你要处理的是几十万行、多个表关联的大数据,普通透视表会有点吃力。这时可以勾选“将此数据添加到数据模型”,微软支持文档说它适合用多个表建透视表、加自定义度量值、处理非常大的数据集。公司用了 Power BI 的话,还能直接连到认可的云数据集。这一步普通办公一般用不到,但知道有这条路,遇到大表不至于抓瞎。

再补两个高频小坑。一个是把字段拖错了区域——同样一个“月份”,放在“行”里是往下排成一行行,放在“列”里是横着排成列,很多人拖完发现表格“躺倒了”,其实就是区域放错了,拽回去即可,不用重做。另一个是日期分组:如果你的“日期”列是一天一天的具体日期,想按“月”或“季度”汇总,不用提前加辅助列,在透视表里右键任一日期,选“组合”,按月、按季度、按年份分组都行,这一步比在原始数据里写公式干净得多。

再说说被人问得最多的一句:“我的数据源每次行数都不一样,是不是每次都要重新选区域?”不用。把原始数据区域转成“表格”(Ctrl+T),它会自动向四周扩展,你在表格末尾粘贴新行,透视表刷新时就自动纳入。如果你的公司版本支持动态数组函数,还能用公式定义一个会自动伸缩的命名区域,不过对大多数人来说,Ctrl+T 已经够用。还有一个提升体验的小控件叫“切片器”,点一下就能按地区、按月份筛选整张透视表,比反复拖字段直观,做汇报时特别讨喜。

最后说个心得。透视表真正的价值不在“快”,而在“可反复改”。公式写死在一个条件上,换个维度就得重写;透视表只要拖一下字段,同一份数据能立刻换一个角度看。把它当成数据的万能插座,而不是一次性计算器,你的表格效率会有质的变化。今天找一张手头的明细表,照着这五步做一遍,比看十篇教程都管用。

回首页,搜索你想要的文档

在首页搜索框输入关键词,快速定位资料

文库首页搜索指引 立即搜索

相关学习资料