將資料從 Excel 移至 Access

本文說明如何將您的資料從 Excel 移至 Access 並將資料轉換為關聯式資料表,讓您可以使用 Microsoft Excel 和 Access 一起存取。 總之,Access 最適合用來捕獲、儲存、查詢及共用資料,而 Excel 最適合用於計算、分析及視覺化資料。

兩篇文章(使用 access 或 Excel 管理您的資料,以及使用 excel access的十大理由),討論哪個程式最適合特定工作,以及如何使用 Excel 與 Access 共同建立實用的方案。

當您將資料從 Excel 移至 Access 時,處理常式有三個基本步驟。

三個基本步驟

附註: 如需 Access 中資料模型與關聯性的詳細資訊,請參閱資料庫設計的基本概念

步驟1:將資料從 Excel 匯入到 Access

如果您需要一些時間來準備並清除資料,匯入資料是一個可讓您更順利進行的作業。 匯入資料就像是移至新的首頁。 如果您在移動前先清除並整理您的財產,就能更輕鬆地在您的新家中進行結算。

匯入前先清除資料

在您將資料匯入 Access 之前,請先在 Excel 中進行下列工作:

  • 將包含非原子資料的儲存格(也就是一個儲存格中的多個值)轉換為多個資料行。 例如,「技能」欄中包含多個技能值的儲存格,例如「c # 程式設計」、「VBA 程式設計」和「Web 設計」,都應該分解成不同的欄,只包含一個技能值。

  • 使用 [修剪] 命令來移除前置、尾隨及多個內嵌空格。

  • 移除非列印字元。

  • 尋找並修正拼寫及標點符號錯誤。

  • 移除重複的列或重複的欄位。

  • 確定資料欄不含混合格式,尤其是格式化為文字的數位或數位格式的日期。

如需詳細資訊,請參閱下列 Excel 說明主題:

附註: 如果您的資料清理需求很複雜,或者您沒有時間或資源來自行自動化程式,您可以考慮使用協力廠商的供應商。 如需詳細資訊,請在網頁瀏覽器中搜尋您最愛搜尋引擎的「資料清理軟體」或「資料品質」。

在匯入時選擇最佳的資料類型

在 Access 中的匯入作業期間,您想要進行適當的選擇,以便您收到幾個(如果有的話)需要手動干預的轉換錯誤。 下表摘要說明在您將資料從 Excel 匯入到 Access 時,Excel 數位格式及 Access 資料類型的轉換方式,並提供在 [匯入試算表嚮導] 中選擇的最佳資料類型秘訣。

Excel 數位格式

Access 資料類型

註解

最佳作法

文字

文字、備忘

Access 文字資料類型會儲存最多255個字元的數位中繼資料。 [Access 備忘錄] 資料類型會儲存最多65535個字元的數位中繼資料。

選擇 [備忘錄],避免截斷任何資料。

Number、百分比、分式、科學

Number

Access 有一個數值資料類型,這會根據 [欄位大小] 屬性(Byte、Integer、Long 整型、Single、Double、Decimal)而變化。

選擇 [Double] (加倍),避免發生任何資料轉換錯誤。

日期

日期

Access 和 Excel 都使用相同的序列日期數位來儲存日期。 在 Access 中,日期範圍較大:從-657434 (西元100年1月1日)到2958465(西元年12月 31 9999 日)。

因為 Access 無法辨識1904日期系統(在適用于 Macintosh 的 Excel 中使用),所以您需要轉換 Excel 或 Access 中的日期,避免混亂。

如需詳細資訊,請參閱變更日期系統、格式或兩位數的年份轉譯,以及匯入或連結至 Excel 活頁簿中的資料

選擇 [日期]。

時間

時間

Access 和 Excel 都使用相同的資料類型來儲存時間值。

選擇 [時間],通常是預設值。

貨幣、會計

貨幣

在 Access 中,Currency 資料類型會將資料儲存為含四個小數位數精度的8個位元組數,並用於儲存財務資料,並防止值的舍入。

選擇 [貨幣],通常是預設值。

布林值

是/否

