将数据从 Excel 移到 Access

重要:  本文是由机器翻译的,请参阅免责声明。请在 此处 中查找本文的英文版本以便参考。

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

使用 Access 或 Excel 管理数据使用与 Excel 的访问权限的前 10 个原因,两篇文章讨论哪种程序是最适合于特定任务以及如何使用 Excel 和 Access 同时创建可行的解决方案。

本文内容

关系数据库和数据建模的本质

关系的表的组成部分

规范化是什么?

表可以在不同的普通窗体

关系和键

数据完整性和有效性

总结对

将数据从 Excel 移到 Access 的基本步骤

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

自动将数据追加简便方式

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

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

获取到 Access 数据

关系数据库和数据建模的本质

多个数据文件,包括 Excel 中,称为平面文件。这些文件大,并且包含冗余数据、 很少使用的列和多个空值。您可以从其他系统或用户,继承这些文件,或他们可能有演化而来的这种方式为已将列添加一段时间,以满足不断变化的要求。虽然平面文件中的数据的组织适用于特定的用途,他们不灵活,并且您可以发现很难回答有关您的数据的一些意料之外的问题。

平面文件

到平面文件经过时间检验的解决方案是关系数据库。Access 是一种关系数据库程序并适合时也设计符合关系数据库模型的关系的表。

返回页首

关系的表的组成部分

在精心设计的关系数据库中,每个表是命名的列和多行的集合,它存储有关一个主题,如员工的信息。表的每一列具有唯一名称,并包含主题,如员工的名字和地址的信息。表的行包含主题,如公司中的所有当前员工的匹配项。单个值存储在交叉处的行和列,而是一个事实,如"西雅图"。最后,您可以重新排序的行和列而不更改表格的含义。

关系表

1.表描述了一个主题-人员、 位置、 事物、 事件或概念

2.每行是唯一的有一个主键,如徽章号

3.每一列具有唯一、 短,且有意义的名称

4.在列中的所有值都都相似含义和格式

5。 每个表 (相当于 Excel 中的单元格) 中的值表示单个事实

返回页首

规范化是什么?

很遗憾,Access 数据库中不只是自动发生一个精心设计的关系的表。您必须使用方法分析平面文件中的数据,并将数据从一个表重排到两个或多个相关表。此方法称为标准化。在分步过程,您将拆分一个表分为两个或多个较小的表的列中删除重复值、 删除冗余数据行,从和添加 (唯一标识表中的每个记录的字段) 的主键和外键 (包含相关表中主键字段中找到的值的字段) 来定义新的表之间的关系。

关系图

1.关系表

2.列名称

3.为主键

4.外键

5.关系线和符号

返回页首

表可以在不同的普通窗体

表可以在其中一个四种不同普通窗体: 零,第一个、 第二步和第三。每个窗体描述向其表格中的数据组织,并且可以成功使用关系数据库中的程度。零范式至少序状态,并且第三范式最有效地组织。

零普通窗体   表是在至少序状态,称为"零普通窗体中,"中的一个登录时将一个或多个列包含"非原子"值时,这意味着,在单个单元格中包含多个值。例如,客户地址可能包含街道地址 (如 2302 哈佛平均)、 城市、 状态和邮政编码 (ZIP)。理想情况下,每个元素的地址存储在单独的列。另一个示例中,为包含完整的列的名称,如"Li,耶鲁"或者"黄雅玲"应该拆分为分别对应于名字和姓氏列。存储在单独的列中的名字和姓氏是一个好的做法,可帮助您快速找到并对数据排序。

零普通窗体中的数据的另一个登录时,它包含不同的主题,例如销售人员、 产品、 客户和订单的信息。请尽可能地数据应分隔到单独的表中为每个主题。

第一个普通的窗体   表格时在第一个普通的窗体中的每一列包含原子值,但一个或多个列包含一些冗余数据,例如销售人员或客户的订单的每个部分的信息。例如,雅玲、 炫皓重复五次工作表中因为她具有两个不同的订单 (与三种产品和两个产品使用一个)。

第二个普通窗体   表格时,在第二个普通窗体中已删除冗余数据,但一个或多个列或者不基于为主键或包含计算的值 (如价格 * 折扣)。

