Excel中最常用的10个函数精讲(完整版)


TOP1:SUM函数


=SUM(N(D3:D7=B11))

=SUM((D3:D7=B11)*(F3:F7))

TOP2:IF函数

=IF(D3>C3,"是","否")

=IF(C3<60,"C",IF(C3<80,"B","A"))

TOP3:LOOKUP函数
向量形式:LOOKUP(lookup_value, lookup_vector, [result_vector])
数组形式:LOOKUP(lookup_value, array)
向量形式 =LOOKUP(C3,$F$7:$F$9,$G$7:$G$9)

数组形式 =LOOKUP(C3,$F$7:$G$9)

=LOOKUP(1,0/($B$3:$B$11=F3),$C$3:$C$11)

TOP4:VLOOKUP函数


=VLOOKUP(J4,$C$3:$D$12,2,0)

=VLOOKUP(C17&D17,CHOOSE({1,2},$C$4:$C$14&$D$4:$D$14,$F$4:$F$14),2,0)

VLOOKUP基础入门-案例大全 VLOOKUP进阶学习-案例大全 视频详解VLOOKUP如何实现1对多查找! 20多个经典案例:视频教程(扫码学习)

TOP5:MATCH函数


=MATCH(B3,$E$2:$E$5,1)

=SUM(N(MATCH(B3:B9,B3:B9,)=ROW(B3:B9)-2))

TOP6:CHOOSE函数

=CHOOSE(RANDBETWEEN(1,4),"张三","李四","王五","赵六")

=CHOOSE(ROUNDUP(MONTH(B3)/3,),"第一季度","第二季度","第三季度","第四季度")

=VLOOKUP(B16,CHOOSE({1,2},$C$3:$C$12,$B$3:$B$12),2,)


TOP7:DATE函数


=DATE(YEAR(B4),CEILING(MONTH(B4),3)+{-2,1},{1,0})

TOP8:DAYS 函数




TOP9:FIND、FINDB 函数




=-LOOKUP(1,-MID(B3,MIN(IFERROR(FIND(ROW($1:$10)-1,B3),99)),ROW($1:$99)))

TOP10:INDEX函数


=INDEX($C$3:$E$10,MATCH($B16,$B$3:$B$10,),COLUMN(A1))



赞 (0)