跳到主要內容

方格區塊圖教學 (EXCEL 2013)


一個適用於 "百分比" 的區塊圖 !!  可以很清楚的圖解出 " 比例問題 "

舉例:我 發了 100 封 E-mail  只有 66 個人回函 !!

使用方格區塊圖表示如 [圖1]  !!  是不是一目了然 很方便呢 ??


圖1 . 方格區塊圖 表示 (深灰色表示出席人數)


------------------直接進行教學-------------------------------------------------

本篇教學適用於稍微對EXCEL功能有基礎概念的人

圖2:設定框線

2.1. 選取框選100個儲存格欄位 (B6 :K15)

2.2.  右鍵→儲存格格式;設定框線


圖2. 設定框線


圖3:設定函數

3.1  將這100個儲存格(B5~K15)填入1 ~ 100%

3.2  於 B16 儲存格輸入函數 RANDTWEEN

3.3  合併儲存格 B16 : K16;設定填滿顏色與字體大小

RANDBETWEEN隨機亂數抽取函數

可以在指定的上下限數字(整數)之間隨機抽取一個數字


這裡設定的公式為 RANDTWEEN(1,100)/100 


抽取的數字範圍 1 ~ 100 , 因為要顯示百分比 因此要除以100
 


圖3 設定函數


圖4:利用上一步的設定的RANDTWEEN函數,進行設定儲存格格式化條件

4.1.  選擇設定格式化條件→管理規則→新增規則→使用公式來決定要格式化那些儲存格

         →設定格式化的儲存格填滿顏色與下面公式

公式 B6 (91% _ 隨機選擇) <=$B$16 (此處需指定限定的儲存格,公式才有辦法進行限制喔)

表示小於等於B16的儲存格顯示顏色 (例如小於等於B16(43%)的儲存格顯現為深灰色)

4.2. 請修改 B16 儲存格 ( 隨機輸入1 ~ 100% )  看動態方格區塊是否會變動 

也就是說 我們在儲存格 B16 隨機輸入 (1 ~ 100%),B6 :K15 方格區塊就會依據公式的

限制條件變更 (小於 或 等於  1 ~100%)




圖4 設定格式化條件

圖5 : 設定儲存格格式 自訂數值代碼 遮蔽百分比數字

5.1. 選取 B6 : K15 儲存格,右鍵→儲存格格式→ 自訂類別 

輸入運算子 ;;; ( 3個 ; ) (43%為3個字元) → ; 表示空白

遮蔽 B6 : K15 儲存格的顯示百分比


圖5 .  設定儲存格格式 自訂數值代碼


圖6.  設定儲存格顏色與欄寬,將圖表進行美化

6.1  選取 B6 : K15 儲存格為設定填滿顏色淺灰色

6.2  選取 B6 : K15 儲存格→右鍵→儲存格格式 設定框線為白色

6.3  設定儲存格欄寬為2.5


圖6. 設定儲存格顏色與欄寬


7. 將 圖表 放置 PPT 說明 !! 














留言

這個網誌中的熱門文章

四象限的散佈圖應用 (EXCEL 2013)

散佈圖是具有相關性以及分布關係之圖表!! 也是報告中常見的圖表

而散佈圖常會帶有四象限作為分類 讓人可以更容易對資訊一目了然 !!

下面就做簡單教學一下

--------------------------------------象限散佈圖教學------------------------------------------------------------

1. 先做一般的散佈圖 選取X軸與Y軸資料做散佈圖   (圖1)

圖1
2. 將圖表格式化 去除X軸與Y軸主要格線(圖2),選取X軸Y軸座標軸將主要刻度顯示外側(圖3)  圖2 圖3
3. 新增象限點 (可利用座標軸取中點,一般會使用X軸Y軸的平均值),新增選取象限點數列資料     (圖4) 圖4 4. 新增象限點的誤差線(新增圖表項目→誤差線→誤差線選項)     將X軸Y軸心新增誤差線 誤差量使用100%;最大誤差值 (圖4) 圖4 5. 新增資料標籤(標籤包含儲存格的值 範圍選取項目),也可以在圖表上新增文字方塊使用      並完成圖表
圖5









規劃求解 教學 (EXCEL 2013)

EXCEL 如果遇到2個變數以上的的計算怎麼辦呢??

這時候就會會常利用到 " 規劃求解 "

規劃求解也是屬於逆算式,也就是說先有計算結果,再計算出附加有限制條件的的最佳數值

功能 

但其實這樣去定義  很難去了解甚麼是規劃求解

下面提供舉例:

---------------------------------------------------------------------------------------------------

一般計算式:

購買單價200元的A產品5個以及單價100元的B產品10個,這樣一共是多少錢??

A產品的總價 + B產品的總價 = 購買的總金額

(200 x 5 )+/ (100 x 10 ) = 2000元


------------------------------何謂規劃求解--------------------------------------------------

規劃求解計算式:

用2000元買單價200元的A產品以及單價100元的B產品10個,

請問各可以買幾個A產品與B產品??

這個時候答案應該就有很多種 總金額2000元可以買??


買 1個A產品 與18個 B產品

    2個A產品 與16個 B產品

    3個A產品 與14個 B產品


單純的"求解" 答案會有非常多的組合??? 每一個都有可能是最佳值 ??

但這答案是不是你要的 這可能就不一定了 ??

如果要求出你心目中的最佳解答必須要提供限制條件去做計算 這時候就必須用到規劃求解

規劃求解的解釋:規劃限制條件去做求解的逆算式

而 "規劃"求解  必須要 "規劃 " 2個或2個以上的限制條件 才有辦法去做最佳值的計算

(一個就是使用目標搜尋)

提供規劃求解的限制條件:

1.  A產品與B產品 各買 3 個以上 

2.  一共要買 12 個

3. 總花費金額 2000 元

這個時候解答的組合就會有

買 3 個 A 產品 14個 B產品  (滿足第一個限制條件,第二個未滿足 因此非規劃求解的最佳解)
     4 個 A 產品 12個 B產品  (同上)

以此類推...... (若只有一個限制條件,仍無法去計算出最佳值)  因此必須要提供2個甚至以上的

限制條件去做計算

規劃求解的答案如下�…