使用ACCESS与EXCEL进行数据分析

使用ACCESS与EXCEL 进行数据比对与分析

天津市南港工业区开发有限公司

财务资产部王东

2014年6月

前言

Microsoft office 办公软件是由微软出品的最流行的办公软件，其中EXCEL 是我们在日常工作中进行数据分析的主要工具，能够胜任日常工作中的绝大部份数据分析工作，但对于一些复杂的数据统计工作就有些力不从心了，在Microsoft Office 办公套件中还有一款产品不被普通用户所熟悉，但对于程序设计人员来说是最基础的数据库解决工具，那就是ACCESS桌面数据库系统，这款软件是一个小型的关系型数据库系统，支持大部份SQL语句，编写此手册就是为了让读者充分发挥EXCEL和ACCESS的长处，将数据分析工作做的更加得心应手，当然本手册只是入门基础，如果读者想更加深入的学习ACCESS的其他功能请在互联网查找相关资料或购买书籍。

由于时间比较仓促，难免存在错误还请各位读者包涵！

编者：王东

2014年6月

目录

1、 ACCESS介绍 (1)

2、启动、新建一个ACCESS文件 (1)

2.1 启动ACCESS (1)

2.2 在ACCESS中新建文件 (1)

2.3使用快捷方式创建ACCESS文件 (2)

3、ACCESS中表、字段说明 (3)

3.1 表的说明 (3)

3.2 字段的说明 (3)

4、数据 (3)

5、建立一个ACCESS数据表 (4)

5.1使用数据录入方式建表【类似EXCEL方式】 (4)

5.2导入EXCEL数据 (5)

6、ACCESS中查询 (12)

7、基本SQL语句 (15)

7.1查询符合条件的记录【SELECT】 (15)

7.1.1显示所有记录 (15)

7.1.2显示指定的列（字段） (15)

7.1.3显示指定的行 (15)

7.1.4对目标表或字段进行重命名 (16)

8、进行数据对比【IN、NOT IN】 (17)

8.1显示在指定序列或不在指定序列的记录 (17)

8.2显示在指定表或不在指定表中的记录 (17)

9、进行数据合并【JOIN】 (18)

9.1 LEFT JOIN 左链接 (18)

9.2 RIGHT JOIN 右链接 (19)

9.3 INNER JOIN 内链接 (19)

9.4用WHERE 在多表中查询只满足条件的记录 (20)

10、对数据进行分类、汇总、统计【SUM、COUNT、GROUP BY 】 (20)

11、将多表进行拼接【UNION】 (21)

11.1 准备工作 (21)

11.2 UNION (21)

11.3 UNION ALL (22)

12、使用DISTINCT 关键字过滤重复数据 (22)

13、将查询结果导出EXCEL进行加工 (23)

14、EXCEL中的数据透视图 (26)

14.1想要的结果 (26)

14.2操作方式 (26)

15、EXCEL中的实用公式 (29)

15.1VLOOKUP公式 (29)

15.2 IF 公式及嵌套 (31)

15.2.1 数据 (31)

15.2.2 需求 (31)

15.2.3 IF公式说明 (31)

15.2.4 实现的IF公式 (31)

15.2.5结果 (31)

1、ACCESS介绍

access 即 Microsoft Office Access 。

Microsoft Office Access是由微软发布的关联式数据库管理系统。它结合了 Microsoft Jet Database Engine 和图形用户界面两项特点，是 Microsoft Office 的系统程式之一。

2、启动、新建一个ACCESS文件

2.1 启动ACCESS

点快捷方式启动

图1

2.2 在ACCESS中新建文件

点左上角图标，选择【新建】菜单，或者直接按（CTRL+N）键

图2

在右侧如图3所示位置输入文件名及文件保存的位置。

图3

点【创建】按钮创建新ACCESS文件。

图4

2.3使用快捷方式创建ACCESS文件

在要保存的目录点鼠标【右键】

图5

选【新建】菜单

图 6

选择菜单

修改系统自动生成的文件名

3、ACCESS中表、字段说明

3.1 表的说明

ACCESS中的表类似于EXCEL中的一个SHEET，用于存放数据。

3.2 字段的说明

ACCESS中的字段类似于EXCEL中的一列。

4、数据

学号姓名性别班级年龄

20140001 张三男高三一17

20140002 李四男高三一19

20140003 王五女高三二16

20140004 马六男高三二18

20140005 李大女高三一18

学员信息表

学号语文数学

20140001 92 75

20140002 99 79

20140003 100 100

20140004 59 72

20140005 76 77

学员成绩表

5、建立一个ACCESS数据表

5.1使用数据录入方式建表【类似EXCEL方式】

1.点创建选项卡中的表

图7

2.录入界面说明

图8

3.使用录入方式创建学员信息表

图9

说明：表头部份为字段（也就是EXCEL中的列表题）

