1.2 Microsoft Query

Mr.林：小白，接下來我們來學習另外一個工具，它就是Microsoft Query。

Microsoft Query是用于將數據從外部數據源檢索到其他Microsoft Office應用（特別是Microsoft Excel）中的一種程序。通過使用Microsoft Query可以從企業的數據庫和文件中檢索數據，而不必在Excel中重新輸入需要分析的數據。也可以在每次更新數據庫時，自動通過源數據庫中的數據來更新Excel報表和匯總數據。

Microsoft Query可以從多種類型的數據源中檢索數據，常見數據源類型包括：

★ Microsoft Office Access

★ Microsoft SQL Server

★ Microsoft FoxPro

★ Oracle

★ Microsoft Office Excel

★ 文本文件

在Microsoft Query中，可以使用“查詢向導”創建簡單的查詢。我們知道在Microsoft Query或Access數據庫中，查詢是一種查找記錄的方法，而這些記錄回答了用戶對數據庫中存儲的數據提出的特定問題。

小白：那么Microsoft Query與Access數據庫有何區別呢？

Mr.林：Microsoft Query與Access數據庫既有相似之處，也有不同之處。

兩者的數據處理與分析功能類似，都可以采用SQL語句進行查詢處理，連Microsoft Query的查詢操作界面與Access數據庫的都很類似，如圖1-59所示，這樣方便我們學習掌握Microsoft Query工具。

圖1-59 Microsoft Query查詢操作界面

Microsoft Query與Access數據庫的不同之處主要體現在前者在性能方面稍遜于后者，以及Microsoft Query數據處理的靈活性及速度都不及Access數據庫。

小白：什么時候該使用Microsoft Query工具？什么時候使用Access數據庫呢？

Mr.林：以下幾種情況可采用Microsoft Query進行處理分析。

★ 計算機沒有安裝Access或其他數據庫軟件。

★ 數據處理量相對較小，數據量在Excel可容納的范圍內。

★ 無須進行數據處理，或者數據處理操作較為簡單。

★ 初學者學習SQL語句。

需要補充說明一點，如果數據量超過Excel容納范圍（如Excel 2007-2010版本最大記錄數為1048576），不過只需得到統計分析結果，無須獲取處理后的明細數據，也是可以使用Micro-soft Query工具進行數據處理與分析的。在計算機配置允許的情況下，Microsoft Query工具也可以處理千萬級的數據，只是性能會變差，耗時較長。

小白：好的，具體如何使用Microsoft Query工具進行數據處理與分析呢？

Mr.林：使用Microsoft Query工具進行數據處理與分析，需要以下四步，如圖1-60所示。

圖1-60 Microsoft Query數據處理與分析步驟

1.2.1 數據導入

建立數據源連接數據

小白：如何建立數據源以連接數據呢？

Mr.林：仍以“訂購明細”、“用戶明細”文本數據為例，我們需要了解不同年齡段用戶的產品購買偏好，要用到“訂購明細”表的“用戶ID”、“產品”字段，以及“用戶明細”表的“年齡”字段，首先就是建立數據源以連接數據。

STEP 01 啟動Excel 2010，在【數據】選項卡上的【獲取外部數據】組中，單擊【自其他來源】，然后單擊【來自Microsoft Query】。

STEP 02 在彈出的【選擇數據源】對話框中，在【數據庫】選項卡下，雙擊【<新數據源>】，如圖1-61所示。

圖1-61 Microsoft Query【選擇數據源】對話框

STEP 03 彈出【創建新數據源】對話框，如圖1-62所示。

圖1-62 Microsoft Query【創建新數據源】對話框

★ 在第1項輸入框中輸入自定義的數據源名稱，以標識和區分數據源，本例輸入“數據”。

★ 在第2項下拉框中，單擊選擇作為數據源的數據庫類型的驅動程序，本例選擇“Microsoft Text Driver (*.txt; *.csv)”項。

★ 在第3項設置數據源所在文件夾路徑，本例選擇“D：\數據”。

★ 在第4項中可選擇其中一個文本文件作為數據源選定默認表，本例選擇“訂購明細.txt”，單擊【確定】按鈕。

