在Ｅｘｃｅｌ中用Ｍｉｃｒｏｓｏｆｔ Ｑｕｅｒｙ導入外部數據

[摘 要] Excel擁有強大的數據管理#65380;分析能力，借助Microsoft Query能夠訪問多種類型的外部數據源，可從外部數據源選擇數據，將該數據導入到工作表中，以及根據需要刷新數據#65377;本文借助于羅斯文數據庫進行分析，講述Excel與外部Access數據源連接的操作方法及技巧#65377;

[關鍵詞] Excel;Query;外部數據;數據源

Query是Excel中的一個可選功能，如果Office安裝過程中選擇了典型安裝，Microsoft Query就不會安裝到計算機中#65377;當用戶第一次在Excel中使用【數據】│【導入外部數據】│【新建數據庫查詢】菜單項時，系統將提示安裝Query，插入Office安裝盤，單擊【確定】按鈕即可#65377;Excel訪問外部數據源的操作方法因數據源類型的不同而有差異，但操作方法和步驟基本相同#65377;

一#65380;外部數據#65380;Query及Excel三者關系

Query是Excel表和數據源之間的橋梁，Query可訪問的數據庫文件包括MicrosoftAccess文件#65380;Microsoft SQL Server文件#65380;Microsoft FoxPro文件#65380;Oracle文件#65380;文本數據庫文件和網頁文件等#65377;如果Query所要訪問的數據源不在本地計算機中，則需要向外部數據庫管理員申請密碼#65380;用戶權限或其他有關連接數據庫的信息#65377;Query 使用數據源連接到外部數據庫并顯示可用的數據，通過創建查詢將數據返回到 Excel#65377;數據源是存儲起來的一組信息，使用Query設置數據源時，可為數據源指定名稱，然后提供數據庫或服務器的名稱和位置#65380;數據庫的類型，以及用戶的登錄和密碼信息，該信息還包括ODBC驅動程序或數據源驅動程序的名稱#65377;查詢讀取數據庫前，要確切掌握指定數據庫中所使用的各表的文件名#65380;字段名的含義等，才能正確獲取所需要數據#65377;外部數據#65380;Query及Excel三者的關系如圖1所示#65377;

二#65380;使用Query查詢向導導入外部數據

使用查詢向導可以很輕松地從外部數據庫的不同表和字段中選擇數據并將數據放在一起#65377;在創建新數據源對話框中指定數據源的名稱#65380;驅動程序并進行連接，選中“使用查詢向導創建/編輯查詢”復選框，確定后在陸續出現的多個查詢向導對話框中依次指定所需要的列#65380;篩選條件#65380;排序方式及結果的保存方式等，就可將數據導入到Excel中#65377;

【例1】使用查詢向導，查找在羅斯文數據庫中購買蘋果汁數量大于等于30件的貨主以及購買的數量和單價#65377;

說明:羅斯文數據庫是Office中的一個示例數據庫，其默認位置為C:\\Program File\\Microsoft Office\\Office11

Samples\\Northwind.mdb#65377;

1. 創建名為“羅斯文公司數據1”的新數據源

打開Excel新建工作簿Book1，然后選擇【數據】│【導入外部數據】│【新建數據庫查詢】菜單項，隨即會彈出【選擇數據源】對話框，切換到【數據庫】選項卡中，然后在其下方的列表框中選擇【<新數據源>】選項，并且選中【使用“查詢向導”創建/編輯查詢】復選框，單擊【確定】按鈕，在【創建新數據源】對話框中，輸入數據源的名稱【羅斯文公司數據1】#65380;選擇驅動程序【Microsoft Access Driver(*.mdb)】，單擊【連接】按鈕，彈出【ODBC Microsoft Access安裝】對話框，單擊【選擇】按鈕，彈出【選擇數據庫】對話框，找出存放數據庫的文件即“Northwind.mdb”，并將其選中#65377;單擊【確定】按鈕返回【ODBC Microsoft Access安裝】對話框#65377;單擊【確定】按鈕，回到【創建新數據源】對話框，單擊【確定】按鈕，回到【選擇數據源】對話框，至此，在【數據庫】選項卡的列表框中會顯示出添加的數據源【羅斯文公司數據1】(如圖2所示)#65377;

2. 查詢向導操作

查詢向導—選擇列:在圖2中，單擊【確定】按鈕，彈出【查詢向導—選擇列】對話框，單擊【>】按鈕分別將【訂單】表中的“貨主名稱”，【產品】表中的“產品名稱”，【訂單明細】表中的“數量”和“單價”導入到【查詢結果中的列】的列表框中#65377;

查詢向導—篩選數據:單擊【下一步】按鈕，彈出【查詢向導—篩選數據】對話框，這里可根據需要對數據進行篩選#65377;如先設置【產品名稱 等于 蘋果汁】，再設置【數量 大于或等于 30】#65377;

