Excel中含金量極高的幾個萬能公式

    Excel函數公式的威力強大眾所周知,但很多人還是用不好,原因之一是沒有將千變萬化的各種形式的公式實質性看透,只懂皮毛。

    為了讓大家更容易理解公式的框架結構和擴展方法,今天帶來的這5個萬能公式,會結合Excel實例展開,并且給出不同應用場景下的擴展方法。

    萬能公式的萬能之處,最終不在于公式本身,而在于使用公式的人。

    1 多條件計數統計

    按照多個條件進行計數統計,是工作中很常見的需求。

    咱們先看個按照兩個條件統計符合條件的個數的數據的案例吧

    案例場景截圖如下(黃色區域輸入公式)

    F2=COUNTIFS(A:A,E2,C:C,'>500')

    多條件計數統計的萬能公式

    =COUNTIFS(條件區域1,條件1,條件區域2,條件2.....條件區域N,條件N)

    這樣,即使有再多的條件,依此類推,在公式中添加參數擴展即可。

    2 多條件求和統計

    按照多個條件進行求和匯總統計,也是辦公一族常遇到的事了。

    案例場景截圖如下(黃色區域輸入公式)

    G2=SUMIFS($D:$D,$A:$A,$F2,$C:$C,G$1)

    注意這個公式中的單元格混合引用的應用

    多條件求和統計的萬能公式

    =SUMIFS(求和區域,條件區域1,條件1,條件區域2,條件2.....條件區域N,條件N)

    利用SUMIFS,先聲明求和區域,再依次羅列各種條件區域和條件,即使有再多的條件,依此類推,在公式中添加參數擴展即可。

    3 多條件查找引用數據

    查找引用數據的需求,幾乎每天都有,如果你還只會Vlookup,那很多問題是無法解決的。

    比如下面的這個多條件查找引用數據的問題。

    案例場景截圖如下(黃色區域輸入公式)

    =LOOKUP(1,0/(($B$2:$B$13=F2)*($C$2:$C$13=G2)),$D$2:$D$13)

    多條件查找引用數據的萬能公式

    =LOOKUP(1,0/((查找條件1)*(查找條件2)*......*(查找條件N)),查找結果所在區域)

    利用LOOKUP函數可以忽略錯誤值查找,以及支持區間判斷查找的特性,再多的查找條件,按如上萬能公式套用即可。

    4 一對多查找所有符合條件的數據

    學會了多條件查找,你還差一點點,當符合條件的數據同時存在多個時,你知道怎么做嗎?

    比如下面這種一對多查找(下圖gif動態演示)

    E2輸入以下數組公式,按<Ctrl+Shift+Enter>組合鍵

    =INDEX(B:B,SMALL(IF(A$2:A$11=D$2,ROW($2:$11),4^8),ROW(A1)))&''

    一對多查找所有符合條件的數據的萬能公式

    =INDEX(查找結果所在區域,SMALL(IF(條件判斷語句,行號數組,4^8),ROW(A1)))&''

    5 工作日天數統計

    一提到工作日計算,很多HR都皺緊了眉頭,因為各種崗位員工的排班不同,休息日不同,再加上員工的請假和各種節假日的調休......99%的HR都是在手動計算工作日中苦苦煎熬。

    看完下面這個示例的解決方案,以上所有問題一掃而光!

    上圖所示為按照周六周日雙休的企業中,加上自定義節假日的工作日算法

    首先定義名稱,按<Ctrl+F3>打開名稱管理器創建自定義名稱holidays=OFFSET(Sheet1!$E$2,,,COUNTA(Sheet1!$E:$E)-1)

    然后在C2輸入公式:

    =NETWORKDAYS.INTL(A2,B2,1,holidays)

    這樣就輕松計算出起始日期和終止日期之間的工作日天數了

    為了大家能夠舉一反三,方便工作中直接套用,看下面這個萬能公式。

    工作日天數統計的萬能公式

    =NETWORKDAYS.INTL(起始日期,終止日期,周末日weekend,自定義節假日holidays)

    這個萬能公式的關鍵點在于第三參數和第四參數。

    關于第3參數weekend第二種表達方式的幾點說明:

    1、weekend 的表達方式2是一個長度為7的字符串

    2、該字符串中的每個字符代表一周中的一天,從星期一開始

    3、1 代表休息日,0 代表工作日

    4、該字符串中只允許使用字符 1 和 0

    關于第4參數holidays的幾點說明:

    1、是一個包含一個或多個日期的可選集合,這些日期將作為節假日不參與工作日個數統計

    2、可以是包含日期的單元格區域,也可以是區域的引用,還可以是代表日期序列值的數組常量

    3、其中的日期或序列值順序可以任意打亂,不影響統計。

    希望能夠幫到大家輕松應對各種情況的工作日計算!

本站是提供個人知識管理的網絡存儲空間,所有內容均由用戶發布,不代表本站觀點。如發現有害或侵權內容,可拔打24小時舉報電話4000070609 或 在線舉報
來自:hercules028  > excel
舉報
[薦]  原創獎勵計劃來了,萬元大獎等你拿!
猜你喜歡
類似文章
Excel中含金量極高的5個萬能公式
那些年 我曾忽略的EXCEL-sumifs&sumproduct
小胖Excel公式函數
Excel中牛逼的SUMIFS函數實例詳解,不看損失大!
多條件求和統計
我們使用Excel函數中實用之王,那幾個函數
更多類似文章 >>
生活服務
日本三级在线线观看,人妖α片,做爱网站,美女AV,AV日本