16個Excel函數公式你都懂嗎?

來源:互聯網
上載者:User

   為了方便日常工作,收集了以下常用的16個Excel函數公式。

  1、尋找重複內容公式:

  =IF(COUNTIF (A:A,A2)>1,"重複","")

  2、尋找重複社會安全號碼碼公式:

  =IF(COUNTIF(A:A,A2&"*")>1,"重複","")

  3、用出生年月來計算年齡公式:

  =DATEDIF(A2,TODAY(),"y")

  4、根據社會安全號碼計算出生年月公式:

  =--TEXT(MID(A2,7,8),"0!/00!/00")

  5、根據社會安全號碼碼提取性別公式:

  =IF(MOD(MID(A2,15,3),2),"男","女")

  6、根據社會安全號碼碼計算退休時間:

  =EDATE(TEXT(MID(A2,7,8),"0!/00!/00"),MOD(MID(A2,15,3),2)*120+600)

  7、計算合約到期公式:

  =EDATE(A4,3)

  8、條件求和。

  計算銷售一部的銷售總額:

  =SUMIF(B:B,"銷售一部",C:C)

  9、多條件求和。

  計算銷售一部1000以上的銷售額:

  =SUMIFS(C:C,B:B,"銷售一部",C:C,">1000")

  10、計算某個日期是第幾季度:

  =LEN(2^MONTH(A2))&"季度"

  11、指定條件的最小值:

  {=MIN(IF(B2:B10="銷售一部",C2:C10))}

  注意:這裡的公式最外層多了一對花括弧。這就是傳說中的陣列公式,輸入時需要按住shift+ctrl不放,按斷行符號。花括弧就會自動添加了,手工添加可是無效的哦。

  12、指定條件的最大值:

  {=MAX(IF(B2:B10="銷售一部",C2:C10))}

  這個公式和第一個公式的用法類似,只是將計算最小值的MIN函數換成了計算最大值的MAX函數。

  13、指定條件的平均值:

  =AVERAGEIF(B2:B10,"銷售一部",C2:C10)

  AVERAGEIF函數的文法換成普通話的意思大致是:

  =AVERAGEIF(條件地區,指定的條件,計算平均值的地區)

  第三個參數可以忽略,比如說使用下面這個公式:

  =AVERAGEIF(C2:C10,">950")

  就是用來計算銷售額在950以上的平均值。

  14、多條件計數:

  =COUNTIFS(B2:B10,"銷售一部",C2:C10,">950")

  COUNTIFS函數的文法換成普通話的意思大致是:

  =COUNTIFS(地區1,條件1, 地區2,條件2…地區N,條件N)

  除此之外,還可以使用SUMPRODUCTFunction Compute多條件計數:

  =SUMPRODUCT((B2:B10="銷售一部")*(C2:C10>950))

  用SUMPRODUCT函數多條件計數的文法,換成普通話的意思大致是:

  =SUMPRODUCT((地區1=條件1)*(地區2=條件2)* (地區N=條件N))

  15、多條件求和:

  前面的內容中,咱們說過多條件求和的SUMIFS函數,如果使用SUMPRODUCT函數多條件求和,文法為:

  =SUMPRODUCT((地區1=條件1)*(地區2=條件2)* 求和地區)

  16、用VLOOKUP函數查詢資料:

  =VLOOKUP(C14,B1:C10,2,)

  如果把VLOOKUP函數的文法換成普通話,意思大致是:

  =VLOOKUP(查詢的值,地區,返回第幾列的內容,匹配類型)

  VLOOKUP函數是使用率最高的函數之一了,日常的查詢應用中經常會用到TA。這裡有幾個問題需要注意:

  ①第二參數地區的首列中必須要包含查詢值。

  ②第三參數是資料區域的第幾列,而不是工作表的第幾列。

  ③如果第四參數忽略,VLOOKUP函數尋找時模糊比對,但要求資料來源地區升序排序。

相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。