EXCEL的公式

滨海市高二物理EXCEL的公式

（按班级汇总）考试学生数: “=COUNTIFS(成绩单!$A$2:$A$950,按班级汇总!$A2,成绩单!$B$2:$B$950,按班级汇总!$B2)”

最高分: “=MAX((成绩单!$A$2:$A$950=按班级汇总!$A2)*(成绩单!$B$2:$B$950=按班级汇总!$B2)*成绩单!$D$2:$D$950)”，按组合键完成输入。

最低分: “=MIN(IF((成绩单!$A$2:$A$950=按班级汇总!$A2)*(成绩单!$B$2:$B$950=按班级汇总!$B2),成绩单!$D$2:$D$950))”，按++组合键完成输入。

平均分: “=AVERAGEIFS(成绩单!$D$2:$D$950,成绩单!$A$2:$A$950,按班级汇总!$A2,成绩单!$B$2:$B$950,按班级汇总!$B2)”，按键完成输入。

客观题平均分：“=SUM(小分统计!$C2:$AP2)”，按键完成输入

主观题平均分：“=SUM(小分统计!$AQ2:$AZ2)”，按键完成输入

（按学校汇总）考试学生数：“=COUNTIF(成绩单!$A$2:$A$950,按学校汇总!$A2)”，按

最高分：“=MAX((成绩单!$A$2:$A$950=按学校汇总!$A2)*成绩单!$D$2:$D$950)”，按键完成输入

最低分: “=MIN(IF(成绩单!$A$2:$A$950=按学校汇总!$A2,成绩单!$D$2:$D$950))”，按键

平均分: “=AVERAGEIFS(成绩单!$D$2:$D$950,成绩单!$A$2:$A$950,按学校汇总!$A2)”，按

客观题平均分：“=SUM((按班级汇总!$A$2:$A$33=按学校汇总!$A2)*(按班级汇总!$C$2:$C$33)*(按班级汇总!$G$2:$G$33))/$B2”，按键完成输入

主观题平均分：“=SUM((按班级汇总!$A$2:$A$33=按学校汇总!$A2)*(按班级汇总!$C$2:$C$33)*（按班级汇总!$H$2:$H$33))/$B2”，按键完成输入

【1】得分率：“=SUM((小分统计!$A$2:$A$33=$A2)*小分统计!C$2:C$33*按班级汇总!$C$2:$C$33)/$B2/分值表!B$3”，按键完成输入，拖动填充句柄，横向填充到BE2单元格，然后从BE2单元格，拖动填充柄，纵向填充到BE5单元格。

（10）选择H2:BE5单元格区域，单击鼠标右键，在快捷菜单中选择“设置单元格格式”，打开“设置单元格格式”对话框，在“分类”列表中选择“百分比”，小数位数为2，单击“确定”按钮。

（11）选择E2:G5单元格区域，单击鼠标右键，在快捷菜单中选择“设置单元格格式”，打开“设置单元格格式”对话框，在“分类”列表中选择“数值”，小数位数为2，单击“确定”按钮。

