了解大數據分析？試試看從VBA爬蟲＋SQL查詢開始

最新 06-26

回頭想想，自己跟數據打交道也不少時日了。所謂大數據，其實是服務於戰略決策、解決實際問題的數據集合的推演過程。數據採集、清洗（規整化、處理無效數據、GBK轉碼）、存儲、分析、輸出&可視化等都是「大數據」概念的組件。那天開了個會，主題好像是「智能財務」，感覺深受啟發，尤其是「數據湖」「數據集市」「智能分錄」「模型工廠」等一眾嶄新理念令我等耳目一新，獲益良多（只要你敢想，估值百倍不是夢~）。

雖然（大量）數據積累是個吃力不討好的活兒。枯燥、易錯、緩慢且不被重視。但如果積累了一定量的高質量數據，卻能成為有力的戰略武器，為許多決策提供極高的參考價值。

其實很多大型網站積累的數據能為我們的分析、建模、決策提供了很好的素材。而「爬蟲」程序，是我們批量採集，搭建基礎庫的一種有效工具，具有較高的技術價值。（「爬蟲工程師」你們了解一下~）。

上回，我們通過「批量爬取小黃圖」一文，接觸到了爬蟲程序常用的VBA函數。批量圖片爬取，主要是識別圖片地址常用的「src」起頭&.jpg,.png後綴等固定字元串結尾，截取指定的圖片地址信息，再用VBA-downloadtofile函數完成批量下載（最新研究發現，VBA可以直接調用迅雷，完成高速批量下載）

這次，我們以【攜程】為例，了解爬蟲工具中各種VBA字元串函數的使用方法&循環爬取策略。目的是將攜程網上深圳全部的酒店信息批量摘下來。建立一個資料庫。

還是一樣，爬蟲思路的構建是從分析目標網站的HTML代碼開始的。

列表中第一個酒店是「南山citiGO」，我們直接在HTML碼頁面搜索（Ctrl+F）去定位（這樣可以跳過大段的無關HTML代碼）。於是，我們在514行發現了citogo的實際列表信息（酒店名稱），以及「地鐵站」「地址」「評價分數」「最低價格」等關鍵信息。我們的目的就是快速、批量獲取&整理成資料庫。

同時，網頁分出了291個頁面，也需要我們通過VBA-循環語句進行批量訪問，提取每一頁的酒店信息。

這個分頁器的HTML代碼其實蘊含著解決批量訪問的關鍵！它可以為我們使用VBA-循環找到很好的路徑。我們發現，該頁面地址是根據shenzhen30/PXX(XX為頁碼)的規律排列的，尾頁是291。哈哈哈，這個好辦，VBA-循環就可以解決了！

通過分析，我們發現，頁面雖大，HTML代碼雖多，但酒店清單其實開始於514行，而開頭的標誌是："hotel_list"，分頁器意味著結尾。也就是說，返回每頁的這一段，就可以作為提取關鍵信息（欄位）的文本依據了。

VBA代碼：【建立msxml2.xmlhttp對象——發行請求——返回HTML文本——截取酒店清單列表——放入A1單元格】

結果返回

根據一開始的思路，我們要截取514行開始的酒店清單，也就是識別頭部「hotel_list」以及尾部「」（位於543行）。這裡我們用VBA-split函數，先把文本從hotel_list分開取第二段，然後再從分開取第一段。VBA代碼如下：

單元格A1得到酒店清單信息，已經開始可以在頭幾行看到CITIGO酒店的名稱，說明HTML 514行之前的內容被截掉了。

現在，我們要做的是：提取清單中每個酒店的名稱，地址，評價分數，房價等信息。他要求我們，使用VBA進行字元串的操作，截取例如「深圳南山citiGO酒店」這樣的文字，放入excel單元格。從頁面上看，每張頁面25個酒店。截取25個「酒店名稱」相當於提取一個含25個元素的數組。每個數組元素表示一個酒店名稱。

這個可以通過酒店名稱的"hotel_num"作為前置標識符，分成25個數組，再通過"

vba代碼如下：

我們可以看到結果很接近最後的目的了：

最後用vba-replace函數把這些替換掉，調整截取的初識位置，返回乾淨的酒店名稱即可

返回結果

用類似方法，VBA函數的分列split，截取mid+instr，替換replace，調整初始位置等字元串操作，我們還能提取酒店地址，評價，價格等具體信息。代碼如下：

攜程深圳第一頁的酒店清單相關信息，我們就全部清理出來了

接下來就是循環所有網頁~通過shenzhen30/p &頁碼的格式進行循環。另外要注意，每循環一次是25個酒店信息，所以循環一頁是25×頁碼（k=1 to 291）+i行(i=1 to 25)

於是，在2018-6-22晚上九點，我們爬取了攜程在深圳的5261家酒店，耗時大概180秒

我們再來總價一下爬蟲程序批量獲取信息的方法論：

①分析網頁HTML代碼，找到關鍵信息的位置。觀察關鍵信息前後的字元串特徵。

②建立VBA object對象，請求返回HTML代碼，同時觀察HTML地址的特徵，建立批量訪問的循環語句

使用VBA函數，包括split, mid,instr,replace 等處理關鍵信息字元串，循環填充到excel單元格

接下來。。。。既然，有了這麼多酒店信息，我們是否可以為將來開房做些有參考價值的數據挖掘呢？哈哈哈，當然是可以的。這裡，我們著重介紹在excel中，將excel worksheet作為一個資料庫，使用sql的分析操作。

sql是standard query language(標準查詢語言)的縮寫，它主要用於查詢、分析資料庫中的數據，例如一句比較標準的例句：select * from table_1 where price>500, 意思是，從table_1這張數據表中返回所有欄位的信息，其中滿足price欄位下的所有數值＞500。雖然基於excel的數據分析中，數據透視&取數公式是主流，但在大量數據分析中，sql的優勢是快速，標準化，查詢結果的可移植。

在excel調用sql，主要的方法是：使用VBA建立一個資料庫聯結對象，將爬蟲返回的excel worksheet作為一個資料庫，再建立一個數據集對象，將worksheet資料庫的表定義成數據集，使用vba-execute函數，用sql查詢想要獲得的信息，最後將查詢結果複製黏貼到一個新的worksheet.

代碼如下：