📙 数据处理技能

📖 知识讲解

在电商工作中，你拿到的表格往往来自不同的同事、系统、甚至手写记录。如果这些表格"长得很随意"，后面做排序、筛选、函数统计就会处处报错。规范，比技巧更重要——一张规范的表，能让 Excel 自动帮你干活；一张混乱的表，你每算一步都要手动修。这一课，我们先搞清楚"好表格长什么样"。

🔹 一维表 vs 二维表

这是最容易被搞混的概念。简单说：

• 一维表（清单式）：每一行是一条记录，每一列是一个字段。比如"1000 笔订单，每笔一行"。这是机器最爱、最适合分析的结构。
• 二维表（交叉表）：用行和列两个方向同时表达信息，比如"行是月份、列是商品"的销量矩阵。给人看很直观，但机器不好统计。

🔹 一表一主题

一张表只放一类主题的数据。不要把"订单"和"库存"塞进同一张表，也不要把"汇总行"混在"明细行"里。订单表就只放订单，统计的事交给函数和透视表。

🔹 表头必须在第 1 行

• 字段名放在第 1 行，下面紧接数据，上方不要留空行（很多同学喜欢空一行再写表头，这会导致 Excel 认不出表头）。
• 字段名要唯一、简短、不含单位：用"单价"而不是"单价(元)"，单位放到数字格式里。
• 不要写重复表头：数据中间不要又插一行"订单号/姓名…"，那会毁掉整张表。

🔹 数据行、列要连续

数据行之间不要留空行，列之间不要留空列。一旦有空行，Excel 的"自动扩展""筛选范围"就会在这里断开。

🔹 不要合并单元格

合并单元格是"好看但坑多"的典型。它会影响排序、筛选、VLOOKUP、透视表。标题可以合并，数据区域绝对不要合并。

🔹 一列只放一种数据类型

同一列要么全是文本，要么全是数字，不要混着来。例如"金额"列里既有 99 又有"¥99"，Excel 就分不清谁是数字、谁要算。

🔹 数字不带单位，日期要是"真日期"

• "¥99""99元""99 元"都要改成纯数字 99，再用单元格格式显示成货币。
• 日期必须是可以计算的真日期（本质是数字），而不是文本"2025.3.5"。真日期才能做"本月销售额""两个日期差几天"这类计算。

🔹 真正的"表格"：Ctrl+T

选中数据区域按 Ctrl+T，可以把普通区域变成 Excel 的智能表格（Table）。好处：

• 自带结构化引用（用列名写公式，如 =[@金额]*0.9，比 A2 好懂）
• 新增数据时自动扩展格式和公式，不用手动拖
• 自带筛选按钮，样式清爽

🔹 冻结首行 / 首列

数据多时，滚动会看不见表头。用视图 → 冻结窗格 → 冻结首行，或选中 B2 后"冻结窗格"可同时冻结首行首列。

🔹 字段命名"四不原则"

🔹 规范 vs 不规范 对照

❌ 不规范（Excel 讨厌）	✅ 规范（Excel 喜欢）
表头上方空两行	表头就在第 1 行
数字写成 "99元"	纯数字 99 + 货币格式
日期是文本 "2025.3.5"	真日期 2025/3/5
中间插空行、插重复表头	数据连续，只有一行表头
合并单元格存数据	不合并，每行一条记录
一列里文本数字混着放	一列只放一种类型

📝 理论测试

完成以下10道题，检验你对数据规范的掌握：

📖 知识讲解

"清洗"就是把拿到的脏数据，修整成干净、可用、能分析的数据。电商日常里，客户资料、订单导出经常带着空格、符号、重复行。掌握清洗，你就比 80% 的同事更能"干活"。

🔹 三步清洗法：看 → 拆 → 统一

• 看：先扫一遍，发现空格、单位、"¥"、乱日期、重复行在哪。
• 拆：用分列把混在一起的内容拆开（如"姓名-电话"拆两列）。
• 统一：统一格式、统一单位、删除重复项、填补缺失值。

🔹 删除重复项

路径：数据 → 删除重复项。关键是以哪几列为判重依据——勾选"订单号"就意味着"订单号相同的算重复"，只勾"手机号"则"同手机号算重复"。判重列选错，就会误删有用的不同记录。

