建立多層關聯下拉式選單 (EXCEL 2016)

大家好 !!  今天想介紹一下如何利用EXCEL去建立多層關聯式下拉式選單

什麼是多層關聯下拉式選單 ??

簡單來說就是 "有階層關連性的下拉式選單" 

例如我們上郵局網站查詢郵遞區號 (圖1)

我隨意查詢的是 "南投縣" "魚池鄉" "文正巷"

一階主要查詢內容：南投縣

二階次要查詢內容：魚池鄉

三階次要查詢內容：文正巷

必須依照階層順序去做查詢

便可以知道南投縣魚池鄉文正巷的郵遞區號是多少??

換句話說 如果我沒有先選到"南投縣"就沒辦法依序選到魚池鄉文正巷

這也是多層關聯下拉式選單的缺點 (不可逆)

今天教大家使用EXCEL進行多層關聯式下拉式選單的製作

也會教大家函數的使用
-----------------------------------------------------------------------------------------
1. 彙整資料 (圖2)

從圖2大家應該可以看到

下拉式選單是我們的查詢範圍，右邊三項分別為關聯性資料

一階主要查詢內容：地區(區域)

二階次要查詢內容：料理(種類)

三階次要查詢內容：食物(菜色)

我們就可以依照順序選擇我們要的菜色

2. 定義名稱(定義次要查詢項目)

將二階與三階進行定義名稱

主要原因是做群組分類並能夠使用 indirect函數 做參照
二階根據一階內容作選擇；三階根據二階內容作選擇

因此我們選取料理(二階)與食物(三階)

選取數據按下快捷鍵 ctrl+G 選擇特殊目標→選擇常數 (圖3)

 (p.s.選擇常數會將空白處抹滅)

接著進行定義名稱(圖4 ~ 圖6) _ 選擇頂端列

頂端列定義的原因是要依據一階的內容作為參照

圖4. 二階定義名稱
圖5. 三階定義名稱
圖6. 確認定義名稱

圖4 .二階定義名稱

圖5 .三階定義名稱

圖6. 定義名稱

3. 進行一階下拉式選單的設定 (圖7)

一階無需參照任何內容 因此我們選擇A列利用資料驗證

將一階設定為下拉式清單)

圖7. 一階設定下拉式選單

4. 利用 indirect函數做資料關聯性

在這邊先介紹 indirect 函數  (圖8)

說明：根據選擇的儲存格回傳內容字串

根據我們的範例來做說明 (圖9)

假設當我選擇一階(北區)

利用indirect函數回傳北區的二階內容 (中式料理、義式料理)

先選擇B列(垂直)  進行資料驗證使用 indirect函數 參照儲存格為A3 (圖9)

也就是說 參照地區回傳內容字串

選擇北區 北區的料理的內容是中式料理與義式料理

利用indirect函數回傳三階內容 (炒麵、義大利麵 等等)

選擇北區 北區的中式料理內容是炒飯、炒麵

先選擇C列(垂直)  進行資料驗證使用 indirect函數 參照儲存格為B3  (圖10)

再次強調 二階根據一階內容作選擇；三階根據二階內容作選擇 以此類推

圖8. indirect函數

                                     圖9. 參照1階

圖10. 參照2階

4. 最後依序下拉式清單測試 (圖10)

    

圖10

品管七大手法： 原來如此 !! 繪製柏拉圖教學 (EXCEL 2013 、2016) _ 重新發佈

大家好 !!

過去曾有分享過如何繪製"柏拉圖"的教學

說實在寫得不好也有一些小瑕疵存在

因此決定更新發佈柏拉圖的教學與觀念 謝謝

以下就直接進入正題

本篇教學介面適用於 EXCEL 2013、2016的朋友

如果是舊版的朋友 基本上繪製觀念是一樣的

-----------------簡單介紹一下柏拉圖------------------------------------------

柏拉圖就是根據調查統計"原因的分布"並找出關鍵少數原因的手法 

