教學課程:將資料匯入 Excel,然後建立資料模型

摘要:   這是系列中第一個教學課程,設計目的是讓您熟悉及習慣使用 Excel 及其內建的資料混合及分析功能。 這些教學課程會從頭開始建置及調整 Excel 活頁簿,建立資料模型,然後使用 Power View 建立令人讚賞的互動式報表。 教學課程的設計會示範在 Excel、樞紐分析表、Power Pivot 和 Power View 中的 Microsoft 商務智慧特性及功能。

附註: 本文描述 Excel 2013 中的資料模型。不過,於 Excel 2013 中導入的資料模型和 Power Pivot 功能也同樣適用於 Excel 2016。

在這些教學課程中,您會學習如何在 Excel 中匯入與瀏覽資料、使用 Power Pivot 建立及調整資料模型,以及使用 Power View 建立可以發佈、保護及共用的互動式報表。

本系列的教學課程如下:

  1. 將資料匯入 Excel 2013,然後建立資料模型

  2. 使用 Excel、Power Pivot 和 DAX 擴充資料模型關聯

  3. 建立以地圖為基礎的 Power View 報表

  4. 併入網際網路資料與設定 Power View 報表預設值

  5. 建立令人讚嘆的 Power View 報表 - 第 1 部分

  6. 建立令人讚賞的 Power View 報表- 第 2 部分

在此教學課程中,一開始您會使用空白的 Excel 活頁簿。

此教學課程中的各節如下所示:

從資料庫匯入資料

從試算表匯入資料

使用複製及貼上匯入資料

建立所匯入資料之間的關聯性

重點複習和測驗

本教學課程結尾有一項測驗,可供您測驗學習成效。

此教學課程系列會使用說明奧運獎牌、主辦國家/地區及各種奧運體育賽事的相關資料。我們建議您依序進行每個教學課程。此外,教學課程使用的是已啟用 Power Pivot 的 Excel 2013。如需 Excel 2013 的詳細資訊,請按一下這裡。如需啟用 Power Pivot 的相關指示,請按一下這裡


從資料庫匯入資料

我們會使用空白的活頁簿開始進行此教學課程。 本節的目標是連線到外部資料來源,並將這些資料匯入至 Excel 以便進一步分析。

首先,從網際網路下載部分資料。 這些資料會說明奧運獎牌,而且是 Microsoft Access 資料庫。

  1. 按一下下列連結以下載此教學課程系列期間要使用的檔案。將這四個檔案下載至可輕鬆存取的位置,例如 [下載] 或 [我的文件],或是您建立的新資料夾:
    > OlympicMedals.accdb Access 資料庫
    > OlympicSports.xlsx Excel 活頁簿
    > Population.xlsx Excel 活頁簿
    > DiscImage_table.xlsx Excel 活頁簿

  2. 在 Excel 2013 中開啟一個空白活頁簿。

  3. 按一下 [資料] > [取得外部資料] > [從 Access]。 功能區會根據活頁簿的寬度動態調整,因此功能區上的命令看起來可能與下列畫面稍有不同。 第一個畫面顯示寬活頁簿的功能區,第二個圖像顯示已調整大小為只占用部分畫面的活頁簿。

    從 Access 匯入資料

    使用小型功能區從 Access 匯入資料


  4. 選取您下載的 OlympicMedals.accdb 檔案,然後按一下 [開啟]。 下列 [選取資料表] 視窗隨即出現,並顯示資料庫中找到的資料表。 資料庫中的資料表類似 Excel 中的工作表或表格。 核取 [啟用選取多個表格] 方塊並選取所有表格。 然後按一下 [確定]

    [選取資料表] 視窗

  5. [匯入資料] 視窗隨即出現。

    附註: 請注意,如下方畫面所示,視窗底部的核取方塊可讓您 [新增此資料至資料模型]。 當您同時匯入或使用兩個或多個資料表時,系統會自動建立資料模型。 資料模型會整合資料表,並使用樞紐分析表、Power Pivot 和 Power View 進行廣泛的分析。 當您從資料庫匯入資料表時,會使用這些資料表之間的現有資料庫關聯,在 Excel 中建立資料模型。 資料模型在 Excel 中是透明的,不過您可以直接使用 Power Pivot 增益集來檢視和修改。 本教學課程稍後會更詳細討論資料模型。


    選取 [樞紐分析表] 選項,將資料表匯入至 Excel,並備妥樞紐分析表用以分析匯入的資料表,然後按一下 [確定]

    [匯入資料] 視窗

  6. 一旦匯入資料,即會使用匯入的表格建立樞紐分析表。

    空白的樞紐分析表

