Excel年齡區間統計圖:DATEDIF、COUNTIF函數及格式化條件

Excel日期函數中有個專門計算期間差異的DATEDIF,本文以員工清冊為例介紹如何計算年齡,進而搭配ROUNDDOWN、COUNTIF、格式化條件等函數指令,建立員工年齡區間統計圖。

如果簡單做個問卷調查,在公司裡面各部門員工在工作上使用最多的電腦軟體是什麼?相信只要有過工作經驗的人,應該毫不猶豫都會說是Excel。然而,以筆者超過十年的實務工作經驗來看,包括在會計師事務所查核各大小公司的管理報表,在公司財務部審核前端各部門的工作成果,發現在職場上大部分的人,並沒有真正發揮Excel的強大功力。

不管是人事部門的薪資彙總表、業務部門的銷貨毛利分析、採購部門的原材料價格報告、生管部門的工單製令追踪、資材部門的倉庫進料明細、研發部門的開發專案管理、財務部門各式各樣的管理報表,可以說在工作上只要有大量數值資料的地方,就會用到Excel。然而大部分人都是把Excel當做電子活頁簿,裡面有一頁一頁座標方格紙的筆記本,把資料填寫各個方格裡面,明明Excel在上方功能區提供了相當多的函數跟指令,但也許是忽略了、也許是望而生卻,寧願只讓Excel做簡單的加減乘除,再自己接手處理複雜的後續,其實這樣就浪費了Excel的功能了,如同持有一隻先進的智慧型手機,配備相當多不同功能的APP,始終卻只用來打電話和收發簡訊,殊為可惜。

這篇文章希望拋磚引玉,以人事部門的工作上報表為例,分享如何善用Excel,發揮其應有功力,達到事半功倍的效果。

一、員工清冊

假設公司有20名員工,員工清冊中分別有工號、部門、姓名、出生日期、職位、學歷等資料(純屬虛構範例),公司人事想利用此份資料,新增員工年齡的欄位,並且做一份公司員工年齡分佈報告。

員工清冊

二、Excel日期輸入

年齡都是以出生日期到目前日期的年份間隔,所以首先要輸入當天日期,如果是Excel苦手,可能直接輸入:「2019/3/5」,這樣的話,因為每天日期都在變,等於每次都要再輸入一次,但如果是Excel達人,知道有個TODAY函數,只要設置好公式,便會顯示電腦系統目前的日期,關鍵是,這個日期會自動更新,不用每次還要再重新輸入,無形之中節省了時間。

TODAY函數

三、TODAY函數

在Excel設置函數公式的方法很簡單,如圖所示,在工作表H2儲存格輸入「=TODAY()」,即會顯示「2019/3/5」,如同前面所述,這個日期是會自動更新的。

在工作表H2儲存格輸入「=TODAY()」

四、Excel日期計算

有了當天日期,接下來是目前年份減掉出生年份,計算出員工年齡。和先前類似,如果是Excel苦手,可能老實地拿起計算機像敲算盤一樣,「2018-1991」得到「27」,但如果是Excel達人,知道有個DATEDIF函數,只要設置好公式,如同寫個簡單程式,告訴Excel要幫忙計算年齡,關於此程式的妙用,待會即揭曉。

DATEDIF函數

五、DATEDIF函數

在儲存格I2輸入函數公式:「=DATEDIF(D2,H2,”Y”)」,其中D2即為出生日期,H2為今日日期,「”Y”」表示為計算年份間隔。

=DATEDIF(D2,H2,"Y")

六、Excel函數結構

以DATEDIF函數為例說明Excel的函數結構。如下表所示,Excel的函數名稱大致表達其功能,比如說此範例的「TODAY」和「DATEDIF」,很容易可以顧名思義此函數的作用。除了TODAY函數較為特別,大部份函數都有幾個參數,每個參數代表某個計算值,有點類似國中數學方程式中的X、Y變數,以這裡的公式「=DATEDIF(D2,H2,”Y”)」為例,配合實例演練,讀者應該就能理解Excel函數公式的結構設計方法。

