excel从数据到美化，一文讲解Excel数据透视表基本操作

Excel技巧 2023-01-08

Excel数据透视表可以说是Excel中最强大的功能，没有之一！通过对明细数据的聚合分类，可以方便快速的得出想要的结果。

很多小伙伴对透视表的操作还比较生疏，今天我们就用一个小案例，从头到尾讲解下透视表的具体用法！

数据源准备

数据透视之前得有一份正确完整的数据源，相信很多小伙伴在对数据进行透视的时候都出现过下图中的报错情况：

"数据透视表字段名无效"，这句话的意思是对于数据源，每一列都应有一个确定的列名，下图这种情况，是无法正确建立数据透视表的；

错误1：A、B列表头存在合并单元格；错误2：D列表头为空，这两种情况皆不能建立数据透视表！也就是说，需要透视的数据源，第一行每个单元格必须有值（可以重复，透视表会默认排序，用字段1，字段2表示）。

下图是今天我们要透视的一份数据源，一共6列数据：

对于这份数据源，通过数据透视表，我们能得到什么样的数据？

1、各分类(如邮寄方式、地区、类别、日期)的销售额与数量；

2、各分类的的销售单价；

3、各分类的销售占比；

4、各分类组合销售额与数量；

等等.......

【Ctrl】+【A】全选数据，依次点击【插入】—【数据透视表】，第一个选择框为表区域，由于刚开始已经Ctrl+A全选了数据，这里不需要修改；第二个选择框为透视表存放的位置，默认新建一个插页用于放置透视表，这里不做修改，点击【确定】，完成数据透视表的建立。

默认新建一个插页存放数据透视表，新建立的数据透视表有两个区域，一个是报表展示区域，一个是透视表字段区域。

透视表字段区域一共有四个方框，通过拖动不同的字段，可以展示不同的结果。

筛选框：顾名思义，将字段拖动到筛选框中，可以利用此字段对透视表进行筛选，上图中将日期拖到筛选框，可以对日期进行筛选；

列框：将字段拖动到此处，数据将以列的形式展示，如上图将类别拖动到列框中，类别各字段分布在各列中；

行框：将字段拖动到此处，数据将以行的形式展示，如上图将地区拖动到行框中，地区各字段分布在各行中；

值框：主要用来统计，数字字段可进行数学运算（求和、平均值、计数等等），文本字段可计数，如上图，将销售额字段拖动到值框中，透视表显示出各类别的销售额。

开头我们说过，通过透视表，可以获得各分类(如邮寄方式、地区、类别、日期)的销售额与数量，下面我们拖一个字段【地区】的销售额与数量；

将字段【地区】拖动到行区域，销售额与数量拖动到值区域，即可完成。

获取各区域销售额平均值

上面介绍过，数字类型拖动到值区域，支持很多数学运算，如求和、计数、平均值、最大最小值等等；选中销售额列，右键选择【值字段设置】-【平均值】-【确定】，即可获取各地区的销售额平均值。

获取各区域销售额占比

在销售额为求和项的基础上，右键销售额列任一单元格，选择【值显示方式】—【列汇总的百分比】，这样就可以获取各地区销售额占比，是不是很方便？同时还存在很多其它汇总方式，小伙伴可以一一尝试。

新建字段获取销售单价

如何获取每个地区的单价？一般的操作是直接用销售额列除以数量列就可以，但是外部添加的公式并不能作为数据透视表的一部分，透视表改变，数据就会发生变化，容易出错。

透视表有个强大的功能【计算字段】，选中透视表，依次点击【分析】—【计算】—【字段、项目合集】—【计算字段】，在弹出的框中，名称任意输入，公式栏输入："=销售额/数量"，点击【确定】按钮，可以发现，透视表中出现新的一列，单价列，并且作为数据透视表的一部分，可以随意拖动。

当拖动多个字段到行区域时，透视表展示的形式往往不是很直观，如下图，拖动两个字段到行区域内，地区与类别都在A列中，极为不适。

下面用透视表通用的调整方法，将透视表调整到适宜观看的列表形式。

选中数据透视表，依次点击【设计】-【报表布局】，点击【以表格形式显示】，继续点击【重复所有项目标签】，在下拉【分类汇总】，点击【不显示分类汇总】，最后选择【分析】，点击取消【+/-按钮】，这样调整出来的透视表是不是更为直观呢？