一、条件格式
步骤:开始-条件格式-色阶(或数据条)
二、迷你图
步骤:选择空白单元格-插入-迷你图
三、三维地图
根据以下数据绘制三维地图:
省份 | 人口 |
云南省 | 9029147 |
湖北省 | 8243692 |
天津市 | 7886861 |
内蒙古自治区 | 7605696 |
四川省 | 5909644 |
广东省 | 4411450 |
安徽省 | 4131016 |
福建省 | 3091139 |
江西省 | 2174959 |
北京市 | 1092389 |
河北省 | 645784 |
广西壮族自治区 | 183737 |
步骤:插入-打开三维地图,可通过选择相应的属性进行展示,下图反应的是根据颜色的深浅展示各省的人口差异。
四、REPT函数
case1:制作条形图
步骤:
-
在C7单元格中输入函数=REPT("|",B7),表示把“|”重复B7中单元格数量的次数。
-
此处计算结果太长,我们把B7除以5,公式变成=REPT("|",B7/5),然后把字体改为playbill,选择左对齐,字体颜色选择红色即可。
注意:我们在利用rept函数制作图表的时候,需要使用playbil、Stencil、Britannic Bold这三种字体。这三种字体中playbil最短,Britannic Bold最长。
我们也可以给条形图加上数据标签,只要输入的公式变成:=REPT("|",B7/5)&REPT(" ",1)&B7,效果如下:
case2:制作星级图
步骤:
-
首先,我们在插入选项卡中选择最右边的符号,字体选择"仿宋"字体,然后从最下面往前找,找到五角星符号。
-
然后复制五角星,在C7单元格中输入函数=REPT("",B7),第一个参数粘贴刚才复制的五角星即可,但是发现粘贴过去就不是五角星了,所以我们要在字体中选择"仿宋"字体。
-
对于五角星太长的问题,我们把第二个参数除以50,公式变成=REPT("",B7/50),往下拖动,就完成了五角星图形的制作,表示销售额每满50万记一颗五角星。
同样的,我们也可以制作如下的星级评选图,只要改一下公式:=REPT("★",B19)&REPT("☆",MAX($B$19:$B$23)-B19),效果如下:
case3:制作柱形图
步骤:
- 在B18单元格中输入函数=REPT("|",B20/5),同样,这里第二个参数除以5是因为柱子太长,调整柱子为合适的长度。字体选择playbill。
- 然后ctrl+1打开单元格格式设置框,在对齐中方向填90度,最后居中显示,字体颜色选择红色即可。调整行高就可以看到对比效果了。
case4:制作甘特图
步骤:
-
直接输入公式“=REPT(" ",B31-MIN($B$31:$B$35))&REPT("|",(C31-B31)*2)",字体选择playbill。公式中“MIN($B$31:$B$35)”将得到“开始日期”中最小的一个日期,用B31减去这个日期,可以得到两个时间差。这样我们对这部分时间差填充空白,紧接用“|”填充这个项目持续时间(C31-B31)。这样得到的结果是甘特图太小了,所以将“C31-B31”放大一倍。
case5:制作盈亏图
步骤:
- 在亏损列输入:=IF(B52<0,REPT("|",ABS(B52)*3),""),在盈利列输入:=IF(B52>=0,REPT("|",ABS(B52)*3),"")。
- 把字体改为playbill,亏损列向右对齐,盈利列向左对齐。
case6:制作蝴蝶图
步骤:
- 在赞成列输入:=B70*100&"%"&" "&REPT("|",B70*100),在反对列输入:=REPT("|",C70*100)&" "&C70*100&"%"。
- 把字体改为playbill,赞成列向右对齐,反对列向左对齐。
case7:制作漏斗图&堆积图
步骤:
- 在case1条形图的基础之上,条形图居中对齐。
- 漏斗图:降序,效果如下:
- 堆积图:升序,效果如下:
五、人形图
case1:人形条形图
步骤:
- 选中需要处理的数据,依次点击【插入】-【推荐的图表】-【条形图】,选择第一种纯色的条形图
- 点击【插入】-【联机图片】搜索“人”形素材
- 选中后点击【插入】,得到所需素材,复制4个人形素材
- 选择【格式】中的【颜色】,对素材进行颜色调整,再对大小进行调整
- 选择一个人形素材进行复制,然后选中一条条形图进行粘贴,重复这个步骤,完成其余的人形素材粘贴
- 选中一条条形图,点击【右键】-【设置数据点格式】-【填充】-【层叠】,之后点击【系列选项】-【分类间距】对大小进行调整,对其他条形图进行上一步操作,得到所需【人形图表】
case2:人形百分比图
步骤:
- 先需要增加辅助列(100%),用这组数据来插入一个普通的柱形图。在“设计”下点击下“切换行/列”,使图表分为两个数据系列。
- 点击【插入】-【联机图片】搜索“人”形素材,选中后点击【插入】,得到所需素材,复制2个人形素材。
- 选择【格式】中的【颜色】,对素材进行颜色调整,再对大小进行调整,将深色的人形图粘贴在代表真实值的柱子上,将另一个粘贴在代表辅助值的柱子上。
- 然后在右侧数据系列格式中分别将两个柱子设置为“层叠并缩放”,再将系列重叠设置为100%,再在“设计-选择数据"中点击下移的小三角。
- 最后我们为此图表加上真实值的数据标签。
六、动态图表
case1:randbetween函数
步骤:
- 在单元格中键入公式:=RANDBETWEEN(1,10),并拖拽。
- 选择数据,插入-迷你图。
- 迷你图和数据每次按F9(刷新)都会变化。
case2:函数+下拉菜单
步骤:
-
选择任意空白单元格,数据-数据验证-允许(序列)-来源(选中A10:A13)。
-
编写公式:=SUMIF($A$10:$A$13,$F$9,B10:B13),并拖拽。
-
选择下拉框和公式输出的数据,选择柱形图。
case3:函数+控件
步骤:
-
随便在A23中写一个数,点击开发工具-插入-滚动条(窗体控件)。
-
右击控件-设置控件格式-最小值(100)、最大值(10000)、单元格链接(A23)。
-
选择数据区域-插入条形图。
case4:透视图+切片器或日程表
步骤:
- 插入-透视图-轴(城市)-值(2018、2019、2020)
- 分析-插入切片器-选择“城市”