徐俊生的筆記

斷斷續續發了幾篇 [使用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 只能比對最左或最上一欄的限制，學會這招，任何比對都難不倒你了。