让霸总爱上你的秘诀–简单的Excel动效图表(二)

276次阅读
没有评论

上回说到,我们通过复选框、IF函数、二维柱形图成功展示了某应用在各应用商城中的下载情况。

这次我们用图表展示一些产品质量监督检查批次合格率。

让霸总爱上你的秘诀–简单的Excel动效图表(二)

下图为各产品在2021-2017年的质量监督检查批次合格率。

让霸总爱上你的秘诀–简单的Excel动效图表(二)

选择任意单元格,在功能区中找到数据→数据工具→数据验证,验证条件选择允许序列,来源填入=$B$2:$J$2,如下图。

让霸总爱上你的秘诀–简单的Excel动效图表(二)

填写完毕点击确定后,此单元格成为了一个下拉菜单。

让霸总爱上你的秘诀–简单的Excel动效图表(二)

在产品质量表上方新建一个表,具体如下。

让霸总爱上你的秘诀–简单的Excel动效图表(二)

B2列是效果图中深色的条形区域,在B2单元格内输入公式:

=HLOOKUP(G3,$11:$18,ROW(A2),0)

① ROW(reference)

Reference为需要得到其行号的单元格或单元格区域。

ROW(A2)是指引用单元格A2的行号2。

② HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

hlookup是不是看着很眼熟?

我们直接接触过的vlookup是指竖向查找函数,而hlookup是指横向查找函数,二者的语法是相同的。

故B2单元格内的公式含义为:在11-18行内查找下拉菜单中显示的值,返回第n行的值。

在C2单元格内输入公式:

=1-B2

C2列是效果图中浅色的条形区域。

让霸总爱上你的秘诀–简单的Excel动效图表(二)

接下来填充D列与E列的数据,这两列构成了效果图中的圆圈。

x轴数据=合格率,y轴数据=0.5、1.5、2.5…以此类推,填充好的表如下图。

让霸总爱上你的秘诀–简单的Excel动效图表(二)

选择A1:C8区域,在功能区中选择插入→图表→二维条形图→堆积条形图。

让霸总爱上你的秘诀–简单的Excel动效图表(二)

插入后图表效果如下图。

让霸总爱上你的秘诀–简单的Excel动效图表(二)

删除图表中的图例,将图表中的横坐标轴边界最大值设为1。

让霸总爱上你的秘诀–简单的Excel动效图表(二)

右键点击图表,选择 选择数据→选择数据源→添加,输入任意系列名称,图中输入的系列名称为name,点击确定。

让霸总爱上你的秘诀–简单的Excel动效图表(二)

右键点击条形图,选择 更改系列图表类型,在底部找到系列名称name,将其图表类型改为散点图。

让霸总爱上你的秘诀–简单的Excel动效图表(二)

再次右击图表,点击 选择数据→name→编辑,在编辑数据系列对话框中,X轴系列值填入$D$2:$D$8,Y轴系列值填入$E$2:$E$8。

让霸总爱上你的秘诀–简单的Excel动效图表(二)

双击图表中次要纵坐标轴,将边界最大值设置为7,将标签位置设置为无。

让霸总爱上你的秘诀–简单的Excel动效图表(二)
让霸总爱上你的秘诀–简单的Excel动效图表(二)

最后美化图表。

分别点击蓝色、橙色条形区域可更换颜色。

右击条形图→设置数据系列格式→间隙宽度,可更改条形区域宽度。

让霸总爱上你的秘诀–简单的Excel动效图表(二)

右击散点→设置数据序列格式→标记,可以设置标记类型、散点与边框的大小、颜色等。

让霸总爱上你的秘诀–简单的Excel动效图表(二)
让霸总爱上你的秘诀–简单的Excel动效图表(二)

右击三点→添加数据标签。

让霸总爱上你的秘诀–简单的Excel动效图表(二)

右击数据标签→设置数据系列格式→勾选X值,将标签位置设置为居中。

让霸总爱上你的秘诀–简单的Excel动效图表(二)

点击数据标签可以更改字体大小和颜色。

在任意单元格内输入公式:

=L1&”产品质量监督检查批次合格率(%)”

此处L1为下拉菜单的位置,点击图表标题,在编辑栏输入等号,点击刚才输入公示的单元格,回车,生成动态标题。

至此一个动态表格就完成了。

文件下载地址

百度网盘:https://pan.baidu.com/s/1x-3LfuU3jdAQ2uEwrkg3vA

提取码:ht7c

让霸总爱上你的秘诀–简单的Excel动效图表(二)

雨米
版权声明:本站原创文章,由 雨米2022-01-18发表,共计1188字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
评论(没有评论)