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

2023-10-09 18:40

本文主要是介绍王佩丰Excel24讲_第21讲:经典excel动态图表实现原理,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

文章目录

  • 一、动态图表实现原理
    • 1.小试牛刀-利用IF创建简单的动态图表
  • 二、利用Offset函数与控件创建动态图表
    • 1.Offset函数概述
    • 2.Offset函数的动态引用示例
    • 3.动态图表1-永远返回最后10行数据
    • 4.动态图表2-通过控件控制图表数据
  • 系列文章目录


一、动态图表实现原理

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

这篇关于王佩丰Excel24讲_第21讲:经典excel动态图表实现原理的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/174801

相关文章

Java实现MD5加密的四种方式

《Java实现MD5加密的四种方式》MD5是一种广泛使用的哈希算法,其输出结果是一个128位的二进制数,通常以32位十六进制数的形式表示,MD5的底层实现涉及多个复杂的步骤和算法,本文给大家介绍了Ja... 目录MD5介绍Java 中实现 MD5 加密方式方法一:使用 MessageDigest方法二:使用

mysql删除无用用户的方法实现

《mysql删除无用用户的方法实现》本文主要介绍了mysql删除无用用户的方法实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧... 1、删除不用的账户(1) 查看当前已存在账户mysql> select user,host,pa

Nginx配置location+rewrite实现隐性域名配置

《Nginx配置location+rewrite实现隐性域名配置》本文主要介绍了Nginx配置location+rewrite实现隐性域名配置,包括基于根目录、条件和反向代理+rewrite配置的隐性... 目录1、配置基于根目录的隐性域名(就是nginx反向代理)2、配置基于条件的隐性域名2.1、基于条件

Linux配置IP地址的三种实现方式

《Linux配置IP地址的三种实现方式》:本文主要介绍Linux配置IP地址的三种实现方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录环境RedHat9第一种安装 直接配置网卡文件第二种方式 nmcli(Networkmanager command-line

Java导入、导出excel用法步骤保姆级教程(附封装好的工具类)

《Java导入、导出excel用法步骤保姆级教程(附封装好的工具类)》:本文主要介绍Java导入、导出excel的相关资料,讲解了使用Java和ApachePOI库将数据导出为Excel文件,包括... 目录前言一、引入Apache POI依赖二、用法&步骤2.1 创建Excel的元素2.3 样式和字体2.

Java实现将Markdown转换为纯文本

《Java实现将Markdown转换为纯文本》这篇文章主要为大家详细介绍了两种在Java中实现Markdown转纯文本的主流方法,文中的示例代码讲解详细,大家可以根据需求选择适合的方案... 目录方法一:使用正则表达式(轻量级方案)方法二:使用 Flexmark-Java 库(专业方案)1. 添加依赖(Ma

使用EasyExcel实现简单的Excel表格解析操作

《使用EasyExcel实现简单的Excel表格解析操作》:本文主要介绍如何使用EasyExcel完成简单的表格解析操作,同时实现了大量数据情况下数据的分次批量入库,并记录每条数据入库的状态,感兴... 目录前言固定模板及表数据格式的解析实现Excel模板内容对应的实体类实现AnalysisEventLis

Mybatis从3.4.0版本到3.5.7版本的迭代方法实现

《Mybatis从3.4.0版本到3.5.7版本的迭代方法实现》本文主要介绍了Mybatis从3.4.0版本到3.5.7版本的迭代方法实现,包括主要的功能增强、不兼容的更改和修复的错误,具有一定的参考... 目录一、3.4.01、主要的功能增强2、selectCursor example3、不兼容的更改二、

如何使用C#串口通讯实现数据的发送和接收

《如何使用C#串口通讯实现数据的发送和接收》本文详细介绍了如何使用C#实现基于串口通讯的数据发送和接收,通过SerialPort类,我们可以轻松实现串口通讯,并结合事件机制实现数据的传递和处理,感兴趣... 目录1. 概述2. 关键技术点2.1 SerialPort类2.2 异步接收数据2.3 数据解析2.

mybatis-plus 实现查询表名动态修改的示例代码

《mybatis-plus实现查询表名动态修改的示例代码》通过MyBatis-Plus实现表名的动态替换,根据配置或入参选择不同的表,本文主要介绍了mybatis-plus实现查询表名动态修改的示... 目录实现数据库初始化依赖包配置读取类设置 myBATis-plus 插件测试通过 mybatis-plu