1、IF函數(shù)
用來完成非此即彼的判斷。
如下圖,要判斷成績是否合格。
公式:
=IF(C2>=60,'及格','不及格')
用法:
=IF(判斷條件,符合條件時返回的值,不符合條件時返回的值)
2、SUMIF函數(shù)
按指定條件求和,報表匯總中的江湖一姐。
如下圖,要在信息表中計算指定部門的總成績。
公式:
=SUMIF(B:B,G3,E:E)
常規(guī)用法:
=SUMIF(條件區(qū)域,指定的條件,求和區(qū)域)
這個函數(shù)有兩個特點:
一是指定條件時,可以使用通配符。
例如:=SUMIF(B:B,'*亞',E:E)
不管B列是“櫻井莉亞”還是“瑪利亞”,只要包含字符'亞',就對E列對應單元格中的數(shù)值進行求和匯總。
二是求和區(qū)域和條件區(qū)域要大小一致,并且要注意兩者的起始位置需保持一致。
3、COUNTIF
統(tǒng)計符合指定條件的單元格個數(shù),看家本領就是按條件計數(shù)。
如下圖,要在信息表中計算蒼老師的課時數(shù)。
公式:
=COUNTIF(C2:C10,E3)
常規(guī)用法:
=COUNTIF(條件區(qū)域,指定的條件)
4、常規(guī)查詢
如下圖所示,要根據(jù)G2單元格姓名,在A~E數(shù)據(jù)區(qū)域中查詢對應的年齡。
公式為:
=VLOOKUP(G2,B1:E6,4,0)
使用方法是:
=VLOOKUP(要找誰,在哪個區(qū)域找,找哪一列的內(nèi)容,匹配公式)
注意第三參數(shù),是指定要返回查詢區(qū)域中第幾列的內(nèi)容,不是整個工作表的第幾列。
5、VLOOKUP+MATCH
常用于不確定列數(shù)的數(shù)據(jù)查詢。
如下圖所示,要根據(jù)B13單元格的姓名,在數(shù)據(jù)表中查詢對應的項目。
C13單元格公式為:
=VLOOKUP(B13,A1:G9,MATCH(C12,1:1,),0)
如果數(shù)據(jù)表的列數(shù)非常多,在使用VLOOKUP函數(shù)時,還需要掰手指頭算算查詢的項目在數(shù)據(jù)表中是第幾列,真是麻煩的很。
現(xiàn)在好了,先用MATCH函數(shù)來查詢項目所在是第幾列,然后VLOOKUP函數(shù)就根據(jù)MATCH函數(shù)提供的情報,返回對應列的內(nèi)容。
6、逆向查詢
如下圖所示,要根據(jù)G2單元格姓名,在A~E數(shù)據(jù)區(qū)域中查詢對應的工號。
公式為:
=LOOKUP(1,0/(G2=B2:B6),A2:A6)
使用方法為:
=LOOKUP(1,0/(條件區(qū)域=指定條件),要返回的區(qū)域)
可以根據(jù)需要,將公式中的 0/(條件區(qū)域=指定條件),寫成:
0/((條件區(qū)域1=指定條件1)*(條件區(qū)域2=指定條件2)*……)
從而實現(xiàn)任意角度的多條件查詢。
7、查詢好搭檔
如下圖所示,要根據(jù)H2單元格姓名,查詢所在的部門。
公式為:
=INDEX(B1:F1,MATCH(H2,B2:F2,))
由MATCH函數(shù)找到查詢值的精確位置,然后由INDEX函數(shù)返回指定區(qū)域中,對應位置的內(nèi)容。可以實現(xiàn)上下左右任意方向的查詢。
8、年齡計算
如下圖所示,要根據(jù)C列的出生年月計算年齡。
公式為:
=DATEDIF(C2,TODAY(),'y')
DATEDIF函數(shù)常用于計算工齡、年齡等日期間隔。
第一參數(shù)是開始日期,第二參數(shù)是結束日期,第三參數(shù)是返回的數(shù)據(jù)類型。使用Y,表示返回整年數(shù)。使用M,則表示返回整月數(shù)。
9、不允許錄入重復數(shù)據(jù)
COUNTIF函數(shù)結合數(shù)據(jù)驗證功能,可以拒絕錄入重復的數(shù)據(jù),如果要輸入大量的員工姓名,這種方法特別實用。
如下圖所示,數(shù)據(jù)驗證中的公式為:
=COUNTIF($D$2:D2,D2)=1
數(shù)據(jù)驗證可以根據(jù)預先指定的條件,對輸入的內(nèi)容進行自動判斷,拒絕不符合條件的內(nèi)容輸入。
實際使用的時候,公式中的D2需要換成實際選中數(shù)據(jù)區(qū)域的首個單元格,比如你選中的區(qū)域是A2:A20,公式就寫成:
=COUNTIF($A$2:A2,A2)=1
好了,今天咱們的內(nèi)容就是這些吧,祝各位一天好心情!
圖文制作:祝洪忠
專業(yè)的職場技能充電站
聯(lián)系客服