Access 2007数据分析入门：从Excel导入到交叉表查询

简介：《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 数据量过万就开始卡，问题不在电脑配置

先给个自查办法：打开你最常处理的那张表，按 Ctrl+End 跳到最后一个有内容的单元格，看左下角行号是多少。如果超过三万行，而且表里还带一堆 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。下面这张表是我自己踩过几次坑之后总结的判断标准，你可以对着自己的场景比一比。

如果三条以上落在右边，那这套 Access 2007 数据分析的路子就值得花时间。反过来，如果你只是想临时看个数、画个图，Excel 五分钟能搞定的事，别硬搬。

3. 零基础先认清四样东西：表、字段、主键、关系

Access 被叫"关系型数据库"，这个名词吓退过不少人。其实拆开看只有四个概念，用一张通讯录就能讲明白：表是整本通讯录，字段是每一栏（姓名、电话、地址），主键是给每个人编的唯一编号，关系是"这一页的人"和"另一页的订单"怎么对应上。这四样搞懂了，后面所有的查询都只是在这四样上面做加减法。这一章不写代码，全是概念和对照，但每一节都告诉你该在软件里点哪里。

3.1 表：一张规整到不能再规整的清单

表就是一张二维清单，但它对"整齐"的要求比 Excel 严格得多。三条硬规矩：一行只能是一条完整记录，一列只能放一种信息，一个格子里只能有一个值。听起来像废话，但新手翻车最多的就是这三点。

最常见的错误是把好几样信息塞进一个格子，比如"区域甲/产品一/2007-01-05"全写在"备注"里。人看着舒服，机器完全没法用——你想按区域汇总，它不知道该从哪切。正确做法是拆成三列：区域一列、产品一列、日期一列。判断标准很简单：如果某一列你以后想按它筛选或分组，它就必须独立成列。

另一个常见错误是合并单元格和空行。Excel 里合并一下好看，导进 Access 就全乱了：合并区域只有第一个格子有值，其余全是空，一汇总就少一半数据。所以在准备源数据的时候，先把合并单元格全拆了，把空行删干净，第一行就是纯纯的列标题，中间不要插任何装饰性内容。

3.2 字段和数据类型：别让数字变成文本

每个字段都要指定一个数据类型，这一步决定了你以后能不能对它求和、排序、算日期差。Access 2007 里常用的是这么几种：

重点说两个。第一，编号类的字段（订单号、工号、商品编码）一律用文本，不要用数字。因为数字类型会把前面的零吃掉，"001"存进去变成"1"，以后再按编号匹配就全对不上了。第二，金额一律用货币类型，别用"数字"里的单精度或双精度，浮点数算钱会出现 0.1+0.2 不等于 0.3 这种事，对账的时候能让你怀疑人生。

3.3 主键：每条记录的唯一身份证

主键就是一列，它的值在这一行里独一无二，绝不允许重复，也不允许为空。有了它，你才能准确地点名某一条记录，才能在两张表之间建立对应关系。

最省事的做法是加一个"自动编号"字段当主键：新建表时 Access 会问你要不要加，选是就行，它自己从 1 开始往上加，永远不会重复。如果你已经有天然唯一的字段（比如订单编号），也可以指定它当主键，但前提是你自己能保证它不重复——一旦录入两条一样的订单号，Access 会直接拒绝保存，报错给你看。

新手会问：不加主键行不行？技术上可以，但你会失去三样东西：不能建关系、不能可靠地更新或删除单条记录、排序结果不稳定。所以别省这一步，建表第一件事就是把主键定下来。

3.4 关系：两张表怎么"对得上话"

现实里的数据很少能塞进一张表。客户信息一张表，订单记录一张表，如果每笔订单都把客户姓名、电话、地址重抄一遍，改一个电话就要改几百行，还容易改漏。关系的意义就是：客户信息只存一份，订单表里只存一个客户编号，需要详细信息时两边一对，就都出来了。

操作路径是：功能区"数据库工具"→"关系"，把两张表加进来，按住"客户表"的主键拖到"订单表"的客户编号上松手，弹出对话框里勾选"实施参照完整性"，确定。这条线就是一对多关系：一个客户对应多笔订单。

"实施参照完整性"这个勾很关键，它保证你不可能在订单表里填一个客户表里根本不存在的编号。听起来是限制，实际是保护——它帮你把脏数据挡在门外，省掉后面一大堆核对工作。

4. 第一个能跑通的例子：从 Excel 导入到交叉表汇总

概念讲完，该动手了。这一章带你把一份 Excel 表格导进 Access 2007，然后一步一步做出三个查询：筛选、分组汇总、交叉表。每一步都给出该点哪里、该填什么，以及一段可以直接抄进 SQL 视图的语句。你不需要理解全部语法，先让它跑出结果，看到屏幕上出现自己算出来的数字，信心就来了。

4.1 第一步：准备一份最小可用的练习数据

