文档介绍:
小李今年毕业: (图书名称) =VLOOKUP(D3,编号对照!$A$2:$C$19,2,FALSE)
(订单明细工作表) =VLOOKUP(D3,编号对照!$A$2:$C$19,3,FALSE)
SUMIFS=(求和区域,条件区域1,条件1, [条件区域2,条件2], ...)
小蒋是一位中学老师:(班级) = MID(A2,4,1)&"班"
某企业拟对其产品:(销售额排名) =RANK(D2,$D$2:$D$21,0)
财务部助理小王:(是否加班) =IF(WEEKDAY(A3,2)>5,"是","否")
(地域) =LEFT(C3,3)
(费用类别) =VLOOKUP(E3,费用类别!$A$2:$B$12,2,FALSE)
(二季度北京差旅费)=SUMIFS(费用报销管理!G3:G401,费用报销管理!A3:A401,">=-04-01",费用报销管理!A3:A401,"<=-06-30",费用报销管理!D3:D401,"北京市")
(钱顺卓报销火车票)=SUMIFS(费用报销管理!G3:G401,费用报销管理!B3:B401,"钱顺卓",费用报销管理!F3:F401,"火车票")
(飞机票所占百分比)=SUMIF(费用报销管理!F3:F401,"飞机票",费用报销管理!G3:G401)/SUM(费用报销管理!G3:G401)
(周末补助总额)=SUMIFS(费用报销管理!G3:G401,费用报销管理!H3:H401,"是",费用报销管理!F3:F401,"通讯补助")
文涵是大地企业:(销售额) =VLOOKUP(D4,商品均价,2,0)*E4
小李是东方企业会计:(应交个人所得税) = IF(K3<=1500,K3*3/100,IF(K3<=4500,K3*10/100-105,IF(K3<=9000,K3*20/100-555,IF(K3<=35000,K3*25%-1005,IF(K3<=55000,K3*30%-2755,IF(K3<=80000,K3*35%-5505,IF(K3>80000,K3*45%-13505)))))))
小李是北京某政法学院:(年级排名) =RANK(M3,M$3:M$102, 0)
(班级) =IF(MID(B3,3,2)="01","法律一班",IF(MID(B3,3,2)="02","法律二班",IF(MID(B3,3,2)="03
销售部助理小王:(图书编号) =VLOOKUP(D3,图书编目表!$A$2:$B$9,2,FALSE)
期末考试结束了:(性别) =IF(MOD(MID(C2,17,1),2)=1, "男","女")
(出生日期) =MID(C2,7,4)&"年"&MID(C2,11,2)&"月"&MID(C2,13,2)&"日"
(年纪) =INT((TODAY()-E2)/365)
“=IF(F2>=90,"优异",IF(F2>=75,"良好",IF(F2>=60,"及格",IF(F2>60,"及格","不及格"))
内容来自淘豆网www.taodocs.com转载请标明出处.