老板分分鐘主動加薪!超實用Excel函數你知多少

幼兒園里的軒爸 2021-05-08 00:15:04
Excel

  [PConline 技巧]剛入職場的小伙伴,很多時候都要面臨業務與環境的雙重考驗。如何在眾人間脫穎而出,成為夜空中最亮的那個仔?今天這篇文章,將帶您快速一覽人事崗必學的幾招Excel函數。難度不高,卻超級超級實用!

1. 身份證號提取性別

  公式:= IF(MOD(MID(B2,17,1),2)=1,"男","女")

  首先通過MID()函數提取身份證號中的第17位,然后通過MOD()函數判斷奇偶性,其中奇數為男,偶數為女。最終在IF()函數的配合下,即可得到性別值。

Excel技巧 Excel公式 Excel函數
身份證號自動生成性別

2. 身份證號提取生日

  公式:=TEXT(MID(B2,7,8),"0000-00-00")

  首先通過MID()函數提取身份證號中的第7-14位,然后使用TEXT()函數對結果格式化(即轉換成“xxxx-xx-xx”的樣式),最終便得到了我們平時習慣的生日信息。

Excel技巧 Excel公式 Excel函數
身份證號自動提取生日

  注:身份證號碼中第7-14位為出生日期。

3. 計算年齡、司齡

  公式:=YEAR(TODAY())-MID(B2,7,4)

  首先通過TODAY()函數獲得當前日期,然后使用YEAR()函數提取日期的年份值。接下來通過MID()函數獲取員工出生年份,兩者之差即為年齡。類似的方法也可用在司齡計算上,只要將生日替換成入職日期即可。

Excel技巧 Excel公式 Excel函數
快速計算年齡、司齡

4. 防止身份證號多輸少輸

  做法:通過“數據有效性”限定單元格長度

  選中身份證號列,點擊“數據”→“數據有效性”,將“文本長度”指定為“18”位。當錄入的身份證號碼超過或不足18位時,Excel便會自動提醒,以降低前端錄入可能導致的錯誤。

Excel技巧 Excel公式 Excel函數
通過數據有效性限定身份證列位數

  注:通過將“數據有效性”→“出錯警告”中的樣式修改為“警告”,可以實現只提醒不禁止效果。

5. 手機號碼自動加“-”

  做法:通過“單元格格式”,修改顯示格式

  右擊手機號碼字段,按下Ctrl+1進入單元格格式面板,點擊最下方“自定義”,輸入“000-0000-0000”。確定之后,手機號碼即以“XXX-XXXX-XXXX”格式顯示。

Excel技巧 Excel公式 Excel函數
手機號碼自動加“-”

  注:這種方法只是修改了字段顯示樣式,并沒有修改實際數據,因此后續仍然可以進行統計,這也是和Replace()函數最大一個區別。

6. 合同到期前自動提醒

  公式:=IF(G7-TODAY()<30,"合同應續簽","")

  首先建立“狀態”列,輸入公式“=IF(G7-TODAY()<30,"合同應續簽","")”。當合同到期日低于設定值(本例中為“30”)時,便自動顯示“合同應續簽”。此外也可將提醒天數單獨設成單元格,通過公式調取單元格數值,以方便用戶自行修改提醒期限。

Excel技巧 Excel公式 Excel函數
合同到期前自動提醒

7. 避免錄入重復值

  公式:=COUNTIF(B:B,B1)=1

  選中數據列,點擊“數據”→“數據工具”→“數據驗證”,將驗證條件改為“自定義”,然后輸入公式“=COUNTIF(B:B,B1)=1”。這里COUNTIF()函數的作用,是判斷B列中是否有重復記錄(如果有則值值一定大于1),如果有中止錄入,如果沒有繼續錄入。

Excel技巧 Excel公式 Excel函數
如果錄入時出現重復數值將禁止錄入

8. 自動標亮周末

  公式:=WEEKDAY(E$6,2)>5

  首先建立星期行,選中考勤表里的數值區域,點擊“開始”→“樣式”→“條件格式”→“新建規則”,然后在選框內輸入公式“=WEEKDAY(E6,2)>5”,最后選中E6,按兩下F4鍵鎖定行不鎖定列(即轉換成E$6),再設置一個特別的顏色即可。這里WEEKDAY()函數的作用是判斷當前列是否大于5,當WEEKDAY()結果>5時(即周六、周日),條件成立,表格自動為該列刷上顏色,不成立時不處理,從而最終形成周末自動上色效果。

Excel技巧 Excel公式 Excel函數
自動標亮周末