一旦將資料匯入 Excel 且自動建立資料模型之後,即表示您已準備好探索資料。

使用樞紐分析表瀏覽資料

使用樞紐分析表就可以輕鬆地探索匯入的資料。 在樞紐分析表中,從表格 (類似您剛才從 Access 資料庫匯入的資料表) 拖曳欄位 (類似 Excel 中的欄) 到樞紐分析表的不同區域以調整資料的呈現方式。 樞紐分析表具有四個區域:[篩選][欄][列][值]

四個樞紐分析表欄位區域

您可能需要進行一些試驗,以確定應該將欄位拖曳至哪個區域。 您可以視需要從表格拖曳為許多或幾個欄位,直到您喜歡樞紐分析表呈現資料的方式為止。 將欄位拖曳到樞紐分析表的不同區域即可隨意探索;當您排列樞紐分析表中的欄位時,基礎資料不會受影響。

現在讓我們探索樞紐分析表中的奧運獎牌資料,一開始先看看使用分項、金牌種類和運動員的國家或地區整理的奧運獎牌得主吧。

  1. [樞紐分析表欄位] 中,按一下 [獎牌] 表格旁邊的箭號加以展開。 在展開的 [獎牌] 表格中尋找 NOC_CountryRegion 欄位,並將它拖曳到的 [欄] 區域。 NOC 代表 National Olympic Committees (國家奧運委員會),這是屬於國家或地區的組織單位。

  2. 接下來,從 [分項] 表格將分項拖曳到 [列] 區域。

  3. 篩選 [分項] 使其只顯示五個運動:[射箭]、[跳水]、[擊劍]、[花式滑冰] 和 [競速滑冰]。 您可以從 [樞紐分析表欄位] 區域內,或從樞紐分析表中本身的 [列標籤] 篩選執行這項作業。

    1. 按一下樞紐分析表中的任何位置,以確認已選取 Excel 樞紐分析表。 在已展開 [分項] 表格的 [樞紐分析表欄位] 清單中,將游標暫留在 [分項] 欄位上,欄位右側隨即顯示下拉式箭號。 按一下下拉式清單,按一下 [(全選)] 以移除所有選取項目,然後向下捲動,並選取 [射箭]、[跳水]、[擊劍]、[花式滑冰] 和 [競速滑冰]。 按一下 [確定]

    2. 或是在樞紐分析表的 [列標籤] 區段中,按一下樞紐分析表中 [列標籤] 旁邊的下拉式清單,按一下 [(全選)] 以移除所有選取項目,然後向下捲動,並選取 [射箭]、[跳水]、[擊劍]、[花式滑冰] 和 [競速滑冰]。 按一下 [確定]

  4. [樞紐分析表欄位] 中,從 [獎牌] 表格將獎牌拖曳到 [值] 區域。 因為 [值] 必須是數值,Excel 會自動將 [獎牌] 變更為 [獎牌數]

  5. [獎牌] 表格,再次選取 [獎牌],將其拖曳到 [篩選] 區域。

  6. 現在來篩選樞紐分析表以顯示總獎牌超過 90 面的國家或地區。 方法如下:

    1. 在樞紐分析表中,按一下 [欄標籤] 右邊的下拉式清單。

    2. 選取 [值篩選],然後選取 [大於...]

    3. 在最後一個欄位中 (右側) 輸入 90。 按一下 [確定]
      [值篩選] 視窗

樞紐分析表看起來類似下列畫面。

更新樞紐分析表