Access 會將-1 用於所有 [是] 值,而0則是所有沒有值,而 Excel 會將所有 TRUE 值設為1,而所有的 FALSE 值則會使用0。

選擇[是/否],這會自動轉換基礎值。

超連結

超連結

Excel 和 Access 中的超連結包含您可以按一下和追蹤的 URL 或網址。

選擇 [超連結],否則 Access 預設會使用 [文字] 資料類型。

資料在 Access 中之後,您就可以刪除 Excel 資料。 不要忘記在刪除原始 Excel 活頁簿之前先將其備份。

如需詳細資訊,請參閱 Access 協助主題匯入或連結至 Excel 活頁簿中的資料

以簡單的方式自動附加資料

Excel 使用者常見的問題就是將相同欄的資料附加到一個大型工作表中。 例如,您可以在 Excel 中開始使用資產追蹤解決方案,但現在已增長到包含許多工作組和部門的檔案。 此資料可能位於不同的工作表和活頁簿中,或是從其他系統進行資料摘要的文字檔。 沒有使用者介面命令或簡單的方法可在 Excel 中附加相似的資料。

最佳解決方案是使用 Access,您可以使用 [匯入試算表嚮導],輕鬆地將資料匯入並附加到一個資料表中。 此外,您可以在一個資料表中附加許多資料。 您可以儲存匯入作業、將其新增為排程的 Microsoft Outlook 工作,甚至使用宏來自動處理常式。

步驟2:使用 [資料表分析器] 嚮導將資料標準化

乍一看,逐步完成正常化資料的程式,可能看起來會是一項令人望而生畏的工作。 幸運的是,在 Access 中將資料表標準化,是一種更簡單的程式,感謝使用 [資料表分析器] 嚮導。

[資料表分析] 精靈

1. 將選取的資料行拖曳到新資料表,並自動建立關聯

2. 使用按鈕命令來重新命名資料表、新增主鍵、將現有欄設為主鍵,以及復原上一個動作

您可以使用此嚮導來執行下列動作:

  • 將表格轉換成一組較小的表格,並自動建立資料表之間的主鍵與外鍵關係。

  • 將主鍵新增至包含唯一值的現有欄位,或建立使用 [自動編號] 資料類型的新 ID 欄位。

  • 自動建立關聯,以利用串聯更新強制進行參照完整性。 不會自動新增階層式刪除來避免意外刪除資料,但您可以稍後輕鬆地新增串聯刪除。

  • 在新資料表中搜尋多餘或重複的資料(例如,有兩個不同電話號碼的相同客戶),並視需要更新。

  • 備份原始資料表,然後將 "_OLD" 附加到它的名稱來重新命名。 接著,您可以使用原始資料表名稱來建立重建原始資料表的查詢,讓任何以原始資料表為基礎的現有表單或報表,都能與新的資料表結構搭配使用。

如需詳細資訊,請參閱使用 [資料表分析] 將您的資料正常化

步驟3:連線至從 Excel 存取資料

在 Access 中將資料標準化,且已建立可重新建立原始資料的查詢或資料表之後,就很簡單,只要連線到 Excel 中的 Access 資料即可。 您的資料現在是作為外部資料源的存取,因此可透過資料連線來連接至活頁簿,這是用來尋找、登入及存取外部資料源的資訊容器。 連線資訊會儲存在活頁簿中,也可以儲存在連線檔案中,例如 Office 資料連線(ODC)檔案(.odc 檔案副檔名)或資料來源名稱檔案(.dsn 副檔名)。 在您連線到外部資料之後,您也可以在 Access 中的資料更新時,自動重新整理(或更新)您的 Excel 活頁簿。

如需詳細資訊,請參閱從外部資料源(Power Query)匯入資料

在 Access 中取得資料

本節會逐步引導您在正常化資料的下列階段:將銷售人員和網址列中的值分解成其最小的原子區段、將相關的主體分隔到各自的資料表中,從 Excel 複製並貼上這些表格至Access,建立新建立的 Access 資料表之間的主要關聯,並在 Access 中建立並執行一個簡單的查詢,以傳回信息。

非標準化形式的範例資料

