01 · 数据整理

菜单:清理表格 / 文本转换 / 清洗规范 · 共 25 个工具

本组负责「把表洗干净」:填空、清格式、去重、排序、换文字,以及七个一键式清洗工具。

多数工具会覆盖选区,动手前建议先保存,或复制一份工作表。


清理表格

填充空单元格 bg_b1

  • 用途:把选区里的空白格一次补齐,适合名单、工参、台账里「空着的格要跟上面一样」或「统一填某个值」。
  • 怎么用:框选区域 → 点按钮 → 选一种填法:
    • 手动输入:填一个固定内容(默认);
    • 用上方单元格的值:照抄上一格,适合分组合并后的空白;
    • 取某个单元格的内容:点「框选」回表里指定一格,连数字格式一起搬。 可勾「只填看得见的格子」,筛选状态下不会填到隐藏行。
  • 结果与注意:直接写回空白格,原有内容不动;公式格不填。填充前弹窗会显示「一共会填多少格」。

清除格式 bg_b2

  • 用途:去掉选区的底色、字体、边框、字号等外观,只留数据,适合拿到别人做的花表格后重新排版。
  • 怎么用:框选区域 → 点按钮 → 确认。
  • 结果与注意:清的是格式,不清内容;数字格式是否一起清,以弹窗选项为准。清完可用 Ctrl+Z 撤销。

清除数据格式 bg_b2b

  • 用途:只把「数字格式」恢复成常规,解决日期显示成 4 万多、编号变成 1.23E+11、百分比乱跳等问题。
  • 怎么用:框选区域 → 点按钮 → 确认。
  • 结果与注意:保留底色、字体、边框;值本身不变,变的是显示方式。

删除空白行 bg_h3

  • 用途:按选中的一列或多列判断,整行都空就删掉整行,适合清洗导出报表中间的空白分隔行。
  • 怎么用:选中用来判断的一列或多列(可以点列标选整列,Ctrl 可加不连续的列)→ 点按钮 → 确认。
  • 结果与注意:只要选中的这些列里有一格非空,这行就保留;这些行在别的列即使有内容,也会跟着整行删掉,确认框会提醒。计算范围只到选中列最后一个非空行。

删除格内空行 bg_h3b

  • 用途:一个单元格里有多行文字(Alt+Enter 换行),把里面的空行去掉。
  • 怎么用:框选要处理的格子 → 点按钮 → 指定结果写到哪里。
  • 结果与注意:默认输出到指定区域,不动原格;适合地址、备注、多行说明。

数据脱敏 bg_ds1

  • 用途:把表格里的身份证、手机号、银行卡、姓名打成星号,用于外发、演示,同时保留还原能力。
  • 怎么用:框选表格 → 点按钮 → 选择要脱敏的类型 → 执行。需要还原时再点一次,选「还原」。
  • 结果与注意:只处理认得出格式的格子,认不出的原样不动;公式格不碰;原文存在本工作簿的隐藏备份表里(VeryHidden,右键取消隐藏也看不到)。还原时会核对当前格是否还是当初的星号串,被人改过的格会跳过,不覆盖新数据。

文本转换

文本转数字 bg_a13

  • 用途:把「看起来是数字、实际是文本」的内容转成能求和、能排序的真数字,例如从系统导出的 00123、带空格的 45 。
  • 怎么用:框选一列或多列 → 点按钮 → 按弹窗选项执行。
  • 结果与注意:公式格跳过;转不了的(夹杂汉字、日期文字)原样保留并报数。转换后前导零可能消失,需要保留编号格式的请改用「数字转文本」或先设文本格式。

数字转文本 bg_a14

  • 用途:把数字转成文本,保住前导零、长编号、身份证号,避免变成科学计数法。
  • 怎么用:框选区域 → 点按钮 → 执行。
  • 结果与注意:公式格跳过;转成文本后不能直接求和,需要计算时再用「文本转数字」转回来。

大小写转换 bg_a4

  • 用途:数字金额 ↔ 中文大写金额互转,例如 1234.56 ↔ 壹仟贰佰叁拾肆元伍角陆分。
  • 怎么用:框选金额列 → 点按钮 → 选「转大写」或「转小写」→ 执行。
  • 结果与注意:只处理能认出的金额;公式格跳过;大写结果是文本,小写结果写回真数字。财务票据请转换后人工复核角分。

