業務研究 Excel 在員額控管上的應用 蕭丞舜 臺北市立南湖高級中學人事室 組員 壹 查 其次是機關的預算員額數 這是根據機 前言 務需要與預算所核定的員額數 最後是現職 關人力所需 在組織編制員額數以內 按業 員額控管 對每一個人事機構而言是最 員額數 顧名思義就是目前在職的人員總數 重要也是最根本的業務 其重要性並非只為 組織編制數與在職人員總數之間的差額就是 了每個月月初至 ECPA 填報現有員額調查表 缺額數 可能基於各種理由而被控管 2 即所 更重要的是可作為機關人員配置的基礎 1 參考 謂的缺額控管 此三者息息相關 惟數目不 資料 避免機關違法用人 並可預估人事業 盡相同 往往讓新進人事人員感到疑惑不解 務的多寡與作業所需時程 是故 人事人員 即使這些概念都弄懂之後 該如何控管 對於員額的控管 務必精準確實 將有助於 員額數正確與否呢 令人遺憾的是現有的人 管理自身的業務效能 事業務資訊系統並無相關的功能可資應用 3 有關員額數 有幾個重要的概念會困擾 僅能利用其他現有的資訊工具來處理 許多 著新進人事人員 在此先作一簡單的剖析 人事單位選擇的是 Word 以之製作每月異動 首先是機關的組織編制員額數 此數目是根 明細檔案的方式來管控 同時也將此檔案用 據組織法或組織規程 於組織設立前或組織 來通知會計與出納單位 作為核發薪資的依 變更時經主管機關核定並報送銓敘部同意備 1 人員配置除了組織法規的規定之外 還要是根 據業務需要 當然也要注意 CEDAW 相關法案 的精神 54 2016.01.06 第 365 期 2 例如基於預算所限的強制控缺 改編為用人費 的空缺 考試分發中 商調中 等 3 WebHR 與 Pemis2K 均無現有員額數控管的相關 功能可用
算機的協助, 且運用函數的困難度高, 並非是適合的控管工具 另外, 與過去相較, 現職人員的異動更為頻繁, 導致每月的現有員額數都可能不同 ( 這些異動也代表要辦理隨之而來的相關業務, 如動態 ( 銓審 ) 報送 退撫 公保與健保的異動等 ), 此時必須重新計算人員的員額數, 稍有疏忽極易出錯, 若機關學校所屬人員數量龐大且身分資格多樣, 探詢出錯原因的困難度更高 如改利用 Excel 4, 則其編排與篩選功能, 可輕易將 WebHR 的資料轉入, 再運用優異的函數運算與儲存格格式化設定等功能來設計控管檔案, 可解決上述問題並達到下列的目的 : 在此僅藉一隅分享製作方法, 祈對有此業務需求的人事人員有所助益 貳 開始製作之前, 自然要先準備這三項員額數的資料, 並配合 ECPA 的現有員額調查表來設計控管用的檔案 拙者服務於地方政 4 Excel Microsoft Office 2013 2010 2007 2003 府所屬的公立學校, 遂以 D5: 組織員額管理系統 ( 以下簡稱 D5 組管系統 ) 現有員額調查表填報作業 的架構來設計所需的 Excel 員額控管檔案 至於組織編制的資料, 可以在 WebHR 內的 組織編制 內查到資料並輸出為 Excel 或 Word 檔案, 詳細的做法如下 : 步驟一 : 登入 WebHR 後, 選擇 組織編制 > 職員員額維護 > 編制員額維護查詢 步驟二 : 在出現的畫面內按 查詢, 於顯示資料後點選 列印 接著會輸出職員組織編制數的 Excel 檔 隨後則是查詢本校的教師員額數 : 步驟一 : 同樣在 組織編制 內選擇 教師員額作業 > 教師編制員額資料維護 步驟二 : 輸入要查詢的學年度後按 查詢 > 編製表 步驟三 : 點選下方 報表選項 的 教師 後, 按上方的 列印, 輸出教師員額編制數的 Word 檔 這些資料將作為 Excel 檢覈現職人員員額數是否超過組織編制員額數的基礎 接下來要準備經核定通過的預算員額數資料 此一資料通常會在 10-11 月由權責機關核定並行文至各機關學校, 內含所有編制內與編制外的員額數資料, 是計算各種類別人員最重要的基礎資料 5 最後則要找出現職人員資料 現職人員的資料可從 WebHR 的 個人資料 > 報 5 55 Word 並無自動計算的功能, 尚須計務研究據 惟 業
業務研究 表列印 內輸出 員工通訊錄 6 得到 惟, 輸出的是 Word 檔, 如要運用於 Excel 尚需 花費工夫轉換, 爰此, 可改用彈性選員的方式將現職職員的資料輸出成 Excel 檔, 應用比較方便快速, 其做法如下 : 步驟一 : 進入彈性選員頁面後, 選員條件為 : 搜尋欄位名稱 選 表 2 ; 實際服務機關代碼 輸入所屬機關學校的代碼, 接著勾選 只查現職人員 後按 下一步 步驟二 : 顯示項目除了必備的姓名 身分證字號外, 自表 1 加選 性別 ; 另外只要加選表 2 的 職稱 就好 步驟三 : 排序設定用預設值即可 7 按一下 產生選員人員名單 步驟四 : 點選下方的 Excel 後按上方的 全選 > 列印, 產製出全校的人員名單 (Excel 檔 ) 要注意的是, 以彈性選員輸出的 Excel 檔案, 每個儲存格內的資料都有 ' 字元, 此字元會讓儲存格內的資料被視為文字, 影響某些函數的應用, 故將資料全數複製, 利用 選擇性貼上 的功能, 以 值 的方式貼到空白的工作表 ( 可命名為 現職人員 ) 上, 即可消除這些 ' 字元 接著以排序的功能設定二層級的排序方式, 第一層級為職稱, 第二層級為性別, 如 6 WebHR 7 Excel 此即可將各職稱的人員依性別排在一起 隨之將編輯中的檔案另存新檔後備用 參 既然 Excel 員額控管檔案要根據 D5 組管系統內 現有員額調查表填報作業 內的表格來設計, 當然要先瞭解該表的架構為何 該表主要分為兩大部分, 左邊是現職員額數, 右邊則是缺額數, 每一部分均分三大類人員, 第一大類是職員 ( 公務人員 ), 第二大類是教育人員, 第三大類則是駐衛警和職工 職員按官等 ( 簡 薦 委 ) 特定人員( 如醫事人員 ) 區分 ; 教育人員則包含校長 教師 教官 運動教練 8 ; 職工就是技術工友 ( 技工 ) 和工友, 其他則有約聘雇 駐衛警 清潔隊員 測量助理等 這些類別又按照性別再分兩類 準備好設計所需的基礎資料, 設計的架構也有初步瞭解之後, 就開啟一個空白 Excel 檔案, 將工作表 1(Sheet 1) 的名稱改為 103 年 9 月員額明細, 然後儲存起來, 檔名為 ( 機關名稱 )103 年員額控管表 9 此工作表的設計主要分兩大部分, 一個是計算結果, 也就是讓 Excel 計算出每月各類人員的員額數, 以資填報 ; 另一部分則是基礎資料, 用來計算各類人員的員額數 基此, 8 9 56
此工作表分成四大區塊 左邊分為上下兩區 便記錄與瞭解當月的異動狀況 亦便於快速 塊 上方是 每月員額填報區塊 仿照 D5 整理成當月異動明細表以提供給相關業務單 組管系統的報表格式 下方則是其他常用的 位 中間是 缺額明細 區塊 右邊則為 在 員額統計數字與 本月異動明細 區塊 方 職人員明細 區塊 設計出來的模樣如圖 1 業務研究 圖 1 左區在 Excel 內的模樣 儲存格加上顏色是為了方便區分需填報的資料 57
業務研究 中間的缺額明細區塊分別有公務人員 教師 教官等類別人員的懸缺數和編列為用 一 COUNTA 計算個數的函數 用來 計算各類別人員的數目 人費的員額數 在職人員區塊則根據上述的 二 SUM 加總函數 計算各類別的總 三大類人員來區分 每一種身分的人員為一 人數與機關預算員額數的總和 區 每區依男女再分為兩類 然後將經過整 職此 開始進行人員數目的計算 計算 理的在職人員資料 依據每種類別分別複製 某一類別人員時 可用姓名為計算基礎 讓 貼上 貼上的內容只需要姓名 職稱和身分 Excel 計算該類別人員 總共有幾名 例如 證字號 10 即可 這裡面最需要注意的是留職停薪人員 根據實務經驗 這是機關學校內最常變動也 要計算薦任男性公務人員的人數 撰寫範例 如下 =COUNTA(AA3:AA36) 是最常出錯的地方 為方便未來資料搬移 此一寫法的含義是 計算儲存格 AA3 至 故不將留職停薪人員放在缺額明細區塊內 AA36 此一範圍內 總共有多少個已填入資料 而是擺在各大類人員旁邊 整理好之後其模 的儲存格 計算結果顯示在函數所在的儲存 樣如圖 2 所示 格 不論儲存格內的資料為文字或單純的 數字 都將被視為一筆資料 在本範例內僅 有三個名字 計算的結果就是 3 以此類推 肆 其他的人員類別的計算亦同 設計的重點在 需用的函數及其撰寫 資 料 編 排 完 成 之 後 緊 接 著 就 是 應 用 於 要清楚欲計算的儲存格範圍 建議可將 範圍加大 以因應機關內人數的變化 Excel 的函數來計算報表內所需的人員數目 不過在學校的人員分類中 D5 組管系統 此處只需要 2 個簡單的函數 即可得到所需 是將教官和運動教練均歸屬於專任教師 如 填報的正確員額數 10 不將性別資料加入的原因 係檔案的設計到最 後還要以性別作為檢誤依據 如在此加入性別 資料 則須增加一欄位才能設定檢誤機制 為 免資料範圍過大 故利用函數與身分證字號來 達到顯示性別與檢誤機制的目的 果將它們混在同一區內計算 當此類人員有 所異動時不易找出 也不易計算該類別人員 的總數 若分在不同區域內 儲存格並不連 續 又該如何計算呢 很簡單 只要用逗號 將各區的儲存格加入即可 範例如下 圖 2 銀灰色標題區塊對應的是 D5 組管系統的缺額分析區 綠色標題區則是在職人員區 塊 局部 此處將職務編號與佔缺人員資料加入 是為了便於瞭解職務佔缺現況 58 2016.01.06 第 365 期
DL123) 如此即可把兩區或兩區以上不相連的儲存格一併計算在內 按此方式在各分類的儲存格內計算出人員的總數 最後就以加總函數將各分項以及最後的現職人數 編制預算數計算出來 : =SUM(D3:D17,F4,F8) 由於 D5 組管系統的報表須將男女分開計算, 資料異動時若將男 ( 女 ) 性同仁錯移至女 ( 男 ) 性區域, 人員的計數就不正確, 尤其是人員異動頻繁的季節, 更易發生此類錯誤 以學校為例, 每年 6 至 8 月是人員異動最頻繁的時期, 如教師的留職停薪 退休 甄選等幾乎均集中於此時, 稍有不慎資料就會搬移至錯誤區域 故, 撰寫一具有提醒機制的邏輯判斷函數乃有其必要 函數要表現的結果有二, 一是顯示為正確的性別, 其二是發生錯誤時顯示的警示文句 警示文句不必過長, 以自己能看懂為主, 以節省顯示空間 至於所需的函數也只要 2 個 : MID IF IF MID 11 11 IF IF If 64 IF value_if_true value_if_false 用於男 女的撰寫範例分別如下 : =IF(AC3="","",IF(MID(AC3,2,1)="1"," 男 "," 非女! ")) =IF(AH3="","",IF(MID(AH3,2,1)="2"," 女 "," 非男! ")) 12 其含意是 : 當儲存格 AC3 為空白時, 函數所在的儲存格顯示為空白 ; 若有資料則擷取資料的第 2 個字元, 擷取的字元若為 1, 則顯示為 男 ; 不是 1 就顯示 非女!, 第二個顯示為女性的函數亦同 這些函數可複製到同一欄的其他儲存格, 至於複製的儲存格數量應參考機關內的編制或預算員額數 伍 至此, 資料的建立就大致上完成 惟, 如何知道計算的結果並未超過預算員額數呢? 人員的異動, 一定要遵守 一人一缺 ; 可有缺無人, 不可有人無缺 的最基本員額控管原則 遂, 人員資料的異動也必是 此 Microsoft Office http://office.microsoft.com/zhtw/excel-help/ha102752994.aspx 12 59 =COUNTA(CC3:CC123,DB3:DB123,DL3: 務研究業
業務研究 進彼出 例如有 1 人離職 ( 退休或調離本 機關 ), 就將此人的資料複製至 到離職 的工作表內後刪除, 然後就應在缺額區的相 13 對應欄位內增加 1 筆資料, 如此預算員額數才不會出錯 同理, 若只是在職狀況的改變, 如現職變更為留職停薪, 一樣是把現職人員的資料異動至留職停薪區, 左上角的員額明細區將會自動在對應的留職停薪處加 1, 在職人員處減 1, 全體總數維持在正確的預算員額數 上述二種情形最容易出錯的是前者, 故有必要設計一警示機制, 提醒使用者要檢查並修正 在 Excel 中可用的功能有很多, 例如 資料驗證, 可強制使用者只能輸入特定資料 數值或該數值以上或以下的值等 或者採用 格式化設定 功能, 讓該儲存格的資料有誤時, 文字和儲存格的顏色產生改 13 1 變, 達到提醒使用者的效果 在此本檔案採用後者, 理由是前者有一不易克服的缺點, 即是警示說明文字難以根據使用者的不同錯誤狀況而給予正確的指引說明, 而後者可以透過二組顏色的設定, 明確地告知使用者最後得到的員額數字到底是超過還是低於預算員額數 設定的方式很容易, 點選預算員額的儲存格, 按一下 常用 頁籤內的 設定格式化的條件 > 醒目提示儲存格規則 > 大於, 在出現的對話視窗內, 數值部分輸入機關已核定之預算員額總數, 顏色使用預設的紅色, 然後按 確定 完成設定 接著以同樣方式選擇 小於, 顏色則選另一組以資區別 爾後, 人員的資料異動時, 一旦大於或小於編制員額總數, 該儲存格的顏色均會即時且自動變化, 只有讓總數與編制員額總數符合, 儲存格才不會變色 此法也可使用在其他特定人員類別的員額數設定, 避免某一類別人員數弄錯了而不自知 圖 3: 以拖曳的方直接搬移資料 圖 4: 搬移後會發現函數內容會被改變 60
檔案至此已全部建構完成, 然, 該如何 使用呢? 基本上是人員有所異動時, 才需要將人員資料搬移到該人員應歸屬的區域內 例如現職人員辦理留職停薪, 資料就要從現職搬移到留職停薪, 各類人員的合計和總數都會自動重新計算, 填報資料時就填報最後計算出來的數字即可 應注意者乃資料的搬移, 不可採用滑鼠直接拖曳或 剪下 > 貼上 的方式, 何故? 此係 Excel 預設值的影響 Excel 預設是將與此儲存格有關的函數, 隨著儲存格的移動而自動一起變更, 故此方式會影響前述已設定好的函數 正確的做法是 複製 資料後, 在目標儲存格 貼上, 然後刪除原儲存格的資料, 如此就不會影響函數的設定 此刻別忘了要把資料也貼到本月異動明細區, 異動的日期如調入 留職停薪的起訖日期 等, 也別忘了記錄下來 若此則足可應付大多數因人員異動而產生的業務, 日後無論查看或製作本月的異動明細, 一目瞭然且簡單快速, 至於填報相關表單更是易如反掌 那麼, 到了下個月怎麼辦? 很簡單, 將本月的工作表再複製一份後修改工作表名稱, 然後根據當月的異動去整理資料即可 做法是將滑鼠遊標移到工作表名稱上, 按住鍵盤上的 Ctrl 鍵, 拖曳到旁邊即可複製出一個新的工作表 14, 然後在該工作表名稱上按二 下滑鼠, 就可修改名稱 按照此法, 每個月均能把機關學校內的員額數算得明白, 任何人員的異動也清楚 到了年底要辦理年終獎金的發放之前, 也可以此來確認異動人員的日期並做為計算發放比例的基礎, 不必浪費時間做全盤性的清查, 可有效提升業務效能 到了來年, 複製此一檔案後更改名稱為 ( 機關名稱 )104 年員額控管表, 如此每年 每月的人員異動明細 員額數變化, 均可輕鬆查閱 統計, 非只填報 D5 組管系統的報表輕鬆愉快且正確率滿分, 日後面對各種相關調查表時也都不必耗費精力去調閱舊卷或到 WebHR 內查找後輸出資料再處理成所需的資訊! 柒 員額數控管是人事業務的基礎工作, 要說是人事業務的根源亦不為過 只是員額數的控管是一個單調卻又繁瑣的工作, 如能透過資訊化工具 ( 即上述的 Excel 檔案 ) 來管理, 人事人員就只需要專注在人員資料的異動即可, 至於總數的計算 是否過多或短少, 則由機器代勞, 所得到的成果是減輕工作負擔 節省作業時間, 還可達到業務效能提升的終極目標, 值得人事人員根據自身的需要, 設計一個合用的 Excel 員額控管檔案來試試 務研究陸 業14 61