文章为B端产品经理根据入职1年来工作所需,结合Excel线上课程所学,总结沉淀的数据透视表文档。

Excel数据透视表篇:4个方面出发,解决80%职场需求

作者尝试用两篇(函数篇+透视表篇)讲述初阶产品Excel80%职场需求,接上篇,本篇讲述数据透视表部分。

本文将从如下图所示 基本操作、布局与格式、组合功能、技巧四个部分进行讲述,只要用心掌握以下四个部分,基本解决80%的难题,工作效率会有质的提升。

Excel数据透视表篇:4个方面出发,解决80%职场需求

Excel数据透视表篇:4个方面出发,解决80%职场需求

函数计算的方法,虽然也能较快的计算出我们需要的结果,但效率不高(这里毕竟只是计算2个值,如果我们计算的值较多时效率问题会更明显)。在此,我们介绍快速按需求获取汇总数据的方法-数据透视表:

第一步:选中目标数据:选中目标区域任意单元格,Ctrl+A。

第二步:插入数据透视表:【插入】选项卡-【数据透视表】,【创建数据透视表】弹窗:“选择要分析的数据”(默认即可)和“选择要放置数据透视表的位置(现有工作表)”。弹窗选项说明如下:

Excel数据透视表篇:4个方面出发,解决80%职场需求

(1)【请选择要分析的数据】:如针对工作簿内数据分析,则点击“选择一个表或区域”(因为我们插入数据透视表前,已经选择区域,所以一般情况下,此处默认即可,也可以进行修改);针对非工作簿内数据分析,则点击“使用外部数据源”。

(2)【选择要放置数据透视表的位置】:如数据字段数较多且分析较复杂的情况下,一般选择“新工作表”,会在新的“sheet”中生成透视表;数据字段数较少的情况下,可选择“现有工作表”,在当前“sheet”中所选区域生成透视表。

第三步:选择字段,生成透视表:从【字段名称】列表里,点击字段拖拽至“筛选器、列、行、值”当中,如下图所示:给到的案例比较简单,只需要【行】和【值】两部分即可获取需要的结果。透视表结构如下图,详细说明如下:

Excel数据透视表篇:4个方面出发,解决80%职场需求

(1)行、列、值的应用:数据维度方在行,自变量放在列(因变量为值)。

如果我们想要看的是每一天,不同部门“30秒电话量总和”的差异,则日期是我们查看的数据维度(按照日期把数据拆分组,一个日期为一组数据,占到一行,呈现出来的就是有多少个日期就会有多少行数据);部门是自变量;而“某天某部门的30秒电话量总和”是因变量。

如果我们想要看的是同一部门,不同日期“30秒电话量总和”的差异,则部门是我们查看的数据维度(按照部门把数据拆分组,一个部门为一组数据,占到一行,呈现出来的就是有多少个部门就会有多少行数据);日期是自变量;而“某部门某日期的30秒电话量”是因变量。

Excel数据透视表篇:4个方面出发,解决80%职场需求

(2)值:汇总方式和显示方式介绍如下

汇总方式:如上所说的因变量-某日期某部门30秒电话量总和,即对数据源表的数据进行求和,求和就是汇总方式。常用的主要是求和和计数;

数据显示方式:即将汇总出来的结果以某种方式展示,从而更清晰的看出数据之间的关系和逻辑。常用的主要是总计的百分比和父行汇总的百分比;

①总计的百分比:个体占总体的情况,每一项分类汇总的值占总计的百分比。如:“某日期某部门30秒电话量总和” 占“数据源中所有日期、所有部门30秒电话量总和”的百分比

②父级百分比:个体占局部的情况,局部百分比。某列*行字段的汇总结果/行字段*所有列(即父行)的汇总结果(如上左图:12月2日销售二部的30秒电话数之和/12月2日所有部门的30秒电话数之和)

Excel数据透视表篇:4个方面出发,解决80%职场需求

2. 插入计算字段

需求的结果数据一般情况下都可使用“值”字段生成,因为“值”字段中的汇总方式包含了使用频率较高的通用的计算功能,但有一定的局限,而计算字段极大扩展了数据透视表的计算功能。

比如原始数据表中有一列数据为目标值,有一列数据为实际值,那么在数据透视表中可以通过计算字段输入公式=30秒电话量/30秒电话量目标值,来求出完成率,方法如下图所示:

    选中透视表任意单元格区域,右击选择公式-计算字段输入字段名称,输入公式:公式中的字段在“字段列表”选择字段插入

Excel数据透视表篇:4个方面出发,解决80%职场需求

3. 使用切片器

切片器功能同我们日常使用的数据报表(或产品在设计报表功能)时的筛选项是一样的,如下图所示,的数据报表中支持按日期筛选,2010版以上的excel版本的切片器功能也可以实现,方法如下。

Excel数据透视表篇:4个方面出发,解决80%职场需求

选中数据透视表任意单元格,在【数据透视表工具】选项卡下的【选项】子选项卡下单击【插入切片器】的下拉按钮,在弹出的【插入切片器】对话框中勾选自己所需的内容即可。切片器对象的右上角,有两个按键,左边的是多选按钮,后面的按键是取消筛选的按钮。

Excel数据透视表篇:4个方面出发,解决80%职场需求

4. 数据源刷新和更改

(1)数据源刷新

很多时候我们的数据源是不定期发生变化的,这就要求在数据透视表中也要体现出来,此时不需要重新创建一个新的数据透视表,刷新一下即可(原基础上修改,不增加行列的话)。

说明:大多数场景下使用的数据源均非外部数据源,本文仅介绍的为数据源为本工作簿的刷新方法。

1)手动刷新数据透视表:在数据透视表中的任意单元格区域鼠标右键,在弹出的快捷菜单中单击【刷新】命令即可;或,在【数据透视表】工具选项卡中,单击【刷新】/【全部刷新】按钮。

2)打开文件时刷新数据透视表:在数据透视表中的任意单元格区域鼠标右键,在弹出的快捷菜单中单击【数据透视表选项】命令,在【数据】选项卡小红,勾选“打开晚间时刷新数据”。

Excel数据透视表篇:4个方面出发,解决80%职场需求

(2)数据源更改

如果增加了行或者列,只是刷新是不行的,还需要更改数据源。

1)选中数据透视表中的任意单元格区域,在【数据透视表】工具选项卡中,单击【更改数据源】按钮,更改数据源区域。

Excel数据透视表篇:4个方面出发,解决80%职场需求

2)将数据源表设置成“表格”,选中数据源,【插入选项卡】点击【表格】按钮,设置成表格。不管增加行还是列都不需要再去更改数据源,只需要刷新即可。(需要注意:只针对将数据源更改为“表格”之后建立的透视表有效)

Excel数据透视表篇:4个方面出发,解决80%职场需求

Excel数据透视表篇:4个方面出发,解决80%职场需求

Excel数据透视表篇:4个方面出发,解决80%职场需求

1. 布局

数据透视表共有三种布局形式,分别是压缩形式、大纲形式、表格形式,各有不同的特点。如下图所示:选中数据透视表中的任意单元格区域,【设计】选项卡,点击【报表布局】更改布局形式。

    压缩形式:是Excel默认的透视表格式,主要的特点是:无论叠加多少个行字段,都只占一列,分项汇总显示在每项的上方。大纲形式:主要特点是:有几个行字段就会占几列,即行字段会并排显示,分项汇总显示在每项的上方。如下图,有部门和小组两个行字段,大纲形式的布局会占两列,而压缩形式只占一列。表格形式:是最常用的一种形式。主要特点是:与大纲形式一样,有几个行字段就会占几列,行字段会并排显示,有几个行字段会占几列;与大纲形式不同的是,表格形式是有表格的(如下图所示)且分项汇总显示在每项的下方。Excel数据透视表篇:4个方面出发,解决80%职场需求
2. 格式
    标签项重复显示:如“一、布局”中所述,布局格式选择中,可进行标签是否重复的设置。显示/隐藏分类汇总:选中透视表任意单元格区域,【右击】,勾选/取消勾选【分类汇总】即可。合并行标签:选中透视表任意单元格区域,【右击】-【数据透视表选项】-【布局和格式选项卡】-【合并且居中排列带标签的单元格】(只对表格形式布局有效)插入空行间隔:【设计】选项卡- 【布局】-【空行】-【在每个项目后插入空行】取消字段前”+-“符:选中透视表任意单元格区域,【右击】-【数据透视表选项】-【展开/折叠按钮】
Excel数据透视表篇:4个方面出发,解决80%职场需求

2. 按数值组合

如果是统计得分情况或年龄分段情况等数据列数值的分布情况,就需要用到透视表的数值分组,选中“分值”列的任意单元格,右键选择“创建组”,在组合中可设置起始和结尾以及步长。

