简介这份PDF读本面向希望提升数据分析能力的Access 2007用户原书由微软认证应用程序开发师迈克尔·亚历山大撰写重点讲清借助关系型数据库开展可扩展、过程透明且数据与呈现分离的分析思路并与Excel的使用边界作对照。内容从建表、数据类型、数据导入和关系模型入手逐步展开聚合查询、生成表/删除/追加/更新等操作查询及交叉表查询同时覆盖重复记录查找删除、空白字段填充、字段连接、文本与大小写转换、首尾空格去除、指定文本查找替换等数据转换任务。压缩包共1个PDF文件约11.35MB适合集中查阅与按章节练习。目前已有80人学习可作为初中级用户系统掌握Access查询、数据整理与分析的实操参考。1. 先搞懂这件事Access 2007 数据分析到底在解决什么麻烦很多人第一次听到 Access 2007 数据分析脑子里蹦出来的是程序员盯着黑色屏幕敲字的画面于是本能地想躲开。换个比喻就好懂了用普通表格软件管数据像把一年的票据全塞进一个大抽屉要找一张就得从头翻到尾而 Access 2007 这类关系型数据库像给每类单据配了独立抽屉、编号和索引卡你说一句「三月份华东区的合计」它直接把结果递过来中间翻了多少张你不用管。这本书带你练的就是把「翻抽屉」变成「递结果」的本事。它适合手头数据越攒越多、又不想碰编程的人适合每个月都要重做一遍报表的人也适合想从零认识关系型数据库的初学者。读之前不需要任何基础会打开文件、会拖鼠标就够了。2. 先算三笔账为什么零基础也该学 Access 做数据分析新手最容易犯的犹豫是「我又不当程序员学数据库干什么」。这个犹豫很正常但它多半来自一个误解把数据库当成写代码的东西。实际上对一个天天跟数据打交道的人来说学 Access 2007 更像是学一门省力的手艺不是学一门编程语言。这一章不讲操作先把三笔账算给你听算完你觉得值再往下看动手的部分算完你觉得不值那也省了后面的时间。三笔账分别是数据量、返工成本和协作成本。它们不是抽象概念每一个都对应着你可能已经遇到过、但还没意识到是工具问题的场景。2.1 第一笔账数据量涨到几万行普通表格就开始喘用表格软件处理一两千行数据体验是流畅的涨到两三万行只要表里有那么几十个公式你每改一个单元格右下角就会转圈等它重算改十次等十次。再往上文件打开本身就要半分钟保存的时候还可能直接卡死。Access 2007 的做法不一样它把数据存在磁盘上的表里你做查询的时候只在需要时取需要的那部分回来。几万行、几十万行对它来说属于日常量级不会因为你多点了一次筛选就全表重算一遍。这也是很多人的血泪经验——不是表格软件不好是它被设计出来的时候压根没打算干这个活。对新手来说这条账的意义在于你现在可能只有几千行但数据是会长的今年几千、明年几万很正常。等你长到那个量再迁移成本比一开始就用对工具高得多。提示不是所有事都要搬进 Access。几十行的临时统计表格软件仍然是最快的别为了练手把简单事情复杂化。2.2 第二笔账数据和分析揉在一起改一次就全重来一遍表格软件最舒服的地方也是最容易出问题的地方数据和公式混在同一张表里。你在原始数据右边加一列算合计再往右加一列算占比然后做一个图。下个月新数据一来你要么覆盖原数据旧结果全没了要么另存一份很快就有一堆「最终版」「最终版2」「真的最终版」。Access 2007 把这两件事拆开了。原始数据放在表里只进不改分析逻辑放在查询里随时可以重跑。下个月数据导进来查询点一下刷新结果就是新的中间过程一行都不用重做。这就是「数据与呈现分离」的实际含义。它听起来像术语落到日常就是一句话你不必每个月从头再做一遍报表。对做月度报表的人来说这一条省下来的时间比前两条加起来都多。2.3 第三笔账几个人共用一份数靠邮件传文件迟早出事三个人各自维护一份数据再互相发文件合并这个流程几乎必然出问题谁的版本是最新的说不清同一笔订单在两个文件里金额不一样也查不出来最后汇总的人只能靠肉眼比对。Access 2007 的解法是把数据放成一份查询各自建各自的。你要明细我要汇总他要按区域分都从同一份底层数据出谁也不会改坏别人的结果。入门阶段你不一定马上用到多人共用但这个思路值得先装进脑子一份数据多个视图而不是多份数据互相打架。2.4 概念先过一遍表、记录、字段、主键到底是什么在动手之前有四个词必须先弄清不然后面每一步都会卡。表一类数据的集合。一张「订单明细」表就装所有订单行。记录表里的一行代表一个具体对象比如一笔订单。字段表里的一列代表这个对象的一个属性比如客户名称、金额、月份。主键能唯一认出某一行的那个字段。订单编号可以做主键客户名称不行因为同一个客户会有很多笔订单。用通讯录类比最直观整本通讯录是表某一个人的那一行是记录姓名、电话、地址这几列分别是字段「身份证号」那一列可以做主键因为不会重复。主键为什么重要因为它决定了数据库能不能分清「这两行是不是同一件事」。没有主键重复导入一次数据你就得到两份一模一样的记录而且事后很难判断哪些是重复的。这个坑在后面的排查章节还会反复出现。2.5 十分钟热身做出你的第一个空数据库概念过完了先做一个没有压力的小动作让手熟悉一下界面。打开 Access 2007点左上角圆形按钮选「新建」。在右侧模板列表里选「空白数据库」。在文件名框里填一个你能记住的名字比如练习01.accdb选好保存位置。点「创建」。屏幕上会出现一个空的表默认叫「表1」。到这里你就已经建好一个数据库文件了扩展名是.accdb。左侧那块区域叫导航窗格以后你建的所有表、查询都会列在里面。现在点右上角的关闭会提示你是否保存「表1」选「否」然后重新打开刚才那个文件看看它还在不在。这个动作的意义是让你先建立「文件是容器表是内容」的直觉。很多人第一次用会以为建表就是建文件结果到处是零散的表1.accdb后面找数据找得很痛苦。入门阶段养成「一个项目一个文件表都建在同一个文件里」的习惯会省掉很多麻烦。3. 第一次动手从空数据库到一张能查数的表上一章结束时你手上有一个空文件。这一章的任务是让它装进真正的数据并且装对。强调「装对」是因为新手在这一步翻车的比例非常高——字段类型选错、导入时少勾一个选项后面所有查询结果都是错的而且错得很隐蔽你得回头重做一遍。流程上就三步建表结构、导数据进来、做体检。听起来简单但每一步都有几个容易忽略的细节下面逐个说。3.1 建表先定字段类型选错后面全是返工Access 2007 建表有两种方式新手建议先用「数据表视图」直接填快但填之前脑子里要先想好每个字段是什么类型。类型一旦定错后面排序会乱、合计会算不出来、日期会变成一串数字。字段类型适合放什么新手常犯的错文本名称、编号、月份这类把编号设成数字前导 0 被吃掉数字数量、单价这类要计算的用来存电话号码位数不够货币金额、单价用数字存金额小数位对不上日期/时间下单日期、发货日期用文本存日期排序变成按字面排自动编号系统自动生成的流水号手动改它导致主键冲突是/否是否已发货这类标记用文本填「是」「否」查询要写两次条件最容易踩的是第一条和第四条。订单编号、员工工号、邮编这类看着像数字的东西本质是编号不是数量一律用文本。日期用文本存的后果更严重2024-10-9和2024-10-10按文本排序10-9会排在10-10后面因为字符9比1大。这类错误在数据量小的时候完全看不出来数据一多就变成玄学。3.2 把外部数据导进来导入向导里最该盯住的几处真实的入门场景几乎不会是「从零手打数据」而是手上已经有一份 CSV 或者 Excel。Access 2007 的导入入口在「外部数据」选项卡里。点「外部数据」→「导入」→ 选「文本文件」CSV 选这个或「Excel」。点「浏览」找到文件选「将副本导入当前数据库」点确定。向导第一步如果是 CSV选「带分隔符」分隔符一般选逗号。关键一步勾选「第一行包含字段名称」。不勾的话你的列名会变成「字段1」「字段2」后面写查询时你会对着这些名字发呆。逐个检查字段类型。向导会自动猜猜错的概率不低尤其是编号类和日期类看到不对就手动改。主键那一步选「让 Access 添加主键」比自己选省事除非你明确知道哪个字段唯一。最后一步可以勾「保存导入步骤」下次同样的文件点一下就能重跑不用再走一遍向导。导入完成后导航窗格里会多一张表。双击打开先别急着做分析看一眼行数和最后一行的内容对不对如果最后几行是空的或者是一堆#说明分隔符或者编码选错了回到向导重来一次比修数据快。注意导入产生的是副本原文件改了不会自动同步。下个月的数据要重新导一次或者用「保存的导入步骤」重跑别以为改原文件就行。3.3 主键和索引宁可多花十分钟也别留下重复行主键的作用前面说过这里说操作。在「数据表视图」里打开表选中你要做主键的那一列右键选「主键」列名左边会出现一个小钥匙图标。主键设好之后数据库会强制这一列不能重复、不能为空。这意味着你下次误导入同一批数据时它会直接报错拦住你而不是默默多出一倍行数。这个拦住的动作就是主键最值钱的地方。索引是另一回事。主键自动带索引其他经常用来筛选的字段比如月份、客户名称也可以加索引右键列名 →「索引」→ 在索引窗口里新建一行。数据量小的时候感觉不出差别几万行以上加了索引的查询会明显快一截。入门阶段不用研究索引原理记住「常用来筛选的列加个索引」就够了。3.4 用 SQL 建一张表你自己的第一条语句界面点多了你可能想知道背后到底发生了什么。Access 2007 支持直接写 SQL入口是「创建」→「查询设计」关掉弹出的「显示表」窗口点左上角「SQL 视图」就能看到一片空白编辑区。-- 建一张订单明细表字段类型按用途选定 CREATE TABLE 订单明细 ( 订单编号 TEXT(20) NOT NULL, -- 编号用文本保留前导零 客户名称 TEXT(50), -- 客户名允许为空 月份 TEXT(2), -- 存 01 到 12 金额 CURRENCY -- 金额用货币型避免小数误差 );写完点「运行」那个红色感叹号表就建好了。这段语句里的几个点值得记TEXT(20)表示最多 20 个字符编号类字段宽度宁可给宽一点CURRENCY是货币型专门给金额用比普通数字型少一堆小数位对齐的麻烦NOT NULL表示这个字段不能空加在订单编号上等于提前堵住了「有单没号」这类脏数据。第一次写不用追求一次成功写错了 Access 会弹错误提示看提示里提到哪个词回去改那个词。这个「读错误信息」的习惯后面会一直用到。3.5 数据进表之后先做这四项体检数据导进来了不代表数据对了。花三分钟做下面四项检查能挡掉后面八成的「结果怎么不对」。比行数源文件多少行表里 excl 标题行也应该是多少行。差一行可能是标题行被当成数据了差很多可能是分隔符选错了。看首尾打开表看第一行和最后一行是不是正常数据最后几行经常是空行或者合计行混进来了。算总计随便找个金额列用状态栏的合计功能看一眼总和和你心里预期的量级对不对。差一个数量级八成是把金额当成文本导进来了。查空值切到查询建一个WHERE 客户名称 IS NULL的查询看有多少条。这个数字如果不是 0说明导入时有行错位。第四项用一句 SQL 就能跑-- 查客户名称为空的记录导入错位时这一查就露馅 SELECT * FROM 订单明细 WHERE 客户名称 IS NULL;养成「先检查再分析」的顺序比事后怀疑结果靠谱得多。4. 查询才是重头戏聚合、交叉表和批量改数怎么跑通表建好了、数据验过了接下来才是这本书真正想教你的事——查询。可以说前面两章都是在为这一章铺路因为 Access 2007 做数据分析的核心工作百分之八十都发生在查询里。新手对查询最大的心理障碍是「要写代码」。其实你可以先用界面点出来再点开 SQL 视图看一眼它生成了什么两相对照很快就懂了。这一章就走这条路先讲清楚查询是什么然后把聚合查询、交叉表查询、数据转换、操作查询四类最常见的活各跑一遍。4.1 查询是什么给数据库写一句话的说明书把查询理解成一句「我要什么」的说明书最贴切。你告诉数据库三件事从哪张表拿、按什么条件筛、要输出哪些列。它把这句说明书翻译成执行计划把结果还给你。比如这句话「从订单明细里只要三月份的输出客户名称和金额。」写成 SQL 就是SELECT 客户名称, 金额 FROM 订单明细 WHERE 月份 03;三行分别对应三个问题SELECT要哪些列FROM从哪张表WHERE按什么条件筛。就这三个词你已经能写出最常用的查询了。Access 的界面路径也值得走一遍创建 → 查询设计 → 选表 → 把字段拖到下面的网格里 → 在「条件」行填03→ 点运行。做完之后右键选「SQL 视图」你会看到界面帮你生成的语句和上面几乎一样。这一步的作用是把「点界面」和「写语句」对上号以后看到别人的 SQL 你也能反推出界面怎么点。4.2 第一个聚合查询按客户算笔数和合计明细查询只是把数据搬出来真正有分析价值的是聚合——按某个维度分组算笔数、求和、求平均。在查询设计视图里先选好表和字段然后点工具栏上那个像∑的「汇总」按钮网格里会多出一行「总计」。默认每个字段都是「分组」你把金额那一列改成「合计」就得到按客户汇总的结果。-- 按客户汇总笔数、合计、平均按合计从大到小排 SELECT 客户名称, Count(*) AS 订单笔数, Sum(金额) AS 合计金额, Avg(金额) AS 平均金额 FROM 订单明细 GROUP BY 客户名称 ORDER BY Sum(金额) DESC;几个参数值得说清楚。Count(*)数的是行数一行算一笔如果只想数有金额的行用Count(金额)空值不计入。Sum和Avg只对数字型和货币型有效如果金额字段被导成文本了这两个函数会返回空或者算错这正是上一章强调字段类型的理由。GROUP BY 客户名称的意思是「同一个客户的行归成一组」SELECT里出现的非聚合字段必须都写进GROUP BY漏写 Access 会报「聚合函数使用错误」这个报错新手碰得最多。想再加一个筛选条件比如只看金额大于 500 的订单用HAVING而不是WHERE-- HAVING 筛的是分组后的结果WHERE 筛的是分组前的行 SELECT 客户名称, Sum(金额) AS 合计金额 FROM 订单明细 GROUP BY 客户名称 HAVING Sum(金额) 5000;WHERE在分组前过滤行HAVING在分组后过滤组。记不住就先记「筛单条用 WHERE筛汇总结果用 HAVING」用错一次就记住了。4.3 交叉表查询把月份竖着排变成横着排汇总查询的结果是一列一列竖着的看单个客户没问题但要看「每个客户每个月的金额」竖着排就得翻很久。这时候用交叉表查询把月份这一列的每个值变成横向的一列一张表就能看全年。Access 有专门的交叉表查询类型创建 → 查询设计 → 选表 → 点工具栏「查询类型」→「交叉表」。网格里会多出一行「交叉表」三个选项分别是「行标题」「列标题」「值」。行标题选客户名称列标题选月份值选金额并设成「合计」。-- 行是客户列是月份交叉点是当月金额合计 TRANSFORM Sum(金额) AS 合计金额 SELECT 客户名称, Sum(金额) AS 全年合计 FROM 订单明细 GROUP BY 客户名称 PIVOT 月份 IN (01,02,03,04,05,06, 07,08,09,10,11,12);TRANSFORM ... PIVOT是 Access 独有的写法别的数据库里不一定长这样但在 Access 里就这一种。IN (...)那段是新手最该抄下来的技巧不加它列的顺序取决于数据里出现了哪些月份可能变成「03、01、07」这种乱序加上了就按你写的顺序固定排列而且缺的月份也会留出空列不会让整张表错位。交叉表的用处不止月份。行换成区域、产品、业务员列换成季度、渠道、客户等级都是同一个套路。一张交叉表基本就是一张能直接给人看的汇总表做汇报时特别省事。4.4 数据转换的六个常用手法去重、补空、拼接、改大小写、去空格、替换真实数据很少是干净的。编号前后带空格、客户名有的写全称有的写简称、金额有空的、姓和名分在两列——这些都要先洗一遍才能分析。Access 里做这些事大部分靠查询里的表达式不用改原表。找重复先知道有没有重复再决定怎么处理。-- 同一客户名出现多次的列出来看 SELECT 客户名称, Count(*) AS 出现次数 FROM 订单明细 GROUP BY 客户名称 HAVING Count(*) 1;补空值空值参与计算会让整列合计变成空先垫一个默认值。-- Nz 把空值换成 0再求和就不会整列报废 SELECT 客户名称, Sum(Nz(金额, 0)) AS 合计金额 FROM 订单明细 GROUP BY 客户名称;拼接字段姓和名分列时拼成一个完整姓名。-- 是 Access 的字符串连接符会自动处理空值 SELECT 姓 名 AS 完整姓名 FROM 客户表;改大小写英文名、编码统一格式UCase转大写LCase转小写。中文用不上但处理产品型号、邮箱时很常见。去首尾空格这是最隐蔽的一个坑。 张三和张三在数据库眼里是两个不同的客户分组时会分成两组。-- Trim 去掉首尾空格分组前先洗一遍 SELECT Trim(客户名称) AS 客户名称, Sum(金额) AS 合计金额 FROM 订单明细 GROUP BY Trim(客户名称);查找替换把某一类写法统一比如把「有限公司」后缀去掉。-- Replace 把所有出现的旧字符串换成新字符串 SELECT Replace(客户名称, 有限公司, ) AS 简称 FROM 订单明细;这六个手法不用背知道有这么几招、用的时候回来查就行。它们的共同点是都不改原表只在查询结果里生效。这样你随时可以对比「洗之前」和「洗之后」出错也能退回去。4.5 操作查询更新、追加、删除这些批量活怎么干前面四类查询都是「看结果」操作查询是「改数据」。它效率极高也极容易出事用之前请务必记住一条先建一个同样的查询把条件拿去当普通查询跑一遍看看它选中了多少行确认无误再改成操作查询。更新查询批量改值。-- 三月份的金额统一上调 10% UPDATE 订单明细 SET 金额 金额 * 1.1 WHERE 月份 03;追加查询把一批数据搬到另一张表比如把去年数据归档。-- 把 12 月的订单追加到归档表 INSERT INTO 订单归档 ( 订单编号, 客户名称, 金额 ) SELECT 订单编号, 客户名称, 金额 FROM 订单明细 WHERE 月份 12;删除查询清掉不要的行。-- 删除金额为空的行 DELETE FROM 订单明细 WHERE 金额 IS NULL;三条语句有一个共同点WHERE决定影响范围写错就是全表。UPDATE和DELETE不加WHEREAccess 会直接作用于所有行而且执行完没法撤销——这就是为什么大家都管它叫「没有后悔药的操作」。稳妥做法是先把WHERE那部分单独跑成SELECT看到行数合理再套进操作查询里执行。注意操作查询执行前先复制一份数据库文件。这不是谨慎过头是真实发生过太多次的翻车现场。5. 新手最容易踩的坑五个高频问题逐个排查这一章不教新东西专门收拾残局。下面五条是入门阶段出现频率最高的问题每一条我都按「你会看到什么现象 → 原因是什么 → 怎么解决」的顺序写遇到问题直接对号入座。5.1 导入后数字变文本合计出来是 0现象明明有金额Sum(金额)的结果是 0 或者空白排序也不对9排在100后面。原因导入向导自动把这一列判成了文本型或者源数据里混了空格、货币符号、千分位逗号Access 一律当字符处理。文本做不了求和结果自然是 0。解决先看表的设计视图确认字段类型是货币或数字。如果是文本新建一个数字型字段用更新查询把转换后的值写进去UPDATE 表 SET 新金额 Val(Replace(旧金额, ,, ))。Val会把字符串开头的数字部分转成数值Replace先去掉千分位逗号。转完再对比总和和源文件对上才算完。5.2 查询结果比表里的行数少现象表里明明有一万行查询出来只有八千。原因最常见的是某个字段有空值而你在条件里写了金额 100。空值参与比较的结果既不是真也不是假直接被排除掉了。另一种是多表关联时用了内连接匹配不上的行被丢掉。解决把条件改成显式处理空值比如WHERE Nz(金额, 0) 100。如果是多表关联丢行把连接方式改成左连接让主表的行全部保留。改完再数一遍行数和预期对上再继续往下做。5.3 合计金额和手工算的对不上现象查询算出来的总额和你在表格软件里拉出来的差那么一点。原因三种可能。一是数据里混进了重复行同一条记录被导了两次二是金额字段里有带空格的文本被当成 0 处理了三是你在表格软件里算的时候包含了标题行或者合计行。解决按 4.4 节的重复查询先查一遍有没有重复有的话用主键去重再导一次。再跑一个WHERE 金额 IS NULL看空值数量。最后核对表格软件那边的行数确认两边统计的是同一个范围。这三步走完差异一般都能解释清楚。5.4 更新查询一执行数据全改了现象本来只想改三月份结果整张表都被改了。原因WHERE条件没写、写错或者条件里的字段是文本而值是数字月份 3匹配不上03。解决立刻从备份恢复——这就是为什么执行前要复制文件。恢复之后先把这个条件拿去当普通查询跑一遍看返回的行数是不是你预期的那个数确认无误再套进更新语句里执行。养成「条件先查后改」的顺序能挡掉绝大多数误操作。5.5 文件越用越大打开越来越慢现象刚开始几兆的文件用了一个月变成几百兆打开要等半天。原因Access 执行删除、更新操作后不会立刻释放空间只是把这些空间标记成可复用反复导入导出也会留下碎片。解决定期做一次压缩修复入口在圆形按钮 →「管理」→「压缩和修复数据库」。这个动作会把文件里空出来的空间收回通常能瘦一大圈。另外检查一下是不是留了太多临时表和中间查询用不上的删掉别一直堆着。6. 下一步学什么把数据分析练成肌肉记忆的三步路径入门阶段最怕的不是学不会是学完就搁下过两周全忘。所以最后一章不给知识清单给一条能照着走的路。按下面三个阶段推每个阶段两到三周中间不要跳。阶段重点练什么判断自己过关的标准第一阶段建表、导入、字段类型、主键能独立把一份 CSV 干净地导进来行数和金额都对得上第二阶段聚合查询、交叉表、常用表达式能不看教程写出按客户按月的汇总WHERE和HAVING不混用第三阶段操作查询、备份习惯、简单报表导出做批量改数前会先跑一遍验证查询改完数据能对上第一阶段的重点是「干净」。这段时间不要急着做分析就反复练导入故意准备几份脏数据带空行的、带千分位逗号的、日期格式不统一的一份一份导进去再验一遍。练到你能一眼看出「这列该用文本还是数字」后面就顺了。第二阶段开始接触真正的分析。找一份你自己关心的数据哪怕是记账本或者运动记录按月份、按类别做汇总再做成交叉表。这里的关键不是技巧多而是把「我想看什么」翻译成「怎么写查询」这个动作练熟。翻不出来的语法就查查完立刻用一遍用完再关掉文档凭记忆重写一次。第三阶段碰操作查询。这个阶段最该练的不是写得多快而是多谨慎。每次动手前复制一份文件每次执行前把条件当普通查询跑一遍。我自己的血泪经验是有次觉得「就改一列不用备份」结果条件里的引号写成中文全角一条语句把半年的金额全乘以了 1.1最后靠三天前的备份才救回来。从那以后我每次碰UPDATE和DELETE都强制走一遍「复制文件 → 条件查询 → 核对行数 → 执行」这四步一次没落过。等你把这四步变成肌肉记忆Access 2007 对你来说就不再是个陌生软件而是一个随手能用的工具。那份资源里还有更多细节——多表关联、参数查询、报表导出按你手上的实际需求挑着看就行不用从头读到尾。希望帮到你。本文还有配套的精品资源点击获取
简介这份PDF读本面向希望提升数据分析能力的Access 2007用户原书由微软认证应用程序开发师迈克尔·亚历山大撰写重点讲清借助关系型数据库开展可扩展、过程透明且数据与呈现分离的分析思路并与Excel的使用边界作对照。内容从建表、数据类型、数据导入和关系模型入手逐步展开聚合查询、生成表/删除/追加/更新等操作查询及交叉表查询同时覆盖重复记录查找删除、空白字段填充、字段连接、文本与大小写转换、首尾空格去除、指定文本查找替换等数据转换任务。压缩包共1个PDF文件约11.35MB适合集中查阅与按章节练习。目前已有80人学习可作为初中级用户系统掌握Access查询、数据整理与分析的实操参考。1. 先搞懂这件事Access 2007 数据分析到底在解决什么麻烦很多人第一次听到 Access 2007 数据分析脑子里蹦出来的是程序员盯着黑色屏幕敲字的画面于是本能地想躲开。换个比喻就好懂了用普通表格软件管数据像把一年的票据全塞进一个大抽屉要找一张就得从头翻到尾而 Access 2007 这类关系型数据库像给每类单据配了独立抽屉、编号和索引卡你说一句「三月份华东区的合计」它直接把结果递过来中间翻了多少张你不用管。这本书带你练的就是把「翻抽屉」变成「递结果」的本事。它适合手头数据越攒越多、又不想碰编程的人适合每个月都要重做一遍报表的人也适合想从零认识关系型数据库的初学者。读之前不需要任何基础会打开文件、会拖鼠标就够了。2. 先算三笔账为什么零基础也该学 Access 做数据分析新手最容易犯的犹豫是「我又不当程序员学数据库干什么」。这个犹豫很正常但它多半来自一个误解把数据库当成写代码的东西。实际上对一个天天跟数据打交道的人来说学 Access 2007 更像是学一门省力的手艺不是学一门编程语言。这一章不讲操作先把三笔账算给你听算完你觉得值再往下看动手的部分算完你觉得不值那也省了后面的时间。三笔账分别是数据量、返工成本和协作成本。它们不是抽象概念每一个都对应着你可能已经遇到过、但还没意识到是工具问题的场景。2.1 第一笔账数据量涨到几万行普通表格就开始喘用表格软件处理一两千行数据体验是流畅的涨到两三万行只要表里有那么几十个公式你每改一个单元格右下角就会转圈等它重算改十次等十次。再往上文件打开本身就要半分钟保存的时候还可能直接卡死。Access 2007 的做法不一样它把数据存在磁盘上的表里你做查询的时候只在需要时取需要的那部分回来。几万行、几十万行对它来说属于日常量级不会因为你多点了一次筛选就全表重算一遍。这也是很多人的血泪经验——不是表格软件不好是它被设计出来的时候压根没打算干这个活。对新手来说这条账的意义在于你现在可能只有几千行但数据是会长的今年几千、明年几万很正常。等你长到那个量再迁移成本比一开始就用对工具高得多。提示不是所有事都要搬进 Access。几十行的临时统计表格软件仍然是最快的别为了练手把简单事情复杂化。2.2 第二笔账数据和分析揉在一起改一次就全重来一遍表格软件最舒服的地方也是最容易出问题的地方数据和公式混在同一张表里。你在原始数据右边加一列算合计再往右加一列算占比然后做一个图。下个月新数据一来你要么覆盖原数据旧结果全没了要么另存一份很快就有一堆「最终版」「最终版2」「真的最终版」。Access 2007 把这两件事拆开了。原始数据放在表里只进不改分析逻辑放在查询里随时可以重跑。下个月数据导进来查询点一下刷新结果就是新的中间过程一行都不用重做。这就是「数据与呈现分离」的实际含义。它听起来像术语落到日常就是一句话你不必每个月从头再做一遍报表。对做月度报表的人来说这一条省下来的时间比前两条加起来都多。2.3 第三笔账几个人共用一份数靠邮件传文件迟早出事三个人各自维护一份数据再互相发文件合并这个流程几乎必然出问题谁的版本是最新的说不清同一笔订单在两个文件里金额不一样也查不出来最后汇总的人只能靠肉眼比对。Access 2007 的解法是把数据放成一份查询各自建各自的。你要明细我要汇总他要按区域分都从同一份底层数据出谁也不会改坏别人的结果。入门阶段你不一定马上用到多人共用但这个思路值得先装进脑子一份数据多个视图而不是多份数据互相打架。2.4 概念先过一遍表、记录、字段、主键到底是什么在动手之前有四个词必须先弄清不然后面每一步都会卡。表一类数据的集合。一张「订单明细」表就装所有订单行。记录表里的一行代表一个具体对象比如一笔订单。字段表里的一列代表这个对象的一个属性比如客户名称、金额、月份。主键能唯一认出某一行的那个字段。订单编号可以做主键客户名称不行因为同一个客户会有很多笔订单。用通讯录类比最直观整本通讯录是表某一个人的那一行是记录姓名、电话、地址这几列分别是字段「身份证号」那一列可以做主键因为不会重复。主键为什么重要因为它决定了数据库能不能分清「这两行是不是同一件事」。没有主键重复导入一次数据你就得到两份一模一样的记录而且事后很难判断哪些是重复的。这个坑在后面的排查章节还会反复出现。2.5 十分钟热身做出你的第一个空数据库概念过完了先做一个没有压力的小动作让手熟悉一下界面。打开 Access 2007点左上角圆形按钮选「新建」。在右侧模板列表里选「空白数据库」。在文件名框里填一个你能记住的名字比如练习01.accdb选好保存位置。点「创建」。屏幕上会出现一个空的表默认叫「表1」。到这里你就已经建好一个数据库文件了扩展名是.accdb。左侧那块区域叫导航窗格以后你建的所有表、查询都会列在里面。现在点右上角的关闭会提示你是否保存「表1」选「否」然后重新打开刚才那个文件看看它还在不在。这个动作的意义是让你先建立「文件是容器表是内容」的直觉。很多人第一次用会以为建表就是建文件结果到处是零散的表1.accdb后面找数据找得很痛苦。入门阶段养成「一个项目一个文件表都建在同一个文件里」的习惯会省掉很多麻烦。3. 第一次动手从空数据库到一张能查数的表上一章结束时你手上有一个空文件。这一章的任务是让它装进真正的数据并且装对。强调「装对」是因为新手在这一步翻车的比例非常高——字段类型选错、导入时少勾一个选项后面所有查询结果都是错的而且错得很隐蔽你得回头重做一遍。流程上就三步建表结构、导数据进来、做体检。听起来简单但每一步都有几个容易忽略的细节下面逐个说。3.1 建表先定字段类型选错后面全是返工Access 2007 建表有两种方式新手建议先用「数据表视图」直接填快但填之前脑子里要先想好每个字段是什么类型。类型一旦定错后面排序会乱、合计会算不出来、日期会变成一串数字。字段类型适合放什么新手常犯的错文本名称、编号、月份这类把编号设成数字前导 0 被吃掉数字数量、单价这类要计算的用来存电话号码位数不够货币金额、单价用数字存金额小数位对不上日期/时间下单日期、发货日期用文本存日期排序变成按字面排自动编号系统自动生成的流水号手动改它导致主键冲突是/否是否已发货这类标记用文本填「是」「否」查询要写两次条件最容易踩的是第一条和第四条。订单编号、员工工号、邮编这类看着像数字的东西本质是编号不是数量一律用文本。日期用文本存的后果更严重2024-10-9和2024-10-10按文本排序10-9会排在10-10后面因为字符9比1大。这类错误在数据量小的时候完全看不出来数据一多就变成玄学。3.2 把外部数据导进来导入向导里最该盯住的几处真实的入门场景几乎不会是「从零手打数据」而是手上已经有一份 CSV 或者 Excel。Access 2007 的导入入口在「外部数据」选项卡里。点「外部数据」→「导入」→ 选「文本文件」CSV 选这个或「Excel」。点「浏览」找到文件选「将副本导入当前数据库」点确定。向导第一步如果是 CSV选「带分隔符」分隔符一般选逗号。关键一步勾选「第一行包含字段名称」。不勾的话你的列名会变成「字段1」「字段2」后面写查询时你会对着这些名字发呆。逐个检查字段类型。向导会自动猜猜错的概率不低尤其是编号类和日期类看到不对就手动改。主键那一步选「让 Access 添加主键」比自己选省事除非你明确知道哪个字段唯一。最后一步可以勾「保存导入步骤」下次同样的文件点一下就能重跑不用再走一遍向导。导入完成后导航窗格里会多一张表。双击打开先别急着做分析看一眼行数和最后一行的内容对不对如果最后几行是空的或者是一堆#说明分隔符或者编码选错了回到向导重来一次比修数据快。注意导入产生的是副本原文件改了不会自动同步。下个月的数据要重新导一次或者用「保存的导入步骤」重跑别以为改原文件就行。3.3 主键和索引宁可多花十分钟也别留下重复行主键的作用前面说过这里说操作。在「数据表视图」里打开表选中你要做主键的那一列右键选「主键」列名左边会出现一个小钥匙图标。主键设好之后数据库会强制这一列不能重复、不能为空。这意味着你下次误导入同一批数据时它会直接报错拦住你而不是默默多出一倍行数。这个拦住的动作就是主键最值钱的地方。索引是另一回事。主键自动带索引其他经常用来筛选的字段比如月份、客户名称也可以加索引右键列名 →「索引」→ 在索引窗口里新建一行。数据量小的时候感觉不出差别几万行以上加了索引的查询会明显快一截。入门阶段不用研究索引原理记住「常用来筛选的列加个索引」就够了。3.4 用 SQL 建一张表你自己的第一条语句界面点多了你可能想知道背后到底发生了什么。Access 2007 支持直接写 SQL入口是「创建」→「查询设计」关掉弹出的「显示表」窗口点左上角「SQL 视图」就能看到一片空白编辑区。-- 建一张订单明细表字段类型按用途选定 CREATE TABLE 订单明细 ( 订单编号 TEXT(20) NOT NULL, -- 编号用文本保留前导零 客户名称 TEXT(50), -- 客户名允许为空 月份 TEXT(2), -- 存 01 到 12 金额 CURRENCY -- 金额用货币型避免小数误差 );写完点「运行」那个红色感叹号表就建好了。这段语句里的几个点值得记TEXT(20)表示最多 20 个字符编号类字段宽度宁可给宽一点CURRENCY是货币型专门给金额用比普通数字型少一堆小数位对齐的麻烦NOT NULL表示这个字段不能空加在订单编号上等于提前堵住了「有单没号」这类脏数据。第一次写不用追求一次成功写错了 Access 会弹错误提示看提示里提到哪个词回去改那个词。这个「读错误信息」的习惯后面会一直用到。3.5 数据进表之后先做这四项体检数据导进来了不代表数据对了。花三分钟做下面四项检查能挡掉后面八成的「结果怎么不对」。比行数源文件多少行表里 excl 标题行也应该是多少行。差一行可能是标题行被当成数据了差很多可能是分隔符选错了。看首尾打开表看第一行和最后一行是不是正常数据最后几行经常是空行或者合计行混进来了。算总计随便找个金额列用状态栏的合计功能看一眼总和和你心里预期的量级对不对。差一个数量级八成是把金额当成文本导进来了。查空值切到查询建一个WHERE 客户名称 IS NULL的查询看有多少条。这个数字如果不是 0说明导入时有行错位。第四项用一句 SQL 就能跑-- 查客户名称为空的记录导入错位时这一查就露馅 SELECT * FROM 订单明细 WHERE 客户名称 IS NULL;养成「先检查再分析」的顺序比事后怀疑结果靠谱得多。4. 查询才是重头戏聚合、交叉表和批量改数怎么跑通表建好了、数据验过了接下来才是这本书真正想教你的事——查询。可以说前面两章都是在为这一章铺路因为 Access 2007 做数据分析的核心工作百分之八十都发生在查询里。新手对查询最大的心理障碍是「要写代码」。其实你可以先用界面点出来再点开 SQL 视图看一眼它生成了什么两相对照很快就懂了。这一章就走这条路先讲清楚查询是什么然后把聚合查询、交叉表查询、数据转换、操作查询四类最常见的活各跑一遍。4.1 查询是什么给数据库写一句话的说明书把查询理解成一句「我要什么」的说明书最贴切。你告诉数据库三件事从哪张表拿、按什么条件筛、要输出哪些列。它把这句说明书翻译成执行计划把结果还给你。比如这句话「从订单明细里只要三月份的输出客户名称和金额。」写成 SQL 就是SELECT 客户名称, 金额 FROM 订单明细 WHERE 月份 03;三行分别对应三个问题SELECT要哪些列FROM从哪张表WHERE按什么条件筛。就这三个词你已经能写出最常用的查询了。Access 的界面路径也值得走一遍创建 → 查询设计 → 选表 → 把字段拖到下面的网格里 → 在「条件」行填03→ 点运行。做完之后右键选「SQL 视图」你会看到界面帮你生成的语句和上面几乎一样。这一步的作用是把「点界面」和「写语句」对上号以后看到别人的 SQL 你也能反推出界面怎么点。4.2 第一个聚合查询按客户算笔数和合计明细查询只是把数据搬出来真正有分析价值的是聚合——按某个维度分组算笔数、求和、求平均。在查询设计视图里先选好表和字段然后点工具栏上那个像∑的「汇总」按钮网格里会多出一行「总计」。默认每个字段都是「分组」你把金额那一列改成「合计」就得到按客户汇总的结果。-- 按客户汇总笔数、合计、平均按合计从大到小排 SELECT 客户名称, Count(*) AS 订单笔数, Sum(金额) AS 合计金额, Avg(金额) AS 平均金额 FROM 订单明细 GROUP BY 客户名称 ORDER BY Sum(金额) DESC;几个参数值得说清楚。Count(*)数的是行数一行算一笔如果只想数有金额的行用Count(金额)空值不计入。Sum和Avg只对数字型和货币型有效如果金额字段被导成文本了这两个函数会返回空或者算错这正是上一章强调字段类型的理由。GROUP BY 客户名称的意思是「同一个客户的行归成一组」SELECT里出现的非聚合字段必须都写进GROUP BY漏写 Access 会报「聚合函数使用错误」这个报错新手碰得最多。想再加一个筛选条件比如只看金额大于 500 的订单用HAVING而不是WHERE-- HAVING 筛的是分组后的结果WHERE 筛的是分组前的行 SELECT 客户名称, Sum(金额) AS 合计金额 FROM 订单明细 GROUP BY 客户名称 HAVING Sum(金额) 5000;WHERE在分组前过滤行HAVING在分组后过滤组。记不住就先记「筛单条用 WHERE筛汇总结果用 HAVING」用错一次就记住了。4.3 交叉表查询把月份竖着排变成横着排汇总查询的结果是一列一列竖着的看单个客户没问题但要看「每个客户每个月的金额」竖着排就得翻很久。这时候用交叉表查询把月份这一列的每个值变成横向的一列一张表就能看全年。Access 有专门的交叉表查询类型创建 → 查询设计 → 选表 → 点工具栏「查询类型」→「交叉表」。网格里会多出一行「交叉表」三个选项分别是「行标题」「列标题」「值」。行标题选客户名称列标题选月份值选金额并设成「合计」。-- 行是客户列是月份交叉点是当月金额合计 TRANSFORM Sum(金额) AS 合计金额 SELECT 客户名称, Sum(金额) AS 全年合计 FROM 订单明细 GROUP BY 客户名称 PIVOT 月份 IN (01,02,03,04,05,06, 07,08,09,10,11,12);TRANSFORM ... PIVOT是 Access 独有的写法别的数据库里不一定长这样但在 Access 里就这一种。IN (...)那段是新手最该抄下来的技巧不加它列的顺序取决于数据里出现了哪些月份可能变成「03、01、07」这种乱序加上了就按你写的顺序固定排列而且缺的月份也会留出空列不会让整张表错位。交叉表的用处不止月份。行换成区域、产品、业务员列换成季度、渠道、客户等级都是同一个套路。一张交叉表基本就是一张能直接给人看的汇总表做汇报时特别省事。4.4 数据转换的六个常用手法去重、补空、拼接、改大小写、去空格、替换真实数据很少是干净的。编号前后带空格、客户名有的写全称有的写简称、金额有空的、姓和名分在两列——这些都要先洗一遍才能分析。Access 里做这些事大部分靠查询里的表达式不用改原表。找重复先知道有没有重复再决定怎么处理。-- 同一客户名出现多次的列出来看 SELECT 客户名称, Count(*) AS 出现次数 FROM 订单明细 GROUP BY 客户名称 HAVING Count(*) 1;补空值空值参与计算会让整列合计变成空先垫一个默认值。-- Nz 把空值换成 0再求和就不会整列报废 SELECT 客户名称, Sum(Nz(金额, 0)) AS 合计金额 FROM 订单明细 GROUP BY 客户名称;拼接字段姓和名分列时拼成一个完整姓名。-- 是 Access 的字符串连接符会自动处理空值 SELECT 姓 名 AS 完整姓名 FROM 客户表;改大小写英文名、编码统一格式UCase转大写LCase转小写。中文用不上但处理产品型号、邮箱时很常见。去首尾空格这是最隐蔽的一个坑。 张三和张三在数据库眼里是两个不同的客户分组时会分成两组。-- Trim 去掉首尾空格分组前先洗一遍 SELECT Trim(客户名称) AS 客户名称, Sum(金额) AS 合计金额 FROM 订单明细 GROUP BY Trim(客户名称);查找替换把某一类写法统一比如把「有限公司」后缀去掉。-- Replace 把所有出现的旧字符串换成新字符串 SELECT Replace(客户名称, 有限公司, ) AS 简称 FROM 订单明细;这六个手法不用背知道有这么几招、用的时候回来查就行。它们的共同点是都不改原表只在查询结果里生效。这样你随时可以对比「洗之前」和「洗之后」出错也能退回去。4.5 操作查询更新、追加、删除这些批量活怎么干前面四类查询都是「看结果」操作查询是「改数据」。它效率极高也极容易出事用之前请务必记住一条先建一个同样的查询把条件拿去当普通查询跑一遍看看它选中了多少行确认无误再改成操作查询。更新查询批量改值。-- 三月份的金额统一上调 10% UPDATE 订单明细 SET 金额 金额 * 1.1 WHERE 月份 03;追加查询把一批数据搬到另一张表比如把去年数据归档。-- 把 12 月的订单追加到归档表 INSERT INTO 订单归档 ( 订单编号, 客户名称, 金额 ) SELECT 订单编号, 客户名称, 金额 FROM 订单明细 WHERE 月份 12;删除查询清掉不要的行。-- 删除金额为空的行 DELETE FROM 订单明细 WHERE 金额 IS NULL;三条语句有一个共同点WHERE决定影响范围写错就是全表。UPDATE和DELETE不加WHEREAccess 会直接作用于所有行而且执行完没法撤销——这就是为什么大家都管它叫「没有后悔药的操作」。稳妥做法是先把WHERE那部分单独跑成SELECT看到行数合理再套进操作查询里执行。注意操作查询执行前先复制一份数据库文件。这不是谨慎过头是真实发生过太多次的翻车现场。5. 新手最容易踩的坑五个高频问题逐个排查这一章不教新东西专门收拾残局。下面五条是入门阶段出现频率最高的问题每一条我都按「你会看到什么现象 → 原因是什么 → 怎么解决」的顺序写遇到问题直接对号入座。5.1 导入后数字变文本合计出来是 0现象明明有金额Sum(金额)的结果是 0 或者空白排序也不对9排在100后面。原因导入向导自动把这一列判成了文本型或者源数据里混了空格、货币符号、千分位逗号Access 一律当字符处理。文本做不了求和结果自然是 0。解决先看表的设计视图确认字段类型是货币或数字。如果是文本新建一个数字型字段用更新查询把转换后的值写进去UPDATE 表 SET 新金额 Val(Replace(旧金额, ,, ))。Val会把字符串开头的数字部分转成数值Replace先去掉千分位逗号。转完再对比总和和源文件对上才算完。5.2 查询结果比表里的行数少现象表里明明有一万行查询出来只有八千。原因最常见的是某个字段有空值而你在条件里写了金额 100。空值参与比较的结果既不是真也不是假直接被排除掉了。另一种是多表关联时用了内连接匹配不上的行被丢掉。解决把条件改成显式处理空值比如WHERE Nz(金额, 0) 100。如果是多表关联丢行把连接方式改成左连接让主表的行全部保留。改完再数一遍行数和预期对上再继续往下做。5.3 合计金额和手工算的对不上现象查询算出来的总额和你在表格软件里拉出来的差那么一点。原因三种可能。一是数据里混进了重复行同一条记录被导了两次二是金额字段里有带空格的文本被当成 0 处理了三是你在表格软件里算的时候包含了标题行或者合计行。解决按 4.4 节的重复查询先查一遍有没有重复有的话用主键去重再导一次。再跑一个WHERE 金额 IS NULL看空值数量。最后核对表格软件那边的行数确认两边统计的是同一个范围。这三步走完差异一般都能解释清楚。5.4 更新查询一执行数据全改了现象本来只想改三月份结果整张表都被改了。原因WHERE条件没写、写错或者条件里的字段是文本而值是数字月份 3匹配不上03。解决立刻从备份恢复——这就是为什么执行前要复制文件。恢复之后先把这个条件拿去当普通查询跑一遍看返回的行数是不是你预期的那个数确认无误再套进更新语句里执行。养成「条件先查后改」的顺序能挡掉绝大多数误操作。5.5 文件越用越大打开越来越慢现象刚开始几兆的文件用了一个月变成几百兆打开要等半天。原因Access 执行删除、更新操作后不会立刻释放空间只是把这些空间标记成可复用反复导入导出也会留下碎片。解决定期做一次压缩修复入口在圆形按钮 →「管理」→「压缩和修复数据库」。这个动作会把文件里空出来的空间收回通常能瘦一大圈。另外检查一下是不是留了太多临时表和中间查询用不上的删掉别一直堆着。6. 下一步学什么把数据分析练成肌肉记忆的三步路径入门阶段最怕的不是学不会是学完就搁下过两周全忘。所以最后一章不给知识清单给一条能照着走的路。按下面三个阶段推每个阶段两到三周中间不要跳。阶段重点练什么判断自己过关的标准第一阶段建表、导入、字段类型、主键能独立把一份 CSV 干净地导进来行数和金额都对得上第二阶段聚合查询、交叉表、常用表达式能不看教程写出按客户按月的汇总WHERE和HAVING不混用第三阶段操作查询、备份习惯、简单报表导出做批量改数前会先跑一遍验证查询改完数据能对上第一阶段的重点是「干净」。这段时间不要急着做分析就反复练导入故意准备几份脏数据带空行的、带千分位逗号的、日期格式不统一的一份一份导进去再验一遍。练到你能一眼看出「这列该用文本还是数字」后面就顺了。第二阶段开始接触真正的分析。找一份你自己关心的数据哪怕是记账本或者运动记录按月份、按类别做汇总再做成交叉表。这里的关键不是技巧多而是把「我想看什么」翻译成「怎么写查询」这个动作练熟。翻不出来的语法就查查完立刻用一遍用完再关掉文档凭记忆重写一次。第三阶段碰操作查询。这个阶段最该练的不是写得多快而是多谨慎。每次动手前复制一份文件每次执行前把条件当普通查询跑一遍。我自己的血泪经验是有次觉得「就改一列不用备份」结果条件里的引号写成中文全角一条语句把半年的金额全乘以了 1.1最后靠三天前的备份才救回来。从那以后我每次碰UPDATE和DELETE都强制走一遍「复制文件 → 条件查询 → 核对行数 → 执行」这四步一次没落过。等你把这四步变成肌肉记忆Access 2007 对你来说就不再是个陌生软件而是一个随手能用的工具。那份资源里还有更多细节——多表关联、参数查询、报表导出按你手上的实际需求挑着看就行不用从头读到尾。希望帮到你。本文还有配套的精品资源点击获取
