用Excel獲取數據——不僅僅隻是打開表格

引言：看到標題，你是否有些困惑？在Excel上具備數據獲取的能力是指什麼？難道不是把csv格式的表格和Excel格式的表格打開就好了嗎？然而並非這樣。
本文選自《數據化運營速成手冊》。

　　其實標題中有兩層意思：第一層意思是在一些數據庫管理不那麼嚴格的中小型企業，可以通過Excel中的ODBC數據接口，與數據庫或者數據倉庫建立連接，直接快速取數，提高工作效率；第二層意思是Excel 2016中有相當強大的數據獲取工具，即便不能從數據庫直接獲取，也能從多個本地的數據表中將數據抽取、整理和轉化，並做到實時更新，也能提高工作效率。

　　下麵就用簡短的篇幅介紹Excel中的“數據查詢”功能。如圖1所示，在“數據”選項卡下麵單擊“新建查詢”下拉菜單，就能看到Excel提供的數據獲取抓手非常豐富。可以從本地的其他工作簿，也可以從SQLServer、MySQL、Oracle等數據庫，還可以從Hadoop的HDFS文件查詢獲取數據。本節以從本地文件查詢和從Web端查詢為例介紹。

　　　　　　　　　　　　　　　
　　　　　　　　　　　　　　　　　　　　　　　　圖1 數據查詢功能

　　延續之前的例子，我們將表A、表B和表C分別建立成3個Excel文件。在“新建查詢”中單擊“從工作簿”後，選擇我們要的表A，就會出現一個連接的“導航器”，如圖2所示。若直接單擊“加載”按鈕，則表A的數據會全部進入打開的工作表，並建立一個查詢連接。若我們單擊“編輯”按鈕，則會彈出編輯查詢的設置界麵。在“查詢編輯”麵板中，可以可視化地實現SQL，諸如選擇列、添加篩選條件、構造新的字段等，如圖3所示。

　　　　　　　　　　　
　　　　　　　　　　　　　　　　　　　　　　　　圖2 新建數據查詢

　　　　　　　　　
　　　　　　　　　　　　　　　　　　　　　　　　圖3 編輯數據查詢

　　我們的目的是將表A、表B和表C三張表合並。那麼，最合理的做法是將表B作為主表，把表A和表C的內容合並上去。首先，我們分別建立了針對三張表的查詢，如圖4所示。

　　　　　　　　　　　　　　　　　　
　　　　　　　　　　　　　　　　　　　　　　　圖4 新建與源數據的查詢

　　第二步開始設置表B的查詢，點開設置麵板後，單擊“合並查詢”按鈕，如圖5所示。

　　　　　　　　　
　　　　　　　　　　　　　　　　　　　　　　　　圖5 應用合並查詢

　　第三步，在“合並查詢”的設置麵板中，選好兩張表對應的鍵，即“用戶ID”，然後在“聯接種類”中選擇“左外部（第一個中的所有行，第二個中的匹配行）”，這就是一個“LEFT JOIN”，如圖6所示。

　　　　　　　　　　　　　
　　　　　　　　　　　　　　　　　　　　　圖6 合並中設置匹配列和聯接種類

　　單擊“確定”按鈕後，查詢設置界麵上出現一個“NewColumn”，如圖7所示，點開它右邊的按鈕，在下拉列表框中選擇要匹配進表B的字段，選擇“用戶姓名”、“所在區域”、“性別”和“注冊來源”。

　　　　　　　　　
　　　　　　　　　　　　　　　　　　　　　　　圖7 選擇要匹配的字段

　　完成這一步，實際上表A和表B已經成功合並了，我們再次單擊“合並查詢”選項，如圖8所示，按剛才的操作，將表C中的“事件”字段進行“LEFT JOIN”，終於大功告成，如圖9所示。

　　　　　　　　　　　　　
　　　　　　　　　　　　　　　　　　　　　圖8 新的合並中再次設置匹配列和聯接種類

　　　　　　　　　　
　　　　　　　　　　　　　　　　　　　　　　　　　圖9 完成合並後的效果

　　經過上述步驟，不同的表建立查詢後，作為數據源的表一旦有變動，我們隻要右鍵單擊查詢設置頁麵中上方的“刷新”按鈕，數據就會更新。不需要反複地複製粘貼，更不需要手動寫函數轉化，大大提高了工作效率。
　　對這個內容再做一點小小的延伸。在工作中，作者會根據分析需求，先用查詢工具將各個來源的表連接成一個主表；然後由這個主表做透視表和數據透視圖，數據透視表的數據源就是引用整張表對象，而不是一個區域。這樣能實現數據報表的半自動化。隻要數據源進行了更新，那麼打開製作報表的Excel工作簿，將查詢鏈接更新一下，再將數據透視表更新一下，新的報表就完成了。用這個方法，可將原來半天的工作量壓縮為30分鍾，效率的提升是非常可觀的。
　　除了在本地文件之間建立查詢，再簡單介紹如何從網頁上爬取數據。我們從NBA數據統計網站上拉取某個頁麵上呈現的上個賽季東西部球隊的戰績情況，如圖10所示。

　　　　　　　　
　　　　　　　　　　　　　　　　　　　　　　　　圖10 NBA數據統計網站