2018年2月28日 星期三

Excel-使用兩組表單控制項的選項按鈕調整前景色彩和背景色彩

在 Excel 中,如何以表單控制項的選項按鈕來設定儲存格的前景色彩和背景色彩?
以下圖為例,表單控制項的選項按鈕直接置於不同的色彩之上,當點選該選項時,即表示要選取對應的色彩。其中有前景色彩和背景色彩可以分開選取,該如何設計?
Excel-使用兩組表單控制項的選項按鈕調整前景色彩和背景色彩
當選取不同色彩時,表格中的前景和背景色彩立即會對應而變動。
Excel-使用兩組表單控制項的選項按鈕調整前景色彩和背景色彩
要使用表單控制項必須先啟用「開發人員」工具列。(選取「檔案/選項」中的「自訂功能區」,再勾選「開發人員」。)
Excel-使用兩組表單控制項的選項按鈕調整前景色彩和背景色彩
如果要分別設定前景和背景的色彩,所以必須利用二個群組分塊,分別再其中置入三個選項按鈕,這樣才能分兩組選取。(參考下圖)
Excel-使用兩組表單控制項的選項按鈕調整前景色彩和背景色彩
在前景色彩選項按鈕的控制項格式中設定儲存格連結為:G5;在背景色彩選項按鈕的控制項格式中設定儲存格連結為:G10。
Excel-使用兩組表單控制項的選項按鈕調整前景色彩和背景色彩
接著,選取儲存格A7:E14,新增以下六個的規則:
公式:=$G$5=1;格式:前景色彩1。
公式:=$G$5=2;格式:前景色彩2。
公式:=$G$5=3;格式:前景色彩3。
公式:=$G$10=1;格式:背景色彩1。
公式:=$G$10=2;格式:背景色彩2。
公式:=$G$10=3;格式:背景色彩3。
Excel-使用兩組表單控制項的選項按鈕調整前景色彩和背景色彩

2018年2月27日 星期二

Excel-判斷兩個IP是否在同一子網路(BITAND,BIN2DEC,LEFT,REPT,MOD)

今天為電腦網路相關課程的子網路切割做講義,利用試算表來轉換二進制和十進制,很方便就產生了想要的內容。
1. 以 Class C 的 IP 為例(/24),可用位址、網路位址、廣播位址如下:
Excel-判斷兩個IP是否在同一子網路(BITAND,BIN2DEC,LEFT,REPT,MOD)
儲存格E11:=LEFT(REPT("1",MOD(24,24))&"00000000",8)
儲存格E12:=BITAND(BIN2DEC(E11),E9)
判斷儲存格E12和儲存格E17是否相等?
若相等為「相同子網路」;若不相等為「不同子網路」。

2. 以 Class C 的 IP 為例(/25),可用位址、網路位址、廣播位址如下:
Excel-判斷兩個IP是否在同一子網路(BITAND,BIN2DEC,LEFT,REPT,MOD)
儲存格E12:=LEFT(REPT("1",MOD(25,24))&"00000000",8)
儲存格E13:=BITAND(BIN2DEC(E12),E10)
判斷儲存格E13和儲存格E18是否相等?
若相等為「相同子網路」;若不相等為「不同子網路」。

3. 以 Class C 的 IP 為例(/26),可用位址、網路位址、廣播位址如下:
(下圖為「不同子網路」)
Excel-判斷兩個IP是否在同一子網路(BITAND,BIN2DEC,LEFT,REPT,MOD)
儲存格E14:=LEFT(REPT("1",MOD(26,24))&"00000000",8)
儲存格E15:=BITAND(BIN2DEC(E14),E12)
判斷儲存格E15和儲存格E20是否相等?
若相等為「相同子網路」;若不相等為「不同子網路」。
(下圖為「相同子網路」)
Excel-判斷兩個IP是否在同一子網路(BITAND,BIN2DEC,LEFT,REPT,MOD)

4. 以 Class C 的 IP 為例(/27),可用位址、網路位址、廣播位址如下:
image