转拼音 bg_py

  • 用途:把中文转成拼音,适合做账号、排序辅助列、英文名参考。
  • 怎么用:框选中文列 → 点按钮 → 在弹窗里选:
    • 拼音样式:全拼 / 带声调 / 首字母;
    • 分隔方式:无分隔 / 空格 / 短横 / 下划线;
    • 大小写:小写 / 首字母大写 / 全大写;
    • 落点:原地覆盖 / 写到右边一列。
  • 结果与注意:多音字按内置词表取音,人名、地名可能不准,重要场合要人工核。公式格不碰。

中文转全拼 bg_py1

  • 用途:「转拼音」的一键预设版,专门把一列站点名/地名转成连写小写全拼(江门恩平 → jiangmenenping)。
  • 怎么用:框选中文列 → 点按钮,直接算完。
  • 结果与注意:结果写在选中列右边一列、逐行对齐,不覆盖原中文;参数固定,不弹窗。与「转拼音」共用同一张离线读音表。

中文数字转阿拉伯数字 bg_cn1

  • 用途:把「二〇二六」「叁万元整」「一百零八」转成能计算的阿拉伯数字。
  • 怎么用:框选中文数字列 → 点按钮 → 执行。
  • 结果与注意:认不出的格子原样跳过,绝不写 0;写进去的是真数字,可直接求和排序;公式格跳过。

繁简互转 bg_hz1

  • 用途:简体 ↔ 繁体互换,适合港澳台资料、对外文档。
  • 怎么用:框选区域 → 点按钮 → 选「转繁体」或「转简体」→ 执行。
  • 结果与注意:只转汉字,标点、字母、数字、全角符号原样保留;空格子跳过;公式格不碰;纯数字格不会为了转换而改成文本。

字符数统计 bg_wc1

  • 用途:像 Word 的「字数统计」一样,统计选中区域有多少字,用于论文、简介、备注限字。
  • 怎么用:框选区域 → 点按钮 → 看报告。
  • 结果与注意:不往表里写任何东西;分别统计含空格、不含空格;日期按显示文字算,不按底层序列号。

批量查找替换 bg_fr1

  • 用途:一次挂 N 条查找替换规则,按顺序跑完,适合一批固定错别字、旧名称、旧编码的统一替换。
  • 怎么用:框选区域 → 点按钮 → 逐条添加「查找内容 / 替换为」→ 可选正则 → 先「预览」看会动哪些格 → 确认执行。
  • 结果与注意:公式格、日期格默认不碰;筛选状态下只改看得见的格;规则按列表顺序执行,前面的结果会影响后面的匹配。

批量加前后缀 bg_af1

  • 用途:给每格开头或结尾统一补内容(单位、编号前缀、备注后缀),适合给一批编码、姓名、地址统一加工。
  • 怎么用:框选区域(也可以整行 / 整列选)→ 点按钮 → 填前缀、后缀,不要的一边留空 → 开始。
  • 结果与注意:原位改写,不新增列;整行 / 整列选中时自动收到有数据的范围,并且只处理非空格;框选则选区内每格都加;公式、错误值、日期、布尔值一律不碰;建议先备份或复制一份工作表。

清洗规范

数据去重 bg_dd1

  • 用途:去掉重复值,保留唯一值;支持整列、整行和普通区域。
  • 怎么用:框选区域 → 点按钮 → 选输出去向(写到指定起始格 / 复制到剪贴板)→ 「输出内容」选一种:值 + 出现次数 / 只出值 / 多行多列摊成一列 → 执行。
  • 结果与注意:方向按选区形状判断——只选 1 行时横向去重;其余情况(1 列、多行多列、整列)按列各自纵向去重,结果并排摆放。多列选区复制到剪贴板时是整块一起判重。
  • 多行多列摊成一列(2026-11-11 加):选中的是好几行好几列、但只想要一列清单时选它。整块按读表顺序(第一行从左到右,再下一行)摊平、跨列一起判重(同一个值在别的列再出现也只留一个),从你框的输出起点开始往下排,只占 1 列;这一档只出值,不带出现次数。输出起点照旧点「框选起始单元格」选,不框就落在数据右边隔一列。
  • 文本写回会自动加「按文字存」标记,001、=1+1 不会被 Excel 改掉。

按行去重 bg_dr1

  • 用途:按指定的几列判重,重复行可以只标黄、删除,或把去重结果复制到新表。
  • 怎么用:框选数据区域 → 点按钮 → 选判重列(留空 = 整行)→ 选处理方式(标黄 / 删除 / 复制到新表)→ 执行。
  • 结果与注意:默认只标黄,不改数据;筛选或有隐藏行时「删除整行」直接禁用,因为看不见的行也会被删;判重列写错、没有重复行都会在动手前拦下。

