关于Excel随机取值,老司机教你多种解决技巧

2017-06-06 22:26 | 发布者: | 查看: 536 | 评论: 0

简介: 所谓随机取值,顾名思义就是从一堆数里,随机抽取几个或几组数;常用于抽奖、抽查、数据随机分组等。 举个栗子,如下图所示,A2:A21是EH培训学院的20名……男女。现在需要从中随机抽取3人,授予38妇女红旗手的称号,需要如何操作才能实现呢,下面我们来操作。 解法1:辅助列法 使用辅助列,解决此类问题,应是最简单较快捷的一种解法,基本上老少咸宜,稍有函数公式基础就可以掌握了。 首先将C列作为辅助列,C2输入以下公式,并复制填充至C2:C21区域。 =RAND() RAND函数生成大于等于0、小于1的随机数。 其次,在E2单元格输入以下公式,复制填充至F2:F4区域即可得到抽奖结果了。 =INDEX(A:A,MATCH(LARGE(C:C,ROW(A1)),C:C,)) LARGE(C:C,ROW(A1)),随着公式向下填充,依次从C列里提取第1、2、3最大的数值;再使用INDEX+MATCH的查询套路得出结果。 结果可能如下: 耸肩,摊手:现在问题来了——请问老祝如何亲吻老祝呢? 想想都太有挑战性了。哈哈哈。 解法2:一条公…

所谓随机取值,顾名思义就是从一堆数里,随机抽取几个或几组数;常用于抽奖、抽查、数据随机分组等。

举个栗子,如下图所示,A2:A21是EH培训学院的20名……男女。现在需要从中随机抽取3人,授予38妇女红旗手的称号,需要如何操作才能实现呢,下面我们来操作。

解法1:辅助列法

使用辅助列,解决此类问题,应是最简单较快捷的一种解法,基本上老少咸宜,稍有函数公式基础就可以掌握了。

首先将C列作为辅助列,C2输入以下公式,并复制填充至C2:C21区域。

=RAND()

RAND函数生成大于等于0、小于1的随机数。

其次,在E2单元格输入以下公式,复制填充至F2:F4区域即可得到抽奖结果了。

=INDEX(A:A,MATCH(LARGE(C:C,ROW(A1)),C:C,))

LARGE(C:C,ROW(A1)),随着公式向下填充,依次从C列里提取第1、2、3最大的数值;再使用INDEX+MATCH的查询套路得出结果。

结果可能如下:

耸肩,摊手:现在问题来了——请问老祝如何亲吻老祝呢?

想想都太有挑战性了。哈哈哈。

解法2:一条公式

好伐,打个响指,那能不能只用一条函数公式得出最终结果?

当然也可以,只是稍显复杂。

选取E2:E4单元格区域,使用数组三键(同时按下Ctrl+Shift+Enter)输入以下公式,即可得出结果。

=INDEX(A:A,RIGHT(SMALL(RANDBETWEEN(1^ROW(2:21),99)/1%+ROW(2:21),ROW(1:3)),2))

RANDBETWEEN(1^ROW(2:21),99),随机取得>=1且<99的20个整数,生成一维纵向内存数组,例如:

{3;17;11;40;64;65;75;83;19;37;88;74;76;17;46;39;43;80;38;92}

之后除以1%,将随机值放大百倍,再加上人名所在行的行号(+ROW(2:21)),使用SMALL函数依次从小到大取值,使用RIGHT函数重新取出行号,最后使用INDEX函数得出抽奖结果。

敲黑板!注意这是一条区域数组公式。

公式的计算原理其实是酱紫;

我们把20位男女所在的行号,看成是座位号。我们对座位号随机加上不同的权重,然后重新进行排队(升序用small,降序用large),得到一个打乱了的座号排列结果,最后依次将人名取出即可。

公式计算结果可能如下:

—挑眉!震惊!震惊!!老祝居然如何自恋!自吻3×2次。天啦噜~哈哈哈。

解法3:洗牌吧

VBA的解法也有多种,最不用动脑的是字典法,但效率偏差,此处略过不提。

嗯,分享下最有效率也最有扩展性的洗牌法。

先讲个故事,憋走开……

假设有一名班花,有一天早晨想斗地主,目前的情况很明显是一缺三,于是打算从全班20位同学里随机抽3人。班花把他们喊出教室,排成一字纵队。

第一步,从20人里随机抽取1人。假设抽到的是排在第15位的“随风小妞”。把她揪出来,拖到第1位去,然后把原本排在第1位的某童鞋安排到第15位去。这样,就成功抽取了一名……牌友。现在是2缺2。

第二步,从排在第2位和第20位之间的童鞋里再随机抽取一人——为什么不是从第1位开始抽呢?

“随风小妞”举手回答说她已经被抽过了,不能被抽第二次(意思就是她不欠抽了)。

假设这次抽到了排在第6位的“空空”,把她请出来,送到第2位去,再把原本排在第2位的某同学安排到第6位。现在是3缺1了。

第3步,从第3位到第20位之间的童鞋再随机抽取1人,排到第3位去。这样,队伍里的前3名就是今天的牌友了。恩,加上班花,4人刚好,散队,打牌去。

是的,现在,只要我们把上面的故事翻译成VBA代码,之前的示例问题也就解决了。

代码如下:

Sub ehkjxg()

Dim r,i&,t,x&

Randomize’随机种子初始化

r=[a2:a21]’将学生喊出教室,装入数组r

For i=1 To 3’抽3个人

x=Int(Rnd()*(UBound(r)-i+1))+i

‘依次从第1~2……位置起随机抽取学生

t=r(x,1)’被抽中的学生出列

r(x,1)=r(i,1)’前列的学生去被抽中学生的位置

r(i,1)=t’被抽中的学生站到前排去

Next

[e2].Resize(3,1)=r’将队伍的前3名学生(抽取结果)放入抽奖区

End Sub

会员评论

相关分类

推荐阅读

返回顶部