【附註】
上圖的內容是自動產生的(按F9鍵,即可產生新數值。),公式如下:
儲存格E16:=INT(RAND()*250+1)
儲存格A2:="IP A:192.168."&D16&"."&E16&"/"&H2
儲存格A3:="IP B:192.168."&D21&"."&E21&"/"&H2
儲存格A4:="子網路遮罩:192.168."&BIN2DEC(D18)&"."&BIN2DEC(E18)
儲存格B7:=$B$16&"."&$C$16&"."&$D$16&"."&((ROW(1:1)-1)*256/8)+1&
"~"&$B$16&"."&$C$16&"."&$D$16&"."&(ROW(1:1)*256/8-2)
儲存格D7:=$B$16&"."&$C$16&"."&$D$16&"."&((ROW(1:1)-1)*256/8)
儲存格E7:=$B$16&"."&$C$16&"."&$D$16&"."&(ROW(1:1)*256/8-1)

2018年2月26日 星期一

Excel-如何產生指定區間的亂數(INT,RAND,RANDBETWEEN)

(網友提問)在 Excel 中如何產生指定區間的亂數整數?
參考下圖,給予起始值和終止值,要產生該區間的亂數整數,並且包含起始值和終止值。
Excel-如何產生指定區間的亂數(INT,RAND,RANDBETWEEN)

【公式設計與解析】
因為 RAND 函數可以產生大於或等於 0 且小於 1 的亂數值,所以將亂數乘以某一個數,即可放大亂數值,而如果要產生的是整數,則藉由 INT 函數來去除小數部分。
儲存格C2:=INT(RAND()*($B2-$A2+1)+$A2)
相當於
儲存格C2:=INT(RAND()*(終止值-起始值+1)+起始值)

你或許也可以使用 RANDBETWEEN 函數
儲存格C2:=RANDBETWEEN(A2,B2)
RANDBETWEEN 函數:傳回所指定數字之間的隨機整數。
語法:RANDBETWEEN(bottom, top)
Bottom:RANDBETWEEN傳回的最小整數。
Top:RANDBETWEEN傳回的最大整數。
但是使用 RANDBETWEEN 函數的問題是只能產生整數,或是你可以將產生的整數除以某一個數,即可產生具小數的數值。

2018年2月20日 星期二

Excel-在資料表中指定欄、列數值在區間範圍中查詢(MATCH,INDEX,常數陣列)

(網友提問)參考下圖,如何在一個資料表中,指定欄、列數值,在區間範圍中查詢?
例如:欄數值55是在46-65之間,列數值7在6-8之間,欄列交叉對應得到11。
Excel-在資料表中指定欄、列數值在區間範圍中查詢(MATCH,INDEX,常數陣列)

【公式設計與解析】
儲存格H3:=INDEX(B2:D5,MATCH(H2,{1,3,6,9},1),MATCH(H1,{0,21,46,66},1))
(1) MATCH(H2,{1,3,6,9},1)
因為列的內容是以區間表示,所以我們在 MATCH 函數中用常數陣列,取每個範圍的第一個數值當為陣列項目,再以參數『1』取「小於」(或是參數『-1』取「大於」)。比對後得到一個位置,傳回一個數值。
image
(2) MATCH(H1,{0,21,46,66},1)
同第(1)式,在 MATCH 函數中用陣列常數,取每個範圍的第一個數值當為陣列項目,再以參數『1』取「小於」(或是參數『-1』取「大於」)。比對後得到一個位置,傳回一個數值。
(3) INDEX(B2:D5,第(1)式,第(2)式)


 INDEX 函數中以第(1)式和第(2)式的內容代入,以欄列交叉的位置傳回對應的數值。

2018年2月19日 星期一

Excel-使用公式以起始字串來篩選(OFFSET,ROW,LEN)

(網友提問)在 Excel 中如果要使用公式以起始字串來篩選清單中的項目,該如何處理?
參考下圖,要以起始字串(本例:A135790)在一個號碼的清單中篩選。
Excel-使用公式以起始字串來篩選(OFFSET,ROW,LEN)

