excel生成有概率的随机数?
发布网友
发布时间：2022-04-22 21:11
共3个回答
热心网友
时间：2023-10-09 20:34
1.把这两列数据放在B2:C26单元格
2.在A2输入=IF(D$2<SUM(C$2:C2),1,0)
并下拉填充至A26
3.在D2输入=RAND()
4.在E2输入=VLOOKUP(1,A2:B26,2,0)
那么E2就是需要的随机数
或者
1.把这两列数据放在B2:C26单元格
2.在D2输入=RAND()
3.在E2输入=OFFSET(B2,(D$2>SUM(C$2:C2))+(D$2>SUM(C$2:C3))+(D$2>SUM(C$2:C4))+(D$2>SUM(C$2:C5))+(D$2>SUM(C$2:C6))+(D$2>SUM(C$2:C7))+(D$2>SUM(C$2:C8))+(D$2>SUM(C$2:C9))+(D$2>SUM(C$2:C10))+(D$2>SUM(C$2:C11))+(D$2>SUM(C$2:C12))+(D$2>SUM(C$2:C13))+(D$2>SUM(C$2:C14))+(D$2>SUM(C$2:C15))+(D$2>SUM(C$2:C16))+(D$2>SUM(C$2:C17))+(D$2>SUM(C$2:C18))+(D$2>SUM(C$2:C19))+(D$2>SUM(C$2:C20))+(D$2>SUM(C$2:C21))+(D$2>SUM(C$2:C22))+(D$2>SUM(C$2:C23))+(D$2>SUM(C$2:C24))+(D$2>SUM(C$2:C25)),)
那么E2就是需要的随机数
这是相当于把方法1中A列的数集中在了一起
热心网友
时间：2023-10-09 20:34
你这个概率的小数点太多,看了头胀,这样吧,我举个例,你就知道咋用了
比如要生成1-5的随机数,它们的概率分别对应10%,5%,25%,45%,15%,那么公式可以这样写:
=LOOKUP(INT(RAND()*100),{0,10,15,40,85},ROW($1:$5))
你可以参照这个公式来写.
热心网友
时间：2023-10-09 20:35
Data- data analysis - random number generation
