分享
分享赚钱 收藏 举报 版权申诉 / 37

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

  • 上传人:精品资料
  • 文档编号:9683094
  • 上传时间:2019-08-23
  • 格式:DOC
  • 页数:37
  • 大小:11.87MB
  • 配套讲稿:

    如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。

    特殊限制:

    部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。

    关 键  词:
    使用ACCESS与EXCEL进行数据分析.doc
    资源描述:

    1、使用 ACCESS 与 EXCEL进行数据比对与分析天津市南港工业区开发有限公司财务资产部 王东2014 年 6 月使用 ACCESS 与 EXCEL 进行数据比对与分析 1 前 言 Microsoft office 办公软件是由微软出品的最流行的办公软件,其中EXCEL 是我们在日常工作中进行数据分析的主要工具,能够胜任日常工作中的绝大部份数据分析工作,但对于一些复杂的数据统计工作就有些力不从心了,在 Microsoft Office 办公套件中还有一款产品不被普通用户所熟悉,但对于程序设计人员来说是最基础的数据库解决工具,那就是 ACCESS 桌面数据库系统,这款软件是一个小型的关系型数据

    2、库系统,支持大部份 SQL 语句,编写此手册就是为了让读者充分发挥 EXCEL 和 ACCESS 的长处,将数据分析工作做的更加得心应手,当然本手册只是入门基础,如果读者想更加深入的学习 ACCESS 的其他功能请在互联网查找相关资料或购买书籍。由于时间比较仓促,难免存在错误还请各位读者包涵!编者:王东2014 年 6 月使用 ACCESS 与 EXCEL 进行数据比对与分析 2 目 录1、 ACCESS 介绍 .12、启动、新建一个 ACCESS 文件 12.1 启动 ACCESS12.2 在 ACCESS 中新建文件 .12.3 使用快捷方式创建 ACCESS 文件 23、ACCESS 中

    3、表、字段说明 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、进行

    4、数据合并【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 UNION .2111.3 UNION ALL .2212、使用 DISTINCT 关键字过滤重复数据 .2213、将查询结果导出 EXCEL 进行加工 .2314、EXCEL 中的数据透视图 .2614.1 想要的结果 .2614.

    5、2 操作方式 .2615、EXCEL 中的实用公式 .2915.1VLOOKUP 公式 29使用 ACCESS 与 EXCEL 进行数据比对与分析 3 15.2 IF 公式及嵌套 .3115.2.1 数据 3115.2.2 需求 3115.2.3 IF 公式说明 3115.2.4 实现的 IF 公式 .3115.2.5 结果 .31使用 ACCESS 与 EXCEL 进行数据比对与分析| 11、 ACCESS 介绍access 即 Microsoft Office Access 。Microsoft Office Access 是由微软发布的关联式数据库管理系统。它结合了 Microsoft

    6、Jet Database Engine 和 图形用户界面两项特点,是 Microsoft Office 的系统程式之一。2、启动、新建一个 ACCESS 文件2.1 启动 ACCESS点 快捷方式 启动图 12.2 在 ACCESS 中新建文件点 左上角 图标,选择【新建】菜单,或者直接按(CTRL+N)键使用 ACCESS 与 EXCEL 进行数据比对与分析| 2图 2在右侧如图 3 所示位置输入文件名及文件保存的位置。图 3点【创建】按钮创建新 ACCESS 文件。图 42.3 使用快捷方式创建 ACCESS 文件在要保存的目录点鼠标【右键】图 5使用 ACCESS 与 EXCEL 进行数

    7、据比对与分析| 3选 【新建】 菜单图 6选择 菜单修改系统自动生成的文件名3、 ACCESS 中表、字段说明3.1 表的说明ACCESS 中的表类似于 EXCEL 中的一个 SHEET,用于存放数据。3.2 字段的说明ACCESS 中的字段类似于 EXCEL 中的一列。4、数据学号 姓名 性别 班级 年龄20140001 张三 男 高三一 1720140002 李四 男 高三一 1920140003 王五 女 高三二 1620140004 马六 男 高三二 1820140005 李大 女 高三一 18学员信息表学号 语文 数学20140001 92 7520140002 99 79使用 AC

    8、CESS 与 EXCEL 进行数据比对与分析| 420140003 100 10020140004 59 7220140005 76 77学员成绩表5、建立一个 ACCESS 数据表5.1 使用数据录入方式建表【类似 EXCEL 方式】1.点 创建 选项卡中的表图 72.录入界面说明图 83.使用录入方式创建学员信息表使用 ACCESS 与 EXCEL 进行数据比对与分析| 5图 9说明:表头部份为字段(也就是 EXCEL 中的列表题)数据的每一行在 ACCESS 中叫记录如何修改字段类型如:我们要把年龄设为数字类型图 10最后我们点 【保存】 按钮,保存表,在弹出的对话框中输入表名【学员信息

    9、表】图 115.2 导入 EXCEL 数据当然,在日常工作中我们经常会用 EXCEL 对数据进行基本的加工,当遇到多表合并、对比时,EXCEL 的公式就有些力不从心,如果使用 VBA 用会被各种安全所限制,所以我们就可以将数据导入到 ACCESS 进行查询分析。以 4 章中的数据为例创建 EXCEL 文件使用 ACCESS 与 EXCEL 进行数据比对与分析| 6图 13图 14点 【外部数据】页签中的【导入】分组中的【EXCEL】图 15弹出如图 16 所示界面使用 ACCESS 与 EXCEL 进行数据比对与分析| 7图 16点 【浏览】按钮,选择要导入的 EXCEL 文件图 17选择好要

    10、导入的文件后点【打开】按钮使用 ACCESS 与 EXCEL 进行数据比对与分析| 8图 18点 【确定】按钮图 19选择要 导入 的 SHEET ,在这里我们先导入【学员信息表】页签,点【下一步】按钮使用 ACCESS 与 EXCEL 进行数据比对与分析| 9图 20点 【下一步】按钮图 21在这个界面对每一列进行数据类型设置,如我们要将【学号】列设置为文本,将【年龄】列设置为数字。使用 ACCESS 与 EXCEL 进行数据比对与分析| 10图 22单击【学号】列,将数据类型设置为【文本】 ,同样的方法,将【年龄】列设置为【数字】注意:如果不对数据进行加工,可以全设置为文本,以防导入数据错

    11、误。设置完各列类型后,点【下一步】按钮图 23点 【下一步】按钮使用 ACCESS 与 EXCEL 进行数据比对与分析| 11图 24如果 无需对表重新起名,直接点【完成】按钮,如果需要对表改名,输入表名后点【完成】按钮。图 25如果数据导入成功,出现图 25 界面,如果失败会提示原因,解决后重新导入即可,点【关闭】按钮。使用 ACCESS 与 EXCEL 进行数据比对与分析| 12图 26数据导入成功后,在左侧会出现导入成功的数据表名,双击表名可查看数据。图 27对 安全警告 的解决,如果文件出现了如下图所示安全警告请点【选项】按钮,图 28使用同样方法,导入学员成绩表。使用 ACCESS

    12、与 EXCEL 进行数据比对与分析| 13图 296、 ACCESS 中查询在 ACCESS 中如果要对数据进行加工、对比、汇总,就要使用 ACCESS 的查询功能。点【创建】选项卡下的, 【查询设计】 按钮。图 30点 【查询设计】按钮后出现如图 31 所示界面图 31直接点 【关闭】按钮,点左上角的【SQL 视图】按钮使用 ACCESS 与 EXCEL 进行数据比对与分析| 14图 32图 33 为 SQL 示图界面,在空白处输入想使用的 SQL 语句图 33SQL 语句的使用方法,请详细阅读下一章内容。输入完 SQL 语句后,点【运行】按钮执行图 34图 35 为查询执行后的结果使用 A

    13、CCESS 与 EXCEL 进行数据比对与分析| 15图 35点【视图】菜单中的【SQL 视图】项可以回到 SQL 语句界面7、基本 SQL 语句7.1 查询符合条件的记录【SELECT】7.1.1 显示所有记录7.1.2 显示指定的列(字段)想要的结果 显示【学员信息表】中的所有数据语句模型 SELECT * FROM 表名实际语句 SELECT * FROM 学员信息表结果想要的结果 显示【学员信息表】表中【学号】 、 【姓名】字段语句模型 SELECT ,., FROM 表名实际语句 SELECT 学号,姓名 FROM 学员信息表使用 ACCESS 与 EXCEL 进行数据比对与分析|

    14、167.1.3 显示指定的行结果想要的结果 显示【学员信息表】中姓名为李四的记录语句模型 SELECT * FROM 表名 WHERE =实际语句 SELECT * FROM 学员信息表 WHERE 姓名=李四注意文本类型两头加 (小写单引号)号,数字直接写结果想要的结果 显示【学员信息表】年龄大于 17 岁的记录实际语句 SELECT * FROM 学员信息表 WHERE 年龄 17注:可使用、 、 =、 =等数学运算符结果想要的结果 显示【学员信息表】年龄在 17 到 18 岁之间的记录实际语句 SELECT * FROM 学员信息表 WHERE (年龄=17) AND (年龄实际语句 S

    15、ELECT * FROM 学员信息表 AS A WHERE A.年龄 AS F1 FROM AS T1实际语句 SELECT 学号 AS XH,姓名 AS XM,年龄 AS NL FROM 学员信使用 ACCESS 与 EXCEL 进行数据比对与分析| 188、 进行数据对比【IN、NOT IN】8.1 显示在指定序列或不在指定序列的记录8.2 显示在指定表或不在指定表中的记录息表 AS A WHERE A.年龄 17注:因为查询没有执行,所以原数据表中的字段并没有成为简化字段,所以条件中还是要用原字段名,表名可以使用简化名结果想要的结果 显示【学员信息表】中姓名为指定人员的记录语句模型 SE

    16、LECT FROM WHERE 字段 in (值 1,值 2.)实际语句 SELECT * FROM 学员信息表 WHERE 姓名 in (张三,李四,王五)说明:如果数据类型为文本型需要对值加单引号,如果是数字只需用逗号分隔结果想要的结果 显示【学员信息表】中年龄不在指定数值内的记录实际语句 SELECT * FROM 学员信息表 WHERE 年龄 NOT IN (17,16)结果想要的结果 显示【学员信息表】中的学号在【学员成绩表】中存在的记录语句模型 SELECT FROM WHERE 字段 in (SELECT FROM )使用 ACCESS 与 EXCEL 进行数据比对与分析| 19

    17、9、 进行数据合并【JOIN】9.1 LEFT JOIN 左链接场情说明 我们想找出参加了本次考试的学员有哪些人,我们在练习数据中建立了一个学员信息表这张表为基本信息表,一个学员成绩表这张表为基本信息的扩展表主要记录每个学员的相关科目成绩,在扩展表中我们对每个参加考试的学员都录入了成绩信息,而没有记录学员的基本情况,这两张表用学号即可关联,如果在扩展表中存在成绩那么这个学员就参加了考试,如果没有成绩则没有参加,我们现在想找出来谁都参加了考试。注:如果想对多个表进行分析,这多个表要有一个关键字段,在我们的样表中每张表都有学号进行确认这个记录属于谁。实际语句 SELECT * FROM 学员信息表

    18、 WHERE 学号 in (SELECT 学号 FROM 学员成绩表 )说明:IN 后面括号中的查询只允许选择一个字段。结果说明:在做此语句前为了显示出区别,我将【学员成绩表】中的最后一条记录删除了,所以出现了 4 条记录,样表没有删除之前是 5 条。想要的结果 显示【学员信息表】中的学号在【学员成绩表】中存在的记录语句模型 SELECT FROM WHERE 字段 NOT IN (SELECT FROM )实际语句 SELECT * FROM 学员信息表 WHERE 学号 NOT in (SELECT 学号 FROM 学员成绩表 )结果我刚才删除了这个结果的成绩信息。语句说明 以放在左边的表

    19、为主表,去查询其他表中的数据与左边的表进行数据组合,如果其他表的记录中的关键字段值不在左表(主表)中存在则丢弃,如果存在则显示,如果左表(主表)中的关键字段值其他表不存在,则左表链接后的他表字段值为空,如果其他表中存在多行主表中的关键字段值则生成多行记录。场景说明 我们在样表数据中【学员基本表】中存放了学员基本信息,在【学员成绩表】中存放了学员的各科目成绩,这两张表是单独存在的,我们现在想做一张即有学员基本信息又有学员成绩的表。使用 ACCESS 与 EXCEL 进行数据比对与分析| 209.2 RIGHT JOIN 右链接9.3 INNER JOIN 内链接语句模型 SELECT 表 1.字

    20、段 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.学号结果语句说明 以右边的表为主表,功能与左链接相同语句模型 SELECT 表 1.字段 1,表 1,字段 n,表 2.字段 1,表 2.字段n FROM 表 1 RIGHT JOIN 表 2 ON 表 1.关键字段=表 2.关键字段实际语句 SELECT A.*,B.语文,B.数学 FROM 学员信息表

    21、AS A RIGHT JOIN 学员成绩表 AS B ON A.学号=B.学号结果这里我们可以看到这个查询丢弃了不在右边表中存在的左表数据。语句说明 不以任何表为主表,只显示符合条件的记录语句模型 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.学号结果使用 ACCESS 与 EXCEL 进行数据比对与分析| 219.4 用

    22、WHERE 在多表中查询只满足条件的记录10、 对数据进行分类、汇总、统计【SUM、COUNT、GROUP BY 】语句说明 不以任何表为主表,只显示符合条件的记录语句模型 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 W

    23、HERE A.学号=B.学号结果语句说明 一般来说 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

    24、 COUNT(姓名) AS 人数,班级 FROM 学员信息表 GROUP BY 班级 HAVING COUNT(姓名) 2使用 ACCESS 与 EXCEL 进行数据比对与分析| 2211、 将多表进 行拼接【UNION】11.1 准备工作我们在 ACCESS 中新建一张表,起名为【学员信息补表】学员信息补表ID 学号 姓名 性别 班级 年龄4 20140004 马六 男 高三二 185 20140005 李大 女 高三一 186 20140006 李一 男 高三一 17结果说明 COUNT 中的关键字段 可以是任何一个字段语句说明 SUM 汇总想要的结果 我们想对【学员成绩表】中的语文、数学

    25、求总成绩语句模型 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 WHE

    26、RE A.学号=B.学号) GROUP BY 班级说明 因为我们的样表是单独的要先进行数据组合然后再查询,以上语句我们使用了 SQL 语句的嵌套结果使用 ACCESS 与 EXCEL 进行数据比对与分析| 23学员信息补表ID 学号 姓名 性别 班级 年龄7 20140007 李二 女 高三二 1711.2 UNION11.3 UNION ALL语句说明 UNION 将多个结构相同的表进行纵向拼合形成新表,并丢弃重复的记录 想要的结果 我们想对【学员信息表】和【学员信息补表】进行纵向合同 语句模型 SELECT 表 1.字段,表 1.字段 N FROM 表 1 UNION (SELECT 表

    27、2.字段,表 2.字段 N)说明 表 1 与表 2 的字段数要相等,并且一一对应实际语句 SELECT 学号,姓名,性别,班级,年龄 FROM 学员信息表 UNION SELECT 学号,姓名,性别,班级,年龄 FROM 学员信息补表结果语句说明 UNION ALL 将多个结构相同的表进行纵向拼合形成新表,不丢弃重复的记录 语名模式 SELECT 表 1.字段,表 1.字段 N FROM 表 1 UNION (SELECT 表 2.字段,表 2.字段 N)实际语句 SELECT 学号,姓名,性别,班级,年龄 FROM 学员信息表 UNION ALLSELECT 学号,姓名,性别,班级,年龄 F

    28、ROM 学员信息补表结果使用 ACCESS 与 EXCEL 进行数据比对与分析| 2412、使用 DISTINCT 关键字过滤重复数据13、 将查询结 果导出EXCEL 进行加工我们在 ACCESS 中的查询中,编写完相应语句并得到正确的结果后,我们可以被这个查询进行保存语句说明 DISTINCT 丢弃重复的记录想要的结果 我们对 UNION ALL 的例子结果进行重复字段过滤语句模型 SELECT DISTINCT 字段 1,字段 n FROM 表实际语句 SELECT DISTINCT 学号,姓名,性别,班级,年龄 FROM ( SELECT 学号,姓名,性别,班级,年龄 FROM 学员信息表 UNION ALLSELECT 学号,姓名,性别,班级,年龄 FROM 学员信息补表 )原结果现结果使用 ACCESS 与 EXCEL 进行数据比对与分析| 25图 36点 菜单栏的【保存】图标按钮图 37输入完要起的名后,点确定按钮。打开或新建一个 EXCEL 文件图 38点 【数据】 页签中的 【自 ACCESS】 按钮使用 ACCESS 与 EXCEL 进行数据比对与分析| 26图 39选择我们已经编辑好的 ACCESS 文件图 40 选择想要导入的 ACCESS 中的表或视图(查询) ,类型 TABLE 为表,VIEW 为视图(查询)

    展开阅读全文
    提示  道客多多所有资源均是用户自行上传分享,仅供网友学习交流,未经上传用户书面授权,请勿作他用。
    关于本文
    本文标题:使用ACCESS与EXCEL进行数据分析.doc
    链接地址:https://www.docduoduo.com/p-9683094.html
    关于我们 - 网站声明 - 网站地图 - 资源地图 - 友情链接 - 网站客服 - 联系我们

    道客多多用户QQ群:832276834  微博官方号:道客多多官方   知乎号:道客多多

    Copyright© 2025 道客多多 docduoduo.com 网站版权所有世界地图

    经营许可证编号:粤ICP备2021046453号    营业执照商标

    1.png 2.png 3.png 4.png 5.png 6.png 7.png 8.png 9.png 10.png



    收起
    展开