Excel函數公式:20個常用公式和示例,人力和行政辦公效率必備!

2020-12-11 效能舵手

Excel函數是Excel功能中最為實用、強大的存在。不誇張地說，不懂函數的同事需要花一天時間才能做完的工作，到了會用函數的同事這裡，可能僅需要2分鐘。尤其是需要做大量數據和表格的部門，差別更是明顯。

1、計算性別(F列)

=IF(MOD(MID(E3,17,1),2),"男","女")

2、出生年月(G列)

=TEXT(MID(E3,7,8),"0-00-00")，如上圖

3、計算年齡(H列)

=DATEDIF(G3,TODAY(),"y")，如上圖

4、退休日期(I列)

=TEXT(EDATE(G3,12*(5*(F3="男")+55)),"yyyy/mm/dd")，如上圖

5、籍貫(J列)

=VLOOKUP(LEFT(E3,2),籍貫代碼!A1:B35,2,0)」，如上圖

即：=vlookup(提取身份證前兩位，與籍貫代碼表匹配，返回第二列數值，精準匹配）

注意：如果輸入公式後，顯示#N/A，那可能是因為身份證號錄入的是文本格式，而我們的身份證號代碼表是數字格式，公式無法匹配，無法返回數據，就會顯示錯誤。

遇到這種情況我們可以在LEFT之前加--，即「=VLOOKUP(--LEFT(E3,2),籍貫代碼!A1:B35,2,0)」，運用數學裡負負得正的原理使得文本格式轉換成數字格式，就可以和代碼表進行匹配了。回車確定後填充即可。

如果想提取詳細的籍貫信息，可以到網上下載六位的區域代碼表，並把公式中，提取前兩位數字，改成提取前六位即可。

6、生肖公式（K列）

公式=MID("鼠牛虎兔龍蛇馬羊猴雞狗豬",MOD(YEAR(G3)-4,12)+1,1)。

註：一共有12個生肖，因此將年齡除以「12（函數「MOD(年齡,12)」）得到的數字相同的就是同一個生肖。如上圖

7、星座公式（L列）

=LOOKUP(--TEXT(G3,"mdd"),{101,"摩羯座";120,"水瓶座";219,"雙魚座";321,"白羊座";420,"金牛座";521,"雙子座";621,"巨蟹座";723,"獅子座";823,"處女座";923,"天秤座";1023,"天蠍座";1122,"射手座";1222,"摩羯座"}) ，如上圖

8、社會工齡(N列)

=DATEDIF(M3,NOW(),"y")

7、公司工齡(Q列)

=DATEDIF(P3,NOW(),"y")&"年"&DATEDIF(P3,NOW(),"ym")&"月"&DATEDIF(P3,NOW(),"md")&"天"

8、合同續籤日期(S列)

=DATE(YEAR(P3)+LEFTB(R3,2),MONTH(P3),DAY(P3))-1

9、合同到期日期(T列)

=TEXT(EDATE(P3,LEFTB(R3,2)*12)-TODAY(),"[<0]過期0天;[<30]即將到期0天;還早")

10、工齡工資(U列)

=MIN(700,DATEDIF($M3,NOW(),"y")*50)

11、本科學歷人數

=COUNTIF(V:V,"本科")

12、銷售部本科學歷人數

=COUNTIFS(C:C,"銷售部",V:V,"本科")

13、30~35歲總人數

=COUNTIFS(H:H,">=30",H:H,"<35")

14、工資條公式

=CHOOSE(MOD(ROW(A3),3)+1,工資數據源!A$1,OFFSET(工資數據源!A$1,INT(ROW(A3)/3),,),"")

註：A3:標題行的行數+2,如果標題行在第3行，則A3改為A5；工資數據源!A$1:工資表的標題行的第一列位置。

15、2019年的個稅計算公式

= ROUND(MAX((A2-B2-C2-D2-5000)*{0.03,0.1,0.2,0.25,0.3,0.35,0.45}-{0,210,1410,2660,4410,7160,15160},0),2)

註：首先在excel表格中輸入對應的數據，A2為稅前工資，B2為專項扣除(一般指五險一金等個人繳納的部分)，C2為專項附加扣除(指子女教育支出/繼續教育支出/大病醫療支出/住房貸款利息/住房租金/贍養老人支出等的專項附加扣除),D2為其他扣除(如健康商業險等個人繳納的部分)。然後在E2單元格中輸入上述計算公式，點回車。

16、隔行隔列求和

=SUMIF(A1:E12,A2,B1:F12)

17、單一條件統計個數

比如，年齡大於28歲的人數。

=COUNTIF(H3:H13,">=28")

18、多條件個數統計

比如，年齡大於28歲的男性人數。

=COUNTIFS(H3:H13,">=28",F3:F13,"男")

19、單條件求平均

比如，男性的平均年齡

=AVERAGEIF(F3:F13,"男",H3:H13)

20、多條件求和

比如，銷售部男性工齡工資總和

=SUMIFS(C1:C6,A1:A6,E2,B1:B6,F2)

如果日常工作中繁瑣的數據仍在困擾著你，嘗試套用一下這些基本、實用的函數公式吧，它們肯定能驚豔到你！

【本文由「盛夏職場工具箱」發布，2019年8月21日】

感謝關注，感謝分享。

相關焦點

  • Excel函數公式：20個常用公式和示例，人力和行政辦公效率必備！
    Excel函數是Excel功能中最為實用、強大的存在。不誇張地說，不懂函數的同事需要花一天時間才能做完的工作，到了會用函數的同事這裡，可能僅需要2分鐘。尤其是需要做大量數據和表格的部門，差別更是明顯。A1:B35,2,0)」，運用數學裡負負得正的原理使得文本格式轉換成數字格式，就可以和代碼表進行匹配了。回車確定後填充即可。如果想提取詳細的籍貫信息，可以到網上下載六位的區域代碼表，並把公式中，提取前兩位數字，改成提取前六位即可。
  • Excel基礎款：職場必備的20條Excel函數公式【附示例和解析】
    職場必備技能排行榜上，Excel絕對地位顯赫。不過有多少人只是把Excel當做簡單的數據錄入工具呢？這裡不妄加評論。文章梳理了20個Excel入門級卻很常用、實用的函數公式，供初入職場或者想提升工作效率的職場人士，做參考。
  • 字符處理文本查找截取常用的10個excel函數公式,上班族職業必備
    點擊上方藍字  免費關注置頂公眾號或設為星標，否則可能收不到文章
  • Excel基礎款:職場必備的20條Excel函數公式「附示例和解析」
    職場必備技能排行榜上，Excel絕對地位顯赫。不過有多少人只是把Excel當做簡單的數據錄入工具呢？這裡不妄加評論。文章梳理了20個Excel入門級卻很常用、實用的函數公式，供初入職場或者想提升工作效率的職場人士，做參考。
  • 一次性學透500個函數!含常用財務Excel函數示例大全,簡直太實用了!
    馬上領取這份資料一次性學透500個函數長按下方二維碼回覆：623即可免費領取500個函數示例今天僅送500份！先到先得其實，學習任何知識都要先有目的，學習函數也是一樣，如果你連函數能做什麼，達成什麼效果都不清楚，想要很好理解和運用函數公式，談何容易！
  • excel一組常用的函數公式，效率提高10%
    excel一組常用的函數公式，效率提高30%從大學懵懂學生，直接來到村裡工作，又很多的時候與農戶信息打交道，從手動計算生日，計算80老人申請補助，到60歲養老補貼大多都要與身份證提取相關數據，今天介紹我用的最多的一組函數，如圖：
  • excel函數公式:常用高頻公式應用總結(上)
    最近後臺的留言實在是太多了，由於時間關係，沒辦法一一給小夥伴們解答，所以這裡我們總結了小夥伴們問的最多的問題，整理出了10個職場人士最常用的excel公式，希望能幫大家排憂解難，趕緊來看看吧!＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊不會使用公式函數幹活真的很沒有效率，但是公式函數那麼多，一下子又學不完，這是很多職場人士面臨的窘境。
  • 辦公必備 | 工作中最常用的Excel公式大整理
    如果在工作中選出使用頻率最多的辦公工具，毫無疑問，就是Word、Excel、PPT套餐；尤其是Excel，在整理數據的時候，菜鳥和老手的效率簡直就是天差地別
  • 新手必備:4個常用的Excel函數公式!
    小編今天給大家介紹4個常用的Excel函數公式，讓你擺脫不會使用Excel統計函數的煩惱。方法：輸入函數公式：=SUM(B2:F2)即可，具體如下圖所示：圖片4我們會使用Excel函數公式製作一些財務報表，考勤表等等一系列報表，但是利用函數公式製作起來太麻煩了，我們可以選擇套用模板，「辦公資源網」中就有著豐富的Excel模板資源，要選擇套用模板的朋友可以選擇到
  • 辦公軟體學習:小白和高手,28個不可錯過的常用函數、公式和技巧
    28個不可錯過的常用函數、公式和技巧今天咱們結合辦公軟體學習中常常用到的或者是經常有疑問的一些技能做一個完全提高性的總結：無論小白和高手們都常用到的28個常用函數、公式和技巧為大家一一總結出來，讓大家能夠在工作中得心應手不再有疑問和更有效率的完成咱們手頭上的工作，想要收穫更多記得點關注查看往期內容哦，如果您覺得不錯也希望您伸出您的大拇指為我點個讚鼓勵一下，您有更好的想法當然也可以在評論區和大家一起交流。
  • excel函數公式：常用高頻公式應用總結（上）
    最近後臺的留言實在是太多了，由於時間關係，沒辦法一一給小夥伴們解答，所以這裡我們總結了小夥伴們問的最多的問題，整理出了10個職場人士最常用的excel公式，希望能幫大家排憂解難，趕緊來看看吧!＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊不會使用公式函數幹活真的很沒有效率，但是公式函數那麼多，一下子又學不完，這是很多職場人士面臨的窘境。
  • 這10個職場常用的Excel文本函數,你必須知道!
    （ID：ExcelLiRui）在日常的職場辦公中，各種字符數據的截取、轉換、統計、處理問題會頻繁出現，如果你掌握一些常用的Excel文本函數，會助力你提升工作效率，事半功倍。本文介紹10個職場最常用的Excel文本函數，包括語法結構、示例演示以及公式寫法。
  • excel函數公式:常用高頻公式應用總結(下)
    前兩天我們分享了5個職場人士最常用的函數公式，相信大家肯定沒看過癮。今天我們如約而至，繼續為大家分享後5個常用的函數公式。趕緊來看看吧~公式6：根據身份證號碼計算出生日期要從身份證號碼中得到出生日期，這種問題對於從事人資行政崗位的小夥伴一定不陌生，公式也比較簡單：=TEXT(MID(A2,7,8),"0-00-00")就能得到所需結果，如圖所示：
  • 辦公必會!9個Excel常用函數
    熟練掌握Excel函數公式，能夠在處理數據時，更快速、高效地完成工作。對普通人來說，只要掌握一些常用的公式，應對日常辦公即可。今天就和大夥分享幾個簡單又常用的公式，快拿筆記本記下來！=RANK(E2,E:E)簡單幾條數據很快就能手動排好名次，如果是上百條數據呢？這時候就需要用到RANK函數了，它能夠快速的幫助你計算出該區域中排名順序。
  • 職場辦公必備經典Excel函數公式套路
    （ID：ExcelLiRui）Excel函數公式威力強大，用法靈活多樣，要想應用自如，必須掌握其中的關鍵技術。今天給大家介紹一下職場辦公必備的3種Excel函數公式經典套路。下面結合案例展開講解，沒時間一氣看完的同學，可以分享到朋友圈給自己備份一份。
  • 《excel函數公式大全》精選
    《excel函數公式大全》精選一、數字處理1、取絕對值函數公式：=ABS(數字)2、取整函數
  • 7個Excel實用函數公式,據說高手經常用!
    IFERROR函數可以說是屏蔽錯誤值的殺手級函數了。如果公式計算沒有錯誤值，則返回公式計算結果，否則返回指定的內容。第一個參數是要運算的公式，第二個參數是在公式計算出現錯誤值的情況下，要指定返回的值。常用寫法：IFERROR(原公式,公式結果為錯誤值時要返回的內容)AND函數用於符合多個條件的判斷。
  • 15個Excel函數公式解讀,新手必備,新手辦公必備!
    在數據的處理和分析中，函數或公式是使用率比較高的工具之一，但Excel的函數非常的繁多，想要全部掌握，幾乎是不可能的，所以，我們必須掌握常用的函數，公式！常用的統計方式有三種，「Y」、「M」、「D」，即「年」、「月」、「日」。目的：根據身份證號碼計算對應的年齡。
  • excel函數公式大全之利用AVERAGE函數與IF函數的組合標記平均值
    excel函數公式大全之利用AVERAGE函數與IF函數的組合標記高於平均值的數據用▲表示低於平均值的數據用▼表示。excel函數與公式在工作中使用非常的頻繁，會不會使用公式直接決定了我們的工作效率，今天我們來學習一下提高我們工作效率的函數AVERAGE函數與IF函數，AVERAGE函數用於求平均值，IF函數用於條件判斷。
  • 人事行政Excel函數應用500例!含HR常用函數示例模板,太實用了!
    用好Excel，可以讓你實現工作效率翻番，特別是HR，學好Excel函數，別人熬夜加班才弄完的資料