是QC七大手法之一 

又稱作 80/20 法則  帕列托法則（The Pareto Rule）

你可以把它想像 "一個問題會發生可能是有許多原因造成的" 
經過調查我們可以發現大部分出自於某1~2個原因導致問題發生

(80/20 其實只是方便說明的統稱 並不是所有問題都是以80/20分類
  許多書籍常見的誤導)

例如：60%的收入 來自於5%的產品銷售

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

現在我們進行如何利用EXCEL繪製柏拉圖 (圖1)、(圖2)

1.   首先請大家在EXCEL輸入相同數據

1-1  計算瑕疵總數量(儲存格E12)
      E12 =E7+E8+E9+E10+E11  =  62+53+19+10+6=150

1-2  計算累積次數：將瑕疵數量做累加合計 (統計柏拉圖的發生原因次數)
      累積次數的合計 (F11)= 瑕疵數量的合計 (E12)
         F7 + E8 =  62 + 53 = 115
         F8 + E9 = 115 + 19 = 134
        F9 + E10 = 134 + 10 = 144
       F10 + E11 =  144 + 6 = 150

1-3  計算累積比率：(累積次數/合計次數) x 100% (表示次數的發生比率)
      累積比率最後會=100% (G11)
        (F7 / F11) x 100% =   ( 62 / 150 ) x 100% = 41%
        (F8 / F11) x 100% =  ( 115 / 150 ) x 100% = 77%
        (F9 / F11) x 100% =  ( 134 / 150 ) x 100% = 89%
      (F10 / F11) x 100% =  ( 144 / 150 ) x 100% = 96%
      (F11 / F11) x 100% =  ( 150 / 150 ) x 100% = 100%
      

圖1. 數據來源

圖2. 計算流程

2. 選取儲存格(D7:E11)繪製 群組直條圖 (圖3)

2-1 選取數據直條→滑鼠右鍵→資料數列格式 將類別間距縮小為0 (圖4)

     直條會緊密靠在一起沒有間距

圖3. 繪製直條圖

圖4 .直條間距縮小為0

3. 新增累積比率數據儲存格(G6 ：G11) (圖5 ~ 圖8)

3-1 新增比率數據更改為資料標記的折線圖 (圖9 ~ 圖10)
     累積比率是根據原因累積次數去計算得出 
     表示單項原因發生的比率與多項原因合計發生的比率
     數據是從低到高，因此會用折線圖表示 
     每一階段的折線表示原因合計比率
   

圖5

圖6

圖7 

圖8 

圖9. 更改累積比率圖表類型

圖10 折線圖 新增完成

4. 將折線圖改為副座標軸 (圖11)
    點選折線圖 右鍵→資料數列格式→改為副座標軸
    此時會出現(右Y軸) 讀取累積比率使用

4-1 修改左右兩個Y座標軸
     累積次數(左Y軸)與累積比率(右Y軸) 數據一致 (圖12)
     右鍵→座標軸格式→座標軸選項設定

4-2 新增副水平軸 (圖13)
     我們在3-1有新增完成一條折線圖
     新增副水平軸是要調整整體折線的位置

4-3 選取副水平軸→將副水平軸改為刻度上 (圖14 ~ 圖15)

圖11

圖12

圖13

圖14

圖15

5 修改圖表尺寸、整理圖表 繪製柏拉圖完成 (圖16) 

  修改文字、顏色、新增座標軸標題、圖案繪製虛線、資料標籤

   

   從EXCEL操作來看

   請先把圖表修改到能直接放入PPT報告裡面的尺寸

   再整理圖表

   千萬不要中途就開始修改格線、顏色、新增標籤、虛線之類的

   原因是很容易讓人亂了方寸  且到最後一定還會再修改

圖 16

 6.  結論：斷裂 與 偏移 是問題發生的關鍵原因 (占了問題發生比率77%)

     以80/20原則來說 要解決問題只要先從斷裂與偏移這兩項著手

     就能夠解決80%的問題

