excel数据分析技巧：数据分布地图的制作过程

编按：哈喽，大家好！看完昨天用VBA制作的全国疫情地图的文章，相信不少同学都被震撼到了，一边感叹excel的强大，一边又觉得VBA门槛高，难学！今天我们就教大家一个相对简单的制作疫情地图的方法，赶紧来看看吧！

【前言】

上一篇关于“新冠肺炎”动态地图色阶图的文章，很多同学都觉得“门槛”有点高。当然，VBA作为EXCEL使用门槛较高的技能，制作的内容也会有一个较好的展示。那今天我们就来学习一些可以“摸得到”的技术吧。

特别声明：本次数据系网络手动摘录，因数据条近万行，故有可能“数据内容”、“GPS坐标”等数值会有出入，欢迎告知。本文只做EXCEL“三维图表”方面技术分享与交流，数据真实性仍以“国家官方网站”为准！

【正文】

首先我们依然是需要数据源的（本文沿用上一期的数据源，可以通过部落窝群找客服老师索要），在工作中，也是如此。我们这些EXCELER操作的是EXCEL，操作的是数据；手里没有数据谈何“技巧”的发挥，而在作者的认知中，一直觉得，数据源整理也应该算是学习EXCEL的基础之一。

一、创建第一张地图图表

在作者看来，制作“三维地图”的方法习惯和我们平时做常规图表的感觉还是不太一样的。我们先一起来做一张图表感受一下效果。

步骤1：首先确定我们的第一张地图要做什么，选中一个主题，我们还是以“各省累计确诊病例”开始循序渐进。在刚才的《源数据》表中，插入一个新的SHEET，命名为“各省累计确诊病例”。复制出“省份”一列，粘贴到《各省累计确诊病例》工作表中，然后“去重”，再使用SUMIFS函数汇总出“截止到统计日期，各省最后一次公布的累计确诊人数”，得到下表：本文主旨为“三维图表”，故函数解析部分从简。

B3单元格函数：

=SUMIFS(源数据!D:D,源数据!A:A,"="&源数据!$G$2,源数据!B:B,A3)

然后双击填充柄填充函数到相应区域。

步骤2：选中数据区域A2:B36，在工具栏中选择“插入”——“三维地图”：

如果是第一次使用“三维地图”，有可能会提示安装模块，按照步骤操作即可，一般来说EXCEL2016版是自带此模块的。点选之后，我们就可以进入“三维地图”界面了，如下图：三维地图操作界面布局图

这个界面的内容非常简约，大部分的操作都是在图层设置窗口中操作，而且比常规图表的格式设置要简单得多！

步骤3：按照下图内容，设置图层一：

步骤4：步骤4是……，没了，是的，已经做完了，就是这么简单，而且鼠标悬停在图表色块上，还可以显示数据内容。

二、向已有的“三维地图”添加数据

相对于每日增长的数据，作者也在关注病情治疗的情况，那我们就把治愈率也放入这个“三维地图图表”中吧！

步骤1：新建一张SHEET，命名为“治愈率”，使用函数手段制作数据源，如下：

C3单元格函数为：

=SUMIFS(源数据!F:F,源数据!A:A,"="&源数据!$G$2,源数据!B:B,A3)

D3单元格函数为：

=IFERROR(ROUND(C3/B3*100,2),0)

（注意治愈率计算公式只做教程使用，数据真实性仍以“国家官方网站”为准！）

步骤2：选中数据区域A2:D36，还是点击工具栏中“三维图表”按钮，但此时我们需点击“将选定数据添加到三维地图”的选项，如下：

因为我们只有一个“演示”，所以会自动跳转到“演示”中，如果我们有多个“演示”，那么会有一个新的窗口，可以选择添加到指定的“演示”中。

步骤3：当我们再次来到“三维图表”界面后，就会发现，此时的图层设置窗口中，当前的图层叫做“图层2”，这个功能和PPT中的感觉很像，我们可以对图层的“显示/隐藏”、“名称”进行操作，还可以“删掉”这个图层。

将刚才的“图层1”命名“各省累计确诊病例”，将现在的“图层2”更名为“治愈率%”。然后按照下图的内容设置图层2。

藉此“添加”新数据的操作完成！

三、任何图表都是为“数据分析”做服务

这样的一张地图，看着是很“炫酷”，但也仅是“耍酷”而已。任何形式的“图表”都应该是为数据分析做服务的！

我国幅员辽阔，地大物博。但是人口密度、行政区域划分不尽相同，而且差异还比较大。此次疫情的数据，也是有着这样的特点，因为疫情波及我国大面积省份，但是只用“省份板块”来处理图表并不太合适，所以我们要细化数据，看看“三维地图”能不能做出更加细化的图表，我们准备用“城市”做出此次疫情的分布图。

