2015年9月2日 星期三

Excel-從不連續的亂數中隨機取出不重覆的數(RAND,OFFSET,SMALL,ROW)

這個例子是有網友想要在 20 個不連續的數字中,隨機取出 6 個不重覆的數,該如何處理?參考下圖,已經有 20 個不連續的數值清單,想要從這些數字取出不重覆的 6 個數。
為了簡化公式,所以使用了一個輔助欄位,在C欄中輸入公式:=RAND(),在C欄中的亂數接近不會重覆。

【公式設計與解析】
儲存格E2:
=OFFSET($B$1,MATCH(SMALL($C$2:$C$21,ROW(1:1)),$C$2:$C$21,0),,,)
ROW(1:1):向下複製公式會產生 ROW(1:1)=1、ROW(2:2)=2、ROW(3:3)=3、…。
SMALL($C$2:$C$21,ROW(1:1)):取出儲存格C2:C21中最小的一個數,向下複製公式時,可以依序取出第 1, 2, 3, 4, 5, 6 較小的數。
MATCH(SMALL($C$2:$C$21,ROW(1:1)),$C$2:$C$21,0):向下複製公式時,可以依序找出第 1, 2, 3, 4, 5, 6 較小的數所對應的位置。
最後,透過 OFFSET 函數將上式對應的數值。
複製儲存格E2,貼至儲存格E2:E7。

沒有留言:

張貼留言

檢視其他文章

好康東東