6-1 解決完問題後，別忘了再次進行改善後的柏拉圖分析
     簡單來說 如果問題獲得解決， 斷裂與偏移的次數與比率會大幅降低

     不管你是甚麼報告，柏拉圖一定要做改善前與改善後的確認

     QC手法是需要長時間的累積數據並做相關性分析

     因此"改善前後的分析" 是非常重要的事情

       

     因為不做"改善後的分析"，要如何知道問題有確實被改善呢??

     

      大家可以想看看這句話的意思

    

      

7. 將圖表放置PPT，進行報告說明

階層分類的圖表 _ 旭日圖 教學 (EXCEL 2013)

EXCEL 2016 在圖表上新增了許多圖表功能 (柏拉圖、瀑布圖...旭日圖 等等)

EXCEL 2016可以說是非常好的版本，

能夠讓我們利用EXCEL 的圖表功能就可以立刻繪製出來

今天想介紹一下比較特別的圖表： 旭日圖 (太陽圖；多層圓環圖；放射圓環圖)，

旭日圖介紹 

旭日圖是表示 "階層的圖表"   與樹狀圖相同 (只是藉由"圓形圖"的方式做表示)

我簡單用手繪的方式提供給大家看看 雖然畫得不好看 (圖1)

                                                                      圖1. 旭日圖與樹狀圖介紹 _ 表示層級的圖表


從(圖1) 我們看到　旭日圖(6層)　與　樹狀圖(5層)　都可以表示階層，但兩者的差異在於

"樹狀圖的主階層只有單一個"，當分階層往下開始增加的話，樹狀圖的面積開始越來越大，

就會慢慢佔據你的使用空間(圖紙的空間有限，容易排擠到文字說明的排列)，

圖表看起來會顯得粗糙，會無法呈現圖表的專業與美感 

而利用圓形表示階層的旭日圖，主階層可以呈現複數

也因為是同心圓的關係，只會在原地擴張面積，

即便是往外增加分階層，旭日圖的佔據空間會比樹狀圖少非常多，使用空間較為靈活

旭日圖的優點是可以在很小的空間內 表示"複數主階層"的圖表)

也就是說 旭日圖是由複數個樹狀圖結合而成的圖表

用來表示多階層的圖表


結論：

"單主階層且分階層少"的圖表應用 我們可以使用 樹狀圖 

"複數主階層且分階層多"的圖表應用 我們可以使用 旭日圖


(圖2) 是我在網路找得多階層的旭日圖範例，提供給大家參考

大家可以想像一下 如果將(圖2) 繪製成樹狀圖，這個圖表看起來應該會很龐大

圖2. 多階層的旭日圖範例

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

如果您是EXCEL 2016的版本請點上方的旭日圖介紹 就有詳細教學，

至於2013版要怎麼繪製旭日圖，那請接著往下看 

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

繪製旭日圖的的思維：

簡單來說，旭日圖是由許多的"圓環圖"所構成 ，圓環數越多，表示層級越多

圓環的大小區塊  我們可以想像是以 " 比例 " 做為表示

由上述方式去思考的話 繪製旭日圖就會簡單許多

我想用(圖3)這個案例進行旭日圖的教學

圖3. 旭日圖教學範例

從(圖3)可以看到 有3個圓環(由內往外3階層)；四種顏色， 

說明有四項數據(Y軸)，3個階層(X軸) 

旭日圖做圖的思維 如( 圖4) 所示, 圖3其實是由 4個小樹狀圖結合而成

圖4. 旭日圖做圖思維

我們可以從(圖4)看到 從階層1的數據開始進行分層 並進行旭日圖的繪製



----------------EXCEL 2013-----------------------------------------------------------------------------


1. 輸入數據，並繪製圓環圖 (圖5)

圖5. 繪製圓環圖