第三范式   表格时范式第三个表中的所有列仅都根据为主键。如下图所示产品和供应商的信息存储在单独的表,然后查找与供应商表中的供应商 ID 字段上联接。

当您想要更改的数据时,零,第一个和第二个普通的窗体中的表可以提出问题。例如,更新经常重复的值是非常耗时的过程。每次您更新一个值,您需要检查每隔一行是否包含相同的值。这可以浪费时间,并将出错的操作。此外,很难有效地进行排序和筛选的列包含重复值。表中第一个和第二个范式大改善移动零普通的窗体,但是它们仍会出现问题时插入、 更新或删除数据。

规范化数据的过程中转时,您将表格转换为从较低的窗体更高版本的窗体,直到所有表都都在第三个普通窗体。第三范式是一个理想选择在大多数情况下,因为:

  • 插入、 删除或更新数据时,可以消除修改问题。

  • 可以使用数据约束和业务规则维护数据完整性。

  • 您可以查询以各种方式来回答问题的数据。

返回页首

关系和键

明确定义的关系数据库中包含多个表,在第三个普通表单中,每个,但是也存在关系帮助汇集数据这些表之间。例如,员工所属部门和分配给项目中,项目具有子任务、 子任务拥有的员工和部门管理项目。关系数据库将在此方案中,有四个表定义: 员工、 项目、 子任务,和与每个定义这些键关系的部门:属于分配给由拥有,和管理

有三种类型的关系:

  • 一对一 (1:1)   例如,每个员工具有唯一徽章 ID 并且 ID 是指每个徽章具有唯一的员工。

  • -一对多 (1:M)   例如,每个员工分配给一个部门,但是部门具有许多员工。这也称为父子关系。

  • 多对多 (多对多)   例如,员工可以分配给多个项目,并且每个项目都可以有许多雇员分配。请注意特殊的表中,称为联接表,通常用于创建第三个范式中总共共同构成多对多关系的三个表的每个表之间的一对多关系。

创建基于主键和外键的两个或多个表之间的关系。主键是其值唯一标识表中,如徽章号或部门的代码中的每一行的表中的列。外键是其值都是相同的另一个表的主键的表中的列。可以外键视为从另一个关系的表的主键的副本。两个表之间的关系进行匹配的值在一个表中的外键在另一个主键的值。

主键和外键关系

返回页首

数据完整性和有效性

您创建关系数据库中所有表中的第三个普通窗体和正确的关系定义后,您希望确保数据完整性。数据完整性意味着您可以正确一致地导航关系并随着时间的推移操作数据库中的表,如更新的数据库。帮助确保数据完整性的关系数据库中有两个基本规则。

实体规则   为表中的每一行必须为主键和该主键必须具有一个值。此规则确保为表中的每一行可以唯一标识,并将永远不会意外丢失。此外,当您插入、 更新或删除数据,唯一性和所有主键存在可进行维护。

参照完整性规则   此规则控制-一对多关系的插入和删除规则。如果表格具有外键外, 键的每个值必须是 null (无值) 或必须匹配关系中的外键是主键的表中的值。

编辑关系

您可以使用各种数据有效性规则,包括数据类型 (如整数)、 数据长度 (如为 15 个字符或更少)、 数据格式 (如货币)、 默认值 (如 10) 和限制也进一步确保关系数据库中的数据完整性 (如 Inventory_Amt > ReOrder_Amt)。这些数据验证规则有助于确保数据库具有质量数据,并且符合建立的业务规则。

值得注意数据输入重要的 Access 数据库和 Excel 工作簿之间的区别。在 Excel 工作表中输入数据是"自由格式。"您可以输入几乎任何位置数据,您可以轻松地撤消更改。但是,Access 数据库更受结构化和约束。此外,在表中输入数据时,更改一直致力于数据库。虽然您可以删除或更新数据更正任何错误,无法与 Excel 相同的方式撤销数据输入。

返回页首

总结对

具有明确定义的关系的关系表到规范化数据并定义其数据完整性后,很容易:

  • 节省空间并提高性能,因为重复和冗余数据实际将被删除。

  • 准确地更新数据和维护数据完整性。

  • 排序、 筛选、 创建聚合,计算的列和汇总数据。

  • 查询中的多种方法来回答预期和一些意料之外的问题的数据。

