【原】這題會一種解的有80%；兩種的50%；仨全會的不到1.5%了！

EXCEL應用之家 2023-11-29 發(fā)布于上海

展開全文

送人玫瑰，手有余香，請將文章分享給更多朋友

動手操作是熟練掌握EXCEL的最快捷途徑！

【置頂公眾號】或者【設(shè)為星標】及時接收更新不迷路

小伙伴們好，今天來和大家分享一道有意思的題目。根據(jù)給定的數(shù)值，來拆分一個比較大的值，并生成多行數(shù)據(jù)。這個類型的題目在現(xiàn)實工作中是會遇到的，就讓我們一起來看看吧。

原題目是這樣子的：

題目要求以50為給定值，將左側(cè)的數(shù)據(jù)結(jié)構(gòu)變更為右側(cè)的結(jié)構(gòu)。實際上就是以50為一檔，連續(xù)來拆分左側(cè)的數(shù)據(jù)，直至剩余數(shù)據(jù)小于50為止。

先來看D列。

在單元格D2中輸入下列公式，三鍵回車并向下拖曳即可。

=INDEX(A$2:A$8,SUM(1,N(ROW(A1)>MMULT(N(ROW($1:$7)>=COLUMN($A:$G)),-INT(-$B$2:$B$8/50)))))

思路：

-INT(-$B$2:$B$8/50)部分，這個操作實現(xiàn)了向上取余。它等同于INT($B$2:$B$8/50)+1
N(ROW($1:$7)>=COLUMN($A:$G))部分，形成下面這樣一個結(jié)構(gòu)。

MMULT(N(ROW($1:$7)>=COLUMN($A:$G)),-INT(-$B$2:$B$8/50))部分，其結(jié)果為{2;4;6;9;15;16;17}，這個正是D列中字母個數(shù)逐次累加的結(jié)果
N(ROW(A1)>MMULT(N(ROW($1:$7)>=COLUMN($A:$G)),-INT(-$B$2:$B$8/50)))部分，再做一個比較，并通過N函數(shù)將TRUE轉(zhuǎn)換為1，F(xiàn)ALSE轉(zhuǎn)換為0
SUM(1,N(ROW(A1)>MMULT(N(ROW($1:$7)>=COLUMN($A:$G)),-INT(-$B$2:$B$8/50))))部分，隨著公式向下拖曳，ROW(A1)的值也逐漸增大。當在第1、2行時，N函數(shù)返回的結(jié)構(gòu)都是0，所以SUM函數(shù)結(jié)結(jié)果就是1；當在第3、4行時，由于3和4都大于2，所以N函數(shù)返回的結(jié)果中有一個1，因此SUM函數(shù)的結(jié)果就是2。后面的依次類推。這個這部分將作為INDEX函數(shù)的第二參數(shù)
最后，利用INDEX函數(shù)來返回正確的答案

再來看看E列。

在單元格E2中輸入下列公式，并向下拖曳即可。

=IF(COUNTIF(D$2:D2,D2)<COUNTIF(D:D,D2),50,VLOOKUP(D2,A:B,2,)-COUNTIF(D$1:D1,D2)*50)

由于這是一個簡單的IF函數(shù)邏輯判斷，這里就不再詳細講解了。朋友們?nèi)绻幸蓡柨梢运叫盼遗叮?/p>

下面我們試著用另一個技巧來解決這個問題。

在單元格D2中輸入下列公式，三鍵回車并向下拖曳即可。

=INDEX(A:A,SMALL(IF(INT($B$2:$B$8/50)+1<COLUMN(A:W),100,ROW($A$2:$A$8)),ROW(A1)))

思路：

IF(INT($B$2:$B$8/50)+1<COLUMN(A:W),100,ROW($A$2:$A$8))部分，INT函數(shù)取整后和COLUMN(A:W)比較。這個和上面介紹的的思路是一致的。利用IF函數(shù)返回不同的結(jié)果
SMALL函數(shù)依次提取第1、2、3..小的數(shù)值
INDEX函數(shù)返回正確答案。

這個技巧我們在以前的推文中有過多次的介紹了，如果朋友們還不是很清楚，可以參看相關(guān)的文章或者私信我。

下面來看E列。

在單元格E2中輸入下列公式，并向下拖曳即可。

=MIN(50,VLOOKUP(D2,A:B,2,)-COUNTIF(D$1:D1,D2)*50)

公式很簡單，不再贅述了。

最后給大家貼一個彩蛋，一條公式來完成這個題目。

在單元格D2中輸入下列公式，三鍵回車并向下拖曳即可。

=IFERROR(INDEX(IF({1,0},A:A,50-ROW(1:50)+1),MID(SMALL(IF(B2:B666>(COLUMN(A:W)-1)*50,ROW(10002:10666)/1%%+DDB(COLUMN(A:W)*50,B2:B666,1,1)+1),ROW(1:888)),{2,6},4),{1,2}),"")