[ CIEM ] Chapter 03 - 数据透视表

Posted on | In ,
  • 数据透视表和D函数
  • 数据分类汇总分析

例题3-2：数据透视表和数据透视图

原始数据 最终效果
  • 打开原始数据
  • 选中一个单元格，插入->数据透视表
    • 选中第一行，按ctrl-shift-↓
    • 新工作表
    • 确定
  • 拖动字段：

  • 右键数据透视表，数据透视表选项，汇总和筛选，显示行统计，显示列统计

  • 点击数据透视表，插入，推荐的图标，簇状柱形图

例题3-3：外部数据源制作数据透视表

原始数据 最终效果
  • alt-d-p
    • 外部数据源
    • 获取数据
    • 选择excel文件以后选择合适的字段
  • 将结果返回excel
  • 完成数据透视表

例题3-4：改变分类

原始数据 最终效果

  • 数据透视表

  • 右键江苏：移动，上移

  • 右键山东：移动，移动至开头

  • 分类汇总

    • 选中华东的三省，右键组合，然后在数据组1处修改为华东
    • 华南同理

  • 在右侧的字段选项中，点击省份2，字段设置，地区

  • 在行字段添加：价位
  • 在列字段添加：渠道

  • 不显示二级分类汇总：

    • 设计，分类汇总，不显示分类汇总

  • 筛选行标签，可以勾选全部或者部分数据

  • 修改汇总字段名称或者汇总方式：求和项。。。，字段值设置，自定义名称，汇总方式

例题3-5：时间序列

原始数据 最终效果
  • 选中一个日期
  • 右键，组合
    • 选中月，年
    • 确定
  • 选中b5:b30，选择开始，数字，减少小数位数
  • 制作数据透视图：
    • 插入-图表-折线图-带数据标记的折线图
    • 右键选中数据序列，添加趋势线
    • 显示公式，显示R平方值

例题3-6：频率分布

原始数据 最终效果
  • 创建数据透视表
    • 值：数量，行：订购日期，列：数量，筛选：产品名称
  • 组合年月
  • 将年，月拖动到筛选
  • 将数量从列拖动到行
  • 右键一个数量，然后组合，步长设为10
  • 调整为百分比：
    • 左键值/计数项：数量
    • 字段设置
    • 值显示方式
    • 总计的百分比

例题3-7：计算百分比

原始数据 最终效果
  • 数据透视表
    • 行：订购日期，值：销售额
  • 分类订购日期为订购年
    • 行：公司名称
    • 筛选：订购年
  • 选中行标签筛选箭头，其它排序选项，降序排列，求和项，销售额
  • 选中前41个客户，组合，修改名称为重要客户

  • 再拖两个销售额到数值，选择计数。然后两个都改成列汇总的百分比

  • 插入数据透视图

    • 选中图例，设置数据系列格式
    • 把看不见的调整为次坐标轴

例题3-11：计算D函数，

Part 1

  • 公式，插入函数，数据库，Dsum
    • 第一个参数选择全部原始数据
    • 第二个参数手动输入，净销售额
    • 第三个参数选择设置好的条件

Part 2

  • 在h2单元格输入：
    • =D5（也就是计算方法，就是刚才的D函数）
  • 选中模拟表位置
  • 数据，模拟运算，模拟运算表
  • 需要把列中每一个分别带入E3，所以在引用列的单元格输入E3

Part3

  • 和上面的步骤完全相同

例题3-11-2：控件

原始数据 最终效果

文件-选项-自定义功能区-勾上开发工具

  • 插入，下拉框

  • 右键控件-设置控件格式

    • 数据源区域：B9：B14
    • 单元格链接：B8（B8单元格里是几，就选B9到B14当中的第几行）

  • 在条件区域D3：index

    • 第一个参数：抽取范围：b9:b14
    • 第二个参数：抽取行：B8
    • 第三个参数：抽取列：1
  • 画图：
    • 选中G3:H8
    • 选中坐标：设置坐标轴格式
      • 0~50000
    • 题目：
      • 在K2单元格输入题目
      • =D3 & “省各类产品净销售额总计图标”
      • 把标题放在图里面：插入文本框
  • 处理全部：
    • 在D3：=IF(B8=1,””,INDEX(B9:B14,B8,1))

例题3-11-3：更多的控件

原始数据 最终效果
  • 日期的第一个公式：B3： =IF(C8=1,”>=2009/1/1”,”>=”&INDEX(C9:C12,C8)&”/1/1”)
  • C3：=IF(C8=1,”<=”&”2011/12/31”,”<=”&INDEX(C9:C12,C8,1)&”/12/31”)
  • D5：=DSUM(原始数据,INDEX(D9:D10,D8),B2:E3)

例题3-12

原始数据 最终效果
  • 订购年月：A5：=DATE(YEAR(B5),MONTH(B5),1)

  • C5：=DSUM(Northwind公司销售数据!A1:D2158,”销售额”,B2:C3)

  • F3：1996/7/1

  • 制作模拟运算表（选的大）

  • 做一个控件

  • 做图：从F3开始选，选的少

    • 修改数据标记
    • 添加趋势线，选中横坐标，然后修改为文本坐标轴
    • 坐标轴选项，对齐方式，可以更改旋转角度

例题3-13

原始数据 最终效果
  • C5：=DSUM(原始数据,”销售额”,B2:B3)
  • 模拟运算表：
    • f3=c5
    • 引用列：c5
  • j4：=LARGE(\$F\$4:\$F\$92,H4)，第几大，范围不能变

  • 匹配最大值的公司名：i4：=INDEX(\$E\$4:\$E\$92,MATCH(J4,\$F\$4:\$F\$92,0))

    • match函数的用法：
      • 第一个参数：要找的数
      • 第二个参数：寻找范围
      • 第三个参数：寻找方式

  • 最后画图就行