1、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=“一车间“)*(C2:C10=“
2、男“)*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)多个工作表不同区域求前三名产量和:=SUM(LA
3、RGE(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(2:11)=TRANSPOSE(ROW(2:11),B2:B11-C2:C11)计
4、算 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=TRANSPOSE(B2:B10),ROW(2:10)0)在不同班级有同名前提下计算学生人数:=SUM(1
5、/MMULT(N(A2:A171,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:$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:
6、B10=“A“)*ROW(2:10)判断学生是否符合奖学金发放条件:=AND(B290,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)评定学生成绩是否及格:=IF(AVERAGE(B2:D2)=60,“及格“,“不及格“)根据学生成绩自动产生评语:=IF(AVERAGE(B2:D2)8
7、0000,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(SUBSTITUTE(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“)将货款显示
8、为“万元”为单位:=TEXT(B2,“￥#“负dbnum2G/通用格式元整 ;零元整;“),IF(B20,“ 负“);“);“),“零角“,IF(ABS(B2)=10.0 倍;00.00%;“)计算字母“A”的首次出现位置，忽略大小写:=TEXT(SEARCH(“a“,A2第“17“),1)将三列数据交换位置:=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(I
9、NDIRECT(“1:“;日期有误;“)判断单元格中首字符的类型:=TEXT(IF(AND(CODE(UPPER(A3)64,CODE(UPPER(A3)0,REPT(“ “,5)&“ “&REPT(“,ABS(B2)&B2&REPT(“ “,5-ABS(B2),REPT(“ “,5-ABS(B2)-LEN(B2)/2)&B2&REPT(“,ABS(B2)&“&REPT(“ “,5)利用公式制作带轴的图表且标示升降:=IF(A20,REPT(“ “,4+MAX(ABS(B$2:B$8)&IF(ROW()=2,“ “,IF(B2=OFFSET(B2,-1,0),“ “,IF(B2OFFSET(B
10、2,-1,0),“,“ “)&REPT(“,ABS(B2)&B2&REPT(“ “,4+MAX(ABS(B$2:B$8)-ABS(B2),REPT(“ “,4+MAX(ABS(B$2:B$8)-ABS(B2)-LEN(B2)/2)&B2&REPT(“,ABS(B2)&IF(ROW()=1,“ “,IF(B2=OFFSET(B2,-1,0),“ “,IF(B2OFFSET(B2,-1,0),“,“ “)&REPT(“ “,4+MAX(ABS(B$2:B$8)计算单元格中数字个数:=LEN(A2)*2-LENB(A2)将数字倒序排列:=TEXT(SUM(MID(A2,ROW(INDIRECT(“1
11、:“&LEN(A2),1)*10(ROW(INDIRECT(“1:“&LEN(A2)-1),REPT(0,LEN(A2)计算购物金额中小数位数最多是几:=MAX(LEN(B2:B10*C2:C10)-LEN(INT(B2:B10*C2:C10)-1计算英文句子中有几个单词:=LEN(A2)-LEN(SUBSTITUTE(SUBSTITUTE(A2,“,“ “),“ “,“)+1将英文句子规范化:=PROPER(LEFT(A2)&TRIM(RIGHT(A2,LEN(A2)-1)分别提取省市县名:=TRIM(MID(SUBSTITUTE($A2,“/“,REPT(“ “,100),COLUMN(A
12、2)*100-99,100)提取英文名字:=LEFT(A2,FIND(“ “,A2)-1)将分数转换成小数:=(LEFT(A2,FIND(“/“,A2)-1)+RIGHT(A2,LEN(A2)-FIND(“/“,A2)/2从英文短句中提取每一个单词:=IFERROR(MID($A2,FIND(“,SUBSTITUTE(“ “&$A2&“ “,“ “,“,COLUMN(A2),FIND(“,SUBSTITUTE(“ “&$A2&“ “,“ “,“,COLUMN(B2)-FIND(“,SUBSTITUTE(“ “&$A2&“ “,“ “,“,COLUMN(A2),“)将单位为“双”与“片”混合的数
13、量汇总:=SUM(IF(ISNUMBER(FIND(“/“,C2:C9),(LEFT(C2:C9,FIND(“/“,C2:C9)-1)+RIGHT(C2:C9,LEN(C2:C9)-FIND(“/“,C2:C9)/2,C2:C9*IF(B2:B9=“片“,0.5,1)提取工作表名:=RIGHT(CELL(“filename“),LEN(CELL(“filename“)-FIND(“,CELL(“filename“)根据产品规格计算产品体积:=PRODUCT(LEFT(B2,FIND(“*“,B2)-1),MID(B2,FIND(“*“,B2)+1,FIND(“*“,B2,FIND(“*“,B2)+1)-1-FIND(“*“,B2),RIGHT(B2,LEN(B2)-FIND(“*“,B2,FIND(“*“,B2)+1)提取括号中的字符串:=IFERROR(MID(A2,FIND(“(“,A2)+1,FIND(“)“,A2)-FIND(“(“,A2)-1),“)
