简介《Access 2007数据分析技巧详解》面向希望把Access 2007用于数据整理与分析的用户尤其适合已接触Excel、需要处理更大数据量并让分析过程更透明可追溯的读者。内容从Access开展数据分析的理由与相对Excel的优势讲起继而覆盖表创建、数据类型、数据导入、关系型数据库与查询基础并延伸到聚合查询、删除/追加/更新等操作查询和交叉表查询。资源还专门讨论查找删除重复记录、填充空白字段、字段连接、文本与大小写转换、去除首尾空格、查找替换等常见数据转换任务可帮助读者建立从数据准备到查询汇总的完整思路。包内为1个PDF文件约11.35MB便于在电脑端通读与按章节查阅。目前已有80人学习适合作为Access数据分析入门与查询技巧查漏补缺的参考。1. 先弄明白一件事Access 2007 不是高级版 Excel很多人的第一反应是我 Excel 用得挺顺为什么还要碰 Access 2007 数据分析这一套我第一次翻这类内容时也这么想直到一份十几万行的销售明细在 Excel 里筛一次卡三秒才明白两者的分工根本不同。打个比方Excel 像随手能改的草稿纸适合边看边判断Access 2007 更像一个小仓库加加工车间数据先按规矩摆进去再通过查询把结果取出来。它要解决的不是图怎么画更好看而是数据多到管不动、步骤多到记不住。适合谁天天和表格打交道、每月重复做同一套汇总、又不想学编程的普通办公人群。2. 为什么值得学Excel 撑不住的时候它接得住新手最容易犯的错是把 Access 当成 Excel 的替代品结果装了又卸觉得还不如表格顺手。真实情况是两者根本不在一个赛道上Excel 强在随手改、随手看Access 强在数据量大、步骤重复、还要多人共用。下面四节讲的是四个最常见的Excel 顶不住的瞬间每一节都配一个你现在就能动手试的小动作做完你就知道该不该往下学。2.1 数据量过万就开始卡问题不在电脑配置先给个自查办法打开你最常处理的那张表按 CtrlEnd 跳到最后一个有内容的单元格看左下角行号是多少。如果超过三万行而且表里还带一堆 VLOOKUP 和 SUMIF那你每次改一个数都要等它重算一遍这不是电脑老了是工具选错了。Excel 的工作方式是把整个工作簿当成一棵对象树全部读进内存每一个单元格都是一个独立对象行数越多对象越多重算就越慢。Access 2007 的存储方式不一样。数据存在磁盘上的表里你发一条查询它只把符合条件的那部分结果取出来给你中间靠索引定位不需要把整张表铺开在内存里。所以同样是筛区域甲、金额大于一千的记录五万行的表在 Access 里基本是点一下就出结果。单表行数上限也不是几万这个量级真正先撞到的限制是单个数据库文件 2GB 的大小对绝大多数办公场景来说这个空间足够装几百万行纯文本和数字。提示判断要不要搬家的第一个信号不是行数多而是你每个月都要对同一份数据重做一遍同样的操作。重复次数越多搬过去越划算。2.2 分析过程要能留痕不能是个黑匣子Excel 里的计算逻辑藏在单元格里。你把表交给同事对方点开某个格子看到一长串嵌套公式第一反应是关掉——他不知道你为什么这么写也不知道改哪个参数会出事。这就是典型的黑匣子结果看得见过程看不见。Access 的处理方式是把每一步分析都做成一个查询对象起个名字存起来。下次数据更新了双击那个查询结果立刻刷新想知道某一步怎么算的打开查询就能看到完整的字段、条件、分组一目了然。我一般建议新手从第一天就养成命名习惯比如qry_01_筛选有效订单、qry_02_按区域汇总前缀表明类型序号表明顺序。三个月后你回头看这一列名字就是你的分析说明书。这件事的价值在于交接和复用。一个人做的分析另一个人能看懂、能改、能接着往下做团队里就不用每次都从零开始。对个人来说也一样半年后老板问这个数怎么来的你不用重新推理一遍打开查询给他看就行。2.3 数据和分析分开存改一次源头不用重做十张表这是 Access 最容易被低估的一点。在 Excel 里原始数据、计算列、透视表、图表往往挤在同一个文件里改一条源数据你得挨个检查哪些地方受影响。Access 天生把这两件事分开表只负责存原始数据查询负责所有计算报表负责呈现。分开之后有个直接好处——源数据只进不改。你把每个月的新数据追加进同一张表所有已经做好的查询自动把新数据算进去不需要重新拖一次透视表。这就是资料里反复提到的数据与呈现分离听起来抽象落地就一句话别在表里手动加计算列所有加减乘除都放到查询里做。具体怎么立规矩表名统一用tbl_开头查询用qry_开头报表用rpt_开头。看起来是小事等你库里有三十个对象的时候这三个月习惯能省你半天找东西的时间。2.4 什么时候该用它什么时候真不必不是所有活都值得开 Access。下面这张表是我自己踩过几次坑之后总结的判断标准你可以对着自己的场景比一比。你的情况用 Excel 更合适用 Access 更合适数据规模几千行以内几万行以上且持续增长操作频率一年做一两次每月、每周都要重做步骤复杂度三两步就能出结果筛选、分组、交叉汇总好几层协作方式自己看或发文件给对方多人往同一份数据里录、查数据来源单一文件多个来源要合到一起如果三条以上落在右边那这套 Access 2007 数据分析的路子就值得花时间。反过来如果你只是想临时看个数、画个图Excel 五分钟能搞定的事别硬搬。3. 零基础先认清四样东西表、字段、主键、关系Access 被叫关系型数据库这个名词吓退过不少人。其实拆开看只有四个概念用一张通讯录就能讲明白表是整本通讯录字段是每一栏姓名、电话、地址主键是给每个人编的唯一编号关系是这一页的人和另一页的订单怎么对应上。这四样搞懂了后面所有的查询都只是在这四样上面做加减法。这一章不写代码全是概念和对照但每一节都告诉你该在软件里点哪里。3.1 表一张规整到不能再规整的清单表就是一张二维清单但它对整齐的要求比 Excel 严格得多。三条硬规矩一行只能是一条完整记录一列只能放一种信息一个格子里只能有一个值。听起来像废话但新手翻车最多的就是这三点。最常见的错误是把好几样信息塞进一个格子比如区域甲/产品一/2007-01-05全写在备注里。人看着舒服机器完全没法用——你想按区域汇总它不知道该从哪切。正确做法是拆成三列区域一列、产品一列、日期一列。判断标准很简单如果某一列你以后想按它筛选或分组它就必须独立成列。另一个常见错误是合并单元格和空行。Excel 里合并一下好看导进 Access 就全乱了合并区域只有第一个格子有值其余全是空一汇总就少一半数据。所以在准备源数据的时候先把合并单元格全拆了把空行删干净第一行就是纯纯的列标题中间不要插任何装饰性内容。3.2 字段和数据类型别让数字变成文本每个字段都要指定一个数据类型这一步决定了你以后能不能对它求和、排序、算日期差。Access 2007 里常用的是这么几种数据类型用来存什么新手最常错在哪文本姓名、编号、备注把该存数字的字段设成文本后面算不了合计数字数量、年龄、次数不分长短整型随手选一个占空间但不出错货币单价、金额用数字存金额小数位会飘日期/时间下单日期、入职时间手动敲成2007年1月5日这种自由格式自动编号主键想自己改这个值改不了是/否是否有效、是否已付用文本存是/否筛选时大小写对不上重点说两个。第一编号类的字段订单号、工号、商品编码一律用文本不要用数字。因为数字类型会把前面的零吃掉001存进去变成1以后再按编号匹配就全对不上了。第二金额一律用货币类型别用数字里的单精度或双精度浮点数算钱会出现 0.10.2 不等于 0.3 这种事对账的时候能让你怀疑人生。3.3 主键每条记录的唯一身份证主键就是一列它的值在这一行里独一无二绝不允许重复也不允许为空。有了它你才能准确地点名某一条记录才能在两张表之间建立对应关系。最省事的做法是加一个自动编号字段当主键新建表时 Access 会问你要不要加选是就行它自己从 1 开始往上加永远不会重复。如果你已经有天然唯一的字段比如订单编号也可以指定它当主键但前提是你自己能保证它不重复——一旦录入两条一样的订单号Access 会直接拒绝保存报错给你看。新手会问不加主键行不行技术上可以但你会失去三样东西不能建关系、不能可靠地更新或删除单条记录、排序结果不稳定。所以别省这一步建表第一件事就是把主键定下来。3.4 关系两张表怎么对得上话现实里的数据很少能塞进一张表。客户信息一张表订单记录一张表如果每笔订单都把客户姓名、电话、地址重抄一遍改一个电话就要改几百行还容易改漏。关系的意义就是客户信息只存一份订单表里只存一个客户编号需要详细信息时两边一对就都出来了。操作路径是功能区数据库工具→关系把两张表加进来按住客户表的主键拖到订单表的客户编号上松手弹出对话框里勾选实施参照完整性确定。这条线就是一对多关系一个客户对应多笔订单。实施参照完整性这个勾很关键它保证你不可能在订单表里填一个客户表里根本不存在的编号。听起来是限制实际是保护——它帮你把脏数据挡在门外省掉后面一大堆核对工作。4. 第一个能跑通的例子从 Excel 导入到交叉表汇总概念讲完该动手了。这一章带你把一份 Excel 表格导进 Access 2007然后一步一步做出三个查询筛选、分组汇总、交叉表。每一步都给出该点哪里、该填什么以及一段可以直接抄进 SQL 视图的语句。你不需要理解全部语法先让它跑出结果看到屏幕上出现自己算出来的数字信心就来了。4.1 第一步准备一份最小可用的练习数据别拿你工作上最复杂的那份表练手先造一份十行以内的小数据。打开 Excel建这么一张表第一行是列标题从 A1 开始中间不要有空行空列订单编号下单日期区域产品数量单价A00012007/1/5区域甲产品一3120A00022007/1/8区域乙产品二580A00032007/1/12区域甲产品三2200A00042007/1/19区域丙产品一8120A00052007/2/2区域乙产品一1120A00062007/2/9区域甲产品二680A00072007/2/15区域丙产品三4200A00082007/2/21区域乙产品三7200注意两点订单编号写成文本样子的 A0001别让它变成纯数字单价列不要带货币符号纯数字就行。存成练习数据.xlsx放桌面。这份数据只有八行但足够你把整套流程走通三遍。4.2 第二步把这份数据导进 Access 2007打开 Access 2007选空白数据库起个名字比如练习库.accdb选好保存位置点创建。然后点功能区外部数据选项卡找到Excel按钮点它选导入 Excel 工作表。浏览到桌面那份练习数据.xlsx勾选第一行包含列标题下一步。这一步会列出每一列的名字和推断出来的数据类型。逐个确认数量和单价必须是数字或货币下单日期必须是日期/时间其余保持文本。勾选让 Access 添加主键表名填销售明细下一步完成。这里有个岔路口旁边还有个链接到 Excel 工作表的按钮功能是让 Access 实时读 Excel 文件而不把数据复制进来。新手练习阶段先别用链接原因第 5 章会专门讲——链接看着方便但只要你移动或改名了 Excel 文件整个库就报错。导入完成后双击左侧的销售明细表你应该能看到八条记录整整齐齐躺在里面。如果数量或单价列显示成了左对齐文本默认左对齐数字默认右对齐说明导入时类型选错了删表重导一次别将就。4.3 第三步用查询做第一次筛选现在做第一个查询。点创建→查询设计在弹出的显示表对话框里选销售明细关闭。上面一半是表结构下面一半是设计网格。先别急着在网格里点直接切到 SQL 视图更快右键查询标签页 → SQL 视图把下面这段贴进去。-- 筛出区域甲里金额超过 1000 的订单按金额从高到低排 SELECT 订单编号, 下单日期, 区域, 产品, 数量, 单价, 数量 * 单价 AS 金额 FROM 销售明细 WHERE 区域 区域甲 AND 数量 * 单价 1000 ORDER BY 数量 * 单价 DESC;这段做的事很直白SELECT后面列出你要哪几列其中数量 * 单价 AS 金额是当场算出来的新列原表里本来没有FROM 销售明细说明数据从哪张表来WHERE是筛选条件两个条件用AND连起来表示同时满足ORDER BY ... DESC表示按这个值从大到小排。几个参数你以后一定会改把区域甲换成别的区域名注意单引号不能丢文本值必须用单引号包住日期条件要用#包比如WHERE 下单日期 Between #2007-1-1# And #2007-1-31#这是 Access 的专有写法用双引号会报错。改完点工具栏上的红色感叹号运行看到结果后按 CtrlS 保存查询名字填qry_01_区域甲大额订单。4.4 第四步聚合查询一次算出各区合计筛选只是把行挑出来真正有用的是分组求和。新建一个查询切到 SQL 视图贴这段-- 按区域分组算出每区的订单笔数、总数量、销售合计、平均单笔金额 SELECT 区域, Count(*) AS 订单笔数, Sum(数量) AS 总数量, Sum(数量 * 单价) AS 销售合计, Avg(数量 * 单价) AS 平均单笔 FROM 销售明细 GROUP BY 区域 HAVING Sum(数量 * 单价) 5000 ORDER BY Sum(数量 * 单价) DESC;GROUP BY 区域的意思是按区域把行归堆归堆之后每个区域只剩一行这时候你才能用聚合函数去算这一堆里的东西。Count(*)数笔数Sum()求和Avg()求平均这三个是最常用的。HAVING是新手最容易懵的地方它和WHERE有什么区别简单说WHERE在归堆之前筛行HAVING在归堆之后筛堆。所以你要对合计这个概念设条件就必须用HAVING写成WHERE Sum(...) 5000会直接报错。这一条血泪经验我当年卡了半个下午。保存成qry_02_区域汇总。现在你手上有一个能随时刷新的汇总表了——以后每个月把新数据追加进销售明细双击这个查询结果立刻更新不用重新拖透视表。4.5 第五步交叉表查询把行变成列最后一步做交叉表也就是 Excel 里的透视表效果左边是区域上面是产品中间是金额。Access 的交叉表查询有一套专门的语法关键字是TRANSFORM和PIVOT-- 行是区域列是产品交叉点是该区域该产品的销售合计 TRANSFORM Sum(数量 * 单价) AS 销售合计 SELECT 区域, Sum(数量 * 单价) AS 行合计 FROM 销售明细 GROUP BY 区域 PIVOT 产品 IN (产品一, 产品二, 产品三);这三段各管一件事TRANSFORM后面写交叉点里放什么值这里放的是求和结果SELECT ... GROUP BY决定行方向按什么分组这里按区域PIVOT决定列方向按什么展开这里按产品。最后的IN (...)是可选但强烈建议加的它把列的顺序和名字钉死不然数据一变列的顺序可能跟着变看起来很不稳。保存成qry_03_区域产品交叉表。运行一下你应该能看到一张三行三列的小表行是区域甲、乙、丙列是产品一到三格子里是金额。到这一步资料里讲的核心链路你就走完一遍了数据进库、按条件筛、按维度汇总、按两个维度交叉。剩下的所有技巧都是在这条链路上加东西。5. 新手最容易踩的五个坑从现象倒推原因这一章专门讲翻车现场。下面五条是我见过频率最高、也最让人抓狂的问题每条按你看到什么现象 → 为什么会这样 → 怎么解决来写。你不用背遇到问题时回来对照一下现象就行。5.1 坑一导进去的数字死活算不了合计现象数据明明导进来了数量那一列看着也是数字但你在查询里用Sum()一算结果要么是空的要么只有一部分。状态栏上可能还提示数据类型不匹配。原因那几列在 Access 里被存成了文本类型。看着像数字实际是字符串字符串没法求和。导入 Excel 时如果源文件里某些格子的数字被存成了文本格式左上角有个绿色小三角或者那一列里混进了一个空格、一个暂无整列就会被判定为文本。解决先回 Excel 检查那一列有没有非数字内容清干净再重导。如果数据已经导进去了可以在查询里加一个转换函数Val()强制转成数字把Sum(数量)改成Sum(Val(数量))。但这只是补救不是根治——根治办法是在原表那列上右键改数据类型或者在导入向导那一步就把类型明确指定为数字。我现在的习惯是导入前先在 Excel 里给每列做个简单统计确认没有异常值再导。5.2 坑二改了 Excel 源文件Access 里一点变化都没有现象在 Excel 里补了几行数据保存切回 Access 打开表还是原来的老数据怎么刷新都没用。原因你当初用的是导入还是链接这两者行为完全不同。导入是把数据复制一份进 Access之后两边就没关系了链接是 Access 每次打开时去读那个 Excel 文件。如果你用的是导入源文件怎么改都不会同步这是设计如此不是 bug。解决想清楚你要哪种。数据是要长期维护、多人录入的就用导入把 Access 当唯一数据源以后新数据追加到 Access 里而不是 Excel 里。数据是别人定期给你发文件、你只想读一读的就用链接但要记住两点文件不能被移动或改名否则链接断掉链接的性能比导入差行数多的时候会明显变慢。新手练习阶段一律用导入省心。5.3 坑三查询结果少了几条记录怎么数都对不上现象表里明明有八条记录查询出来只有五条你翻来覆去看WHERE条件觉得自己写的是对的。原因九成是空值Null在作怪。WHERE 金额 1000这种写法看起来是金额不等于一千但数据库的规则是Null参与任何比较结果都是未知未知的行不会被选出来。所以那几条金额为空的记录就凭空消失了。另一个常见原因是条件写得太严AND前后两个条件把范围卡死了自己没意识到。解决把条件放宽先不加WHERE跑一次看看总行数对不对再一条一条加条件加一条数一次。要专门找空值用Is Null而不是 Null-- 正确写法找出金额为空的行 SELECT 订单编号, 区域, 数量, 单价 FROM 销售明细 WHERE 数量 Is Null OR 单价 Is Null; -- 错误写法这样永远查不出任何东西 -- WHERE 数量 Null Null在数据库里永远为假这是语法层面的规则记住就行。养成习惯只要结果数不对先怀疑空值再怀疑条件。5.4 坑四更新查询一执行数据就回不来了现象手滑点了一下更新查询的运行按钮几十行数据被覆盖想撤销发现 CtrlZ 按了没反应。原因Access 里的操作查询更新、删除、追加、生成表执行后是直接写库的没有撤销。Excel 里改错了 CtrlZ 能救回来这里没有后悔药。而且很多人是在设计视图里随手点运行根本没意识到它已经真改了。解决三条纪律。第一做任何操作查询之前先复制一份数据库文件命名加上日期这是最笨也最有效的办法。第二正式跑之前把操作查询改成选择查询预览一遍确认它筛出来的正好是你要改的那批记录——方法是先写SELECT * FROM ... WHERE ...看结果行数和内容对不对再把SELECT改成UPDATE。第三跑完立刻用另一个查询核对结果。下面是一个更新查询的标准样子注意WHERE一定要写死-- 把区域甲里单价为空的记录单价统一补成 100 UPDATE 销售明细 SET 单价 100 WHERE 区域 区域甲 AND 单价 Is Null;SET后面是改什么WHERE后面是改哪些。没有WHERE的 UPDATE 会改整张表这是最经典的翻车方式跑之前盯三秒确认条件写了。5.5 坑五文件越用越大打开越来越慢现象刚建库时打开秒开用了两个月文件从几兆涨到几百兆双击要等半天切换对象也卡。原因三个来源。一是频繁导入导出和删除数据Access 不会自动回收腾出来的空间文件只涨不缩二是你在表里存了图片、附件这类大对象一张图就顶几千行文字三是库里的临时对象、旧查询越堆越多。解决定期做一次压缩和修复数据库路径是功能区数据库工具→压缩和修复数据库它会重建文件、回收空间通常能瘦下来一大截。图片和附件不要直接存进表里存文件路径就行需要看的时候再按路径打开。另外删除数据用删除查询批量删比一行行手动删更干净删完再压缩一次。这个操作我一般一个月做一次五分钟的事能避免很多莫名其妙的卡顿。6. 下一步学什么一条从会用到用顺的路径第一个例子跑通之后最容易犯的错是到处乱翻看到什么学什么结果每个功能都摸了一下真用的时候一个都想不起来。我建议按下面这个顺序补每一阶段都要有能拿出手的产出别只看不练。阶段学什么练什么什么算过关第一周表设计、数据类型、主键把自己的工作数据整理成两三张规范表能说清每列为什么用这个类型第二周选择查询、条件、表达式把日常最常做的筛选做成查询改一个条件就能出不同结果第三周聚合查询、分组、HAVING做一张月度汇总新数据追加后能一键刷新第四周交叉表查询、操作查询做一张二维交叉表试一次追加查询敢在备份上跑更新和删除前三周是地基第四周开始接触会改动数据的操作所以务必先备份再动手。操作查询里最值得先掌握的是追加查询因为它对应的是每个月把新数据并进主表这个真实需求用熟了你的整个流程就闭环了新数据先进临时表检查无误后用追加查询并入主表所有汇总查询自动更新。删除、更新这两种放在最后练原因你从第 5 章应该已经看出来了。还有个小技巧把每个查询都当成一个小零件别写一个巨无霸查询干所有事。先做一个清洗后明细查询把该算的列算好、该排除的行排掉后面所有汇总都从这个查询里取数。这样源数据变了你只需要维护最前面那一个查询后面十几个都跟着变。我第一次学的时候图省事把所有逻辑塞进一个查询后来需求一变改一处崩三处重做了整整一天。从那以后我每次新建库第一件事都是先写一张清洗后明细查询再往下搭所有东西这个习惯救过我很多次。希望帮到你。如果你手上已经有这份资料建议直接翻到讲查询基础和交叉表的那两块边看边在电脑上点一遍比读十遍都管用。本文还有配套的精品资源点击获取