Excel情报局

用案例讲Excel

探索挖掘分享Excel实用技能
SuperExcelMan
用1%的Excel基础搞定99%的职场难题

做一个超级实用的Excel公众号

Excel是门手艺玩转需要勇气
数万表格爱好者聚集地
你是不是也经常在职场中遇到这样的头疼事…

所有数据都被硬塞进了一个单元格，就像下面这张图里的A3单元格：
——”c18,b9,c6,b1,a6,d1,a12″

每个单元格都是由“字母+数字”组成的编码，我们需要让它们先按字母从A到Z排列，紧接着；在相同字母的组内，数字部分也必须严格按照数值大小升序排列。在传统表格操作中：如果按照笨办法，你需要“分列”拆分、处理文本型数字、多列排序、最后再合并……步骤繁琐，极易出错。
这看似简单，实则是一个经典的Excel排序难题，或者你压根就不知道该怎么下手！



今天，小编只给你总结出一个实用的公式，就能让这个“字母优先，数字随后”的智能排序过程变的一气呵成。

其中出现了多个新函数，可谓是内容慢慢，收获慢慢：
LET
REGEXP
TEXTSPLIT
ARRAYTOTEXT
BYROW
SORTBY
TAKE
CONCAT

通过文本分拆、分层排序、重组文本，巧妙的将多个新函数组合，实现了复杂的逻辑。下面小编将公式拆解为五个核心步骤，具体讲解其作用原理。


第一步：拆分为独立元素

使用TEXTSPLIT拆分函数：

=TEXTSPLIT(A3,,”,”)

TEXTSPLIT函数以逗号为(行)分隔符，将A3的内容拆分成垂直数组。



第二步：提取字母与数字（关键步骤）

使用REGEXP正则公式提取模式：

=REGEXP(TEXTSPLIT(A3,,”,”),{“[A-z]+”,”d+”})

[A-z]+：匹配一个及以上的大小写字母。
d+：匹配一个及以上的数字。
花括号括起来，作为数组的两个元素：{“[A-z]+”,”d+”}：
表示分别提取字母和数字。

省略REGEXP函数第3参数，默认正则为“提取”模式。

REGEXP函数对每个元素，用正则表达式分别提取“字母部分”和“数字部分”，生成一个两列的数组。



结果被赋值给变量a：



套上LET公式函数的壳子：

=LET(a,

           REGEXP(TEXTSPLIT(A3,,”,”),{“[A-z]+”,”d+”}),

                 a参与的计算规则待定)

外层的LET函数让公式结构更清晰，将中间结果定义为变量a，可反复调用，避免了重复计算，也提升了可读性。

最后就是需要确定第3参数，即a需要参与的计算规则。

a需要参与的计算规则，可以概括为“双层排序”。对数组a进行排序，设定两个排序条件：

①即按照a的第1列（字母列）作为主要关键字升序排序，确保字母相同的数据分组放在一起，并按升序排序；

②然后以a第2列（数字列）作为次要关键字升序排序，确保字母相同的数据分组内数字也按升序排序。


告别辅助列！就用这一个公式->在同一单元格里的字母数字混合文本双层排序

第三步：执行双层排序


开始完善LET公式函数的第3参数：
=LET(a,REGEXP(TEXTSPLIT(A3,,”,”),{“[A-z]+”,”d+”}),SORTBY(a,TAKE(a,,1),1))

TAKE(a,,1)：
取数组a的第1列（字母列）。

排序依据1：
SORTBY(a,TAKE(a,,1),1)
按字母列升序排序。
继续完善LET公式函数的第3参数：
=LET(a,REGEXP(TEXTSPLIT(A3,,”,”),{“[A-z]+”,”d+”}),SORTBY(a,TAKE(a,,1),1,–TAKE(a,,-1),1))

–TAKE(a,,-1)：
取数组a的最后1列（数字列），–将其强制转换为数值。这是正确排序数字的关键(如 12>6)，否则会按文本排序(“12″<“6”)。

排序依据2：
SORTBY(a,TAKE(a,,1),1,–TAKE(a,,-1),1)


整体逻辑：先按字母排，字母相同的再按数字大小排。




第四步：合并字母与数字

继续完善LET公式函数的第3参数：
=LET(a,REGEXP(TEXTSPLIT(A3,,”,”),{“[A-z]+”,”d+”}),BYROW(SORTBY(a,TAKE(a,,1),1,–TAKE(a,,-1),1),CONCAT))

函数：BYROW(…, CONCAT)
作用：将排序后的两列数组逐行连接，合并回“字母+数字”的格式。



第五步：数组合并为文本

最后完善LET公式函数的第3参数为：
=LET(a,REGEXP(TEXTSPLIT(A3,,”,”),{“[A-z]+”,”d+”}),ARRAYTOTEXT(BYROW(SORTBY(a,TAKE(a,,1),1,–TAKE(a,,-1),1),CONCAT)))

函数：ARRAYTOTEXT(…)
作用：将垂直数组转换成一个用逗号和空格分隔的单一文本字符串，与源数据的格式保持一致。

至此，整个公式展现了Excel与WPS动态数组函数强大的数据处理能力，将过去需要多个辅助列才能完成的工作，浓缩成了一个公式。


学习Excel/如果你没有天赋/那就一直重复/当你快到本能反应的时候/你的重复就是别人眼中的天赋/冲破捆绑/展翅翱翔/回顾关键内容/善用图片表达/学会建立联系/拓展深度广度/浓缩关键概念/应用到行动中/善于归纳总结/尝试进行分享

map遍历scan遍历reduce迭代pivotby降维

pivotby函数byrow函数groupby函数let函数

对单元格部分文字内容替换 格式更改←

借助这个函数！合并工作表简直太好用←

比vlookup还强大的动态随机查询←

根据起始序号与终止序号写入内容←

regexp正则提取：款式&尺码多组提取←

一个关于人口普查Excel户籍整理的问题←

一个关于人口普查Excel户籍整理的问题2←

regexp+vlookup根据简称查询全称←

regexp+countif+filter根据全称查找简称←

excel1秒批处理1000条混乱时间数据←

Regexp这个大哥级函数人狠话不多←

公式里常出现的”@”、”#”、”.”是干啥的←