徐俊生的筆記

斷斷續續發了幾篇 [使用VBA抓資料]、[表格資料的處理] 以及 [圖表製作] 的心得後，這一天終於來到。 有人問了這個問題，「每次都發一點一點的，什麼時候可以看到我使用 Excel 的全貌呢？」 在小弟的認知當中， Excel 大約可以分為三個領域，在網路裡每個領域都有可找到高手神人： 資料處理與分析 (包括資料庫的觀念、內建工具及函數的使用、樞紐分析及大數據分析工具的使用) 數據視覺化呈現的圖表製作 自動化的 vba 程式設計 剛好，最近就有一個整合案例分享給大家，也是我作業的日常。 某一天，健保署統計了去年一整年使用痠痛貼布>192片的病患名單，要醫院自己看著辦。當然了，過去的已經改變不了，老闆會關心的點就是「今年度痠痛貼布的使用情形呢？」 於是乎…從電腦開機、去茶水間倒杯溫溫的茶走回座位開始，一直到最後結果出來，小弟我大約用了２０分鐘的時間，這期間跑資料游標呈現漏斗狀不太能動電腦時，還抽空和女同事探討了一下人生問題，這樣子的速度快不快？ 由於不便呈現真實、近期資料，所以我把資料日期拉得遠一些，一樣是半年，並且增加或刪除一些資料、去識別化。主要呈現這三個部份： 用 VBA 取得所需要的醫令(我自己有vba寫好的報表產生器模板，只要改一點點地方就可以抓到需要的資料) 把文字形態的日期用涵數date()格式化為正確的日期格式。再使用樞紐分析，統計出老闆關心的部份，像是1~6月合計使用>96片病患清單、單月使用>16片註記… 做一個圖來說明。 影片到此為止沒有錄進去後續的美編排版，其實raw data到手整理經過Excel Table化之後，使用樞紐分析可以解決八、九成以上的統計需求，而且可以非常的快速，節省下許多時間，強力推薦給每位Excel 使用者。

小弟我天生就有一股不想跟別人一樣的傲氣 所以這裡看不到內建圖表，有的都是些組合式的圖表 沒有基礎，只有進階 有時候當我們需要呈現的數據不太多，基本上只有一個 或是這一個屬性的連續性變動數據時，除了 Excel 內建的長條圖、曲線圖之外 我們其實還能有更不一樣的呈現方式 例如之前已經展示過的：  資訊圖表改造心得分享-令人讚嘆偉大的鬆餅圖(Waffle Chart)   資訊圖表改造心得分享-令人驚艷美爆的子彈圖(Bullet Graph)  除此之外，這篇我要製作的一樣不是 Excel 裡頭的內建圖表 它是　溫度計圖( Thermometer Chart ) 如果當你只有 實際值 和 目標值 ，可以考慮這個數據的表示方法 在本篇文章程中，我會告訴你，如何在 Excel 中創建一個溫度計圖表的具體步驟： 1.選擇數據點 → 插入條形圖 → 切換列/欄 2.把實際值（在這個例子中的圖為藍色）改為 副座標軸 3.設定主座標軸和副座標軸的最小值固為 0, 最大值為 100 （請注意，即使值已經是0和100，你還是應該手動改變這個值，不要讓它自動） 4.刪除右邊的軸（選擇它並按下Delete鍵），並刪除水平格線（選擇它並按下Delete鍵） 5.把目標值（在這個例子中的圖為橘色）填滿：白色；邊框：實線（顏色必需與實際值相同） 6.調整圖表，使它看起來像一個溫度計。把括：左側垂直座標軸 - 設定內側刻度線和顏色；下方的水平座標軸 - 設為無框線；圖表寬度拉窄一點，讓它看起來就像支溫度計 7.拉一個圓圈，製作成溫度計的底部，完成！     是不是很簡單呢？ 當然囉～如果不只一個數據，而是有好幾個數據，就會像下圖這樣： 若您想要為溫度計設定警戒值，小弟我也為您加上了，像下圖這樣： 有在 follow 前幾篇文章的粉粉應該能發現，直條圖堆疊起來的方式以及警戒值的設定看起來是不是很像【子彈圖(Bullet Graph)】呢？ 您的感覺沒錯，在直條圖第一個步驟 切換列/欄 以及第二個步驟 某數據改為 副座標軸 時，就是子彈圖(Bullet Graph)的關鍵步驟，而子彈圖(Bullet Graph)為了要做出子彈的樣子還多出了 更改類別間距 ...

