excel 按指定次数重复罗列文本 -j9九游会真人游戏第一

excel 按指定次数重复罗列文本

秋叶excel 2025/2/23 11:45:12 责编：梦泽评论：0

原文标题：《这个表格整理技巧真牛 x，谁总结的，太精辟！》

在《台风图》一文中，为了构建绘图省份类别列，小花使用了辅助列 lookup 的方法，实现了按指定次数重复罗列文本的效果。

如下图：

excel 按指定次数重复罗列文本

g2 公式如下：

=lookup(row(a1)*50,f:f,a:a)

它实际上利用了 lookup 模糊查询的原理，稍加简化下，可能更方便理解。

1、辅助列法

e2 公式如下：

=lookup(row()-1,c:c,a:a)

excel 按指定次数重复罗列文本

公式原理说明：

需要理解的是，省份值按指定次数重复罗列后，是一组包含 21 个值的有序数列 a {"广东";"广东";"广东";"广东";"广东";"台湾";"台湾";"台湾";"福建";"福建";"福建";"海南";"海南";"海南";"浙江";"广西";"山东";"香港";"上海";"辽宁";"江苏"}，我们需要的是将这组数列按次序索引至 g2:g22 单元格区域中。

excel 按指定次数重复罗列文本

c 列为 1 重复次数累计求和，不难发现，每个 c 列值刚好是其所在行 a 列省份在有序数列 a 中首次出现的位置，换言之，从 c2 值（含）到 c3 值（不含），有序数列 a 对应位置均为 a2 省份值，从 c3 值（含）到 c4 值（不含），有序数列 a 对应位置均为 a3 省份值，以此类推。

excel 按指定次数重复罗列文本

于是，指定次数重复罗列问题就被转化为，查询每个序数在 c 列所在区间，再返回对应 a 列值的典型模糊查询问题。

此时，用 row (a1)-1 来获取有序数值，再使用 lookup 模糊查找即可。

因为 lookup 匹配规则为返回不大于且最接近于查询值所在位置对应的结果值。

按指定次数重复罗列是常见的 excel 实战问题，这一问题有多种公式解法。

除了上述的辅助列法外，还有 offset 法、textsplit 法、tocol 法等 6 种方法。

以下，我们将逐一解读。

2、offset 法

有时候通过构建内含数组取代辅助列会使公式变得复杂且高深，但其使用价值却不容置喙。

上例中的辅助列就可以使用数组的方式直接构建。

数组法-——offset 内含数组：

=lookup(row()-2,sumif(offset($b$1,,,row($a$2:$a$12)-1),">0"),$a$2:$a$12)

excel 按指定次数重复罗列文本

公式说明：

该公式计算原理与辅助列法完全相同，区别仅仅是利用 sumif offset 函数构建虚拟的辅助列而已。

同时，由于 lookup 自带数组运算，该公式无需按【ctrl shift enter】数组运算组合键也能正确计算。

3、textsplit 法

对于 wps 和 office 365 的使用者来说，使用新函数 textsplit 不失为指定次数重复罗列问题的高效解法。

textsplit 法-——concat rept 连结：

=textsplit(concat(rept($a$2:$a$8&",",$b$2:$b$8)),,",",true)

excel 按指定次数重复罗列文本

公式说明：

rept 函数分别将 a2:a8&"," 按指定次数重复后；

由 concat 函数将这些重复文本连结后，就形成了一个按指定次数重复、"," 间隔的字符串；

再由 textsplit 函数将这个字符串按 "," 拆分成不同的行，这就是实现了按指定次数重复罗列。

4、mid 法

当然，如果需要重复的文本是固定长度的，那么，早期版本 excel 使用者也可以用 mid 函数来替代 textsplit 函数。

mid 法 ——concat rept 连结：

{=mid(concat(rept($a$2:$a$8,$b$2:$b$8)),row()*2-3,2)}

excel 按指定次数重复罗列文本

公式说明：

concat rept 函数将 a2:a8 按指定次数重复并连结成字符串后，由 mid 按固定的字符长度分段提取文本。

5、tocol 法

除了 textsplit 函数外，office 365 中的另一个新函数也可以用来处理指定次数重复罗列问题，它就是 tocol 函数。

tocol 法-——if 矩阵判断：

=tocol(if($b$2:$b$8>=column(a:e),$a$2:$a$8,na()),2,)

excel 按指定次数重复罗列文本

公式说明：

判断 b2:b8 是否大于 column (a:e)，将返回一组逻辑值组成的矩阵，if 函数的作用是根据矩阵中的逻辑值 true 赋值为 a2:a8 对应值，逻辑值 false 赋值为错误值#n / a。

最后，再使用 tocol 将矩阵转换为一列，同时忽略错误值。

excel 按指定次数重复罗列文本

6、small 法

早期版本的 excel 用户也可以按逻辑值矩阵的思路、运用 index small if 数组公式来解决指定次数重复罗列问题。

small 法-——if 矩阵判断：

{=index(a:a,small(if($b$2:$b$8>=column(a:f),row($2:$8),100),row()-1))}

excel 按指定次数重复罗列文本

公式说明：

公式原理类似 tocol 法，只是 if_false 需赋值为 100，从而确保 small 计算准确。

该公式也可以用 large 替换 small，此时，if_false 需赋值为 0。

7、countif 法

与有序数列模糊索引、重复字符串连结拆分、逻辑矩阵赋值的思路不同，countif 法另辟蹊径，通过动态计数结果来判断下一单元格的值，当某一要重复的值到达重复次数后，下一个值就开始被重复。

与本文的其他公式不同，任何上方单元格公式的计算结果都会对当前单元格产生影响，它们彼此间是递推关系，而非传统的独立关系。

countif 法-——if 矩阵判断：

{=index($a$2:$a$8,sum(--(countif($d$1:d1,$a$2:$a$8)=$b$2:$b$8)) 1)}

excel 按指定次数重复罗列文本

公式说明：

❶ countif ($d$1:d1,$a$2:$a$8)：按省份分别统计已重复的次数；

❷ --(❶=$b$2:$b$8)：判断各省份已重复次数和应重复次数是否一致，并将逻辑值转化为数字 1 和 0；

❸ sum (❷) 1：已经按指定次数重复的省份个数 1，即为本单元格需要重复罗列的单元格在 a2:a8 的序数值

❹ index ($a$2:$a$8,❸)：根据索引值返回最终结果。

8、写在最后

以上，就是小花分享的 6 种指定次数重复罗列公式，主要包含 4 种不同的思路：

❶ 有序数列模糊索引 —— 辅助列法和 offset 法。

❷ 重复字符串连结拆分 ——textsplit 和 mid 法。

❸ 逻辑判断矩阵赋值 ——tocol 法和 small 法。

❹ 分类计数动态递推 ——countif 法。

本文来自微信公众号：秋叶 excel（id：excel100），作者：小花