從Excel 2007開(kāi)始，它就陸續(xù)有了各種”IFS“類型的函數(shù)，比如Sumifs，Countifs，Averageifs，Maxifs，Minifs，Ifs。這些函數(shù)允許同時(shí)設(shè)定多個(gè)條件，用起來(lái)非常方便。
有些人可能還不知道有這樣的函數(shù)，今天就來(lái)給大家說(shuō)道說(shuō)道。
題外話，其實(shí)有些時(shí)候，并不是函數(shù)或公式有多復(fù)雜、多么地難理解，而是你知不知道。這就和生活中的各種信息不對(duì)稱是一樣一樣地。
在有這些“Ifs”類型的公式之前，比如我們要實(shí)現(xiàn)多條件求和，我們通常要用到Sum+If的數(shù)組公式，或者使用Sumproduct函數(shù)。
有的同學(xué)可能還不會(huì)用數(shù)組公式，在處理實(shí)際問(wèn)題的時(shí)候也會(huì)非常不方便。
Maxifs、Minifs、Ifs是在Office 365中增加的函數(shù)。Excel中沒(méi)有Maxif和Minif，直接就增加了Maxifs和Minifs。
這些”Ifs“們，簡(jiǎn)單地來(lái)理解就是一種可以設(shè)置多個(gè)條件的統(tǒng)計(jì)函數(shù)。下面我們通過(guò)實(shí)例來(lái)看看怎么使用這些函數(shù)。
先看看Maxifs和Minifs函數(shù)
MAXIFS 函數(shù)返回一組給定條件或標(biāo)準(zhǔn)指定的單元格中的最大值。
語(yǔ)法：MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
注意：max_range 和 criteria_rangeN 參數(shù)的大小和形狀必須相同，否則這些函數(shù)會(huì)返回 #VALUE! 錯(cuò)誤。
類似的，MINIFS用來(lái)返回最小值，其他跟MAXIFS一樣。
第一個(gè)參數(shù)max_range就是你要對(duì)哪個(gè)區(qū)域求最大值。
后面的參數(shù)中，每?jī)蓚€(gè)一組，分別代表?xiàng)l件區(qū)域和條件表達(dá)式。
我們來(lái)看一個(gè)例子，下圖是一個(gè)計(jì)劃排產(chǎn)表，我們想用公式來(lái)顯示每個(gè)型號(hào)的產(chǎn)品生產(chǎn)的開(kāi)始日期和結(jié)束日期。
開(kāi)始日期，在J2單元格輸入公式
=MINIFS($B$1:$H$1,B2:H2,'>0')
結(jié)束日期，在K2單元格輸入公式
=MAXIFS($B$1:$H$1,B2:H2,'>0')
公式的第一個(gè)參數(shù)是計(jì)劃排產(chǎn)的日期范圍。
大家知道，日期也是數(shù)值型數(shù)據(jù)的一種，可以直接轉(zhuǎn)換成數(shù)字，可以直接比較大小。
第二個(gè)參數(shù)是計(jì)劃數(shù)量的區(qū)域，計(jì)劃數(shù)量大于0，就表示這一天安排生產(chǎn)了。
所以用公式找到計(jì)劃數(shù)量大于0的日期中的最小值和最大值，就是我們排產(chǎn)的開(kāi)始日期和結(jié)束日期。
假如我們想要增加多個(gè)條件，就按照條件區(qū)域和條件作為一組來(lái)增加到公式里就可以了。
下面我們來(lái)說(shuō)說(shuō)IFS函數(shù)
語(yǔ)法：IFS([Something is True1, Value if True1, [Something is True2, Value if True2],…[Something is True127, Value if True127])
這個(gè)函數(shù)也是從公式的字面意思我們不難看出，如果第一個(gè)條件的結(jié)果是True的話，就返回后面跟著的這個(gè)值；否則就開(kāi)始檢查第二個(gè)條件，如果第二個(gè)條件的結(jié)果是True的話，就返回第二個(gè)條件后面跟著的值；依此類推...如果到最后一個(gè)條件都檢查完了還是沒(méi)有返回結(jié)果的話，就返回錯(cuò)誤值#N/A。
為了避免出現(xiàn)公式結(jié)果出現(xiàn)#N/A錯(cuò)誤值，我們可以把最后一個(gè)條件直接設(shè)成True （劃重點(diǎn)），后面接著顯示所有條件之外的返回結(jié)果。
如下圖示例，我們需要根據(jù)學(xué)生分?jǐn)?shù)劃分等級(jí)，在C2單元格輸入以下公式：
=IFS(B2>=90,'優(yōu)秀',B2>=80,'良',B2>=60,'及格',TRUE,'不及格')
大家看，倒數(shù)第二個(gè)參數(shù)直接就是True，就是用來(lái)表示，如果前面的所有條件都不滿足，就顯示”不及格“這個(gè)結(jié)果。
最后是Sumifs、Countifs、Averageifs
Sumifs語(yǔ)法：
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Countifs語(yǔ)法：
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)
Averageifs語(yǔ)法：
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
從語(yǔ)法上來(lái)看，Sumifs和Averageifs跟前面提到的Maxifs和Minifs相似，第一個(gè)參數(shù)都是統(tǒng)計(jì)的數(shù)值區(qū)域，后面的參數(shù)就是每?jī)蓚€(gè)一組，分別代表?xiàng)l件區(qū)域和條件表達(dá)式。
而Countifs就不一樣了，直接就是每?jī)蓚€(gè)參數(shù)作為一組，分別代表?xiàng)l件區(qū)域和條件表達(dá)式。
如下圖所示的數(shù)據(jù)，我們需要統(tǒng)計(jì)“啤酒”“已開(kāi)票”的數(shù)量、行數(shù)、平均值。
數(shù)量：在H2中輸入公式
=SUMIFS(C2:C11,A2:A11,E2,B2:B11,F2)
行數(shù)：在H5中輸入公式
=COUNTIFS(A2:A11,E5,B2:B11,F5)
平均值：在H8中輸入公式
=AVERAGEIFS(C2:C11,A2:A11,E8,B2:B11,F8)
在Sumifs中C2:C11是統(tǒng)計(jì)的數(shù)值區(qū)域，A2:A11是第一個(gè)條件區(qū)域，E2是第一個(gè)條件區(qū)域?qū)?yīng)的篩選值；B2:B11是第二個(gè)條件區(qū)域，F(xiàn)2是第二個(gè)條件區(qū)域的對(duì)應(yīng)的篩選值。Averageifs跟Sumifs類似。
在Countifs中沒(méi)有統(tǒng)計(jì)的數(shù)值區(qū)域，A2:A11是第一個(gè)條件區(qū)域，E5是第一個(gè)條件區(qū)域?qū)?yīng)的篩選值；B2:B11是第二個(gè)條件區(qū)域，F(xiàn)5是第二個(gè)條件區(qū)域的對(duì)應(yīng)的篩選值。
好了，“Ifs”類型的函數(shù)就介紹完了。
總的來(lái)說(shuō)，這幾個(gè)函數(shù)都不難，比較容易理解，大家練習(xí)幾遍應(yīng)該就可以完全掌握。關(guān)鍵點(diǎn)還是在于，首先大家要知道有這樣的函數(shù)并掌握，在遇到實(shí)際問(wèn)題時(shí)才能靈活運(yùn)用，少走彎路。
本文由公眾號(hào) Excel輕松學(xué) 友情推薦
聯(lián)系客服
