Excel动态图表方法大集合,总有一款适合你!

图片发自简书App


图文 | 朱莉  来源 | 精进Excel


在这个万物都要看颜值的年代,做个数据报表也要好看。如果这时候能做个会动的图表,相信一定会加分不少。



动态图表制作的方法很多,根据不同情况,可以使用数据透视图加切片器,或者函数公式加上名称管理等来实现。


以下会用数据透视图和3个函数公式法分别举例,操作略有不同。快来找一个适合自己的方法吧。


以下内容信息量较大,建议先收藏哦~~


01 数据透视图法


数据透视图如透视表一样,适用于数量量大且格式规范的数据源。


通过透视图做出的图表与普通图表之间一个很大的区别是,透视图可以如透视表一样,灵活的变换布局,以及排序和筛选。


通过透视图做的动态图表,就是使用了数据透视的切片器功能,直观进行选项间的切换。


下面我们来说说制作步骤。


我们要处理的数据是一份销售记录,里面包含销售的城市、地区以及销售量等。我们现在想要以城市作为选项,查看每一个城市各产品的销量。


① 插入数据透视图


鼠标选中要透视的数据中的任意单元格,然后点击“插入”选项卡下的“数据透视图”,因为今天重点在图,所以我选择的是只创建数据透视图。



② 将数据透视图字段,鼠标左键点击拖拽到下方的四个框中


方法就是,想让哪个字段出现在什么位置,就将它拖到哪个框中。如下方动图所示:


想要出现在图中横坐标轴位置的字段,就把它拖到坐下角的轴(类别)框中,想要作为筛选查看的字段就放置在右上角的图例(系列)中。最后把要求和计算的“数量”拖到右下角的“值”区域。



③ 插入切片器

重点步骤,选中数据透视图,Excel中自动感应出三个数据透视图选项卡,然后单击“插入切片器”,然后勾选“城市”。


这时候,在切片器上单击任意的城市,透视图中就会出现相应城市的数据啦。



如果觉得默认的透视图外观不够美观,可以对透视图进行修改,比如可以对字段按钮单击鼠标右键,选择隐藏字段按钮。


切片的外观也可以修改,可以改成多列的排列,也可以修改按钮或者切片器的大小等。



对图表类型不满意,也可以点击“设计”选项卡-“更改图表类型”,选择合适的图形。




02 函数公式法1 - INDIRECT


函数公式法在小批量的二维表格中比较适用。不同的公式用到的步骤略有差异,但最终目的都是要通过公式的选择,来创造一个根据选项变话的区域,然后我们再用这个区域作图即可。


知识点:

制作下拉列表;

批量创建名称;

名称管理器;

INDIRECT函数。


① 制作供选择用的下拉列表


选中要制作下拉列表的单元格,点击“数据”-“数据验证”(数据有效性),“允许”中选择“序列”,“来源”选择左边这一列城市名。


这个步骤同样适用于后面几种函数公式,后续不再赘述。



② 批量插入名称


选择除第一行标题外的所有行,点击“公式”选项卡,在“定义的名称”区域选择“根据所选内容创建”,弹出的对话框选择“最左列”。



我们可以看到,刚才这一步起到的效果。


下图左上角的名称框中,我们选择任意城市后,表格中这个城市后面所有的单元格都被选中了。


也就是说这个城市,就是后面这几个单元格的名字,城市名就代表这几个单元格的。



③ 新建名称


然后再次在“名称管理器”中点“新建”,“名称”输入“销量”,引用位置输入:


=INDIRECT(函数公式法1!$J$3)


点击确定,这时候名称管理器中就创建好了一个叫“销量”的名称。



INDIRECT函数在这里的作用是,将括号里的文字,变成真正的单元格引用。


当J3单元格中是“成都”时,


=INDIRECT(函数公式法1!$J$3)

=INDIRECT(成都)

=B5:H5


所以“销量”这个名称代表的内容,当J3为“成都”时,就是B5到H5单元格的引用;


同理,当J3为“北京”时,就是B3到H3单元格的引用。


这样,“销量”就代表了一个根据J3单元内容随时变化的区域。


④ 最后一步作图


点击“插入”-“图表”中的“柱状图”(根据需要选择图形)