上篇　 資訊圖表改造心得分享-令人讚嘆偉大的鬆餅圖(Waffle Chart) 　最末提到的影片檔那樣有三個 KPI， 而且有下拉式選單可以選取 季別 連動性地更改 waffle Chart 的圖要怎麼製作？ 這將會使用到三個技巧，分別是： 下拉式選單 Index 函數 Match 函數 1. 下拉式選單 在 Excel 這個工作表 儲存格A1 製作 下拉式選單，是利用 [資料]→[資料驗證]→[設定]→清單 的方式，下方的來源可以是逗點分格的文字清單，也可以是一個資料範圍： 2.Index + 3. Match 絕配的組合函數 在 [Calculation] 工作表，我們的資料是在 A1~D5 儲存格，KPI 與 季別 的二維列表 然後在 A8~B10 我們將 下拉式選單(就是在工作表[Dynamic Waffle Charts]的A1儲存格) 選出季別 的 KPI值 放在這裡 這裡使用到二個函數，分別是 Index() 和 Match()。 第一次看到這函數的朋友們，就算是看了說明也不曉得它該怎麼使用，真的~~~microsoft 裡它的說明就像這樣： 寫了等於沒寫，看了也是白看！我建議直接看別人寫的文章會比較容易明瞭，像是這兩篇就寫得很詳細： Excel INDEX 函數用法教學：取出表格中特定位置的資料 Excel MATCH 函數用法教學：在表格中搜尋指定項目位置 直白來說，就是用 Index (範圍，取出範圍的哪一列，取出範圍的哪一欄) 致於要取出哪一列和哪一欄，我們就用 Match (找什麼東西，在哪個範圍，比對類型) 來指出 看到這裡，有沒有仍然一頭霧水？？！！ 沒關係，涓滴何以成江海？ 上面那二篇連結多看個幾十遍就能融會貫通的，之後你會發現，Index + Match 的組合可以 海K vlookup 和 hlookup 只能比對最左或最上一欄的限制，學會這招，任何比對都難不倒你了。

你有沒有聽說過鬆餅圖（英文原名稱為 Waffle Chart）？ (其實中文"鬆餅圖"是我自己替它命名的，因為我還沒看過有什麼使用中文的人使用過它!!) 我在很多 儀表板 和一些文章中 看到過 ，發現它真的很酷、很不一樣，在很多 時候它被用作為圓餅圖的替代品。 下面是鬆餅圖的一個例子： 在上面的例子中三個 KPI ， 每個 Waffle Chart 是一個 10X10 的格子，其中每一格代表1％。 彩色方塊深色表示目標達到的程度，上方淺色表示還沒達到的，並且在方框內顯示達成 % 百分比。 為何我喜歡這樣的圖？ 1. 看起來很酷，很不一樣，至少在中文書籍、網頁我還沒看到有誰使用過。 2. 閱讀和理解起來非常簡單。 3. 它十分地聚焦，吸引閱讀者的注意力，可以有效地用於突出特定的 KPI 指標。 4. 它不會歪曲或扭曲數據（大家都曉得，圓餅圖有時會因視角而產生偏差）。 它有什麼缺點？(其實它的優點的反面就是它的缺點囉~) 1. 就價值而言，它只不過是一個數據，沒辦法呈現太多的資訊。 2. 在 Excel 中製作它需要一些步驟（並不像其它內建的圖那麼簡單）。 3. 若您硬是要塞進去多一點的資訊，其實也不是不行啦! 但它就會變得難以理解～不信的話，您看下圖： 讀得懂嗎？　能懂我服了你！（看起來有沒有很像磁碟磁區重組） 在上面示範的影片當中我是製作成儀表板的方式，意謂著它必需是可以動態地改變，下面驟中也會呈現這功能是怎麼辦到的。 使用【格式化條件】製作 Waffle Chart 有三個步驟： 1. 在儲存格中製作 Waffle Chart。 2. 製作標籤。 3. 製作可在 Excel 儀表板中使用的連結圖片。 1. 在儲存格中製作 Waffle Chart (1).在工作表中，選擇一個 10*10 的儲存格並調整其大小，使它看起來像鬆餅圖一樣一格一格的，長寬都一樣。 (2).在 10*10 儲存格中，從左下角開始，向右向上輸入1%~100%的值。 (3).選取這 10*10 格後，轉到首頁 - >設定格式化的條件 - >新增規則。 (4).在“新增規則”對話框中，選...

