excel2007函数公式实例汇总

《excel2007函数公式实例汇总》由会员分享，可在线阅读，更多相关《excel2007函数公式实例汇总（47页珍藏版）》请在金锄头文库上搜索。

1、ExcelExcel 20072007 函数公式实例汇总函数公式实例汇总 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)对

2、一车间男性职工的工资求和:=SUM(B2:B10=“一车间“)*(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 之和

3、:=SUM(ROW(1:100)多个工作表不同区域求前三名产量和:=SUM(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,“1;1;1),MMULT(B3:E5=TRANSPOSE(ROW(2:11),B2:B11)计算每日库存数:=MMULT(N(ROW(

4、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-D2: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=TRANSPOS

5、E(B2:B10), ROW(2:10)0)在不同班级有同名前提下计算学生人数: =SUM(1/MMULT(N(A2:A1780),ROW(2:11)0)汇总 A 组男职工的工资:=MMULT(TRANSPOSE(N(B2:B112 角=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(COLUMN()=2,A2+1,60(3- COLUMN(A:A)+1),60(3-COLUMN(A:A)生成隔行累加的序列:=QUO

6、TIENT(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,20000,40000,60000,80000,100000)/2,0)产生 100 到 200 之间带小数的随机数:=RAND

7、()*(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),)1,2,4,5,9,2,0),B2:D2)不间断的序号:=SUBTOTAL(103,$B$2:B2)仅对筛选出的人员排名次:=CONCATENATE(“第 “,SUM(N(IF(SUBTOTAL(103,OFFSET(优等生!A$1,ROW($2:$31)-2,) =1,$C$2:$

8、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)判断学生是否符合奖学金发放条件:=AND(B290,C260),AND(B2=“女“,C255)根据年龄与职务判断职工是否退休:=OR(AND(B2=“男“,D260+(C2=“干部“)*3), AND(B2=“女“,D255+(C2=“干部“)*3)没有任何裁判给“不通过”

9、就进行决赛:=NOT(OR(B2:E2=“不通过“)计彝成绩区域数字个数:=SUM(NOT(ISERROR(NOT(B2:B11)*1)评定学生成绩是否及格:=IF(AVERAGE(B2:D2)=60,“及格“,“不及格“)根据学生成绩自动产生评语:=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(D20,B2:B13);支出 =SUM(IF(S

10、UBSTITUTE(IF(B2:B13“,ROW($1:$11), ROWS($1:$11)+1),ROW(),COLUMN(B2)1;2;3;4;5;6;7;8;9,A2=0女“)将所有数据转换成保留两位小数再求和:=SUM(-TEXT(B2:B11*C2:C11,“0.00“)将货款显示为“万元”为单位:=TEXT(B2,“￥#“负dbnum2G/通 用格式元整;零元整;“),IF(B20,“负“) ;“) ;“), “零角“,IF(ABS(B2)=10.0 倍;00.00%;“)计算字母“A”的首次出现位置，忽略大小写:=TEXT(SEARCH(“a“,A2第“17“),1)将三列数据交

11、换位置:=TEXT(1,-1,0,C1:C5“11!0!0!0;5!0!0;“)计算星期日完工的工程个数:=COUNT(TEXT(B2:B10+C2:C10-1,“AAA“)=“日“)0)计算本月星期日的个数:=SUM(N(TEXT(TODAY()-TEXT(TODAY(),“d“) +ROW(INDIRECT(“1:“;日期有误;“)判断单元格中首字符的类型:=TEXT(IF(AND(CODE(UPPER(A3) 64,CODE(UPPER(A3)0,REPT(“ “,5)REPT(“ “,5-ABS(B2),REPT(“ “,5- ABS(B2)- LEN(B2)/2)重复字 符=IF(C

12、OUNT(SEARCH(“0*0“,A2),0,“) 2;3;4;5;6;7;8;9,B2名 次=RANK(I2,(B2:B11,D2:D11,F2:F11),0)分别统计每个分数段的人员个数:=FREQUENCY(B2:B11,D2:D5)蝉联冠军最多的次数:=MAX(FREQUENCY(ROW(B$2:B$11),(B$2:B$10“错 误“)*ROW(B2:B12)-1)=3)计算小学加初中人数及中专加大学人数:=FREQUENCY(B2:B11“初中“),0)计算文本的频率分布:=FREQUENCY(CODE(B2:B11),CODE(D2:D5)夺 冠排行榜:=IF(ROW(A1)

13、SUM(1/COUNTIF($B$2:$B$11,$B$2:$B$11), “,INDEX($B$2:$B$11,MATCH(LARGE(FREQUENCY(MATCH($B$2:$B$11,$B$2:$B$11,) ,ROW($1:$9)- ROW($1:$10)%,ROW(A1),FREQUENCY(MATCH($B$2:$B$11,$B$2:$B$11,),ROW($1: $9)-ROW($1:$10)%,)谁 蝉联冠军次数最多:=INDEX(B2:B11,MATCH(MAX(FREQUENCY(ROW(2:11), (B2:B10B3:B11)*ROW(2:10),)中国式排名:=SU

14、M(-(IF(FREQUENCY(B$2:B$11,B$2:B$11),B$2:B$11B2)+1谁 获得第二名: =INDEX(A:A,SMALL(IF(B$2:B$11=SMALL(IF(FREQUENCY($B$2:$B$11,$B$2:$B$1 1),$B$2:$B$11),2),ROW($2:$11),1048576),ROW(A1)“B 组“;“C 组“ “!B2:B10“)=MAX(INDIRECT(D$1=TRANSPOSE(ROW(INDIRECT(“1:“二班“;“三班“),1,ROWS(2:11),)计算每个学生总分是否高于本班平均成绩:=SUM(C2:E2) AVERA

15、GE(IF(A2=A$2:A$11),SUBTOTAL(9,OFFSET(B$1,ROW($2:$11)- 1,1,COLUMNS(C:E)计算每个学生进入前三名的科目总数:=SUM(N(RANK(N(OFFSET($B$2,ROW()- 2,COLUMN(B:F)-2,1,1),OFFSET($B$2,0,COLUMN(B:F)- 2,ROWS($2:$11),1)SUBTOTAL(1,OFFSET($B$2,0,COLUMN(B:F)-2,ROWS($2:$11),1)罗 列平均成绩倒数三名的班级: =OFFSET(A1,MATCH(SMALL(SUBTOTAL(1,OFFSET(A1,ROW($2:$9)- 1,1,1,COLUMNS(B:F)*1000+ROW(2:9),ROW(1:3),SUBTOTAL(1,OFFSET(A1,ROW ($2:$9)-1,1,1,COLUMNS(B:F)*1000+ROW(2:9),),)将姓名重复三次:=T(OFFSET(A$1,ROUNDUP(ROW(INDIRECT(“1:“1;1;1;1)*1000+ROW(2:11),ROW(1:10),1000)-1,)计算五个地区销售利润: =TRANSPOSE(MMULT(