当然,那里多种高级方面关系数据库设计,例如复合键 (包含两个或多个列的值的键),请到其他普通窗体 (第四个范式 — 多值的相关性),和非规范化。但是,大多数简单到中等数据库需要您有有关数据库设计需要了解下列案例研究本文中的基本信息。

返回页首

将数据从 Excel 移到 Access 的基本步骤

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

三个基本步骤

返回页首

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

导入数据是操作可能会更加顺畅,如果您需要一些时间才能准备和清理您的数据。将数据导入就像将移动到新的位置。如果清理和组织您财产,然后再移动到新的位置结算是变得更为轻松。

清理数据导入之前

将数据导入到 Access 之前,请在 Excel 中最好为:

  • 转换到多个列的单元格包含非原子数据 (即,一个单元格中的多个值)。例如,包含多个技能值,如"C# 编程,"的"技能"列中的单元格"VBA 编程,"和"Web 设计"应被细分分隔每个包含只有一个技能值的列。

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

  • 删除非打印字符。

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

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

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

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

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

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

在 Access 中导入操作时,您希望将很好的选择,以便接收需要手工的一些 (如果有) 转换错误。下表总结了 Excel 数字格式和访问数据类型转换时将数据从 Excel 导入 Access,并提供了有关最佳的数据类型,选择导入电子表格向导中的一些提示。

Excel 数字格式

Access 数据类型

批注

最佳做法

文本

文本、 备注

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

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

科学记数的数字,百分比,分数

数量

访问都有一个不同的数字数据类型基于字段大小属性 (字节、 整数、 长整型,单个、 双、 小数)。

选择双击以避免任何数据转换错误。

Date

Date

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

Access 无法识别 1904年日期系统 (用于在 Excel 中适用于 Macintosh),因为您需要转换日期 Excel 或 Access 为了避免混淆。

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

.

选择日期

日期

日期

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

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

货币、 会计专用

Currency

在 Access 中,货币数据类型为 8 字节数字为四位小数,精确地存储数据,而用于存储财务数据和避免的值进行四舍五入。

选择货币这通常是默认值。

Boolean

是/否

访问使用-1 表示是的所有值和所有没有值,0,而 Excel 用于所有 FALSE 值的所有 TRUE 值为 1 和 0。

选择是/否,它会自动将转换基础值。

超链接

超链接

Excel 和 Access 中的超链接中包含 URL 或 Web 地址,您可以单击并跟踪。

选择超链接,否则为 Access 可能默认情况下使用文本数据类型。

在 Access 中的数据后,您可以删除 Excel 数据。不要忘记备份首先之前删除原来的 Excel 工作簿。

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

返回页首

自动将数据追加简便方式

常见的问题 Excel 用户具有已将具有相同的列的数据添加到一个大型工作表。例如,您可能必须资产跟踪开始在 Excel 中,但现在已发展壮大包括从许多工作组和部门的文件的解决方案。此数据可能会在不同工作表和工作簿,或从其他系统的数据源的文本文件。没有用户界面命令或轻松追加在 Excel 中的类似数据。

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

返回页首

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

第一次看到逐步执行规范化数据的过程可能看起来非常艰巨的任务。幸运的是,规范化 Access 中的表是一个容易得多,感谢表分析器向导的过程。

表分析器向导

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

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

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

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

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

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

  • 搜索冗余或重复数据 (例如相同客户提供两个不同的电话号码) 的新表,并根据需要更新此。

  • 备份原始表,并将其重命名通过将"_OLD"追加到其名称。然后,您创建的查询的重建原始表中的,使用原始表名称,以便任何现有窗体或报表基于原始表将使用新的表格结构。

返回页首

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

数据已在 Access 中已标准化和查询或表已创建重建原始数据后,即可从 Excel 连接到 Access 数据。您数据现在为外部数据源,在 Access 中,以便可以连接到工作簿的数据连接,这是用于查找的信息的容器,通过登录到,并访问外部数据源。连接信息存储在工作簿,也可以存储中的连接文件,如 Office 数据连接 (ODC) 文件 (.odc 文件扩展名) 或数据源名称的文件 (.dsn 扩展名)。连接到外部数据后,您可以也会自动刷新 (或更新) 从 Access 在 Access 中更新数据时 Excel 工作簿。

