編按：

除湖北外，全國(guó)新冠肺炎新增病例已經(jīng)降到個(gè)位數(shù)增長(zhǎng)了，國(guó)家也鼓勵(lì)我們有序復(fù)工。但是打開手機(jī)看到的還是觸目驚心的累計(jì)確診圖、現(xiàn)有確診圖，并且數(shù)據(jù)仍然在緩慢的上漲。那我們出門安全嗎？什么時(shí)候才能不戴口罩出門呢？

經(jīng)過(guò)作者和小編的努力，我們得到了全國(guó)地級(jí)城市的“疫情綠區(qū)圖”或者說(shuō)“疫情消退圖”。在這些圖上，我們可以一眼看出自己所在地或者要去的地方已經(jīng)多久沒(méi)有新增病例了。

通過(guò)這張圖，我們發(fā)現(xiàn)，疫情正在從讓人擔(dān)心的紅色、橙色減退為安全的綠色。全國(guó)絕大部分地區(qū)都已經(jīng)變成不同程度的綠色，連續(xù)14天及以上沒(méi)有新增病歷的深綠區(qū)域也已經(jīng)很多了。

我們放大顯示了部分省份的疫情綠區(qū)圖，為大家的安全出行提供一份參考。

下面我們來(lái)看看是怎么得到這些綠區(qū)圖的，并且分析何時(shí)才能像以前一樣不戴口罩就可以出門。

【前言】

考慮了很久，還是決定寫下這篇文章，相對(duì)于EXCEL教程類的文章來(lái)說(shuō)，無(wú)論是函數(shù)型、總結(jié)型，甚至是VBA教程、BI教程，我們都可以寫出很多的內(nèi)容，可是今天我要分享的教程卻是“相當(dāng)麻煩但是又寫不出多少內(nèi)容”的文章。

“數(shù)據(jù)地圖”是很多行業(yè)領(lǐng)域都需要的，但是要把它做好卻并不容易，難度系數(shù)：中，復(fù)雜系數(shù)：高！在全國(guó)一盤棋的抗疫戰(zhàn)略中，我們部落窩總覺(jué)得應(yīng)該做點(diǎn)什么來(lái)表示我們也是這其中的一份子，所以作者E圖表述還是將這個(gè)圖做了出來(lái)，希望用我們EXCELER的特有方式來(lái)為這次“戰(zhàn)疫”奉獻(xiàn)我們特有的力量。

【正文】

一、疫情綠區(qū)圖創(chuàng)建

在VBA中，SHAPE是圖形，數(shù)據(jù)地圖就是利用對(duì)于自選圖形的屬性編輯，達(dá)到我們需要的效果，首先我們要有一份可編輯的中國(guó)各省地級(jí)城市的地圖矢量圖，這個(gè)大家可以加入部落窩的大家庭，向老師索取。

STEP 1：處理圖形

這個(gè)地圖已經(jīng)被作者處理過(guò)了，主要有兩方面的處理：

1.給每一個(gè)圖形添加名稱。

選中圖形，對(duì)照地圖，在名稱框中輸入城市或者區(qū)域的名稱。

2.定位所有對(duì)象，調(diào)整圖形標(biāo)簽的格式。

按鍵盤上的F5鍵，彈出“定位”窗口，依次點(diǎn)擊“定位條件——對(duì)象——確定。”

在選中所有圖形后，按照下面的格式調(diào)整圖形樣式。

圖形的設(shè)置就到這里了，我們可以得到下面的圖，接下來(lái)我們?cè)賹?duì)數(shù)據(jù)進(jìn)行處理。

STEP 2：處理數(shù)據(jù)

VBA雖然強(qiáng)大，但是我們沒(méi)有必要將所有的東西都用VBA來(lái)處理，所以我們把數(shù)據(jù)處理的部分交給了“函數(shù)”。

首先我們依然是需要數(shù)據(jù)源的，在工作中，也是如此。我們這些EXCELER操作的是EXCEL，操作的是數(shù)據(jù)；手里沒(méi)有數(shù)據(jù)談何“技巧”的發(fā)揮，而在作者的認(rèn)知中，一直覺(jué)得，數(shù)據(jù)源整理也應(yīng)該算是學(xué)習(xí)EXCEL的基礎(chǔ)之一。

數(shù)據(jù)來(lái)源：今日頭條“抗擊肺炎”專題版塊