数据的每一行在ACCESS中叫记录

如何修改字段类型

如：我们要把年龄设为数字类型

图 10

最后我们点【保存】按钮，保存表，在弹出的对话框中输入表名【学员信息表】

图11

5.2导入EXCEL数据

当然，在日常工作中我们经常会用EXCEL对数据进行基本的加工，当遇到多表合并、对比时，EXCEL的公式就有些力不从心，如果使用VBA用会被各种安全所限制，所以我们就可以将数据导入到ACCESS进行查询分析。

以4章中的数据为例创建EXCEL文件

图13

图 14

点【外部数据】页签中的【导入】分组中的【EXCEL】

图 15

弹出如图 16 所示界面

图 16

点【浏览】按钮，选择要导入的EXCEL文件

图17 选择好要导入的文件后点【打开】按钮

图18 点【确定】按钮

选择要导入的 SHEET ，在这里我们先导入【学员信息表】页签，点【下一步】按钮

点【下一步】按钮

图 21

在这个界面对每一列进行数据类型设置，如我们要将【学号】列设置为文本，将【年龄】

列设置为数字。

图 22

单击【学号】列，将数据类型设置为【文本】，同样的方法，将【年龄】列设置为【数字】

注意：如果不对数据进行加工，可以全设置为文本，以防导入数据错误。

设置完各列类型后，点【下一步】按钮

点【下一步】按钮

图 24

如果无需对表重新起名，直接点【完成】按钮，如果需要对表改名，输入表名后点【完成】

按钮。

图 25

如果数据导入成功，出现图 25界面，如果失败会提示原因，解决后重新导入即可，点【关闭】

按钮。

数据导入成功后，在左侧会出现导入成功的数据表名，双击表名可查看数据。

对安全警告的解决，如果文件出现了如下图所示安全警告

请点【选项】按钮，

图 28

使用同样方法，导入学员成绩表。

图 29

6、ACCESS中查询

在ACCESS中如果要对数据进行加工、对比、汇总，就要使用ACCESS的查询功能。

点【创建】选项卡下的，【查询设计】按钮。

图 30

点【查询设计】按钮后出现如图31所示界面

图 31

直接点【关闭】按钮，点左上角的【SQL视图】按钮

图 32

图 33 为SQL示图界面，在空白处输入想使用的SQL语句

图 33 SQL语句的使用方法，请详细阅读下一章内容。

输入完SQL语句后，点【运行】按钮执行

图 34 图35 为查询执行后的结果

图 35

点【视图】菜单中的【SQL视图】项可以回到SQL语句界面

7、基本SQL语句

7.1查询符合条件的记录【SELECT】

7.1.1显示所有记录

想要的结果显示【学员信息表】中的所有数据

语句模型SELECT * FROM [表名]

实际语句SELECT * FROM 学员信息表

结果

7.1.2显示指定的列（字段）

想要的结果显示【学员信息表】表中【学号】、【姓名】字段

语句模型SELECT <字段>,<字段>,...,<字段> FROM [表名]

实际语句SELECT 学号,姓名 FROM 学员信息表

结果

7.1.3显示指定的行

想要的结果显示【学员信息表】中姓名为李四的记录

语句模型SELECT * FROM [表名] WHERE <字段>=<条件>

实际语句SELECT * FROM 学员信息表 WHERE 姓名=’李四’

注意文本类型两头加’（小写单引号）号，数字直接写结果

想要的结果显示【学员信息表】年龄大于17岁的记录

实际语句SELECT * FROM 学员信息表 WHERE 年龄 >17

注：可使用>、 <、 <>、 <=、 >=、 =等数学运算符

结果

想要的结果显示【学员信息表】年龄在17到18岁之间的记录

实际语句SELECT * FROM 学员信息表 WHERE (年龄>=17) AND (年龄<=18) 注：AND 与就是两个条件都满足才显示

OR 或就是满足其中一个条件就显示

NOT 非就是不满足条件就显示

结果

想要的结果显示【学员信息表】姓名以张开头的记录

实际语句SELECT * FROM 学员信息表 WHERE 姓名 like '张*'

说明：like 包含用于文本型字段以什么开头 LIKE '开头*'

包含 LIKE '*包含*' 以什么结尾 LIKE '*结尾'

注意：ACCESS的通配符与标准SQL语句有区别，标准SQL语句不

使用*使用%

结果

7.1.4对目标表或字段进行重命名

想要的结果对【学员信息表】表名进行简化操作

语句模型SELECT * FROM [表名] AS A WHERE <条件>

实际语句SELECT * FROM 学员信息表 AS A WHERE A.年龄 <19

结果

想要的结果对【学员信息表】表名、字段进行简化

语句模型SELECT <字段> AS F1 FROM <表名> AS T1

实际语句SELECT 学号 AS XH,姓名 AS XM,年龄 AS NL FROM 学员信息