ARTICLE · 1385807
每日 Excel 小技巧|第19期:排序,自定义序列与辅助列排序
每日 Excel 小技巧|第19期:排序,自定义序列与辅助列排序哈喽大家好,这里是【每天学习一个 Excel 小技巧】专栏。 前两期我们学了常规排序和按格式排序。但还有一种情况,这两个都搞不定:数据本身是文字,而且它的 "正确顺序" 跟拼音、笔画都没关系。比如 "初级、中级、高级、专家",按拼音排是 "初级→高级→中级→专家",完全不对;你要的是按能力等级排。 这种 "我自己定义顺序" 的需求,就靠自定义序列;再复杂一点,就靠辅助列。这一期把两个工具讲透。
先看 Excel 自带的,打开排序对话框就能看到。 
操作路径 选中区域 →【数据】→【排序】 【次序】下拉点开 → 拉到最下面,能看到【自定义序列…】 里面有 Excel 内置的几组:星期(日、一、二… 六)、月份(一月、二月…)、天干地支(甲、乙、丙…) 选一个,确定 → 数据立刻按这个顺序排好 内置序列有个重要特点:不可修改。 你可以 "使用" 它,但不能改它的顺序、不能改它的内容,也不能删 想按自己需要的顺序排,只能新建一条自己的序列 ✅实用场景:一列 "星期几",按普通拼音 / 笔画排会变成 "六、日、四、五、一" 这种乱序,用内置的 "星期" 序列,立刻就变成 "日、一、二… 六",特别规整。
场景:一列数据写着 "初级、中级、高级、专家",你要它们按这个等级顺序排。 第 1 步:打开 "编辑自定义列表" 【文件】→【选项】 左侧选【高级】→ 往下拉到【常规】分组 找到【编辑自定义列表…】按钮,点开 弹出 "自定义序列" 窗口,左侧是序列清单,右侧是 "列表条目" ⚠️注意:这个窗口里,内置序列(星期、月份)选中后,右侧的编辑框是灰色不可改的—— 这就是前面说的 "不可修改"。想加自己的,必须先点【新序列】。 第 2 步:新建序列并录入 点右侧中间【新序列】(或 "添加") 左侧清单底部出现 "新序列" 这个名字,可以直接改成 "等级序列" 光标点进右侧【列表条目】框,一行输一个: 初级 中级 高级 专家 点【添加】(如果是修改已有序列就点【确定】) 点【确定】关闭窗口 第 3 步:用新序列排序 选中数据区 →【数据】→【排序】 【主要关键字】选 "等级" 列 【次序】下拉 → 拉到最底 →【自定义序列…】→ 选中 "等级序列" 确定 👉结果:初级 → 中级 → 高级 → 专家,正好是你定义的顺序。
方法 1:导入工作表里已有的列表(推荐!) 如果你表里已经有一列写好了 "初级 中级 高级 专家",不用手打。 先在表格里选中这一列区域 (比如 A2:A5,注意不带表头) 【文件】→【选项】→【高级】→【编辑自定义列表】 点右上角【导入】,Excel 会自动把刚才选中的区域读进来 点【确定】保存 👉这个动作的关键是 "先选中,再打开窗口",顺序反了就导不进去。 方法 2:从文本文件导入 把顺序整理成一个 txt 文件,一行一个词 在 "自定义序列" 窗口点【导入】→ 选那个 txt 方法 3:用已有序列复制改造 选中 "星期",先【新序列】建一条,再手动改内容。不能直接改内置的,但可以先建再抄。 ⚠️三个必须知道的限制: 自定义序列存在 "你这台电脑的这个 Excel 账号" 里 换电脑、换账号、发给同事的文件里,都不会带过去。 序列内容不能有重复项,也不能以空格开头。 序列建好之后,新序列和旧序列的顺序调整,要在 "自定义序列" 窗口里用左右箭头按钮(不在排序对话框里改)。
自定义序列只能处理 "有限的、固定的几个词"。遇到更复杂的情况,就要请出辅助列——先造一列能排序的依据,再按这一列排。 实例 A:用 MATCH 按 "指定人名顺序" 排(最经典) 场景:一张分工表,你想按 "张三、李四、王五、赵六" 这个特定顺序排,但这个顺序和拼音、笔画都不一致。 操作步骤 在数据右侧插入辅助列,标题写 "顺序号" 假设姓名在 A 列,A2 起是数据,辅助列 B2 输入: =MATCH(A2,{"张三","李四","王五","赵六"},0) 下拉到底 结果:张三 = 1、李四 = 2、王五 = 3、赵六 = 4,(表里没有的人返回#N/A) 选中整片区域 →【数据】→【排序】→ 主要关键字选 "顺序号"、次序升序 排完把辅助列删掉 👉原理:MATCH 返回 "这个名字在名单里排第几",把它变成数字,数字就能排序了。 ✅优势:名单可以直接写在公式里,也可以引用一张 "人员顺序表",都能用。 实例 B:带单位的数字排序("5 台""12 台 " 排出来是 5、12 吗?) 场景:数量列写的是 5台、12台、100台,直接排会变成 100、12、5 或者顺序全乱。 操作步骤 插入辅助列,用公式把数字提出来: =VALUE(SUBSTITUTE(A2,"台","")) 下拉填充 按这个纯数字辅助列升序排 排完删辅助列 ✅同理可换:SUBSTITUTE 里换成 "元"、"个"、"小时" 都能用。 实例 C:按 "周次 / 季度" 这种中文顺序排 第1周、第2周…第10周 先提取数字(=VALUE(SUBSTITUTE(SUBSTITUTE(A2,"第",""),"周",""))),按数字排 一季度、二季度… 用自定义序列最省事(四个词固定) 实例 D:多条件组合排(先按部门、再按金额) 辅助列 1:=MATCH(部门格,{"生产部","销售部","行政部"},0) → 部门变成 1/2/3 辅助列 2:直接引用金额列 排序时第 1 关键字选辅助列 1,第 2 关键字选辅助列 2
自定义序列不跟着文件走 发给同事的文件里,他自己点排序看不到你的序列。要保证对方也能排,就用辅助列 + MATCH的方式,公式会跟着文件。 导入时忘记先选中区域:导入按钮点了没反应。 序列里有空格或空行:排序时会出现 "排不出来" 的怪现象,建序列时别在末尾多敲回车。 辅助列排完记得删或者 "选择性粘贴为值" 再删掉公式(否则源数据一变,顺序又乱了)。 辅助列别用文字造顺序:(比如 ="1张三"),用纯数字最稳。
📌本期一句话速记(收藏) 表格
内置序列(星期、月份)不可修改,只能新建自己的 自定义序列只在本机本账号有效 辅助列的核心:把 "不能排的规则" 翻译成 "能排的数字" 排完删辅助列,或用 "粘贴为值"
📌本专栏:每天学习一个 Excel 小技巧 从基础格式、函数到数据透视、报表自动化,一步步带你吃透 Excel,适合职场、台账、质检报表使用。 👉点个关注不迷路,跟着我每天花十分钟,把别人的加班时间省下来✨ 💬你有没有遇到过 "按姓名根本排不出想要顺序" 的表?评论区说说,我教你用辅助列拆开。
1-1 认识自定义序列:Excel 本来就内置了一批

1-2 建立自己的自定义序列(手动录入)
1-3 三种 "少打字" 的建序列方法
适合序列很长(几十上百个)的情况,比如门店名单、产品线清单
1-4 辅助列排序:任何规则都能排
也可以直接在两列上各设一级关键字,不必造辅助列 —— 辅助列的意义在于 "把规则数字化的过程".















































































