Excel 按指定次數(shù)重復(fù)羅列文本

Excel 按指定次數(shù)重復(fù)羅列文本 

秋葉Excel 2025/2/23 11:45:12 責(zé)編：夢(mèng)澤評(píng)論：6 原文標(biāo)題：《這個(gè)表格整理技巧真牛 X，誰(shuí)總結(jié)的，太精辟！》  在《臺(tái)風(fēng)圖》一文中，為了構(gòu)建繪圖省份類別列，小花使用了輔助列 + LOOKUP 的方法，實(shí)現(xiàn)了按指定次數(shù)重復(fù)羅列文本的效果。  如下圖：    G2 公式如下：  =LOOKUP(ROW(A1)*50,F:F,A:A) 它實(shí)際上利用了 LOOKUP 模糊查詢的原理，稍加簡(jiǎn)化下，可能更方便理解。  1、輔助列法 E2 公式如下：  =LOOKUP(ROW()-1,C:C,A:A)   公式原理說(shuō)明：  需要理解的是，省份值按指定次數(shù)重復(fù)羅列后，是一組包含 21 個(gè)值的有序數(shù)列 A {"廣東";"廣東";"廣東";"廣東";"廣東";"臺(tái)灣";"臺(tái)灣";"臺(tái)灣";"福建";"福建";"福建";"海南";"海南";"海南";"浙江";"廣西";"山東";"香港";"上海";"遼寧";"江蘇"}，我們需要的是將這組數(shù)列按次序索引至 G2:G22 單元格區(qū)域中。    C 列為 1 + 重復(fù)次數(shù)累計(jì)求和，不難發(fā)現(xiàn)，每個(gè) C 列值剛好是其所在行 A 列省份在有序數(shù)列 A 中首次出現(xiàn)的位置，換言之，從 C2 值（含）到 C3 值（不含），有序數(shù)列 A 對(duì)應(yīng)位置均為 A2 省份值，從 C3 值（含）到 C4 值（不含），有序數(shù)列 A 對(duì)應(yīng)位置均為 A3 省份值，以此類推。    于是，指定次數(shù)重復(fù)羅列問(wèn)題就被轉(zhuǎn)化為，查詢每個(gè)序數(shù)在 C 列所在區(qū)間，再返回對(duì)應(yīng) A 列值的典型模糊查詢問(wèn)題。  此時(shí)，用 ROW (A1)-1 來(lái)獲取有序數(shù)值，再使用 LOOKUP 模糊查找即可。  因?yàn)?LOOKUP 匹配規(guī)則為返回不大于且最接近于查詢值所在位置對(duì)應(yīng)的結(jié)果值。  按指定次數(shù)重復(fù)羅列是常見(jiàn)的 Excel 實(shí)戰(zhàn)問(wèn)題，這一問(wèn)題有多種公式解法。  除了上述的輔助列法外，還有 OFFSET 法、TEXTSPLIT 法、TOCOL 法等 6 種方法。  以下，我們將逐一解讀。  2、OFFSET 法 有時(shí)候通過(guò)構(gòu)建內(nèi)含數(shù)組取代輔助列會(huì)使公式變得復(fù)雜且高深，但其使用價(jià)值卻不容置喙。  上例中的輔助列就可以使用數(shù)組的方式直接構(gòu)建。  數(shù)組法-——OFFSET 內(nèi)含數(shù)組：  =LOOKUP(ROW()-2,SUMIF(OFFSET($B$1,,,ROW($A$2:$A$12)-1),">0"),$A$2:$A$12)   公式說(shuō)明：  該公式計(jì)算原理與輔助列法完全相同，區(qū)別僅僅是利用 SUMIF+OFFSET 函數(shù)構(gòu)建虛擬的輔助列而已。  同時(shí)，由于 LOOKUP 自帶數(shù)組運(yùn)算，該公式無(wú)需按【Ctrl+Shift+Enter】數(shù)組運(yùn)算組合鍵也能正確計(jì)算。  3、TEXTSPLIT 法 對(duì)于 WPS 和 OFFICE 365 的使用者來(lái)說(shuō)，使用新函數(shù) TEXTSPLIT 不失為指定次數(shù)重復(fù)羅列問(wèn)題的高效解法。  TEXTSPLIT 法-——CONCAT+REPT 連結(jié)：  =TEXTSPLIT(CONCAT(REPT($A$2:$A$8&",",$B$2:$B$8)),,",",TRUE)   公式說(shuō)明：  REPT 函數(shù)分別將 A2:A8&"," 按指定次數(shù)重復(fù)后；  由 CONCAT 函數(shù)將這些重復(fù)文本連結(jié)后，就形成了一個(gè)按指定次數(shù)重復(fù)、"," 間隔的字符串；  再由 TEXTSPLIT 函數(shù)將這個(gè)字符串按 "," 拆分成不同的行，這就是實(shí)現(xiàn)了按指定次數(shù)重復(fù)羅列。  4、MID 法 當(dāng)然，如果需要重復(fù)的文本是固定長(zhǎng)度的，那么，早期版本 Excel 使用者也可以用 MID 函數(shù)來(lái)替代 TEXTSPLIT 函數(shù)。  MID 法 ——CONCAT+REPT 連結(jié)：  {=MID(CONCAT(REPT($A$2:$A$8,$B$2:$B$8)),ROW()*2-3,2)}   公式說(shuō)明：  CONCAT+REPT 函數(shù)將 A2:A8 按指定次數(shù)重復(fù)并連結(jié)成字符串后，由 MID 按固定的字符長(zhǎng)度分段提取文本。  5、TOCOL 法 除了 TEXTSPLIT 函數(shù)外，OFFICE 365 中的另一個(gè)新函數(shù)也可以用來(lái)處理指定次數(shù)重復(fù)羅列問(wèn)題，它就是 TOCOL 函數(shù)。  TOCOL 法-——IF 矩陣判斷：  =TOCOL(IF($B$2:$B$8>=COLUMN(A:E),$A$2:$A$8,NA()),2,)   公式說(shuō)明：  判斷 B2:B8 是否大于 COLUMN (A:E)，將返回一組邏輯值組成的矩陣，IF 函數(shù)的作用是根據(jù)矩陣中的邏輯值 TRUE 賦值為 A2:A8 對(duì)應(yīng)值，邏輯值 FALSE 賦值為錯(cuò)誤值#N / A。  最后，再使用 TOCOL 將矩陣轉(zhuǎn)換為一列，同時(shí)忽略錯(cuò)誤值。    6、SMALL 法 早期版本的 Excel 用戶也可以按邏輯值矩陣的思路、運(yùn)用 INDEX+SMALL+IF 數(shù)組公式來(lái)解決指定次數(shù)重復(fù)羅列問(wèn)題。  SMALL 法-——IF 矩陣判斷：  {=INDEX(A:A,SMALL(IF($B$2:$B$8>=COLUMN(A:F),ROW($2:$8),100),ROW()-1))}   公式說(shuō)明：  公式原理類似 TOCOL 法，只是 IF_FALSE 需賦值為 100，從而確保 SMALL 計(jì)算準(zhǔn)確。  該公式也可以用 LARGE 替換 SMALL，此時(shí)，IF_FALSE 需賦值為 0。  7、COUNTIF 法 與有序數(shù)列模糊索引、重復(fù)字符串連結(jié)拆分、邏輯矩陣賦值的思路不同，COUNTIF 法另辟蹊徑，通過(guò)動(dòng)態(tài)計(jì)數(shù)結(jié)果來(lái)判斷下一單元格的值，當(dāng)某一要重復(fù)的值到達(dá)重復(fù)次數(shù)后，下一個(gè)值就開(kāi)始被重復(fù)。  與本文的其他公式不同，任何上方單元格公式的計(jì)算結(jié)果都會(huì)對(duì)當(dāng)前單元格產(chǎn)生影響，它們彼此間是遞推關(guān)系，而非傳統(tǒng)的獨(dú)立關(guān)系。  COUNTIF 法-——IF 矩陣判斷：  {=INDEX($A$2:$A$8,SUM(--(COUNTIF($D$1:D1,$A$2:$A$8)=$B$2:$B$8))+1)}   公式說(shuō)明：  ? COUNTIF ($D$1:D1,$A$2:$A$8)：按省份分別統(tǒng)計(jì)已重復(fù)的次數(shù)；  ? --(?=$B$2:$B$8)：判斷各省份已重復(fù)次數(shù)和應(yīng)重復(fù)次數(shù)是否一致，并將邏輯值轉(zhuǎn)化為數(shù)字 1 和 0；  ? SUM (?)+1：已經(jīng)按指定次數(shù)重復(fù)的省份個(gè)數(shù) + 1，即為本單元格需要重復(fù)羅列的單元格在 A2:A8 的序數(shù)值  ? INDEX ($A$2:$A$8,?)：根據(jù)索引值返回最終結(jié)果。  8、寫在最后 以上，就是小花分享的 6 種指定次數(shù)重復(fù)羅列公式，主要包含 4 種不同的思路：  ? 有序數(shù)列模糊索引 —— 輔助列法和 OFFSET 法。  ? 重復(fù)字符串連結(jié)拆分 ——TEXTSPLIT 和 MID 法。  ? 邏輯判斷矩陣賦值 ——TOCOL 法和 SMALL 法。  ? 分類計(jì)數(shù)動(dòng)態(tài)遞推 ——COUNTIF 法。  本文來(lái)自微信公眾號(hào)：秋葉 Excel（ID：excel100），作者：小花