Excel2007函数公式应用实例

Excel2007函数公式应用实例,

"涉及到137个函数、7个行业、41类用途，为大家提供一个参考，拓展思路的机会。

公式由{}包括的为数组公式，在复制粘贴到单元后先去掉{}然后按住Shift键+Ctrl键再按Enter键，自动生成数组公式。",

例1,"对三组生产数据求和:=SUM(B2:B7,D2:D7,F2:F7)"

例2,对生产表中大于100的产量进行求和:{=SUM((B2:B11>100)*B2:B11)}

例3,对生产表大于110或者小于100的数据求和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)}

例4,"对一车间男性职工的工资求和:{=SUM((B2:B10=""一车间"")*(C2:C10=""男"")*D2:D10)}"

例5,"对姓赵的女职工工资求和:{=SUM((LEFT(A2:A10)=""赵"")*(C2:C10=""女"")*D2:D10)}"

例6,"求前三名产量之和:=SUM(LARGE(B2:B10,{1,2,3}))"

例7,求所有工作表相同区域数据之和:=SUM(A组:E组!B2:B9)

例8,求图书订购价格总和:{=SUM((B2:E2=参考价格!A$2:A$7)*参考价格!B$2:B$7)}

例9,求当前表以外的所有工作表相同区域的总和:=SUM(一月:五月!B2)

例10,"用SUM函数计数:{=SUM((B2:B9=""男"")*1)}"

例11,求1累加到100之和:{=SUM(ROW(1:100))}

例12,"多个工作表不同区域求前三名产量和:{=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)))}"

例13,"计算仓库进库数量之和:=SUMIF(B2:B10,""=进库"",C2:C10)"

例14,"计算仓库大额进库数量之和:=SUMIF(B2:B8,"">1000"")"

例15,"对1400到1600之间的工资求和:{=SUM(SUMIF(B2:B10,""<=""&{1400,1600})*{-1,1})}"

例16,"求前三名和后三名的数据之和:=SUMIF(B2:B10,"">""&LARGE(B2:B10,4))+SUMIF(B2:B10,""<""&SMALL(B2:B10,4))"

例17,"对所有车间人员的工资求和:=SUMIF(A2:A10,""?车间"",C2)"

例18,"对多个车间人员的工资求和:=SUMIF(A2:A10,""??车间*"",C2)"

例19,"汇总姓赵、刘、李的业务员提成金额:=SUM(SUMIF(A2:A10,{""赵"",""刘"",""李""}&""*"",C2:C10))"

例20,"汇总鼠标所在列中大于600的数据:=SUMIF(INDIRECT(""R2C""&CELL(""col"")&"":R8C""&CELL(""col""),FALSE),"">600"")"

例21,"只汇总60～80分的成绩:=SUMIFS(B2:B10,B2:B10,"">=60"",B2:B10,""<=80"")"

例22,"汇总三年级二班人员迟到次数:=SUMIFS(D2:D10,B2:B10,""三年级"",C2:C10,""二班"")"

例23,"汇总车间女性人数:=SUMIFS(C2:C11,A2:A11,""*车间"",B2:B11,""女"")"

例24,"计算车间男性与女性人员的差:=SUM(SUMIFS(C2:C11,B2:B11,{""女"",""男""},A2:A11,""*车间"")*{-1,1})"

例25,"计算参保人数:=SUMPRODUCT((C2:C11=""是"")*1)"

例26,"求25岁以上男性人数:=SUMPRODUCT((B2:B10=""男"")*1,(C2:C10>25)*1)"

例27,"汇总一班人员获奖次数:=SUMPRODUCT((B2:B11=""一班"")*C2:C11)"

例28,"汇总一车间男性参保人数:=SUMPRODUCT((A2:A10&B2:B10&C2:C10=""一车间男是"")*1)"

例29,"汇总所有车间人员工资:=SUMPRODUCT(--NOT(ISERROR(FIND(""车间"",A2:A10))),C2:C10)"

例30,"汇总业务员业绩:=SUMPRODUCT((B2:B11={""江西"",""广东""})*(C2:C11=""男"")*D2:D11)"

例31,"根据直角三角形之勾、股求其弦长:=POWER(SUMSQ(B1,B2),1/2)"

例32,"计算A1：A10区域正数的平方和:{=SUMSQ(IF(A1:A10>0,A1:A10))}"

例33,"根据二边长判断三角形是否为直角三角形:=CHOOSE((SUMSQ(MAX(B1:B3))=SUMSQ(LARGE(B1:B3,{2,3})))+1,""非直角"",""直角"")"

例34,计算1到10的自然数的积:=FACT(10)

例35,计算50到60之间的整数相乘的结果:=FACT(60)/FACT(49)

例36,计算1到15之间奇数相乘的结果:=FACTDOUBLE(15)

例37,计算每小时生产产值:=PRODUCT(C2:E2)

例38,"根据三边求普通三角形面积:=(PRODUCT(SUM(B1:B3)/2,SUM(B1:B3)/2-LARGE(B1:B3,{1,2,3})))^0.5"

例39,"根据直角三角形三边求三角形面积:=PRODUCT(LARGE(B1:B3,{2,3}))/2"

例40,跨表求积:=PRODUCT(产量表:单价表!B2)

例41,"求不同单价下的利润:{=MMULT(B2:B10,G2:H2)*25%}"

例42,"制作中文九九乘法表:=COLUMN()&""*""&ROW()&""=""&MMULT(ROW(),COLUMN())"

例43,"计算车间盈亏:=SUM(MMULT((B3:E5>0)*B3:E5,{1;1;1;1}),MMULT((B3:E5<0)*B3:E5,{1;1;1;1}))"

例44,"计算各组别第三名产量是多少:{=MAX(MMULT(COLUMN(A:E)^0,B2:G6))}"

例45,"计算C产品最大入库量:{=MAX(MMULT(N(A2:A11=""C""),TRANSPOSE((B2:B11)*(A2:A11=""C""))))}"

例46,"求入库最多的产品数量:{=MAX(MMULT(TRANSPOSE((B2:B11)*(A2:A11={""A"",""B"",""C"",""D""})),(A2:A11={""A"",""B"",""C"",""D""})*1))}"

例47,"计算累计入库数:{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11)}"

例48,"计算每日库存数:{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11-C2:C11)}"

例49,"计算A产品每日库存数:{=MMULT(N(ROW(2:17)>=TRANSPOSE(ROW(2:17))),(B2:B17=""A"")*(C2:C17-D2:D17))}"

例50,"求第一名人员最多有几次:{=MAX(MMULT(N(B2:B7=TRANSPOSE(B2:B7)),ROW(2:7)^0))}"

例51,"求几号选手选票最多:{=RIGHT(MAX(MMULT(N(B2:B10=TRANSPOSE(B2:B10)),ROW(2:10)^0)*100+B2:B10))}"

