注意: Microsoft Access 不支持使用应用的敏感度标签导入 Excel 数据。 解决方法是,可以在导入之前删除标签,然后在导入后重新应用标签。 有关详细信息,请参阅 在 Office 中将敏感度标签应用于文件和电子邮件。
本文介绍如何将数据从 Excel 移动到 Access,以及如何将数据转换为关系表,以便可以同时使用 Microsoft Excel 和 Access。 总之,Access 最适合捕获、存储、查询和共享数据,Excel 最适合用于计算、分析和可视化数据。
两篇文章 :使用 Access 或 Excel 管理数据 ,以及 将 Access 与 Excel 配合使用的 10 大原因,讨论哪个程序最适合特定任务,以及如何结合使用 Excel 和 Access 来创建实用的解决方案。
将数据从 Excel 移动到 Access 时,此过程有三个基本步骤。
注意: 有关 Access 中的数据建模和关系的信息,请参阅 数据库设计基础知识。
步骤 1:将数据从 Excel 导入到 Access
导入数据是一种操作,如果你花一些时间准备和清理数据,可以更顺利地进行。 导入数据就像移动到新家一样。 如果你在搬家之前清理和整理你的财产,安顿到你的新家要容易得多。
在导入之前清理数据
在将数据导入 Access 之前,在 Excel 中,最好是:
-
将包含非原子数据的单元格 (即,一个单元格中的多个值) 转换为多个列。 例如,“Skills”列中包含多个技能值(如“C# 编程”、“VBA 编程”和“Web 设计”)的单元格应进行细分,以分隔每个仅包含一个技能值的列。
-
使用 TRIM 命令可删除前导、尾随和多个嵌入空格。
-
删除非打印字符。
-
查找并修复拼写和标点错误。
-
删除重复行或重复字段。
-
确保数据列不包含混合格式,尤其是格式为文本的数字或格式为数字的日期。
有关详细信息,请参阅以下 Excel 帮助主题:
注意: 如果数据清理需求很复杂,或者你没有时间或资源自行自动执行该过程,则可以考虑使用第三方供应商。 有关详细信息,请在 Web 浏览器中按喜欢的搜索引擎搜索“数据清理软件”或“数据质量”。
在导入时选择最佳数据类型
在 Access 中的导入操作期间,你想要做出正确的选择,以便在任何需要手动干预的) 转换错误时收到少量 (。 下表汇总了将数据从 Excel 导入到 Access 时 Excel 数字格式和 Access 数据类型的转换方式,并提供了一些提示,介绍了在导入电子表格向导中选择的最佳数据类型。
Excel 数字格式 |
Access 数据类型 |
批注 |
最佳做法 |
---|---|---|---|
文本 |
文本、备注 |
Access Text 数据类型最多存储 255 个字符的字母数字数据。 Access 备注数据类型最多存储 65,535 个字符的字母数字数据。 |
选择 “备注” 以避免截断任何数据。 |
数字、百分比、分数、科学 |
数字 |
Access 具有一种数字数据类型,该数据类型因字段大小属性而异, (字节、整数、长整型、单精度、双精度、小数) 。 |
选择 “双精度 ”可避免任何数据转换错误。 |
日期 |
日期 |
Access 和 Excel 都使用相同的序列号来存储日期。 在 Access 中,日期范围较大:从 -657,434 (100 年 1 月 1 日 ) 到 2,958,465 (9999 年 12 月 31 日,) 。 由于 Access 无法识别 Macintosh) Excel 中使用的 1904 日期系统 (,因此您需要在 Excel 或 Access 中转换日期以避免混淆。 有关详细信息,请参阅 更改日期系统、格式或两位数年份解释 和 在 Excel 工作簿中导入或链接到数据。 |
选择 “日期”。 |
时间 |
时间 |
Access 和 Excel 都使用相同的数据类型存储时间值。 |
选择 “时间”,这通常是默认值。 |
货币、会计 |
货币 |
在 Access 中,Currency 数据类型将数据存储为 8 字节数字,精度为 4 位小数,并用于存储财务数据并防止值舍入。 |
选择 “货币”,这通常是默认值。 |
布尔 |
是/否 |
Access 对所有是值使用 -1,对所有 No 值使用 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 数据
在 Access 中规范化数据并创建可重构原始数据的查询或表后,从 Excel 连接到 Access 数据就很简单了。 你的数据现在作为外部数据源在 Access 中,因此可以通过数据连接连接到工作簿,数据连接是用于查找、登录和访问外部数据源的信息容器。 连接信息存储在工作簿中,也可以存储在连接文件中,例如 Office 数据连接 (ODC) 文件 (.odc 文件扩展名) 或数据源名称文件 (.dsn 扩展名) 。 连接到外部数据后,还可以在 Access 中更新数据时自动刷新 (或从 Access 更新 excel 工作簿) 。
有关详细信息,请参阅 从外部数据源导入数据 (Power Query)。
将数据引入 Access
本部分将引导你完成规范化数据的以下阶段:将 Salesperson 和 Address 列中的值分解为最原子的部分,将相关主题分离到自己的表中,将这些表从 Excel 复制并粘贴到 Access 中,在新创建的 Access 表之间创建关键关系,并在 Access 中创建并运行一个简单的查询以返回信息。
非规范化格式的示例数据
以下工作表包含 Salesperson 列和地址列中的非原子值。 这两列应拆分为两列或更多个单独的列。 此工作表还包含有关销售人员、产品、客户和订单的信息。 此信息还应按主题进一步拆分为单独的表。
销售人员 |
订单 ID |
订单日期 |
产品 ID |
数量 |
价格 |
Customer Name |
地址 |
手机 |
---|---|---|---|---|---|---|---|---|
李,耶鲁大学 |
2349 |
3/4/09 |
C-789 |
3 |
$7.00 |
Fourth Coffee |
7007 康奈尔圣雷德蒙德, 西澳大利亚州 98199 |
425-555-0201 |
李,耶鲁大学 |
2349 |
3/4/09 |
C-795 |
6 |
$9.75 |
Fourth Coffee |
7007 康奈尔圣雷德蒙德, 西澳大利亚州 98199 |
425-555-0201 |
亚当斯,艾伦 |
2350 |
3/4/09 |
A-2275 |
2 |
$16.75 |
嘉元实业 |
1025 哥伦比亚圈柯克兰, 西澳大利亚州 98234 |
425-555-0185 |
亚当斯,艾伦 |
2350 |
3/4/09 |
F-198 |
6 |
$5.25 |
嘉元实业 |
1025 哥伦比亚圈柯克兰, 西澳大利亚州 98234 |
425-555-0185 |
亚当斯,艾伦 |
2350 |
3/4/09 |
B-205 |
1 |
$4.50 |
嘉元实业 |
1025 哥伦比亚圈柯克兰, 西澳大利亚州 98234 |
425-555-0185 |
Hance,吉姆 |
2351 |
3/4/09 |
C-795 |
6 |
$9.75 |
康拓工程有限公司 |
2302 哈佛大道贝尔维尤, 西澳大利亚州 98227 |
425-555-0222 |
Hance,吉姆 |
2352 |
3/5/09 |
A-2275 |
2 |
$16.75 |
嘉元实业 |
1025 哥伦比亚圈柯克兰, 西澳大利亚州 98234 |
425-555-0185 |
Hance,吉姆 |
2352 |
3/5/09 |
D-4420 |
3 |
$7.25 |
嘉元实业 |
1025 哥伦比亚圈柯克兰, 西澳大利亚州 98234 |
425-555-0185 |
科赫,里特 |
2353 |
3/7/09 |
A-2275 |
6 |
$16.75 |
Fourth Coffee |
7007 康奈尔圣雷德蒙德, 西澳大利亚州 98199 |
425-555-0201 |
科赫,里特 |
2353 |
3/7/09 |
C-789 |
5 |
$7.00 |
Fourth Coffee |
7007 康奈尔圣雷德蒙德, 西澳大利亚州 98199 |
425-555-0201 |
最小部分的信息:原子数据
使用此示例中的数据,可以使用 Excel 中的 “文本到列 ”命令将单元格 (的“原子”部分(如街道地址、城市、州和邮政编码)) 分隔为离散列。
下表显示了同一工作表中的新列,这些列已拆分为原子化所有值。 请注意,Salesperson 列中的信息已拆分为“姓氏”和“名字”列,并且“地址”列中的信息已拆分为“街道地址”、“城市”、“州”和“邮政编码”列。 此数据采用“第一个正常形式”。
姓氏 |
名字 |
|
街道地址 |
城市 |
州/省 |
邮政编码 |
---|---|---|---|---|---|---|
Li |
耶鲁 |
2302 哈佛大道 |
Bellevue |
WA |
98227 |
|
亚当斯 |
艾伦 |
1025 哥伦比亚圈 |
柯克兰 |
WA |
98234 |
|
Hance |
米申 |
2302 哈佛大道 |
Bellevue |
WA |
98227 |
|
科赫 |
芦苇 |
7007 康奈尔圣雷德蒙德 |
雷德蒙德 |
WA |
98199 |
在 Excel 中将数据分解为有条理的主题
下面的几个示例数据表在拆分为销售人员、产品、客户和订单的表格后,显示 Excel 工作表中的相同信息。 表设计不是最终的,但它在正确的轨道上。
Salespersons 表仅包含有关销售人员的信息。 请注意,每个记录都有一个唯一的 ID (SalesPerson ID) 。 SalesPerson ID 值将在“订单”表中用于将订单连接到销售人员。
售货员 |
||
---|---|---|
Salesperson ID |
姓氏 |
名字 |
101 |
Li |
耶鲁 |
103 |
亚当斯 |
艾伦 |
105 |
Hance |
米申 |
107 |
科赫 |
芦苇 |
“产品”表仅包含有关产品的信息。 请注意,每条记录都有一个唯一的 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 康奈尔街 |
雷德蒙德 |
WA |
98199 |
425-555-0201 |
“订单”表包含有关订单、销售人员、客户和产品的信息。 请注意,每个记录都有一个唯一的 ID (订单 ID) 。 此表中的某些信息需要拆分为包含订单详细信息的附加表,以便“订单”表仅包含四列:唯一的订单 ID、订单日期、销售人员 ID 和客户 ID。 此处显示的表尚未拆分为“订单详细信息”表。
订单 |
|||||
---|---|---|---|---|---|
订单 ID |
订单日期 |
SalesPerson ID |
客户 ID |
产品 ID |
数量 |
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 条记录是有意义的。 请注意,Orders 表具有唯一 ID (Order ID) ,该 ID 将从“订单详细信息”表中引用。
Orders 表的最终设计应如下所示:
订单 |
|||
---|---|---|---|
订单 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 |
数量 |
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 技术社区中咨询专家或在社区中获取支持。