2010年7月1日 星期四

Excel-將數字分組配置

在Excel中建立一組隨機亂數資料,如何自動配置到另一個資料表中呢?例如依每間隔20為單位來配置。

儲存格G2:{=SMALL(IF($A$2:$E$11>=--LEFT(G$1,FIND("-",G$1,1)-1),IF($A$2:$E$11<=--RIGHT(G$1,LEN(G$1)-FIND("-",G$1,1)),$A$2:$E$11)),ROW(1:1))}

此公式為陣列公式,輸入時要按Ctrl+Shift+Enter。

將儲存格G2複製到儲存格G2:K20。

其中:

(1) --LEFT(G$1,FIND("-",G$1,1)-1)表示要找出第一列儲存格中「XX-YY」的XX值,--的用意為轉換成數字,因為LEFT()函數求得的結果為文字。

(2) --RIGHT(G$1,LEN(G$1)-FIND("-",G$1,1))表示要找出第一列儲存格中「XX-YY」的YY值。

(3) SMALL(大於XX且小於YY的儲存格, ROW(1,1))表示找出合於條件的第1個,當儲存格往下複製時,ROW(1,1)會變成ROW(2,2)、ROW(3,3)…。

接著:

因為如果往下複製儲存格G2,如果儲存格的結果是找不到這個數,會出現#NUM!的錯誤訊息。現在要使用設定格式化條件的方式,將錯誤訊息隱藏。例如將儲存格的錯誤訊息之色彩,設定成和底色一樣,就可以達到隱藏的作用。

在儲存格G2中設定當公式:=ISERROR(G2)為真時,顯示和底色相同的文字色彩。

沒有留言:

張貼留言

好康東東