Power Query應用系列【結合社群軟體及VBA巨集製作安督日報】

各位有沒有想過LINE、Messenger等社群軟體Apps的文字訊息可以進行數據轉換再利用？告訴大家一個好消息，Excel的Power Query編輯器可以幫助我們將手機社群軟體的文字訊息進行自動化轉換成資料庫形式，如此一來，某些情況下就可以請辦公室外(現場)的工作夥伴們協助以手機回傳訊息，節省辦公室人員資料彙總的時間。

本文章將以工廠施工安全管理為例，說明如何利用現場操作員、安督人員等人力，回報施工作業前、中、後的檢查狀況，以利廠工安能快速彙整資料及製作出精美的安督日報。

工廠施工安全管理簡介 利用手機社群軟體做為資料輸入端 Power Query轉換文字訊息至資料庫形式 安督日報自動化設定及VBA巨集應用 總結與補充

工廠施工安全管理簡介

相信有一半以上的讀者對於施工安全管理沒什麼概念，故在開始之前，小編先針對施工安全管理流程做一個簡單的介紹。

以工廠內施工管理為例：每天一早上工前，業主單位、監工單位及承攬商會聚在一起開會(稱工具箱會議)，檢查施工人員身體狀況(走平衡木、量測血壓、酒測等方式)、個人防護器具是否完備，確認今日施工項目及檢討(宣導)施工危害風險及防範措施。

另於施工前、中、後，除承攬商工安人員進行相關作業的安全檢查外，業主或監工單位指派的安督員也需進行複檢，以確保施工安全。

施工作業較危險的項目包含：動火(火災、爆炸風險)、侷限空間(昏倒、窒息風險)、吊掛(砸傷/亡)及堆高機操作(壓傷/亡)等等。

工安管理的目標無非就是零工殤事故，但現實往往沒那麼理想，若要分析事故發生原因，依根因順序排列包含了：
(1)現場人員對施工流程及風險認知不足，僅憑經驗和習慣做事，安全意識不佳。
(2)高風險作業環境的施工檢核不落實，及相關斷電/斷能程序沒落實。
(3)事故應變能力不足，無法減輕後果的嚴重性。

講到工安管理，大部分公司的主管都著重在現場的檢查；而對於安督資訊的資料蒐集(數位化)及報表製作較不重視，或可以說是較難執行，舉個例子來說：若工廠一天內進行10幾項大大小小的施工作業，相關安督資料皆由廠工安1人彙總，以人力上來說是不可行的(而且很多資料都是快下班了才能蒐集到)，若還要求廠工安製作安督日報表，這種苦差事可是沒人要做的。但不是說報表不重要，若能兼顧現場檢查及資料彙總(製作報表)，對於工安管理是有絕對的幫助。

綜上所述，小編認為要加強工安管理、降低工安事故的發生，除了要培養第一線施工人員、安督人員對於施工作業流程及風險評估的思維外，亦要進行安督資料數位化蒐集，及設定自動化報表。

在此考量下，小編設計出了一個Excel安督日報模型，藉由：
(1)手機社群軟體作為數據的輸入端，除了可客製化的設計訊息內容，培養安督人員風險評估的思維，亦可大大減輕了廠工安蒐集資料的負擔。
(2)利用Excel中Power Query編輯器的功能，將文字訊息自動化地轉換為資料庫形式。
(3)利用Excel公式輸出報告內容，及利用VBA巨集功能自動插入/刪除施工照片，以下將依序進行介紹。

利用手機社群軟體做為資料輸入端

此案例為某工廠的廠工安利用社群軟體Zalo創立了一個安督群組，群組成員包含了業主及監工單位的相關人員。PS Zalo為越南所開發的通訊軟體。

為了要讓Excel Power Query編輯器進行文字訊息數據轉換，在Zalo安督群組中的訊息格式需有一定的規則，例如相關項目以分號隔開(;)，項目名稱及項目內容以冒號區隔(:)。另安督訊息內容可依各單位喜好或公司規定進行設定，大致包含了基本的施工資訊、施工流程說明、作業風險評估及風險防範措施等項目，本案例的安督訊息回報內容如下圖。

要求現場安督人員在施工前進行檢查，並將結果回傳到群組中(如下圖)。
PS 施工後的檢查確認也是依照此方法進行，本文章就只針對施工前的回報內容為例，進行Excel Power Query數據轉換。

另施工前、中、後亦應規定現場人員要回傳施工許可證照片及現場檢查照片，本案例僅針對施工中照片為例說明。(如下圖)

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後，各位可以看到原本1欄的文字訊息已經被拆開成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)的視窗。(如下圖)

以下為小編輸入的公式(M語言)，各位讀者可先參考，未來有機會再做詳細介紹。(如下圖)

點選OK後，可以看到已成功插入了施工單號欄位，但各位會發現此欄位Header下方有出現紅色的線條，原因係因為剛剛輸入的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)有哪些。(如下圖)

安督日報自動化設定及VBA巨集應用

下一步驟開始之前，先回到我們的設計流程圖中看看，目前已完成了左半部的流程，接下來要進行右半部的設定，需先將Power Query轉換後的資料表複製貼上到第2個工作表中(資料庫用途)，然後在設定第3個工作表(報表用途)的報告格式及報告內容公式(以VLOOKUP將資料庫內容導入)。

首先將Power Query轉換後的資料表複製貼上到第2個工作表中。(如下圖)

接著於第3個工作表中設計安督日報的格式。(如下圖)

及於第3個工作表中A欄設定一個公式，以自動產出施工單號(主鍵)，來跟第2個工作表的資料庫主鍵進行VLOOKUP對應。(如下圖)

範例公式=VLOOKUP($A$7,安督資料庫,3,FALSE)，詳細可參考小編提供的檔案。(如下圖)

接下來插入2個開發者使用工具的執行程式方塊。(如下圖)

下圖為利用VBA巨集寫的程式碼：刪除照片。(未來將另寫文章介紹VBA巨集)

增加資料標籤後，可以看到圖表上Mazda 3的數列上已出現資料標籤(99、83、56)，此為資料標籤預設值(資料數列的值=銷售量)，但因本範例想看出不同車型在該月的佔比(%)，故需進行客製化資料標籤。

下圖為利用VBA巨集寫的程式碼：刪除照片。(未來將另寫文章介紹VBA巨集)

客製化資料標籤前，需先製作一個輔助用資料(如下圖右方)，來計算出每個月份中不同車型的銷售量佔比(%)。

完成報告後，可再mail或以Zalo寄給相關人員知悉。(如下圖)

總結及補充：

。