将数据从 Excel 移动到 Access

本文介绍如何从 Excel 移动数据以访问数据并将其转换为关系表,以便您可以使用 Microsoft Excel 并一起访问。 总而言之,Access 最适合用于捕获、存储、查询和共享数据,Excel 最适合用于计算、分析和可视化数据。

两个文章:使用 access 或 Excel 管理你的数据使用 excel Access 的10大理由,讨论哪种程序最适合特定任务以及如何使用 Excel 和协同访问来创建实用解决方案。

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

三个基本步骤

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

步骤1:将 Excel 中的数据导入 Access

导入数据是一种操作,如果你花一些时间来准备和清理数据,则可以更顺利地执行操作。 导入数据类似于移动到新家。 如果您在移动之前清理并整理财产,则在您的新家中进行结算会更容易。

导入前清理数据

在 Excel 中将数据导入 Access 之前,最好先执行以下操作:

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

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

  • 删除非打印字符。

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

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

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

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

注意: 如果你的数据清洁需求很复杂,或者你没有时间或资源来自动处理过程,你可能会考虑使用第三方供应商。 有关详细信息,请在 Web 浏览器中搜索常用搜索引擎的 "数据清理软件" 或 "数据质量"。

导入时选择最佳数据类型

在 Access 中的导入操作过程中,你希望做出很好的选择,以便你收到需要手动干预的少量(如果存在)转换错误。 下表总结了在将数据从 Excel 导入到 Access 时如何转换 Excel 数字格式和 Access 数据类型,并提供了有关在导入电子表格向导中选择的最佳数据类型的一些提示。

Excel 数字格式

Access 数据类型

批注

最佳做法

文本

文本、备忘录

Access 文本数据类型存储最多255个字符的字母数字数据。 Access 备注数据类型存储最多65535个字符的字母数字数据。

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

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

数字

Access 具有一种数字数据类型,该数据类型根据字段大小属性(Byte、Integer、Long Integer、Single、Double 和 Decimal)的不同而不同。

选择 "加倍" 以避免任何数据转换错误。

日期

日期

Access 和 Excel 都使用相同的序列日期数字来存储日期。 在 Access 中,日期范围较大:从-657434 (公元100年1月1日)到2958465(公元9999年12月31日)。

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

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

选择 "日期"。

时间

时间

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

选择 "时间" (通常为默认值)。

货币、会计

货币

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

选择 "货币" (通常为默认值)。

布尔

是/否​​

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

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

超链接

超链接

Excel 和 Access 中的超链接包含可以单击和关注的 URL 或 Web 地址。

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

数据在 Access 中后,您可以删除 Excel 数据。 请先不要忘记备份原始 Excel 工作簿,然后再将其删除。

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

以简单的方式自动追加数据

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

最佳解决方案是使用 Access,通过使用导入电子表格向导,可以轻松地将数据导入一个表中并将数据追加到一个表中。 此外,你可以将大量数据追加到一个表中。 你可以保存导入操作、将其添加为计划的 Microsoft Outlook 任务,甚至使用宏自动执行该过程。

步骤2:使用表分析器向导标准化数据

乍一看,逐步完成规范化数据的过程可能会是一项艰巨的任务。 幸运的是,使用表分析器向导,对 Access 中的表进行规范化要容易得多。

表分析器向导

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

2. 使用按钮命令重命名表、添加主键、使现有列成为主键,并撤消最后一项操作

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

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

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

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

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

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

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

步骤3:连接以访问 Excel 中的数据

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

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

将数据导入 Access

本部分将引导你完成规范化数据的以下阶段:将销售人员和地址列中的值分解到最多的原子片段中,将相关主题分隔到各自的表中,将相关主题复制并粘贴到 Excel 中Access,在新创建的 Access 表之间创建键关系,并在 Access 中创建并运行一个简单查询来返回信息。

非规范化形式的示例数据

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

销售人员

订单 ID

订单日期

产品 ID

Qty

价格

客户名称

地址

手机

Li,Yale

2349

3/4/09

C-789

3

$7.00

Fourth Coffee

7007 Cornell 圣雷德蒙,WA 98199

425-555-0201

Li,Yale

2349

3/4/09

C-795

6

$9.75

Fourth Coffee

7007 Cornell 圣雷德蒙,WA 98199

425-555-0201

Adams、潘蕾

2350

3/4/09

A-2275

2

$16.75

嘉元实业

1025哥伦比亚圆圈 Kirkland,WA 98234

425-555-0185

Adams、潘蕾

2350

3/4/09

F-198

6

$5.25

嘉元实业

1025哥伦比亚圆圈 Kirkland,WA 98234

425-555-0185

Adams、潘蕾

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

康拓工程有限公司

2302 Harvard (邯郸),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 圣雷德蒙,WA 98199

425-555-0201

Koch、簧片

2353

3/7/09

C-789

5

$7.00

Fourth Coffee

7007 Cornell 圣雷德蒙,WA 98199

425-555-0201

最小部分中的信息:原子数据

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

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

姓氏

名字

 

街道地址

城市

州/省

邮政编码

离子

Yale

2302 Harvard

Bellevue

WA

98227

Adams

潘蕾

1025哥伦比亚圆圈

柯克兰

WA

98234

Hance

米申

2302 Harvard

Bellevue

WA

98227

Koch

Reed

7007 Cornell 圣雷德蒙

雷德蒙德

WA

98199

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

下面的多个示例数据表在将 Excel 工作表拆分为销售人员、产品、客户和订单的表后,会显示相同的信息。 表格设计并非最终版本,但它位于正确的轨道上。

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

人员

销售人员 ID

姓氏

名字

101

离子

Yale

103

Adams

潘蕾

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 Harvard

Bellevue

WA

98227

425-555-0222

1003

嘉元实业

1025哥伦比亚圆圈

柯克兰

WA

98234

425-555-0185

1005

Fourth Coffee

7007 Cornell St

雷德蒙德

WA

98199

425-555-0201

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

订单

订单 ID

订单日期

销售人员 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),该 ID 将从 "订单明细" 表中引用。

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

订单

订单 ID

订单日期

销售人员 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

Order Details 表不包含需要唯一值的列(即没有主键),因此任何或所有列都可以包含 "冗余" 数据。 但是,此表中的两条记录应该完全相同(此规则适用于数据库中的任何表)。 在此表中,应该有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 后,可以创建表之间的关系,然后创建查询以返回有关各种主题的信息。 例如,您可以创建一个查询,该查询返回在3/05/09 和3/08/09 之间输入的订单的订单 ID 和销售人员姓名。

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

需要更多帮助吗?

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

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

此信息是否有帮助?

谢谢您的反馈!

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

×