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

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），作者：小花