🔹 分列（文本拆分神器）

路径：数据 → 分列。两种模式：

• 分隔符号：按逗号、空格、横杠、"-"等把一列拆成多列。
• 固定宽度：按固定字符位置切分（如身份证前6后8）。
• 顺带好处：分列最后一步选"常规"，能把"文本型数字"直接转成真数字！

🔹 TRIM 去首尾空格 + Ctrl+H 去所有空格

• =TRIM(文本) 去掉首尾及中间多余空格（只留单词间一个）。
• Ctrl+H 查找替换：查找" "（空格）替换为空，可一次性去掉所有空格（含中间）。

🔹 CLEAN 清不可见字符

从网页/系统导出的数据常带看不见的换行符、制表符。=CLEAN(文本) 可清除这些不可打印字符，常与 TRIM 搭配：=TRIM(CLEAN(A1))。

🔹 文本型数字转数值的 4 种方法

🔹 不规范日期的处理

• 分列法：选中日期列 → 分列 → 不选分隔符直接下一步 → 列格式选"日期 YMD"，"2025.3.5"变真日期。
• 重组法：若日期被拆成 年/月/日 三列，用 =DATE(年,月,日) 重新拼回真日期。

🔹 Ctrl+G 定位空值批量填充

路径：Ctrl+G → 定位条件 → 空值，然后输入内容按 Ctrl+Enter，可一次性给所有空单元格填同一个值（如"未知""待补"）。

🔹 缺失值与 #N/A 的统一处理

空单元格、#N/A 会让统计出错。策略：缺失的文本填"未知/待补"，缺失的数字填 0 或用 =IFERROR(原公式,"") 把错误转成空，保证下游公式稳定。

🔹 清洗前后做"数据备份"

📝 理论测试

完成以下10道题，检验你对数据清洗的掌握：

📖 知识讲解

一张 60 行的订单表，老板只问"广东省、已完成的、金额前 10 的单子"。你总不能一行行肉眼找。排序和筛选，就是 Excel 帮你"排好队、挑出来"的两把快刀。

🔹 排序前：先选中整行/整表！

这是新手最容易翻车的地方。如果你只点了一个单元格再排序，Excel 会问"是否扩展选定区域"：

• 选"扩展选定区域"→ 整行一起动，数据不错位（✅ 正确）。
• 选"以当前选定区域排序"→ 只有这一列动，其他列不动，整行数据就错位了（❌ 灾难）。

🔹 单关键字与多关键字排序

• 单关键字：数据 → 排序，选一个"主要关键字"（如订单金额），升序/降序。
• 多关键字：加"次要关键字"（如先按省份、再按金额），支持层层嵌套，先排主再排次。

🔹 自定义序列

默认的排序是按字母/数字。但"华东、华北、华南"或"初中高"这种业务顺序怎么办？用自定义序列（文件→选项→高级→编辑自定义列表，或排序时"次序→自定义序列"），告诉 Excel 你想要的先后。

🔹 按颜色 / 字体颜色排序

如果某些单元格被手动标了颜色，排序→次序选"单元格颜色"，可按颜色把同色行排到一起。

🔹 按行排序

极少数情况要按"行"排（转置后的表），排序→选项→按行排序。一般数据分析用不到，了解即可。

🔹 筛选：Ctrl+Shift+L

路径：数据 → 筛选，或快捷键 Ctrl+Shift+L。表头出现下拉箭头，点击即可筛选。

• 文本筛选：包含、开头是、结尾是、等于，支持通配符（* 任意多字符、? 单个字符）。
• 数字筛选：前 10 项、高于平均值、介于、大于/小于。
• 日期筛选：本月、下月、期间（介于两个日期之间）。
• 按颜色筛选：按单元格/字体颜色挑出特定行。
• 搜索式筛选：在搜索框输入关键字，实时过滤。

🔹 多列叠加筛选

在"省份"筛"广东"，再在"状态"筛"已完成"，两个条件叠加，结果是"广东且已完成"的子集。列越多，筛得越精细。

🔹 高级筛选

当条件复杂（如"广东或江苏""金额>1000 且 已完成"），用数据 → 高级筛选。要点：

