【背景】
R同事每季皆需統計全公司上百位同仁的假勤紀錄。首先要從系統匯出原始檔案，再去蕪存菁、加工成主管容易閱讀的報表。過去皆為純手動操作輸入，不熟練者約需一天，熟練後只需半天。亨利羊看不下去，這哪需要一天半天，這分明是可以瞬間完成的事情，於是自告奮勇提出解決方案。
【問題】
每季系統匯出的表頭長這樣：
| 分公司 | 職稱 | 中文姓名 | 英文姓名 | 到職日 | 事假 | 病假 | 生理假 | 婚假 | 分娩假 | 陪產假 | 喪假 | 公假 | 年假 | 補休 | 彈性假 | 銷年假(註銷已申請年假) | 銷補休(註銷已申請補休) | 銷彈性假(註銷已申請彈性假) | 產檢假 | 榮譽假 | 合計天 |
主管希望看到的表頭長這樣：
| 分公司 | 中文姓名 | 英文姓名 | 身份類別 | 到職日 | 2015_病假 (單位：天) | 2015_生理假 (單位：天) | 2015_事假 (單位：天) | 2015_年假 (單位：天) | 2015_其他 (單位：天) | ||||||||||||||||||||
| Q1 | Q2 | Q3 | Q4 | 合計 | Q1 | Q2 | Q3 | Q4 | 合計 | Q1 | Q2 | Q3 | Q4 | 合計 | Q1 | Q2 | Q3 | Q4 | 合計 | Q1 | Q2 | Q3 | Q4 | 合計 | |||||
【解決方法】
1.將每季系統匯出的資料，原封不動的分別貼上在工作表:Q1、Q2、Q3、Q4。每過一季就貼上一張。
2.運用SUMIFS和INDIRECT函數。以下為F1:J5的示例：
| 2015_病假 (單位：天) | ||||
| Q1 | Q2 | Q3 | Q4 | 合計 |
| =SUMIFS(INDIRECT(“‘”&F$2&”‘!$G:$G”),INDIRECT(“‘”&F$2&”‘!$C:$C”),總表!$B3) | =SUMIFS(INDIRECT(“‘”&G$2&”‘!$G:$G”),INDIRECT(“‘”&G$2&”‘!$C:$C”),總表!$B3) | =SUMIFS(INDIRECT(“‘”&H$2&”‘!$G:$G”),INDIRECT(“‘”&H$2&”‘!$C:$C”),總表!$B3) | =SUMIFS(INDIRECT(“‘”&I$2&”‘!$G:$G”),INDIRECT(“‘”&I$2&”‘!$C:$C”),總表!$B3) | =SUM(F3:I3) |
| =SUMIFS(INDIRECT(“‘”&F$2&”‘!$G:$G”),INDIRECT(“‘”&F$2&”‘!$C:$C”),總表!$B4) | =SUMIFS(INDIRECT(“‘”&G$2&”‘!$G:$G”),INDIRECT(“‘”&G$2&”‘!$C:$C”),總表!$B4) | =SUMIFS(INDIRECT(“‘”&H$2&”‘!$G:$G”),INDIRECT(“‘”&H$2&”‘!$C:$C”),總表!$B4) | =SUMIFS(INDIRECT(“‘”&I$2&”‘!$G:$G”),INDIRECT(“‘”&I$2&”‘!$C:$C”),總表!$B4) | =SUM(F4:I4) |
| =SUMIFS(INDIRECT(“‘”&F$2&”‘!$G:$G”),INDIRECT(“‘”&F$2&”‘!$C:$C”),總表!$B5) | =SUMIFS(INDIRECT(“‘”&G$2&”‘!$G:$G”),INDIRECT(“‘”&G$2&”‘!$C:$C”),總表!$B5) | =SUMIFS(INDIRECT(“‘”&H$2&”‘!$G:$G”),INDIRECT(“‘”&H$2&”‘!$C:$C”),總表!$B5) | =SUMIFS(INDIRECT(“‘”&I$2&”‘!$G:$G”),INDIRECT(“‘”&I$2&”‘!$C:$C”),總表!$B5) | =SUM(F5:I5) |
【成果】
Q4一結束、數據一匯出，貼上瞬間即完成分類統計。
流程改善前：耗時半天。
流程改善後：耗時半分鐘。
工作效率改善任務，成功！
【延伸任務】
R同事雖然統計完了，但是他還有一個艱困的任務：把各部門員工的資料分別儲存在不同檔案，寄送給不同主管……請看此文：Excel_VBA_案例分享_將統計表自動依照部門分類儲存成不同檔案