2017年3月28日 星期二

Excel-根據數字顯示儲存格色彩以了解數字分佈狀況(INT,MOD,格式化的條件)

在網路上看到有網友在推論一些數字與圖形的關係,例如在下圖中,分別要在顯示1~9數字的位置顯示儲存格色彩,如何利用 Excel 來做到這樣的結果?
在下圖中,要練習如何產生數列(儲存格B2:J10),而且除了分別顯示1~9的儲存格色彩之外(共九個),也利用捲軸的表單控制項來做成動態的顯示結果。就用這個實例來練習公式運算和設定格式的條件。
image
1. 建立基本數字表
這個基本表是如何建立的呢?
原則是例如:儲存格I9,是儲存格I2(=8)和儲存格B9(=8)相乘的結果(=64),將十位數(=6)和個位數(=4) 相加(=10),再將這個結果(=10)的十位數(=1)和個位數(=0) 相加,其結果顯示為 1。
image
公式設計:
儲存格C3:=INT((INT($B3*C$2/10)+MOD($B3*C$2,10))/10)+MOD(INT($B3*C$2/10)+MOD($B3*C$2,10),10)
複製儲存格C2,貼至儲存格C3:J10。
(1) INT($B3*C$2/10:取得兩數相乘結果的十位數。
(2) MOD($B3*C$2,10))/10:取得兩數相乘結果的個位數。
(3) INT((第(1)式+第(2)式)/10):取得第(1)式和第(2)式相加結果的十位數。
(4) MOD(第(1)式+第(2)式,10):取得第(1)式和第(2)式相加結果的個位數。

2. 建立捲軸
image
在[開發人員/控制項]選單中插入一個「捲軸」表單控制項:
image
其內容設定為:最小值:1;最大值:9;儲存格連結:$B$11。
當使用捲軸時,儲存格B11會顯示1~9其中一個數。
image

3. 設定儲存格條件
接著,來設定對應數字的儲存格填滿格式。
先選取儲存格B2:J10,設定格式化的條件:
選取「使用公式來決定要格式化哪些儲存格」,輸入規則:=B2=$B$11。
image
所有和儲存格B11相同的數字儲存格都會填上藍色底色。
image

4. 製作1~9的數字對應圖
如何得到以下的結果?
先複製上圖的公式運算結果,貼至九個區域中(選擇性貼上「值」)。在每個區域的左下角依序填上數字1~9。
image
選取公式運算結果的數字,自訂數值格式為「;;;」(三個分號),儲存格上的數字不會顯示出來。
image
再以上述的格式化條件加以設定即可完成。
創作者介紹

沒有留言:

張貼留言

檢視其他文章

好康東東