需求:如下图所示,将A1:A4单元格内的文本按B列数量在D列依次生成

wps表格中怎么自动统计出相同数量 WPS或者Excel中按指定数量生成内容(1)

本人用的是WPS,未装Excel365版,很多新公式使用不了,现介绍几种比较常用的解决方法

方法一:辅助列法

如下图,在C列建一个辅助列,复制B列第1个数量到C1单元格,C2=C1 B2,向下填充,即可将B列数量进行累加,得到文本在D列填充后对应的最后一行.例如文本A在D列最后的位置为第3行,文本D最后的位置在第8行

wps表格中怎么自动统计出相同数量 WPS或者Excel中按指定数量生成内容(2)

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)这一步的结果图如下:

wps表格中怎么自动统计出相同数量 WPS或者Excel中按指定数量生成内容(3)

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,返回数量所对应的行号,结果如下:

wps表格中怎么自动统计出相同数量 WPS或者Excel中按指定数量生成内容(4)

文本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函数可以精确匹配或者查找比匹配值较大的值即可获得结果

,