使用ACCESS与EXCEL进行数据分析.doc

使用ACCESS与EXCEL进行数据比对与分析天津市南港工业区开发有限公司财务资产部 王东2014年6月使用ACCESS与EXCEL进行数据比对与分析前 言 Microsoft office 办公软件是由微软出品的最流行的办公软件，其中EXCEL是我们在日常工作中进行数据分析的主要工具，能够胜任日常工作中的绝大部份数据分析工作，但对于一些复杂的数据统计工作就有些力不从心了，在Microsoft Office 办公套件中还有一款产品不被普通用户所熟悉，但对于程序设计人员来说是最基础的数据库解决工具，那就是ACCESS桌面数据库系统，这款软件是一个小型的关系型数据库系统，支持大部份SQL语句，编写此手册就是为了让读者充分发挥EXCEL和ACCESS的长处，将数据分析工作做的更加得心应手，当然本手册只是入门基础，如果读者想更加深入的学习ACCESS的其他功能请在互联网查找相关资料或购买书籍。由于时间比较仓促，难免存在错误还请各位读者包涵！编者：王东2014年6月目 录1、 ACCESS介绍12、启动、新建一个ACCESS文件12.1 启动ACCESS12.2 在ACCESS中新建文件12.3使用快捷方式创建ACCESS文件23、ACCESS中表、字段说明33.1 表的说明33.2 字段的说明34、数据35、建立一个ACCESS数据表45.1使用数据录入方式建表【类似EXCEL方式】45.2导入EXCEL数据56、ACCESS中查询127、基本SQL语句157.1查询符合条件的记录【SELECT】157.1.1显示所有记录157.1.2显示指定的列（字段）157.1.3显示指定的行157.1.4对目标表或字段进行重命名168、进行数据对比【IN、NOT IN】178.1显示在指定序列或不在指定序列的记录178.2显示在指定表或不在指定表中的记录179、进行数据合并【JOIN】189.1 LEFT JOIN 左链接189.2 RIGHT JOIN 右链接199.3 INNER JOIN 内链接199.4用WHERE 在多表中查询只满足条件的记录2010、对数据进行分类、汇总、统计【SUM、COUNT、GROUP BY 】2011、将多表进行拼接【UNION】2111.1 准备工作2111.2 UNION2111.3 UNION ALL2212、使用DISTINCT 关键字过滤重复数据2213、将查询结果导出EXCEL进行加工2314、EXCEL中的数据透视图2614.1想要的结果2614.2操作方式2615、EXCEL中的实用公式2915.1VLOOKUP公式2915.2 IF 公式及嵌套3115.2.1 数据3115.2.2 需求3115.2.3 IF公式说明3115.2.4 实现的IF公式3115.2.5结果31 3 1、 ACCESS介绍access 即 Microsoft Office Access 。Microsoft Office Access是由微软发布的关联式数据库管理系统。它结合了 Microsoft Jet Database Engine 和 图形用户界面两项特点，是 Microsoft Office 的系统程式之一。2、启动、新建一个ACCESS文件2.1 启动ACCESS点 快捷方式 启动图12.2 在ACCESS中新建文件点 左上角 图标，选择【新建】菜单，或者直接按（CTRL+N）键图2在右侧如图3所示位置输入文件名及文件保存的位置。图3点【创建】按钮创建新ACCESS文件。图42.3使用快捷方式创建ACCESS文件在要保存的目录点鼠标【右键】图5选 【新建】 菜单图 6选择 菜单修改系统自动生成的文件名3、ACCESS中表、字段说明3.1 表的说明ACCESS中的表类似于EXCEL中的一个SHEET，用于存放数据。3.2 字段的说明ACCESS中的字段类似于EXCEL中的一列。4、数据学号姓名性别班级年龄20140001张三男高三一1720140002李四男高三一1920140003王五女高三二1620140004马六男高三二1820140005李大女高三一18学员信息表学号语文数学20140001927520140002997920140003100100201400045972201400057677学员成绩表5、建立一个ACCESS数据表5.1使用数据录入方式建表【类似EXCEL方式】1. 点 创建 选项卡中的表图72. 录入界面说明图83. 使用录入方式创建学员信息表图9说明：表头部份为字段（也就是EXCEL中的列表题）数据的每一行在ACCESS中叫记录如何修改字段类型如：我们要把年龄设为数字类型图 10最后我们点 【保存】 按钮，保存表，在弹出的对话框中输入表名【学员信息表】图 115.2导入EXCEL数据当然，在日常工作中我们经常会用EXCEL对数据进行基本的加工，当遇到多表合并、对比时，EXCEL的公式就有些力不从心，如果使用VBA用会被各种安全所限制，所以我们就可以将数据导入到ACCESS进行查询分析。以4章中的数据为例创建EXCEL文件图 13图 14点 【外部数据】页签中的【导入】分组中的【EXCEL】图 15弹出如图 16 所示界面图 16点 【浏览】按钮，选择要导入的EXCEL文件图 17选择好要导入的文件后点【打开】按钮图 18点 【确定】按钮图 19选择要 导入 的 SHEET ，在这里我们先导入【学员信息表】页签，点【下一步】按钮图 20点 【下一步】按钮图 21在这个界面对每一列进行数据类型设置，如我们要将【学号】列设置为文本，将【年龄】列设置为数字。图 22单击【学号】列，将数据类型设置为【文本】，同样的方法，将【年龄】列设置为【数字】注意：如果不对数据进行加工，可以全设置为文本，以防导入数据错误。设置完各列类型后，点【下一步】按钮图 23点 【下一步】按钮图 24如果 无需对表重新起名，直接点【完成】按钮，如果需要对表改名，输入表名后点【完成】按钮。图 25如果数据导入成功，出现图 25界面，如果失败会提示原因，解决后重新导入即可，点【关闭】按钮。图 26数据导入成功后，在左侧会出现导入成功的数据表名，双击表名可查看数据。图 27对 安全警告 的解决，如果文件出现了如下图所示安全警告请点【选项】按钮，图 28使用同样方法，导入学员成绩表。图 296、ACCESS中查询在ACCESS中如果要对数据进行加工、对比、汇总，就要使用ACCESS的查询功能。点【创建】选项卡下的，【查询设计】 按钮。图 30点 【查询设计】按钮后出现如图31所示界面图 31直接点 【关闭】按钮，点左上角的【SQL视图】按钮图 32图 33 为SQL示图界面，在空白处输入想使用的SQL语句图 33SQL语句的使用方法，请详细阅读下一章内容。输入完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 学员信息表 AS A WHERE A.年龄 17注：因为查询没有执行，所以原数据表中的字段并没有成为简化字段，所以条件中还是要用原字段名，表名可以使用简化名结果8、进行数据对比【IN、NOT IN】8.1显示在指定序列或不在指定序列的记录想要的结果显示【学员信息表】中姓名为指定人员的记录语句模型SELECT FROM WHERE 字段 in (值1,值2.)实际语句SELECT * FROM 学员信息表 WHERE 姓名 in (张三,李四,王五)说明：如果数据类型为文本型需要对值加单引号，如果是数字只需用逗号分隔结果想要的结果显示【学员信息表】中年龄不在指定数值内的记录实际语句SELECT * FROM 学员信息表 WHERE 年龄 NOT IN (17,16)结果8.2显示在指定表或不在指定表中的记录想要的结果显示【学员信息表】中的学号在【学员成绩表】中存在的记录语句模型SELECT FROM WHERE 字段 in (SELECT FROM )场情说明我们想找出参加了本次考试的学员有哪些人，我们在练习数据中建立了一个学员信息表这张表为基本信息表，一个学员成绩表这张表为基本信息的扩展表主要记录每个学员的相关科目成绩，在扩展表中我们对每个参加考试的学员都录入了成绩信息，而没有记录学员的基本情况，这两张表用学号即可关联，如果在扩展表中存在成绩那么这个学员就参加了考试，如果没有成绩则没有参加，我们现在想找出来谁都参加了考试。注：如果想对多个表进行分析，这多个表要有一个关键字段，在我们的样表中每张表都有学号进行确认这个记录属于谁。实际语句SELECT * FROM 学员信息表 WHERE 学号 in (SELECT 学号 FROM 学员成绩表 )说明：IN 后面括号中的查询只允许选择一个字段。结果说明：在做此语句前为了显示出区别，我将【学员成绩表】中的最后一条记录删除了，所以出现了4条记录，样表没有删除之前是5条。想要的结果显示【学员信息表】中的学号在【学员成绩表】中存在的记录语句模型SELECT FROM WHERE 字段 NOT IN (SELECT FROM )实际语句SELECT * FROM 学员信息表 WHERE 学号 NOT in (SELECT 学号 FROM 学员成绩表 )结果我刚才删除了这个结果的成绩信息。9、进行数据合并【JOIN】9.1 LEFT JOIN 左链接语句说明以放在左边的表为主表，去查询其他表中的数据与左边的表进行数据组合，如果其他表的记录中的关键字段值不在左表（主表）中存在则丢弃，如果存在则显示，如果左表（主表）中的关键字段值其他表不存在，则左表链接后的他表字段值为空，如果其他表中存在多行主表中的关键字段值则生成多行记录。场景说明我们在样表数据中【学员基本表】中存放了学员基本信息，在【学员成绩表】中存放了学员的各科目成绩，这两张表是单独存在的，我们现在想做一张即有学员基本信息又有学员成绩的表。语句模型SELECT 表1.字段1,表1,字段n,表2.字段1,表2.字段n FROM 表1 LEFT JOIN 表2 ON 表1.关键字段=表2.关键字段实际语句SELECT A.*,B.语文,B.数学 FROM 学员信息表 AS A LEFT JOIN 学员成绩表 AS B ON A.学号=B.学号结果9.2 RIGHT JOIN 右链接语句说明以右边的表为主表，功能与左链接相同语句模型SELECT 表1.字段1,表1,字段n,表2.字段1,表2.字段n FROM 表1 RIGHT JOIN 表2 ON 表1.关键字段=表2.关键字段实际语句SELECT A.*,B.语文,B.数学 FROM 学员信息表 AS A RIGHT JOIN 学员成绩表 AS B ON A.学号=B.学号结果这里我们可以看到这个查询丢弃了不在右边表中存在的左表数据。9.3 INNER JOIN 内链接语句说明不以任何表为主表，只显示符合条件的记录语句模型SELECT 表1.字段1,表1,字段n,表2.字段1,表2.字段n FROM 表1 INNER JOIN 表2 ON 表1.关键字段=表2.关键字段实际语句SELECT A.*,B.语文,B.数学 FROM 学员信息表 AS A INNER JOIN 学员成绩表 AS B ON A.学号=B.学号结果9.4用WHERE 在多表中查询只满足条件的记录语句说明不以任何表为主表，只显示符合条件的记录语句模型SELECT 表1.字段1,表1,字段n,表2.字段1,表2.字段n FROM 表1 ,表2 WHERE 表1.关键字段=表2.关键字段WHERE 后面可以跟多个条件，之间可以用逻辑运算符进行连接。如 WHERE A.姓名=张三 and b.学号=201400001 and a.学号=b.学号实际语句SELECT A.*,B.语文,B.数学 FROM 学员信息表 AS A, 学员成绩表 AS B WHERE A.学号=B.学号结果10、对数据进行分类、汇总、统计【SUM、COUNT、GROUP BY 】语句说明一般来说GROUP BY 语句要与 SUM 或 COUNT 组合使用想要的结果我们想对【学员信息表】中的每个班有多少人进行统计语句模型SELECT COUNT(关键字段) AS , FROM 表名 GROUP BY ,实际语句SELECT COUNT(姓名) AS 人数,班级 FROM 学员信息表 GROUP BY 班级结果对统计结果加条件筛选我们现在只想看到人数大于2个人的班有哪几个班语句模型SELECT COUNT(关键字段) AS , FROM 表名 GROUP BY , HAVING COUNT(关键字段) 实际语句SELECT COUNT(姓名) AS 人数,班级 FROM 学员信息表 GROUP BY 班级 HAVING COUNT(姓名) 2结果说明COUNT 中的关键字段 可以是任何一个字段语句说明SUM 汇总想要的结果我们想对【学员成绩表】中的语文、数学求总成绩语句模型SELECT SUM(字段1) as 求和1,SUM(字段n) as 求和n FROM 表名实际语句SELECT SUM(语文) as 语文总分,SUM(数学) as 数学总分 FROM 学员成绩表结果分组统计我们现在想按班级进行分类汇总语句模型SELECT SUM(字段1) as 求和1,SUM(字段n) as 求和n,分组字段 FROM 表名 GROUP BY 分组字段实际语句SELECT SUM(语文) as 语文总分,SUM(数学) as 数学总分,班级 FROM (SELECT * FROM 学员信息表 AS A,学员成绩表 AS B WHERE A.学号=B.学号) GROUP BY 班级说明因为我们的样表是单独的要先进行数据组合然后再查询，以上语句我们使用了SQL语句的嵌套结果11、将多表进行拼接【UNION】11.1 准备工作我们在ACCESS中新建一张表，起名为【学员信息补表】学员信息补表ID学号姓名性别班级年龄420140004马六男高三大女高三一男高三二女高三二1711.2 UNION语句说明UNION 将多个结构相同的表进行纵向拼合形成新表,并丢弃重复的记录 想要的结果我们想对【学员信息表】和【学员信息补表】进行纵向合同 语句模型SELECT 表1.字段,表1.字段N FROM 表1 UNION (SELECT 表2.字段,表2.字段N)说明表1与表2的字段数要相等，并且一一对应实际语句SELECT 学号,姓名,性别,班级,年龄 FROM 学员信息表 UNION SELECT 学号,姓名,性别,班级,年龄 FROM 学员信息补表结果11.3 UNION ALL语句说明UNION ALL将多个结构相同的表进行纵向拼合形成新表,不丢弃重复的记录 语名模式SELECT 表1.字段,表1.字段N FROM 表1 UNION (SELECT 表2.字段,表2.字段N)实际语句SELECT 学号,姓名,性别,班级,年龄 FROM 学员信息表 UNION ALLSELECT 学号,姓名,性别,班级,年龄 FROM 学员信息补表结果12、使用DISTINCT 关键字过滤重复数据语句说明DISTINCT 丢弃重复的记录想要的结果我们对UNION ALL的例子结果进行重复字段过滤语句模型SELECT DISTINCT 字段1,字段n FROM 表实际语句SELECT DISTINCT 学号,姓名,性别,班级,年龄 FROM ( SELECT 学号,姓名,性别,班级,年龄 FROM 学员信息表 UNION ALLSELECT 学号,姓名,性别,班级,年龄 FROM 学员信息补表 )原结果现结果13、将查询结果导出EXCEL进行加工我们在ACCESS中的查询中，编写完相应语句并得到正确的结果后，我们可以被这个查询进行保存图 36点 菜单栏的【保存】图标按钮图 37输入完要起的名后，点确定按钮。打开或新建一个EXCEL文件图 38点 【数据】 页签中的 【自ACCESS】 按钮图39选择我们已经编辑好的ACCESS文件图40 选择想要导入的ACCESS中的表或视图（查询），类型TABLE为表，VIEW为视图（查询）图41选择你想要导入的起始位置后点【确定】按钮图4214、EXCEL中的数据透视图14.1想要的结果以上节导入的EXCEL数据为例 ，我们想以班级为行、性别为列，求平均年龄，表样如下班级男平均年龄女平均年龄高三一高三二14.2操作方式选中你想要进行数据分析的区域，第一行必须是标题，不能为空图 43点 【插入】页签中的【数据透视表】图 4