炎炎夏日,突然靈機一動,我們是否可以使用excel獲得上海歷史若干年的天氣數據呢? 想好了就動,先查查互聯網吧! (注:今天的文章步驟和截圖相對較多)
突然找到這麼一家網站,看上去是包含我們想要的數據的
數據包含歷史每一天的最高溫,最低溫,天氣,風向和風力
觀察URL構成 shanghaitianqi
如果選擇了2017年1月的天氣查詢數據,URL就會變成這樣子
那麼可以簡單推斷,歷史數據的獲得可以通過以下通項:
Https://15tianqi.cn/[YEAR]shanghai[MONTH]yuetianqi/
接下來讓我們開始“爬蟲”吧!
步驟1,讓我們打開一個Excel,並且選擇從網絡獲取數據
這裡在高級選項中把我們的URL中可變的部分(以後要作為參數)分割出來
點擊鏈接來獲得數據
步驟2,選擇table 0 並且點擊編輯來編輯數據
可以看到數據包含一些說明行和分表的字段名。
步驟3, 讓我們做一些過濾,這裡主要為了刪除多餘的行數
步驟4, 用第一行作為表頭,這樣我們的數據表就有正確的表頭了
再過濾掉其他重複的信息,因為日期已經作為表頭了,那麼其它行中的帶有字符“日期”的行數就可以刪除了!
階段性成果好啦,看到我們已經獲得了一個月(如最開始輸入的 2017年1月)的數據
步驟5, 我們根據氣溫的數據來製作兩個新的字段,數據類型,方便以後運算
修改為數值型
最高溫和最低溫都修改出來 (這裡PowerQuery會自動提取數值,很方便!!)
並且所有的調整步驟都會被記錄下來(這也是為什麼上篇文章說有了PowerQuery,可以告別VBA了)
步驟6, 下來讓我們來製作兩個參數
分別命名為【年】和【月】
注意 !!類型 需要是【文本型】,因為要拼接URL用!!!
步驟7, 雙擊查詢的源(Source),來做修改
把剛才我們填寫 2017 和 1 的地方分別修改成我們建立的參數【年】和【月】
步驟8, 鼠標右鍵Table 這個查詢,並創建函數
命名函數為 HDTQ (這裡隨意,我是取了【獲得天氣】的首字母)
創建完成可以看到多了一個函數列在查詢側邊欄裡面,並且有兩個可以輸入參數值的位置
步驟9, 讓我們用新的值, 2016年1月來測試以下有沒有數據返回
可以看到數據正常獲取到了
第二個階段性成果好啦! 接下來我們要一次性獲取更多月份數據了!
步驟10, 我們關閉Power Query 並返回到工作簿中-去創建我們需要使用的參數值列表!!
步驟11, 創建兩個表,包含你要獲得的數據的年份和月份並且導入到Power Query中
導入以後的樣子,會多兩個表
步驟12,我們需要把Year這張表和Month這張表強行並在一起
步驟13,展開MONTH可以獲得所有的列聯結果
相當於Cross Join 的結果集
步驟14,移除空值等不相干數據
步驟15,對新生成的表中的數據使用我們之前創建好的函數HDTQ
按順序選擇好對應的字段名稱來作為參數
有時候我們會遇到一些警告,可能是一些安全設置導致的
勾選忽略Privacy Level
這個時候我們就可以看到對應到每個月的數據表都順利獲得了
步驟16,展開數據表我們就可以看到數據了
步驟17, 把數據導入到工作簿中,我們選擇一個新的工作頁
大功告成!! 可以看到我們想要的天氣數據都順利輸出到Excel 工作簿中了
下來,你就可以利用這些數據來研究研究上海的歷史天氣,氣溫了
閱讀更多 FunInCode 的文章