Excel数据分析高级技巧①——动态图表制作(offset,vlookup,控件…)

时间:2024-02-29 22:15:30

tableau具有非常强大的自定义筛选功能,使得动态图表相当地酷炫。那Excel可以做出一样酷炫的动态图表吗?答案必须是个大大的Yes! 本文总结三种常用的动态图表制作方法: 数据透视表+切片器,vlookup+match+数据验证,Offset

一、数据透视表+切片器

1、查看原始文件——一份不同城市的近几月的销量记录表 2、建立数据透视表——全选数据-插入-数据透视表-选择现有工作表-将商品和销售额分别拉入行和值-右键销售额修改字段为求和,得到各商品的累计销售金额 在这里插入图片描述 3、插入切片——点击透视表任一位置会打开数据透视表菜单-点击分析-插入切片器- 筛选城市和月份,得到这2个字段的切片器,此时筛选会使得透视表数据自动改变 在这里插入图片描述 在这里插入图片描述

4、建立图表——点击透视表任一位-插入-二维柱状图-右键隐藏图表上所有字段按钮,得到初步的图形 在这里插入图片描述 在这里插入图片描述

5、美化图表——将切片器移至图表右侧-点点击图表选择喜欢的模式,搞定! 在这里插入图片描述

二、vlookup+match+数据验证

1、先看原始数据——将刚才的原始数据插入透视表,并将城市和销售额拉入行和值,值汇总,复制得到要用的数据 在这里插入图片描述 2、新建数据验证——选择一个空格-数据-数据验证-允许选择序列-数据来源点击右边的按钮框选所有的月份-得到一个带倒三角的筛选框 在这里插入图片描述 在这里插入图片描述 在这里插入图片描述

3、建立绘图数据——先复制城市-月份设置等于数据验证的单元格-数据部分利用vlookup+match匹配出想要筛选的月份的销售额,如下图所示: 在这里插入图片描述 在这里插入图片描述 4、建立图表——选择绘图数据-插入-饼图-找到一个空的单元格,输入公式=Sheet4!$G KaTeX parse error: Expected \'EOF\', got \'&\' at position 3: 10&̲"各城市的销售金额占比",得到… E$12,即可根据筛选后的月份进行图表动态调整 在这里插入图片描述 在这里插入图片描述 在这里插入图片描述 在这里插入图片描述

三、Offset函数

Offset一般用于动态展示近一段时间(如近7天,近1个月等)的数据,当新增数据后,可自动刷新图表,不用每一次新增数据都重新做图,是不是超级方便! 1、先看原始数据—1到6月的销售金额 在这里插入图片描述 2、定义两个字段的函数名称——公式-名称管理器-新建2个函数

    月份1:=OFFSET(Sheet3!$A$1,COUNTA(Sheet3!$A:$A)-1,0,IF(COUNTA(Sheet3!$A:$A)-1<5,(COUNTA(Sheet3!$A:$A)-1)*(-1),-5))
  金额1:
  =OFFSET(Sheet3!$B$1,COUNTA(Sheet3!$B:$B)-1,0,IF(COUNTA(Sheet3!$B:$B)-1<5,(COUNTA(Sheet3!$B:$B)-1)*(-1),-5))

在这里插入图片描述 3、创建图表——选择数据-插入二维柱状图-编辑数据-左边编辑-选择名称和刚才定义的月份1-再点击右边编辑-选择名称和刚才定义的金额1-确认 在这里插入图片描述 在这里插入图片描述 在这里插入图片描述 4、增加10月和11月的数据,图表自动更新成7月-11月的数据了 在这里插入图片描述

这就是三种动态图表制作方法的汇总, 第1种数据透视表相对操作简单且美观,适用于分析维度相对较多的分析场景;第3种offset函数更适用于不断更新的数据,只是函数写起来略有点麻烦,第2种其实可以用第1种替代


本人互联网数据分析师,目前已出 Excel SQL Pandas Matplotlib Seaborn 机器学习 统计学 个性推荐 关联算法 工作总结 系列。


微信搜索 "数据小斑马 " 公众号,回复“ 数据分析 "就可以 免费领取 数据分析升级打怪 15本必备教材

在这里插入图片描述

在这里插入图片描述