EXCEL笔试题

1.计算今天到“2019/11/11”的天数

=DATEDIF(TODAY(),DATE(2019,11,11),"D")
=DATEDIF(TODAY(),DATEVALUE("2019/11/11"),"D")

2.本月末日期

=EOMONTH(TODAY(),0)

3.提取日期的年月日

=TEXT(DATE(2019,10,25),"yyyy")
=TEXT(DATE(2019,10,25),"mm")
=TEXT(DATE(2019,10,25),"dd")

4.语文科目平均分

=AVERAGEIF(B2:B10,"语文",C2:C10)

5. 孙同学除体育成绩外的成绩总和

=SUMIFS(C2:C10,A2:A10,"孙")-SUMIFS(C2:C10,A2:A10,"孙",B2:B10,"英语")

6.若A为真，返回1；A假B真，返回2,；否则，返回-2

=IF(A,1,IF(B,2,-2))

7.利用Vlookup函数反向透视

=VLOOKUP("xm100001",IF({1,0},C2:C6,A2:A6),2,0)