Power Query應用篇-安督日報模型介紹
大家有沒有想過LINE、Messenger等Apps的文字訊息可以進行數據轉換再利用？告訴大家一個好消息，Excel的Power Query編輯器可以幫助我們將手機社群軟體的文字訊息進行自動化轉換成資料庫形式資料表，如此一來，某些情況下就可以請辦公室外(現場)的工作夥伴們協助以手機回傳訊息，節省辦公室人員資料彙總的時間。
本文章將以工廠施工安全管理為例，說明如何利用現場操作員、安督人員等人力，回報施工作業前、中、後的檢查狀況，以利廠工安人員能快速地彙整資料及製作出精美的安督日報。
工廠施工安全管理簡介
相信有一半以上的讀者對於施工安全管理沒什麼概念，故在開始之前，小編先針對施工安全管理流程做一個簡單的介紹。
以工廠內施工管理為例：每天一早上工前，業主單位、監工單位及承攬商會聚在一起開會(稱工具箱會議)，檢查施工人員身體狀況(走平衡木、量測血壓、酒測等方式)、個人防護器具是否完備，確認今日施工項目及檢討(宣導)施工危害風險及防範措施。
另於施工前、中、後，除承攬商工安人員進行相關作業的安全檢查外，業主或監工單位指派的安督員也需進行複檢，以確保施工安全。
施工作業較危險的項目包含：動火(火災、爆炸風險)、侷限空間(昏倒、窒息風險)、吊掛(砸傷/亡)及堆高機操作(壓傷/亡)等等。
工安管理的目標無非就是零工安事故，但現實往往沒那麼理想，若要分析事故發生原因，依根因順序排列包含了：
(1)現場人員對施工流程及風險認知不足，僅憑經驗和習慣做事，安全意識不佳。
(2)高風險作業環境的施工檢核不落實，及相關斷電/斷能程序沒落實。
(3)事故應變能力不足，無法減輕後果的嚴重性。
講到工安管理，大部分公司的主管都著重在現場的檢查；而對於安督資訊的資料蒐集(數位化)及報表製作較不重視，或可以說是較難執行，舉個例子來說：若工廠一天內進行10幾項大大小小的施工作業，相關安督資料皆由廠工安1人彙總，以人力上來說是不可行的(而且很多資料都是快下班了才能蒐集到)，若還要求廠工安人員製作安督日報表，這種苦差事可是沒人要做的。但不是說報表不重要，若能兼顧現場檢查及資料彙總(製作報表)，對於工安管理是有絕對的幫助的。
綜上所述，小編認為要加強工安管理、降低工安事故的發生，除了要培養第一線施工人員、安督人員對於施工作業流程及風險評估的思維外，亦要進行安督資料數位化蒐集，及設定自動化報表。
在此考量下，小編設計出了一個Excel安督日報模型，藉由：
(1)手機社群軟體作為數據的輸入端，除了可客製化的設計訊息內容，培養安督人員風險評估的思維，亦可大大減輕了廠工安人員蒐集資料的負擔。
(2)利用Excel中Power Query編輯器的功能，將文字訊息自動化地轉換為資料庫形式的資料表。
(3)利用Excel公式輸出報告內容，及利用VBA巨集功能自動插入/刪除施工照片。
以下將依序進行介紹。
各位讀者可下載本範例的檔案進行練習：
安督日報模型製作流程
1.利用手機通訊軟體做為資料輸入端
此案例為某工廠的廠工安人員利用社群軟體Zalo創立了一個安督群組，群組成員包含了業主及監工單位的相關人員。PS Zalo為越南所開發的通訊軟體。
為了要讓Excel Power Query編輯器進行文字訊息數據轉換，在Zalo安督群組中的訊息格式需有一定的規則，例如相關項目以分號隔開(;)，項目名稱及項目內容以冒號區隔(:)。另安督訊息內容可依各單位喜好或公司規定進行設定，大致包含了基本的施工資訊、施工流程說明、作業風險評估及風險防範措施等項目，本案例的安督訊息回報內容如下圖。
要求現場安督人員在施工前進行檢查，並將結果回傳到群組中(如下圖)。
PS 施工後的檢查確認也是依照此方法進行，本文章就只針對施工前的回報內容為例，進行Excel Power Query數據轉換。
另施工前、中、後亦應規定現場人員要回傳施工許可證照片、現場檢查照片及斷電/斷能等照片，本案例僅針對施工中照片為例說明。(如下圖)
2.利用Power Query轉換文字訊息至資料庫形式
要將安督訊息導入Excel，可直接將文字訊息複製貼上到Excel儲存格中。(如下動圖)
本案例共有3個安督訊息(如下圖)，接著要開始進行Power Query數據轉換程序設定，第一步驟要設定欲連結的資料來源，可直接點選Tab Data -> From Table/Range 進行連結。
點選後，將跳出建立表格視窗(如下圖)，點選OK後將直接進入Power Query編輯器。
PS 若先將上圖的訊息表格化(Table)，操作步驟會稍有不同，可參考Power Query入門介紹文章。
下圖為進入Power Query編輯器的畫面，各位可以看到剛剛的安督訊息位於中間的資料表中(一欄三列)。
在右邊的視窗中可以修改查詢(Query)的名稱，此範例改為「安督訊息」。
接著要開始進入資料轉換的步驟，首先我們要利用分號將各個項目分開來，Power Query具備這個功能的是分割資料行(Split Column，位於Tab Transform)，此範例選擇以分隔符號的方式將資料行分開(By Delimiter)。
進入分割資料行視窗後，上方可以選擇我們要的分隔符號，本範例為分號(Semicolon)，選好後按OK。
點選OK後，各位可以看到原本一欄的文字訊息已經被拆開成14個欄位。
再進行下一步轉換步驟前，先讓我們修改一下資料行名稱，將第1欄(回報內容.1)改為施工日期、第2欄(回報內容.2)改為工作內容摘要、、以此類推。(如下圖)
全部資料行名稱修改完成後，畫面如下圖。
接著我們要將各個資料行中的文字去除項目名稱，只留下項目內容，例如第1欄位目前文字是(1.施工日期:2022/11/15)，而我們要的數據只是(2022/11/15)，Power Query具備這個功能的是擷取(Extract，位於Tab Transform)，此範例為選擇擷取分隔符號後的文字(Text After Delimiter)。(如下圖)
進入擷取視窗後，輸入欲設定的分隔符號，本範例為冒號(:)，完成後點選OK。(如下圖)
完成後，各位可以看到每個資料行中都只剩下我們要的安督訊息了。(如下圖)
接著要進行一個較常被忽略的轉換步驟，那就是去除空白字元(Space)，因為人們於手機輸入訊息的時候，不能保證他們都沒有不小心按到空白鍵，帶有空白字元的數據將於某些情況下影響到數據Lookup/Join對應的正確性。Power Query具備這個功能的是修剪(Trim)，位於Tab Transform -> Format，記得操作前要先全選全部的資料行喔(快捷鍵Ctrl + A)。(如下圖)
操作到這裡，數據轉換的步驟已經完成。
讓我們再回到此案例的設計流程圖(如下圖)，因為之後我們要將Power Query處理後的資料表貼到第2個工作表(資料庫)中，及再利用資料庫主鍵與第3個工作表進行VLOOKUP對應，所以我們還需要製作一個主鍵給資料庫。
主鍵是不會重複的，本案例將主鍵格式設定為施工日期+流水號如：yyyy-mm-dd-1、yyyy-mm-dd-2、yyyy-mm-dd-3...。
在開始製作主鍵之前，先讓我們將施工日期欄位的資料類型改為日期，操作步驟如下圖。
接著要先插入一個從1開始的索引(index)資料行，操作步驟為Tab Add Column -> Index Column -> From 1。(如下圖)
各位可以看到已增加了一個新欄位index，數值分別為1、2、3。(如下圖)
接著要利用index資料行結合施工日期做出一個主鍵資料行，故需再插入一個新資料行，操作步驟為：Tab Add Column -> Custom Column (因要輸入公式，故選擇自訂資料行)。(如下圖)
接著會跳出自訂資料行(Custom Column)的視窗。(如下圖)
於視窗上方輸入資料行名稱：施工單號，於下方資料行公式中輸入公式。
PS 此公式為M語言，未來有機會再做詳細介紹。(如下圖)
點選OK後，可以看到已成功插入了施工單號資料行，但各位會發現此資料行表頭下方有出現紅色的線條，其係因剛剛輸入的M語言還沒有明確定義此資料行的資料類型，故它顯示ABC123，代表尚未定義資料類型。
再依前述更改資料類型的步驟，將此資料行資料類型改為文字(ABC)即可，修改完後各位可以看到紅色線條已經消失。(如下圖)
接著我們調整一下資料行順序，將施工單號資料行移動到此查詢(Query)的第一欄，操作方式為滑鼠右鍵點選資料行表頭(Header) -> Move -> To Beginning。(如下圖)
然後將之後不會再用到的index資料行刪除，可以滑鼠右鍵點選表頭 -> Remove刪除，或滑鼠左鍵選取資料行後，再以鍵盤Delete鍵刪除。(如下圖)
到這邊已經完成了所有資料轉換的步驟。
接下來要將此查詢(Query)下載回Excel中，操作步驟為Tab Home -> Close & Load -> Colse & Load To...。(如下圖)
回到Excel後會出現載入視窗，於上方選擇載入的方式為表格(Table)，及下方選擇載入的位置於B10儲存格，完成後點選OK。
此時可以看到此查詢(Query)已成功載入到B10儲存格中。
這個數據轉換步驟的設定只需要設定一次，未來若有新的資料來源，只要在B10這個資料表中點選滑鼠右鍵->重新整理(Refresh)，即可以自動載入轉換後的資料表，重新整理的方式還可以從Tab Data -> Refresh All 這裡點選重新整理。(如下動圖)
另外，點選查詢與連線(Queries & Connections)可以看到目前此Excel檔案中有哪些查詢(Query)。(如下圖)
3.安督日報自動化設定及VBA巨集應用
下一步驟開始之前，先回到我們的設計流程圖中看看，目前已完成了左半部的流程，接下來要進行右半部的設定，需先將Power Query轉換後的資料表複製貼上到第2個工作表中(資料庫用途)，然後在設定第3個工作表(報表用途)的報告格式及報告內容公式(以VLOOKUP將資料庫內容導入)。
首先將Power Query轉換後的資料表複製貼上到第2個工作表中。(如下圖)
接著於第3個工作表中設計安督日報的格式。(如下圖)
及於第3個工作表中A欄設定一個公式，可以自動產生施工單號(主鍵)，以跟第2個工作表的資料庫主鍵進行VLOOKUP對應。(如下圖)
日報內容的公式組成大部分相同=VLOOKUP($A$7,安督資料庫,3,FALSE)，詳細可參考小編提供的檔案。(如下圖)
接下來使用開發人員工具：插入按鈕。(如下圖)
下圖為利用VBA巨集寫的程式碼：刪除照片。(VBA 巨集入門介紹文章)
另外，針對插入照片的程式碼，因為其需要讀取電腦中的圖庫路徑，所以我們需先於電腦中建立一個新的資料夾，且相關圖片檔名需要進行規則性地命名，本案例圖檔名稱為施工單號+流水號。
下圖為插入照片的程式碼。
程式碼設定完成後，再測試程式執行狀況。(如下動圖)
到這邊我們已經成功地做出安督日報模型，之後每天下班前可再將報告以mail或Zalo的方式，寄給相關人員知悉。(如下圖)
總結及補充：
總結一下，小編認為好的施工安全管理，除了培養第一線人員對於施工作業流程的了解及風險評估的思維、廠工安等主管的現場走動巡查之外，亦要進行安督資料數位化地蒐集，及設定自動化報表。
本範例係利用手機社群軟體Apps做為蒐集數據的出發點，再以Power Query進行數據轉換，那有些人可能會想：那為什麼不直接建一個Google表單或Microsoft表單給現場人員輸入就好了？其實兩者都行，本範例是因為針對施工安全管理上，若能將相關安督資訊和照片即時地Po在手機群組上，可以讓相關人員一起進行討論及確認，更為即時及便利。
本篇範例係為小編在業界成功推行的一個解決方案之一，除了能改善工廠安督數據蒐集上的不便外，也能提升一線人員的安全意識，例如雖然公司已規定施工中要進行許多的檢查及表單簽認，但有時候現場人員一忙，面對密密麻麻的檢核表檢查容易流於形式(打勾、打勾再打勾...)。
另外，此方案亦能更有效地運用現場人力做為資料蒐集的輸入端(因為他們通常有較多的空檔時間)，以節省下辦公室人員的作業時間。有在工廠工作的朋友不妨參考看看喔！
刪除圖片巨集無法執行的話，請改成以下程式碼: (最近剛發現的問題)
Sub clearPics()
Dim shp As Shape
For Each shp In Worksheets(“報告(Report)”).Shapes
If shp.Type = 11 Then
shp.Delete
End If
Next shp
End Sub