例52,"总共有几个选手参选:{=SUM(1/(MMULT(N(B2:B10=TRANSPOSE(B2:B10)),ROW(2:10)^0)))}"

例53,"在不同班级有同名前提下计算学生人数:{=SUM(1/MMULT(N(A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17)),ROW(2:17)^0))}"

例54,"计算前进中学参赛人数:{=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))}"

例55,"串联单元格中的数字:{=MMULT(10^(COLUMNS(B:K)-COLUMN(C:L)),TRANSPOSE(B2:K2))}或=SUMPRODUCT(B2:K2,10^(COLUMNS(B:K)-COLUMN(B:K)-1))"

例56,"计算达标率:{=MMULT(TRANSPOSE(N(A2:A11<=(B2:B11))),ROW(2:11)^0)/ROWS(2:11)}"

例57,"计算成绩在60-80分之间合计数与个数:求和{=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11<80)*B2:B11),ROW(2:11)^0)}，求个数{=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11<80)),ROW(2:11)^0)}"

例58,"汇总A组男职工的工资:{=MMULT(TRANSPOSE(N(B2:B11&C2:C11=""男A组"")*D2:D11),ROW(2:11)^0)}"

例59,"计算象棋比赛对局次数l:=COMBIN(B1,B2)"

例60,"计算五项比赛对局总次数:{=SUM(COMBIN(B2:B5,2))}"

例61,"预计所有赛事完成的时间:=COMBIN(B1,B2)*B3/B4/60"

例62,"计算英文字母区分大小写做密码的组数:=PERMUT(B1*2,B2)"

例63,"计算中奖率:=TEXT(1/PERMUT(B1,B2),""0.00%"")"

例64,计算最大公约数:=GCD(B1:B5)

例65,计算最小公倍数:=LCM(B1:B5)

例66,"计算余数:=MOD(A2,B2)"

例67,"汇总奇数行数据:=SUMPRODUCT(MOD(ROW(2:13),2)*C2:C13)"

例68,"根据单价数量汇总金额:=SUMPRODUCT(MOD(COLUMN(A:I),2)*A2:I2,(MOD(COLUMN(B:J),2)=0)*B2:J2)"

例69,"设计工资条:=IF(MOD(ROW(),3)=1,单行表头工资明细!A$1,IF(MOD(ROW(),3)=2,OFFSET(单行表头工资明细!A$1,ROW()/3+1,0),""""))"

例70,"根据身份证号计算性别:=IF(MOD(MID(B2,15,3),2),""男"",""女"")"

例71,"每隔4行合计产值:=IF(MOD(ROW(),5)=1,SUM(OFFSET(F2,-4,,4,)),D2*E2)"

例72,"工资截尾取整:=B2+MOD(一月!B2,10)-MOD(B2+MOD(一月!B2,10),10)"

例73,"汇总3的倍数列的数据:{=SUM(IF(MOD(COLUMN(A:I),3)=0,A2:I10))}"

例74,"将数值逐位相加成一位数:=IF(A2=0,0,MOD(A2-1,9)+1)"

例75,"计算零钞: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"

例76,"秒与小时、分钟的换算:=QUOTIENT(MOD($A2,IF(COLUMN()=2,A2+1,60^(3-COLUMN(A:A)+1))),60^(3-COLUMN(A:A)))"

例77,"生成隔行累加的序列:=QUOTIENT(ROW()+1,2)"

例78,"根据业绩计算业务员奖金:=CHOOSE(MIN(QUOTIENT(B2,10000)+1,6),0,3%,5%,7%,9%,11%)*B2"

例79,计算预报温度与实际温度的最大误差值:{=MAX(ABS(C2:C8-B2:B8))}

例80,"计算个人所得税:=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)"

例81,产生100到200之间带小数的随机数:=RAND()*(200-100)+100

例82,"产生ll到20之间的不重复随机整数:{=RANK(A2:A11,A2:A11)+10}"

例83,"将20个学生的考位随机排列:{=INDEX(A$2:A$11,RANK(H2:H11,H2:H11))}"

例84,"将三个学校植树人员随机分组:=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),)"

例85,"产生-50到100之间的随机整数:=RANDBETWEEN(-50,100)"

例86,"产生1到100之问的奇数随机数:{=INDEX(IF(MOD(ROW(1:100),2),ROW(1:100),ROW(1:100)-1),RANDBETWEEN(1,100))}"

例87,"产生1到10之间随机不重复数:{=LARGE(IF(COUNTIF(A$1:A1,ROW($1:$10))=0,ROW($1:$10)),RANDBETWEEN(1,12-ROW()))}"

例88,"根据三角形三边长求证三角形是直角三角形:=IF(POWER(MAX(B1:B3),2)=SUM(POWER(LARGE(B1:B3,{2,3}),2)),""是"",""不是"")"

例89,"计算Al:A10区域开三次方之平均值:{=AVERAGE(POWER(A1:A10,1/30))}"

例90,"计算Al:A10区域倒数之积:{=PRODUCT(POWER(A1:A10,-1))}"

例91,"根据等边三角形周长计算面积:=SQRT(B1/2*POWER(B1/2-B1/3,3))"

例92,"抽取奇数行姓名:=INDEX(B:B,ODD(RANDBETWEEN(1,ROWS(1:12)-1)))"

例93,统计A1:B10区域中奇数个数:=SUMPRODUCT(N(ODD(A1:B10)=(A1:B10)))

例94,"统计参考人数:=SUMPRODUCT((EVEN(COLUMN(A1:J12))=COLUMN(A1:J12))*(MOD(ROW(A1:J12),3)=1)*(A1:J12<>""""))"

例95,计算A1:B10区域中偶数个数:=SUMPRODUCT(N(EVEN(A1:B10)=(A1:B10)))

例96,"合计购物金额、保留一位小数:=TRUNC(SUMPRODUCT(B2:B10,C2:C10),1)"

例97,"将每项购物金额保留一位小数再合计:=SUMPRODUCT(TRUNC(B2:B10*C2:C10,1))"

例98,"将金额进行四舍六入五单双:=IF((A2-TRUNC(A2,1))<=0.04,TRUNC(A2,1),IF((A2-TRUNC(A2,1))>=0.06,TRUNC(A2,1)+0.1,TRUNC((TRUNC(A2,1)+0.1)/2,1)*2))"

例99,"根据重量单价计算金额，结果以万为单位:=TRUNC(SUMPRODUCT(B2:B10,C2:C10),-4)/10000"

例100,计算年假天数:=TRUNC((TODAY()-B2)*((TODAY()-B2)>=365)/365*5)

例101,"根据上机时间计算上网费用:=(TRUNC(B2)+(B2-TRUNC(B2)>=0.5))*1.5+(MOD(B2,1)<0.5)"

例102,"将金额见角进元与见分进元:见分进元=CEILING(TRUNC(A2,2),1);见角进元=CEILING(TRUNC(A2,1),1)"