有关详细信息,请参阅连接 (导入) 数据概述Excel 和 Access 之间交换 (复制、 导入、 导出) 数据

返回页首

获取到 Access 数据

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

非规范化窗体中的示例数据

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

Salesperson

订单 ID

订购日期

产品 ID

Qty

价格

客户名称

地址

移动电话

Li 耶鲁

2348

3/2/09

J-558

4

$ 8.50

康拓工程有限公司

2302 哈佛平均邯郸,WA 98227

425-555-0222

Li 耶鲁

2348

3/2/09

B-205

2

$ 4.50

康拓工程有限公司

2302 哈佛平均邯郸,WA 98227

425-555-0222

Li 耶鲁

2348

3/2/09

D-4420

5

$ 7.25

康拓工程有限公司

2302 哈佛平均邯郸,WA 98227

425-555-0222

Li 耶鲁

2349

3/4/09

C-789

3

$ 7.00

Fourth Coffee

7007 康奈尔 St Redmond,WA 98199

425-555-0201

Li 耶鲁

2349

3/4/09

C-795

6

$ 9.75

Fourth Coffee

7007 康奈尔 St Redmond,WA 98199

425-555-0201

雅玲,炫皓

2350

3/4/09

A-2275

2

$ 16.75

爱德风险投资公司

1025 哥伦比亚圆形柯克,WA 98234

425-555-0185

雅玲,炫皓

2350

3/4/09

F-198

6

$ 5.25

爱德风险投资公司

1025 哥伦比亚圆形柯克,WA 98234

425-555-0185

雅玲,炫皓

2350

3/4/09

B-205

1

$ 4.50

爱德风险投资公司

1025 哥伦比亚圆形柯克,WA 98234

425-555-0185

Hance Jim

2351

3/4/09

C-795

6

$ 9.75

康拓工程有限公司

2302 哈佛平均邯郸,WA 98227

425-555-0222

Hance Jim

2352

3/5/09

A-2275

2

$ 16.75

爱德风险投资公司

1025 哥伦比亚圆形柯克,WA 98234

425-555-0185

Hance Jim

2352

3/5/09

D-4420

3

$ 7.25

爱德风险投资公司

1025 哥伦比亚圆形柯克,WA 98234

425-555-0185

Koch 簧片

2353

3/7/09

A-2275

6

$ 16.75

Fourth Coffee

7007 康奈尔 St Redmond,WA 98199

425-555-0201

Koch 簧片

2353

3/7/09

C-789

5

$ 7.00

Fourth Coffee

7007 康奈尔 St Redmond,WA 98199

425-555-0201

Sousa 路易斯

2354

3/7/09

A-2275

3

$ 16.75

康拓工程有限公司

2302 哈佛平均邯郸,WA 98227

425-555-0222

雅玲,炫皓

2355

3/8/09

D-4420

4

$ 7.25

爱德风险投资公司

1025 哥伦比亚圆形柯克,WA 98234

425-555-0185

雅玲,炫皓

2355

3/8/09

C-795

3

$ 9.75

爱德风险投资公司

1025 哥伦比亚圆形柯克,WA 98234

425-555-0185

Li 耶鲁

2356

3/10/09

C-789

6

$ 7.00

康拓工程有限公司

2302 哈佛平均邯郸,WA 98227

425-555-0222

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

使用此示例中的数据,您可以使用 Excel 中的文本转换成列命令分隔 (如街道地址、 城市、 状态和邮政编码) 的单元格的"原子"部分到离散列。

他们已被拆分使所有值原子后下, 表显示在同一个工作表中的新列。请注意,已将销售人员列中的信息拆分为名字姓氏和名字列和地址列中的信息已分为街道地址、 城市、 状态和邮政编码的列。此数据位于"第一个普通窗体"。

姓氏

名字

 

街道地址

城市

州/省

邮政编码

Li

耶鲁

2302 哈佛平均

毕尔褔

WA

