需求：如下图所示，将A1:A4单元格内的文本按B列数量在D列依次生成
本人用的是WPS,未装Excel365版,很多新公式使用不了,现介绍几种比较常用的解决方法。
方法一：辅助列法
如下图,在C列建一个辅助列,复制B列第1个数量到C1单元格,C2=C1 B2,向下填充,即可将B列数量进行累加,得到文本在D列填充后对应的最后一行.例如文本A在D列最后的位置为第3行，文本D最后的位置在第8行
D1=IFERROR(INDEX(A:A,XMATCH(ROW(),$C$1:$C$4,1)),""),下拉填充
用XMATCH函数,查找当前行应该填入的文本的最大行号,以D1为例,当前行为1,在C列进行查找比1大的第1个值,也就是3,再返回其对应的文本A;以D4为例,当前行为4,在C列查找比4大的第1个值,也就是5,对应的文本即为B,依此类推,最后套一个IFERROR函数容错.
如果没有XMATCH函数,可以用=IFERROR(INDEX(A:A,MATCH(1=1,$C$1:$C$4>=ROW(),)),"")代替,但这是数组公式,在D1输入公式后需要按CTRL SHIFT ENTER
方法二：万金油公式(small if row)
D1=IFERROR(INDEX($A$1:$A$4,SMALL(IF($B$1:$B$4>=COLUMN(A:F),ROW($B$1:$B$4)),ROW(A1))),"")
此为数组公式，非Excel365版需在D1输入公式后按CTRL SHIFT ENTER，下拉直至出现空单元格
原理：$B$1:$B$4>=COLUMN(A:F)这一步的结果图如下：
COLUMN(A:F)生成数字1-6的横向数组，分别与B1:B4单元格内待生成的数量进行比较，返回true或者false，由图可见，结果将按照数量在各行内生成相应数量的true
IF($B$1:$B$4>=COLUMN(A:F),ROW($B$1:$B$4))根据上一步返回的true和false，返回数量所对应的行号，结果如下：
文本A对应的数量为3，所在行号为1，这一步返回了3个1；B对应的数量为2，所在行号为2，这一步返回了2个2……最终将文本对应的行数生成了指定数量，如果将这一步返回的结果按从小到大排列，将返回一个数组[1，1，1，2，2，3，4，4，false,false,false……]。
最后，配合small row，可以依次提取需要的行，然后用Index函数返回对应需要填充的文本即可，OVER~
注意：如果需要生成的数量较大，例如文本A生成数量为10，那么公式中的COLUMN(A:F）中的范围需要修改，保证列数大于10即可，例如可以修改为A:J，A:Z都可以，否则会出错。
方法三：先组合文本再拆分
D1=TRIM(MID(SUBSTITUTE(CONCAT(REPT($A$1:$A$4&",",$B$1:$B$4)),",",REPT(" ",99)),ROW(A1)*99-98,99))
此为数组公式，非Excel365版需在D1输入公式后按CTRL SHIFT ENTER，下拉直至出现空单元格
原理：用rept函数将A1:A4单元格内的文本加上”,”号重复指定次数，然后合并为一个字符串”A,A,A,B,B,C,D,D,”最后用替换,号为99个空格后依次提取,然后再清除空格,得到想要的结果;因此方法比较简单,不再细述
方法四：subtotal xlookup
D1=XLOOKUP(ROW(),SUBTOTAL(9,OFFSET($B$1,,,ROW($1:$4))),$A$1:$A$4,"",1)
原理：SUBTOTAL(9,OFFSET($B$1,,,ROW($1:$4)))利用subtotal函数能直接对多维数组进行计算的特性,对B列的数量进行累加,返回文本对应将要填充的最后一行的行号,这一步生成的结果为一个数组[3,5,6,8](其实就是将方法一省略了辅助列),然后利用xlookup函数可以精确匹配或者查找比匹配值较大的值即可获得结果。
评论 (0)