例103,分别统计收支金额并忽略小数:收入合计=SUMPRODUCT(INT(B2:B8));支出合计=SUMPRODUCT(TRUNC(C2:C8))

例104,"成绩表的格式转换:姓名=INDEX(A:A,INT((ROW(A6))/3));科目=INDEX(B$1:D$1,1,MOD((ROW(A1)-1),3)+1);成绩=INDEX($B$2:$D$7,INT((ROW(A1)-1)/3)+1,MOD((ROW(A1)-1),3)+1)"

例105,"隔两行进行编号:=IF(MOD(ROW(),3)=1,INT(ROW(A3)/3),"""")"

例106,INT函数在序列中的复杂运用:=INT(SQRT(2*ROW(A1))+0.5);=10^INT((ROW()-1)/2);=INT(10^(ROW())/9);=INT((ROW(A2))*2/3)

例107,"统计交易损失金额:=SUMPRODUCT(B2:B11-CEILING(B2:B11,0.1))"

例108,"根据员工工龄计算年资:=C2+CEILING(B2*30,30)*(INT(B2)>0)"

例109,"成绩表转换:=INDEX($A:$E,CEILING(ROW()*3/5,3)-(COLUMN()=7),MOD(ROW(B2)-1,5)+1)"

例110,"计算机上网费用:=CEILING(B2,30)/30*2"

例111,"统计可组建的球队总数:=SUMPRODUCT(FLOOR(B2:B10,5)/5)"

例112,"统计业务员提成金额，不足20000元忽略:=FLOOR(B2,20000)/20000*500"

例113,"FLOOR函数处理正负数混合区域:=FLOOR(A1*100,10*(IF(A1>0,1,-10)))"

例114,"将数据转换成接近6的倍数:=MROUND(A1,6)"

例115,"以超产80为单位计算超产奖:{=SUM(MROUND(B2:B11-700,80*IF(B2:B11>=700,1,-1)))/80*50}"

例116,"将统计金额保留到分位:=ROUND(SUMPRODUCT(B2:B10,C2:C10),2)"

例117,"将统计金额转换成以万元为单位:=ROUND(SUMPRODUCT(B2:B10,C2:C10)%%,)"

例118,"对单价计量单位不同的品名汇总金额:{=SUM(ROUND(B2:B10*C2:C10*IF(D2:D10=""G"",1000,1),(D2:D10=""G"")*2))}"

例119,"将金额保留“角”位，忽略“分”位:{=SUM(ROUNDDOWN(B2:B10*C2:C10,1))}"

例120,"计算需要多少零钞:{=SUM(ROUNDDOWN(B2:B10*C2:C10,{0,-1})*{1,-1})}"

例121,"计算值为l万的整数倍数的数据个数:{=SUM(N((B2:B10*C2:C10)=ROUNDDOWN(B2:B10*C2:C10,-4)))}"

例122,"计算完成工程需求人数:{=SUM(ROUNDUP(B2:B11/C2:C11,))}"

例123,"按需求对成绩进行分类汇总:=SUBTOTAL(HLOOKUP(G$1,{""平均成绩"",""科目数量"",""最高成绩"",""最低成绩"",""成绩合计"";1,2,4,5,9},2,0),B2:D2)"

例124,"不间断的序号:=SUBTOTAL(103,$B$2:B2)"

例125,"仅对筛选出的人员排名次:{=CONCATENATE(""第"",SUM(N(IF((SUBTOTAL(103,OFFSET(优等生!A$1,ROW($2:$31)-2,)))=1,$C$2:$C$31,)>C2))+1,""名"")}"

例126,判断两列数据是否相等:

例127,计算两列数据同行相等的个数:{=SUM(N(A1:A10=B1:B10))}

例128,计算同行相等且长度为3的个数:{=SUM((A1:A10=B1:B10)*(LEN(A1:A10)=3))}

例129,"提取A产品最后单价:{=INDEX(C:C,MAX((B2:B10=""A"")*ROW(2:10)))}"

例130,"判断学生是否符合奖学金发放条件:=AND(B2>90,C2<>""汉族"")"

例131,"所有裁判都给“通过”就进入决赛:{=AND(B2:E2=""通过"")}"

例132,"判断身份证长度是否正确:=OR(LEN(B2)={15,18})"

例133,"判断歌手是否被淘汰:{=OR(B2:E2=""不通过"")}"

例134,"根据年龄判断职工是否退休:=OR(AND(B2=""男"",C2>60),AND(B2=""女"",C2>55))"

例135,"根据年龄与职务判断职工是否退休:=OR(AND(B2=""男"",D2>60+(C2=""干部"")*3),AND(B2=""女"",D2>55+(C2=""干部"")*3))"

例136,"没有任何裁判给“不通过”就进行决赛:{=NOT(OR(B2:E2=""不通过""))}"

例137,计彝成绩区域数字个数:{=SUM(NOT(ISERROR(NOT(B2:B11)))*1)}

例138,"评定学生成绩是否及格:=IF(AVERAGE(B2:D2)>=60,""及格"",""不及格"")"

例139,"根据学生成绩自动产生评语:=IF(AVERAGE(B2:D2)<60,""不及格"",IF(AVERAGE(B2:D2)<90,""良好"",IF(AVERAGE(B2:D2)<100,""优秀"",""满分"")))"

例140,"根据业绩计算需要发放多少奖金:{=SUM(IF(B2:B11>80000,1000,500))}"

例141,"根据工作时间计算12月工资:=C2+SUM(IF(B2>{0,1,3,5,10},{300,500,500,500,500}))"

例142,"合计区域的值并忽略错误值:{=SUM(IF(ISERROR(A1:C10),0,A1:C10))}"

例143,"既求积也求和:=IF(D2<>"""",PRODUCT(C2:D2),SUM(OFFSET(E2,-3,,3)))"

例144,"分别统计收入和支出:收入{=SUM(IF(B2:B13>0,B2:B13))};支出{=SUM(IF(SUBSTITUTE(IF(B2:B13<>"""",B2:B13,0),""负"",""-"")*1<0,SUBSTITUTE(B2:B13,""负"",""-"")*1))}"

例145,"将成绩从大到小排列:{=IF(ROW(A1)>COUNT(B$2:B$11),"""",LARGE(B$2:B$11,ROW(A1)))}"

例146,"排除空值:{=INDEX($A:$B,SMALL(IF($B$1:$B$11<>"""",ROW($1:$11),ROWS($1:$11)+1),ROW()),COLUMN(B2))&""""}"

例147,"有选择地汇总数据:{=SUM(IF(A2:A11={""A组"",""C组""},C2:C11))}"

例148,"混合单价求金额合计:{=SUM(ROUND(B2:B10*C2:C10*IF(D2:D10=""K"",1000,1),2))}"