• 先写好条件区域（表头 + 条件值，和原表隔开几行）。
• 同一行的多个条件 = "与"（同时满足）。
• 不同行的条件 = "或"（满足任一）。
• 可勾选"选择不重复的记录"，顺带去重。

🔹 筛选后求和的坑 → 用 SUBTOTAL

📝 理论测试

完成以下10道题，检验你对排序筛选的掌握：

📖 知识讲解

条件格式，就是"给数据立规矩"：当某个条件满足时，Excel 自动给单元格上色、加图标、画数据条。库存快没了自动变红、达成率高的自动变绿——数据自己会"报警"，你一眼就能发现问题。

🔹 四大类（其实是五小类）

路径：开始 → 条件格式。

🔹 公式规则（重点难点）

"新建规则 → 使用公式确定要设置格式的单元格"，这是最灵活也最容易写错的地方。核心掌握两点：

• 选中区域的写法：先选中要应用规则的范围，比如 $A$2:$H$50。
• 相对/绝对引用：公式基于"选中区域的左上角第一个单元格"来写。想让"整行随某列变化"，对该列用相对行、绝对列，例如 =$H2<10（H 列绝对、行相对）。这样拖动时，每一行都拿自己的 H 列去比，整行一起变色。

🔹 用 AND / OR / ISBLANK / COUNTIF 做复杂预警

• =AND($D2<$E2,$F2>100)：库存低于安全库存且近7日销量大 → 紧急。
• =OR($G2="退款中",$G2="已取消")：订单状态异常 → 标黄。
• =ISBLANK($C2)：某关键字段为空 → 提醒补录。
• =COUNTIF($A$2:$A$50,$A2)>1：商品编码出现超过 1 次 → 重复值标红。

🔹 管理规则、优先级与"停止如果为真"

开始 → 条件格式 → 管理规则，可以：调整优先级（谁先判断）、修改应用范围、勾选停止如果为真（满足上层规则就不再往下判，避免颜色打架）。

🔹 条件格式 vs 手动填色

🔹 克制使用，避免"彩虹表"

条件格式虽好，但满屏红绿黄蓝就成了"彩虹表"，反而看不清重点。一个表聚焦 1~2 个核心预警即可。

📝 理论测试

完成以下10道题，检验你对条件格式的掌握：

📖 知识讲解

清洗是"事后补救"，数据验证是"事前预防"。在别人填表之前，你就定好规矩：这一列只能填男/女、手机号必须 11 位、商品编码不能重复……从源头卡住错误，后面的分析就省心了。（老版本 Excel 叫"数据有效性"，365 里叫"数据验证"，是一回事。）

🔹 入口与允许类型

路径：数据 → 数据验证（数据 → 数据验证 → 设置）。"允许"下拉里可选：

🔹 下拉列表（序列）

• 直接输入：允许"序列"，来源写 男,女（英文逗号分隔）。
• 引用区域：来源选工作表里的一列（如 =$A$2:$A$10）。
• 定义名称做动态下拉：先把选项区定义为名称，来源写 =名称，选项增减自动更新。
• INDIRECT 二级联动（挑战）：省份选"广东"→城市只出广东的市。用"定义名称 + INDIRECT(省份单元格)"实现。

🔹 输入信息与出错警告

• 输入信息：选中单元格时弹提示，教用户怎么填（如"请选择男或女"）。
• 出错警告三种风格：
  · 停止：非法输入直接拦下，不许填（最严格）
  · 警告：弹窗询问"确定要填吗"，可取消
  · 信息：只提醒，不阻止

🔹 圈释无效数据

路径：数据 → 数据验证 → 圈释无效数据。已存在的旧数据如果不符合规则，会用红圈标出来，方便你逐条修正。

🔹 自定义公式防重复 / 限长度

• 防重复录入：允许"自定义"，公式 =COUNTIF($B$3:$B$100,B3)=1（B3 在范围内唯一才允许）。
• 限手机号 11 位：允许"自定义"，公式 =LEN(C3)=11。

🔹 用数据验证保护"表头不被改"

给表头行设置"自定义"公式 =FALSE 并选"停止"警告，别人就无法改表头；或配合保护工作表锁定表头区域。

🔹 重要提醒：只防输入不防粘贴

📝 理论测试

完成以下10道题，检验你对数据验证的掌握：