多条件排序 bg_b4

  • 用途:按 1~4 个条件依次排序,并可顺带做「中国式排名」。只选 1 个条件时就是普通自定义排序,升降序和自定义序列都能用。
  • 怎么用:框选数据区域(第一行当标题,不参与排序)→ 点按钮 → 「条件个数」选 1~4 → 依次选每个条件的列和「升序 / 降序 / 自定义」→ 选排名输出列 → 点「开始排名」。
  • 结果与注意:排序会移动整行数据;有合并单元格的区域不建议直接排。排名按排序后的行顺序从 1 往下写;选了「自定义」会弹出窗口,一行一个值,按填写顺序排,没写到的值排在最后。

数据质量体检 bg_dq1

  • 用途:选中一块数据,逐列检查空值、重复、文本数字、文本日期、首尾空格、全角字符、IQR 异常值、身份证/手机号疑似错误,先知道数据哪里脏。
  • 怎么用:框选数据区域 → 点按钮 → 看只读报告。报告里按列列出问题类型和数量。
  • 结果与注意:一格都不写,纯体检;报告可以照着去用对应的清洗工具。异常值用 IQR 口径,业务上合理的极端值要自己判断。

空值处理 bg_nv1

  • 用途:把空白格按规则补齐,适合数值列补均值、分类列补「未知」、时间序列向下/向上填充。
  • 怎么用:框选区域 → 点按钮 → 选填充方式:
    • 向下填充(用上一格);
    • 向上填充(用下一格);
    • 本列平均值;
    • 本列中位数;
    • 填 0;
    • 填指定文字。 可勾「只处理可见格」。
  • 结果与注意:只填空格;公式格不填;勾了「只处理可见格」时隐藏/筛选行跳过。弹窗会实时算「会填几格」。

金额/数量清洗 bg_am1

  • 用途:把一列「¥1,234.50」「1.2万元」「(500)」「30%」这类内容洗成能计算的数字;数量列可把数字和单位拆开。
  • 怎么用:框选一列 → 点按钮 → 选金额或数量 → 选输出到原列右边(默认)或覆盖原列 → 执行。
  • 结果与注意:认货币符号、千分位、万元/亿元、括号负数、百分号;认不出的格跳过不改;公式格一律不动;覆盖原列时隐藏/筛选行也不动;覆盖必须再确认一次。

金额/单号清洗 bg_fc1

  • 用途:财务表一次搞定两件相反的事——金额洗成真数值(能求和),单号洗成真文本(前导零、长号码不被改坏);两列以上可以一起框。
  • 怎么用:框选整块(含表头)→ 点按钮 → 勾「首行是表头」→ 工具逐列自动判成金额 / 单号 / 跳过,判错可在下拉里改 → 选「写到右侧新列」(默认)或「写回原列」→ 看选中列预览 → 执行。
  • 选项:「万 / 亿 / 千」换算成元(不勾则整格不写);金额统一两位小数;身份证尾 x 统一大写。
  • 结果与注意:金额认 ¥ / ¥ / 元、千分位、括号负数、尾负号、全角数字;单号认发票号、银行账号、工号、身份证等,落表自动加撇号保住前导零。超过 15 位的数(如 19 位银行卡、20 位全电发票号)如果已经变成科学计数法,只标「丢位」不写——Excel 只存 15 位有效数字,后几位已经无法还原。公式格、隐藏/筛选行不动;写回原列需再确认。

文本日期统一 bg_dt1

  • 用途:把 2026.10.31、2026年10月31日、20261031、10/31/2026 这些文字日期认成真日期,并统一显示格式。
  • 怎么用:框选日期列 → 点按钮 → 选歧义日期的理解方式(月/日谁在前)→ 选输出格式 → 选写到右边或覆盖原列 → 看预览 → 执行。
  • 结果与注意:只要月/日有一边大于 12 就自动判定;两边都大于 12 直接报错、不猜。本来已是日期的格也可顺手统一显示格式。公式格不动。

系统数据清洗 bg_sc1

  • 用途:处理从业务系统整块导出的表:按列自动判成日期 / 数字 / 文本 / 跳过,用户可以逐列改,一次把整表类型理顺。
  • 怎么用:框选整块表 → 点按钮 → 勾「首行是表头」(如适用)→ 在列表里逐列确认或修改处理方式 → 看选中列前 100 行预览 → 选结果位置 → 执行。
  • 结果与注意:默认结果放新位置,原表保留;全选「跳过」或全是空格时会提前拦下;自动判错可以在列表里逐列改。