用excel制作全國疫情地圖(三維地圖篇)
?
作者:E圖表述來源:部落窩教育發(fā)布時間:2020-02-28 08:05:27點擊:16324
編按:
哈嘍,大家好!看完昨天用VBA制作的全國疫情地圖的文章,相信不少同學(xué)都被震撼到了,一邊感嘆excel的強大,一邊又覺得VBA門檻高,難學(xué)!今天我們就教大家一個相對簡單的制作疫情地圖的方法,趕緊來看看吧!
【前言】
上一篇關(guān)于“新冠肺炎”動態(tài)地圖色階圖的文章,很多同學(xué)都覺得“門檻”有點高。當(dāng)然,VBA作為EXCEL使用門檻較高的技能,制作的內(nèi)容也會有一個較好的展示。那今天我們就來學(xué)習(xí)一些可以“摸得到”的技術(shù)吧。
特別聲明:本次數(shù)據(jù)系網(wǎng)絡(luò)手動摘錄,因數(shù)據(jù)條近萬行,顧有可能“數(shù)據(jù)內(nèi)容”、“GPS坐標(biāo)”等數(shù)值會有出入,歡迎告知。本文只做EXCEL“三維圖表”方面技術(shù)分享與交流,數(shù)據(jù)真實性仍以“國家官方網(wǎng)站”為準(zhǔn)!
【正文】
首先我們依然是需要數(shù)據(jù)源的(本文沿用上一期的數(shù)據(jù)源,可以通過部落窩群找客服老師索要),在工作中,也是如此。我們這些EXCELER操作的是EXCEL,操作的是數(shù)據(jù);手里沒有數(shù)據(jù)談何“技巧”的發(fā)揮,而在作者的認(rèn)知中,一直覺得,數(shù)據(jù)源整理也應(yīng)該算是學(xué)習(xí)EXCEL的基礎(chǔ)之一。
一、創(chuàng)建第一張地圖圖表
在作者看來,制作“三維地圖”的方法習(xí)慣和我們平時做常規(guī)圖表的感覺還是不太一樣的。我們先一起來做一張圖表感受一下效果。
步驟1:
首先確定我們的第一張地圖要做什么,選中一個主題,我們還是以“各省累計確診病例”開始循序漸進(jìn)。在剛才的《源數(shù)據(jù)》表中,插入一個新的SHEET,命名為“各省累計確診病例”。復(fù)制出“省份”一列,粘貼到《各省累計確診病例》工作表中,然后“去重”,再使用SUMIFS函數(shù)匯總出“截止到統(tǒng)計日期,各省最后一次公布的累計確診人數(shù)”,得到下表:
本文主旨為“三維圖表”,顧函數(shù)解析部分從簡。
B3單元格函數(shù):
=SUMIFS(源數(shù)據(jù)!D:D,源數(shù)據(jù)!A:A,"="&源數(shù)據(jù)!$G$2,源數(shù)據(jù)!B:B,A3)
然后雙擊填充柄填充函數(shù)到相應(yīng)區(qū)域。
步驟2:
選中數(shù)據(jù)區(qū)域A2:B36,在工具欄中選擇“插入”——“三維地圖”:
如果是第一次使用“三維地圖”,有可能會提示安裝模塊,按照步驟操作即可,一般來說EXCEL2016版是自帶此模塊的。點選之后,我們就可以進(jìn)入“三維地圖”界面了,如下圖:
三維地圖操作界面布局圖
這個界面的內(nèi)容非常簡約,大部分的操作都是在圖層設(shè)置窗口中操作,而且比常規(guī)圖表的格式設(shè)置要簡單得多!
步驟3:
按照下圖內(nèi)容,設(shè)置圖層一:
步驟4:
步驟4是……,沒了,是的,已經(jīng)做完了,就是這么簡單,而且鼠標(biāo)懸停在圖表色塊上,還可以顯示數(shù)據(jù)內(nèi)容。
二、向已有的“三維地圖”添加數(shù)據(jù)
相對于每日增長的數(shù)據(jù),作者也在關(guān)注病情治療的情況,那我們就把治愈率也放入這個“三維地圖圖表”中吧!
步驟1:
新建一張SHEET,命名為“治愈率”,使用函數(shù)手段制作數(shù)據(jù)源,如下:
C3單元格函數(shù)為:
=SUMIFS(源數(shù)據(jù)!F:F,源數(shù)據(jù)!A:A,"="&源數(shù)據(jù)!$G$2,源數(shù)據(jù)!B:B,A3)
D3單元格函數(shù)為:
=IFERROR(ROUND(C3/B3*100,2),0)
(注意治愈率計算公式只做教程使用,數(shù)據(jù)真實性仍以“國家官方網(wǎng)站”為準(zhǔn)?。?/span>
步驟2:
選中數(shù)據(jù)區(qū)域A2:D36,還是點擊工具欄中“三維圖表”按鈕,但此時我們需點擊“將選定數(shù)據(jù)添加到三維地圖”的選項,如下:
因為我們只有一個“演示”,所以會自動跳轉(zhuǎn)到“演示”中,如果我們有多個“演示”,那么會有一個新的窗口,可以選擇添加到指定的“演示”中。
步驟3:
當(dāng)我們再次來到“三維圖表”界面后,就會發(fā)現(xiàn),此時的圖層設(shè)置窗口中,當(dāng)前的圖層叫做“圖層2”,這個功能和PPT中的感覺很像,我們可以對圖層的“顯示/隱藏”、“名稱”進(jìn)行操作,還可以“刪掉”這個圖層。
將剛才的“圖層1”命名“各省累計確診病例”,將現(xiàn)在的“圖層2”更名為“治愈率%”。然后按照下圖的內(nèi)容設(shè)置圖層2。
藉此“添加”新數(shù)據(jù)的操作完成!
三、任何圖表都是為“數(shù)據(jù)分析”做服務(wù)
這樣的一張地圖,看著是很“炫酷”,但也僅是“??帷倍?。任何形式的“圖表”都應(yīng)該是為數(shù)據(jù)分析做服務(wù)的!
我國幅員遼闊,地大物博。但是人口密度、行政區(qū)域劃分不盡相同,而且差異還比較大。此次疫情的數(shù)據(jù),也是有著這樣的特點,因為疫情波及我國大面積省份,但是只用“省份板塊”來處理圖表并不太合適,所以我們要細(xì)化數(shù)據(jù),看看“三維地圖”能不能做出更加細(xì)化的圖表,我們準(zhǔn)備用“城市”做出此次疫情的分布圖。
步驟1:
依然需要準(zhǔn)備數(shù)據(jù)源,新建工作表“各城市累計確診病例”,如下圖:
B3單元格函數(shù)如下:
=SUMIFS(源數(shù)據(jù)!D:D,源數(shù)據(jù)!A:A,"="& 源數(shù)據(jù)!$G$2,源數(shù)據(jù)!C:C,A3)
C3單元格函數(shù)如下:
=SUMIFS(源數(shù)據(jù)!F:F,源數(shù)據(jù)!A:A,"="& 源數(shù)據(jù)!$G$2,源數(shù)據(jù)!C:C,A3)
D列和E列是城市的GPS定位坐標(biāo)的經(jīng)緯度。建議喜歡“地圖圖表”的同學(xué)們,平時養(yǎng)成搜集“定位坐標(biāo)”的習(xí)慣,此坐標(biāo)系“百度地圖”一個一個城市搜索來的。
對于地圖模型來說,如果單純地用文字表述來做出“區(qū)域圖”或者“柱形圖”,有的時候地圖是識別不出來的,比如較偏僻的城市,或者城市名稱不合規(guī),都會造成無法識別,這個叫做“地圖可信度”,所以我們可以采用更加精確的“GPS定位坐標(biāo)”來做這個“地圖圖表”!
步驟2:
選中數(shù)據(jù)區(qū)域A2:E337,按照上面“添加數(shù)據(jù)到已有演示”中的方法,繼續(xù)添加圖層3,更名為“城市累計確診病例/治愈病例對比”,按照下圖設(shè)置此圖層,如圖:
這里雖然默認(rèn)叫做氣泡圖,其實更像是一個“餅圖”,因為可以添加多系列數(shù)值進(jìn)來,藉此完成!
【發(fā)散思考】
因為武漢的數(shù)據(jù)是一個相對很大的數(shù)字,對于這種數(shù)據(jù)差異很大的情況,上圖中湖北的數(shù)據(jù)影響了其他省市的數(shù)據(jù)展示,所以我們可以再建立一個圖層,將兩組數(shù)據(jù)的氣泡圖,分別命名為“非湖北”和“湖北”,還是按照上面的操作,我們可以得到下圖,有興趣的同學(xué)可以自己下來操作一下。祝愿:圖表中代表治愈的藍(lán)色能早日充斥到整個中國被疫情波及的地域。
四、“高亮顯示”關(guān)鍵信息
最后我們再來看看,如何讓“三維圖表”也能夠像“常規(guī)圖表”一樣,能把一些比較重要的信息,“高亮顯示”出來。所謂“高亮顯示”就是在一組數(shù)據(jù)圖表中,如果達(dá)到某個標(biāo)準(zhǔn),就可以自動的更改顏色,起到提示的作用!
步驟1:
在數(shù)據(jù)表中制作數(shù)據(jù)源的輔助數(shù)據(jù):
G2單元格輸入函數(shù):
=MAX(A:A)
表示提取最后的發(fā)布日期。
H2單元格輸入函數(shù):
=MAX(A:A)-MIN(A:A)+1
表示提取每個城市連續(xù)統(tǒng)計的天數(shù),記得日期相減要加1,這是常識。
I2單元格輸入一個常數(shù):按照標(biāo)準(zhǔn)7-14天,7-14之間的數(shù)字,任意填一個
G4單元格輸入函數(shù):
=IF(C4<>C3,D4,D4-D3)
用于統(tǒng)計每日新增量。
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)))))))}
用于統(tǒng)計截止2月24日,最后一次每日新增量連續(xù)為0的次數(shù)。這是一個經(jīng)典用法,但這個函數(shù)不太好理解。紅色的部分是這個函數(shù)的關(guān)鍵,大家可以在“部落窩”搜索一下關(guān)于FREQUENCY函數(shù)的用法,以后作者E圖表述也會講到這個函數(shù)。
I4單元格輸入函數(shù):
=IF(A4<>$G$2,0,IF(H4>=$I$2,$I$2,0))
若連續(xù)0增長病例的天數(shù)達(dá)到I2單元格“標(biāo)尺”的標(biāo)準(zhǔn),即顯示I2標(biāo)尺的值,此作為我們需要高亮顯示的關(guān)鍵信息。
步驟2:
建立新工作表,命名為“勝利的顏色”。按如下操作設(shè)置數(shù)據(jù)值。
B2單元格輸入函數(shù):
=SUMIFS(源數(shù)據(jù)!I:I,源數(shù)據(jù)!A:A,"="&源數(shù)據(jù)!$G$2,源數(shù)據(jù)!C:C,A3)
步驟3:
選中數(shù)據(jù)區(qū)域A2:B337,添加到“三維圖表”中,按下圖設(shè)置。
綠色的部分就是代表已經(jīng)連續(xù)7天或者7天以上0增加病例。藉此完成,從圖表上來說,現(xiàn)在疫情是向著利好的方向發(fā)展的。
【編后語】
作為“地圖類型”的圖表,作者給了VBA的方式,也給了大家比較簡單的“三維地圖”的操作。雖然總感覺沒有VBA版的地圖圖表做得賞心悅目,但是在做的過程中,作者感覺“三維地圖”的操作比較簡單。雖然在顯示標(biāo)簽內(nèi)容的時候還是有所欠缺的,但是相信微軟不會留下這樣的一個BUG給我們,所以它的可研究內(nèi)容還有很多,大家一起努力吧。
本文配套的練習(xí)課件請加入QQ群:747953401下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
用VBA制作全國疫情地圖《用excel制作全國動態(tài)疫情地圖(VBA篇)》
疫情動態(tài)圖《新冠肺炎最新走勢情況,一張excel動態(tài)圖帶你看清?。ń刂?020-02-11)》
疫情動態(tài)組合圖《新冠肺炎:“累計確診病例”動態(tài)excel組合圖》
用excel制作波浪圖《疫情過后最想做的10件事是啥?可愛的excel波浪圖給你答案!》
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!