2.  將各別的數據依照階層不同的顏色做填滿，如(圖6) 所示，缺塊的地方請由白色做填滿

     完成旭日圖的繪製

圖6

快速建立圖片浮水印的教學 (Power Point 2013)

如果有代表公司、學校到外地進行簡報或是打造自我風格的風格的簡報 !!

很多人會利用浮水印... 浮水印簡單來說就是把圖片顏色變淡

從OFFICE軟體來看  WORD本身具有提供製作浮水印的功能  (圖1)

圖1. WORD 製作浮水印

但如果是要教學利用POWER POINT去製作浮水印呢?? 

首先呢 利用微軟本身查詢如何去製作浮水印

微軟提供製作浮水印的方式 看完之後會覺得很麻煩 ..!!  對吧 !!

-------- Power Point 製作浮水印的思維 -----------

如果要把圖片的顏色變淡....我們就必須去調整圖片透明度的功能 !!

如果我們只是一般用Power Point 去插入圖片，利用圖片格式 是無法調整透明度的(圖2)

圖2. 直接插入圖片無法去調整圖片透明度

原因是因為 " 設定圖片格式功能 "主要是提供給圖案用的....

根據這個概念 !!

我們可以將圖案利用插入圖片的方式去做填滿，

那這個"插入圖片的圖案" 就可以設定圖片格式，

簡單來說就是製作 "填滿圖片影像的圖案 " 去調整透明度 將圖片顏色變淡 就

會成為類似浮水印了

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

下面直接教學快速建立圖片浮水印的方式

1.  插入圖片，調整為適當浮水印的大小

2. 插入適合的圖案，幫圖片製作外框 (我利用矩形圖案 作為外框)

3. 刪除圖片，外框圖案右鍵設定設定圖片格式，利用圖片或材質填滿插入圖片製外框圖案內

    

    (圖3)

圖3  製作填滿圖片樣式的圖案

4. 調整適當的透明度 (圖4) 完成浮水印

圖4. 調整透明度

5. 將外框圖案設定為"無線條"，並調整浮水印大小與擺放位置 ，完成浮水印的製作 (圖5)

圖5. 修改外框圖案線條

另外一種製作浮水印的方式

1. 插入圖片

2.  選擇 格式→圖片效果→預設格式→預設格式8  (圖7)完成

圖7

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

微軟教學的圖片浮水印製作與我們製作的圖片浮水印差在哪裡呢??

差異在於 我們只是將填滿圖片的圖案顏色變淡，看起來類似浮水印，但其實只是圖案!!

並非是浮水印

而PPT的圖片浮水印教學與WORD提供的製作浮水印功能，提供的並不是圖片

是無法選取調整格式的，我利用WORD製作浮水印給大家了解 (圖6)

圖6. 利用WORD製作浮水印

輕鬆畫出專業的直條圖 _ 百分比計量表繪製教學 (EXCEL 2013)

今天想分享教學 百分比計量表 的繪製 !!

完成圖(圖1)：

圖1

做圖思維：

利用了陰影與顏色差異設定，將一般的直條圖看起來具有立體感深度 (圖2)

圖2

----------------教學開始---------------------------

1. 請輸入(圖1)的EXCEL數值

2. 繪製百分比堆疊直條圖(圖3)，顯示出ABC數值相加100%的3個直條圖

圖3

3. 選取第一組數值黃色直條進行設定 內陰影(左上角)顯示立出體感深度，並設定陰影如(圖3)    
    與顏色 (黃色)

4. 選取第二組數值 ( 藍色、橘色、綠色 ) 直條陰影與直條顏色設定 (圖3)

主要原因是為了讓整體的直條圖看起來有立體感 !!

圖3

5. 進行圖表簡化設定 (圖1)X軸與Y軸設定，完成 !!

p.s. 同樣的概念也可以應用在橫條圖上 !!

方格區塊圖教學 (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 說明 !!