下欄工作表的 [銷售人員] 欄和 [位址] 欄中包含非原子值。 這兩個數據行都應該分割成兩個或更多個不同的欄。 此工作表也包含有關銷售員、產品、客戶及訂單的資訊。 您也應該將此資訊進一步依主旨分割成不同的資料表。

銷售人員

訂單識別碼

訂單日期

產品識別碼

Qty

Price

客戶名稱

地址

手機

Li、Yale

2349

3/4/09

C-789

3

$7.00

Fourth Coffee

7007 Cornell 聖雷德蒙,華盛頓98199

425-555-0201

Li、Yale

2349

3/4/09

C-795

6

$9.75

Fourth Coffee

7007 Cornell 聖雷德蒙,華盛頓98199

425-555-0201

凱倫、王亞力

2350

3/4/09

A-2275

2

$16.75

冒險工廠

1025哥倫比亞圓圈 Kirkland,WA 98234

425-555-0185

凱倫、王亞力

2350

3/4/09

F-198

6

$5.25

冒險工廠

1025哥倫比亞圓圈 Kirkland,WA 98234

425-555-0185

凱倫、王亞力

2350

3/4/09

B-205

1

$4.50

冒險工廠

1025哥倫比亞圓圈 Kirkland,WA 98234

425-555-0185

Hance,Jim

2351

3/4/09

C-795

6

$9.75

Contoso, Ltd.

2302 Harvard 平均 Bellevue,WA 98227

425-555-0222

Hance,Jim

2352

3/5/09

A-2275

2

$16.75

冒險工廠

1025哥倫比亞圓圈 Kirkland,WA 98234

425-555-0185

Hance,Jim

2352

3/5/09

D-4420

3

$7.25

冒險工廠

1025哥倫比亞圓圈 Kirkland,WA 98234

425-555-0185

Koch、簧片

2353

3/7/09

A-2275

6

$16.75

Fourth Coffee

7007 Cornell 聖雷德蒙,華盛頓98199

425-555-0201

Koch、簧片

2353

3/7/09

C-789

5

$7.00

Fourth Coffee

7007 Cornell 聖雷德蒙,華盛頓98199

425-555-0201

最小部分中的資訊:原子資料

使用資料在這個範例中,您可以使用 Excel 中的 [文字到欄] 命令,將儲存格的「原子」部分(例如街道位址、城市、州和郵遞區號)分成不同的欄。

下表顯示在分割後的同一張工作表中的新欄,以使所有值成為原子。 請注意,[銷售人員] 欄中的資訊已分割為 [姓氏] 和 [名字] 欄,而 [位址] 欄中的資訊已分割為 [街道位址]、[城市]、[州] 和 [郵遞區號] 欄。 此資料為「第一個標準格式」。

姓氏

名字

 

街道地址

城市

郵遞區號

離子

Yale

2302 Harvard 的平均

貝利夫市

WA

98227

凱倫

王亞力

1025哥倫比亞圓圈

柯克蘭

WA

98234

Hance

俊文

2302 Harvard 的平均

貝利夫市

WA

98227

Koch

簧片

7007 Cornell 聖雷德蒙

雷蒙市

WA

98199

在 Excel 中將資料分解成已組織的主題

下列幾個範例資料表格會在已分割為銷售員、產品、客戶及訂單的表格之後,在 Excel 工作表中顯示相同的資訊。 [表格設計] 並非最終版本,但會在右軌上。

[銷售人員] 表格只包含銷售人員的相關資訊。 請注意,每筆記錄都有唯一的識別碼(銷售人員識別碼)。 [銷售人員識別碼] 值將用於 [訂單] 資料表中,以將訂單連線至 [銷售人員]。

人員

銷售人員識別碼

姓氏

名字

101

離子

Yale

103

凱倫

王亞力

105

Hance

俊文

107

Koch

簧片

[產品] 資料表只包含產品的相關資訊。 請注意,每個記錄都有唯一的識別碼(產品識別碼)。 [產品識別碼] 值將用於將產品資訊連接至 [訂單詳細資料] 資料表。

Products

產品識別碼

Price

A-2275

16.75

B-205

4.50

C-789

7.00

C-795

9.75

D-4420

7.25