例149,"计算异常停机时间:{=SUM(SUBSTITUTE(SUBSTITUTE(IF(C2:C11<>"""",C2:C11,0),""修机"",""""),""换原料"","""")*1)}"

例150,"计算最大数字行与文本行:{=MAX(IF(B:B<>"""",ROW(A:A)))}"

例151,"找出谁夺冠次数最多:{=INDEX(B:B,MIN(IF(MAX(COUNTIF(B2:B12,B2:B12))=COUNTIF(B2:B12,B2:B12),ROW(2:12))))}"

例152,将全角字符转换为半角:=ASC(A2)

例153,计算汉字全角半角混合字符串中的字母个数:=LEN(ASC(A2))*2-LENB(ASC(A2))

例154,将半角字符转换成全角显示:=WIDECHAR(A2)

例155,计算混合字符串中汉字个数:=LEN(A2)-(LENB(WIDECHAR(A2))-LENB(ASC(A2)))

例156,"判断单元格首字符是否为字母:=OR(AND(CODE(A2)>64,CODE(A2)<91),AND(CODE(A2)>96,CODE(A2)<123))"

例157,"计算单元格中数字个数:{=SUM((CODE(MID(A2,ROW(INDIRECT(""1:""&LEN(A2))),1))>47)*(CODE(MID(A2,ROW(INDIRECT(""1:""&LEN(A2))),1))<58))}"

例158,"计算单元格中大写加小写字母个数:{=SUM((CODE(UPPER(MID(A2,ROW(INDIRECT(""1:""&LEN(A2))),1)))>64)*(CODE(UPPER(MID(A2,ROW(INDIRECT(""1:""&LEN(A2))),1)))<91))}"

例159,产生大、小写字母A到Z的序列:大写字母=CHAR(ROW(A65))，小写字母=CHAR(ROW(A65)+32)

例160,"产生大写字母A到ZZ的字母序列:=IF(ROW()<27,CHAR(MOD(ROW()-1,26)+65),CHAR(65+(ROW()-1)/26-1))&IF(ROW()>26,CHAR(MOD(ROW()-1,26)+65),"""")"

例161,"产生三个字母组成的随机字符串:=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))"

例162,用公式产生换行符:=A2&CHAR(10)&B2

例163,"将数字转换成英文字符:字符码=RANDBETWEEN(1,100)，升序位置=CHAR(MOD(A1-1,26)+65)"

例164,"将字母升序排序:{=CHAR(SMALL(CODE(A$2:A$13),ROW(A1)))}"

例165,"返回自动换行单元格的第二行数据:=RIGHT(A2,LEN(A2)-FIND(CHAR(10),A2))"

例166,"根据身份证号码提取出生年月日:=CONCATENATE(MID(B2,7,4-2*(LEN(B2)=15)),""年"",MID(B2,11-2*(LEN(B2)=15),2),""月"",MID(B2,13-2*(LEN(B2)=15),2),""日"")"

例167,"计算平均成绩及评判是否及格:=CONCATENATE(INT(AVERAGE(B2:D2)),"":"",IF(AVERAGE(B2:D2)>=60,"""",""不""),""及格"")"

例168,"提取前三名人员姓名:=CONCATENATE(LOOKUP(0,0/(B2:B11=LARGE(B2:B11,1)),A2:A11),""|"",LOOKUP(0,0/(B2:B11=LARGE(B2:B11,2)),A2:A11),""|"",(LOOKUP(0,0/(B2:B11=LARGE(B2:B11,3)),A2:A11)))"

例169,将单词转换成首字母大写:=PROPER(A2)

例170,将所有单词转换成小写形式:=LOWER(A2)

例171,"将所有句子转换成首字母大写其余小写:=CONCATENATE(PROPER(LEFT(A2)),LOWER(RIGHT(A2,LEN(A2)-1)))"

例172,将所有字母转换成大写形式:=UPPER(A2)

例173,"计算字符串中英文字母个数:{=SUM(N(NOT(EXACT(UPPER(MID(A2,ROW(INDIRECT(""1:""&LEN(A2))),1)),LOWER(MID(A2,ROW(INDIRECT(""1:""&LEN(A2))),1))))))}"

例174,"计算字符串中单词个数:{=SUM(N(EXACT(TRIM(MID(UPPER(A2),ROW(INDIRECT(""1:""&LEN(A2))),1)),MID(PROPER(A2),ROW(INDIRECT(""1:""&LEN(A2))),1))))}"

例175,将文本型数字转换成数值:{=SUM(VALUE(B2:B10))}

例176,"计算字符串中的数字个数:=SUMPRODUCT(N(ISNUMBER(VALUE(MID(A2,ROW($1:$100),1)*1))))"

例177,"提取混合字符串中的数字:{=MAX(IFERROR(VALUE(MID(A2,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A2&1234567890)),ROW(INDIRECT(""1:""&LEN(A2))))),0))}"

例178,"串联区域中的文本:=CONCATENATE(T(A2),T(B2),T(C2))"

例179,"给公式添加运算说明:=CONCATENATE(""你好"",B2,""2008"")&T(N(""公式含义:连接“你好”和单元格B2、“2008”""))"

例180,"根据身份证号码判断性别:=TEXT(MOD(MID(B2,15,3),2),""[=1]男;[=0]女"")"

例181,"将所有数据转换成保留两位小数再求和:{=SUM(--TEXT(B2:B11*C2:C11,""0.00""))}"

例182,"将货款显示为“万元”为单位:=TEXT(B2,""￥#""&""""""""&"".""&""""""""&""#,万元"")"

例183,"根据身份证号码计算出生日期:=IF(LEN(B2)=15,19,"""")&TEXT(MID(B2,7,8-(LEN(B2)=15)*2),""#年00月00日"")"

例184,"显示今天的英文日期及星期:=""资料日期：""&TEXT(TODAY(),""dddd,mmmmdd,yyyy"")"

例185,"显示今天每项工程的预计完成时间:=TEXT(SUM(""08:00"",B$2:B2),""h:mm:ss上午/下午"")"

例186,"统计A列有多少个星期日:{=SUM(N(TEXT(A1:A11,""aaa"")=""日""))}"

例187,"将数据显示为小数点对齐:=TEXT(B2,""#.0????"")"

例188,"计算A列的日期有几个属于第二季度:{=SUM((--(TEXT(A1:A11,""m""))>{3,6})*{1,-1})}"

例189,"在A列产生1到12月的英文月份名:=TEXT((ROW())&""-1"",""mmmm"")"

例190,"将日期显示为中文大写:=TEXT(""2008-8-10"",""[DBNum2]yyyy年m月d日"")"

例191,"将数字金额显示为人民币大写:=IF(MOD(B2,1)=0,TEXT(INT(B2),""[dbnum2]G/通用格式元整;负[dbnum2]G/通用格式元整;零元整;""),IF(B2>0,,""负"")&TEXT(INT(ABS(B2)),""[dbnum2]G/通用格式元;;"")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(FIXED(B2),2),""[dbnum2]0角0分;;""),""零角"",IF(ABS(B2)<>0,,""零"")),""零分"",""""))"

