王佩丰Excel24讲_第21讲：经典excel动态图表实现原理

一、动态图表实现原理

1.小试牛刀-利用IF创建简单的动态图表

• 目标（通过勾选复选框控制图表）
  
  - 步骤
  • 首先插入2个复选框，并编辑复选框名字—“彩盒”、“宠物用品”
    
    
    
  • 如何使复选框与单元格联动？
    • 点击右键设置“控件格式”，选中一个单元格作为单元格链接，则发现勾选复选框显示TRUE，不勾选复选框显示FALSE
      
      
      
    • 接下来通过IF函数，实现单元格链接与单元格数据的联动，将IF函数公式剪切到“公式-定义名称-引用位置”，目的是将“彩盒”这个名称与IF函数连接起来。
      
      注意，需要全部加上绝对引用
      
      
    • 最后插入二维折线图，右键点击“选择数据”，输入“系列名称：彩盒”，“系列值：Sheet1!彩盒”
      
      
      
      
    • 此时会发现，勾选复选框“彩盒”会显示彩盒的折线数据，不勾选“彩盒”则不会显示数据
      
      
    • 同理，“宠物用品”也是一样
      
      
      
      
      
      
  • 但是此时有一个小问题，即分别勾选“彩盒”和“宠物用品”时，纵坐标轴的数值在不断变化，看起来不美观，可以将其设置成固定的数值
    
    
    
    
    
    • 此时发现无论勾选“彩盒”还是“宠物用品”，纵坐标轴的数值均不变
      
      
  • 但是此时距离我们完成目标还差一步，在目标中复选框是位于图例前面的，如下图：
    
    • 如何实现呢？
      首先勾选“图表元素”，即在图表中显示图例
      
      然后删掉复选框中的文字，只留下单独额复选框
      
      
      之后将复选框移置图例前面，但是发现并没有显示出来，这是因为被图表盖住了
      
      选中图表，设置为“置于底层”，会发现复选框显示出来了
      
      
      至此可实现：通过勾选复选框控制图表
      
      
      
      

二、利用Offset函数与控件创建动态图表

1.Offset函数概述

OFFSET(参照区域,行数,列数,[高度],[宽度])
以指定的引用为参照系，通过给定的偏移量、行数及列数返回一个新的引用
换言之：以某个点为基准，下移n行，右移n列，取n行n列

COUNTA(值1,…)
返回参数列表中非空单元格的个数

• 示例1
  
• 示例2
  
  
  

2.Offset函数的动态引用示例

• 常规的用数据透视表插入数据无法实现原数据变化透视表跟着变化

  • 首先选中任意一个单元格，点击“插入-数据透视表-确定”，做一个根据“产品类别”汇总“金额”的数据透视表
    
    
  • 此时原数据是125行，若增加3行，即为128行，刷新数据透视表，发现数据并未发生变化。
    
    
    
    
    
    

• 那么如何使得数据透视表可以根据原数据行数变化而变化呢？
  此时可以用offset()函数定义一个变化的数据区域，然后插入数据透视表即可

  • 首先如何取一个可变化的数据区域呢？
    利用offset函数，以A1为基准，向下移0行，向右移0行，取11列数据-即取到K列，取A列所在的所有非空单元格行，公式=OFFSET($ A$ 1,0,0,COUNTA($ A:$A),11)
    
  • 然后剪切公式，新建“公式-名称管理器-名称‘数据区域’-引用位置‘所剪切的公式’”，
    
  • 接着“插入-数据透视表-选择单元格区域：数据区域-勾选新工作表”
    
  • 选择“产品类别”和“金额”，此时的总计数据量是207万多，然后插入3行新数据（并将其中一个金额改成1000万），再刷新数据透视表，发现此时数据透视表的总计变成1208万多。
    
    
    
    
    据此实现了数据透视表根据原数据行数变化而变化
    
    

3.动态图表1-永远返回最后10行数据

• 示例：永远返回最后10行数据
  目标图表如下：
  
• 步骤
  • 写公式=OFFSET($ B$ 1,COUNTA($ B:$ B)-10,0,10,1)，以下图为例：以B1为基准，向右移0行，向下移17-10=7行，取10行1列数据
    
  • 然后将该公式定义为名称是“成交量”的一个新公式（该公式只取成交量的倒数10位的数据）
    
  • 插入空白折线图，右键“选择数据”，点击“系列-添加”，输入“系列名称：成交量”，“系列值：=Sheet4!成交量”，此时点击图表发现对应原数据最后10行
    
    
    
    
    
  • 当然可以多复制2行数据，此时发现图表对应数据由8-17行变化为10-19行
    
  • 至此成交量已完成动态变化，但是发现此时的横轴是数字1-10，并非日期，所以日期这列也要做动态引用，过程与成交量相同
    
    
    
    
    
    
    
    至此可变化的日期列和成交量列完成
    
    

4.动态图表2-通过控件控制图表数据

• 目标图表如下：
  
  每点一下第1个滚动条，所取数据向下走1行，但取的行数不变，都是20行数据；第2个滚动条控制取多少数据，点一下取22行，再一点取23行、24行、25行…
  这两个滚动条分工不同，分别控制offset中的两个参数，一个是下移多少行，另一个是取多少行

• 具体步骤

  • 点击“开发工具”，插入“滚动条”，右键“设置对象格式”时发现只有“大小、属性”，无法设置控件格式（这是因为我用的wps下的excel），可以在“插入-窗体”下选择“滚动条”，此时右键“设置对象格式”，发现多了一个“控制”界面可以设置控件格式
    
    
    
    
    
    
  • 首先“插入-窗体-滚动条”，并复制一个，如下图：
    
    
    
  • 此时控件做出来了，但是控件要和excel表关联到一起才能被函数引用到，因为函数需要引用具体某个单元格的值，怎么实现呢？右键设置控件格式“最小值：1”（默认是0，如果为0取不到数据会报错）、“单元格链接F3”；同时更改另一个滚动条的设置，也是“最小值：1”，但是单元格链接是F6
    
    
    
  • 接下来开始写公式=OFFSET($ B$ 1,$ F$ 3,0,$ F$ 6,1)
    以B1为基准，向下移F3行，向右移0行，取F6行，取1列
    
  • 然后将该公式剪切并定义为名称是“成交量2”的公式，并插入空白柱形图，右键“选择数据”，添加“系列”，“系列名称：成交量、系列值：=Sheet2!成交量2”，此时拖动2个滚动条发现柱形图会随之变化，至此成交量柱形图已完成；但是还差日期
    
    
    
    
    
    
  • 接着再写一个offset()函数，使得日期也能随滚动条变化而变化，公式=OFFSET($ A$ 1,$ F$ 3,0,$ F$ 6,1)，以A1为基准，向下移F3行，向右移0行，取F6行，取1列
    
  • 然后将该公式剪切并定义为名称是“日期2”的公式，选中图表区域，右键“选择数据”，修改“类别”，“轴标签区域为：=Sheet2!日期2”，发现横坐标轴有原来的数字变成了现在的日期，此时拖动2个滚动条发现柱形图的横纵坐标均会发生变化，至此柱形图已完成
    
    
    
    
    
    

系列文章目录

第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讲：宏表函数