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