【excel笔记二】2012-11-29_henry_dx_新浪博客

学习佛山小老鼠说函数笔记
http://club.excelhome.net/thread-933566-1-1.html

实例一：求出成绩等级

=LOOKUP(L14,{0,60,70,80;"不及格","及格","良好","优秀"})

 

扩展：分区间对应，只针对数值型

=SUM(IF((A2>{0,30,50,80,110,160,210,260,330})+(A2<={30,50,80,110,160,210,260,330,100000000})=2,

{2,3,4,5,6,7,8,9,10},0))

 

实例二：提取一行中最后一个非空单元格的数据

=LOOKUP(1,0/(C2:K2<>""),C2:K2)

 

实例三、没有排序怎样用lookup查找

=LOOKUP(1,0/($B$39:$B$44=$H39),C$39:C$44)

 

实例四、根据身份证自动显示性别

=IF(A2="","",TEXT(-1^MID(A2,15,3),"女;男"))

 

实例五、求表一区域F42:G44大于10数据和

{=SUM((F42:G44>10)*(F42:G44))}

 

实例六、把小写金额转为大写金额

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(F10*100,"[Dbnum2]0百0拾0元0角0分"),"

零百",""),"零拾",""),"零元",""),"零角",""),"零分","")

 

实例七、从文本里提取数字

=--MID(D7,MIN(FIND(ROW($A$1:$A$10)-1,D7&5/19,1)),LEN(D7)-(LENB(D7)-LEN(D7)))

 

实例八、去重复值公式
=IF(SUM(1/COUNTIF($C$13:$C$25,$C$13:$C$25))>=ROW(A1),INDEX($C$13:$C$25,SMALL(IF(MATCH

($C$13:$C$25,$C$13:$C$25,0)=ROW($C$13:$C$25)-12,ROW($C$13:$C$25)-12,""),ROW(A1))),"")

 

实例九、查找有重复名字的数据

=IF(COUNTIF($A$6:$A$20,$G$6)>=ROW(A1),INDEX($A$6:$D$20,SMALL(IF($A$6:$A$20=$G$6,ROW($A$6:$A$20)-

5,2^20),ROW(A1)),COLUMN(B1)),"")

 

附加一、含有某个字眼的多条件求和

=SUMIFS($D$4:$D$19,$B$4:$B$19,$B24,$C$4:$C$19,"*"&D$23&"*")

 

附加二、多条件求和

=SUM(SUMIFS($C$3:$C$10,$B$3:$B$10,{"a","c"}))