Excel 函数公式实例汇总.docx

1、Excel 函数公式实例汇总Excel 2007函数公式实例汇总Excel2007函数公式收集了688个实例，涉及到137个函数、7个行业、41类用途，为大家提供一个参考，拓展思路的机会。公式由包括的为数组公式，在复制粘贴到单元后先去掉然后按住Shift键+Ctrl键再按Enter键，自动生成数组公式。对三组生产数据求和:=SUM(B2:B7,D2:D7,F2:F7) 对生产表中大于100的产量进行求和:=SUM(B2:B11100) *B2:B11) 对生产表大于110或者小于100的数据求和:=SUM(B2:B11110)*B2:B11) 对一车间男性职工的工资求和:=SUM(B2:B10

2、=一车间)*(C2:C10=男)*D2:D10) 对姓赵的女职工工资求和:=SUM(LEFT(A2:A10)=赵)*(C2:C10=女)*D2:D10) 求前三名产量之和:=SUM(LARGE(B2:B10,1,2,3) 求所有工作表相同区域数据之和:=SUM(A组:E组!B2:B9) 求图书订购价格总和:=SUM(B2:E2=参考价格!A$2:A$7)*参考价格!B$2:B$7) 求当前表以外的所有工作表相同区域的总和:=SUM(一月:五月!B2) 用SUM函数计数:=SUM(B2:B9=男)*1)求1累加到100之和:=SUM(ROW(1:100) 多个工作表不同区域求前三名产量和:=SU

3、M(LARGE(CHOOSE(1,2,3,4,5,A组!B2:B9,B组!B2:B9,C组!B2:B9,D组!B2:B9,E组!B2:B9),ROW(1:3) 计算仓库进库数量之和:=SUMIF(B2:B10,=进库,C2:C10) 计算仓库大额进库数量之和:=SUMIF(B2:B8,1000) 对1400到1600之间的工资求和:=SUM(SUMIF(B2:B10,&LARGE(B2:B10,4)+SUMIF(B2:B10,600) 只汇总6080分的成绩:=SUMIFS(B2:B10,B2:B10,=60,B2:B10,25)*1) 汇总一班人员获奖次数:=SUMPRODUCT(B2:B1

4、1=一班)*C2:C11) 汇总一车间男性参保人数:=SUMPRODUCT(A2:A10&B2:B10&C2:C10=一车间男是)*1) 汇总所有车间人员工资:=SUMPRODUCT(-NOT(ISERROR(FIND(车间,A2:A10),C2:C10) 汇总业务员业绩:=SUMPRODUCT(B2:B11=江西,广东)*(C2:C11=男)*D2:D11) 根据直角三角形之勾、股求其弦长:=POWER(SUMSQ(B1,B2),1/2) 计算A1：A10区域正数的平方和:=SUMSQ(IF(A1:A100,A1:A10)根据二边长判断三角形是否为直角三角形:=CHOOSE(SUMSQ(MA

5、X(B1:B3)=SUMSQ(LARGE(B1:B3,2,3)+1,非直角,直角) 计算1到10的自然数的积:=FACT(10) 计算50到60之间的整数相乘的结果:=FACT(60)/FACT(49) 计算1到15之间奇数相乘的结果:=FACTDOUBLE(15) 计算每小时生产产值:=PRODUCT(C2:E2) 根据三边求普通三角形面积:=(PRODUCT(SUM(B1:B3)/2,SUM(B1:B3)/2-LARGE(B1:B3,1,2,3)0.5 根据直角三角形三边求三角形面积:=PRODUCT(LARGE(B1:B3,2,3)/2 跨表求积:=PRODUCT(产量表:单价表!B2)

6、 求不同单价下的利润:=MMULT(B2:B10,G2:H2)*25% 制作中文九九乘法表:=COLUMN()&*&ROW()&=&MMULT(ROW(),COLUMN() 计算车间盈亏:=SUM(MMULT(B3:E50)*B3:E5,1;1;1;1),MMULT(B3:E5=TRANSPOSE(ROW(2:11),B2:B11) 计算每日库存数:=MMULT(N(ROW(2:11)=TRANSPOSE(ROW(2:11),B2:B11-C2:C11) 计算A产品每日库存数:=MMULT(N(ROW(2:17)=TRANSPOSE(ROW(2:17),(B2:B17=A)*(C2:C17-D

7、2:D17)求第一名人员最多有几次:=MAX(MMULT(N(B2:B7=TRANSPOSE(B2:B7),ROW(2:7)0) 求几号选手选票最多:=RIGHT(MAX(MMULT(N(B2:B10=TRANSPOSE(B2:B10),ROW(2:10)0)*100+B2:B10) 总共有几个选手参选:=SUM(1/(MMULT(N(B2:B10=TRANSPOSE(B2:B10),ROW(2:10)0) 在不同班级有同名前提下计算学生人数:=SUM(1/MMULT(N(A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17),ROW(2:17

8、)0) 计 算前进中学参赛人数:=SUM(IFERROR(1/MMULT(N(A2:A17&B2:B17& C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17)*(A2:A17=前进中 学),ROW(2:17)0),0) 串联单元格中的数字:=MMULT(10(COLUMNS(B:K)-COLUMN(C:L),TRANSPOSE(B2:K2)或=SUMPRODUCT(B2:K2,10(COLUMNS(B:K)-COLUMN(B:K)-1) 计算达标率:=MMULT(TRANSPOSE(N(A2:A1160)*(B2:B1160)*(B2:B11& lt;80),ROW(

9、2:11)0) 汇总A组男职工的工资:=MMULT(TRANSPOSE(N(B2:B11&C2:C11=男A组)*D2:D11),ROW(2:11)0) 计算象棋比赛对局次数l:=COMBIN(B1,B2) 计算五项比赛对局总次数:=SUM(COMBIN(B2:B5,2) 预计所有赛事完成的时间:=COMBIN(B1,B2)*B3/B4/60 计算英文字母区分大小写做密码的组数:=PERMUT(B1*2,B2) 计算中奖率:=TEXT(1/PERMUT(B1,B2),0.00%) 计算最大公约数:=GCD(B1:B5) 计算最小公倍数:=LCM(B1:B5)计算余数:=MOD(A2,B2) 汇

10、总奇数行数据:=SUMPRODUCT(MOD(ROW(2:13),2)*C2:C13) 根据单价数量汇总金额:=SUMPRODUCT(MOD(COLUMN(A:I),2)*A2:I2,(MOD(COLUMN(B:J),2)=0)*B2:J2) 设计工资条:=IF(MOD(ROW(),3)=1,单行表头工资明细!A$1,IF(MOD(ROW(),3)=2,OFFSET(单行表头工资明细!A$1,ROW()/3+1,0),) 根据身份证号计算性别:=IF(MOD(MID(B2,15,3),2),男,女) 每隔4行合计产值:=IF(MOD(ROW(),5)=1,SUM(OFFSET(F2,-4,4,

11、),D2*E2) 工资截尾取整:=B2+MOD(一月!B2,10)-MOD(B2+MOD(一月!B2,10),10) 汇总3的倍数列的数据:=SUM(IF(MOD(COLUMN(A:I),3)=0,A2:I10) 将数值逐位相加成一位数:=IF(A2=0,0,MOD(A2-1,9)+1) 计算零钞:5角=INT(MOD(SUM(B2:B10),1)/0.5);2角=INT(MOD(MOD(SUM(B2:B10),1),0.5)/0.2);1角=MOD(MOD(MOD(SUM(B2:B10),1),0.5),0.2)/0.1 秒与小时、分钟的换算:=QUOTIENT(MOD($A2,IF(COL

12、UMN()=2,A2+1,60(3-COLUMN(A:A)+1),60(3-COLUMN(A:A) 生成隔行累加的序列:=QUOTIENT(ROW()+1,2) 根据业绩计算业务员奖金:=CHOOSE(MIN(QUOTIENT(B2,10000)+1,6),0,3%,5%,7%,9%,11%)*B2 计算预报温度与实际温度的最大误差值:=MAX(ABS(C2:C8-B2:B8) 计 算个人所得 税:=ROUND(0.05*SUM(H2-1600-0,500,2000,5000,20000,40000,60000,80000,100000+ABS(H2-1600-0,500,2000,5000,

13、20000,40000,60000,80000,100000)/2,0) 产生100到200之间带小数的随机数:=RAND()*(200-100)+100 产生ll到20之间的不重复随机整数:=RANK(A2:A11,A2:A11)+10 将20个学生的考位随机排列:=INDEX(A$2:A$11,RANK(H2:H11,H2:H11) 将 三个学校植树人员随机分组:=OFFSET(A$1,RANK(G2,G$2:G$11),)&:& OFFSET(B$1,RANK(G2,G$2:G$11),)&:&OFFSET(C$1,RANK(G2,G$2:G$11),) 产生-50到100之间的随机整数

14、:=RANDBETWEEN(-50,100) 产生1到100之问的奇数随机数:=INDEX(IF(MOD(ROW(1:100),2),ROW(1:100),ROW(1:100)-1),RANDBETWEEN(1,100) 产生1到10之间随机不重复数:=LARGE(IF(COUNTIF(A$1:A1,ROW($1:$10)=0,ROW($1:$10),RANDBETWEEN(1,12-ROW() 根据三角形三边长求证三角形是直角三角形:=IF(POWER(MAX(B1:B3),2)=SUM(POWER(LARGE(B1:B3,2,3),2),是,不是) 计算Al:A10区域开三次方之平均值:=

15、AVERAGE(POWER(A1:A10,1/30) 计算Al:A10区域倒数之积:=PRODUCT(POWER(A1:A10,-1) 根据等边三角形周长计算面积:=SQRT(B1/2*POWER(B1/2-B1/3,3) 抽取奇数行姓名:=INDEX(B:B,ODD(RANDBETWEEN(1,ROWS(1:12)-1) 统计A1:B10区域中奇数个数:=SUMPRODUCT(N(ODD(A1:B10)=(A1:B10) 统计参考人数:=SUMPRODUCT(EVEN(COLUMN(A1:J12)=COLUMN(A1:J12)*(MOD(ROW(A1:J12),3)=1)*(A1:J12)

16、计算A1:B10区域中偶数个数:=SUMPRODUCT(N(EVEN(A1:B10)=(A1:B10) 合计购物金额、保留一位小数:=TRUNC(SUMPRODUCT(B2:B10,C2:C10),1) 将每项购物金额保留一位小数再合计:=SUMPRODUCT(TRUNC(B2:B10*C2:C10,1) 将 金额进行四舍六入五单双:=IF(A2-TRUNC(A2,1)=0.06,TRUNC(A2,1)+0.1,TRUNC(TRUNC(A2,1)+0.1)/2,1)*2) 根据重量单价计算金额，结果以万为单位:=TRUNC(SUMPRODUCT(B2:B10,C2:C10),-4)/10000

17、 计算年假天数:=TRUNC(TODAY()-B2)*(TODAY()-B2)=365)/365*5)根据上机时间计算上网费用:=(TRUNC(B2)+(B2-TRUNC(B2)=0.5)*1.5+(MOD(B2,1)0) 成绩表转换:=INDEX($A:$E,CEILING(ROW()*3/5,3)-(COLUMN()=7),MOD(ROW(B2)-1,5)+1) 计算机上网费用:=CEILING(B2,30)/30*2 统计可组建的球队总数:=SUMPRODUCT(FLOOR(B2:B10,5)/5) 统计业务员提成金额，不足20000元忽略:=FLOOR(B2,20000)/20000*

18、500 FLOOR函数处理正负数混合区域:=FLOOR(A1*100,10*(IF(A10,1,-10) 将数据转换成接近6的倍数:=MROUND(A1,6) 以超产80为单位计算超产奖:=SUM(MROUND(B2:B11-700,80*IF(B2:B11=700,1,-1)/80*50 将统计金额保留到分位:=ROUND(SUMPRODUCT(B2:B10,C2:C10),2) 将统计金额转换成以万元为单位:=ROUND(SUMPRODUCT(B2:B10,C2:C10)%,) 对单价计量单位不同的品名汇总金额:=SUM(ROUND(B2:B10*C2:C10*IF(D2:D10=G,10

19、00,1),(D2:D10=G)*2) 将金额保留“角”位，忽略“分”位:=SUM(ROUNDDOWN(B2:B10*C2:C10,1) 计算需要多少零钞:=SUM(ROUNDDOWN(B2:B10*C2:C10,0,-1)*1,-1) 计算值为l万的整数倍数的数据个数:=SUM(N(B2:B10*C2:C10)=ROUNDDOWN(B2:B10*C2:C10,-4) 计算完成工程需求人数:=SUM(ROUNDUP(B2:B11/C2:C11,) 按需求对成绩进行分类汇总:=SUBTOTAL(HLOOKUP(G$1,平均成绩,科目数量,最高成绩,最低成绩,成绩合计;1,2,4,5,9,2,0)

20、,B2:D2) 不间断的序号:=SUBTOTAL(103,$B$2:B2) 仅对筛选出的人员排名次:=CONCATENATE(第,SUM(N(IF(SUBTOTAL(103,OFFSET(优等生!A$1,ROW($2:$31)-2,)=1,$C$2:$C$31,)C2)+1,名) 判断两列数据是否相等: 计算两列数据同行相等的个数:=SUM(N(A1:A10=B1:B10) 计算同行相等且长度为3的个数:=SUM(A1:A10=B1:B10)*(LEN(A1:A10)=3) 提取A产品最后单价:=INDEX(C:C,MAX(B2:B10=A)*ROW(2:10) 判断学生是否符合奖学金发放条件

21、:=AND(B290,C2汉族) 所有裁判都给“通过”就进入决赛:=AND(B2:E2=通过) 判断身份证长度是否正确:=OR(LEN(B2)=15,18) 判断歌手是否被淘汰:=OR(B2:E2=不通过) 根据年龄判断职工是否退休:=OR(AND(B2=男,C260),AND(B2=女,C255) 根据年龄与职务判断职工是否退休:=OR(AND(B2=男,D260+(C2=干部)*3),AND(B2=女,D255+(C2=干部)*3) 没有任何裁判给“不通过”就进行决赛:=NOT(OR(B2:E2=不通过) 计彝成绩区域数字个数:=SUM(NOT(ISERROR(NOT(B2:B11)*1)

22、 评定学生成绩是否及格:=IF(AVERAGE(B2:D2)=60,及格,不及格)根据学生成绩自动产生评语:=IF(AVERAGE(B2:D2)60,不及格,IF(AVERAGE(B2:D2)90,良好,IF(AVERAGE(B2:D2)80000,1000,500) 根据工作时间计算12月工资:=C2+SUM(IF(B20,1,3,5,10,300,500,500,500,500) 合计区域的值并忽略错误值:=SUM(IF(ISERROR(A1:C10),0,A1:C10) 既求积也求和:=IF(D2,PRODUCT(C2:D2),SUM(OFFSET(E2,-3,3) 分 别统计收入和支出

23、:收入=SUM(IF(B2:B130,B2:B13);支出 =SUM(IF(SUBSTITUTE(IF(B2:B13,B2:B13,0),负,-)*1COUNT(B$2:B$11),LARGE(B$2:B$11,ROW(A1) 排除空值:=INDEX($A:$B,SMALL(IF($B$1:$B$11,ROW($1:$11),ROWS($1:$11)+1),ROW(),COLUMN(B2)& 有选择地汇总数据:=SUM(IF(A2:A11=A组,C组,C2:C11) 混合单价求金额合计:=SUM(ROUND(B2:B10*C2:C10*IF(D2:D10=K,1000,1),2) 计算异常停机

24、时间:=SUM(SUBSTITUTE(SUBSTITUTE(IF(C2:C11,C2:C11,0),修机,),换原料,)*1) 计算最大数字行与文本行:=MAX(IF(B:B,ROW(A:A) 找出谁夺冠次数最多:=INDEX(B:B,MIN(IF(MAX(COUNTIF(B2:B12,B2:B12)=COUNTIF(B2:B12,B2:B12),ROW(2:12) 将全角字符转换为半角:=ASC(A2) 计算汉字全角半角混合字符串中的字母个数:=LEN(ASC(A2)*2-LENB(ASC(A2) 将半角字符转换成全角显示:=WIDECHAR(A2) 计算混合字符串中汉字个数:=LEN(A2)-(LENB(WIDECHAR(A2)-LENB(ASC(A2) 判断单元格首字符是否为字母:=OR(AND(CODE(A2)64,CODE(A2)96,CODE(A2)47)* (CODE(MID(A2,ROW(INDIRECT(1:&LEN(A2),1)58) 计算单元格中大写 加小写字母个数:=SUM(CODE(UPPER(MID(A2,ROW(INDIRECT(1:&LEN(A