销售达标率：“=COUNTIF(表1[一月份],\">60000\") /44”

销售第一名业绩：=LARGE(表1[一月份],1)

金额，折扣：：“=D4*E4*(1-IF(D4>=2000,IF(C4=\"产品A\折扣表!$C$6,IF(C4=\"产品B\折扣表!$D$6,折扣表!$E$6)),IF(D4>=1500,IF(C4=\"产品A\折扣表!$C$5,IF(C4=\"产品B\折扣表!$D$5,折扣表!$E$5)),IF(D4>=1000,IF(C4=\"产品A\折扣表!$C$4,IF(C4=\"产品B\折扣表!$D$4,折扣表!$E$4)),IF(C4=\"产品A\折扣表!$C$3,IF(C4=\"产品B\折扣表!$D$3,折扣表!$E$3))))))”

（2）在“大额订单”工作表中，在A1单元格中输入“类型”，在B1单元格中输入“数量”，在A2单元格中输入“产品A”，在B2单元格中输入“>1500”；在C1单元格中输入“类型”，在D1单元格中输入“数量”，在C3单元格中输入“产品B”，在D3单元格中输入“>1900”；在E1单元格中输入“类型”；在F1单元格中输入“数量”，在E4单元格中输入“产品C”，在F4单元格中输入“>1500”。（3）在“数据”选项卡下，选择“排序和筛选”项的“高级”按钮，弹出“高级筛选”对话框，在“方式”下选中“将筛选结果复制到其他位置”单选按钮，在“列表区域”选择“销售记录”工作表中的C3到F891单元格区域，显示为“销售记录!$A$3:$F$891”；在“条件区域”选择“大额订单”工作表中的A1到C4单元格区域，显示为“大额订单!$A$1:$F$4”；“复制到”选择“大额订单”工作表中的A6单元格，显示为“大额订单!$A$6”。单击“确定”按钮即可。

性别男女： =IF(ISODD(MID([@身份证号码],17,1)),\"男\女\")

出生日期：“=MID(E2,7,4)&\"年\"&MID(E2,11,2)&\"月\"&MID(E2,13,2)&\"日\"

年龄：“=DATEDIF(TEXT(MID(E2,7,8),\"#-00-00\"),\"2015-09-30\

月工龄工资:

=IF(K2>=30,(K2-29)*50+20*30+9*20,IF(K2>=10,(K2-9)*30+9*20,IF(K2>0,K2*20,0)))

基本月工资： =SUM(L2:M2) 填充名字： =VLOOKUP(A4,员工基础档案!A:B,2)

应交个税： =IF(F4>80000, 45%,IF(F4>55000,

35%,IF(F4>35000,30%,IF(F4>9000,25%,IF(F4>4500,20%,IF(F4>1500,10%,3%))))))*E4-VLOOKUP(IF(F4>80000,45%,IF(F4>55000,35%,IF(F4>35000,30%,IF(F4>9000,25%,IF(F4>4500,20%,IF(F4>1500,10%,IF(F4>0,3%,0))))))),个人所得税税率!$D$4:$E$11,2,0)

工资条： =IF(MOD(ROW(),3),OFFSET('12月工资表

'!$A$3,(MOD(ROW()-1,3)>0)*ROUND(ROW()/3,0),COLUMN(A3)-1,),\"\")

按组合键进入将{ MERGEFIELD 语文 }修改为{ MERGEFIELD 语文 \\#0.00}（4）

完成情况：=IF(AND(F3=\"是\是\是\"),\"完成\未完成\")

报告奖金： =IF(D3<=1000,100,IF(D3<=2800,D3*8%,D3*10%))+IF(I3=\"完成\

撰写报告数：=COUNTIF(表1[责任人],员工个人情况统计!$A3)

修改次数为0：=COUNTIFS(表1[责任人],员工个人情况统计!$A3,表1[报告修改次数],0)

报告奖金总计： =SUMIF(表1[责任人],员工个人情况统计!$A3,表1[报告奖金])

删除“姓名”列中所有汉语拼音字母，只保留汉字： =LEFT(C3,LENB(C3)-LEN(C3))，写在另外一个列，比如N列

等级： =IF(MIN(G3:L3)<60,\"不合格\合格\良\优\")))

收入种类： =VLOOKUP(LEFT([@减免性质代码],2),代码!$B$5:$C$22,2,FALSE)

减免政策大类： =VLOOKUP(MID([@减免性质代码],3,2),代码!$E$5:$F$15,2,FALSE)

减免政策小类： =VLOOKUP(MID([@减免性质代码],5,2),代码!$H$5:$I$49,2,FALSE)

年份： =IFERROR(TEXT(MID([@政策名称],FIND(\"〔\政策名称],1)+1,4),\"0年\"),\"\")

两季度销售情况：（产品销售汇总表）：

一季度销量：“=SUMIF(一季度销售情况表!$B$2:$B$44,B2,一季度销售情况表!$D$2:$D$44)”

一季度销售额：“=SUMIF(一季度销售情况表 !$B$2:$B$44,B2,一季度销售情况表 !$F$2:$F$44)” 二季度同上

请通过函数提取每个业务员所在的地区并按下列对应关系填写在“地区”列中：

“业务编号”的3、4位对应区域，01北京，02上海，03深圳：“=IF(MID(A3,4,1)=\"1\北京\上海\深圳\"))”

如果每订单的图书销量超过40本（含40本），则按照图书单价的9.3折进行销售；否则按照图书单价的原价进行销售。按照此规则，计算并填写“订单明细”工作表中每笔订单的“销售额小计”: “=PRODUCT(IF([@销量（本）]<40,[@单价],[@单价]*0.93),[@销量（本）])”

东南西北区域：“=VLOOKUP(LEFT([@发货地址],3),表3,2,FALSE)”

停车收费：收费标准：“=VLOOKUP(C2,收费标准!$A$3:$B$5,2,0)”

停放时间：“=TEXT((H2-F2)*24+I2- G2,\"h:mm\")”

收费金额：“=E2*ROUNDUP(((HOUR(J2)*60+MINUTE(J2))/15),0)”

拟收费金额：“=E2*ROUNDDOWN(((HOUR(J2)*60+MINUTE(J2))/15),0)”

月总支出额中大于月均总支出110%的数据：“=$M$15*1.1”

对应季度：“=LOOKUP(MONTH(A3),{1;4;7;10},{\"1\";\"2\";\"3\";\"4\季度\")”

2013年图书销售分析：“=SUMIFS(销售订单!$G$3:$G$678,销售订单!$B$3:$B$678,\">=\"&DATE(2013,1,1),销售订

单!$B$3:$B$678,\"<=\"&DATE(2013,1,31),销售订单!$D$3:$D$678,A4)”（1月到12月公式一样）

法律一二三四班公式：“=LOOKUP(MID(B3,3,2),{\"01\法律一班\法律二班\法律三班\法律四班\”

星期六星期日是否加班：IF(OR(WEEKDAY(A3)=1,WEEKDAY(A3)=7),\"是\否\")

男女公式：“=IF(ISODD(MID([@身份证号码],17,1)),\"男\女\")”

身份证号码：“=MID([@身份证号码],7,4) & \"年\" & MID([@身份证号码],11,2) & \"月\" & MID([@身份证号码],13,2) & \"日\"”

年龄：“=INT((TODAY()-[@出生日期])/365)”

排名：“=\"第\"&RANK.EQ(F2,F$2:F$45)&\"名\"”

应交个人所得税

“=IF(K3>80000,K3*0.45-13505,IF(K3>55000,K3*0.35-5505,IF(K3>35000,K3*0.3-2755,IF(K3>9000,K3*0.25-1005,IF(K3>4500,K3*0.2-555,IF(K3>1500,K3*0.1-105,K3*0.03))))))”