在这个万物都要看颜值的年代,做个数据报表也要好看。如果这时候能做个会动的图表,相信一定会加分不少。 动态图表制作的方法很多,根据不同情况,可以使用数据透视图加切片器,或者函数公式加上名称管理等来实现。 以下会用数据透视图和3个函数公式法分别举例,操作略有不同。快来找一个适合自己的方法吧。 以下内容信息量较大,建议先收藏哦~~ 001 数据透视图法数据透视图如透视表一样,适用于数量量大且格式规范的数据源。 通过透视图做出的图表与普通图表之间一个很大的区别是,透视图可以如透视表一样,灵活的变换布局,以及排序和筛选。 通过透视图做的动态图表,就是使用了数据透视的切片器功能,直观进行选项间的切换。 下面我们来说说制作步骤。 我们要处理的数据是一份销售记录,里面包含销售的城市、地区以及销售量等。我们现在想要以城市作为选项,查看每一个城市各产品的销量。 ① 插入数据透视图 鼠标选中要透视的数据中的任意单元格,然后点击“插入”选项卡下的“数据透视图”,因为今天重点在图,所以我选择的是只创建数据透视图。 ② 将数据透视图字段,鼠标左键点击拖拽到下方的四个框中。 方法就是,想让哪个字段出现在什么位置,就将它拖到哪个框中。如下方动图所示: 想要出现在图中横坐标轴位置的字段,就把它拖到坐下角的轴(类别)框中,想要作为筛选查看的字段就放置在右上角的图例(系列)中。最后把要求和计算的“数量”拖到右下角的“值”区域。 ③ 插入切片器 重点步骤,选中数据透视图,Excel中自动感应出三个数据透视图选项卡,然后单击“插入切片器”,然后勾选“城市”。 这时候,在切片器上单击任意的城市,透视图中就会出现相应城市的数据啦。 如果觉得默认的透视图外观不够美观,可以对透视图进行修改,比如可以对字段按钮单击鼠标右键,选择隐藏字段按钮。 切片的外观也可以修改,可以改成多列的排列,也可以修改按钮或者切片器的大小等。 对图表类型不满意,也可以点击“设计”选项卡-“更改图表类型”,选择合适的图形。 002 函数公式法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单元格开始的第一行的标题。 这时,动态图表就做好了。 003 函数公式法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相同。 004 函数公式法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到了吗?欢迎留言告诉我,你还有什么别的操作方法? |
|