【手動篩選】
如果你使用篩選工具,就可以使用「文字篩選」下的「開始於」功能來篩選。
Excel-使用公式以起始字串來篩選(OFFSET,ROW,LEN)
輸入開始的字串:(本例為A135790)
Excel-使用公式以起始字串來篩選(OFFSET,ROW,LEN)
隨之得到篩選結果:
Excel-使用公式以起始字串來篩選(OFFSET,ROW,LEN)

【公式設計與解析】
如何以公式來完成 ?
先選取A1:A35,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:號碼。
儲存格D2:{=IFERROR(OFFSET($A$1,SMALL(IF(LEFT(號碼,LEN($C$2))=$C$2,
ROW(號碼),""),ROW(1:1))-1,0),"")}
複製儲存格D2,貼至儲存格D2:D14。
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
Excel-使用公式以起始字串來篩選(OFFSET,ROW,LEN)
(1) IF(LEFT(號碼,LEN($C$2))=$C$2,ROW(號碼),"")
先以LEN 函數計算儲存格C2的文字長度,然後在陣列公式中利用 LEFT 函數判斷所有號碼清單由左邊取和儲存格C2相同的的文字長度,如果和儲存格C2相同者傳回其列號(利用 ROW 函數),否則傳回空字串。
(2) SMALL(第(1)式,ROW(1:1))
利用 SMALL 函數於第(1)式的傳回值由小至大取出列號。公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。
(3) OFFSET($A$1,第(2)式-1,0)
將第(2)式傳回的列號代入 OFFSET 函數取得對應的儲存格內容。
(4) IFERROR(第(3)式,"")
公式可能傳回錯誤訊息,藉由 IFERROR 函數將錯誤訊息置換為空字串。

過年-南台灣旅遊(2018)

農曆年就是要返鄉和家日團聚!為了怕塞車之苦,所以每年都提早請休假,一邊玩一邊返鄉。今年的重要行程如下:(部分景點被省略)
過年前到過年期間,一連幾天風和日麗,也表現在隨手拍攝的照片上。
【嘉義民雄星巴克】路過(刻意)!喝了再上!
過年-南台灣旅遊
【萬巒彩繪吊橋】附近吃萬巒豬腳。
過年-南台灣旅遊 過年-南台灣旅遊
【車城福安宮】還有吃綠豆蒜!
過年-南台灣旅遊
【星沙灣】
過年-南台灣旅遊
過年-南台灣旅遊
【墾丁凱撒飯店沙灘早上】
過年-南台灣旅遊
過年-南台灣旅遊
過年-南台灣旅遊
【船帆石】
過年-南台灣旅遊
過年-南台灣旅遊
【砂島】砂灘現在只能遠觀,無法就近了!
過年-南台灣旅遊
過年-南台灣旅遊
過年-南台灣旅遊 過年-南台灣旅遊(2018)
【臺灣最南點碑】這個繳費機肯定是全臺灣最的南的繳費機了!(參觀的人太多,所以沒有拍到無人的最南點碑)
過年-南台灣旅遊
【龍磐公園】看出去的景很美!但是風超大!
過年-南台灣旅遊
【港口吊橋】收費20元。
過年-南台灣旅遊
【Step Up 客製化夾腳拖】
不只可以客製(轉動輪盤即可配對鞋底和鞋帶),還可動手完成自己的設計,不滿意也可以重來。鞋帶斷了也能免費更換。
過年-南台灣旅遊
過年-南台灣旅遊
過年-南台灣旅遊 過年-南台灣旅遊
【有點霾害+高雄的落日】
過年-南台灣旅遊
【美濃民俗村】附近吃粄條!
過年-南台灣旅遊 過年-南台灣旅遊
過年-南台灣旅遊 過年-南台灣旅遊
【漁光島】
過年-南台灣旅遊
過年-南台灣旅遊 過年-南台灣旅遊
【大降目園區】有武德殿和新化老街等。
過年-南台灣旅遊
過年-南台灣旅遊 過年-南台灣旅遊

檢視其他文章

好康東東