别拿你工作上最复杂的那份表练手，先造一份十行以内的小数据。打开 Excel，建这么一张表，第一行是列标题，从 A1 开始，中间不要有空行空列：

注意两点：订单编号写成"文本"样子的 A0001，别让它变成纯数字；单价列不要带货币符号，纯数字就行。存成练习数据.xlsx，放桌面。这份数据只有八行，但足够你把整套流程走通三遍。

4.2 第二步：把这份数据导进 Access 2007

打开 Access 2007，选"空白数据库"，起个名字比如练习库.accdb，选好保存位置，点创建。然后：

1. 点功能区"外部数据"选项卡，找到"Excel"按钮，点它，选"导入 Excel 工作表"。
2. 浏览到桌面那份练习数据.xlsx，勾选"第一行包含列标题"，下一步。
3. 这一步会列出每一列的名字和推断出来的数据类型。逐个确认：数量和单价必须是"数字"或"货币"，下单日期必须是"日期/时间"，其余保持"文本"。
4. 勾选"让 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 的专有写法，用双引号会报错。改完点工具栏上的红色感叹号运行，看到结果后按 Ctrl+S 保存，查询名字填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 坑四：更新查询一执行，数据就回不来了

现象：手滑点了一下更新查询的运行按钮，几十行数据被覆盖，想撤销，发现 Ctrl+Z 按了没反应。

原因：Access 里的"操作查询"（更新、删除、追加、生成表）执行后是直接写库的，没有撤销。Excel 里改错了 Ctrl+Z 能救回来，这里没有后悔药。而且很多人是在设计视图里随手点运行，根本没意识到它已经真改了。

解决：三条纪律。第一，做任何操作查询之前，先复制一份数据库文件，命名加上日期，这是最笨也最有效的办法。第二，正式跑之前，把操作查询改成"选择查询"预览一遍，确认它筛出来的正好是你要改的那批记录——方法是先写SELECT * FROM ... WHERE ...，看结果行数和内容对不对，再把SELECT改成UPDATE。第三，跑完立刻用另一个查询核对结果。下面是一个更新查询的标准样子，注意WHERE一定要写死：

-- 把区域甲里单价为空的记录，单价统一补成 100 UPDATE 销售明细 SET 单价 = 100 WHERE 区域 = '区域甲' AND 单价 Is Null;

SET后面是改什么，WHERE后面是改哪些。没有WHERE的 UPDATE 会改整张表，这是最经典的翻车方式，跑之前盯三秒确认条件写了。

5.5 坑五：文件越用越大，打开越来越慢

现象：刚建库时打开秒开，用了两个月，文件从几兆涨到几百兆，双击要等半天，切换对象也卡。

原因：三个来源。一是频繁导入导出和删除数据，Access 不会自动回收腾出来的空间，文件只涨不缩；二是你在表里存了图片、附件这类大对象，一张图就顶几千行文字；三是库里的临时对象、旧查询越堆越多。

解决：定期做一次"压缩和修复数据库"，路径是功能区"数据库工具"→"压缩和修复数据库"，它会重建文件、回收空间，通常能瘦下来一大截。图片和附件不要直接存进表里，存文件路径就行，需要看的时候再按路径打开。另外，删除数据用删除查询批量删，比一行行手动删更干净，删完再压缩一次。这个操作我一般一个月做一次，五分钟的事，能避免很多莫名其妙的卡顿。

6. 下一步学什么：一条从"会用"到"用顺"的路径

第一个例子跑通之后，最容易犯的错是到处乱翻，看到什么学什么，结果每个功能都摸了一下，真用的时候一个都想不起来。我建议按下面这个顺序补，每一阶段都要有能拿出手的产出，别只看不练。

前三周是地基，第四周开始接触"会改动数据"的操作，所以务必先备份再动手。操作查询里最值得先掌握的是追加查询，因为它对应的是"每个月把新数据并进主表"这个真实需求，用熟了你的整个流程就闭环了：新数据先进临时表，检查无误后用追加查询并入主表，所有汇总查询自动更新。删除、更新这两种放在最后练，原因你从第 5 章应该已经看出来了。

还有个小技巧：把每个查询都当成一个小零件，别写一个巨无霸查询干所有事。先做一个"清洗后明细"查询，把该算的列算好、该排除的行排掉，后面所有汇总都从这个查询里取数。这样源数据变了，你只需要维护最前面那一个查询，后面十几个都跟着变。我第一次学的时候图省事，把所有逻辑塞进一个查询，后来需求一变，改一处崩三处，重做了整整一天。

从那以后我每次新建库，第一件事都是先写一张"清洗后明细"查询，再往下搭所有东西，这个习惯救过我很多次。希望帮到你。如果你手上已经有这份资料，建议直接翻到讲查询基础和交叉表的那两块，边看边在电脑上点一遍，比读十遍都管用。

本文还有配套的精品资源，点击获取