查詢向導—排序順序:單擊【下一步】按鈕，彈出【查詢向導—排序順序】對話框，這里按【貨主名稱】的升序排列#65377;

查詢向導—完成:單擊【下一步】按鈕，彈出【查詢向導—完成】對話框，然后在【請確定下一步的動作】組合框中選中【將數據返回Microsoft Excel】單選按鈕#65377;單擊【完成】按鈕，彈出【導入數據】對話框，然后在【數據的放置位置】組合框中選擇【現有工作表】單選按鈕，并在其下方的文本框中選擇放置的位置【=$A$1】，單擊【確定】按鈕，即可將符合條件的數據導入Excel(如圖3所示)#65377;

三#65380;直接使用Query導入外部數據

Query圖形化界面更直觀，直接使用Query可以創建查詢向導不允許的復雜查詢#65377;

【例2】不使用Query查詢向導，直接通過Query在羅斯文數據庫中查找購買蘋果汁數量大于等于30件的貨主以及購買的數量和單價，并且要求在Query中添加列字段“金額”#65377;

1.創建名為“羅斯文公司數據2”的新數據源

和向導查詢步驟相同建立一個名為【羅斯文公司數據2】的數據源，注意撤選【使用“查詢向導”創建/編輯查詢】復選框(如圖4所示)#65377;

2. 直接在Query下的操作

添加表:在圖4中，選中【羅斯文公司數據2】，單擊【確定】按鈕，彈出【添加表】對話框，分別對【訂單】表#65380;【產品】表#65380;【訂單明細】表進行【添加】操作，添加完畢后單擊【關閉】按鈕#65377;

設置查找字段:分別雙擊【訂單】表中的“貨主名稱”#65380;【產品】表中的“產品名稱”#65380;【訂單明細】表中的“數量”和“單價”#65377;也可直接將上述字段用鼠標拖動到Query的下方區域中#65377;

設置查詢條件:選擇【視圖】│【條件】菜單項，或者單擊工具欄上的【顯示/隱藏條件】按鈕，設置如圖5所示條件#65377;

設置排序順序:單擊【貨主名稱】列，單擊工具欄上的【升序】按鈕，即按【貨主名稱】的升序排列#65377;

添加列字段【金額】:選擇【記錄】│【添加列】菜單項，彈出【添加列】對話框，進行如圖6所示的設置，設置完成后，單擊【添加】按鈕，然后單擊【關閉】按鈕，即可完成【金額】列的添加#65377;

返回數據:單擊按鈕【將數據返回到Excel】，彈出【導入數據】對話框，然后在【數據的放置位置】組合框中選擇【現有工作表】單選按鈕，并在其下方的文本框中選擇放置的位置【=$A$1】，單擊【確定】按鈕，完成導入過程#65377;

四#65380;保存和調用查詢

如果經常用到這個查詢，則每次進行這些操作步驟比較麻煩，我們可將該查詢過程保存起來#65377;查詢的保存:如果使用Query查詢向導導入數據，則在【查詢向導—完成】對話框中單擊【保存查詢(S)…】按鈕;直接使用Query導入數據，則可在Query界面中單擊工具欄上的【保存】按鈕或者單擊【文件】│【保存】菜單項#65377;當第一次保存查詢定義時，將打開【另存為】對話框，將查詢保存為“.dqy”的查詢文件#65377;

以后調用這個查詢，只需要在Excel中執行【數據】│【導入外部數據】，找到保存的“.dqy”查詢文件就可以了#65377;如果Query已經打開，單擊Query的【文件】│【打開】菜單項，找到保存的“.dqy”文件，運行查詢，然后將結果返回到Excel工作表中#65377;

五#65380;在 Excel 中處理數據

使用Query或 Query查詢向導創建查詢，將數據返回到 Excel 工作表之后，數據會變為外部數據區域，可以對其進行格式設置和刷新#65377;Excel 提供了許多用于刷新查詢的選項，包括每次打開工作簿時自動刷新數據和設置刷新頻率刷新數據#65377;查詢結果返回到Excel工作表中之后，就可以借助Excel的數據處理功能#65380;圖表處理功能等，對查詢結果進行必要的格式化處理#65380;數據統計或圖表處理，以方便用戶的使用#65377;

目前財務軟件多使用Access或Server的數據庫，在會計實務工作中對于不太熟悉Access或Server的財務人員來說，通過Query或Query查詢向導，可以將數據返回到Excel的工作表進行分析管理#65377;

主要參考文獻

[1] 華聯科技. Excel電子表格[M]. 北京:機械工業出版社，2007.

[2] 楊文林. 會計電算化中級培訓教材[M]. 北京:經濟科學出版社，2004.

[3] Hlaudit. 還說Excel中的神秘武器——簡述在Excel中打開Access數據[DB/OL]. http://bbs.iaudit.cn/ShowPost.asp?menu=PreviousForumID=21ThreadID=26886，2007-08-09.