|
第２７章 使用外部數(shù)據(jù)庫文件
用戶在使用Excel進(jìn)行工作的時候，不但可以使用工作表中的數(shù)據(jù),，還可以訪問外部數(shù)據(jù)庫文件,。使用外部數(shù)據(jù)庫文件有很多優(yōu)點,，其中最大的優(yōu)點就是用戶可以利用導(dǎo)入和查詢,，在Excel中使用熟悉的工具外部數(shù)據(jù)進(jìn)行處理和分析。
多數(shù)情況下,，用戶并不需要導(dǎo)入整個外部數(shù)據(jù)文件,，只需通過對外部數(shù)據(jù)庫執(zhí)行查詢,，就可以將外部數(shù)據(jù)庫中的某一個子集截入到Excel工作表中,。其中可以只包含用戶所需要的某些字段或是某些數(shù)據(jù)。
?。玻罚薄×私馔獠繑?shù)據(jù)庫文件
由于Excel?。玻埃埃彻ぷ鞅淼男胁荒艹^65 536行，列不能超過256列,，因此用戶即使擁有最先進(jìn)的計算機(jī)系統(tǒng),，也無法處理非常巨大的表格。而許多外部數(shù)據(jù)庫文件在操作系統(tǒng)的允許下幾乎可以無限大,，其保存的記錄數(shù)量也可以是Excel工作表的千萬倍,。外部數(shù)據(jù)庫文件可以是文本文件、Microsoft Access數(shù)據(jù)庫,、Microsoft SQL數(shù)據(jù)庫、Microsoft OLAP多維數(shù)據(jù)集,、dBASE數(shù)據(jù)庫等,。雖然用戶不能在Excel中處理巨大的數(shù)據(jù),，但是可以通過導(dǎo)入和查詢外部數(shù)據(jù)庫中的文件，獲取所需要的信息,。
?。玻罚病±梦谋疚募@取數(shù)據(jù)
Excel提供了３種方法可以從文本文件獲取數(shù)據(jù)。
?。保├貌藛螜?#8220;文件”－“打開”命令,，可以直接導(dǎo)入文本文件。
?。玻┰诓藛螜谏弦来螁螕?#8220;數(shù)據(jù)”——“導(dǎo)入外部數(shù)據(jù)”——“導(dǎo)入數(shù)據(jù)”,，直接導(dǎo)入文本文件。
?。常┦褂肕icrosoft Query,。
使用第１種方法時，文本文件會被導(dǎo)入到單張的Excel工作表中,。使用這種方式時,，如果文本文件的數(shù)據(jù)發(fā)生變化，并不會在Excel中體現(xiàn),，除非重新進(jìn)行導(dǎo)入,。
使用第２種方法時，Excel會在當(dāng)前工作表的指定位置上顯示導(dǎo)入的數(shù)據(jù),，同時Excel會將文本文件作為外部數(shù)據(jù)源,。一旦文本文件中的數(shù)據(jù)發(fā)生變化，用戶只需單擊鼠標(biāo)右鍵,，在彈出的快捷菜單中選擇“刷新數(shù)據(jù)”即可獲得最新的數(shù)據(jù),。
如果用戶的文本文件數(shù)據(jù)量巨大，在Excel中不能導(dǎo)入全部數(shù)據(jù),，而只需選擇某些滿足特定需要的記錄,，可以使用Microsoft Query。利用
Microsoft Query,，用戶可以確定選擇條件,，將導(dǎo)入操作限制在實際需要的記錄上。有關(guān)Microsoft Query的更多細(xì)節(jié),，請參閱２７－４節(jié),。
２７．２．１ 編輯文本導(dǎo)入
導(dǎo)入文本文件時,，雖然不能滿足用戶只導(dǎo)入指定記錄的需要,，但它卻能夠向用戶提供其他形式上的控制。例如,，用戶在導(dǎo)入文本文件時可以將不需要的列刪除,，還能夠設(shè)置導(dǎo)入列的數(shù)據(jù)類型,，主要為常規(guī)、文本,、日期,。
示例２７．１ 導(dǎo)入文本文件到Excel工作表中
如果要將“２７．２物料入庫信息查詢．ｔｘｔ”的文本文件導(dǎo)入到Excel中，可以參照以下步驟,。
步驟１．創(chuàng)建一個新的空白工作表,。
步驟２．單擊菜單“文件”——“打開”，在出現(xiàn)的“打開”對話框中單擊“文件類型”的下拉列表,，選擇“文本文件”,，找到“物料入庫信息查詢.txt”的文本文件并雙擊它，出現(xiàn)“文本導(dǎo)入向?qū)А?步驟之1”對話框,。“文本導(dǎo)入向?qū)А?步驟之1”對話框中的“導(dǎo)入起始行”為1,，也就是說文本文件將從第1行連同標(biāo)題導(dǎo)入，如果選擇2,，則從第2行導(dǎo)入,，導(dǎo)入的數(shù)據(jù)中將不包含標(biāo)題。“文件原始格式,，如果用戶在對話框下部的預(yù)覽窗口中發(fā)現(xiàn)字符顯示為亂碼,，可以在列表中手動選擇一個匹配的字符集。
步驟３．單擊“下一步”按鈕,，設(shè)置分列數(shù)據(jù)所包含的分隔符號,，用戶可以選擇“分號”、“逗號”,、空格及“其他”,，“其他”可根據(jù)數(shù)據(jù)實際的分隔情況來輸入，如“_",、"*"等,，本例中則使用“Tab鍵”。
步驟４．單擊“下一步”按鈕,，進(jìn)入“文本導(dǎo)入向?qū)А?步驟之3”對話框,。在此步驟中，用戶可以取消對某列的導(dǎo)入,，同時可以設(shè)置每個導(dǎo)入列的列數(shù)據(jù)格式,。單擊第一列貨位，在“列數(shù)據(jù)格式”中單擊“不導(dǎo)入此列（跳過）”選項按鈕,；單擊款號列,，“列數(shù)據(jù)格式”中單擊“文本”選項按鈕；單擊日期列,，“列數(shù)據(jù)格式”中單擊“日期”選項按鈕,。
步驟５．單擊“確定”按鈕完成導(dǎo)入,。
注意“要將導(dǎo)入的文件另存為Excel類型的文件,，否則將不能保存導(dǎo)入的文本文件,。
２７．２．２ Excel中的分列功能
Excel中的分列功能在處理大量數(shù)據(jù)時十分方便,、快捷,。它能將一列帶胡統(tǒng)一分隔符號的數(shù)據(jù)，快速地按照分隔符號分隔多列來達(dá)到用戶的需要,，也可以一次性地將不能計算的文本型數(shù)值改變?yōu)榭梢詤⑴c計算的常規(guī)型數(shù)值,。
示例２７．２ 快速將數(shù)據(jù)分為多列
圖所示的數(shù)據(jù)列表是從財務(wù)軟件中導(dǎo)出的費(fèi)用數(shù)據(jù)，其中的Ｇ列“借方”為文本型數(shù)值,，不能統(tǒng)計金額合計,，Ｆ列的“摘要”中“_”后面都是發(fā)生費(fèi)用的各個部門?？梢詮腅xcel Home網(wǎng)站下載“28.2Excel分列功能.xls”文件,。
步驟１．首先在Excel數(shù)據(jù)列表中選中整個G列后單擊右鍵,，在彈出的快捷菜單中選擇“插入”,，插入一個空白列，為下列的數(shù)據(jù)分列操作預(yù)留空間,。
步驟２．先遣整個Ｆ列,，單擊菜單“數(shù)據(jù)”——“分列”,，在彈出的“文本分列向?qū)А巢襟E１”對話框中，選擇“分隔符號”選項,。單擊“下一步”按鈕,，出現(xiàn)“文本分列向?qū)А巢襟E之２”對話框。
步驟３．勾選“分隔符號”中“其他”復(fù)選框,，在右邊的輸入框中輸入“_”下劃線,，請注意“數(shù)據(jù)預(yù)覽”中的變化。
步驟４．單擊“下一步”按鈕,，出現(xiàn)“文本分列向?qū)А巢襟E之３”對話框,。在“列數(shù)據(jù)格式”中，有常規(guī),、文本,、日期３種數(shù)據(jù)格式，在“數(shù)據(jù)預(yù)覽”中單擊一列數(shù)據(jù),，在“列數(shù)據(jù)格式”選擇要設(shè)置的數(shù)據(jù)類型,，可以快速改變整列的數(shù)據(jù)類型。
步驟５．單擊“完成”按鈕,，完成對數(shù)據(jù)列表中Ｆ列的分列,，此時,，部門作為單獨的一列被分離出來了。
同樣,，對“借方”進(jìn)行文本與數(shù)值的轉(zhuǎn)換,，在“文本分列向?qū)А巢襟E３”對話框中“列數(shù)據(jù)格式”選擇“常規(guī)”即可。
?。玻罚场?dǎo)入外部數(shù)據(jù)
如果用戶需要引用本地計算機(jī)或網(wǎng)絡(luò)上的外部數(shù)據(jù),，例如引用局域網(wǎng)內(nèi)共享文件中的數(shù)據(jù)，可以通過使用Excel“導(dǎo)入外部數(shù)據(jù)”的功能來達(dá)到目的,。只要外部數(shù)據(jù)源的位置不改變,，用戶就可以在Excel數(shù)據(jù)列表中隨時右擊鼠標(biāo)選擇“刷新數(shù)據(jù)”，便可以獲得引用的外部數(shù)據(jù)源中最新的數(shù)據(jù),。用戶通過設(shè)置“外部數(shù)據(jù)區(qū)域?qū)傩?#8221;,，還可以使得引用數(shù)據(jù)在每次打開工作表時自動刷新或是在工作表打開的狀態(tài)下自動定時刷新。如果數(shù)據(jù)源的位置改變了,，用戶還需要重新設(shè)置名稱和路徑,，才能保證外部數(shù)據(jù)的正常刷新。
例如,，要引用文件“２７．３標(biāo)準(zhǔn)工時數(shù)據(jù)．ｍｄｂ”中的數(shù)據(jù)并保持與它的時時更新,，請參照以下步驟進(jìn)行。
步驟１．創(chuàng)建一個新的空白工作表,。
步驟２．單擊菜單“數(shù)據(jù)”——“導(dǎo)入外部數(shù)據(jù)’——鼠標(biāo)指向“導(dǎo)入數(shù)據(jù)”并單擊它,。
步驟３．在彈出的“選擇數(shù)據(jù)源”對話框中，單擊“文件類型”下拉列表,，選擇文件類型為“Access 數(shù)據(jù)庫”,，在“查找范圍”中找到“27.3標(biāo)準(zhǔn)工時數(shù)據(jù).mdb”文件。
步驟４．單擊“打開”按鈕,，出現(xiàn)“選擇表格”對話框,，選擇“標(biāo)準(zhǔn)工時數(shù)據(jù)”。
步驟５．單擊“確定”按鈕,，出現(xiàn)“導(dǎo)入數(shù)據(jù)”對話框,，數(shù)據(jù)的放置位置選擇“現(xiàn)有工作表”選項，并單擊Ａ１單元格,，導(dǎo)入的數(shù)據(jù)將在當(dāng)前工作表的Ａ１單元格順序排列,；也可以根據(jù)用戶需要選擇“新建工作表”，Excel將新建一個工作表,，然后從Ａ１單元格開始插入數(shù)據(jù),；還可以創(chuàng)建數(shù)據(jù)透視表，Excel可以顯示數(shù)據(jù)透視表向?qū)В盟梢灾付〝?shù)據(jù)透視表的布局（有關(guān)數(shù)據(jù)透視表的創(chuàng)建請參見第２８章）,。
步驟６．在“導(dǎo)入數(shù)據(jù)”對話框中單擊“屬性”按鈕,，出現(xiàn)“外部數(shù)據(jù)區(qū)域?qū)傩?#8221;對話框，在“刷新控件”中勾選“打開工作簿時,，自動刷新”復(fù)選框,，這樣，用戶只要一打開這個導(dǎo)入外部數(shù)據(jù)的工作簿,，就會啟用自動刷新,，來自動更新外部數(shù)據(jù),。
步驟７．單擊“確定”按鈕,，返回“導(dǎo)入數(shù)據(jù)”對話框，再單擊“確定”按鈕,，完成設(shè)置,。工作表中將會出現(xiàn)“標(biāo)準(zhǔn)工時數(shù)據(jù)；正在獲取數(shù)據(jù)”的提示行,。稍候幾秒鐘后就會出現(xiàn)導(dǎo)入的外部數(shù)據(jù),。
當(dāng)用戶重新打開已經(jīng)導(dǎo)入外部數(shù)據(jù)的工作簿時就會出現(xiàn)“查詢刷新”對話框。
單擊“啟用自動刷新”工作表將自動對引用的外部數(shù)據(jù)進(jìn)行更新,，單擊“禁用自動刷新”則不對引用的外部數(shù)據(jù)進(jìn)行更新,。
如果用戶在使用外部數(shù)據(jù)時需要對引用的外部數(shù)據(jù)進(jìn)行更新的話，可以用鼠標(biāo)右擊工作表數(shù)據(jù)區(qū)的任意單元格,，在出現(xiàn)的快捷菜單中選擇“刷新數(shù)據(jù)”,。
用戶還可以利用“外部數(shù)據(jù)”工具欄對已經(jīng)導(dǎo)入的外部數(shù)據(jù)重新進(jìn)行設(shè)置。鼠標(biāo)右擊任何可見的工具欄,，在彈出的快捷菜單中選擇“外部數(shù)據(jù)”,。
２７．４ 利用Microsoft Query創(chuàng)建查詢
Microsoft Query充當(dāng)Excel數(shù)據(jù)列表和外部數(shù)據(jù)源之間的橋梁作用,。使用Microsoft Query,，可以連接到外部數(shù)據(jù)源，從外部數(shù)據(jù)源中選擇數(shù)據(jù),，并將該數(shù)據(jù)導(dǎo)入到Excel數(shù)據(jù)列表中,，還可以根據(jù)需要刷新數(shù)據(jù)，與外部數(shù)據(jù)源中的數(shù)據(jù)保持同步,。
?。玻罚矗薄icrosoft Query簡介
使用Microsoft Query，用戶可以選擇數(shù)據(jù)源中所需的數(shù)據(jù)列,，并將其導(dǎo)入Excel,。在Microsoft Query中為特定數(shù)據(jù)庫設(shè)置數(shù)據(jù)源以后，只要想創(chuàng)建查詢，便可以從該數(shù)據(jù)源中選擇并檢索數(shù)據(jù),，而不必重新鍵入所有連接信息,。創(chuàng)建查詢并將數(shù)據(jù)返回到Excel數(shù)據(jù)列表后，Microsoft Query會為Excel工作簿提供查詢和數(shù)據(jù)源信息,，以便用戶在需要刷新數(shù)據(jù)時重新連接到數(shù)據(jù)庫,。實際上Microsoft Query程序承擔(dān)了一種連接外部數(shù)據(jù)庫與Excel數(shù)據(jù)列表的紐帶作用。
用戶可以利用Query來訪問任何安裝了ODBC,、OLE—DB或OLAP驅(qū)動程序的數(shù)據(jù)源,。Excel為下列數(shù)據(jù)源提供了驅(qū)動程序：
Microsoft Office Access,dBASE,Excel,Oracle,Paradox,Microsoft SQL Server OLAP Services,文本文件數(shù)據(jù)庫
２７．４．２ Microsoft Query的有關(guān)術(shù)語
Microsoft Query相關(guān)術(shù)語
２７．４．３ 查詢向?qū)Ш?Query的異同
“查詢向?qū)?#8221;是Query的一種接口,，可以幫助用戶方便地選擇條件和篩選方案,。用“查詢向?qū)?#8221;完成簡單的查詢是一種非常理想的方法，但它并不具備Query的所有功能,。例如,，如果用戶的查詢條件中不僅涉及簡單的比較，還涉及了對數(shù)據(jù)的計算,，或者用戶建立的查詢需要在運(yùn)行時提示使用者輸入一個或多個參數(shù),，就必須使用Query，因為“查詢向?qū)?#8221;無法做到,。同時,，Query的重命名列、篩選不包括在結(jié)果集中的字段,、將結(jié)果集限抽為唯一項,、完成匯總計算等功能也是“查詢向?qū)?#8221;無法完成的。
?。玻罚矗础≈苯邮褂肕icrosoft Query
如果用戶要想直接在Microsoft Query中創(chuàng)建查詢,，可以參照以下步驟進(jìn)行,。
例如，要對“２８．４Microsoft Query檢索數(shù)據(jù)源．ｍｄｂ”創(chuàng)建Microsoft Query查詢,。
步驟１．創(chuàng)建一個空白工作表,，單擊菜單“數(shù)據(jù)”——“導(dǎo)入外部數(shù)據(jù)”——鼠標(biāo)指向“新建數(shù)據(jù)庫查詢”并單擊它，出現(xiàn)“選擇數(shù)據(jù)源”對話框,。
步驟２．由于是對Ａccess數(shù)據(jù)庫文件創(chuàng)建Microsoft Query查詢,，所以在“數(shù)據(jù)庫”選項卡中選擇“Ms Access Database*”文件類型，同時取消“使用‘查詢向?qū)?#8217;創(chuàng)建/編輯查詢”的勾選,，單擊“確定”按鈕,，彈出“選擇數(shù)據(jù)庫”對話框，在“目錄”中選擇“28.4Microsoft Quer
y檢索數(shù)據(jù)源.mdb”的文件所在的位置,。
注意：必須取消“使用‘查詢向?qū)?#8217;創(chuàng)建／編輯查詢”的勾選,，否則將進(jìn)入“查詢向?qū)?#8221;模式，而不是Microsoft Query,。
步驟３．單擊“選擇數(shù)據(jù)庫”對話框內(nèi)的“確定”按鈕,，會彈出Microsoft Query“添加表”對話框。
步驟４．先中“價格查詢”,，單擊“添加”按鈕，Microsoft Query中會出現(xiàn)來自“價格查詢”的字段列表,。完成表的選擇后,，單擊“關(guān)閉”按鈕，即可風(fēng)Microsoft Query查詢窗口,。
步驟５．在“字段下拉列表”中分別單擊“客戶代碼”,、“款式號”、“商品類別”,、“單價”字段,，向“數(shù)據(jù)窗格”中添加數(shù)據(jù)。單擊Microsoft Query工具欄“視圖”——“條件”,，在“條件”字段的下拉列表中選擇“客戶代碼”,，在“值”中輸入“［］”，在彈出的“輸入?yún)?shù)值”對話框中輸入客戶代碼“Ｃ０００００５”（也可以輸入其他的客戶代碼）,，單擊“確定”按鈕,。設(shè)置第二個條件字段為“商品類別”，參數(shù)設(shè)置為“Ａ”,。
步驟６．單擊Microsoft Query工具欄“文件”——“將數(shù)據(jù)返回Microsoft Query Excel”,，在彈出的“導(dǎo)入數(shù)據(jù)”對話框中設(shè)置“數(shù)據(jù)的放置位置”為“現(xiàn)有工作表”的A4單元格。
步驟７．單擊“參數(shù)”按鈕,，在“查詢參數(shù)”對話框中,，“參數(shù)１”的“獲取參數(shù)值的方式”選擇“從下列單元格中獲取數(shù)值”選項，激活編輯框并單擊單元格Ｂ２，同時勾選“單元格值更改時自動刷新”復(fù)選框,；“參數(shù)２”的設(shè)置同“參數(shù)１”,，只是獲取數(shù)值的單元格為Ｂ３。
步驟８．單擊“確定”按鈕,，返回“導(dǎo)入數(shù)據(jù)”對話框,，單擊“確定”按鈕，即可導(dǎo)入外部數(shù)據(jù),，并且可以根據(jù)單元格Ｂ２,、Ｂ３的參數(shù)變換，單元格Ａ４以下的數(shù)據(jù)會被及時刷新,。但是,，如果單元格Ｂ２、Ｂ３沒有輸入查詢參數(shù),，查詢表中將只會顯示一行標(biāo)題,，并沒有查詢數(shù)據(jù)。
步驟９．在單元格Ｂ２中輸入?yún)?shù)“Ｃ０００００２”,，Ｂ３中輸入?yún)?shù)“Ｆ”立即可見查詢的外部數(shù)據(jù),。
步驟１０．要查詢表中的Ａ２單元格輸入“客戶代碼”，Ａ３單元格中輸入“商品類別”并存Ｂ２,、Ｂ３單元格中運(yùn)用數(shù)據(jù)有效性功能,，將“客戶代碼”和“商品類別”作為下拉列表的可選項，可以大大提高用戶的查詢速度,。
|
|
|