現在,您輕輕鬆鬆就做出一個含有三個不同表格之欄位的基本樞紐分析表。這項工作之所以如此輕鬆,是因為表格之間預先存在關聯。由於來源資料庫已存在表格關聯,而且您在一次作業中即匯入所有表格,因此 Excel 可以在資料模型中重建那些表格關聯。

但萬一資料是來自不同來源,或是後來才匯入,這時候該怎麼辦呢?通常您可以根據相符欄來與新資料建立關聯。在接下來的步驟當中,您將匯入其他表格,並了解如何建立新的關聯。

從試算表匯入資料

現在,從其他來源 (這次從現有的活頁簿) 匯入資料,然後指定現有資料與新資料之間的關聯。 關聯性可讓您分析 Excel 中的資料集合,並且從匯入的資料建立有趣及擬真的視覺效果。

首先建立空白的工作表,然後從 Excel 活頁簿匯入資料。

  1. 插入新的 Excel 工作表,並將其命名為 [運動]

  2. 瀏覽含有所下載範例資料檔案的資料夾,然後開啟 [OlympicSports.xlsx]

  3. 選取並複製 [工作表1] 中的資料。 如果您選取包含資料的儲存格 (例如儲存格 A1),您也可以按 Ctrl + A 以選取所有相鄰的資料。 關閉 OlympicSports.xlsx 活頁簿。

  4. [運動] 工作表上,將游標放在儲存格 A1中並貼上資料。

  5. 若資料仍然醒目提示,請按下 Ctrl + T 將資料格式化為表格。 您也可以從功能區選取 [常用] > [格式化為表格],即可將資料格式化為表格。 由於資料有標題,請在顯示的 [建立表格] 視窗中選取 [有標題的表格],如下所示。

    [建立表格] 視窗

    將資料格式化為表格有許多優點。 您可以指定表格的名稱,可讓您更易於識別。 您也可以建立表格之間的關係,以便在樞紐分析表、Power Pivot 和 Power View 中進行探索與分析。

  6. 為表格命名。 在 [表格工具] > [設計] > [內容] 中,找到 [表格名稱] 欄位並輸入 [運動]。 活頁簿看起來會類似下列畫面。
    在 Excel 中為表格命名

  7. 儲存活頁簿。

使用複製及貼上匯入資料

現在我們已經從 Excel 活頁簿匯入資料,接下來要從在網頁上找到的表格或任何可以複製及貼到 Excel 的其他來源匯入資料。 在下列步驟中,您可以在表格中新增奧運主辦城市。

  1. 插入新的 Excel 工作表,並將其命名為 [主辦者]

  2. 選取並複製下列表格,包括表格標題。

區/鄉/鎮/市

NOC_CountryRegion

Alpha-2 代碼

年度

季節

墨爾本/斯德哥爾摩

AUS

AS

1956

夏季

雪梨

AUS

AS

2000

夏季

因斯布魯克

AUT

AT

1964

冬季

因斯布魯克

AUT

AT

1976

冬季

安特衛普

BEL

BE

1920

夏季

安特衛普

BEL

BE

1920

冬季

蒙特婁

CAN

CA

1976

夏季

普萊西德湖

CAN

CA

1980

冬季

卡加利

CAN

CA

1988

冬季

聖莫里茲

SUI

SZ

1928

冬季

聖莫里茲

SUI

SZ

1948

冬季

北京

CHN

CH

2008

夏季

柏林

GER

GM

1936

夏季

加米許-帕騰基興

GER

GM

1936

冬季

巴塞隆納

ESP

SP

1992

夏季

赫爾辛基

FIN

FI

1952

夏季

巴黎

FRA

FR

1900

夏季

巴黎

FRA

FR

1924

夏季

夏慕尼

FRA

FR

1924

冬季

格爾諾伯勒

FRA

FR

1968

冬季

阿爾貝維爾

FRA

FR

1992

冬季

倫敦

GBR

UK

1908

夏季

倫敦

GBR

UK

1908

冬季

倫敦

GBR

UK

1948

夏季

慕尼黑

GER

DE

1972

夏季

雅典

GRC

GR

2004

夏季

科爾蒂納丹佩佐

