Excel2007函数公式实例汇总(2)

将 三个学校植树人员随机分组:=OFFSET(A$1,RANK(G2,G$2:G$11),)&\OFFSET(B$1,RANK(G2,G$2:G$11),)&\产生-50到100之间的随机整数:=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区域开三次方之平均值:{=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<>\

计算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.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)) 根据重量单价计算金额，结果以万为单

位:=TRUNC(SUMPRODUCT(B2:B10,C2:C10),-4)/10000

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

根据上机时间计算上网费

用:=(TRUNC(B2)+(B2-TRUNC(B2)>=0.5))*1.5+(MOD(B2,1)<0.5)

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

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

成 绩表的格式转换:姓名=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) 隔两行进行编号:=IF(MOD(ROW(),3)=1,INT(ROW(A3)/3),\

INT函数在序列中的复杂运

用:=INT(SQRT(2*ROW(A1))+0.5);=10^INT((ROW()-1)/2);=INT(10^(ROW())/9);=INT((ROW(A2))*2/3)

统计交易损失金额:=SUMPRODUCT(B2:B11-CEILING(B2:B11,0.1)) 根据员工工龄计算年资:=C2+CEILING(B2*30,30)*(INT(B2)>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*500 FLOOR函数处理正负数混合区域:=FLOOR(A1*100,10*(IF(A1>0,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=\

将金额保留“角”位，忽略“分”位:{=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,{\平均成绩\科目数量\最高成绩\最低成绩\成绩合计\不间断的序号:=SUBTOTAL(103,$B$2:B2)

仅对筛选出的人员排名次:{=CONCATENATE(\第\优等

生!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=\判断学生是否符合奖学金发放条件:=AND(B2>90,C2<>\汉族\所有裁判都给“通过”就进入决赛:{=AND(B2:E2=\通过\判断身份证长度是否正确:=OR(LEN(B2)={15,18}) 判断歌手是否被淘汰:{=OR(B2:E2=\不通过\

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

没有任何裁判给“不通过”就进行决赛:{=NOT(OR(B2:E2=\不通过\计彝成绩区域数字个数:{=SUM(NOT(ISERROR(NOT(B2:B11)))*1)} 评定学生成绩是否及格:=IF(AVERAGE(B2:D2)>=60,\及格\不及格\

根据学生成绩自动产生评语:=IF(AVERAGE(B2:D2)<60,\不及格

\良好\优秀\满分\根据业绩计算需要发放多少奖金:{=SUM(IF(B2:B11>80000,1000,500))} 根据工作时间计算12月工

资:=C2+SUM(IF(B2>{0,1,3,5,10},{300,500,500,500,500})) 合计区域的值并忽略错误值:{=SUM(IF(ISERROR(A1:C10),0,A1:C10))} 既求积也求和:=IF(D2<>\分 别统计收入和支出:收入{=SUM(IF(B2:B13>0,B2:B13))};支出 {=SUM(IF(SUBSTITUTE(IF(B2:B13<>\负\0,SUBSTITUTE(B2:B13,\负\

将成绩从大到小排

列:{=IF(ROW(A1)>COUNT(B$2:B$11),\排除空

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

有选择地汇总数据:{=SUM(IF(A2:A11={\组\组\混合单价求金额合

计:{=SUM(ROUND(B2:B10*C2:C10*IF(D2:D10=\

计算异常停机时

间:{=SUM(SUBSTITUTE(SUBSTITUTE(IF(C2:C11<>\修机\换原料\

计算最大数字行与文本行:{=MAX(IF(B:B<>\

找出谁夺冠次数最

多:{=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)<91),AND(CODE(A2)>96,CODE(A2)<123)) 计 算单元格中数字个

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

计算单元格中大写 加小写字母个

数:{=SUM((CODE(UPPER(MID(A2,ROW(INDIRECT(\gt;64)*(CODE(UPPER(MID(A2,ROW(INDIRECT(\产生大、小写字母A到Z的序列:大写字母=CHAR(ROW(A65))，小写字母=CHAR(ROW(A65)+32)

产 生大写字母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),\

产生三个字母组成的随机字符

串:=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))

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

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

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

返回自动换行单元格的第二行数据:=RIGHT(A2,LEN(A2)-FIND(CHAR(10),A2)) 根据身份证号码提取出生年月

日:=CONCATENATE(MID(B2,7,4-2*(LEN(B2)=15)),\年

\月\日 \计算平均成绩及评判是否及格:=CONCATENATE(INT(AVERAGE(B2:D2)),\\不\及格\

提 取前三名人员姓

名:=CONCATENATE(LOOKUP(0,0/(B2:B11=LARGE(B2:B11,1)),A2:A11),\P(0,0/(B2:B11=LARGE(B2:B11,2)),A2:A11),\B2:B11,3)),A2:A11)))

将单词转换成首字母大写:=PROPER(A2) 将所有单词转换成小写形式:=LOWER(A2)

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库Excel2007函数公式实例汇总(2)在线全文阅读。