原文標(biāo)題：《這個(gè)表格整理技巧真牛 X，誰總結(jié)的，太精辟！》
在《臺(tái)風(fēng)圖》一文中，為了構(gòu)建繪圖省份類別列，小花使用了輔助列 + LOOKUP 的方法，實(shí)現(xiàn)了按指定次數(shù)重復(fù)羅列文本的效果。
如下圖：
G2 公式如下：
=LOOKUP(ROW(A1)*50,F:F,A:A)
它實(shí)際上利用了 LOOKUP 模糊查詢的原理，稍加簡化下，可能更方便理解。
1、輔助列法
E2 公式如下：
=LOOKUP(ROW()-1,C:C,A:A)
公式原理說明：
需要理解的是，省份值按指定次數(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 對應(yīng)位置均為 A2 省份值，從 C3 值（含）到 C4 值（不含），有序數(shù)列 A 對應(yīng)位置均為 A3 省份值，以此類推。
于是，指定次數(shù)重復(fù)羅列問題就被轉(zhuǎn)化為，查詢每個(gè)序數(shù)在 C 列所在區(qū)間，再返回對應(yīng) A 列值的典型模糊查詢問題。
此時(shí)，用 ROW (A1)-1 來獲取有序數(shù)值，再使用 LOOKUP 模糊查找即可。
因?yàn)?LOOKUP 匹配規(guī)則為返回不大于且最接近于查詢值所在位置對應(yīng)的結(jié)果值。
按指定次數(shù)重復(fù)羅列是常見的 Excel 實(shí)戰(zhàn)問題，這一問題有多種公式解法。
除了上述的輔助列法外，還有 OFFSET 法、TEXTSPLIT 法、TOCOL 法等 6 種方法。
以下，我們將逐一解讀。
2、OFFSET 法
有時(shí)候通過構(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)
公式說明：
該公式計(jì)算原理與輔助列法完全相同，區(qū)別僅僅是利用 SUMIF+OFFSET 函數(shù)構(gòu)建虛擬的輔助列而已。
同時(shí)，由于 LOOKUP 自帶數(shù)組運(yùn)算，該公式無需按【Ctrl+Shift+Enter】數(shù)組運(yùn)算組合鍵也能正確計(jì)算。
3、TEXTSPLIT 法
對于 WPS 和 OFFICE 365 的使用者來說，使用新函數(shù) TEXTSPLIT 不失為指定次數(shù)重復(fù)羅列問題的高效解法。
TEXTSPLIT 法-——CONCAT+REPT 連結(jié)：
=TEXTSPLIT(CONCAT(REPT($A$2:$A$8&",",$B$2:$B$8)),,",",TRUE)
公式說明：
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ù)的文本是固定長度的，那么，早期版本 Excel 使用者也可以用 MID 函數(shù)來替代 TEXTSPLIT 函數(shù)。
MID 法 ——CONCAT+REPT 連結(jié)：
{=MID(CONCAT(REPT($A$2:$A$8,$B$2:$B$8)),ROW()*2-3,2)}
公式說明：
CONCAT+REPT 函數(shù)將 A2:A8 按指定次數(shù)重復(fù)并連結(jié)成字符串后，由 MID 按固定的字符長度分段提取文本。
5、TOCOL 法
除了 TEXTSPLIT 函數(shù)外，OFFICE 365 中的另一個(gè)新函數(shù)也可以用來處理指定次數(shù)重復(fù)羅列問題，它就是 TOCOL 函數(shù)。
TOCOL 法-——IF 矩陣判斷：
=TOCOL(IF($B$2:$B$8>=COLUMN(A:E),$A$2:$A$8,NA()),2,)
公式說明：
判斷 B2:B8 是否大于 COLUMN (A:E)，將返回一組邏輯值組成的矩陣，IF 函數(shù)的作用是根據(jù)矩陣中的邏輯值 TRUE 賦值為 A2:A8 對應(yīng)值，邏輯值 FALSE 賦值為錯(cuò)誤值#N / A。
最后，再使用 TOCOL 將矩陣轉(zhuǎn)換為一列，同時(shí)忽略錯(cuò)誤值。
6、SMALL 法
早期版本的 Excel 用戶也可以按邏輯值矩陣的思路、運(yùn)用 INDEX+SMALL+IF 數(shù)組公式來解決指定次數(shù)重復(fù)羅列問題。
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 計(jì)算準(zhǔn)確。
該公式也可以用 LARGE 替換 SMALL，此時(shí)，IF_FALSE 需賦值為 0。
7、COUNTIF 法
與有序數(shù)列模糊索引、重復(fù)字符串連結(jié)拆分、邏輯矩陣賦值的思路不同，COUNTIF 法另辟蹊徑，通過動(dòng)態(tài)計(jì)數(shù)結(jié)果來判斷下一單元格的值，當(dāng)某一要重復(fù)的值到達(dá)重復(fù)次數(shù)后，下一個(gè)值就開始被重復(fù)。
與本文的其他公式不同，任何上方單元格公式的計(jì)算結(jié)果都會(huì)對當(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)}
公式說明：
? 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 法。
本文來自微信公眾號：秋葉 Excel（ID：excel100），作者：小花
廣告聲明：文內(nèi)含有的對外跳轉(zhuǎn)鏈接（包括不限于超鏈接、二維碼、口令等形式），用于傳遞更多信息，節(jié)省甄選時(shí)間，結(jié)果僅供參考，IT之家所有文章均包含本聲明。