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

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

秋叶Excel 2025/2/23 11:45:12 责编：梦泽评论：6 原文标题：《这个表格整理技巧真牛 X，谁总结的，太精辟！》  在《台风图》一文中，为了构建绘图省份类别列，小花使用了辅助列 + LOOKUP 的方法，实现了按指定次数重复罗列文本的效果。  如下图：    G2 公式如下：  =LOOKUP(ROW(A1)*50,F:F,A:A) 它实际上利用了 LOOKUP 模糊查询的原理，稍加简化下，可能更方便理解。  1、辅助列法 E2 公式如下：  =LOOKUP(ROW()-1,C:C,A:A)   公式原理说明：  需要理解的是，省份值按指定次数重复罗列后，是一组包含 21 个值的有序数列 A {"广东";"广东";"广东";"广东";"广东";"台湾";"台湾";"台湾";"福建";"福建";"福建";"海南";"海南";"海南";"浙江";"广西";"山东";"香港";"上海";"辽宁";"江苏"}，我们需要的是将这组数列按次序索引至 G2:G22 单元格区域中。    C 列为 1 + 重复次数累计求和，不难发现，每个 C 列值刚好是其所在行 A 列省份在有序数列 A 中首次出现的位置，换言之，从 C2 值（含）到 C3 值（不含），有序数列 A 对应位置均为 A2 省份值，从 C3 值（含）到 C4 值（不含），有序数列 A 对应位置均为 A3 省份值，以此类推。    于是，指定次数重复罗列问题就被转化为，查询每个序数在 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)   公式说明：  该公式计算原理与辅助列法完全相同，区别仅仅是利用 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)   公式说明：  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)}   公式说明：  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,)   公式说明：  判断 B2:B8 是否大于 COLUMN (A:E)，将返回一组逻辑值组成的矩阵，IF 函数的作用是根据矩阵中的逻辑值 TRUE 赋值为 A2:A8 对应值，逻辑值 FALSE 赋值为错误值#N / A。  最后，再使用 TOCOL 将矩阵转换为一列，同时忽略错误值。    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))}   公式说明：  公式原理类似 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)}   公式说明：  ❶ 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），作者：小花