将数据从 Excel 移动到 Access

本文演示如何将数据从 Excel 移动到 Access,以及如何将数据转换为关系表,以便可以同时使用 Microsoft Excel 和 Access。 总之,Access 最适合捕获、存储、查询和共享数据,Excel 最适合用于计算、分析和可视化数据。

两篇文章(使用 Access 或 Excel 管理数据)以及将 Access 与 Excel 一同使用的 10大理由,讨论了最适合特定任务的程序以及如何将 Excel 和 Access 一起用于创建实用解决方案。

将数据从 Excel 移动到 Access 时,该过程有三个基本步骤。

三个基本步骤

注意: 有关 Access 中数据建模和关系的信息,请参阅 数据库设计基础知识

步骤 1:将数据从 Excel 导入 Access

导入数据是一项操作,如果准备和清理数据需要一些时间,操作会更加顺畅。 导入数据就像移动到新家一样。 如果你在迁移之前清理和组织你的拥有,那么进入新家庭会容易得多。

导入之前清理数据

将数据导入 Access 之前,在 Excel 中,建议:

  • 将包含非原子数据的单元格 (即,一个单元格中的多个值) 多个列。 例如,"技能"列中包含多个技能值的单元格(如"C# 编程"、"VBA 编程"和"Web 设计")应分解为仅包含一个技能值的单独列。

  • 使用 TRIM 命令删除前导空格、尾随空格和多个嵌入空格。

  • 删除非打印字符。

  • 查找并修复拼写和标点错误。

  • 删除重复行或重复字段。

  • 确保数据列不包含混合格式,尤其是设置为文本格式的数字或设置为数字格式的日期。

有关详细信息,请参阅以下 Excel 帮助主题:

注意: 如果数据清理需求很复杂,或者没有时间或资源自行自动执行该过程,可以考虑使用第三方供应商。 有关详细信息,请通过 Web 浏览器中最喜欢的搜索引擎搜索"数据清理软件"或"数据质量"。

导入时数据类型选择最佳选项

在 Access 中执行导入操作期间,需要做出良好的选择,以便收到 (() 需要手动干预的任何转换错误)。 下表汇总了将数据从 Excel 导入 Access 时如何转换 Excel 数字格式和 Access 数据类型,并提供了一些有关在"导入电子表格向导"中选择的最佳数据类型的提示。

Excel 数字格式

Access 数据类型

备注

最佳做法

文本

文本、备忘录

Access 文本数据类型存储最多 255 个字符的字母数字数据。 Access 备忘录数据类型存储最多 65,535 个字符的字母数字数据。

选择 " 备注"以避免截断任何数据。

数字、百分比、分数、科学

数字

Access 有一个数字数据类型,该属性根据字段大小属性 (字节、整数、长整型、单精度数、双精度数、十进制数) 。

选择 "双 精度型"以避免任何数据转换错误。

日期

日期