98227

1025 哥伦比亚圆形

柯克

WA

98234

光明

2302 哈佛平均

毕尔褔

WA

98227

Koch

簧片

7007 康奈尔 St Redmond

Redmond

WA

98199

2302 哈佛平均

毕尔褔

WA

98227

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

按照的示例数据的多个表显示 Excel 工作表中的相同信息后,它已拆分为表的销售人员、 产品、 客户和订单。表设计未最终状态,但它处于开启正轨。

销售人员表包含仅有关销售人员的信息。请注意,每个记录的唯一标识号 (销售人员 ID)。销售人员 ID 值将用于在订单表中连接到销售人员的订单。

销售人员

销售人员 ID

姓氏

名字

101

Li

耶鲁

103

105

光明

107

Koch

簧片

109

产品表包含仅有关产品的信息。请注意,每个记录的唯一标识号 (产品 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

J-558

8.50

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

客户

客户 ID

名称

街道地址

城市

州/省

邮政编码

移动电话

1001

康拓工程有限公司

2302 哈佛平均

毕尔褔

WA

98227

425-555-0222

1003

爱德风险投资公司

1025 哥伦比亚圆形

柯克

WA

98234

425-555-0185

1005

Fourth Coffee

7007 康奈尔 St

Redmond

WA

98199

425-555-0201

订单表包含有关订单、 销售人员、 客户和产品信息。请注意,每个记录的唯一标识号 (订单 ID)。此表中的信息的一些需要拆分到其他表中包含订单的详细信息,以使订单表包含仅有四列 — 唯一的订单 ID、 订单日期、 销售人员 ID 和客户 id。此处所示的表已不尚未分为订单明细表。

订单

订单 ID

订购日期

销售人员 ID

客户 ID

产品 ID

Qty

2348

3/2/09

101

1001

J-558

4

2348

3/2/09

101

1001

B-205

2

2348

3/2/09

101

1001

D-4420

5

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

2354

3/7/09

109

1001

A-2275

3

2355

3/8/09

103

1003

D-4420

4

2355

3/8/09

103

1003

C-795

3

2356

3/10/09

101

1001

C-789

5

订单的详细信息,如产品 ID 和数量是移出订单表和存储在表名为订单详细信息。请记住,有 9 订单,以便它在此表中存在 9 记录有意义。请注意,订单表有唯一标识号 (订单 ID),这将引用了订单明细表。

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

订单

订单 ID

订购日期

销售人员 ID

客户 ID

2348

3/2/09

101

1001

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

2354

3/7/09

109

1001

2355

3/8/09

103

1003

2356

3/10/09

101

1001

订单明细表包含不需要唯一值的列 (即没有没有主键),以便可以包含"冗余"数据的任何或所有列。但是,此表中的任何两个记录不应完全相同 (此规则适用于数据库中的任何表)。此表中,应 17 记录-每个对应于各个顺序的产品。例如,为了 2349年,三个 C-789 产品所包含的整个订单的两部分之一。

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

订单明细

订单 ID

产品 ID

Qty

2348

J-558

4

2348

B-205

2

2348

D-4420

5

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

2354

A-2275

3

2355

D-4420

4

2355

C-795

3

2356

C-789

5

复制并粘贴到 Access 从 Excel 的数据

现在,有关销售人员、 客户、 产品、 订单和订单详细信息的信息分为在 Excel 中的单独主题,您可以直接在 Access 中,它将表复制该数据。

创建 Access 表之间的关系并运行查询

您已经移动到 Access 数据后,您可以创建表之间的关系,,然后创建查询返回有关各种主题的信息。例如,您可以创建 3/05/09 和 3/08/09 之间返回订单 ID 和订单输入的销售人员姓名的查询。

此外,您可以创建窗体和报表,可简化数据输入和销售分析。

返回页首

注意: 机器翻译免责声明:本文是由无人工介入的计算机系统翻译的。Microsoft 提供机器翻译是为了帮助非英语国家/地区用户方便阅读有关 Microsoft 产品、服务和技术的内容。由于机器翻译的原因,本文可能包含词汇、语法或文法方面的错误。

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

此信息是否有帮助?

谢谢您的反馈!

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

×