2011年11月8日 星期二

Excel-隨機產生英數字

在 Excel 的工作表中,如果想要隨機產生一個可能是英文字(大、小寫字母)或是數字(0,1,…,9),該如何處理?

【基本概念】

參考下圖右,可知數字的 ASCII 碼,位於 48~57 (10 進制數),大寫字母位於 65~90,小寫字母位於 97~122。

 

【設定公式】

先想想以下的公式:

產生隨機 0~9:{=CHAR(LARGE(ROW($48:$57),INT(RAND()*10+1)))}

產生隨機 A~Z:{=CHAR(LARGE(ROW($65:$90),INT(RAND()*26+1)))}

產生隨機 a~z:{=CHAR(LARGE(ROW($97:$122),INT(RAND()*26+1)))}

這三個是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

本例可以如下做法:

儲存格L2:{=CHAR(LARGE(ROW($1:$26)+{31,64,96},INT(RAND()*62+1)))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

複製儲存格L2,往下各列貼上。

 

【原理解析】

公式中:

使用 CHAR 函數可以將一個 10 進制數,找出對應的字元。

ROW($1:$26):產生 1~26 的數列。

ROW($1:$26)+{31,64,96}:可以產生 32~57、65~90、97~122 的數列。(共可產生 26X3 個數列)

因為數字的 ASCII 碼,位於 48~57 (10 進制數),大寫字母位於 65~90,小寫字母位於 97~122。

所以 33~47 之間數列,並不是我們想要的範圍。

INT(RAND()*62+1):產生 1~62 之間的亂數整數(10+26+26=62)。

配合 LARGE 函數,可以取 122~48之間的數。(97~122共26個,65~90共26個,48~57共10個)

 

【例舉】

儲存格L2:{=CHAR(LARGE(ROW($1:$26)+{31,64,96},INT(RAND()*62+1)))}

假設 INT(RAND()*62+1) 亂數產生10。公式要找數列中第 10 個大的數字:

33,65,97;34,66,98;35,67,99;36,68,100;37,69,101;38,70,102;39,71,103;40,72,104;41,73,105;42,74,106;43,75,107;44,76,108;45,77,109;46,78,110;47,79,111;48,80,112;49,81,113;50,82,114;51,83,115;52,84,116;53,85,117;54,86,118;55,87,119;56,88,120;57,89,121;58,90,122;

由式中由大到小的第 10 個為「113」,對應的字元為「q」。

因為亂數的最大值是 62,也就是說只有由大到小的前 62 個數字有機會被挑選到。因此 33~47(上式粗體+底線)永遠不會被挑選到。

沒有留言:

張貼留言

好康東東