STEP 04 在【創建新數據源】對話框中設置完相應參數后，單擊【確定】按鈕，返回【選擇數據源】對話框，這時在【數據庫】選項卡下就會出現一個名為“數據”的數據源，如圖1-61所示，這時數據源已經設置好。

小白：Excel數據源如何建立連接？

Mr.林：Excel數據源建立連接與文本數據源建立連接的操作步驟類似，只是選擇的驅動程序不一樣，可以只設置Excel 2003版以下的數據格式，也可以設置Excel 2007—2010版的數據格式。你可以事后進行練習，我在此就不再重復闡述。

小白：再請教個問題，圖1-61所示的【選擇數據源】對話框中，可以直接選擇現有的“Ex-cel Files*”、“Text Files*”等現有數據源么？

Mr.林：可以的，步驟也類似，但是每次打開同一個Excel、文本文件，都需要重新操作一次，如果建立好數據源，以后就可以直接調用，所以需要根據自己的需求選擇建立一次性數據源還是永久數據源。

小白：好的，明白了。

使用“查詢向導”選擇數據

Mr.林：建立好數據源，接下來就使用“查詢向導”選擇我們所需要的數據。

STEP 01 返回【選擇數據源】對話框，確認對話框中下方的【使用“查詢向導”創建/編輯查詢】項被勾選上，雙擊【數據庫】選項卡下剛建好的名為“數據”的數據源，如圖1-61所示。

STEP 02 在彈出的【查詢向導—選擇列】對話框中，將所需的“訂購明細”表的“用戶ID”、“產品”、“數量”字段，以及“用戶明細”表的“年齡”字段，分別從左邊【可用的表和列】中選擇并移至右邊的【查詢結果中的列】，單擊【下一步】按鈕，如圖1-63所示。

圖1-63 Microsoft Query【查詢向導 — 選擇列】對話框

STEP 03 在彈出的警告提示框中（如圖1-64所示），單擊【確定】按鈕，將出現如圖1-65所示的Microsoft Query查詢操作界面。

圖1-64 Microsoft Query警告提示框

圖1-65 Microsoft Query查詢操作界面1

Mr.林：小白，你來看一下，從圖1-65所示的Microsoft Query查詢操作界面可以發現什么？

小白認真地看了一會后興奮地跳起來：Mr.林，我發現了：

①“訂購明細”與“用戶明細”兩個表之間沒有建立關系。

②“用戶ID”與“年齡”沒有對應，出現同一個用戶有不同年齡信息。

③ Microsoft Query查詢操作界面最下方有提示“將字段從某一表拖曳到另一表的相關字段”。

Mr.林：沒錯，基本上就是這些，這一切都源于沒有對“訂購明細”與“用戶明細”兩個表建立關系，在第三步操作中，如圖1-64所示，就已經提示需要拖動字段進行人工連接。所以接下來就要建立兩表關系。

STEP 04 將“用戶明細”表中的“用戶ID”字段拖曳到“訂購明細”表的“用戶ID”字段，得到如圖1-66所示的結果。

圖1-66 Microsoft Query查詢操作界面2

Mr.林：Microsoft Query工具中，用拖動字段的方式建立各表之間的連接關系默認是內連接（INNER JOIN），即選擇兩個表中關鍵字段相匹配的記錄。

在Microsoft Query工具中，單擊菜單中的【表】，選擇【連接】項，即可得到如圖1-67所示的連接關系對話框，連接內容中的1、2、3項分別對應內連接（INNER JOIN）、左連接（LEFT JOIN）、右連接（RIGHT JOIN）三種關系，這與Access數據庫中的連接關系是一致的。

圖1-67 Microsoft Query連接關系對話框

到此，我們就完成了數據導入的工作，后續還要進行相應的數據處理工作。

1.2.2 數據處理

Mr.林：之前我們已經學習了在Access數據庫中如何用SQL語句進行數據處理，所以我們在Microsoft Query工具中也采用SQL語句進行數據處理。小白，你有沒發現菜單欄中有個SQL按鈕？

小白：發現了。

Mr.林：用鼠標單擊它，見證奇跡的時刻又來了——剛才文本數據導入的操作同樣被轉化為SQL語句，并在SQL語句輸入對話框中顯示，如圖1-68所示。

圖1-68 Microsoft Query SQL語句輸入對話框

