数据分析的工具,Excel是最通用普遍使用的工具;熟练地使用Excel中的数据透视表,则是每个职场人士必须具备的基本能力!
虽然透视表的入门非常简单,但很可惜的是,仍有不少的同学未能高效地使用,本文将分享数据透视表相关的10大效率操作,助你养成良好的使用习惯,高效地玩透视表~
生成透视表前,习惯要把数据源转换成【表】的形式:
① 点击数据源中的任意一个单元格
② 按下快捷键「CTRL+T」(请必须确保你的数据是有表头的)
③ 点下确定,就成功把数据源转成【表】了
为什么要用表---表格有自动扩展的特性,当你增加数据时,不需要再修改透视表的数据源;再深入的用法就是你可以用PQ或SQL连接外部的数据进来,实现全自动化的报表。
分析数据时,经常要看年、季、月各种时间维度的数据。不需要在数据源中增加多余的列,只需要使用分组功能就ok,非常高效!
① 把日期字段拉到行或列区域中
② 右键点击透视表中任意一个日期,选择「分组」
③ 选择你要组合的日期维度,点确定,就实现了!
有时我们想要一个特定的顺序排列,为了实现这个目的,你可以选择手工拖动进行重新排序,这个在行字段、列字段都是一样的操作方法。
① 选择行/列字段里的某一项,光标停留在该项的边框上,直到出现移动的图标(有4个方向的箭头)
② 按住鼠标左键把该项拖动到你期望的位置,就实现了拖动排序
透视表的报表,我们其实经常要改指标的数字格式的,例如把数字的单位改成「万」。但很多人的操作不够规范,下图是错误示范:
正确的操作应该是如下图所示:
更改值字段的数字格式,操作步骤:
① 右键值字段的任意一个数字
③ 设置你要的数字格式
→ 改变的是透视表字段的格式,而非单元格的格式
→ 如果你要换一个指标时,不需要重新调整单元格格式,行数变化也自适应
→ 选整列调整单元格格式会影响了列所有的单元格
条件格式,在表格可视中可以帮助我们更好地看数据,所以透视表配合条件格式+自定义格式是非常好的展示效果。
第4点我们介绍过自定义格式需要设置值字段,而不要选择单元格格式。那么在透视表中如果要设置条件格式,同样是不要选择单元格或整列去设置。正确的姿势是:
从上图可以看到,即使我更换了行字段,条件格式仍然存在,并会自动适应行数。
① 选中值字段的任意一个单元格,设置好条件格式
② 然后在该单元格右侧会有一个智能标记提示,点开它
③ 所选单元格→就是仅指当前选定的单元格
所有显示为该值字段的单元格→就是指设置这个值字段有条件格式(行数会自适应)
所有显示为“品牌”的值字段→就是指仅为品牌的值字段设置条件格式,切换成其他筛选字段就没了条件格式
当你想看到某一项的明细时,你可以双击对应的项/值,去显示下层的数据或者明细的数据
① 双击行列字段的项,可以选择下钻的字段
② 双击某项的值,会生成新的Sheet显示明细
当透视表本身的功能不足以实现需求时,我们还需要借助公式去配合处理。但是当我们公式参数选择透视表的值时,经常会出现GETPIVOTDATA这个烦人的东西~
GETPIVOTDATA其实是可以帮我们动态获取透视表的指定数据,但一般用得很少,所以取消它没有什么不良影响。
每次刷新透视表、切换行列值字段时,列宽都动态在变化?对布局造成各种困扰!所以必须要知道怎么取消这个选项:
① 右键透视表任意一个单元格 →
② 切换至「布局和格式」的选项中,如上图所示
③ 取消勾选“更新时自动调整列宽”
当你插入透视表时,Excel会把数据源都放入到工作簿级别的缓存中,然后后续的数据透视表操作是基于缓存去进行的。所以当生成透视表后,再删除你的数据源,其实并不会对透视表有影响。
这有什么用?或者有什么要注意的?
① 脱离数据源,透视表正常运作,可以减少文件的Size,利于分享、打开速度提升
② 你也可以通过上面介绍的双击下钻看到明细数据
③ 注意:需要刷新数据时,将会失败,因为连接不了数据源了。
你可以把多张表的数据连接在一起,在一个透视表中使用。例如「订单表」、「产品表」、「门店表」等,传统方法是用vlookup整合成一张大表,然后再生成透视表。
但这样的操作太麻烦,运算效率低,数据量大,刷新个vlookup都要几分钟,实在令人受不了!尤其是当你要处理几十万、上百万数据时,用传统的透视表尤如老鼠拉龟!
在Excel 2016以上,开始支持由Power Pivot数据模型中输出为透视表,这时就真的彻底抛弃vlookup了!几百万行的数据量,也能高效的运算,灵活的指标构造语言,还支持导入到power bi中使用!
想学习用Excel的Power Pivot模型输出透视表报表的朋友,可以趁着现在有特别活动,加入《数说》会员学习Excel的BI入门,只要你有透视表的经验,就能实现入门,学习Excel全自动报表的新逻辑!
除了Excel,《数说》还有业务分析思维,分析理论,Excel分析实操等内容,非常丰富!由于《数说》内容的丰富、实用、通用,《数说》是全网中屈指可数的数据分析综合优质课程之一!
中秋特惠活动 → 限时7折
你的时间花在哪里,成就就在哪里!
各位老铁们好,相信很多人对怎么用excel做数据透视表都不是特别的了解,因此呢,今天就来为大家分享下关于怎么用excel做数据透视表以及在excel中怎么做数据透视表的问题知识,还望可以帮助大家,解决大家的一些困惑,下面一起来看看吧!
怎么做excel数据透视表
excel数据透视表容易直观地观察数据的总量,在想知道某一项的合计时很常用,尤其是数据量庞大时非常方便。下面我详细介绍一下做excel数据透视表的方法,希望对你有帮助!
1、将光标移入需建立数据透视表的工作表中,然后单击"数据"→"数据透视表和数据透视图"。
2、"数据透视表和数据透视图向导-3步骤…"对话框上,选用默认选项,“Microsoft Excel数据清单或数据库”,单击"下一步"。
3、在"数据透视表和数据透视图向导-3步骤…"对话框上,在“选定区域”框中,已经自动选定工作表的(整个)数据区域,单击"下一步"。
4、在"数据透视表和数据透视图向导-3步骤之3"对话框上,选用默认选项:"新建工作表",在当前工作的左侧创建一个新工作表。这样做,可以保证原始数据的安全以及可利用性。单击"完成"后,一张新工作表-“数据表(下图所示)”即出现在当前工作的左侧。
1、 数据表上,几个区域的设定方法
(1)“请将行字段拖至此处”(相当于“左端标题行”),将作为横向分类依据的字段,从“数据透视表”工具栏上拖至此处。例如,将“乡镇(街道)”字段拖至此处。
(2)“请将列字段拖至此处”(相当于“顶端标题行”),将作为纵向分类依据的字段,从“数据透视表”工具栏上拖至此处。拖至此处的字段是计算的“依据”。
(3)“请将数据项拖至此处”(相当于“普通数据区”),将作为统计依据的字段,从“数据透视表”工具栏上拖至此处。拖入该区中的字段,Excel将用某种算法,对其进行计算,然后将计算结果(汇总数值)显示出来。例如,将“工业总产值”、“本年折旧”字段拖至此处,即可得到各乡镇的求和汇总数。
(4)“请将页字段拖至此处”,将作为分类显示(筛选)依据的字段,从“数据透视表”工具栏上拖至此处。
可以将一个或多个字段拖至此处。此区域中的字段是分类筛选的.“首要”条件。例如,将“县”字段拖至此处,然后在其中,选择“沙县”,则下方表格中,只统计“沙县”有关的情况。
提示:“顶端标题行”和“页字段”区中,均可有多个字段。
改变透视表内部的透视关系,从不同的角度查看数据之间的内在关系。主要包括:改变透视关系、添加或删除项目以及数据更新
更改各区域中的放置字段,或改变字段的分组条件即可以改变透视关系。
添加字段的方法上面已经介绍过了,删除方法,是将需删除字段拖出数据表区域即可。
改变字段分组条件的方法:单击字段名右侧的三角形按钮,然后单击下拉式列表菜单上的相应条件
(2)改变统计字段的算法
双击透视表左上角的统计字段名,打开”数据透视表字段“对话框(如下图)。
在“汇总方式”框内,选定所需的算法,然后单击“确定”。
若原工作表中的数据(含标题行-字段名)更改后,透视表作相应更新的方法:
默认情况下,当重新打开工作薄时,透视表数据将自动更新。
在不重新打开工作薄的情况下,在原工作表的数据更改后,在透视表中单击“数据透视表”工具栏上的“更新数据”按钮即可。
提示:普通数据更改后,单击“更新数据”按钮后,即可完成更新。若更改了已经拖入透视表中的字段名,则该字段将从透视中删除,需要重新添加。
将透视表制作成透视图表的方法是:单击“数据透视表”工具栏上的“图表向导”即可生成一张图表(位于左侧的新工作表上)。
透视图表的编辑方法,与普通图表相似。
①首先,在Excel中打开准备好了的电子表格,单击菜单栏--“插入”--“数据透视表”。
②弹出创建数据透视表对话框,我们选择表区域,以及放置数据透视表的位置,单击确定。
③右侧窗格出现数据透视表字段列表,我们将里面的四个字段全部勾选,然后单击“选项”按钮。
④在书籍透视表选项界面中,切换到汇总和筛选标签,只勾选“排序中的排序时使用自定义列表”,单击确定。
⑤切换到显示标签,勾选“显示字段标题和筛选下拉列表”,以及“经典数据透视表布局选项”。
⑥确定之后相应的数据透视表发生变化,右击单元格,选择“字段设置”。
⑦弹出字段设置界面,在“分类汇总和筛选”标签中,勾选“无”,单击确定。
⑧选中数据透视表区域,按下Ctrl+C复制,然后点击下面的新建工作簿按钮。
⑨在新建的表格中,将复制的内容粘贴进来,然后按下F5键,打开定位对话框,点击“定位条件”,勾选“空值”,确定。
举报,一经查实,本站将立刻删除、维护您的正当权益。
版权声明:文章内容来源于网络,版权归原作者所有,如有侵权请点击这里与我们联系,我们将及时删除。