ITA

IT

1956

冬季

羅馬

ITA

IT

1960

夏季

都靈

ITA

IT

2006

冬季

東京

JPN

JA

1964

夏季

札幌

JPN

JA

1972

冬季

長野

JPN

JA

1998

冬季

首爾

KOR

KS

1988

夏季

墨西哥

MEX

MX

1968

夏季

阿姆斯特丹

NED

NL

1928

夏季

奧斯陸

NOR

NO

1952

冬季

利勒哈默爾

NOR

NO

1994

冬季

斯德哥爾摩

SWE

SW

1912

夏季

聖路易

USA

US

1904

夏季

洛杉磯

USA

US

1932

夏季

普萊西德湖

USA

US

1932

冬季

斯闊谷

USA

US

1960

冬季

莫斯科

URS

RU

1980

夏季

洛杉磯

USA

US

1984

夏季

亞特蘭大

USA

US

1996

夏季

鹽湖城

USA

US

2002

冬季

塞拉耶佛

YUG

YU

1984

冬季

  1. 在 Excel 中,將游標放在 [主辦者] 工作表的儲存格 A1 中並貼上資料。

  2. 將資料格式化為表格。 如本教學課程中所述,您按下 Ctrl + T 即可格式化表格,或是從 [常用] > [格式化為表格] 進行。 由於資料有標題,請在出現的 [建立表格] 視窗中選取 [有標題的表格]

  3. 為表格命名。 在 [表格工具] > [設計] > [內容] 中,找到 [表格名稱] 欄位並輸入 [主辦者]

  4. 選取 [年度] 欄,並且從 [常用] 索引標籤將其格式化為小數位數為 0 的 [數字]

  5. 儲存活頁簿。 該活頁簿看起來會類似下列畫面。

[主辦城市] 表格

現在您已經有具備表格的 Excel 活頁簿,您可以建立表格之間的關聯。 建立表格之間的關聯可讓您混合來自兩個表格的資料。

建立所匯入資料之間的關聯性

您可以從匯入的表格立即開始使用樞紐分析表中的欄位。如果 Excel 無法判斷如何將欄位融入樞紐分析表,則必須與現有資料模型建立關聯。在下列步驟中,了解如何在您從不同來源匯入的資料之間建立關聯。

  1. [工作表1] 上,按一下 [樞紐分析表欄位] 頂端的 [全部],檢視可用表格的完整清單,如下列畫面所示。
    按一下 [樞紐分析表欄位] 中的 [所有],以顯示所有可用的表格

  2. 捲動清單,查看您剛才新增的表格。

  3. 展開 [運動],然後選取 [運動],將其加入至樞紐分析表。 請注意,Excel 會提示您建立關聯,如下列畫面所示。
    樞紐分析表欄位中的 [建立...] 關聯提示

    此通知發生的原因是您從不屬於基礎資料模型的表格使用欄位。 將表格新增至資料模型的方法之一是與已在資料模型中的表格建立關聯。 若要建立關聯,其中一個表格的欄必須具有唯一的非重複值。 在範例資料中,從資料庫匯入的 [分項] 表格包含使用運動代碼 (稱為 SportID) 的欄位。 這些相同的運動代碼出現成為所匯入 Excel 資料的欄位。 現在來建立關聯吧。

  4. 按一下反白的 [樞紐分析表欄位] 區域中的 [建立...][建立關聯] 對話方塊隨即開啟,如下列畫面所示。

    [建立關聯] 視窗

  5. [表格] 的下拉式清單中,選擇 [分項]

  6. [欄 (外部)] 中,選擇 [SportID]

  7. [關聯表格] 中,選擇 [Stores]

  8. [關聯欄 (主要)] 中,選擇 [SportID]

  9. 按一下 [確定]

樞紐分析表會變更以反映新的關聯。 但是樞紐分析表的外觀看起來不太正確,這是因為 [列] 區域中的欄位順序所致。 [分項] 是特定運動的子類別,但由於我們在 [列] 區域中將 [分項] 排列於 [運動] 上方,因此未正確地加以組織。 下列畫面會顯示此錯誤順序。
順序錯誤的樞紐分析表

  1. [列] 區域中,將 [運動] 移至 [分項] 上方。 這樣好多了,樞紐分析表會使用您想看見的方式呈現資料,如下列畫面所示。

    順序修正後的樞紐分析表

Excel 隨即會在幕後建立一個資料模型,該模型可用於整個活頁簿內任何樞紐分析表、樞紐分析圖、Power Pivot 或 Power View 報表中。 表格關聯性是資料模型的基礎,可用以判斷導覽和計算路徑。

在下一個教學課程 (使用 Excel 2013、Power Pivot 和 DAX 延伸資料模型關聯性) 中,您會以目前所學到的內容為基礎,並且使用功能強大的視覺化 Excel 增益集 (稱為 Power Pivot ) 逐步延伸資料模型。 您也可以學習如何計算表格中的欄,以及使用該計算結果欄,以便將毫無關聯的表格新增至資料模型。

重點複習和測驗

複習所學內容

您現在有一個 Excel 活頁簿,其中包含存取多個表格資料的樞紐分析表,而且有幾個表格是由您個別匯入。您已了解如何從資料庫、從另一個 Excel 活頁簿,以及從複製資料並貼到 Excel 的方式進行匯入。

若要讓這些資料共同運作,則必須建立表格關聯,讓 Excel 用來關聯不同的列。您也已了解讓表格中的欄與另一個表格中的資料相互關聯,是建立關聯性及查閱關聯列的必要條件。

您已準備好要進行此系列的下一個教學課程。 連結如下:

使用 Excel 2013、Power Pivot 和 DAX 延伸資料模型關聯性

小測驗

想看看你還記得自己學到了什麼? 開始試試看吧。 下列測驗會反白顯示您在此教學課程中學到的特性、功能或要求。 您可在頁面底部找到答案。 祝您好運!

問題 1:為什麼將匯入的資料轉換成表格很重要?

A:您不需要將其轉換成表格,因為所有匯入資料都會自動轉換為表格。

B:如果您將匯入資料轉換成表格,則會將其排除在資料模型外。 只有排除在資料模型外時,才可供樞紐分析表、Power Pivot 和 Power View 使用。

C:如果您將匯入的資料轉換成表格,則這些表格可以包含在資料模型中,且可供樞紐分析表、Power Pivot 和 Power View 使用。

D:您無法將匯入的資料轉換成表格。

問題 2:您可以將下列何種資料來源匯入至 Excel,並包含在資料模型中?

A:Access 資料庫及其他許多資料庫。

B:現有的 Excel 檔案。

C:您可以複製並貼到 Excel 並格式化為表格的任何項目,包括網站中的資料表格、文件或可貼到 Excel 的任何其他內容。

D:以上皆是。

問題 3:在樞紐分析表中,當您重新排列四種樞紐分析表欄位區域的欄位時,會發生什麼情況?

A:不會發生任何事。一旦您將欄位放置在樞紐分析表欄位區域,即無法重新排列欄位。

B:樞紐分析表會變更格式以反映版面配置,但基礎資料不受影響。

C:樞紐分析表會變更格式以反映版面配置,而所有的基礎資料會永久變更。

D:基礎資料會變更,隨即產生新的資料集。

問題 4:在表格之間建立關聯時的必要條件為何?

A:兩個表格的任何欄都不可以包含唯一且非重複的值。

B:其中一個表格必須是 Excel 活頁簿的一部分。

C:不可以將欄轉換為表格。

D:以上皆非。

測驗答案

  1. 正確答案:C

  2. 正確答案:D

  3. 正確答案:B

  4. 正確答案:D

附註: 本教學課程系列中的資料與圖像是根據以下內容:

  • Guardian News & Media Ltd. 所提供的奧運資料集

  • CIA Factbook (cia.gov) 所提供的旗幟影像

  • 世界銀行 (worldbank.org) 所提供的人口資料

  • Thadius856 與 Parutakupiu 所設計的奧林匹克運動設計標誌

擴展您的技能
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與其中一位 Office 支援專員連絡以深入了解您的意見。

×