F-198

5.25

[客戶] 資料表只包含客戶的相關資訊。 請注意,每個記錄都有唯一識別碼(客戶識別碼)。 [客戶識別碼] 值將用於將客戶資訊連接至 [訂單] 資料表。

客戶

客戶識別碼

名稱

街道地址

城市

郵遞區號

手機

1001

Contoso, Ltd.

2302 Harvard 的平均

貝利夫市

WA

98227

425-555-0222

1003

冒險工廠

1025哥倫比亞圓圈

柯克蘭

WA

98234

425-555-0185

1005

Fourth Coffee

7007 Cornell St

雷蒙市

WA

98199

425-555-0201

[訂單] 資料表包含訂單、銷售人員、客戶和產品的相關資訊。 請注意,每個記錄都有唯一的識別碼(訂單識別碼)。 此表格中的部分資訊需要分割為額外的資料表,其中包含訂單詳細資料,因此 [訂單] 資料表只包含四欄:唯一訂單識別碼、訂單日期、銷售員識別碼及客戶 ID。 這裡顯示的表格尚未分割成 [訂單詳細資料] 資料表。

訂單

訂單識別碼

訂單日期

銷售人員識別碼

客戶識別碼

產品識別碼

Qty

2349

3/4/09

101

1005

C-789

3

2349

3/4/09

101

1005

C-795

6

2350

3/4/09

103

1003

A-2275

2

2350

3/4/09

103

1003

F-198

6

2350

3/4/09

103

1003

B-205

1

2351

3/4/09

105

1001

C-795

6

2352

3/5/09

105

1003

A-2275

2

2352

3/5/09

105

1003

D-4420

3

2353

3/7/09

107

1005

A-2275

6

2353

3/7/09

107

1005

C-789

5

訂單詳細資料(例如產品識別碼和數量)會移出 [訂單] 資料表,並儲存在名為 [訂單詳細資料] 的資料表中。 請記住,有9個訂單,所以在這個表格中有9筆記錄。 請注意,[訂單] 資料表具有唯一識別碼(訂單識別碼),它會從 [訂單詳細資料] 資料表中引用。

[訂單] 資料表的最終設計應如下所示:

訂單

訂單識別碼

訂單日期

銷售人員識別碼

客戶識別碼

2349

3/4/09

101

1005

2350

3/4/09

103

1003

2351

3/4/09

105

1001

2352

3/5/09

105

1003

2353

3/7/09

107

1005

[訂單詳細資料] 資料表中不包含需要唯一值的資料行(也就是沒有主鍵),因此,任何或所有欄都能包含「多餘」資料。 不過,此資料表中的兩筆記錄不應該完全相同(這個規則適用于資料庫中的任何資料表)。 在此表格中,應有17筆記錄,每個記錄對應至個別訂單中的產品。 例如,在 [訂單 2349] 中,三個 C-789 產品組成整個訂單的兩個部分之一。

因此,[訂單詳細資料] 資料表應該如下所示:

訂單詳細資料

訂單識別碼

產品識別碼

Qty

2349

C-789

3

2349

C-795

6

2350

A-2275

2

2350

F-198

6

2350

B-205

1

2351

C-795

6

2352

A-2275

2

2352

D-4420

3

2353

A-2275

6

2353

C-789

5

將資料從 Excel 複製並貼到 Access 中

現在,關於銷售人員、客戶、產品、訂單及訂單詳細資料的相關資訊已分解成 Excel 中的個別主題,您可以直接將這些資料複製到 Access 中,將其變成資料表。

在 Access 資料表與執行查詢之間建立關聯

將資料移至 Access 之後,您可以在資料表之間建立關聯,然後建立查詢以傳回各種主題的相關資訊。 例如,您可以建立查詢,以傳回在3/05/09 和3/08/09 之間輸入之訂單的訂單識別碼和銷售人員名稱。

此外,您還可以建立表單和報表,讓資料輸入和銷售分析變得更容易。

需要更多協助嗎?

您可以隨時詢問 Excel 技術社群中的專家、在 Answers 社群取得支援,或是在 Excel User Voice 上建議新功能或增強功能。

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×