Access 和 Excel 都使用相同的序列号来存储日期。 在 Access 中,日期范围更大:从 100 ) 年 1 月 1 日的 -657,434 (到 9999 年 12 月 31 日 (2,958,465 ) 。

由于 Access 无法识别 Excel for Macintosh (使用的 1904 日期系统) ,因此您需要在 Excel 或 Access 中转换日期以避免混淆。

有关详细信息,请参阅 "更改日期 系统、格式或两位数年份解释"以及导入或链接到 Excel 工作簿 中的数据

选择"日期"。

时间

时间

Access 和 Excel 都使用相同的值来存储数据类型。

选择 "时间",这通常是默认值。

货币、会计

货币

在 Access 中,货币数据类型数据存储为 8 字节数字,精度为四位小数,用于存储财务数据并防止值的舍入。

选择 "货币",这通常是默认货币。

布尔

是/否​​

Access 将 -1 用于所有"是"值,0 用于所有"否"值,而 Excel 使用 1 表示所有 TRUE 值,0 用于所有 FALSE 值。

选择 "是/否",自动转换基础值。

超链接

超链接

Excel 和 Access 中的超链接包含一个 URL 或 Web 地址,您可以单击并关注该 URL 或 Web 地址。

选择 "超链接",否则 Access 可能会默认数据类型文本格式。

数据位于 Access 中后,可以删除 Excel 数据。 在删除原始 Excel 工作簿之前,不要忘记先备份它。

有关详细信息,请参阅 Access 帮助主题"导入或链接到 Excel 工作簿中的数据"。

轻松自动追加数据

Excel 用户面临的一个常见问题是,将包含相同列的数据追加到一个大型工作表中。 例如,您可能有一个资产跟踪解决方案,该解决方案在 Excel 中开始,但现在已发展为包含来自许多工作组和部门的文件。 此数据可能在不同的工作表和工作簿中,或者在其他系统的数据馈送的文本文件中。 没有用户界面命令或简单的方法在 Excel 中追加类似的数据。

最佳解决方案是使用 Access,在这里,可以使用"导入电子表格向导"轻松地将数据导入和追加到一个表中。 此外,可以将许多数据追加到一个表中。 您可以保存导入操作,将它们添加为计划的 Microsoft Outlook 任务,甚至使用宏自动执行该过程。

步骤 2:使用表分析器向导规范化数据

一目了然地说,逐步完成规范化数据的过程似乎是一项令人生畏的任务。 幸运的是,借助表分析器向导,在 Access 中规范化表的过程要容易得多。

表分析器向导

1. 将所选列拖动到新表并自动创建关系

2. 使用按钮命令重命名表、添加主键、将现有列作为主键,以及撤消上一操作

可以使用此向导执行以下操作:

  • 将表转换为一组较小的表,并自动在表之间创建主键和外键关系。

  • 将主键添加到包含唯一值的现有字段,或创建使用"自动编号"数据类型。

  • 自动创建关系以使用级联更新实施参照完整性。 不会自动添加级联删除以防止意外删除数据,但以后可以轻松添加级联删除。

  • 在新表中搜索冗余或重复 (,例如具有两个不同电话号码的同一客户) 并按需要进行更新。

  • 备份原始表,并在其名称后面追加"_OLD重命名它。 然后,创建一个使用原始表名称重建原始表的查询,以便基于原始表的任何现有窗体或报表都使用新的表结构。

有关详细信息,请参阅"使用表分析器规范化数据"。

步骤 3:从 Excel 连接到 Access 数据

在 Access 中规范化数据并创建重新构造原始数据的查询或表后,只需从 Excel 连接到 Access 数据。 现在,数据作为外部数据源在 Access 中,因此可以通过数据连接连接到工作簿,数据连接是一个信息容器,用于查找、登录和访问外部数据源。 连接信息存储在工作簿中,也可以存储在连接文件中,例如 Office 数据连接 (ODC) 文件 (.odc 文件扩展名) 或数据源名称文件 (.dsn 扩展名) 。 连接到外部数据后,每当 Access 中更新 (,) Access 中自动刷新或更新 Excel 工作簿。

有关详细信息,请参阅"将数据从外部数据源导入 (Power Query) 。

将数据放入 Access

本部分将指导您完成规范化数据的以下阶段:将"销售人员"和"地址"列中的值分解为大多数原子部分,将相关主题分隔到其自己的表中,将这些表从 Excel 复制并粘贴到 Access,在新建的 Access 表之间创建键关系,以及创建和运行 Access 中的简单查询以返回信息。

非规范化形式的示例数据

以下工作表包含"销售人员"列和"地址"列中的非原子值。 这两列应拆分为两个或多个单独的列。 此工作表还包含有关销售人员、产品、客户和订单的信息。 此信息也应按主题进一步拆分为单独的表。

销售人员

订单 ID

订单日期

产品 ID

Qty

价格

客户名称

地址

手机

2349

3/4/09

C-789

3

$7.00

Fourth Coffee

7007 康奈尔圣雷德蒙德,WA 98199

425-555-0201

2349

3/4/09

C-795

6

$97.50

Fourth Coffee

7007 康奈尔圣雷德蒙德,WA 98199

425-555-0201

Adams、Ellen

2350

3/4/09

A-2275

2

$167.50

嘉元实业

1025 哥伦比亚圆克兰,WA 98234

425-555-0185

Adams、Ellen

2350

3/4/09

F-198

6

$52.50

嘉元实业

1025 哥伦比亚圆克兰,WA 98234

425-555-0185

Adams、Ellen

2350

3/4/09

B-205

1

$4.50

嘉元实业

1025 哥伦比亚圆克兰,WA 98234

425-555-0185

Hance、Jim

2351

3/4/09

C-795

6

$97.50

康拓工程有限公司

2302 华盛顿州贝尔维尤市 98227

425-555-0222

Hance、Jim

2352

3/5/09

A-2275

2

$167.50

嘉元实业

1025 哥伦比亚圆克兰,WA 98234

425-555-0185

Hance、Jim

2352

3/5/09

D-4420

3

$7.25

嘉元实业

1025 哥伦比亚圆克兰,WA 98234

425-555-0185

Koch、Reed

2353

3/7/09

A-2275

6

$167.50

Fourth Coffee

7007 康奈尔圣雷德蒙德,WA 98199

425-555-0201

Koch、Reed

2353

3/7/09

C-789

5

$7.00

Fourth Coffee

7007 康奈尔圣雷德蒙德,WA 98199

425-555-0201

信息在其最小部分:原子数据

处理此示例中的数据时,可以使用 Excel 中的"文本到列"命令将单元格 (的"原子"部分(如街道地址、城市、省/直州、邮政编码) 分到离散列中。

下表显示了拆分后同一工作表中的新列,使所有值成为原子值。 请注意,"销售人员"列中的信息已拆分为"姓氏"和"名字"列,"地址"列中的信息已拆分为"街道地址、城市、省/市/县"和"邮政编码"列。 此数据为"第一个正常形式"。

姓氏

名字

 

街道地址

城市

州/省

邮政编码

Li

因此,我们

2302 省/市/区

Bellevue

WA

98227

Adams

Ellen

1025 哥伦比亚圈

柯克兰

WA

98234

Hance

米申

2302 省/市/区

Bellevue

WA

98227

Koch

Reed

7007 康奈尔圣雷德蒙德

雷德蒙德

WA

98199

在 Excel 中将数据分解为有序的主题

在 Excel 工作表拆分为销售人员、产品、客户和订单的表后,后续几张示例数据表显示相同的信息。 表格设计不是最终设计,但它位于正确的方向。

"销售人员"表仅包含有关销售人员的信息。 请注意,每条记录在 SalesPerson ID (都有) 。 SalesPerson ID 值将在"订单"表中用于将订单连接到销售人员。

销售人员

销售人员 ID

姓氏

名字

101

Li

因此,我们

103

Adams

Ellen

105

Hance

米申

107

Koch

Reed

"产品"表仅包含有关产品的信息。 请注意,每条记录都有唯一 ID (产品 ID) 。 产品 ID 值将用于将产品信息连接到"订单明细"表。

产品

产品 ID

价格

A-2275

16.75

B-205

4.50

C-789

7.00

C-795

9.75

D-4420

7.25

F-198

5.25

"客户"表仅包含有关客户的信息。 请注意,每条记录都有唯一 ID (客户 ID) 。 "客户 ID"值用于将客户信息连接到"订单"表。

客户

客户 ID

名称

街道地址

城市

州/省

邮政编码

手机

1001

康拓工程有限公司

2302 省/市/区

Bellevue

WA

98227

425-555-0222

1003

嘉元实业

1025 哥伦比亚圈

柯克兰

WA

98234

425-555-0185

1005

Fourth Coffee

7007 康奈尔 St

雷德蒙德

WA

98199

425-555-0201

"订单"表包含有关订单、销售人员、客户和产品的信息。 请注意,每条记录具有唯一 ID (订单 ID) 。 此表中的某些信息需要拆分为包含订单详细信息的其他表,以便"订单"表仅包含四列 - 唯一订单 ID、订单日期、销售人员 ID 和客户 ID。 此处显示的表尚未拆分为"订单明细"表。

订单

订单 ID

订单日期

SalesPerson ID

客户 ID

产品 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

订单详细信息(例如产品 ID 和数量)将移出"订单"表,并存储在名为"订单详细信息"的表中。 请记住,有 9 个订单,因此,此表中有 9 条记录很有意义。 请注意,"订单"表具有一个唯 (ID) "订单明细"表中引用。

"订单"表的最终设计应如下所示:

订单

订单 ID

订单日期

SalesPerson ID

客户 ID

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 产品构成整个订单的两个部分之一。

因此,"订单明细"表应如下所示:

订单详细信息

订单 ID

产品 ID

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 后,可以创建表之间的关系,然后创建查询以返回有关各种主题的信息。 例如,您可以创建一个查询,用于返回 2009 年 3 月 5 日与 2009 年 3 月 8 日之间输入的订单的订单的销售人员姓名。

此外,还可以创建窗体和报表,使数据输入和销售分析更容易。

需要更多帮助吗?

可随时在 Excel 技术社区中咨询专家,在解答社区获得支持,或在 Excel User Voice 上建议新功能或功能改进。

需要更多帮助?

扩展你的 Office 技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×