王佩丰Excel24讲_第6讲：数据透视表

一、创建数据透视表

• 源数据
  
  每列是一个属性，代表一个字段；每行是一条记录
• 目标表
  
  按月统计各部门的发生额
• 具体步骤
  点击“插入”>“数据透视表”>“行：月”>“列：部门”>“值：发生额”
  
  
• 如何更改数据透视表汇总方式？
  双击“求和项-发生额”，便可看到不同类型的“值字段汇总方式”，如下图：
  
  
  

二、数据透视表中的组合

1.日期组合

• 源数据
  
• 目标表
  
  按“所属区域”和“订购日期”统计各“产品类别”的金额
• 具体步骤
  • 首先点击“插入”>“数据透视表”>“行：所属区域、订购日期”，发现“订购日期”是具体年/月/日，需要改成“季度”展示。
  • 选中“订购日期”数据中任意一个单元格，然后右键选中“组合”，步长改为“季度”展示。
    
    
    
    
  • 然后选择“列：产品类别”>“值：金额”
    
    
    

2.文本组合

• 文本本身需要做一些手工的组合，没有什么规律，比如各个“产品类别”之间没有什么客观的联系，这边不多赘述。
  
  

3.数值组合

 按照金额本身划分一个区间

• 选中数字区域的任意一个单元格，“插入>数据透视表”
  
• 然后将“金额”分别拖入“行”和“值”，即根据金额的分类统计金额本身；但是不希望金额分的如此详细，打算做下分类，随意选中“金额”任意一个数据，右键选择“组合”，为了便于分类，将起始改为0，终止改为320000，步长改为40000；由此便完成了一个数据区间的统计。
  
  
  
  
  

注意：有两种情况可能导致日期无法汇总：
1.日期中有空格
2.有一个或全部日期是文本格式

三、汇总多列数据

 一列的数据从多个角度看

• 源数据
  
• 目标表
  
• 具体步骤
  先将“员工姓名”和“工号”拖入行，但是发现不在一行，此时选中透视表中任意一个数据单元格，点击“报表布局-以表格形式显示”，即可实现多个行标签并列显示的效果。
  
  
  
• 再将“生产数量”拖入值中，分别改值字段汇总方式为“求和、平均值、最大值、最小设值”，字段名称可自己设置。
  
  
  
  
  

四、在透视表中使用计算

• 源数据
  
• 目标表
  
• 具体步骤
  首先“插入-数据透视表”，将“规格型号”拖入行，“数量、合同金额、成本”分别拖入列
  
• 此时还差“利润率”字段，但是源表中别无这个数据，因此需要计算，点击“字段、项目”下的“计算字段”；输入名称是“合同金额”、公式=（合同金额-成本）/合同金额；由此利润率便计算出来了，并选中该列设置单元格格式为百分比，可是利润率中出现了#DIV/0！的报错，是因为分母为0了，对此可以勾选“数据透视表选项”的“格式-对于错误值，显示为空”即可。
  
  
  
  
  
  
  
  
  
  

五、利用筛选字段自动创建工作表

根据“科目划分”下的35个分类分别创建35张工作簿

• 源数据
  

• 目标表
  

• 具体步骤

  • 插入>数据透视表>行：科目划分>值：科目划分
  • 点击数据透视表中任意一个单元格，选中“选项-显示报表筛选页”，便可出现35张数据透视表，并且名字分别“科目划分”下的分类
  • 最后，选中这35张透视表，选择右键删除透视表中的文字和格式，即可变成35张空表
    
    
    

系列文章目录

第1讲：excel常用的基础操作
第2讲：excel单元格格式设置
第3讲：查找、替换、定位
第4讲：排序与筛选
第5讲：分类汇总、数据有效性
第6讲：数据透视表
第7讲：excel常用函数与公式
第8讲：if函数
第9讲：countif函数
第10讲：sumif函数
第11讲：vlookup函数
第12讲：match+index函数
第14讲：日期函数
第15讲：条件格式与公式
第16讲：简单文本函数
第17讲：数学函数
第18讲：lookup和数组
第19讲：indirect函数
第20讲：图表基础
第21讲：经典excel动态图表实现原理
第22讲：制作甘特图与动态甘特图
第23讲：双坐标柱形图、饼图美化与ppt图表
第24讲：宏表函数