办公小技巧:简单易操作 Excel热图轻松做

CFan 电脑爱好者 2020-12-08 10:01应用 标签:热图 技巧 办公

为了直观地比较某一类数据,我们经常以热图的方式进行展示,这样可以更加快速地显示出数据集的比较视图。比如对于老师来说,可以将班上每次考试成绩和本年预测本科、专科录取分数关联形成热图,还可以将每次考试进步最快和退步最多的同学使用热图标注,一目了然地显示出同学们学习成绩的发展趋势。

高考预测成绩一目了然

首先准备班级所有同学两次考试的成绩数据,按自己的需要录入原始数据(图1)。

201914ecrt1

图1 原始数据

假设预测今年的本科和专科分数线为481分和390分,那么选中B2:C17区域,点击“开始→条件格式→大于”,在打开的窗口中输入大于480分的单元格填充为“浅红填充深色文本”,这样所有超过480分的同学成绩都会被填充为浅红色(图2)。继续设置一个条件格式,将介于390~480之间的数据填充为绿色。

201914ecrt2

图2 设置条件格式

这样每次考试完毕填写成绩后,哪位同学可能被本科或者专科录取,通过颜色就能一目了然地看出来了(图3)。

201914ecrt3

图3 成绩热图

学生成绩发展趋势图

我们还可以制作更直观的动态热图,比如需要将每次考试进步最快和退步最多的三位同学标注出来,可以先定位到D2单元格,输入公式“=C2-B2”(不含外侧引号),得出每位同学前后进步(或者退步)的分值,然后从中选择最大三位数和最小三位数,对应的就是每次考试进步最快和退步最多的同学。为了便于查看,可以通过添加控件方法来快速筛选。

点击“开发工具”选项卡,选取“控件→插入→表单控件→复选框(窗体控件)”,在当前工作表中插入一个窗体控件。右击插入的控件选择“编辑文字”,按提示将控件的名字更改为“进步最快三位同学”。同上,继续插入一个复选框控件,并将其名称设置为“退步最大三位同学”(图4)。

201914ecrt4

图4 插入控件

右击“进步最快三位同学”控件选择“设置控件格式”,在打开的设置窗口中切换到“控制”,值选择单元格链接,在其中选择“$H$1”,即H1单元格的数值作为控制复选框选择的条件。默认情况下如果复选框被选择,那么H1的值为TRUE,否则为FALSE(图5)。

201914ecrt5

图5 控件设置

操作同上,选择另一个控件,单元格链接的值选择“$H$2”。接着选中E2:E17数据,点击“开始→条件格式→使用公式确定要设置格式的单元格”,接着在公式框输入“=IF($H$1=TRUE,IF(E2>=LARGE($E$2:$E$17,3),TRUE,FALSE))”(图6)。

公式解释:

这里使用IF嵌套Large函数来筛选E列最大的三个值(即进步最快三个同学),IF函数条件是通过H1的数值进行判断。当复选框的控件被选中时,控件关联的H1数值是True(否则为False),接着通过Large函数在E2:E17中选择最大的三个数值。

201914ecrt6

图6 最大三个值的设置公式

继续点击“格式”,在打开的窗口中选择“填充”,将符合条件的数据单元格填充为蓝色,这样成绩进步最快的三个同学会被蓝色标注。

操作同上,点击“开始→条件格式→使用公式确定要设置格式的单元格”,接着在公式框输入“=IF($H$2=TRUE,IF(E2<=SMALL($E$2:$E$17,3),TRUE,FALSE))”,这里条件判断使用H2单元格数值,最小的三位数则使用SMALL函数进行筛选,将符合其条件的单元格填充为紫色显示(图7)。

201914ecrt7

图7 最小三个值的设置

完成上述的设置后,如果我们需要查看进步最快的三个同学成绩,那么只要勾选“进步最快三位同学”控件的复选框,符合条件的三位同学会被填充为蓝色,从而快速显示出来。因为是复选框控件,我们也可以根据自己的需要对进步、退步同学选择进行热图的动态显示(图8)。

201914ecrt8

图8 动态热图

Excel热图美化

为了让热图的显示更为美观,最后还可以对齐进行美化。首先将单元格的大小调整为72×72像素,并将字体调整为居中显示。选中B2:E17区域,点击“开始→边框→无边框”,将数据区域设置为无边框显示。点击“插入→形状→矩形”,在控件下方插入一个矩形并置于底层,将控件和插入的矩形组合在一起。

最后再为热图的形状颜色添加文字说明,这样最终的热图看起来就更为专业、美观了,当然我们还可以将最终显示的热图导出为图片,放置在PPT中进行展示(图9)。

201914ecrt9

图9 热图美化

入驻平台680