以DATEDIF函數為例說明Excel的函數結構

七、Excel公式複製

設計好了函數公式,接下來還有個問題,員工資料有20筆,難道要一筆一筆輸入20條公式!這還是簡化範例,實務狀況可能有成百上千的員工,所以設計好了公式之後,接下來再跟各位分享Excel如何快速複製公式。

Excel如何快速複製公式

八、小黑十字架

江湖一點訣,其實Excel快速複製公式很簡單,如圖所示,只要在報表的第一筆資料設置好公式:「=DATEDIF(D2,TODAY(),”Y”)」,將游標移到儲存格右下角,游標圖示會從白十字架變成黑十字架,此時可以將滑鼠左鍵按住往下拖曳,公式即會自動複製,如果快速連按兩下,公式會自動向下複製到報表最後一筆資料,也就是儲存格G21第20筆資料。

其實Excel快速複製公式很簡單

九、Excel巢狀公式

剛好利用這個機會和各位讀者分享,Excel函數參數裡除了引用其他儲存格和單純輸入文字數值之外,還可以是另外一個函數公式,以該函數公式的計算結果作為參數值,如此形成了一個組合套嵌的複合公式,Excel專業術語稱之為「巢狀公式」。像這樣子多重函數的組合應用,會是Excel達人的進階關鍵。

如此形成了一個組合套嵌的複合公式

十、Excel函數集

TODAY函數和DATEDIF函數只是Excel眾多具有不同功能函數的其中之一,讀者有興趣的話,可以在Excel上方功能區的公式頁籤,如圖所示的函數庫指令集中,看到有相當多分門別類的函數可供使用。相信不管在工作上大部份關於資料處理的實務案例,幾乎都會有一款剛好適合的函數派的上用場。

TODAY函數和DATEDIF函數只是Excel眾多具有不同功能函數的其中之一

十一、ROUNDDOWN函數

前面的範例步驟,主要是藉助Excel執行資料整理,現有資料加以運算得到工作報表所需的欄位,擴充原有資料內容,讓報表更加完整豐富。不過通常在工作上,除了整理資料之外,如果要讓報表內容發揮價值,勢必要再進一步統計分析。

Excel在這方面也是非常好用的工具,除了設計函數公式,也可以利用現成的眾多指令。例如同樣以這篇文章範例而言,公司人事想以剛才計算出來的年齡,彙總統計呈現員工年齡區間的報告,首先設計函數公式:「=ROUNDDOWN(G2/10,0)」,其作用為無條件捨去,取到整數位,如果對於此函數不太熟悉,可點選資料編輯列左邊的「fx」,便會跳出函數引數視窗,會有關於函數和各個參數的說明,是設計和輸入Excel函數公式超好用的輔助工具。

=ROUNDDOWN(G2/10,0)

十二、COUNTIF函數

最後終於可以統計各年齡區間的員工人數:「=COUNTIF(H:H,ROW()-1)」﹐這裡用到了COUNTIF,其功能為「計算一範圍內符合指定條件儲存格的數目」,指定條件則是以ROW函數作為巢狀公式中的內嵌函數,用意在於得到流水編號以便快速複製公式。

=COUNTIF(H:H,ROW()-1)

十三、格式化條件

有了員工人數統計,只要利用上方功能區中「常用」頁籤,「樣式」指令群組中的「設定格式化的條件」,便可以輕鬆建立具有圖形化效果的資料橫條|有興趣讀者可以再深入瞭解這個指令的強大功能,於此不作細述。

設定格式化的條件

聰明使用Excel,搞定工作大小事

這篇文章以人事部門的工作範例,分享Excel於資料處理和資料分析的妙用,以作者本人實務經驗,常常看到公司很多部門人員辛苦寫半天,不如一張Excel表,其實只要善加利用所配備的函數指令強大工具,大部份報表並不難,聰明使用Excel,瞬間搞定工作大小事,不但提升了效率,更能避免忙中出錯。

每天學習,每天充電:Excel函數文章合輯

Excel年齡區間統計圖:DATEDIF、COUNTIF函數及格式化條件 1

最新文章: