夜雨聆风学习资料网

ARTICLE · 1385807

每日 Excel 小技巧|第19期:排序,自定义序列与辅助列排序

每日 Excel 小技巧|第19期:排序,自定义序列与辅助列排序
哈喽大家好,这里是【每天学习一个 Excel 小技巧】专栏。 前两期我们学了常规排序和按格式排序。但还有一种情况,这两个都搞不定:数据本身是文字,而且它的 "正确顺序" 跟拼音、笔画都没关系。比如 "初级、中级、高级、专家",按拼音排是 "初级→高级→中级→专家",完全不对;你要的是按能力等级排。 这种 "我自己定义顺序" 的需求,就靠自定义序列;再复杂一点,就靠辅助列。这一期把两个工具讲透。

1-1 认识自定义序列:Excel 本来就内置了一批

先看 Excel 自带的,打开排序对话框就能看到。
操作路径
选中区域 →【数据】→【排序】
【次序】下拉点开 → 拉到最下面,能看到【自定义序列…】
里面有 Excel 内置的几组:星期(日、一、二… 六)、月份(一月、二月…)、天干地支(甲、乙、丙…)
选一个,确定 → 数据立刻按这个顺序排好
内置序列有个重要特点:不可修改。
你可以 "使用" 它,但不能改它的顺序、不能改它的内容,也不能删
想按自己需要的顺序排,只能新建一条自己的序列
✅实用场景:一列 "星期几",按普通拼音 / 笔画排会变成 "六、日、四、五、一" 这种乱序,用内置的 "星期" 序列,立刻就变成 "日、一、二… 六",特别规整。

1-2 建立自己的自定义序列(手动录入)

场景:一列数据写着 "初级、中级、高级、专家",你要它们按这个等级顺序排。
第 1 步:打开 "编辑自定义列表"
【文件】→【选项】
左侧选【高级】→ 往下拉到【常规】分组
找到【编辑自定义列表…】按钮,点开
弹出 "自定义序列" 窗口,左侧是序列清单,右侧是 "列表条目"
⚠️注意:这个窗口里,内置序列(星期、月份)选中后,右侧的编辑框是灰色不可改的—— 这就是前面说的 "不可修改"。想加自己的,必须先点【新序列】。
第 2 步:新建序列并录入
点右侧中间【新序列】(或 "添加")
左侧清单底部出现 "新序列" 这个名字,可以直接改成 "等级序列"
光标点进右侧【列表条目】框,一行输一个:
初级
中级
高级
专家
点【添加】(如果是修改已有序列就点【确定】)
点【确定】关闭窗口
第 3 步:用新序列排序
选中数据区 →【数据】→【排序】
【主要关键字】选 "等级" 列
【次序】下拉 → 拉到最底 →【自定义序列…】→ 选中 "等级序列"
确定
👉结果:初级 → 中级 → 高级 → 专家,正好是你定义的顺序。

1-3 三种 "少打字" 的建序列方法

方法 1:导入工作表里已有的列表(推荐!)
如果你表里已经有一列写好了 "初级 中级 高级 专家",不用手打。
先在表格里选中这一列区域
(比如 A2:A5,注意不带表头)
【文件】→【选项】→【高级】→【编辑自定义列表】
点右上角【导入】,Excel 会自动把刚才选中的区域读进来
点【确定】保存
👉这个动作的关键是 "先选中,再打开窗口",顺序反了就导不进去。
方法 2:从文本文件导入
把顺序整理成一个 txt 文件,一行一个词
在 "自定义序列" 窗口点【导入】→ 选那个 txt

适合序列很长(几十上百个)的情况,比如门店名单、产品线清单

方法 3:用已有序列复制改造
选中 "星期",先【新序列】建一条,再手动改内容。不能直接改内置的,但可以先建再抄。
⚠️三个必须知道的限制:
自定义序列存在 "你这台电脑的这个 Excel 账号" 里
换电脑、换账号、发给同事的文件里,都不会带过去。
序列内容不能有重复项,也不能以空格开头。
序列建好之后,新序列和旧序列的顺序调整,要在 "自定义序列" 窗口里用左右箭头按钮(不在排序对话框里改)。

1-4 辅助列排序:任何规则都能排

自定义序列只能处理 "有限的、固定的几个词"。遇到更复杂的情况,就要请出辅助列——先造一列能排序的依据,再按这一列排。
实例 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

也可以直接在两列上各设一级关键字,不必造辅助列 —— 辅助列的意义在于 "把规则数字化的过程".


1-5 五个坑(避免白忙)

自定义序列不跟着文件走
发给同事的文件里,他自己点排序看不到你的序列。要保证对方也能排,就用辅助列 + MATCH的方式,公式会跟着文件。
导入时忘记先选中区域:导入按钮点了没反应。
序列里有空格或空行:排序时会出现 "排不出来" 的怪现象,建序列时别在末尾多敲回车。
辅助列排完记得删或者 "选择性粘贴为值" 再删掉公式(否则源数据一变,顺序又乱了)。
辅助列别用文字造顺序:(比如 ="1张三"),用纯数字最稳。

📌本期一句话速记(收藏)
表格
需求
工具
几个固定词按我想要的顺序
自定义序列
(文件→选项→高级→编辑自定义列表)
序列已经写在表里
先选中区域 → 再点【导入】
能排的顺序无法预先列出
辅助列 + MATCH
 造序号
带单位的数字
VALUE(SUBSTITUTE())
 提取数字
要发给别人也能排
用辅助列,不用自定义序列
内置序列(星期、月份)不可修改,只能新建自己的
自定义序列只在本机本账号有效
辅助列的核心:把 "不能排的规则" 翻译成 "能排的数字"
排完删辅助列,或用 "粘贴为值"

📌本专栏:每天学习一个 Excel 小技巧 从基础格式、函数到数据透视、报表自动化,一步步带你吃透 Excel,适合职场、台账、质检报表使用。
 👉点个关注不迷路,跟着我每天花十分钟,把别人的加班时间省下来✨ 💬你有没有遇到过 "按姓名根本排不出想要顺序" 的表?评论区说说,我教你用辅助列拆开。

回首页,搜索你想要的文档

在首页搜索框输入关键词,快速定位资料

文库首页搜索指引 立即搜索

相关学习资料