将外部资料库汇入Excel工作表.ppt

第 21 章 Excel 與外部 資料庫的結合 應用實例說明 許多中、小企業機構都會使用 Office 套裝軟體來處理不同的辦公事務, 譬如一般行政人員會使用 Word 來編輯文件, 財會人員會使用 Excel 來計算公司的資產、折舊..., 業務部門則是將銷售資料登錄到 Access 資料庫中保存..., 每個軟體都有不同的適用範圍, 而平常也都是各自獨立作業。 應用實例說明 但是, 本章將會打破這層隔閡, 帶領您將 Excel 的視野範圍擴大到外部資料, 把 Access 的資料庫匯入到 Excel 工作表中進行計算、分析, 並為公司製作出具有決策參考價值的報表。 應用實例說明 浦元家電是一家專門生產家庭電器用品的公司, 該公司的總經理指示業務部門應盡速完成以下這 3 份報表, 以便在業務會議中做為研究檢討的參考資料： 92 年度的完整銷售清單：按照日期排序, 列出 92 年度的每一筆銷售明細。 銷售數量與銷貨收入統計表：各別計算每項產品的總銷售數量與銷售金額。 應用實例說明 區域 — 產品銷售量統計圖：分區統計 92 年度各項產品的銷售數量, 並繪製成長條圖。 浦元家電的銷售記錄目前都是逐筆建立在 Access 的資料庫當中, 如下圖所示： 應用實例說明 應用實例說明 Product.mdb 當中雖有完整的銷售記錄, 但一筆筆的資料並無法提供任何的決策資訊, 必須加以匯總、整理才能夠製作出總經理所需的報表。 Excel 可以將數種外部資料直接匯入到工作表中進行處理,例如 Access、FoxPro、Dbase、及文字檔格式的資料, 都可以載入至工作表, 做進一步的處理與分析。 應用實例說明 本章提要 將外部資料庫匯入 Excel 工作表 更新匯入的外部資料 匯入外部資料製作樞紐分析表 使用外部資料繪製樞紐分析圖 將外部資料庫匯入 Excel 工作表 匯入外部資料 建立外部資料庫查詢 匯入外部資料 首先, 我們來為業務部門製作第一份報表：92 年度銷售清單。請開啟一份空白的的活頁簿, 然後選取 Sheet1 的 A1 儲存格, 執行『資料/匯入外部資料/ 匯入資料』命令： 匯入外部資料 匯入外部資料 匯入外部資料 匯入外部資料 從『我的資料來源』 重新匯入資料 使用匯入資料功能之後, Excel 會自動記錄這次所匯入的外部資料來源,並存放在我的資料來源資料夾中。 當您下次重新執行『資料/ 匯入外部資料/匯入資料』命令時, 就會自動切換到預設的我的資料來源資料夾, 並可在交談窗中看到 Excel 建立好的資料庫連結檔, 只要開啟該檔案便可重新匯入相同的外部資料： 從『我的資料來源』 匯入資料 匯入外部資料 雖然透過執行『資料/匯入外部資料/匯入資料』命令, 三兩下子就把外部資料載入工作表了。不過經理需要的是 92 年度的銷售記錄, 而目前工作表中連同 93 年度的資料也都匯進來了。 底下要告訴您一個方法, 讓您可以事先篩選需要的資料。 建立外部資料庫查詢 要篩選局部的外部資料進行匯入, 就必須改用新增資料庫查詢的方式來做匯入。請您切換到另一張空白的工作表, 選取 A1 儲存格, 然後跟著底下的說明進行操作： 首先是選擇要匯入的外部資料來源。請執行『資料/ 匯入外部資料/新增資料庫查詢』命令, 開啟選擇資料來源交談窗： 建立外部資料庫查詢 第一次執行此項功能時, 若出現尚未安裝的提示訊息, 請放入 Office 2003 光碟, 將此項功能安裝完成。 建立外部資料庫查詢 建立外部資料庫查詢 在查詢精靈 — 選取資料欄交談窗中指定需要匯入的資料欄位： 建立外部資料庫查詢 接著在查詢精靈 — 篩選資料交談窗中設定資料篩選條件。在此我們要來篩選 92 年的出貨記錄： 建立外部資料庫查詢 建立外部資料庫查詢 設定日期篩選條件的時候, 必須配合來源資料的日期格式, 例如本例的出貨日期 欄使用西元日期, 因此查詢精靈 — 篩選資料交談窗中也要使用西元日期格式。 在查詢精靈 – 排列順序交談窗中設定資料排序方式。在此我們要依照 出貨日期 做遞增排序, 若出貨日期相同, 再依照品名 做遞增排序： 建立外部資料庫查詢 建立外部資料庫查詢 最後, 選擇要將資料匯入到工作表中, 總經理所要的報表就即將呈現在您眼前了： 建立外部資料庫查詢 建立外部資料庫查詢 接下來您還可以繼續為工作表做必要的美化與修飾！例如在最上方插入空白列, 然後加上報表標題, 或是將 出貨日期 欄的格式修改成您喜好的日期