然后按下面的結(jié)構(gòu)處理數(shù)據(jù)，便于我們代碼的引用。

G2單元格輸入函數(shù)：

=MAX(A:A)

H2單元格輸入函數(shù)：

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

G4單元格輸入函數(shù)，并下拉填充：

=IF(C4<>C3,D4,D4-D3)

H4單元格輸入函數(shù)，并下拉填充：

{=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)))))))}

H4單元格的函數(shù)，用于統(tǒng)計(jì)截止2月24日，最后一次每日新增量連續(xù)為0的次數(shù)。當(dāng)然，這不是今天的重點(diǎn)，大家可以先使用“拿來(lái)主義”，以后我們肯定會(huì)講這個(gè)內(nèi)容。

處理完數(shù)據(jù)，我們?cè)俳⒁粡埧瞻坠ぷ鞅?，?span>A1單元格輸入：城市名稱，B1單元格輸入：數(shù)據(jù)。講到這里，同學(xué)們知道我們一共有幾張工作表嗎？一共四張，如下命名：

STEP 3：輸入VBA代碼

按ALT+F11組合鍵，打開VBE界面，錄入第一段代碼，工程名稱：填充圖形顏色

Sub 填充圖形顏色()

  Dim i As Integer, a As String

  Dim rg As Range

  On Error Resume Next

  With ActiveSheet

    a = .[C1]

    For Each rg In .Range("B2:B" & .[B65000].End(3).Row) '在《圖表數(shù)據(jù)》B列中循環(huán)每一個(gè)單元格

      i = Application.Match(rg.Value, [C:C], 1) '確定每個(gè)值，在某個(gè)區(qū)間

      ActiveSheet.Shapes(rg.Offset(0, -1).Value).Fill.ForeColor.RGB = Cells(i, "A").Interior.Color '按照區(qū)間對(duì)應(yīng)的色階，填充圖形顏色

      ActiveSheet.Shapes(rg.Offset(0, -1).Value).TextFrame2.TextRange.Characters.Text = rg.Offset(0, -1).Value & Chr(10) & rg.Value & a '給圖形的標(biāo)簽賦值為 “城市名稱+數(shù)值+單位”的形式

    Next rg

  End With

End Sub

因?yàn)槲覀冏龅氖悄０?，所以同學(xué)們可以直接使用數(shù)據(jù)源，不需要更改代碼。如有想學(xué)習(xí)代碼的同學(xué)，可以參考作者為代碼添加的批注說(shuō)明。

因?yàn)樽髡咭獙⒏鞣N數(shù)據(jù)統(tǒng)計(jì)在一張地圖中標(biāo)記，所以我們還要做4段代碼，分別是“累計(jì)確診病例”、“現(xiàn)有確診病例”、“今日新增病例”、“連續(xù)零增加病例”。代碼的結(jié)構(gòu)都是一樣的，我們用其中的“連續(xù)零增加病例”代碼列出范例如下。

Sub 連續(xù)0增長(zhǎng)病例()

   With Sheets("數(shù)據(jù)分析圖")

    .[A26].Interior.Color = RGB(249, 83, 77) '以下6行代碼，是設(shè)置色階的RGB代碼

    .[A27].Interior.Color = RGB(197, 208, 112)

    .[A28].Interior.Color = RGB(165, 199, 112)

    .[A29].Interior.Color = RGB(119, 185, 113)

    .[A30].Interior.Color = RGB(76, 172, 113)

    .[A31].Interior.Color = RGB(10, 154, 114)

    .[B26] = "0-2天無(wú)新增" '以下6行代碼，是區(qū)間值的說(shuō)明

    .[B27] = "3-4天無(wú)新增"

    .[B28] = "5-6天無(wú)新增"

    .[B29] = "7-9天無(wú)新增"

    .[B30] = "10-13天無(wú)新增"

    .[B31] = "≥14天無(wú)新增"

    .[C26] = 0 '以下6行代碼，是確定色階的輔助列，工作表中改成白色

    .[C27] = 3

    .[C28] = 5

    .[C29] = 7

    .[C30] = 10

    .[C31] = 14

  End With

  Dim a As Integer, i As Integer

  Dim arr, d

  With Sheets("源數(shù)據(jù)")

    a = .Range("A3").End(4).Row '確定源數(shù)據(jù)的末行,并賦值給變量a

    arr = .Range("A4:I" & a) '將動(dòng)態(tài)數(shù)據(jù)區(qū)域，賦值給數(shù)組

  End With

  Set d = CreateObject("scripting.dictionary") '建立字典腳本

  For i = 1 To UBound(arr) '循環(huán)數(shù)組

    d(arr(i, 3)) = arr(i, 8) '將最后一次的數(shù)值賦值給字典

  Next i

  Sheets("數(shù)據(jù)分析圖").[C1] = "地級(jí)城市：連續(xù)“零增加”病例天數(shù)分布圖" '確定圖表標(biāo)題

  With Sheets("圖表數(shù)據(jù)")

    .Range("A2").Resize(d.Count, 1) = Application.Transpose(d.keys) '將字典的關(guān)鍵字賦值到《圖表數(shù)據(jù)》的A列

    .Range("B2").Resize(d.Count, 1) = Application.Transpose(d.items) '將字典的項(xiàng)賦值到《圖表數(shù)據(jù)》的B列

    .Range("C1") = "天" '標(biāo)注單位

  End With

  Call 填充圖形顏色 '引用填充圖形顏色的工程

  [A1].Select '定位最后的光標(biāo)

  Erase arr

  Set d = Nothing

