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

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

?

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

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

?

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

?

【前言】?

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

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

?

【正文】

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

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

?

STEP 1：處理圖形?

?

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

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

?

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

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

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

?

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

?

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

?

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

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

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

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

?

然后按下面的結(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)，大家可以先使用“拿來主義”，以后我們肯定會(huì)講這個(gè)內(nèi)容。?

處理完數(shù)據(jù)，我們再建立一張空白工作表，在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ū)間對應(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é)，可以參考作者為代碼添加的批注說明。?

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

?

Sub?連續(xù)0增長病例()

?? 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天無新增"?'以下6行代碼，是區(qū)間值的說明

??? .[B27] = "3-4天無新增"

??? .[B28] = "5-6天無新增"

??? .[B29] = "7-9天無新增"

??? .[B30] = "10-13天無新增"

??? .[B31] = "≥14天無新增"

???

??? .[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] = "地級城市：連續(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)擊它就可以加載我們剛才寫的代碼了。?

?

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

?

STEP 5：衍生出各省地圖?

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

?

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

?

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

?

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

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

Sub?四川()

? Sheets("四川省").Select

End Sub

?

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

?

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

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

?

?

?

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

?

【編后語】

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

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

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

?

1.中位數(shù)分析?

在我們整理的Excel數(shù)據(jù)文件中，有一張工作表“圖表數(shù)據(jù)”。這里面統(tǒng)計(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)前最長潛伏期）零增加就視為疫情結(jié)束的話，需要14天，也就是3月10日后，除湖北外，全國絕大多數(shù)地區(qū)將進(jìn)入深綠。

?

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

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

?

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

?

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

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

?

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

?

4.結(jié)論?

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

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

???最后，我們看看截止到2月26日數(shù)據(jù)，湖北各地已經(jīng)有8個(gè)地級市沒有出現(xiàn)新增病歷了！

?

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

＊＊＊＊部落窩教育－excelVBA應(yīng)用技巧＊＊＊＊

原創(chuàng)：E圖表述/部落窩教育（未經(jīng)同意，請勿轉(zhuǎn)載）

更多教程：部落窩教育（www.itblw.com）

微信公眾號：exceljiaocheng