【函数练习】生成A列没有出现的不重复随机数

太阳之子

mmlzh

来个火车
区域=MOD(SMALL(IF((MMULT(N(MID(ROW(1:500),{1,2,3},1)="4"),{1;1;1})=0)*(COUNTIF(A:A,ROW(1:500))=0)*FREQUENCY(ROW(INDIRECT(E3&":"&F3)),ROW(1:499)),RANDBETWEEN(ROW(1:500)^0,999)/1%%+ROW(1:500)),ROW(1:20)),10^4)

sbdk007

也来开个火车
=MOD(SMALL(IF((COUNTIF(A2:A8,ROW(1:999))=0)*(MMULT(N(MID(ROW(1:999),{1,2,3},1)="4"),{1;1;1})=0)*(ROW(1:999)>=E3)*(ROW(1:999)<=F3),RANDBETWEEN(ROW(1:999)^0,999)/1%%+ROW(1:999)),ROW(1:20)),10^4)

魚。。

凑凑热闹：好像都差不多
=MOD(SMALL(IF((ROW(1:520)>=E3)*(ROW(1:520)<=F3)*(COUNTIF(A:A,ROW(1:520))=0)*(SUBSTITUTE(ROW(1:520),4,)=ROW(1:520)&""),RANDBETWEEN(-V1:V520,1314)/1%%+ROW(1:520)),ROW(1:20)),10^4)

魚。。

话说为什么用这个会有重复的
ISERR(FIND(4,ROW(1:520)))

我走我流

来个区域数组，超级大火车，思路是先求出数组，然后取此数组的随机20个

=SMALL(IF(ISNA(MATCH(ROW(INDIRECT(E3&":"&F3)),A2:A8,))*ISERR(FIND(4,ROW(INDIRECT(E3&":"&F3)))),ROW(INDIRECT(E3&":"&F3))),MOD(SMALL(RANDBETWEEN(ROW(INDIRECT("1:"&F3-E3-COUNT(FIND(4,ROW(INDIRECT(E3&":"&F3))))-6))^0,F3-E3-COUNT(FIND(4,ROW(INDIRECT(E3&":"&F3))))-6)/1%%+ROW(INDIRECT("1:"&F3-E3-COUNT(FIND(4,ROW(INDIRECT(E3&":"&F3))))-6)),ROW(1:20)),10^4))原公式太长，稍微精简下

准提部林

數組:
=-LOOKUP(1,LARGE(IF(ISNA(RANK(--SUBSTITUTE(ROW(INDIRECT(E$3&":"&F$3)),4,"u"),(A:A,C$1:C1))),-ROW(INDIRECT(E$3&":"&F$3))),MOD(RAND()*99+ROW($1:$99),99)))

准提部林

數組:
一樣投機, 也減不了幾個字:
=E$3-1-LOOKUP(,-MATCH(501+RAND()*250-ROW($1:$500),ROW($1:$500)/ISNA(RANK(--SUBSTITUTE(ROW(INDIRECT(E$3&":"&F$3)),4,"u"),(A:A,C$1:C1)))))

micch

1. =MOD(AGGREGATE(15,6,RANDBETWEEN(ROW(1:500)^0,999)/1%%+TEXT(SUBSTITUTE(ROW(1:500),4,666)+FREQUENCY(A:A,ROW(1:499))/1%%,"[<"&E3&"]w;[<"&F3&"]0;w"),ROW()),1000)

复制代码

瞎写一个吧

micch

1. =MOD(AGGREGATE(15,6,RANDBETWEEN(-Z1:Z500,999)/1%%+TEXT(SUBSTITUTE(FREQUENCY(A:A,ROW(1:499))*4&ROW(1:500),4,666),"[<"&E3&"]w;[<"&F3&"]0;"),ROW()),1000)

复制代码

尴尬，写错了，换汤不换药
学习准提老师match试试看，不懂对错瞎试吧

1. =MATCH(RAND()*233+E$3+1,--TEXT(SUBSTITUTE(FREQUENCY((C$1:C1,A:A),ROW($1:$500))*4&ROW($1:$501),4,666),"[<"&E$3&"]w;[<"&F$3&"];"))

复制代码