步骤1：依然需要准备数据源，新建工作表“各城市累计确诊病例”，如下图：

B3单元格函数如下：

=SUMIFS(源数据!D:D,源数据!A:A,"="& 源数据!$G$2,源数据!C:C,A3)

C3单元格函数如下：

=SUMIFS(源数据!F:F,源数据!A:A,"="& 源数据!$G$2,源数据!C:C,A3)

D列和E列是城市的GPS定位坐标的经纬度。建议喜欢“地图图表”的同学们，平时养成搜集“定位坐标”的习惯，此坐标系“百度地图”一个一个城市搜索来的。

对于地图模型来说，如果单纯地用文字表述来做出“区域图”或者“柱形图”，有的时候地图是识别不出来的，比如较偏僻的城市，或者城市名称不合规，都会造成无法识别，这个叫做“地图可信度”，所以我们可以采用更加精确的“GPS定位坐标”来做这个“地图图表”！

步骤2：选中数据区域A2:E337，按照上面“添加数据到已有演示”中的方法，继续添加图层3，更名为“城市累计确诊病例/治愈病例对比”，按照下图设置此图层，如图：

这里虽然默认叫做气泡图，其实更像是一个“饼图”，因为可以添加多系列数值进来，藉此完成！

【发散思考】

因为武汉的数据是一个相对很大的数字，对于这种数据差异很大的情况，上图中湖北的数据影响了其他省市的数据展示，所以我们可以再建立一个图层，将两组数据的气泡图，分别命名为“非湖北”和“湖北”，还是按照上面的操作，我们可以得到下图，有兴趣的同学可以自己下来操作一下。祝愿：图表中代表治愈的蓝色能早日充斥到整个中国被疫情波及的地域。

四、“高亮显示”关键信息

最后我们再来看看，如何让“三维图表”也能够像“常规图表”一样，能把一些比较重要的信息，“高亮显示”出来。所谓“高亮显示”就是在一组数据图表中，如果达到某个标准，就可以自动的更改颜色，起到提示的作用！

步骤1：在数据表中制作数据源的辅助数据：

G2单元格输入函数：

=MAX(A:A)

表示提取最后的发布日期。

H2单元格输入函数：

=MAX(A:A)-MIN(A:A)+1

表示提取每个城市连续统计的天数，记得日期相减要加1，这是常识。

I2单元格输入一个常数：按照标准7-14天，7-14之间的数字，任意填一个

G4单元格输入函数：

=IF(C4C3,D4,D4-D3)

用于统计每日新增量。

H4单元格输入函数：

{=IF(A4$G$2,0,LOOKUP(9^9,N(FREQUENCY(IF(OFFSET(G4,0,0,-$H$2,1)=0,ROW(INDIRECT("$4:$" & $H$2+3))),IF(OFFSET(G4,0,0,-$H$2,1)0,ROW(INDIRECT("$4:$" & $H$2+3)))))))}

用于统计截止2月24日，最后一次每日新增量连续为0的次数。这是一个经典用法，但这个函数不太好理解。红色的部分是这个函数的关键，大家可以在“部落窝”搜索一下关于FREQUENCY函数的用法，以后作者E图表述也会讲到这个函数。

I4单元格输入函数：

=IF(A4$G$2,0,IF(H4>=$I$2,$I$2,0))

若连续0增长病例的天数达到I2单元格“标尺”的标准，即显示I2标尺的值，此作为我们需要高亮显示的关键信息。

步骤2：建立新工作表，命名为“胜利的颜色”。按如下操作设置数据值。

B2单元格输入函数：

=SUMIFS(源数据!I:I,源数据!A:A,"="&源数据!$G$2,源数据!C:C,A3)

步骤3：

选中数据区域A2:B337，添加到“三维图表”中，按下图设置。

绿色的部分就是代表已经连续7天或者7天以上0增加病例。藉此完成，从图表上来说，现在疫情是向着利好的方向发展的。

【编后语】

作为“地图类型”的图表，作者给了VBA的方式，也给了大家比较简单的“三维地图”的操作。虽然总感觉没有VBA版的地图图表做得赏心悦目，但是在做的过程中，作者感觉“三维地图”的操作比较简单。虽然在显示标签内容的时候还是有所欠缺的，但是相信微软不会留下这样的一个BUG给我们，所以它的可研究内容还有很多，大家一起努力吧。

＊＊＊＊部落窝教育－excel数据地图制作方法＊＊＊＊

原创：E图表述/部落窝教育（未经同意，请勿转载）

更多教程：部落窝教育（http://www.itblw.com）

更多课程添加老师微信:blwjymx2领取

微信公众号：exceljiaocheng