★ FEATURED ARTICLE 简介《Access 2007数据分析技巧详解》面向希望借助关系型数据库提升数据处理与分析能力的Access用户也适合需要从Excel过渡到更大规模数据管理的职场读者。全书围绕Access 2007展开先说明其在数据分析中的定位及相较Excel在可扩展性、分析透明度、数据与呈现分离、共享处理等方面的优势再讲解表创建、数据类型、数据导入、关系型数据库与查询基础。书中重点覆盖聚合查询、制表/删除/追加/更新等操作查询和交叉表查询并给出查找删除重复记录、填充空白字段、字段连接、文本与大小写转换、去除首尾空格、查找替换等数据转换方法。包内共1个PDF文件大小约11.35MB便于在电脑端阅读与检索已有80人学习。读者可据此系统掌握从数据整理、查询汇总到清洗转换的完整分析流程积累可直接迁移到实际工作的查询思路与操作范式。1. 十万行台账的分水岭为什么数据分析要落在 Access 2007 而不是继续用表格一份两万多行的销售台账放在表格软件里筛一次要等三秒改一处公式全表重算发出去三个人各改一版——到这一步再谈 Access 2007 数据分析就不是工具偏好问题了。这份资料讲的是把原始数据先放进一个关系型数据库里用查询技巧把统计口径固定成可复用的对象再用数据转换把脏字段洗干净最后才谈呈现。书里没有一上来教控件和窗体顺序是表、查询、操作查询、清洗这条线对做报表的人是对的。它适合手上有几万到几十万行、每个月都要重复出同一套口径报表的人也适合已经会用透视表但每次都得从头拖一遍的人。反过来如果数据只有几百行、一周看一次用表格更省事硬上数据库是给自己找麻烦。2. 关系型底座先立住表结构、字段类型与导入的三个决策点这一章是整份资料里最不性感、但最容易埋雷的部分。表结构一旦定错后面所有查询都在替它擦屁股数字存成文本排序和汇总全错金额用单精度浮点累加到几万行后合计对不上账你会怀疑人生。我见过太多人跳过这一章直接去学交叉表结果卡在“数据类型不匹配”上出不来。所以顺序是先想清楚数据会不会反复刷新再决定导入方式再按字段的真实语义定类型最后才动手导并且导完立刻做一次行数和合计对账。2.1 导入表还是链接表先按“数据会不会变”分流Access 2007 的外部数据入口在功能区“外部数据”选项卡里Excel、文本文件、其他 Access 数据库、ODBC 都在这一排。同一次操作里通常有两个选项“将数据导入当前数据库的新表中”和“通过创建链接表来链接到数据源”。这两个不是风格差异是两种完全不同的数据结构。导入表是把数据复制一份进当前.accdb文件好处是这份数据变成真正的本地表可以建索引、可以写更新和删除、可以当生成表查询的目标代价是源文件再改你这边不会跟着变得重新导一次。链接表是保留一个指针数据还在原来的 Excel 或文本文件里好处是永远读最新代价是几乎所有的写操作都会翻车。对照着看更清楚对比项导入表链接表数据存放位置复制进.accdb仍在源文件能否建索引能不能Excel 链接表能否做更新/删除查询能多数情况下报“操作必须使用一个可更新的查询”大数据量筛选速度快慢逐行走驱动源数据更新后需重新导入自动同步单文件上限2 GB受源文件限制我的判断标准很土这份数据只用来分析一次导入每天或每周要刷新用链接表接进来然后用一个生成表查询把它落地成本地表后续所有分析都对着落地表做。这样既拿到了最新数据又保住了本地表的索引和可更新性。链接表只当“进水口”不当“操作台”。还有一个细节值得当场记住链接 Excel 表时Access 的判断依据是每列前若干行的内容。整列本来该是数字但前几行里有“—”“N/A”或者空串这一列就会被判成文本之后所有 SUM 都返回空或者报类型不匹配。这不是 bug是它在按样本猜。所以链接表只适合看不适合算。2.2 字段类型与主键把“看起来像数字”的列定死新建表用“创建”选项卡里的“表设计”2007 的字段类型列表比早期版本多了“附件”去掉了用户级安全那一套。类型选择的原则只有一条按这一列的语义选不按它现在长什么样选。字段类型适合存什么常见误用文本编号、邮编、手机号、身份证号把纯数字编号设成数字前导 0 丢失数字-长整型数量、次数拿来存金额小数被截断数字-双精度需要小数的测量值拿来累加金额浮点误差累积货币金额、单价、合计很少被误用是最稳的选择日期/时间业务日期、时间戳用文本存日期区间查询全废自动编号代理主键当业务编号用删行后号段断裂是/否布尔标记用文本存“是/否”筛选要写两种值备注长文本、说明当普通文本用排序和索引受限“货币”这个类型值得单独说一句。它固定四位小数、做精确运算累加几万行不会出现 0.0000001 的偏差双精度是二进制浮点做乘除没问题做大量累加就会出现合计差几分钱的情况对账时非常难受。凡是和钱有关的列直接选货币别犹豫。主键同样要当场定。没有主键的表在数据表视图里删改记录很容易弹出“由于您和其他用户同时试图更改数据无法保存对记录的更改”看着像玄学实际就是引擎找不到唯一标识来定位那一行。要么加一个自动编号字段当代理键要么把业务上真正唯一的字段组合设成主键。业务键建主键时记得同时把索引属性设成“有无重复”经常出现在 WHERE 和 GROUP BY 里的字段单独建“有有重复”索引几万行的差别肉眼可见。2.3 一次完整的导入落地从建表到校验计数我的习惯是反着来不直接用导入向导建表而是先手工建一张空表字段名、类型、长度全按目标定好再用追加的方式把数据灌进去。这样字段类型永远不会被源数据“猜”错导入规范也可以保存下来下次复用。具体步骤是这样按 2.2 的规则建好空表比如tbl订单_导入主键先不设导入完成后再加。“外部数据”→“Excel”选中源文件选“将数据追加到现有表中”指定目标表。在向导里点“高级”逐列核对字段类型和跳过行数然后点“另存为”把这套设置存成一个导入规范下次直接选。导入完成后不要急着做查询先跑一次对账。对账这一步很多人省掉后面所有结论都建在错误数据上。我一般用一个查询把三件事一次看完行数、关键字段的空值数、金额合计。-- 导入后的第一道体检行数、空主键、金额合计 SELECT Count(*) AS 总行数, Sum(IIf([客户编号] Is Null, 1, 0)) AS 空客户编号行, Sum(IIf([订单日期] Is Null, 1, 0)) AS 空日期行, Sum([金额]) AS 金额合计 FROM [tbl订单_导入];这段 SQL 里Count(*)数的是物理行数不管字段是否为空IIf(条件, 1, 0)是 Access 里的条件表达式配合Sum就变成条件计数比写四个独立查询快得多Sum([金额])只有在金额列类型正确时才有意义如果它返回空基本可以断定这一列是文本类型回到表设计里改掉再重导。字段名里有中文或空格时用方括号包住这是 Access SQL 的硬要求不加就会报“语法错误”。行数和源文件对不上时先看是不是首行标题被当成数据了再看有没有隐藏行、合并单元格。金额合计对不上时先看有没有负数被当成文本、有没有千分位逗号没清掉。这两条排查完九成问题就定位了。3. 把口径写进 SQL从设计器网格到聚合、参数与日期边界查询是这份资料的重心也是最值得花时间的地方。理由很实在网格拖出来的查询换个字段就要重拖一遍口径散落在十几条查询里三个月后自己都说不清那张报表是怎么算的写成 SQL 之后口径是一个可以被读、被复制、被对比的文本改哪儿一目了然。这份资料虽然以设计器为主线但每个章节后面都会把对应的 SQL 摊开给你看这个做法我认可。3.1 设计器网格和 SQL 视图是一一对应的“创建”选项卡里点“查询设计”关掉弹出的显示表对话框右键或者用“视图”按钮切到“SQL 视图”你会看到一条空白的 SELECT。把同一件事在两边各做一遍对应关系就清楚了。设计器下半部分的网格有四行字段、表、排序、显示加上“条件”和“或”两行它们分别对应 SELECT 列表、FROM、ORDER BY、是否输出、WHERE 的第一组条件和后续 OR 分支。点工具栏上那个 Σ总计按钮网格会多出“总计”行这时候查询从明细变成聚合GROUP BY就出现了。SELECT [订单].客户编号, [订单].订单日期, [订单].金额 FROM 订单 WHERE [订单].订单日期 #2024-1-1# AND [订单].订单日期 #2025-1-1# ORDER BY [订单].金额 DESC;这段对应的是最普通的明细查询WHERE里的日期字面量在 Access 里必须用#包起来写成2024-1-1会按文本比较结果全错ORDER BY ... DESC对应网格里“排序”行选“降序”[订单].这个前缀在单表查询里可以省多表关联时不能省否则同名字段会打架。有一点要提前知道SQL 视图改过的查询回到设计器可能提示“无法用图形方式表示”尤其是用了UNION、子查询、TRANSFORM之后。这不是查询坏了是图形层表达不了只能留在 SQL 视图里维护。所以我的做法是网格搭骨架、SQL 改细节一旦复杂到图形层跟不上就干脆全用 SQL 写别来回切。3.2 聚合查询WHERE、GROUP BY、HAVING 的三层分工聚合是数据分析的主菜。Access 的聚合查询有三个执行层顺序是固定的先用 WHERE 从明细里筛行再用 GROUP BY 把行压成组最后用 HAVING 把组筛掉。搞混 WHERE 和 HAVING 是最常见的错误比如想筛“金额合计大于一万的客户”把条件写在 WHERE 里Access 会先报“试图执行一个不包含指定表达式作为聚合函数一部分的查询”。SELECT [订单].客户编号, Count(*) AS 单数, Sum([订单].金额) AS 金额合计, Avg([订单].金额) AS 平均单价 FROM 订单 WHERE [订单].订单日期 #2024-1-1# AND [订单].订单日期 #2025-1-1# GROUP BY [订单].客户编号 HAVING Sum([订单].金额) 10000 ORDER BY Sum([订单].金额) DESC;WHERE里的日期范围是行级过滤作用在聚合之前参与聚合的只有 2024 年这一年的数据GROUP BY [订单].客户编号决定结果集每个客户一行HAVING Sum([订单].金额) 10000是组级过滤作用在聚合之后所以它能引用聚合函数而 WHERE 不能。Count(*)数的是组内行数如果某个客户下单记录里有完全重复的行它会一起数进去要按订单号去重就写Count([订单号])因为聚合函数会忽略 Null但不会忽略重复去重要靠DISTINCT。DISTINCT在 Access 里的位置是SELECT DISTINCT设计器里对应查询属性中的“唯一的记录 是”。它和GROUP BY的区别是DISTINCT只去重不聚合GROUP BY通常会带聚合函数。取唯一客户名单这种活儿用DISTINCT更省事别硬套GROUP BY。还有一个属性值得记住“TOP 值”。设计器里右键查询属性把“TOP 值”改成 10SQL 里就会出现TOP 10配合ORDER BY就是“金额最高的十个客户”。它比先出全部结果再手工看前几行靠谱得多尤其是数据量大到数据表视图会卡的时候。3.3 参数查询与日期边界为什么 Between 会少一天固定日期范围的查询下个月就得改一次很蠢。参数查询解决这个问题把条件写成[请输入开始日期]运行时会弹框让你填。但有一个必须做的动作——在 SQL 顶部用PARAMETERS声明参数类型。PARAMETERS 起 DateTime, 止 DateTime; SELECT [订单].客户编号, Sum([订单].金额) AS 金额合计 FROM 订单 WHERE [订单].订单日期 [起] AND [订单].订单日期 [止] GROUP BY [订单].客户编号;不写PARAMETERS那行Access 会把用户输入的值当文本处理和日期字段比较时可能出现“条件表达式中的数据类型不匹配”或者更阴的——不报错但结果为空。DateTime这个类型名是 Access SQL 的关键字写Date也能认但DateTime更明确。日期边界是另一个高频事故点。很多人写Between #2024-1-1# And #2024-12-31#结果 12 月 31 日下午的订单一条都没算进去。原因很简单日期字段里存的是日期加时间#2024-12-31#等价于2024-12-31 00:00:00带时分秒的记录比它大自然落在区间外。解决办法是养成左闭右开的习惯 起 AND 止止传成下一天或者下一个月初。这样不管字段里有没有时间部分边界都是干净的。另一个常见做法是用DateValue([订单日期])把时间抹掉再比较能出结果但字段上套了函数就没法用索引几万行以上会明显变慢只适合临时核对。顺带说一句按月分组的写法。Format([订单日期], yyyy-mm)能出“2024-01”这样的标签可读性好但同样会导致索引失效而且它是文本排序是按字符串排的。要既快又稳就在生成表阶段单独算一个月份字段存成日期型的月初后续聚合直接对它分组。4. 操作查询四件套生成表、追加、更新、删除的执行顺序与交叉表转置操作查询是这份资料里“敢用就回不去”的部分。前面三章的查询都是只读的跑错了顶多结果难看操作查询不一样跑错了数据就没了而且没有后悔药。所以这一章的重点不是语法是顺序——先备份再动数据动完立刻复核。4.1 生成表查询给每一步操作留一层中间层生成表查询的语法是SELECT ... INTO 新表名 FROM ...。它在 Access 里对应“查询类型”里的“生成表查询”执行时会问你要新表的名称和目标数据库。-- 把 2024 年的明细落地成一张本地中间表 SELECT [tbl订单_导入].订单号, [tbl订单_导入].客户编号, [tbl订单_导入].订单日期, [tbl订单_导入].金额, DateSerial(Year([订单日期]), Month([订单日期]), 1) AS 月初 INTO [tbl订单_2024] FROM [tbl订单_导入] WHERE [tbl订单_导入].订单日期 #2024-1-1# AND [tbl订单_导入].订单日期 #2025-1-1#;DateSerial(Year(d), Month(d), 1)算出每个月的一号存成日期型后面按月汇总时直接GROUP BY 月初既有索引又不会有字符串排序的问题。INTO子句要求目标表不存在如果表已存在会直接报“表已存在”所以重跑之前得先删掉旧表。字段类型由源字段推断文本长度可能被放大到 255这一点在后续追加时需要留意。生成表查询真正的价值是当中间层。原始导入表保持只读、不动它所有清洗、去重、汇总都从它派生新表出错就删掉中间表重来。这套做法听着笨但它是唯一能让你在三个月后还敢改逻辑的方式。4.2 追加、更新、删除不可逆操作的执行顺序这三类操作我固定按一个顺序走先用相同的 WHERE 做一次SELECT Count(*)看清楚影响多少行再用SELECT INTO做一份备份然后才执行操作查询最后再用同一个 WHERE 复核一遍。追加查询的写法是INSERT INTO 目标表 (字段列表) SELECT ...。字段顺序不必和源表一致但类型必须兼容文本往数字里塞会整行被丢弃而且不报错——这是最坑的地方。-- 把 2023 年及以前的数据归档到历史表 INSERT INTO [tbl订单_归档] ( 订单号, 客户编号, 订单日期, 金额 ) SELECT 订单号, 客户编号, 订单日期, 金额 FROM [tbl订单_导入] WHERE 订单日期 #2024-1-1#;字段列表必须显式写出不要依赖SELECT *因为源表加一个字段SELECT *的列数就变了追加会直接失败。金额这种关键字段尤其要核对目标表的类型是不是货币如果是长整型小数会被静默截断。更新查询的关键在 WHERE。下面这条把年度金额过十万的客户打上 A 级标记UPDATE [tbl客户] SET [客户等级] A WHERE [客户编号] IN ( SELECT [客户编号] FROM [tbl订单_2024] GROUP BY [客户编号] HAVING Sum([金额]) 100000 );SET后面可以一次改多个字段用逗号分隔WHERE ... IN (子查询)在 Access 里可用但子查询里带GROUP BY时会慢一些。数据量大就分两步先用生成表查询把 A 级客户编号落地成一张小表再用UPDATE ... INNER JOIN关联更新UPDATE 目标表 INNER JOIN 来源表 ON 条件 SET 字段 值这个语法 Access 是认的。删除查询的语法是DELETE FROM 表 WHERE 条件如果涉及多表或别名要写成DELETE 表.* FROM 表 WHERE ...。执行前那句提示框会告诉你影响多少行那个数字一定要看不看就点“是”的人早晚要在生产库上重来一次。4.3 交叉表查询TRANSFORM…PIVOT 与列漂移交叉表是把行转成列Access 的语法结构和其他数据库的透视写法不太一样是三段式TRANSFORM Sum([金额]) AS 金额合计 SELECT [客户编号], Sum([金额]) AS 行合计 FROM [tbl订单_2024] GROUP BY [客户编号] PIVOT Format([月初], yyyy-mm) IN (2024-01,2024-02,2024-03,2024-04);TRANSFORM后面跟的是单元格里的聚合值这里是金额合计SELECT ... GROUP BY决定行方向每个客户一行顺便带一个行合计PIVOT后面决定列方向Format([月初],yyyy-mm)生成列名。设计器里对应的操作是“查询类型”→“交叉表查询”网格里会多出一行“交叉表”可以给字段指定“行标题”“列标题”“值”。IN (...)这段列清单是重点很多人不写结果源数据里多出一个月报表就多一列下个月又少一列每次都要重排格式非常难受。写上IN之后列名和顺序被固定死不在清单里的值会被丢掉。代价是新增月份要手工补这是可预期的维护成本比列漂移好得多。两个硬限制记一下交叉表的列数上限是 255超过就报错交叉表的结果集不可更新想改数据必须先落地成普通表。5. 常见问题排查五类翻车现场的现象、成因与定点修复这一章按“现象—原因—解决”来写全是实际会撞上的。有些属于配置问题有些属于 SQL 写法问题还有一些是 Access 本身的机制决定的知道了一次就不会再犯。5.1 导入之后字段类型跑偏两个必须当场发现的现象现象一金额列能看见数字但排序错乱Sum返回空或者报“数据类型不匹配”。打开表设计一看字段类型是文本。原因在于导入向导是按前若干行样本推断类型的源列里只要混进了“N/A”“待定”、千分位逗号或者前后空格整列就被判成文本链接 Excel 表时这个判断更激进因为它连样本行数都不给你看。解决办法是别让向导建表先按目标类型手工建好空表再用“追加到现有表”导入或者在向导的“高级”里逐列指定类型并存成导入规范下次直接调用。已经导错了的不要在原表上改类型硬扛重建一张表重导更快。现象二小数位消失12.34 变成 12合计差一截。两种可能字段类型被设成了长整型或者源数据是用文本存的带小数的值。前者的修复是在表设计里改成货币或双精度改完 Access 会尝试就地转换转不动的行会报“类型转换失败”那就得重导。后者的修复是在导入前把源列清洗成纯数字。这里再强调一次金额用货币不要用单精度单精度只有七位有效数字几万行累加之后合计对不上排查起来极其痛苦。5.2 操作查询阶段的三次翻车现象三查询一执行就报“操作必须使用一个可更新的查询”。这条报错的意思是“我能读但我不知道该怎么把结果写回去”。常见成因有四种查询基于链接的 Excel 表查询里用了聚合或交叉表多表连接里有一侧是外部连接查询属性里勾了“唯一的记录”。解决办法统一先用SELECT ... INTO把结果落地成本地表再对本地表做更新或删除。链接表只当数据入口不当操作对象这条规矩能省掉一半的报错。现象四更新或删除执行完影响的行数远超预期而且撤不回来。操作查询点“是”就提交Access 的数据表视图没有针对它的撤销。行数超预期的典型原因是 WHERE 里用了 OR 却没加括号A AND B OR C会被解析成(A AND B) OR C条件一下子放宽了另一个原因是 WHERE 里引用了查询里起的别名别名在 WHERE 阶段还不存在条件被忽略。我的固定动作是写完操作查询先切成 SELECT用同样的 WHERE 跑一次Count(*)再跑一次SELECT TOP 10 *看样本确认没问题再改回 UPDATE 或 DELETE。备份用生成表查询一条语句的事比出事之后再想办法强。现象五按月统计总是少一天或者多算一天。典型写法是Between #2024-1-1# And #2024-1-31#而订单日期里带时分秒1 月 31 日下午的单子全部落在区间外。反过来如果止日期写成下个月 1 号又用了就会把下个月 1 号 0 点整的记录算进来。统一改成 起 AND 止止传成下个周期的第一天两头都干净。这类问题在明细核对时几乎看不出来只有合计对不上才暴露所以对账查询要常备。5.3 文本与空值的隐蔽坑现象六两个文本字段用连接整列变成空白Trim之后长度还是不对和别的表匹配不上。前半个问题出在它遇到 Null 会把整个表达式变成 Null而会把 Null 当空串处理。连接文本一律用或者显式写Nz(字段, )。后半个问题出在 Trim 的能力边界Access 的Trim只去掉半角空格 Chr(32)从网页或系统导出带过来的不间断空格 Chr(160)、制表符 Chr(9)、回车换行 Chr(13)Chr(10) 它一个都去不掉肉眼看着一模一样长度就是差一两个字符匹配自然失败。解决办法是嵌套Replace把这几类不可见字符逐个替换掉再用 Trim 收尾。清洗链的写法在下一章展开。6. 清洗收尾与自证拆合字段、不可见字符与两条对账查询清洗这一步我的原则是能在一个查询里做完就别拆成五个手工步骤因为手工步骤无法复现。下面这条查询把字段拆合和不可见字符清理一次做完字段名按实际替换即可。SELECT [客户名称], Trim(Replace(Replace(Replace([客户名称], Chr(160), ), Chr(9), ), Chr(13) Chr(10), )) AS 名称_清洗后, Left([客户编号], 4) AS 地区码, Mid([客户编号], 5, 6) AS 流水号, IIf(InStr([地址], 省) 0, Left([地址], InStr([地址], 省)), ) AS 省份 FROM [tbl客户_导入];Replace从内往外执行先干掉不间断空格再干制表符最后干回车换行外面套Trim收掉残留的半角空格Left和Mid按固定位置拆分编号前提是编号长度规整不规整就改用InStr找分隔符IIf(InStr(...)0, Left(...), )是“找到了才截找不到给空串”避免Left遇到 0 位置报错。这套写法比手工一列一列改可靠得多而且下个月重跑一遍就行。清洗完必须自证不能靠肉眼扫。我固定跑两条对账查询用UNION ALL把源和目标的指标摆在一起-- 行数与金额双向对账 SELECT 源 AS 来源, Count(*) AS 行数, Sum([金额]) AS 合计 FROM [tbl订单_导入] WHERE [订单日期] #2024-1-1# AND [订单日期] #2025-1-1# UNION ALL SELECT 目标, Count(*), Sum([金额]) FROM [tbl订单_2024];行数不一致通常是去重去多了或者日期边界写错了行数一致但合计不一致基本可以断定是金额字段类型不对或者有行被静默丢弃。两条查询都通过才敢往下一步做交叉表和报表。提示UNION ALL两侧的列数和类型必须一致Count(*)返回长整型、Sum返回数值混着写一般没问题但如果某一侧Sum全是 Null结果会显示空白而不是 0用Nz(Sum([金额]), 0)包一层更稳。还有一个习惯值得养成把数据库拆成前端和后端两个文件后端只放表前端放查询、窗体和报表用“数据库工具”里的“拆分数据库”一条命令就能做。以后清洗逻辑要改、报表要调只动前端数据不动出问题也好回退。这个操作五分钟能做完能省掉以后无数次“刚才那个查询被我删了”的血泪经验。从那以后我每次动操作查询之前都强制走一遍“先 SELECT 计数、再 SELECT INTO 备份、才改类型执行”这套流程一次没落下过。慢是慢了一点但再没出现过整表数据回不来的情况。希望帮到你。本文还有配套的精品资源点击获取
阅读完成 · 觉得有帮助？