在創建儀表板時面臨的挑戰之一是，在有限的空間中顯示分析結果。因此，在選擇製作什麼樣類型的圖表則變得非常非常重要。 長條圖，你一定聽說過，也使用過，不論是直條圖或橫條圖類型的，都歸類在此。 如果你這麼想...千篇一律的長條圖又有什麼好拿來說嘴的？ 的確，如果只是制式圖表，小的怎麼好意思拿出來 show 呢！ 今天要講的是，從長條圖變化出來的  子彈圖 ( bullet graph ) 英文名稱是叫做 Bullet graph，由 Stephen Few 開發， 中文名稱叫什麼我倒是不曉得，因為還沒看過什麼人在使用。如果您知道的話順便告訴我一聲，反正我就叫它 「子彈圖」 就是了。 這些是我工作中的改造實例，修改後的圖是不是賞心悅目多了呢？ 從上圖可以看到，從過去３～８月的業績目標每月都是不一樣的(灰色部份)，但業務部門很給力，不但達成每月預定目標更是超額； 不過觀察圖表我們發現了一個隱憂，就是５～８月業績是呈現下降的，更在９月份前兩週都沒能達到預定進度，似乎是在這下降趨勢之中即能預料？ 透過這樣的資訊圖表，是不是很容易一眼就能解讀？ 關於子彈圖的的優點是，它在儀表板中佔用的空間很小，並且可以 充滿了很多很多訊息 。 例如下面這張(左圖)，只是單單一個直條圖而已，卻可以呈現目標值、達成率，以及各個階段目標！  右圖呈現不同年度的資料，我們甚至可以把每年的 尚可、好、佳、極佳 這種各階段目標定成不同百分比，是不是十分地不可思議？！ 至於怎麼做？ 若有多個不同階段目標值的/甚至還有一條橫槓的，要怎麼做？ 其實 Goolge 一下會發現有一堆能人異士分享作法，雖說中文的也有，但英文的網頁還是寫得比較完整些，我也是從英文網站學習來的，文章寫得又詳細圖又美觀，有興趣學習的人可以參考參考：  http://www.storytellingwithdata.com/blog/2017/5/25/the-bullet-graph   　

在台上簡報的時候，排版出密密麻麻的文字是大多數人的通病，從學生時期到出社會工作，無論任何地方都會看見這樣的例子。 藉由「視覺化」的方式傳達你的想法內容會較易讓人接受，一方面是藉此讓觀看者能夠更願意看，一方面亦是在節省彼此的時間，讓簡報過程得以更加順利。 以下我以自身經驗，呈現如何將 "文字" 轉化成視覺化的 "圖型" !!

自己從很久很久以前就開始製作簡報資訊圖表，一直都是自己摸索著， 看著前人們、上課的講師們留下來的範本，依樣畫葫。 真正針對簡報美學下功夫正式的學習，也是近一年的事。 在這裡我將一一分享如何把自己以前一直在使用的圖表，透過某些方法的改造、升級，製造出漂亮的圖表！ 以下這些例子不能說是比別人的好，我只是樂於將自己改造的過程分享出來，並期待會有更完美的方式來呈現。