小白：是呀！這個功能真好用。

Mr.林：現在我們只需修改SQL語句，采用PARTITION分組函數，增加年齡分組相應的語句，修改后的語句如下：

SELECT訂購明細.用戶ID,訂購明細.產品,訂購明細.數量,PARTITION(用戶明細.年齡,1,100,5) AS
年齡分組
FROM訂購明細.txt訂購明細, 用戶明細.txt用戶明細
WHERE用戶明細.用戶ID = 訂購明細.用戶ID;

將編寫好的SQL語句直接復制至SQL語句輸入對話框中，單擊【確定】按鈕，Microsoft Query工具將運行SQL語句，結果如圖1-69所示。

圖1-69 Microsoft Query數據處理結果示例

Mr.林：這樣就完成了年齡分組的數據處理工作。

數據合并、數據計算、數據分組、數據去重等其他的SQL語句數據處理方式，均與Access數據庫中的基本一致，就不再重復闡述。

1.2.3 數據分析

SQL語句分析法

Mr.林：接下來要在剛才數據處理的基礎上進行數據分析。

小白還沒等Mr.林繼續往下說就搶道：Mr.林，數據分析的操作讓我來試試，在原有的SQL語句后加上按“年齡分組”和“產品”進行GROUP BY分組，并對“訂購明細”表的“數量”字段進行求和計算，修改后的語句如下：

      SELECT PARTITION(用戶明細.年齡,1,100,5) AS年齡分組, 訂購明細.產品, SUM(訂購明細.數量)
      AS產品數
      FROM訂購明細.txt訂購明細, 用戶明細.txt用戶明細
      WHERE用戶明細.用戶ID = 訂購明細.用戶ID
      GROUP BY PARTITION(用戶明細.年齡,1,100,5),訂購明細.產品;

將編寫好的SQL語句直接復制至SQL語句輸入對話框中，單擊【確定】按鈕，Microsoft Query工具將運行SQL語句，結果如圖1-70所示。

圖1-70 Microsoft Query數據分析結果示例

Mr.林滿意地點了點頭：小白，說得好，看來你已經掌握SQL語句了，并且能靈活運用，很不錯。

小白笑瞇瞇地說：還要感謝Mr.林教導有方。

Mr.林：補充一點：我將數據處理與數據分析分為兩個單獨步驟，為的是讓你對數據處理與數據分析過程能有更清晰的了解。我們知道數據處理的目的就是為了進行數據分析，如果對SQL語句熟悉的話，就不必分開編寫，只要直接用最后數據分析的那條SQL語句就可以完成數據處理與分析工作。

小白：好的。

數據透視表法

Mr.林：剛才學了SQL語句分析法，我們再來學習另外一種方法，就是數據透視表法，這個工具已經教過，在這里就不細說了。這里要學習的是如何把處理好的數據作為數據透視表的數據源。

我在剛才年齡分組數據處理的基礎上進行介紹。如圖1-69所示，這是處理好的數據，現要將其導入Excel數據透視表中，作為分析所需的數據源，具體操作步驟如下。

STEP 01 單擊菜單欄中的【文件】→【將數據返回Microsoft Excel】項，或者單擊菜單欄中的【將數據返回Microsoft Excel】按鈕（如圖1-69所示），或者直接關閉Microsoft Query查詢操作界面，以上三種操作方式都將彈出如圖1-71所示的Excel【導入數據】對話框。

圖1-71 Excel【導入數據】對話框

STEP 02 在彈出的【導入數據】對話框的【請選擇該數據在工作簿中的顯示方式】中選擇【數據透視表】項，數據的放置位置采用默認方式，單擊【確定】按鈕，Excel會執行導入數據操作，并在Excel下方的狀態欄中顯示數據導入進度。

STEP 03 數據導入完畢后，則會創建一個空白的數據透視表框架，同時在其右側顯示一個【數據透視表字段列表】窗格。將【數據透視表字段列表】窗格中相應的字段拖動至【行標簽】、【列標簽】、【匯總數值】處，得到如圖1-72所示的結果，與圖1-70所示的結果是一樣的，只是數據透視表結果可以更加靈活、方便地以交叉表方式顯示。

圖1-72 Excel數據透視表分析結果示例

小白：果然如此，這一招真實用。