3. 文本分组

如果是按地区统计或者个性化统计需求,可直接在透视表里面创建文本分组,在需要统计的列中,按住Ctrl键选择要组合的单元格,然后点击“鼠标右键”选择“创建组”即可,数据透视表就会按照我们所选定的内容进行组合,可以自行修改组的名称,例如改为华北大区。

Excel数据透视表篇:4个方面出发,解决80%职场需求

(3)数值类数据不能为文本格式。转换成常规数值的方法:使用“分列”功能进行处理,选中数据,【数据】菜单-选择“分列”(点击“下一步”-完成即可)。

(4)需对透视表数据再进行函数计算的,可将透视表转为普通表格:粘贴为值。

2. 3点学习建议(同上篇)

(1)工作中尝试承接涉及数据分析相关需求,有目标、具体场景的情况下学习速度会倍增。执行过程中,会遇到各种各样的问题,可通过快速百度查询、请教数据分析的同事等方式解决。

(2)报名1门exel线上课程(价格¥150左右),历时1个月。推荐起点学院的“21天Excel零基础俗称训练营”https://vip.qidianla.com/course/detail/if5qd.html;

推荐理由:性价比高、实用性强,表现在:内容好、有小节/章节作业检验+有答疑、有班主任老师管理(时间节点)+PK/奖励(上课期间还因为外出1周,错过了1个星期没有结业)。

Excel数据透视表篇:4个方面出发,解决80%职场需求

(3)输出exel学习经验并建立自己的知识速查表。学完不是自己的,只有消化吸收了才是自己的。对于技能学习只有孰能生巧一条路,学习完课程看似掌握了,但如果学完前期缺少实际场景的不断应用,很容易忘记。

所以学习完,建议输出exel学习经验,强化理解吸收;并形成自己的知识速查表,方便工作中使用快速查询及不断完善。

如上,有错误之处欢迎大家指正/交流。

#相关阅读#

Excel函数篇:十个函数解决80%职场需求

本文由 @ 团团 原创发布于人人都是产品经理,未经许可,禁止转载

题图来自 Unsplash,基于 CC0 协议




互联网人如何进行知识管理?

互联网人如何进行知识管理?

信息爆炸时候,我们每天都吸引了大量知识信息,而庞杂混乱的信息难以为我们提高有效的帮助,我们需要做的就是进行知识管理,通过方法窍门高效利用起来,提升自我。 01 ...

2020-06-19
产品总监晋升之道,游戏思维的团队管理艺术

产品总监晋升之道,游戏思维的团队管理艺术

作为一个产品总监,在面临年轻的团队,如果你的管理方式有问题,对团队乃至整个公司都是一件特别大的灾难。所以,今天我们来聊聊产品总监的团队管理问题与游戏化的团队...

2020-06-19
一个公式,从作品集小白修炼到初级交互设计师

一个公式,从作品集小白修炼到初级交互设计师

本文作者从自己个人经历和想法出发,从三个转变的角度谈谈如何从作品集过渡到行业项目,希望对所有刚刚踏入产品或交互行业的新手们有所帮助。 一、前言 在经历了半年多...

2020-06-18
2020年应聘反思,我总结了这些点

2020年应聘反思,我总结了这些点

在职场生涯中,我们可能会在好几家公司工作,重新应聘必然会遇到很多的问题。本文作者基于自身经历,从五个角度总结了自己的应聘经历,分享给对求职迷茫的童鞋。 先简...

2020-06-18
掌握砍价中的六大用户心理,轻松搞定甲方需求

掌握砍价中的六大用户心理,轻松搞定甲方需求

本文总结了砍价哲学中的六大用户心理,并结合案例对每个用户心理展开了分析说明。沟通的最大技巧是真诚,而谈判的目标是共赢。希望掌握好这六大用户心理,能帮助你更好...

2020-06-17
进大厂,还是进小厂?

进大厂,还是进小厂?

选择大厂还是小厂,对于很多人来说,是难以抉择的。大厂和小厂都有各自的优势、缺点,本文作者结合自己的个人视角,谈谈在大、小厂的经历和感悟。 职场也像个围城,在...

2020-06-09
网易严选交互团队的管理方式进化史

网易严选交互团队的管理方式进化史

管理团队是一个艰巨的任务,不仅需要管理者的智慧,也需要具备相关的管理知识。本文作者从具体的工作分工、文档管理分发和人员培养这三个方面出发,分享了网易严选交互...

