excel函数例题大全

1

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=\一车间\男\： 5，对姓赵的女职工工资求和:{=SUM((LEFT(A2:A10)=\赵\女\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=\男\ 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,\进库\14，计算仓库大额进库数量之和:=SUMIF(B2:B8,\：

15，对1400到1600之间的工资求和:{=SUM(SUMIF(B2:B10,\16，求前三名和后三名的数据之

和:=SUMIF(B2:B10,\， 17，对所有车间人员的工资求和:=SUMIF(A2:A10,\车间\18，对多个车间人员的工资求和:=SUMIF(A2:A10,\车间*\：

19，汇总姓赵、刘、李的业务员提成金额:=SUM(SUMIF(A2:A10,{\赵\刘\李\ 20，汇总鼠标所在列中大于600的数

据:=SUMIF(INDIRECT(\21，只汇总60～80分的成绩:=SUMIFS(B2:B10,B2:B10,\22，汇总三年级二班人员迟到次数:=SUMIFS(D2:D10,B2:B10,\三年级\二班\23，汇总车间女性人数:=SUMIFS(C2:C11,A2:A11,\车间\女\

24，计算车间男性与女性人员的差:=SUM(SUMIFS(C2:C11,B2:B11,{\女\男\车间\

25，计算参保人数:=SUMPRODUCT((C2:C11=\是\

26，求25岁以上男性人数:=SUMPRODUCT((B2:B10=\男\

2

27，汇总一班人员获奖次数:=SUMPRODUCT((B2:B11=\一班\

28，汇总一车间男性参保人数:=SUMPRODUCT((A2:A10&B2:B10&C2:C10=\一车间男是\29，汇总所有车间人员工资:=SUMPRODUCT(--NOT(ISERROR(FIND(\车间\30，汇总业务员业绩:=SUMPRODUCT((B2:B11={\江西\广东\男\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()&\

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=\46，求入库最多的产品数

量:{=MAX(MMULT(TRANSPOSE((B2:B11)*(A2:A11={\})*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=\50，求第一名人员最多有几次:{=MAX(MMULT(N(B2:B7=TRANSPOSE(B2:B7)),ROW(2:7)^0))}

3

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=\前进中学\

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组\

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),\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),\

4

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),)&\$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))}

5

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

6

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=\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,{\平均成绩\科目数量\最高成绩\最低成绩\成绩合计\124，不间断的序号:=SUBTOTAL(103,$B$2:B2)

125，仅对筛选出的人员排名次:{=CONCATENATE(\第\优等生!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=\130，判断学生是否符合奖学金发放条件:=AND(B2>90,C2<>\汉族\131，所有裁判都给“通过”就进入决赛:{=AND(B2:E2=\通过\132，判断身份证长度是否正确:=OR(LEN(B2)={15,18}) 133，判断歌手是否被淘汰:{=OR(B2:E2=\不通过\

134，根据年龄判断职工是否退休:=OR(AND(B2=\男\女\

135，根据年龄与职务判断职工是否退休:=OR(AND(B2=\男\干部\女\干部\

7

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,\不及格\良好\优秀\满分\

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<>\144，分别统计收入和支出:收入{=SUM(IF(B2:B13>0,B2:B13))};支出

{=SUM(IF(SUBSTITUTE(IF(B2:B13<>\负\负\145，将成绩从大到小排列:{=IF(ROW(A1)>COUNT(B$2:B$11),\146，排除空

值:{=INDEX($A:$B,SMALL(IF($B$1:$B$11<>\B2))&\

147，有选择地汇总数据:{=SUM(IF(A2:A11={\组\组\

148，混合单价求金额合计:{=SUM(ROUND(B2:B10*C2:C10*IF(D2:D10=\

149，计算异常停机时间:{=SUM(SUBSTITUTE(SUBSTITUTE(IF(C2:C11<>\修机\换原料\

150，计算最大数字行与文本行:{=MAX(IF(B:B<>\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，计算单元格中数字个

8

数:{=SUM((CODE(MID(A2,ROW(INDIRECT(\ECT(\158，计算单元格中大写加小写字母个

数:{=SUM((CODE(UPPER(MID(A2,ROW(INDIRECT(\A2,ROW(INDIRECT(\

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)),\年\月\日 \167，计算平均成绩及评判是否及格:=CONCATENATE(INT(AVERAGE(B2:D2)),\\不\及格\168，提取前三名人员姓

名:=CONCATENATE(LOOKUP(0,0/(B2:B11=LARGE(B2:B11,1)),A2:A11),\ARGE(B2:B11,2)),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(\

9

ROW(INDIRECT(\174，计算字符串中单词个

数:{=SUM(N(EXACT(TRIM(MID(UPPER(A2),ROW(INDIRECT(\2),ROW(INDIRECT(\

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

计算字符串中的数字个数:=SUMPRODUCT(N(ISNUMBER(VALUE(MID(A2,ROW($1:$100),1)*1)))) 176，提取混合字符串中的数

字:{=MAX(IFERROR(VALUE(MID(A2,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A2&1234567890)),ROW(INDIRECT(\

177，串联区域中的文本:=CONCATENATE(T(A2),T(B2),T(C2))

给公式添加运算说明:=CONCATENATE(\你好\公式含义:连接“你好”和单元格B2、“2008”\

178，根据身份证号码判断性别:=TEXT(MOD(MID(B2,15,3),2),\男;[=0]女\179，将所有数据转换成保留两位小数再求和:{=SUM(--TEXT(B2:B11*C2:C11,\180，将货款显示为“万元”为单位:=TEXT(B2,\￥#\万元\

181，根据身份证号码计算出生日期:=IF(LEN(B2)=15,19,\年00月00日\

182，显示今天的英文日期及星期:=\资料日期：\183，显示今天每项工程的预计完成时间:=TEXT(SUM(\上午/下午\184，统计A列有多少个星期日:{=SUM(N(TEXT(A1:A11,\日\185，将数据显示为小数点对齐:=TEXT(B2,\

186，计算A列的日期有几个属于第二季度:{=SUM((--(TEXT(A1:A11,\187，在A列产生1到12月的英文月份名:=TEXT((ROW())&\188，将日期显示为中文大写:=TEXT(\年m月d日\

189，将数字金额显示为人民币大写:=IF(MOD(B2,1)=0,TEXT(INT(B2),\通用格式元整;负[dbnum2]G/通用格式元整;零元整;\负\通用格式元;;\角0分;;\零角\零\零分\

190，判断单元格的数据类型:=TEXT(A2,\大于○;小于○;○;文本\

191，计算达成率，以不同格式显示:=TEXT(B2/800,\倍;[>0]0.00%;\

10

192，计算字母“A”的首次出现位置，忽略大小写:=TEXT(SEARCH(\没找到;第\个\

193，从身份证号码中提取表示性别的数字:=MID(B2,TEXT(LEN(B2),\194，将三列数据交换位置:{=TEXT({1,-1,0},C1:C5&\195，计算年终奖:=TEXT(B2,\

196，计算星期日完工的工程个数:{=COUNT((TEXT(B2:B10+C2:C10-1,\日\197，计算本月星期日的个

数:{=SUM(N(TEXT(TODAY()-TEXT(TODAY(),\AY(),\日\

198，检验日期是否升序排列:=TEXT(N(A3>=A2),\日期有误;\199，判断单元格中首字符的类

型:=TEXT(IF(AND(CODE(UPPER(A3))>64,CODE(UPPER(A3))<91),CODE(A3),A3),\&\字母;;数字;汉字\

200计算每个季度的天数:{=SUM(--TEXT(DATE(2008,3*ROW(A1)-ROW($1:$3)+2,),\201，将数据重复显示5次:=SUBSTITUTE(TEXT(A2&\？\？\202，将表示起止时间的数字格式化为时间格式:=TEXT(B2,\203，根据起止时间计算经过时

间:=TEXT(INT(((TEXT(RIGHT(B4,4),\MOD(((TEXT(RIGHT(B4,4),\小时.00分钟\

204，将数字转化成电话格式:=TEXT(A2,\

205，在A1:A7区域产生星期一到星期日的英文全称:{=TEXT(ROW(1:7)+1,\206，将汇总金额保留一位小数并显示千分位分隔符:{=FIXED(SUM(--FIXED(B2:B11*C2:C11,1)),1,FALSE)}

207，计算订单金额并以“百万”为单位显示:=FIXED(SUMPRODUCT(B2:B10,C2:C10),-6)/1000000 208，将数据对齐显示，将空白以“．”占位:=WIDECHAR(REPT(\

209，利用公式制作简易图表:=IF(B2>0,REPT(\ \｜\ \ \2)/2)&B2&REPT(\｜\ \210，利用公式制作带轴的图表且标示升降:{=IF(A2<>\┫\〓\ \

11

\1,0),\-1,0),\B2))&B2&REPT(\ \

\LEN(B2)/2)&B2&REPT(\ \1,0),\-1,0),\ \

211，计算单元格中数字个数:=LEN(A2)*2-LENB(A2) 212，将数字倒序排

列:{=TEXT(SUM(MID(A2,ROW(INDIRECT(\))-1)),REPT(0,LEN(A2)))}

213，计算购物金额中小数位数最多是

几:{=MAX(LEN(B2:B10*C2:C10)-LEN(INT(B2:B10*C2:C10)))-1}

214，计算英文句子中有几个单词:=LEN(A2)-LEN(SUBSTITUTE(SUBSTITUTE(A2,\215，将英文句子规范化:=PROPER(LEFT(A2))&TRIM(RIGHT(A2,LEN(A2)-1)) 216，分别提取省市县名:=TRIM(MID(SUBSTITUTE($A2,\\

217，提取英文名字:=LEFT(A2,FIND(\

218，将分数转换成小数:=(LEFT(A2,FIND(\219，从英文短句中提取每一个单词:=IFERROR(MID($A2,FIND(\\

\220，将单位为“双”与“片”混合的数量汇

总:{=SUM(IF(ISNUMBER(FIND(\:C9)-FIND(\片\

221，提取工作表名:=RIGHT(CELL(\222，根据产品规格计算产品体

积:=PRODUCT(LEFT(B2,FIND(\IND(\

223，提取括号中的字符串:=IFERROR(MID(A2,FIND(\224，分别提取长、宽、高:=MID($B2,FIND(\（\）

12

\（\225，提取学校与医院地址:{=IF(OR(IFERROR(FIND({\学校\医院\226，计算密码字符串中字符个

数:{=COUNT(FIND(CHAR(ROW(65:90)),A2),FIND(CHAR(ROW(97:122)),A2),FIND(ROW(1:10)-1,A2))}

227，通讯录单列转三

列:{=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)} 228，将15位身份证号码升级为18

位:{=IF(LEN(B2)=18,B2,LEFT(REPLACE(B2,7,,19),17)&MID(\ACE(B2,7,,19),ROW(INDIRECT(\229，将产品型号规范化:=IF(MID(A2,5,2)=\

230，求最大时间:{=TEXT(MAX(--TEXT(REPLACE(LEFT(A2:A7,7),5,1,RIGHT(A2:A7,2)),\00-00\231，分别提取小时、分钟、

秒:=REPLACE(REPLACE($A$1&$A2,FIND(B$1,$A$1&$A2),100,),1,FIND(A$1,$A$1&$A2)+1,) 232，将年级或者专业与班级名称分

开:{=REPLACE(A2,MAX(IFERROR(SEARCH(CHAR(ROW($65:$90)),A2),0)),10,)} 233，提取各软件的版本

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

235，查找编号中重复出现的数字:重复数字个数

{=COUNT(SEARCH((ROW($1:$10)-1)&\重复字符

=IF(COUNT(SEARCH(\$1:$9)&\236，统计名为“刘星”者人数:{=COUNT(SEARCH(\刘星\

237，剔除多余的省名:=SUBSTITUTE(A2,IF(ISERROR(SEARCH(\重庆市\四川省\

13

238，将日期规范化再求差:=SUBSTITUTE(C2,\239，提取两个符号之间的字符串:=TRIM(MID(SUBSTITUTE(B2,\\

240，产品规格格式转换:=SUBSTITUTE(SUBSTITUTE(A2,\：\241，判断调色配方中是否包含色粉“B”:=LEN(SUBSTITUTE(B2,\242，提取姓名与省份:=TRIM(MID(A2,1,FIND(\\

243，将IP地址规范化:=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(\244，提取最后一次短跑成绩:=REPLACE(A2,1,FIND(\々\々\

245，从地址中提取省名:=LEFT(A2,FIND(\省\

246，计算小学参赛者人数:{=COUNT(0/(LEFT(B2:B11)=\小\

247，计算四川方向飞机票总价:=SUMPRODUCT(N(LEFT(A2:A11,2)=\四川\飞机\

248，通过身份证号码计算年

龄:=TEXT(TODAY(),\2))

249，从混合字符串中取重量:=LOOKUP(9E+307,--LEFT(B2,ROW($1:$10)))*C2 250，将金额分散填充:=LEFT(RIGHT(\￥\251，提取成绩并计算平均:{=AVERAGE(MID(A2:A7,4,LEN(A2:A7)-3)*1)} 252，提取参赛选手姓名:=MID(A2,FIND(\：\253，从混合字符串中提取金

额:=LOOKUP(307,--MID(B2,MIN(FIND({1;2;3;4;5;6;7;8;9},B2&123456789)),ROW($1:$99))) 254，从卡机数据提取打卡时间:=730>--MID(A2,14,4)

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

256，根据身份证号码统计男性人数:{=SUM(MOD(LEFT(RIGHT(B2:B11,1+(LEN(B2:B11)=18))),2))} 257，从汉字与数字混合字串中提取温度数

据:{=MAX(IFERROR(--RIGHT(LEFT(B2,LEN(B2)-1),ROW($1:$10)),0))} 258，将字符串位数统

14

一:{=TEXT(RIGHT(A2,LEN(A2)-1),\259，对所有人员按平均分排

序:{=INDEX(A:A,RIGHT(LARGE(B$2:B$11*1000+ROW($2:$11),ROW()-1),3))} 260，取金额的角位与分位叫:=--RIGHT(ROUND(A2*100,),2)

261，从格式不规范的日期中取出日:=TRIM(RIGHT(SUBSTITUTE(A2,\\262，计算平均成绩(忽略缺考人员):=ROUND(AVERAGE(B2:B10),2) 263，计算90分以上的平均成

绩:{=ROUND(AVERAGE(IF(ISNUMBER(B2:B10)*(B2:B10>90),B2:B10)),2)} 264，计算当前表以外的所有工作表平均值2:=AVERAGE(一班:五班!B:B)

265，计算二车间女职工的平均工资:{=AVERAGE(IF((B2:B10=\二车间\女\266，计算一车间和三车间女职工的平均工资:{=AVERAGE(IF((B2:B10=\一车间\三车间\女\

267，计算各业务员的平均奖金:{=AVERAGE(1500+300*(INT((C2:C11-80000)/10000)))} 268，计算平均工资(不忽略无薪人员):=ROUND(AVERAGEA(B2:B10),2) 269，计算每人平均出口量:{=AVERAGEA((C2:C11=\270，计算平均成绩，成绩空白也计算:{=AVERAGEA(B2:B11*1)}

271，计算二年级所有人员的平均获奖率:{=TEXT(AVERAGEA(IF(LEFT(A2:A10,3)=\二年级\

272，统计前三名人员的平均成绩:=AVERAGEA(LARGE(B2:B11,{1,2,3})) 273，求每季度平均支出金额:=AVERAGEIF(B2:B9,\支出\

274，计算每个车间大于250的平均产量:=AVERAGEIF(B2:C11,\

275，去掉首尾求平均:=AVERAGEIFS(B2:B11,B2:B11,\276，生产A产品且无异常的机台平均产量:=AVERAGEIFS(C2:C11,B2:B11,\277，计算生产车间异常机台个数:=COUNT(C2:C11)

278，计算及格率:{=TEXT(COUNT(0/(B2:B11>=60))/COUNT(B2:B11),\

279，统计属于餐饮娱乐业的店名个数:{=COUNT(SEARCH({\小吃\酒吧\茶\咖啡\电影\休闲\网吧\

280，统计各分数段人数:{=COUNT(0/((B$2:B$11>ROW(A6)*10)*(B$2:B$11<=ROW(A7)*10)))} 281，统计有多少个选手:{=COUNT(0/(MATCH(B2:B11,B2:B11,)=(ROW(2:11)-1)))} 282，统计出勤异常人数:=COUNTA(B2:B11)

15

283，判断是否有人缺考:=IF(COUNTA(B2:E10)=ROWS(B2:E10)*COLUMNS(B2:E10),\没有\有\284，统计未检验完成的产品数:=COUNTBLANK(B2:B11)

285，统计产量达标率:=TEXT(COUNTIF(B2:B11,\286，根据毕业学校统计中学学历人数:=COUNTIF(B2:B11,\中学\287，计算两列数据相同个数:{=SUM(COUNTIF(A2:A11,B2:B11))} 288，统计连续三次进入前十名的人

数:{=SUM(COUNTIF(C2:C11,IF(COUNTIF(A2:A11,B2:B11),B2:B11)))} 289，统计淘汰者人数:{=SUM(N(COUNTIF(A2:C11,A2:C11)=1))} 290，统计区域中不重复数据个数:{=SUM(1/COUNTIF(B2:B8,B2:B8))}

291，统计诺基亚、摩托罗拉和联想已隹出手机个数:=SUM(COUNTIF(B2:B11,\诺基亚\摩托罗拉\联想\

292，统计联想比摩托罗拉手机的销量高多少:{=SUM(COUNTIF(B2:B11,{\诺基亚*\联想*\293，统计冠军榜前三

名:{=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)))}

294，统计真空、假空单元格个数:=COUNTIF(成绩!C2:C11,\

295，对名册表进行混合编号:=IF(RIGHT(B1)<>\班\班\班\

296，提取不重复数据5:{=INDEX(B:B,MATCH(0,COUNTIF($D$1:D1,B$2:B$11),0)+1)} 297，中国式排名:{=SUM(IF(B$2:B$11>B2,1/COUNTIF(B$2:B$11,B$2:B$11)))+1} 298，统计大于80分的三好学生个数:{=COUNTIFS(B2:B11,\三好学生\299，统计业绩在6万到8万之间的女业务员个数:=COUNTIFS(B2:B11,\女\

300，统计二班和三班数学竞赛获奖人数:=SUM(COUNTIFS(B2:B11,{\二班\三班\数学*\301，根据身高计算各班淘汰人数:=SUM(COUNTIFS(B$2:B$11,E1,C$2:C$11,{\302，计算A列最后一个非空单元格行号:{=MAX((A:A<>\303，计算女职工的最大年龄:{=MAX((B2:B11=\女\

304，消除单位提取数据:{=MAX(IFERROR(ABS(LEFT(A2,ROW($1:$100))),))*IF(LEFT(A2)=\

16

305，计算单日最高销售金额:{=MAX(SUMIF(A2:A11,A2:A11,C2:C11))} 306，查找第一名学生姓名:=INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10,))

307，统计季度最高产值合计:{=MAX(SUBTOTAL(9,OFFSET(B2,,COLUMN(B:E)-2,ROWS(2:10),1)))} 308，根据达标率计算员工奖金:=MAX((B2>{0,0.8,0.9,1,1.05})*{200,250,300,450,550}) 309，提取产品最后报价和最高报价:{=INDEX(C:C,MAX((A2:A11=\

310，计算卫冕失败最多的次数:{=MAX(FREQUENCY(ROW(2:11),((B2:B10=\第一名\第一名\

311，低于平均成绩中的最优成绩:{=MAX(IF(B2:B11

316，显示动态日期，但不能超过9月30日:=MIN(\

317，根据工作时间计算可休假天数:=MIN(SUM((B2={\318，确定最佳成绩:=MATCH(MIN(B2:B11),B2:B11,)

319，计算文具类产品和家具类产品最小利率:{=TEXT(MIN(IF(ISNUMBER(SEARCH(\（?具类\

320，计算得票最少者有几票:{=MIN(COUNTIF(B2:C11,B2:C11))} 321，根据工程的难度系数计算奖金:=MIN(A2,1+(A2>1.3)*0.3)*500 322，将科目与成绩分

开:{=MID(A2,MIN(IF(ISNUMBER(FIND(ROW($1:$9),A2)),FIND(ROW($1:$9),A2))),100)}

323，计算五个班的第一名人员的最低成绩:=MIN(SUBTOTAL(4,INDIRECT({\一\二\三\四\五\班!B2:b11\

324，根据员工生产产品的废品率记分:=MAX(MIN(6-(B2*100-5),10),0) 325，统计售价850元以上的产品最低利率是多少:=DMIN(A1:D11,F4,F1:F2) 326，统计文具类和厨具类产品的最低单价:=DMIN(A1:B11,2,D1:D2) 327，第三个最小的成绩:=SMALL(B2:B11,3)

328，计算最后三名成绩的平均值:=AVERAGE(SMALL(B2:B11,{1,2,3})) 329，将成绩按升序排列:{=SMALL(B$2:B$11,ROW(A1))}

330，罗列三个班第一名成绩:{=SMALL(IF(C$2:C$11=\第一名\

17

331，将英文月份名称升序排

列:{=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)))&\332，查看产品曾经销售的所有价

位:{=IF(ROW(A1)>SUM(1/COUNTIF(B$2:C$11,B$2:C$11)),\$11,\

333，罗列三个工作表B列最后三名成绩:=SMALL(一班:三班!B:B,ROW(A1)) 334，第3个最小成绩到第6个最小成绩之间的人

数:{=SUM((((SMALL(B2:D11,ROW(INDIRECT(\,-1})))}

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

336，计算大于等于前10个最大产量之和:=SUMPRODUCT((B2:C11>LARGE(B2:C11,11))*B2:C11) 337，按成绩列出学生排行

榜:{=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))}

338，最后一次获得第一名是第几届:{=INDEX(A:A,LARGE((B2:B11=\第一名\339，提取销量的前三名的外销产品名

称:{=LOOKUP(0,0/($B$2:$B$10*100+ROW($2:$10)=(LARGE(IF(RIGHT(A$2:A$10,3)=\外销）\340，哪种产品生产次数最多:{=TEXT(MODE(B2:B9*1),\341，罗列出被投诉多次的工作人员编

号:{=IFERROR(TEXT(MODE(IF(COUNTIF($D$1:D1,$B$2:$B$11)=0,$B$2:$B$11*1)),\342，对学生成绩排名:=RANK(B2,B$2:B$11,0)

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

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

345，分别统计每个分数段的人员个数:{=FREQUENCY(B2:B11,D2:D5)} 346，蝉联冠军最多的次

数:{=MAX(FREQUENCY(ROW(B$2:B$11),(B$2:B$10<>B$3:B$11)*ROW(B$2:B$10)))} 347，计算最多经过几次测试才成功:{=MAX(FREQUENCY(ROW(2:11),(B2:B11=\成功

18

\

348，计算三个不连续区间的频率分

布:{=SUM(LOOKUP({1,3,5},ROW(1:5),FREQUENCY(B2:B11,{500,550,600,650})))} 349，计算因密码错误被锁定几次:{=COUNT(0/((FREQUENCY(ROW(2:12),(B2:B12<>\错误\

350，计算小学加初中人数及中专加大学人数:{=FREQUENCY((B2:B11<>\小学\初中\

351，计算文本的频率分布:{=FREQUENCY(CODE(B2:B11),CODE(D2:D5))} 352，夺冠排行

榜:{=IF(ROW(A1)>SUM(1/COUNTIF($B$2:$B$11,$B$2:$B$11)),\GE(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)%,)))} 353，谁蝉联冠军次数最

多:=INDEX(B2:B11,MATCH(MAX(FREQUENCY(ROW(2:11),(B2:B10<>B3:B11)*ROW(2:10))),FREQUENCY(ROW(2:11),(B2:B10<>B3:B11)*ROW(2:10)),))

354，中国式排名:{=SUM(--(IF(FREQUENCY(B$2:B$11,B$2:B$11),B$2:B$11>B2)))+1} 谁获得第二

名:{=INDEX(A:A,SMALL(IF(B$2:B$11=SMALL(IF(FREQUENCY($B$2:$B$11,$B$2:$B$11),$B$2:$B$11),2),ROW($2:$11),1048576),ROW(A1)))&\

355，记录当前日期与时间:=TEXT(NOW(),\月d日 h:m:s\356，确定是否已到加油时间:=TEXT(NOW()-B2,\357，国庆倒计时:=TEXT(\

358，统计发货到收款天数:=ROUNDUP(IF(B2<>\359，统计已到达收款时间的货品数量:=COUNTIF(B2:B10,\360，本月需要完成几批货物生产:{=SUM(N(B2:B11=TEXT(TODAY(),\361，计算本季度收款的合

计:{=SUM(IF(ROUNDUP(B2:B11/3,0)=ROUNDUP(TEXT(TODAY(),\362，判断今年是否闰

年:=OR((MOD(TEXT(TODAY(),\EXT(TODAY(),\

19

363，计算2008年有多少个星期

日:{=SUM(N(TEXT(DATE(2008,1,ROW(INDIRECT(\日\

364，计算本月有多少天:=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0),\365，确定今年母亲节的日

期:=DATE(YEAR(TODAY()),5,14-WEEKDAY(DATE(YEAR(TODAY()),4,30),2)) 366，今年包含多少个星

期:{=SUM(N(WEEKDAY(DATE(YEAR(TODAY()),1,ROW(INDIRECT(\(TODAY()),2,29))=29))))),2)=7))+(WEEKDAY(DATE(YEAR(TODAY()),1,365+(DAY(DATE(YEAR(TODAY()),2,29))=29)))<7)}

367，将身份证号码转换成出生日期序

列:=DATE(MID(B2,7,2+(LEN(B2)=18)*2),MID(B2,9+(LEN(B2)=18)*2,2),MID(B2,11+(LEN(B2)=18)*2,2))

368，计算建国多少周年:=YEAR(TODAY())-1949

369，计算2000年前电脑培训平均收费:{=AVERAGE(IF(YEAR(A2:A11)<2000,B2:B11))} 370，计算今天离本年度最后一天的天数:=(YEAR(TODAY())&\371，计算本月需要交货的数量:{=SUM((MONTH(B2:B11)=MONTH(TODAY()))*C2:C11)}

372，计算8月份笔筒和毛笔的进货数量:{=SUM(IF(MONTH(A2:A11)=8,IF((B1:H1=\笔筒\毛笔\

373，计算交货起止月:{=MIN(MONTH(B2:B11))&\月-\月\374，有几个月要交货:{=COUNT(0/FREQUENCY(MONTH(B2:B11),MONTH(B2:B11)))} 375，哪几个月要交

货:{=IFERROR(SMALL(IF(FREQUENCY(MONTH(B$2:B$11),MONTH(B$2:B$11)),MONTH(B$2:B$11)),ROW(A1))&\月\

376，统计家具类和文具类产品在1月份的出库次数:{=SUM((B2:B11={\文具类\家具类\

377，计算今年平均每月天数:{=AVERAGE(DAY(DATE(YEAR(TODAY()),ROW(2:13),0)))} 378，计算员工转正时间:=DATE(YEAR(B2),MONTH(B2)+3+(DAY(B2)>15),16) 379，统计本月下旬出库数量:{=SUM(C2:C11*(DAY(B2:B11)>20))} 380，计算生产速度是否达标:=YEARFRAC(C2,D2)<=(E2/B2)

20

381，计算截至今天的利息:=B2*D2*YEARFRAC(C2,NOW()) 382，计算还款日期:=TEXT(EDATE(B2,C2),\

383，计算2008年到2010年共有多少天:{=SUM(DAY(EDATE(\384，提示合同续约:=TEXT(EDATE(B2,C2*12)-TODAY(),\合同过期;[<=10]即将到期;;\385，计算借款日期到本月底的天数:=EOMONTH(B2,MONTH(TODAY())-MONTH(B2))-B2 386，计算本季度天数:=SUM(DAY(EOMONTH(NOW(),{0,1,2}-MOD(MONTH(NOW())-1,3)))) 387，生成工资结算日期:=TEXT(EOMONTH(B2,0)+1,\年M月D日\388，统计两倍工资的加班小时

数:=SUMPRODUCT(--(TEXT(ROW(INDIRECT(B2&\六\389，计算员工工作天数和月数:=DATEDIF(B2,C2,\390，根据进厂日期计算员工可假休天

数:=MIN(IF(DATEDIF(B2,TODAY(),\B2,TODAY(),\

391，根据身份证号码计算年龄(包括年月

天):=CONCATENATE(DATEDIF(TEXT(MID(B2,7,LEN(B2)/2-1),\年\月\天\

392，计算年资:=10*MIN(DATEDIF(B2,TODAY(),\393，计算临时工的工资:=ROUND(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(B2,\分\小时\394，计算本日工时工

资:=(HOUR(C2-TIMEVALUE(\ALUE(\395，计算8：00一16：00的平均电

压:{=AVERAGE(IF((DAY(A2:A11)=8)*(HOUR(A2:A11)>=8)*(HOUR(A2:A11)>=16),B2:B11))} 396，计算工作时间，精确到分

钟:=HOUR(C2)+MINUTE(C2)/60-HOUR(B2)-MINUTE(B2)/60-D2+24*(C2

金:=IF(HOUR(B2)>=18,-(ROUNDUP((HOUR(B2-\N((HOUR(\

398，计算工程时间:=SUMPRODUCT(MINUTE(B2:B11)+(SECOND(B2:B11)>0))

21

399，计算今天是星期几:=WEEKDAY(NOW(),2)

400，汇总星期日的支出金额:{=SUM((WEEKDAY(A2:A11,2)=7)*(B2:B11=\支出\401，汇总第一个星期的出库数

量:{=SUM(OFFSET(A2,,MIN(IF(WEEKDAY(B1:P1,2)=1,COLUMN(B:P))),,7))} 402，计算每日工时工资:=8*5*IF(WEEKDAY(A2,2)<6,1,1.5)+(B2-8)*5*1.5 403，计算指定日期所在月份有几个星期

日:{=SUM(N(WEEKDAY(DATE(YEAR(A2),MONTH(A2),ROW(INDIRECT(\2,0))))))=1))} 404，按周汇总产

量:{=SUM(((WEEKDAY($B1,2)-WEEKDAY($B1:$AF1,2))+(COLUMN($B1:$AF1)-1)=(1+(COLUMN(A1)-1)*7))*$B2:$AF2)} 405，按周汇总进仓与出仓数

量:{=SUM(((WEEKDAY($B1,2)-WEEKDAY($B1:$BK1,2))+INT((COLUMN($B1:$BK1))/2)=(1+(INT((COLUMN(A1)+1)/2)-1)*7))*$B3:$BK3*($B2:$BK2=B7))} 406，罗列本月休息

日:{=IFERROR(SMALL(IF(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),ROW(INDIRECT(\\&DAY(EOMONTH(NOW(),0)))))),ROW()),\

407，计算周末奖金补贴:=SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(B2&\408，罗列值班日

期:{=MIN(IF(WEEKDAY(DATE(2008,ROW(),ROW($1:$31)),2)=7,DATE(2008,ROW(),ROW($1:$31))))}

409，计算本月加班时

间:{=SUM((MOD(MOD(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),ROW(INDIRECT(\DAY(EOMONTH(NOW(),0))))),2),7),2)={1,0})*{3,2})} 410，今天是本年度第几周:=WEEKNUM(TODAY()) 411，本月包括多少

周:=WEEKNUM(EOMONTH(NOW(),0),2)-WEEKNUM((EOMONTH(NOW(),-1)+1),2)+1 412，罗列第30周日

期:{=TEXT(SMALL(IF(WEEKNUM(DATE(YEAR(NOW()),1,ROW($1:$366)),2)=30,DATE(YEAR(NO

22

W()),1,ROW($1:$366))),ROW(A1)),\413，统计某月第四周的支出金

额:{=SUM((WEEKNUM(A2:A11*1,1)-WEEKNUM(YEAR(A2:A11)&\4)*B2:B11)} 414，判断本月休息

日:{=(SUM(N(WEEKNUM(ROW((INDIRECT((EOMONTH(NOW(),-1)+1)&\)))),2)-WEEKNUM(EOMONTH(NOW(),-1)+1,2)+1=5))>3)+4}

415，计算离职日期:=WORKDAY(A2,5,{\416，计算工程完工日

期:{=WORKDAY(A2,B2,EOMONTH(A2,ROW(INDIRECT(\417，计算2008年第一季度有多少个工作

日:=NETWORKDAYS(EOMONTH(NOW(),-MONTH(NOW()))+1,EOMONTH(NOW(),3-MONTH(NOW())),{\418，计算2008年第一季度有多少个非工作

日:=EOMONTH(NOW(),3-MONTH(NOW()))-EOMONTH(NOW(),-MONTH(NOW()))-NETWORKDAYS(EOMONTH(NOW(),-MONTH(NOW()))+1,EOMONTH(NOW(),3-MONTH(NOW())),{\008-2-8\

419，计算今天离国庆节还有多少个工作

日:=NETWORKDAYS(TODAY(),DATE(YEAR(TODAY())+(TODAY()>DATE(YEAR(TODAY()),10,1)),10,1))

420，填充12个月的月份名:=CONCATENATE(\第\月\421，产生“坐标”:=CHAR(64+COLUMN(A1)) 422，检查日仓库报表日期是否正

确:{=IF(SUM(N((11-RANK(A2:A11,A2:A11))=(ROW(2:11)-1)=FALSE)),\非递增\递增\423，检查字符串中哪一个字符出现次数最

多:{=CHAR(MODE(IFERROR(CODE(MID(A2,ROW(1:16),1)),\424，产生每两行累加1的编

号:=IF(ROW()=1,1,IF(MOD(ROW(),3),COUNT(OFFSET(A$1,,,ROW()-1))+1,\425，最后一次不及格是哪次测试:{=INDEX(A:A,MAX((B2:B11<60)*ROW(2:11)))} 426，计算第11名到第30名学员的平均成

23

绩:{=AVERAGE(IF(RANK(B2:B101,B2:B101)=TRANSPOSE(ROW(11:30)),B2:B101))} 427，计算成绩排名，不能产生并列名

次:=SUMPRODUCT(--((A$2:A$15=A2)*(($C$2:$C$15)+1/ROW($C$2:$C$15))>C2+1/ROW(2:2)))+1 428，计算第一次收入金额大于30元时的金额是多

少:=INDEX(B:B,MIN(IF((A2:A11=A2)*(B2:B11>30),ROW(2:11))))

429，计算扣除所有扣款后的最高薪资:{=MAX(B2:B10-MMULT(C2:G10*1,ROW(1:5)^0))} 430，对班级和成绩升序排

列:{=1*MID(SMALL(1*($A2:$A12&TEXT($B2:$B12,\431，罗列今日销售的诺基亚手机型号:{=T(INDEX(B:B,SMALL(IF(ISERROR(FIND(\诺基亚\

432，统计图书数量:{=IF(B2=\433，罗列第一名学生姓

名:{=T(INDEX(A:A,SMALL(IF($B$2:$B$11=MAX(B$2:B$11),ROW($2:$11),12),ROW(A1))))} 434，罗列1到1000之间的质

数:{=INDEX(A:A,SMALL(IF(A$2:A$1000<>\435，判断某数是否为质数:{=IF(A2<2,\非质非合

\质数\合数\

436，计算某个数的约数个数及罗列所有约数:约数个数

{=COUNT(0/(MOD(A2,ROW(INDIRECT(\罗列约数

{=IFERROR(SMALL(IF(MOD(A$2,ROW(INDIRECT(\OW(A1)),\

437，将六个号码组合成一个:{=SUM(B1:B6*10^(2*(ROWS(B1:B6)-ROW(1:6))))} 438，将每个人的贷款重新分

组:{=INDEX($C:$C,SMALL(IF($A$2:$A$11=$E2,ROW($2:$11),ROWS($1:$12)),COLUMN(A1)))} 439，检测每个志愿是否与之前的重复:=MATCH(B2,$B$2:$B$10,)<>ROWS($2:2) 440，将列标转换成数字:=COLUMN(INDIRECT(A2&1)) 441，重组人事资料

表:=REPLACE(INDIRECT(\442，班级成绩查

24

询:{=INDEX($B:$E,SMALL(IF($A$2:$A$12=$H$2,ROW($2:$12),ROWS($1:$12)+1),ROW(A1)),COLUMN(A1))&\

443，罗列每日缺席名单:{=INDEX(全体成员!$1:$1,SMALL(IF(COUNTIF($B2:$K2,全体成员!$A$1:$M$1)=0,COLUMN($A:$M),16384),COLUMN(A1)))&\444，计算所有人的一周产量并排

名:{=INDEX(1:1,RIGHT(LARGE(SUBTOTAL(9,OFFSET($A2:$A8,,COLUMN($B:$J)-1,,))*10+COLUMN($B:$J)-1,COLUMN(A1)))+1)} 445，将金额分散填充，空位以“-”占

位:=MID(TEXT(INT($A2*100),REPT(\($A:A),1)

446，提取引用区域右下角的数

据:=INDIRECT(ADDRESS(ROW(B3:D7)+ROWS(B3:D7)-1,COLUMN(B3:D7)+COLUMNS(B3:D7)-1)) 447，整理成绩单:=INDIRECT(CHAR(ROWS($1:22)*3)&COLUMN())

448，合并三个工作表的数据:=INDIRECT(CHOOSE(MOD(ROW(A2)-1,3)+1,\一年

级!A\二年级!A\三年级!A\449，多区域计数:=SUM(COUNTIF(INDIRECT({\450，求积、求和两相

宜:=SUM(IF(C2=\(ROW()-1)),C2*D2))

451，计算五个工作表最大平均值:{=MAX(SUBTOTAL(1,INDIRECT({\一\二\三\四\五\班!B2:b11\

452，按卡号中的英文及数值排

序:{=INDIRECT(\),ROW(B1)),100))} 453，多行多列取唯一

值:{=IF(OR((B$2:D$5<>\\454，罗列三个表中的最大值:{=SUBTOTAL(4,INDIRECT({\组\组\组\455，将三列课程转换成单列且忽略空

值:{=INDIRECT(TEXT(SMALL(IF($B$2:$D$7<>\

25

000\

456，罗列两个正整数的所有公约

数:{=IFERROR(SMALL(IF((MOD(A$2,ROW(INDIRECT(\W(INDIRECT(\457，B列最大值的地址:{=ADDRESS(MAX(IF(B2:B11=MAX(B2:B11),ROW(2:11))),2)} 458，记录最后一次销量大于3000的地

址:{=ADDRESS(MOD(MAX((IF(ISNUMBER(B2:D7),B2:D7,0)>3000)*ROW(B2:D7)+(IF(ISNUMBER(B2:D7),B2:D7,0)>3000)*COLUMN(B2:D7)*1000),1000),INT(MAX((IF(ISNUMBER(B2:D7),B2:D7,0)>3000)*ROW(B2:D7)+(IF(ISNUMBER(B2:D7),B2:D7,0)>3000)*COLUMN(B2:D7)*1000)/1000))} 459，根据下拉列表引用不同工作表的产量:=INDIRECT(ADDRESS(11,2,1,1,D1)) 460，根据下拉列表罗列班级成绩第一名姓

名:{=IFERROR(INDIRECT(ADDRESS(LARGE(((INDIRECT(D$1&\&\

461，查询成绩:=OFFSET(A1,MATCH(F1,A2:A11,0),MATCH(G1,B1:D1,0))

462，在具有合并单元格的A列产生自然数编号:=1+COUNT(OFFSET($A$2,,,ROW()-2,)) 463，引用合并区域时防止产生0值:=IF(A1<>\464，计算10届运动会中有几次破纪

录:=SUMPRODUCT(N(SUBTOTAL(5,OFFSET(B2,,,ROW(2:10)))

465，计第奎续三天之总产量大于等于25万元的次

数:=SUMPRODUCT(N(SUBTOTAL(9,OFFSET($B$1,ROW(1:10)-1,,3))>=25))

466，进、出库合计查询:=SUM(OFFSET(A1,E2,MATCH(G2&\总计\467，根据人数自动调整表格大

小:{=IFERROR(OFFSET($E$1,SMALL(IF(F$2:F$5>=TRANSPOSE(ROW(INDIRECT(\F$5)))),ROW($2:$5)-1),ROW(1:1)),),\

468，累计数据:{=SUM(OFFSET(B$2,,,ROW()-1))} 469，计算至少两科不及格的学生人

数:{=SUM(--(COUNTIF(OFFSET($B$1,ROW(2:11)-1,,,4),\470，列出成绩最好的科

目:{=OFFSET(A2,,SUM((MAX(SUBTOTAL(9,OFFSET(A2,1,ROW(1:4),4)))=SUBTOTAL(9,OFFSET(A

31

组!$B$2:$I$2,$B$2:$B$9)*COUNTIF(B

组!$C$2:$D$4,$B$2:$B$9),ROW($B$2:$B$9),10),ROW(A4)))&\533，不区分大小写查找单价:=VLOOKUP(B2,单价表!A$2:C$11,3,0)*C2

534，乱序资料表中查找多个项目:=VLOOKUP($B2,单价表!$A$2:$E$11,MATCH(C$1,单价表!$A$1:$E$1,0),0)

535，将得分转换成等级:=VLOOKUP(B2,{0,\536，查找美元与人民币报价:=VLOOKUP(B2,INDIRECT(E2&\报价!A2:B9\

537，多条件查找:{=VLOOKUP(A2&B2&C2,IF({1,0},资料表!A2:A11&资料表!B2:B11&资料表!D2:D11,资料表!C2:C11),2,0)}

538，查找最后更新单价:{=VLOOKUP(10^16,--LEFT(VLOOKUP(B2,单价

表!A:Z,COUNTA(INDIRECT(\单价表!A\单价表!A:A,0)&\单价表!A:A,0))),0),ROW($1:$16)),1)} 539，查找双列信

息:{=VLOOKUP(A9,CHOOSE({3,2,1},A1:A6&B1:B6,C1:C6&D1:D6,E1:E6&F1:F6),{2,3},)} 540，提取姓名拼音的首字母:=VLOOKUP(LEFT(A2),拼音,2)&VLOOKUP(MID(A2,2,1),拼音,2)&VLOOKUP(MID(A2,3,1),拼音,2)

541，用不确定条件查找:{=VLOOKUP(A2&\资料表!A2:A10,A2)=0,资料表!B2:B10,资料表!A2:A10),资料表!E2:E10),2,0)}

542，按学历对姓名排序:{=VLOOKUP(MOD(SMALL(MATCH(B$2:B$10,{\大学\高中\初中\小学\543，使用通配符进行查找:{=VLOOKUP(\资料表!B$2:B$9,资料表!A$2:A$9),2,0)} 544，多工作表查找最大

值:{=TEXT(VLOOKUP(MAX(SUBTOTAL(9,INDIRECT(TEXT(ROW(1:6),\年级!B\年级!B\545，对带有合并单元格的区域查找年假天

数:=VLOOKUP(F2,OFFSET(B2,MATCH(E2,A2:A13,0)-1,,4,2),2)

546，查找某业务员在某季度的销量:=HLOOKUP(G2,A1:E9,MATCH(H2,A:A,0),0) 547，在同一行查找数据:{=HLOOKUP(MAX(A2:H2),IF({1;0},B2:H2,A2:G2),2,FALSE)}

32

548，计算两个产品不同时期的单价:=HLOOKUP(MONTH(A2),IF(B2=\塑胶机\549，多条件计算加班

费:=TEXT(HOUR(B2)+HLOOKUP(MINUTE(B2),{0,20.0001,50.0001;0,0.5,1},2),\+HLOOKUP(MINUTE(B2),{0,20.0001,50.0001;0,0.5,1},2) 550，根据进厂日期计算有薪假天

数:=HLOOKUP(DATEDIF(B2,TODAY(),\551，制作准考证:=HLOOKUP(B2,学生档案

库!$1:$11,ROUNDUP(COLUMN()/5,0)+1+INT(ROW()/7)*2,FALSE)

552，不区分大小写判断两列相同数据个数:{=COUNT(MATCH(A2:A11,B2:B11,0))} 553，按汉字评语进行排序:{=INDEX(A:B,MOD(SMALL(MATCH($B$2:$B$12,排名标准!$A$2:$A$9,)*100+ROW($B$2:$B$12),ROW(2:12)-1),100),{1,2})} 554，提取A列最后一个数据:{=INDIRECT(\555，提取字符串中的汉字:{=MID(A2,MATCH(1,1/(MID(A2,ROW($1:$99),1)>=\啊\TCH({1,2},1/(MID(A2,ROW($1:$99),1)>=\啊\556，将文件号中的中文大写转小写:{=\第

\TCH(MID(A2,{2,3,4},1),TEXT(ROW($1:$10)-1,\00\号文件\

557，计算补课科目总数:{=COUNT(0/(MATCH(B2:B8,B2:B8,0)=ROW(2:8)-1))}，

558，产生混合编号:=TEXT(COUNTIF(C$1:C1,\々\

559，提取迟到次数最多者姓名:=INDEX(B2:B11,MODE(MATCH(B$2:B$11,B$2:B$11,0))) 560，罗列多次迟到者姓

名:{=IFERROR(INDEX(B$2:B$11,MODE(IF(COUNTIF(D$1:D1,B$2:B$11)=0,MATCH(B$2:B$11,B$2:B$11,0)))),\

561，区分、不区分大小写统计字符个

数:{=COUNT(0/(MATCH(MID(A2,ROW($1:$100),1),MID(A2,ROW($1:$100),1),0)=ROW($1:$100)))-1}

562，按金、银、铜牌排名

次:{=MATCH(B2:B11+C2:C11%+D2:D11%%,LARGE(B2:B11+C2:C11%+D2:D11%%,ROW(2:11)-1),0)

33

}

563，按班级插入分隔

行:{=INDEX(A:B,MOD(SMALL(IF({1,0},ROW(2:11)*1001,IF(ROW(2:11)-1=MATCH(A2:A11,A2:A11,0),((MATCH(A2:A11,A2:A11,)+COUNTIF(A2:A11,A2:A11))*1000+100),1048576)),ROW(1:100)),1000),{1,2})&\

564，统计一、二班举重参赛人员数:{=COUNT(MATCH(B2:B11&C2:C11,{\一班\二班\举重\累计销量并列出排行

榜:{=OFFSET($B$1,MATCH(1,N(MAX(IF(COUNTIF($D$1:D1,B$2:B$12)=0,SUMIF(B$2:B$12,B$2:B$12,C$2:C$12)))=IF(COUNTIF($D$1:D1,B$2:B$12)=0,SUMIF(B$2:B$12,B$2:B$12,C$2:C$12))),),)&\}

565，利用公式对入库表进行数据分

析:{=INDEX(B:B,SMALL(IF(MATCH(B$2:B$200,B$2:B$200,0)=ROW($2:$200)-1,ROW($2:$200),65536),ROW(A1)))&\

566，罗列每个地区的获奖人员姓

名:{=IFERROR(INDEX($A:$A,MATCH(1,(COUNTIF(E$1:E1,$A$2:$A$10)=0)*($B$2:$B$10=E$1),)+1),\

567，对合并区域进行数据查询:=OFFSET(B1,MATCH(G2,A2:A13,0)-1+MATCH(H2,{\冰箱\空调\洗衣机\

568，将一维人事资料表转二维:{=REPLACE(IFERROR(OFFSET($A$1,MATCH(C$1:F$1&\：*\)}

569，区分大小写查找单价:{=INDEX(B:B,MATCH(0,0/EXACT(E1,A1:A8),0))} 570，根据姓名查找左边的身份证号:=LOOKUP(E2,B2:B9,A2:A9) 571，将中文大写编号转换成阿位伯数字小

写:=TEXT(LOOKUP(1,0/(B2=TEXT(ROW($1:$1000),\572，将姓名按拼音升序排

列:{=LOOKUP(0,0/(ROW(A1)=MMULT(N($A$2:$A$11>=TRANSPOSE($A$2:$A$11)),ROW($2:$11)^0)),A$2:A$11)}

573，将酒店按星级降序排

列:{=LOOKUP(ROUND(1/MOD(LARGE(LEN(B$2:B$10)+1/ROW($2:$10),ROW(A1)),1),0),ROW($2:$

34

10),A$2:A$10)}

574，计算某班六年中谁获第一名次数最多:{=MAX(COUNTIF(B2:B7,B2:B7))} 575，罗列每个名次的所有姓

名:{=IFERROR(INDEX($A:$A,(SMALL(IF($B$2:$B$11=LARGE(IF(FREQUENCY($B$2:$B$11,$B$2:$B$11),$B$2:$B$11),ROW(A1)),ROW($2:$11)),COLUMN(A2)))),\

576，提取新书的印刷批次:=LOOKUP(9E+307,--RIGHT(LEFT(A2,FIND(\577，罗列2008年每月第一个及最后一个星期

日:{=MIN(IF(WEEKDAY(DATE(2008,ROW(A1),ROW(INDIRECT(\1,0))))),2)=7,DATE(2008,ROW(A1),ROW(INDIRECT(\填补空白区:=LOOKUP(1,0/($A$2:A2<>\

578，将字母转换成评分:{=AVERAGE(LOOKUP(B2:I2,{\579，将字母转换成评分并对选手排

名:{=LOOKUP(MOD(LARGE(MMULT(LOOKUP($B$2:$I$7,{\W($1:$8)^0)*10000+ROW($2:$7),ROW(A1)),10),ROW($2:$7),A$2:A$7)} 580，标识各选手应得的奖

牌:{=LOOKUP(SUM(N(IF(FREQUENCY(B$2:B$11,B$2:B$11),B$2:B$11,0)>B2))+1,ROW($1:$4),{\冠军\亚军\季军\581，计算各厂商参赛人

数:{=IFERROR(LOOKUP(SMALL(IF(A$2:A$21<>\21)&\\582，从品名信息中分别提取多段数

值:=IFERROR(-LOOKUP(0,-MID($A2,FIND(B$1,$A2)+LEN(B$1),ROW($1:$100))),\583，反向查找数据:=LEN(A2)-LOOKUP(100,SEARCH(B2,A2,ROW($1:$99)))-LEN(B2)+2 一级、二级分组编号:=TEXT(COUNTIF(B$1:B2,\第

*\第\584，计算购货金

额:{=LOOKUP(9E+307,--MID($A2,MATCH(0,0*MID($A2,ROW($1:$1000),1),0),ROW($1:$15)))*(LOOKUP(9E+307,--LEFT(REPLACE(A2,1,FIND(\585，谁是百米冠军:=LOOKUP(0,0/(B2:B11=MIN(B2:B11)),A2:A11)

586，从销售记录中提取销量与单价并计算金额:{=LOOKUP(10^16,--RIGHT(REPLACE(A2,FIND(\公

35

斤\元\

587，根据比赛结果降序排列选手且标识名

次:{=LOOKUP(SUM(N(COUNTIF(B$2:B$21,E2)<--IF(FREQUENCY(COUNTIF($B$2:$B$21,B$2:B$21),COUNTIF($B$2:$B$21,B$2:B$21)),COUNTIF($B$2:$B$21,B$2:B$21))))+1,ROW($1:$4),{\冠军\亚军\季军\588，计算每个职工的得

分:=LOOKUP(,-FIND(B2,{\589，查询业务员的负责地

区:{=T(INDEX(B:B,SMALL(IF(LOOKUP(ROW(A$2:A$11),IF(A$2:A$11<>\A$11)=$D$2,ROW(A$2:A$11),1048576),ROW(1:1))))}

590，根据产量计算员工产量得分:{=LOOKUP(B2,{3,0.5}*(ROW($1:$11)-1))}

591，根据员工得分转换为相应的等级:=LOOKUP(B2,--REPLACE(等级与分值!B$2:B$6,FIND(\等级与分值!B$2:B$6),10,\等级与分值!A$2:A$6)

592，提取产量冠军的组别:=IF(COUNTA(B2:E2),LOOKUP(1,0/ISTEXT(B2:E2),B$1:E$1),\593，区分工种和达标率计算奖金:=LOOKUP(C2*100,1*LEFT(达标与奖金标准!B$1:K$1,FIND(\达标与奖金标准!B$1:K$1)-1),OFFSET(达标与奖金标准!B$1,MATCH(B2,达标与奖金标准!A$2:A$4,0),,,10))

594，使用通配符查找所有符合条件的数据:{=IFERROR(LOOKUP(1,0/SEARCH(\医院*\595，分别提取身份证号码中的年月

日:=TEXT(TEXT(MID($A2,7,8),\年\月\日\年\月\日\

596，根据不良率判断送货品处理办法:=CHOOSE((SUM(N(C2/B2>={0,0.005,0.01}))),\合格\允收\退货\

597，让VLOOKUP函数在多区域查找:=VLOOKUP(A11,CHOOSE(MATCH(B11,{\一年级\二年级\三年级\

598，将区域互换位置:=VLOOKUP(E2&\

599，跨表统计最大值:{=CHOOSE(MOD(MAX(SUBTOTAL(9,INDIRECT({\组\组\组\组\组\组\

36

600，罗列所有参加田径的人

员:{=IFERROR(VLOOKUP(1,CHOOSE({1,2},--(COUNTIF(OFFSET(C$2,,,ROW($2:$11)-1),\田径\

601，计算今天是本月的上旬、中旬还是下旬:=CHOOSE(MIN(CEILING(DAY(TODAY())/10,1),3), \上旬\中旬\下旬\

602，建立文件目录:=HYPERLINK(\产量表\\\月产量表.xlsx]sheet1!A1\月产量表\

603，链接“总表”中B列最大值单元格:{=HYPERLINK(\总表!B\总表!B:B)=总表!B:B)*ROW(B:B)),\至总表B列最大值\

604，链接至B列最末的非空单元格:{=HYPERLINK(\列最后非空值\605，选择冠军姓

名:{=HYPERLINK(\T(\:B13))))-1)*2)),REPT(\得票冠军\606，选择二年级旷课人员名单:{=HYPERLINK(\二年级\二年级\二年级名单\607，选择产量最高工作

表:{=HYPERLINK(\组!B2:B11\组!A1\跳至最大产量组\

608，选择打印区域:=HYPERLINK(\未设置打印区\跳至打印区域\

609，计算期末平均成绩:{=AVERAGE(IF(ISEVEN(COLUMN(B:I)-1),B3:I3))} 610，提取期末成绩明细:{=INDEX(成绩

表!1:1,SMALL(IF(ISEVEN(COLUMN($B:$I)-(ROW()<>1)),COLUMN($B:$I)),COLUMN(A1)))} 611，提取每日累计出库数和每日库存数:日期=INDEX(A:A,ROW(A1)*2);累计出库数

{=SUM(ISODD(ROW(INDIRECT(\每日库存数{=SUM(SUMIF(OFFSET(B$1,1,,ROW(A1)*2),{\进库\出库\612，根据身份证号码汇总男、女职工总数:男{=SUM(--ISODD(MID(B2:B10,15,3)))};女{=SUM(--ISEVEN(MID(B2:B10,15,3)))}

37

613，提取当前表打印区域地址:=IF(ISNA(VLOOKUP(\苹果

\苹果\614，计算生产部人数和非生产部人数:生产部人数{=SUM((NOT(ISERR(FIND(\车间\非生产部人数{=SUM((ISERR(FIND(\车间\615，提取A、B列相同项与不同

项:{=T(INDEX(A:A,SMALL(IF(NOT(ISERROR(MATCH(A$2:A$11,B$2:B$11,0))),ROW($2:$11),1048576),ROW(A1))))} 616，计算产品体

积:=IF(ISERROR(FIND(\\

617，引用单价并去除干扰符:=IF(ISNA(MATCH(B2,单价表!B$1:E$1,0)),\请更新单价\单价表!B$1:E$2,2,0),ROW($1:$100)))) 618，查询书籍在七年中的最高单价:{=IF(ISNA(MATCH(A10,A2:A8,0)),\书名错误\

619，根据计价单位查询单价:=IF(ISNA(MATCH(B2,F$1:H$1,0)),\未设定汇率\620，数字、字母与汉字个数计算:数字个数

{=SUM(--(ERROR.TYPE(INDIRECT(\判断错误类型:=LOOKUP(ERROR.TYPE(A2),ROW(1:7),{\空值错误\被零除错误\值错误\无效的单元格引用\无效的名称\数字错误\值不可用\

621，罗列某运动员九次参赛成绩:{=INDEX($1:$1,MAX(ISTEXT(B2:E2)*COLUMN(B:E)))} 提取每年级第一名名

单:=LOOKUP(1,0/ISTEXT(B2:E2),B$1:E$1)&\622，将按日期排列的销售表转换成按品名排

列:{=IFERROR(VLOOKUP($A2,IF(MATCH(ROW($1:$15),IF(ISTEXT(日期!$A$1:$A$15),MATCH(日期!$A$1:$A$15,日期!$A$1:$A$15,0)))=MATCH(B$1,日期!$A$1:$A$15,0),日期!$B$1:$C$15),2,0),\623，按月份统计每个产品的机器返修数

量:=SUMPRODUCT(ISNUMBER(FIND(F$2,$A$2:$A$11))*(TEXT($B$2:$B$11,\M\

624，按文字描述求和:{=SUM(ISNUMBER(FIND(A$2:A$8,D2))*B$2:B$8)}

38

625，按编码计算库存总数:{=SUM(ISNUMBER(FIND(\626，从产品规格中提取直径、长、宽:长(直

径)=LOOKUP(9.9E+307,--RIGHT(IF(ISNUMBER(FIND(\\\A2),ROW($1:$100)));宽=IF(ISNUMBER(FIND(\\\累计每日得分:=(N(C1)=0)*5+N(C1)+IF(B2>0,-B2,0.1) 627，统计各班所有科目成绩大于60分者人

数:{=MMULT(N(TRANSPOSE(A2:A21)=H3:H6),N(COUNTIF(OFFSET(C2:F2,ROW(2:21)-2,),\=4))}

628，区分大小写统计不重复值个

数:{=SUM(N(MMULT(N(EXACT(A2:A11,TRANSPOSE(A2:A11))),ROW(2:11)^0)=TRANSPOSE(ROW(2:11)-1))/TRANSPOSE(ROW(2:11)-1))} 629，累计每日库存

数:=N(G1)+SUM(OFFSET(C$1,ROW(A1)*2-1,,2))-SUM(OFFSET(D$1,ROW(A1)*2-1,,2)) 630，提取当前工作表名、工作簿名及存放目录:工作表

=REPLACE(CELL(\工作簿

=SUBSTITUTE(REPLACE(CELL(\name\存放目录

=REPLACE(CELL(\631，提取第一次参赛取得最佳成绩者姓名与成绩:参赛者

{=INDEX(A:A,MOD(MAX((IF(NOT(ISBLANK(C2:C11)),MATCH(A2:A11,A:A,0))=ROW(2:11))*C2:C11*100+ROW(2:11)),100))};成绩

{=MAX((IF(NOT(ISBLANK(C2:C11)),MATCH(A2:A11,A:A,0))=ROW(2:11))*C2:C11)} 632，计算哪一个项目得票最

多:{=INDEX({\11),\

633，根据利率、存款与时间计算存款加利息数:=FV(B2,D2,-C2,0)

634，计算七个投资项目相同收益条件下谁投资更少:{=MAX(PV(B2:B8,C2:C8,0,100000))} 635，根据利息和存款数计算存款达到1万元需要几个月:=NPER(A2,0,-B2,C2)*12 636，根据投资金额、时间和目标收益计算增长率:=RATE(B2,0,-A2,C2)

637，根据贷款、利率和时间计算某段时间的利息:=CUMIPMT(B2/12,C2*12,A2,1,24,0)

39

638，根据贷款、利率和时间计算需偿还的本金:=CUMPRINC(B2/12,C2*12,A2,1,24,0) 639，以固定余额递减法计算资产折旧值:=DB(A$2,B$2,C$2,ROW(A1),12) 640，以双倍余额递减法计算资产折旧值:=DDB(A$2,B$2,C$2,1,2) 641，以年限总和折旧法计算折旧值:=SYD(A$2,B$2,C$2,ROW(A1))

642，使用双倍余额递减法计算任何期间的资产折旧值:=VDB(A$2,B$2,C$2*12,7,12,2) 643，获取当前工作簿中工作表数量:=COLUMNS(sheets)&T(NOW()) 644，建立工作表目录与超级链

接:=IFERROR(HYPERLINK(INDEX(sheets,ROW(A1))&\NOW()),1,FIND(\645，选择最后工作表的最后非空单元

格:=HYPERLINK(INDEX(sheets,COLUMNS(sheets))&\COLUMNS(sheets))&\

646，引用单元格数据同时引用格式:=IF(TODAY()>A2,\格式)) 647，分别汇总当前表以外的所有工作表数

据:AcSht=GET.CELL(62);sheets=GET.WORKBOOK(1);WorkBook=GET.CELL(66);{=IFERROR(REPLACE(INDEX(sheets,SMALL(IF(TRANSPOSE(sheets)<>AcSht,ROW(INDIRECT(\ts)))),ROW(A2))),1,LEN(WorkBook)+2,\

648，提取单元格的公式:名称=GET.CELL(6,Sheet1!$B1)&T(NOW())

649，罗列工作簿中所有名称:{=IFERROR(INDEX(名称,SMALL(IF(名称<>\名称\名称))))),ROW(A1))),\

650，在任意单元格显示当前页数及总页数:无拘无束的页眉=\第\横向当前页=1,纵向当前页,横向当前页+纵向当前页)&\页/共\总页&\页\纵向当前页=IFERROR(MATCH(ROW(),GET.DOCUMENT(64))+1,1) 651，提取单元格中的批注:批注=GET.OBJECT(12, \备注 1\

652，利用列表框筛选数据:筛选=IF(GET.OBJECT(78,\列表框 1\列表框 1\

653，判断单元格是否被图形对象覆盖:=ADDRESS(ROW(INDIRECT(左上,0)),COLUMN(INDIRECT(左上,0)))&\右下,0)),COLUMN(INDIRECT(右下,0)))

654，将单元格的公式转换成数值:计算=EVALUATE(Sheet1!A3)

40

655，将IP地址补足三位:IP地址

=TEXT(EVALUATE(\ALUATE(\UBSTITUTE(Sheet1!A4,\656，按分隔符取数并求平均:成绩

=EVALUATE(SUBSTITUTE(\657，根据产品规格计算体积:体积

=EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!XFD4,\（L）\（W）\（H）\

658，计算减肥前后的三国差异:后=EVALUATE(\前=EVALUATE(\659，计算各楼层空佘面积:面积

=EVALUATE(SUBSTITUTE(SUBSTITUTE(Sheet1!XFD1,\

660，将数据分列，提取省市县:分列=EVALUATE(\省\省\市\市\

661，按图书编号汇总价格:图书=EVALUATE(\662，标识B列中的重复值:条件格式:=COUNTIF($B:$B,B1)>1

663，将数据间隔着色:条件格式:=MOD(SUM(N($B$2:$B2<>$B$1:$B1)),2)=0

664，隐藏错误值:单元格的公式:=VLOOKUP(A2,单价表!$A$2:$B$10,2,0)，条件格式:=ISERROR(B1) 665，突显前三个最大值:条件格式:=B2>LARGE($B$2:$F$10,4) 666，将成绩高于平均值的姓名标示“优等”:条件格式:=(B2>AVERAGE($B$2:$F$10))*MOD(COLUMN(),2) 667，突显奇数行:条件格式:=ISODD(ROW())

668，突显非数值:条件格式:=NOT(ISNUMBER(A2))*ISEVEN(COLUMN()) 669，B列中禁止输入重复数据:数据有效性设置-自定义:=COUNTIF(B:B,B8)=1 670，仅允许录入英文姓名:数据有效性设置-自定

义:=SUM(--(ERROR.TYPE(INDIRECT(MID(SUBSTITUTE(A2,\

\\

671，强制录入规范化的日期:数据有效性设置-自定义:=(LEN(A2)=8)*TEXT(A2,\672，建立动态下拉选单:定义名称:水果=OFFSET(单价表!$A$1,,,COUNTA(单价表!$A:$A))

41

673，建立二级下拉选单:定义名称:省=OFFSET(参考区!$A$1,,,,COUNTA(参考区!$1:$1));市=OFFSET(参考区!$A$1,1,MATCH(Sheet1!$A$2,参考区!$1:$1,0)-1,COUNTA(OFFSET(参考区!$A$1,1,MATCH(Sheet1!$A$2,参考区!$1:$1,0)-1,1048575))) 674，建立不重复的下拉选

单:{=INDEX(A:A,SMALL(IF(COUNTIF(Sheet1!A$1:A$8,A$1:A$8)=0,ROW($1:$8),1048576),ROW(A2)))&\生成不重复单位);定义名称:=OFFSET(名单!$B$1,,,8-COUNTBLANK(名单!$B$1:$B$8)) 675，让A列只能输入质数:数据有效性设置-自定

义:=OR(A2=2,A2=3,PRODUCT(MOD(A2,ROW(INDIRECT(\

676，设置D列只能录入男职工的姓名:数据有效性设置-自定义:=VLOOKUP(D2,A:B,2,0)=\男\677，禁止录入不完整的产品规格:数据有效性设置-自定义:=ISNUMBER(SEARCH(\长?*宽?*高?*\

678，自动记录进库时间:=IF(ISBLANK(B2),\679，记录历史最高值:=MAX(B:B,D2) 680，解一元二次方程:X+100=X^2+10

681，解二元一次方程:X=10X=(100-5Y)/25,Y=Y/5=(200 +4X)/4

IF(ROUND(M37,2)<0,\无效数值\零

\(ROUND(M37,2)<1,\元

\)&IF(INT(ROUND(M37,2)*10)-INT(ROUND(M37,2))*10=0,IF(INT(ROUND(M37,2))*(INT(ROUND(M37,2)*100)-INT(ROUND(M37,2)*10)*10)=0,\零

\),TEXT(INT(ROUND(M37,2)*10)-INT(ROUND(M37,2))*10,\角\)&IF((INT(ROUND(M37,2)*100)-INT(ROUND(M37,2)*10)*10)=0,\整

\分\1， dbnum2，dbnum1，dbnum3 都是格式语言，各种格式而已。

2， IF(INT(ROUND(M37,2))*(INT(ROUND(M37,2)*100)-INT(ROUND(M37,2)*10)*10)=0,\零\) 实现了为上一条件是否显示为空白或者零。即如果上一步为0，下一步还是可能两种选择。 3，邮件合并过程中如果出现非准格式，可以采用右击这个链接，然后编辑域，然后选择最下面那个，后加\\#,##0.00即可。

本文来源：https://www.bwwdw.com/article/4nv5.html