End Sub

關(guān)于其他三段代碼，大家可以嘗試著自己操作，當(dāng)然也可以加入部落窩的學(xué)習(xí)QQ群，下載素材學(xué)習(xí)。

STEP 4：添加控件并加載宏

首先我們添加控件，依次點(diǎn)擊“工具欄——開發(fā)工具——插入——選項(xiàng)按鈕?！?/span>

鼠標(biāo)右鍵點(diǎn)擊控件，點(diǎn)擊“編輯文字”，依次修改控件的標(biāo)簽。

看到上圖中“指定宏”的選項(xiàng)了嗎，點(diǎn)擊它就可以加載我們剛才寫的代碼了。

這些就是我們做的“工程名稱”，選擇對(duì)應(yīng)的名稱再點(diǎn)擊確定，就可以將代碼加載到控件上，點(diǎn)擊控件的過(guò)程也就是激活此工程代碼的過(guò)程。藉此完成。

STEP 5：衍生出各省地圖

我們已經(jīng)做出了大部分的地圖，那么如果能從這個(gè)全國(guó)地圖中，再看到各省的地級(jí)城市地圖不是更加方便？下面我們就來(lái)看一下如何“根據(jù)全國(guó)地圖制作各省地圖”，以四川為例：

步驟1：

在《行政區(qū)域圖》中按住CTRL鍵復(fù)選你要的省份的城市拼圖，復(fù)制到一個(gè)新的工作表，將工作表名稱命名為某省。

步驟2：

在《數(shù)據(jù)分析圖》中復(fù)制出“色階區(qū)間區(qū)域A25:B31”、“標(biāo)題”、“選擇控件”，并將其一并粘貼到《四川省》工作表中，形成下圖的布局。

因?yàn)槲覀兪菑?fù)制過(guò)來(lái)的，所以控件上依然有代碼加載，可以直接操作，無(wú)需再重新加載。

步驟3：

在VBE代碼中，錄入下面的代碼：

Sub 四川()

  Sheets("四川省").Select

End Sub

步驟4：

再次回到《數(shù)據(jù)分析圖》工作表，復(fù)選四川省的各城市拼圖，單擊鼠標(biāo)右鍵，在彈出的菜單中選擇“指定宏”，選擇“四川”，點(diǎn)擊“確定”。此時(shí)，我們?cè)冱c(diǎn)擊這些拼圖的時(shí)候，就可以鏈接到《四川》這個(gè)工作表中了。

步驟5：

按照上面的操作，依次制作出《武漢》、《廣東》兩個(gè)工作表，然后按住CTRL鍵，復(fù)選《武漢》、《四川》、《廣東》三個(gè)工作表，在A1單元格中輸入“返回全國(guó)圖”，單擊鼠標(biāo)右鍵，在菜單中選擇“超鏈接”選擇項(xiàng)，設(shè)置鏈接到《數(shù)據(jù)分析圖》工作表，藉此我們整體的一個(gè)地圖就完成了，有興趣的同學(xué)可以自己制作自己省份的地圖。

下面給大家看幾張展示圖，記得一定要親手操作一遍。

從圖表上來(lái)看，疫情的防控工作，我們大中國(guó)做的真的很不錯(cuò)，1個(gè)月的時(shí)間就已經(jīng)控制到這樣的一個(gè)程度，說(shuō)明我們的做法是正確的，身為一位中國(guó)EXCELER，我驕傲?。?/span>

【編后語(yǔ)】

二、何時(shí)可以不戴口罩了？

我們已經(jīng)得到了全國(guó)和各省份的疫情綠區(qū)圖，這些數(shù)據(jù)顯示絕大多數(shù)地區(qū)的疫情已經(jīng)被打敗，正在消退，正在遠(yuǎn)離，超過(guò)7天、14天，甚至21天的連續(xù)零增加病歷的地區(qū)越來(lái)越多。

那何時(shí)我們可以不戴口罩出門，可以在公交、地鐵上自由呼吸呢？

1.中位數(shù)分析

在我們整理的Excel數(shù)據(jù)文件中，有一張工作表“圖表數(shù)據(jù)”。這里面統(tǒng)計(jì)的是全國(guó)各地級(jí)市截止2月24日連續(xù)零增加病例的天數(shù)。

我們復(fù)制數(shù)據(jù)列，并去掉湖北的數(shù)據(jù)，然后進(jìn)行升序排列，得到321個(gè)數(shù)據(jù)。這些數(shù)據(jù)中，最小的是0，有11個(gè)，最大的是32，有30個(gè)。數(shù)列的中位數(shù)為10。如果我們保守些，按照連續(xù)24天（當(dāng)前最長(zhǎng)潛伏期）零增加就視為疫情結(jié)束的話，需要14天，也就是3月10日后，除湖北外，全國(guó)絕大多數(shù)地區(qū)將進(jìn)入深綠。

2.現(xiàn)存確診的走勢(shì)分析

通過(guò)百度APP我們能查到非湖北現(xiàn)存確診人數(shù)圖。

這是一條看起來(lái)比較光滑的接近拋物線的圖。我們可以在這張圖上進(jìn)行趨勢(shì)推測(cè)，現(xiàn)存確診歸零大約在3月16日前后。

3.當(dāng)前治愈數(shù)據(jù)的分析

我們統(tǒng)計(jì)了全國(guó)、湖北、非湖北的每日新增治愈。

很顯然，湖北外新增治愈在2月20日達(dá)到最高后開始降低。這個(gè)態(tài)勢(shì)是從低到高再到低的走勢(shì)。截止到2月25日，非湖北共治愈8830人，每日平均治愈人數(shù)約為245。全國(guó)非湖北現(xiàn)存確診人數(shù)是4037，當(dāng)前每日新增是個(gè)位數(shù)，可以不考慮，全部治愈（非湖北死亡很低，所以忽略死亡）需要約16天，也就是到3月12日前后。

4.結(jié)論

綜合前面3項(xiàng)分析，我們預(yù)測(cè)到3月中旬，全國(guó)湖北外的地方疫情就結(jié)束了，我們就可以摘掉口罩，自在出門了。當(dāng)然，如果謹(jǐn)慎的話，需要等到湖北結(jié)束，時(shí)間還需要延后10~20天，也就是3月底或4月中旬，我們才能摘掉口罩。

當(dāng)然所有的預(yù)測(cè)都是建立在我們繼續(xù)堅(jiān)持“戴口罩、勤洗手、少聚集”的做法上，所以，在疫情結(jié)束前請(qǐng)大家繼續(xù)堅(jiān)持！

再次聲明：本文只做EXCEL技術(shù)交流與分享，對(duì)于數(shù)據(jù)內(nèi)容正誤，請(qǐng)以國(guó)家官方發(fā)布信息為準(zhǔn)。

本文配套的練習(xí)課件請(qǐng)加入QQ群：747953401下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

疫情動(dòng)態(tài)圖《新冠肺炎最新走勢(shì)情況，一張excel動(dòng)態(tài)圖帶你看清！（截止2020-02-11）》

疫情動(dòng)態(tài)組合圖《新冠肺炎：“累計(jì)確診病例”動(dòng)態(tài)excel組合圖》

用excel制作波浪圖《疫情過(guò)后最想做的10件事是啥？可愛(ài)的excel波浪圖給你答案！》

使用切片器制作動(dòng)態(tài)圖《光漲肉價(jià)，不漲工資？用excel做張老板最愛(ài)的自動(dòng)化表格，讓你的工資翻一番！》