对着图形单击鼠标右键,点击“选择数据”,“系列名称”,可以选择J3单元格,“系列值”中输入:


=函数公式法1!销量


然后点击确定。(蓝色部分“函数公式法1!”是工作表的名称)


水平标签选择从B2单元格开始的第一行的标题。


这时,动态图表就做好了。




03 函数公式法2 - OFFSET+MATCH


第一种函数公式,重点是靠两次区域命名加INDIRECT函数来实现动态区域的引用。


第二种函数利用OFFSET函数自身的功能来实现偏移的效果。


知识点:

OFFSET函数;

MATCH函数;

名称管理。


① 公式选项卡,新建名称。


在“名称”中输入:“销量2”(主要为了跟上一个区分开来)


然后在引用位置中输入:


=OFFSET(函数公式法2!$B$2:$H$2,

MATCH(函数公式法2!$J$3,函数公式法2!$A$3:$A$16,0),0)



函数讲解:


OFFSET函数语法如下:



OFFSET函数是以指定的引用区域为参考,通过给定偏移量得到新的引用,返回的区域既可以为一个单元格或单元格区域,也可以指定返回的行数和列数。



MATCH函数语法如下:


MATCH函数的作用是,找到某个值,在给定区域中的位置。(在第几行或者第几列)


在这次的例子中,


MATCH(函数公式法2!$J$3,函数公式法2!$A$3:$A$16,0)


就是查找J3单元格中的内容,在A3到A6区域中的第几行,也就确定了OFFSET函数需要向下偏移几行。



比如,当J3单元格中是“成都”时,MATCH函数找到“成都”在A3到A16,也就是这些城市列表中,在第3行。所以OFFSET函数,就以上图蓝色区域的标题行作为参考,向下偏移3行(也就是成都所在的行)。


=OFFSET(函数公式法2!$B$2:$H$2,

MATCH(函数公式法2!$J$3,函数公式法2!$A$3:$A$16,0),0)


第三参数为0,表示向右不偏移。


省略第4、5参数,则返回与第一参数相同大小的区域。


所以上述公式,就能根据J3单元格中内容的不同,返回J3内容在表格区域中的对应的数据。


② 插入图表


步骤与函数公式法1相同。



04 函数公式法3 - VLOOKUP


上面两种函数公式法,都是通过公式,生成一个根据J3内容实时变动的引用区域。下面这种方法,不直接生成引用区域,而是通过构造一个“辅助”的区域,区域固定不变,但是区域中的内容根据公式变化。这样作图时只需在“辅助”的区域上做即可。


知识点:


VLOOKUP函数。


① 构造“辅助”行


在区域下方的空白单元格中,A18单元格中输入:


=J3


然后在B18到H18单元格中输入:


=VLOOKUP($A$17,$A$2:$H$15,COLUMN(),0)



VLOOKUP函数语法如下:



作用就是找到A18单元格中的内容,在上面表格中,对应的值。


第一参数是要找谁,第二参数是在哪找,第三参数是返回第几列的内容,第四参数是精确或模糊匹配。


这里,第三参数嵌套了一个COLUMN函数,目的是使用当前的列号,作为VLOOKUP的第三参数,也可以直接手动输入值,2,3,4……等。


这样,当J3内容发生变化时,18行中相应的数值就会发生变化。


接下来就是对18行的数据进行制图。


② 插入图表


过程类似,不再赘述。作图区域选择18行即可。



动图完成了,剩下的就是图形的美化过程了,大家可以根据自己的喜好修改图表布局、颜色等等。



以上就是今天教程的主要内容,大家都get到了吗?欢迎留言告诉我,你还有什么别的操作方法?



- END -



©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 156,265评论 4 359
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 66,274评论 1 288
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 106,087评论 0 237
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 43,479评论 0 203
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 51,782评论 3 285
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 40,218评论 1 207
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,594评论 2 309
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 30,316评论 0 194
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 33,955评论 1 237
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,274评论 2 240
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,803评论 1 255
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 28,177评论 2 250
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,732评论 3 229
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,953评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,687评论 0 192
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 35,263评论 2 267
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 35,189评论 2 258

推荐阅读更多精彩内容