2020-06-08
如何通过一张表,提高20%的工作效率?

如何通过一张表,提高20%的工作效率?

在日常工作中,每周的工作都充实而有所沉淀吗?是否觉得自己在假忙碌?如何有序推进工作进展,有序记录自己的工作?本文作者结合自身实践,讲述了在方法论上对自己工作...

2020-06-08
2020年,转行数据分析师需要注意哪些问题?

2020年,转行数据分析师需要注意哪些问题?

随着大数据在各个领域的应用越来越广,数据驱动产品和精细化运营已经成为企业经营的制胜法宝,相应地,数据分析师这个岗位也越来越受到关注。2020年,还能转行数据分析...

2020-06-05
京东群面题|如何用0.01元买到一瓶可乐?

京东群面题|如何用0.01元买到一瓶可乐?

这是一道京东的群面题,作者给出了一个简单的回答思路。 任何交易都遵循等价交换原则,如果一瓶可乐价格为0.01元(正常售价约为2.5元),那么必然需要其它形式的价值填...

2020-06-05
“空降型”产品经理快速入场指南

“空降型”产品经理快速入场指南

无论是内部调岗,还是职场跳槽,许多产品经理都必须面对融入的问题。就如一个空降兵,面对历史遗留问题,和现实的剧震,依旧需要义无反顾地发起冲锋。除了需要勇气,更...

2020-06-04
腾讯产品能力框架之通用能力篇:执行力

腾讯产品能力框架之通用能力篇:执行力

执行力就是以目标为导向,以结果为准绳。本文讲解腾讯产品能力框架中的通用能力——执行力,从五个层级分解学习能力,助力职场人走得更远。 本文是产品能力框架的第三篇...

2020-06-04
如何正确构建和使用产品知识库?

如何正确构建和使用产品知识库?

产品知识库的价值不在于文章数量的多少,所以不要刻意追求更新频率和更新数量。能够运用知识库去解决实际工作中遇到的问题,这才是其真正的价值。 1. 什么是产品知识库...

2020-06-04
解决80%以上职场冲突的认知方法——事实最大

解决80%以上职场冲突的认知方法——事实最大

事实最大。你掌握的事实越透彻,也就越强大。 日常人和人的沟通中,80%以上的冲突和挫败,来自与“事实”有关的两个问题: 问题1:没有基于事实来沟通。 假如,你是一位产...

2020-06-04
面试最烦的一种人——刷面经、背题型、找套路

面试最烦的一种人——刷面经、背题型、找套路

正如上次写群面的文章说的,群面的目的是做一名合格的「Horsekeeper」,而不是去想着争风头、当leader、抢reporter这些花里胡哨的。同样单面也不是刷面经、背题型这么简...

2020-06-04
我的14年设计路:从新人到设计总监

我的14年设计路:从新人到设计总监

从初入职场的野蛮生长到独当一面的设计总监,作者在职场摸爬滚打了十四年,分享自己的经历和总结,希望对你有所帮助。 最近,我从蘑菇街辞职了,我在这家公司一共呆了9...

2020-06-03
转岗交互前,我做了哪些准备(下)

转岗交互前,我做了哪些准备(下)

从UI设计到交互设计,作者回顾了自己的转岗之路,对过程中的经验与思考进行了总结,希望能对你有所帮助。 上篇文章我分享了交互设计师的产出物和我是如何学习交互设计...

2020-06-02
从职场菜鸟到独当一面,这三点建议要牢记

从职场菜鸟到独当一面,这三点建议要牢记

刚步入职场的新人最希望的就是能够以较快的速度成长起来,具体应该怎么做呢?本文给出了三点有用的建议。 现在外部大环境不是太好,就业压力巨大,岗位的竞争尤为激烈...

2020-06-02
我花了两年,成功把自己「毁」掉了

我花了两年,成功把自己「毁」掉了

初入职场的年轻人总是特别害怕和迷茫,不知道该怎么在职场中生存。本文作者将用自己两年的职场经验,告诉你什么路该走,什么路不该走。 毕业后的时光过得很快,今天是...

2020-06-01
锅背得冤不冤?产品经理的丛林生存法则

锅背得冤不冤?产品经理的丛林生存法则

甩锅体质:趋利避害是人的本性,甚至是动物的本性。产品经理作为被重点甩锅的对象,可以有108种背锅方式。究竟产品经理的黑锅,背得冤不冤? 一、锅都有哪些? 1. 进度...

2020-06-01