徐俊生的筆記

斷斷續續發了幾篇 [使用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   　

很快地，一年一年又一年，就這麼過去了 二年前(2016.5.29)    在   http://prinsa.pixnet.net/blog/post/327581883  首次 發表了一篇    藥品許可證及藥理治療分類批次下載程式  ，是以 Excel VBA 下載   [ 藥品許可證 ] 及 [ 藥理治療分類 ]  資料的文章 一年前 (2017.5.4)  在    http://prinsa.pixnet.net/blog/post/341796603  再把功能強化，成為 健保給付、許可證、藥理分類(ATC/AHFS)一鍵通通下載!   時間很巧合地又來到了五月，在這期間我做了些什麼？ 2018.04.14 分享 健保藥價比對程式    2018.04.?? 健保署的健保用藥品項查詢網址由 www 改為 www1  2018.05.?? 再由 www1 改回 www (主機升級換機嗎? 還是掛了換備機頂著先?) 程式碼整個改寫，耗用資源更少，不會抓個幾百筆資料後就死機在那裡。優化了去年的版本，取消視覺化不甚實用的功能，換言之，在執行過程中直到結束為此，你完全看不到它在做什麼，換來的是執行速度的提升！ 以前抓取5筆資料大約要二十多秒，現在同樣的時間它可以完成17筆資料，效率提升了三倍有餘，連我自己都嚇了一跳！在改寫的過程中預期執行速度變快肯定是會有的，怎知差距會大到這個地步！ 下載這個版本，自己回去玩玩看就知真假。 藥品許可證及藥理治療分類批次下載   2018.05.04版 (已下架) 藥品許可證及藥理治療分類批次下載 2019.02.12版  (已下架) (此版又更快了, 每秒一筆) 這2支 (藥品許可證及藥理治療分類批次下載、健保藥價比對程式) 我自己都沒有在使用，在工作上已經 N 年沒碰到這一塊了，所以人客您如若發現了問題沒有 pass 給我，等到我自己發現也可能是 N 年以後的事了。 至於會繼續維護、強化它，純粹出自於興趣。 一個喜歡打電動的孩子，...

繼 藥品許可證及藥理治療分類批次下載.xls、WHO.ATC.DDD.Index.xls 又一大作!! 簡短地介紹它的功能, 就是： 以我們 "自己的藥品清單"→連線到  健保署 的 健保用藥品項網路查詢服務 →比對健保價 後將有價格有異動的藥品列出來 。是不是覺得真是太神奇了？ 所謂的"自己的藥品清單" 有二種方式取得－ ※可以透由此 excel 連線到 伺服器 查詢得到。是在 Excel 中利用 ADO 連接伺服器資料庫 , 把資料從伺服器抓出來，欲使用此功能需要資訊部門協助設定連線方式、修改SQL，外人無法代勞。 ※無法連接伺服器取得者，可以自己手動複制、貼上。 最後比對完成的結果會像下面這張截圖： 之後您只要將 健保藥價異動品項.xls 整理一下，就可以丟給 資訊部門 做排程更新健保藥價了。 影片示範得很清楚了，不多說。用，就對了！ 下載 健保藥價比對程式   2018.05.04 如果程式運行出現 [ 找不到專案或程式庫 ] 什麼之類的東西， 或許有些DLL沒有安裝進去!  可透過下列方式： 您可以在編輯巨集那，選取「工具」->「設定引用項目」，勾選下面兩個引用項目： Microsoft ActiveX Data Objects 6.1 Library Microsoft ActiveX Data Objects Recordset 6.0 Library 在執行流程上，我會以這樣的方式來進行： 1. 使用網頁異動偵測軟體，來追蹤 [ 健保署首頁→藥材專區→藥品→健保用藥品項 ] 有沒有新的異動檔，收到通知後就可開工進行比對。例如： 秒級偵測網頁更新變動通知(PageMonitor,Check4Change)  或 visualping - 偵測網頁更新變動自動email通知 2. 執行這支 健保藥價比對程式 ，自動從HIS主機抓出現用藥品檔→自動比對→結果自動輸出到 健保藥價異動品項.xls →整理一下。 3.丟給 資訊部門 做排程更新。 很開心地可以收工囉！ Ｑ.為何不使用健保用藥品項異動檔來比對? A: 因為有時候短期間就公告好幾筆, 例如 3/27, 3/22, 3...