9. 讓表格自動拓展

  方法:Ctrl+T轉成“超級表”

  想讓表格自動擴展,可以利用Excel里的“超級表”功能。具體方法是:選中數據區域,按下Ctrl+T,然后修改“表格工具”→“表格樣式”為“無”(即不使用超級表默認樣式)。右擊隱藏新彈出的篩選行,即可實現表格的自動擴展。

Excel技巧 Excel公式 Excel函數
Ctrl+T實現表格自動拓展

10. 隔行換色

  公式:=MOD(ROW(),2)=0

  隔行換色有兩種方法,一是轉換成“超級表”(Ctrl+T),二是借助公式與條件格式配合完成。以公式法為例,首先選中數據區域,點擊“開始”→“樣式”→“條件格式”→“新建規格”→“使用公式確定要設置格式的單元格”,然后在選框內輸入公式“=MOD(ROW(),2)=0”。這里ROW()函數的作用是獲取當前行號,和2取余后,便得到了1、0、1、0、1、0……這樣的數列。由于公式整體位于條件格式內,因此當行號取余結果=0時,條件成立,表格自動為該行刷上顏色,不成立時不處理,最終形成了隔行換色的效果。

Excel技巧 Excel公式 Excel函數
公式+條件格式實現“隔行換色”

  注:與傳統的格式刷法相比,超級表和公式法都可以實現周末自動填色。且后期無論如何添刪,都不會導致色條出現混亂。

11. 自動標記遲到、早退

  公式:=IF(B2>TIME(8,0,0),"遲到","") 和 =IF(C2<TIME(18,0,0),"早退","")

  首先在表格后建立一個“遲到”列,輸入公式“=IF(B2>TIME(8,0,0),"遲到","")”,再建立一個“早退”列,輸入公式“=IF(C2<TIME(18,0,0),"早退","")”。這里簡單說明一下,TIME()函數主要用于約束最終時間,當上班簽到時間晚于約束值,或者下班簽到時間早于約束值時,便會觸發文字提示。

Excel技巧 Excel公式 Excel函數
自動標記遲到、早退

12. 自動打序號

  公式:=ROW()-1

  首先點擊序號列第一組單元格,輸入公式=ROW(),此時函數返回的是當前行數,根據實際行數計算(比如本文為“1”),發現兩者差值為“1”。接下來對公式進行修改,將計算后的差值減到公式后方(),填充后即可實現自動打序號效果。

  盡管自動打序號已經實現,但此時仍然不夠智能。可以在此基礎上再嵌套一個IF()函數,只有當右側有數據時才會顯示序號,沒有的話直接顯示空白。

Excel技巧 Excel公式 Excel函數
自動序列

13. 去除數據有效性列表里的空值

  公式:=OFFSET($O$6,,,COUNTA($O$6:$O$19))

  如果你經常使用“數據有效性”制作下拉列表,就會發現這樣一個尷尬,當我們前期為序列留出大量空白時,下拉列表也會出現大量空白,日常操作很不方便。其實這個問題可以這樣解決,首先點擊“數據”→“數據驗證”→“序列”,在“來源”框中填入公式。這里COUNTA()函數的作用,是求出當前數據源中的有效記錄數,然后通過OFFSET()函數確定取值范圍。由于公式限定了下拉列表的取值域,因此我們會得到一個完全沒有空值的菜單。同時新記錄也將自動添加到列表中,不會影響后續操作。

Excel技巧 Excel公式 Excel函數
清除下拉列表里的空值

14. 分級考核統計

  公式:=LOOKUP(J6,N6:N9,M6:M9)

  傳統分級統計是通過COUNT()函數與IF()函數配合計算,雖然簡單,可一旦條件過多,就會導致公式異常復雜。類似情況,其實也能借助LOOKUP()函數解決。

  首先建立一個分級副表,左側為等級,右側為達到該等級的考核線。接下來在評級框內輸入公式“=LOOKUP(J6,N6:N9,M6:M9)”,將兩組取值域按F4鍵全部轉為絕對地址。這里LOOKUP()的作用是通過目標值,直接到副表中查找對應等級,相比IF()函數顯得精簡很多。而且這樣處理后的表格,也方便用戶隨時調整考核線。

Excel技巧 Excel公式 Excel函數
簡單的分級考核公式

  注:使用LOOKUP()函數時,要注意副表考核值必須由小到大排列,否則將導致結果異常。

寫在最后

  以上這些,是人事工作者每天都要遇到的,很多復雜的操作其實往往來源于日常積累。如果以上這些,對您的工作有所幫助的話,就請給小編點個贊吧!

打開太平洋知科技,閱讀體驗更佳
制作海報 分享到微信 分享到微博

網友評論

寫評論

聚超值推薦

相關推薦

好男人资源视频-好男人资源-有人有片资源吗免费的