例192,"判断单元格的数据类型:=TEXT(A2,""大于○;小于○;○;文本"")"

例193,"计算达成率，以不同格式显示:=TEXT(B2/800,""[>=1]0.0倍;[>0]0.00%;"")"

例194,"计算字母“A”的首次出现位置，忽略大小写:=TEXT(SEARCH(""a"",A2&""a""),""[>""&LEN(A2)&""]没找到;第""&SEARCH(""a"",A2&""a"")&""个"")"

例195,"从身份证号码中提取表示性别的数字:=MID(B2,TEXT(LEN(B2),""[=15]15;17""),1)"

例196,"将三列数据交换位置:{=TEXT({1,-1,0},C1:C5&"";""&""!""&B1:B5&"";""&A1:A5)}"

例197,"计算年终奖:=TEXT(B2,""[>3]15!0!0;[>1]1!0!0!0;5!0!0;"")"

例198,"计算星期日完工的工程个数:{=COUNT((TEXT(B2:B10+C2:C10-1,""AAA"")=""日"")^0)}"

例199,"计算本月星期日的个数:{=SUM(N(TEXT(TODAY()-TEXT(TODAY(),""d"")+ROW(INDIRECT(""1:""&DAY(DATE(,TEXT(TODAY(),""m"")+1,)))),""AAA"")=""日""))}"

例200,"检验日期是否升序排列:=TEXT(N(A3>=A2),"";;日期有误;"")"

例201,"判断单元格中首字符的类型:=TEXT(IF(AND(CODE(UPPER(A3))>64,CODE(UPPER(A3))<91),CODE(A3),A3),""[=""&CODE(A3)&""]字母;;数字;汉字"")"

例202,"计算每个季度的天数:{=SUM(--TEXT(DATE(2008,3*ROW(A1)-ROW($1:$3)+2,),""d""))}"

例203,"将数据重复显示5次:=SUBSTITUTE(TEXT(A2&""？"",""@@@@@""),""？"","""")"

例204,"将表示起止时间的数字格式化为时间格式:=TEXT(B2,""#!:00-00!:00"")"

例205,"根据起止时间计算经过时间:=TEXT(INT(((TEXT(RIGHT(B4,4),""#!:00"")-TEXT(LEFT(B4,3+(LEN(B4)=8)),""#!:00""))*24*60)/60)+MOD(((TEXT(RIGHT(B4,4),""#!:00"")-TEXT(LEFT(B4,3+(LEN(B4)=8)),""#!:00""))*24*60),60.1)%,""0小时.00分钟"")"

例206,"将数字转化成电话格式:=TEXT(A2,""(0000)0000-0000"")"

例207,"在A1:A7区域产生星期一到星期日的英文全称:{=TEXT(ROW(1:7)+1,""DDDD"")}"

例208,"将汇总金额保留一位小数并显示千分位分隔符:{=FIXED(SUM(--FIXED(B2:B11*C2:C11,1)),1,FALSE)}"

例209,"计算订单金额并以“百万”为单位显示:=FIXED(SUMPRODUCT(B2:B10,C2:C10),-6)/1000000"

例210,"将数据对齐显示，将空白以“．”占位:=WIDECHAR(REPT(""."",10-LEN(B2))&B2)"

例211,"利用公式制作简易图表:=IF(B2>0,REPT("""",5)&""｜""&REPT(""■"",ABS(B2))&B2&REPT("""",5-ABS(B2)),REPT("""",5-ABS(B2)-LEN(B2)/2)&B2&REPT(""■"",ABS(B2))&""｜""&REPT("""",5))"

例212,"利用公式制作带轴的图表且标示升降:{=IF(A2<>"""",A2&""┫"","""")&IF(A2="""",REPT(""〓"",(MAX(ABS(B$2:B$8))+6)*2),IF(B2>0,REPT("""",4+MAX(ABS(B$2:B$8)))&IF(ROW()=2,"""",IF(B2=OFFSET(B2,-1,0),""→"",IF(B2>OFFSET(B2,-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(B2>OFFSET(B2,-1,0),""↑"",""↓""))&REPT("""",4+MAX(ABS(B$2:B$8))))))}"

例213,计算单元格中数字个数:=LEN(A2)*2-LENB(A2)

例214,"将数字倒序排列:{=TEXT(SUM(MID(A2,ROW(INDIRECT(""1:""&LEN(A2))),1)*10^(ROW(INDIRECT(""1:""&LEN(A2)))-1)),REPT(0,LEN(A2)))}"

例215,计算购物金额中小数位数最多是几:{=MAX(LEN(B2:B10*C2:C10)-LEN(INT(B2:B10*C2:C10)))-1}

例216,"计算英文句子中有几个单词:=LEN(A2)-LEN(SUBSTITUTE(SUBSTITUTE(A2,""'"",""""),"""",""""))+1"

例217,"将英文句子规范化:=PROPER(LEFT(A2))&TRIM(RIGHT(A2,LEN(A2)-1))"

例218,"分别提取省市县名:=TRIM(MID(SUBSTITUTE($A2,""/"",REPT("""",100)),COLUMN(A2)*100-99,100))"

例219,"提取英文名字:=LEFT(A2,FIND("""",A2)-1)"

例220,"将分数转换成小数:=(LEFT(A2,FIND(""/"",A2)-1)+RIGHT(A2,LEN(A2)-FIND(""/"",A2)))/2"

例221,"从英文短句中提取每一个单词:=IFERROR(MID($A2,FIND(""~"",SUBSTITUTE(""""&$A2&"""","""",""~"",COLUMN(A2))),FIND(""~"",SUBSTITUTE(""""&$A2&"""","""",""~"",COLUMN(B2)))-FIND(""~"",SUBSTITUTE(""""&$A2&"""","""",""~"",COLUMN(A2)))),"""")"

例222,"将单位为“双”与“片”混合的数量汇总:{=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)))}"

例223,"提取工作表名:=RIGHT(CELL(""filename""),LEN(CELL(""filename""))-FIND(""]"",CELL(""filename"")))"

例224,"根据产品规格计算产品体积:=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)))"

例225,"提取括号中的字符串:=IFERROR(MID(A2,FIND(""("",A2)+1,FIND("")"",A2)-FIND(""("",A2)-1),"""")"

例226,"分别提取长、宽、高:=MID($B2,FIND(""@"",SUBSTITUTE($B2,""（"",""@"",COLUMN(A1)))+1,FIND(""@"",SUBSTITUTE($B2,""）"",""@"",COLUMN(A1)))-FIND(""@"",SUBSTITUTE($B2,""（"",""@"",COLUMN(A1)))-1)"

例227,"提取学校与医院地址:{=IF(OR(IFERROR(FIND({""学校"",""医院""},A2),FALSE)),A2,"""")}"

例228,"计算密码字符串中字符个数:{=COUNT(FIND(CHAR(ROW(65:90)),A2),FIND(CHAR(ROW(97:122)),A2),FIND(ROW(1:10)-1,A2))}"

例229,"通讯录单列转三列:{=MID(INDEX($A:$A,SMALL(IF(IFERROR(FIND(C$1,$A$1:$A$15),FALSE),ROW($1:$15),100000),ROW(A1))),LEN(C$1)+1,100)}"

例230,"将15位身份证号码升级为18位:{=IF(LEN(B2)=18,B2,LEFT(REPLACE(B2,7,,19),17)&MID(""10X98765432"",MOD(SUM(MID(REPLACE(B2,7,,19),ROW(INDIRECT(""1:17"")),1)*2^(18-ROW(INDIRECT(""1:17"")))),11)+1,1))}"

例231,"将产品型号规范化:=IF(MID(A2,5,2)=""00"",A2,REPLACE(A2,5,,""00""))"

例232,"求最大时间:{=TEXT(MAX(--TEXT(REPLACE(LEFT(A2:A7,7),5,1,RIGHT(A2:A7,2)),""00!:0000-00"")),""hmm/dd/mm"")}"

例233,"分别提取小时、分钟、秒:=REPLACE(REPLACE($A$1&$A2,FIND(B$1,$A$1&$A2),100,),1,FIND(A$1,$A$1&$A2)+1,)"

例234,"将年级或者专业与班级名称分开:{=REPLACE(A2,MAX(IFERROR(SEARCH(CHAR(ROW($65:$90)),A2),0)),10,)}"

例235,"提取各软件的版本号:=REPLACE(REPLACE(A2,1,SEARCH(""("",A2),),LEN(REPLACE(A2,1,SEARCH(""("",A2),)),1,)"

例236,"店名分类:=IF(COUNT(SEARCH({""小吃"",""酒吧"",""茶"",""咖啡"",""电影"",""休闲"",""网吧""},A2))=1,""餐饮娱乐"",IF(COUNT(SEARCH({""干洗"",""医院"",""药"",""茶"",""蛋糕"",""面包"",""物流"",""驾校"",""开锁"",""家政"",""装饰"",""搬家"",""维修"",""中介"",""卫生"",""旅馆""},A2))=1,""便民服务"",IF(COUNT(SEARCH({""游乐场"",""旅行社"",""旅游""},A2))=1,""旅游"")))"

例237,"查找编号中重复出现的数字:重复数字个数{=COUNT(SEARCH((ROW($1:$10)-1)&""*""&(ROW($1:$10)-1),A2))};重复字符=IF(COUNT(SEARCH(""0*0"",A2)),0,"""")&SUBSTITUTE(SUMPRODUCT(ISNUMBER(SEARCH(ROW($1:$9)&""*""&ROW($1:$9),A2))*ROW($1:$9)*10^(9-ROW($1:$9))),0,)"

例238,"统计名为“刘星”者人数:{=COUNT(SEARCH(""?刘星"",A2:A9))}"

例239,"剔除多余的省名:=SUBSTITUTE(A2,IF(ISERROR(SEARCH(""重庆市"",A2)),"""",""四川省""),"""")"

例240,"将日期规范化再求差:=SUBSTITUTE(C2,""."",""-"")-SUBSTITUTE(B2,""."",""-"")"

例241,"提取两个符号之间的字符串:=TRIM(MID(SUBSTITUTE(B2,""*"",REPT("""",50)),FIND(""*"",B2),100))"

例242,"产品规格格式转换:=SUBSTITUTE(SUBSTITUTE(A2,""："",""(""),""*"","")*"")&"")"""

例243,"判断调色配方中是否包含色粉“B”:=LEN(SUBSTITUTE(B2,""B"",""""))<>LEN(B2)"

例244,"提取姓名与省份:=TRIM(MID(A2,1,FIND(""|"",A2)-1)&MID(SUBSTITUTE(A2,""|"",REPT("""",100)),500,100))"

例245,"将IP地址规范化:=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("".""&A2,"".0"","".""),"".0"","".""),""."","""",1)"

例246,"提取最后一次短跑成绩:=REPLACE(A2,1,FIND(""々"",SUBSTITUTE(A2,""|"",""々"",LEN(A2)-LEN(SUBSTITUTE(A2,""|"",)))),)"

例247,"从地址中提取省名:=LEFT(A2,FIND(""省"",A2))"

例248,"计算小学参赛者人数:{=COUNT(0/(LEFT(B2:B11)=""小""))}"

例249,"计算四川方向飞机票总价:=SUMPRODUCT(N(LEFT(A2:A11,2)=""四川""),N(B2:B11=""飞机""),C2:C11)"

例250,"通过身份证号码计算年龄:=TEXT(TODAY(),""YYYY"")-(IF(LEN(B2)=18,"""",19)&LEFT(REPLACE(B2,1,6,""""),2+(LEN(B2)=18)*2))"

例251,"从混合字符串中取重量:=LOOKUP(9E+307,--LEFT(B2,ROW($1:$10)))*C2"

例252,"将金额分散填充:=LEFT(RIGHT(""￥""&$A2*100,13-COLUMN()))"

例253,"提取成绩并计算平均:{=AVERAGE(MID(A2:A7,4,LEN(A2:A7)-3)*1)}"

例254,"提取参赛选手姓名:=MID(A2,FIND(""："",A2)+1,LEN(A2))"

例255,"从混合字符串中提取金额:=LOOKUP(307,--MID(B2,MIN(FIND({1;2;3;4;5;6;7;8;9},B2&123456789)),ROW($1:$99)))"

例256,"从卡机数据提取打卡时间:=730>--MID(A2,14,4)"

例257,"根据卡机数据判断员工部门:=CHOOSE(MATCH(--RIGHT(A2,3),{1,38,14,11,8,21,43,9,28},0),""生产部"",""业务部"",""总务部"",""人事部"",""食堂"",""保卫部"",""采购部"",""送货部"",""财务部"")"

例258,"根据身份证号码统计男性人数:{=SUM(MOD(LEFT(RIGHT(B2:B11,1+(LEN(B2:B11)=18))),2))}"

例259,"从汉字与数字混合字串中提取温度数据:{=MAX(IFERROR(--RIGHT(LEFT(B2,LEN(B2)-1),ROW($1:$10)),0))}"

例260,"将字符串位数统一:{=TEXT(RIGHT(A2,LEN(A2)-1),""!""&LEFT(A2)&REPT(0,MAX(LEN(A$2:A$10))-1))}"

例261,"对所有人员按平均分排序:{=INDEX(A:A,RIGHT(LARGE(B$2:B$11*1000+ROW($2:$11),ROW()-1),3))}"

例262,"取金额的角位与分位叫:=--RIGHT(ROUND(A2*100,),2)"

例263,"从格式不规范的日期中取出日:=TRIM(RIGHT(SUBSTITUTE(A2,""."",""

"",2),3))"

例264,"计算平均成绩(忽略缺考人员):=ROUND(AVERAGE(B2:B10),2)"

例265,"计算90分以上的平均成绩:{=ROUND(AVERAGE(IF(ISNUMBER(B2:B10)*(B2:B10>90),B2:B10)),2)}"

例266,计算当前表以外的所有工作表平均值2:=AVERAGE(一班:五班!B:B)

例267,"计算二车间女职工的平均工资:{=AVERAGE(IF((B2:B10=""二车间"")*(C2:C10=""女""),D2:D10))}"

例268,"计算一车间和三车间女职工的平均工资:{=AVERAGE(IF((B2:B10=""一车间"")+(B2:B10=""三车间"")*(C2:C10=""女""),D2:D10))}"

例269,计算各业务员的平均奖金:{=AVERAGE(1500+300*(INT((C2:C11-80000)/10000)))}

例270,"计算平均工资(不忽略无薪人员):=ROUND(AVERAGEA(B2:B10),2)"

例271,"计算每人平均出口量:{=AVERAGEA((C2:C11=""A"")*D2:D11)}"

例272,计算平均成绩，成绩空白也计算:{=AVERAGEA(B2:B11*1)}

例273,"计算二年级所有人员的平均获奖率:{=TEXT(AVERAGEA(IF(LEFT(A2:A10,3)=""二年级"",B2:B10/C2:C10)),""0.00%"")}"

例274,"统计前三名人员的平均成绩:=AVERAGEA(LARGE(B2:B11,{1,2,3}))"

例275,"求每季度平均支出金额:=AVERAGEIF(B2:B9,""支出"",C2)"

例276,"计算每个车间大于250的平均产量:=AVERAGEIF(B2:C11,"">250"")"

例277,"去掉首尾求平均:=AVERAGEIFS(B2:B11,B2:B11,"">""&MIN(B2:B11),B2:B11,""<""&MAX(B2:B11))"

例278,"生产A产品且无异常的机台平均产量:=AVERAGEIFS(C2:C11,B2:B11,""A"",D2:D11,"""")"

例279,计算生产车间异常机台个数:=COUNT(C2:C11)

例280,"计算及格率:{=TEXT(COUNT(0/(B2:B11>=60))/COUNT(B2:B11),""0.00%"")}"

例281,"统计属于餐饮娱乐业的店名个数:{=COUNT(SEARCH({""小吃"",""酒吧"",""茶"",""咖啡"",""电影"",""休闲"",""网吧""},A2:A11))}"

例282,统计各分数段人数:{=COUNT(0/((B$2:B$11>ROW(A6)*10)*(B$2:B$11<=ROW(A7)*10)))}

例283,"统计有多少个选手:{=COUNT(0/(MATCH(B2:B11,B2:B11,)=(ROW(2:11)-1)))}"

例284,统计出勤异常人数:=COUNTA(B2:B11)

例285,"判断是否有人缺考:=IF(COUNTA(B2:E10)=ROWS(B2:E10)*COLUMNS(B2:E10),""没有"",""有"")"

例286,统计未检验完成的产品数:=COUNTBLANK(B2:B11)

例287,"统计产量达标率:=TEXT(COUNTIF(B2:B11,"">=800"")/COUNT(B2:B11),""0.00"")"

例288,"根据毕业学校统计中学学历人数:=COUNTIF(B2:B11,""*中学"")"

例289,"计算两列数据相同个数:{=SUM(COUNTIF(A2:A11,B2:B11))}"

例290,"统计连续三次进入前十名的人数:{=SUM(COUNTIF(C2:C11,IF(COUNTIF(A2:A11,B2:B11),B2:B11)))}"

例291,"统计淘汰者人数:{=SUM(N(COUNTIF(A2:C11,A2:C11)=1))}"

例292,"统计区域中不重复数据个数:{=SUM(1/COUNTIF(B2:B8,B2:B8))}"

例293,"统计诺基亚、摩托罗拉和联想已隹出手机个数:=SUM(COUNTIF(B2:B11,""*""&{""诺基亚"",""摩托罗拉"",""联想""}&""*""))"

例294,"统计联想比摩托罗拉手机的销量高多少:{=SUM(COUNTIF(B2:B11,{""诺基亚*"",""*联想*""})*{1,-1})}"

例295,"统计冠军榜前三名:{=INDEX(B:B,SMALL(IF(COUNTIF(B$2:B$12,B$2:B$12)*((MATCH(B$2:B$12,B$2:B$12,)=ROW($2:$12)-1))>=LARGE(COUNTIF(B$2:B$12,B$2:B$12)*((MATCH(B$2:B$12,B$2:B$12,)=ROW($2:$12)-1)),3),ROW($2:$12)),ROW(A1)))}"

例296,"统计真空、假空单元格个数:=COUNTIF(成绩!C2:C11,""="")"

例297,"对名册表进行混合编号:=IF(RIGHT(B1)<>""班"",ROW()-COUNTIF($B$1:B1,""??班""),TEXT(COUNTIF($B$1:B1,""??班""),""[DBNum2]0""))"

例298,"提取不重复数据5:{=INDEX(B:B,MATCH(0,COUNTIF($D$1:D1,B$2:B$11),0)+1)}"

例299,"中国式排名:{=SUM(IF(B$2:B$11>B2,1/COUNTIF(B$2:B$11,B$2:B$11)))+1}"

例300,"统计大于80分的三好学生个数:{=COUNTIFS(B2:B11,""三好学生"",C2:C11,"">80"")}"

例301,"统计业绩在6万到8万之间的女业务员个数:=COUNTIFS(B2:B11,""女"",C2:C11,"">60000"",C2:C11,""<=800000"")"

例302,"统计二班和三班数学竞赛获奖人数:=SUM(COUNTIFS(B2:B11,{""二班"",""三班""},C2:C11,""数学*""))"

例303,"根据身高计算各班淘汰人数:=SUM(COUNTIFS(B$2:B$11,E1,C$2:C$11,{""<160"","">180""}))"

例304,"计算A列最后一个非空单元格行号:{=MAX((A:A<>"""")*ROW(A:A))}"

例305,"计算女职工的最大年龄:{=MAX((B2:B11=""女"")*C2:C11)}"

例306,"消除单位提取数据:{=MAX(IFERROR(ABS(LEFT(A2,ROW($1:$100))),))*IF(LEFT(A2)=""-"",-1,1)}"

例307,"计算单日最高销售金额:{=MAX(SUMIF(A2:A11,A2:A11,C2:C11))}"

例308,"查找第一名学生姓名:=INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10,))"

例309,"统计季度最高产值合计:{=MAX(SUBTOTAL(9,OFFSET(B2,,COLUMN(B:E)-2,ROWS(2:10),1)))}"

例310,"根据达标率计算员工奖金:=MAX((B2>{0,0.8,0.9,1,1.05})*{200,250,300,450,550})"

例311,"提取产品最后报价和最高报价:{=INDEX(C:C,MAX((A2:A11=""B"")*ROW(2:11)))}"

例312,"计算卫冕失败最多的次数:{=MAX(FREQUENCY(ROW(2:11),((B2:B10=""第一名"")<>(B3:B11=""第一名""))*ROW(2:10)))}"

例313,"低于平均成绩中的最优成绩:{=MAX(IF(B2:B11<AVERAGE(B2:B11),B2:B11,))}"

例314,"计算语文成绩大于90分者的最高总成绩:=DMAX(A1:E11,5,G1:G2)"

例315,"计算数学成绩等于100分的男生最高总成绩:=DMAX(A1:E11,""总分"",B1:B2)"

例316,"根据下拉列表计算不同项目的最大值:=DMAX(A1:E11,G4,G1:G2)"

例317,计算中间成绩:=MEDIAN(B2:B11)

例318,"显示动态日期，但不能超过9月30日:=MIN(""2008-9-30"",TODAY())"

例319,"根据工作时间计算可休假天数:=MIN(SUM((B2={""A"",""B"",""C""})*{5,4,3})+(C2-1),10)"

例320,"确定最佳成绩:=MATCH(MIN(B2:B11),B2:B11,)"

例321,"计算文具类产品和家具类产品最小利率:{=TEXT(MIN(IF(ISNUMBER(SEARCH(""（?具类"",A2:A11)),B2:B11)),""0.00%"")}"

例322,"计算得票最少者有几票:{=MIN(COUNTIF(B2:C11,B2:C11))}"

例323,"根据工程的难度系数计算奖金:=MIN(A2,1+(A2>1.3)*0.3)*500"

例324,"将科目与成绩分开:{=MID(A2,MIN(IF(ISNUMBER(FIND(ROW($1:$9),A2)),FIND(ROW($1:$9),A2))),100)}"

例325,"计算五个班的第一名人员的最低成绩:=MIN(SUBTOTAL(4,INDIRECT({""一"",""二"",""三"",""四"",""五""}&""班!B2:b11"")))"

例326,"根据员工生产产品的废品率记分:=MAX(MIN(6-(B2*100-5),10),0)"

例327,"统计售价850元以上的产品最低利率是多少:=DMIN(A1:D11,F4,F1:F2)"

例328,"统计文具类和厨具类产品的最低单价:=DMIN(A1:B11,2,D1:D2)"

例329,"第三个最小的成绩:=SMALL(B2:B11,3)"

例330,"计算最后三名成绩的平均值:=AVERAGE(SMALL(B2:B11,{1,2,3}))"

例331,"将成绩按升序排列:{=SMALL(B$2:B$11,ROW(A1))}"

例332,"罗列三个班第一名成绩:{=SMALL(IF(C$2:C$11=""第一名"",D$2:D$11),ROW(A1))}"

例333,"将英文月份名称升序排列:{=INDEX(A$2:A$13,SMALL(IF(CODE($A$2:$A$13)=SMALL(CODE(A$2:A$13),ROW(A1)),ROW($1:$12)),COUNTIF(C$1:C1,CHAR(SMALL(CODE(A$2:A$13),ROW(A1)))&""*"")+1))}"

例334,"查看产品曾经销售的所有价位:{=IF(ROW(A1)>SUM(1/COUNTIF(B$2:C$11,B$2:C$11)),"""",SMALL(B$2:C$11,1+COUNTIF(B$2:C$11,""<=""&E1)))}"

例335,"罗列三个工作表B列最后三名成绩:=SMALL(一班:三班!B:B,ROW(A1))"

例336,"第3个最小成绩到第6个最小成绩之间的人数:{=SUM((((SMALL(B2:D11,ROW(INDIRECT(""1:""&COUNT(B2:D11))))>SMALL(B2:D11,{3,6}))*{1,-1})))}"

例337,"计算与第3个最大值并列的个数:{=SUM(--(B2:B11=LARGE(B2:B11,3)))}"

例338,"计算大于等于前10个最大产量之和:=SUMPRODUCT((B2:C11>LARGE(B2:C11,11))*B2:C11)"

例339,"按成绩列出学生排行榜:{=INDEX(A$2:A$11,MATCH(LARGE(10-ROW($2:$11)+B$2:B$11*1000,ROW(A1)),10-ROW($2:$11)+B$2:B$11*1000,0))}"

例340,"最后一次获得第一名是第几届:{=INDEX(A:A,LARGE((B2:B11=""第一名"")*ROW(2:11),1))}"

例341,"提取销量的前三名的外销产品名称:{=LOOKUP(0,0/($B$2:$B$10*100+ROW($2:$10)=(LARGE(IF(RIGHT(A$2:A$10,3)=""外销）"",B$2:B$10*100+ROW($2:$10)),ROW(A1)))),A$2:A$10)}"

例342,"哪种产品生产次数最多:{=TEXT(MODE(B2:B9*1),""00"")}"

例343,"罗列出被投诉多次的工作人员编号:{=IFERROR(TEXT(MODE(IF(COUNTIF($D$1:D1,$B$2:$B$11)=0,$B$2:$B$11*1)),""00""),"""")}"

例344,"对学生成绩排名:=RANK(B2,B$2:B$11,0)"

例345,"计算两列数值相同个数:=COUNT(RANK(B2:B11,C2:C11))"

例346,"查询某人成绩在三个班中的排名:成绩{=LOOKUP(0,0/(E2:E11=H2),F2:F11)};名次=RANK(I2,(B2:B11,D2:D11,F2:F11),0)"

例347,"分别统计每个分数段的人员个数:{=FREQUENCY(B2:B11,D2:D5)}"

例348,"蝉联冠军最多的次数:{=MAX(FREQUENCY(ROW(B$2:B$11),(B$2:B$10<>B$3:B$11)*ROW(B$2:B$10)))}"

例349,"计算最多经过几次测试才成功:{=MAX(FREQUENCY(ROW(2:11),(B2:B11=""成功"")*ROW(2:11)))}"

例350,"计算三个不连续区间的频率分布:{=SUM(LOOKUP({1,3,5},ROW(1:5),FREQUENCY(B2:B11,{500,550,600,650})))}"

例351,"计算因密码错误被锁定几次:{=COUNT(0/((FREQUENCY(ROW(2:12),(B2:B12<>""错误"")*ROW(B2:B12))-1)>=3))}"

例352,"计算小学加初中人数及中专加大学人数:{=FREQUENCY((B2:B11<>""小学"")*(B2:B11<>""初中""),0)}"

例353,"计算文本的频率分布:{=FREQUENCY(CODE(B2:B11),CODE(D2:D5))}"

例354,"夺冠排行榜:{=IF(ROW(A1)>S