Excel2007函数公式实例大全(8)_知行网
Excel2007函数公式实例大全(8)
计算10个月中的销售利润并排名:{=OFFSET(A1,MOD(LARGE(INT(MMULT(SUBTOTAL(6,OFFSET(INDIRECT({&华东区&,&华南区&,&华北区&,&华中区&,&西南区&}&&!A1&),ROW(2:11)-1,1,1,3)),{1;1;1;1;1}))*1000+ROW(2:11),ROW(1:10)),1000)-1,)}
将表格转置方向:{=TRANSPOSE(A1:E5)}
对组数进行排名:{=MMULT(N(B2:B11*(IF(LEFT(C2:C11)=&万&,10000,1))&TRANSPOSE(B2:B11*(IF(LEFT(C2:C11)=&万&,10000,1)))),ROW(2:11)^0)+1}
区分大小写提取产品单价:{=MMULT((EXACT(B2:B11,TRANSPOSE(单价表!A2:A5)))*TRANSPOSE(单价表!B2:B5),{1;1;1;1})}
区分大小写查单价且统计三组总金额:{=MMULT(TRANSPOSE(SUBTOTAL(9,OFFSET(B1,ROW(2:11)-1,1,,5))*MMULT((EXACT(B2:B11,TRANSPOSE(单价表!A2:A5)))*TRANSPOSE(单价表!B2:B5),{1;1;1;1})),1*(A2:A11={&A组&,&B组&,&C组&}))}
引用销售金额高于200次数最多者:{=INDEX(A:A,RIGHT(MAX(MMULT((B2:H9&200)*1,TRANSPOSE(COLUMN(B:H)^0))*10+ROW(2:9))))}
根据评委评分和权重分配统计最后得分:{=SUM(B2:F8*(A2:A8=B10)*TRANSPOSE(I2:I6))}
罗列选手得分前三名的姓名:{=OFFSET($A1,RIGHT(LARGE(MMULT($B2:$F8*TRANSPOSE($I2:$I6),TRANSPOSE(COLUMN($B:$F)^0))*10^6+ROW(2:8),COLUMN(A1)),2)-1,,)}
根据字母评语转换得分:{=MMULT(TRANSPOSE(评语换算得分!A$2:A$11=TRANSPOSE(E2:E11))*1,评语换算得分!B$2:B$11)+SUBTOTAL(9,OFFSET(B2,ROW(2:11)-2,,,COLUMNS(B:D)))}
多列、隔行数据汇总:{=SUM(MMULT(D2:G11,TRANSPOSE(COLUMN(D:G)^0))*(A2:A11=&赵还珠&))}
计算犯规低于3次的人数:{=SUM(N(MMULT(--(B2:B21=TRANSPOSE(B2:B21)),ROW(2:21)^0)={1,2})/{1,2})}
提取姓名:=INDEX(B:B,ROW()*2)&&&
从电话簿中选择性引用数据:=INDEX($A:$B,ROW(A1)*3-2,COLUMN(A:A))
消除厂牌打印资料照片行:{=INDEX(A:A,SMALL(IF(MOD(ROW($1:$12),3)&0,ROW($1:$12),1048576),ROW(A1)))&&&}
罗列优秀员工:{=INDEX(A:A,MOD(SMALL(B$2:B$11*100+ROW($2:$11),ROW(8:8)),100))}
插入空行分割数据:=IF(MOD(ROW(),3)&0,INDEX(A:A,ROW(A2)*2/3),&&)
仅仅提取通讯录中四分之三信息:=INDEX(A:B,ROW(A2)*2/3,(MOD(ROW(A3),3)+1)/3+1)
罗列12月中产量倒数第一名次数最多者名单:{=INDEX(B:B,SMALL(IF((COUNTIF(B$2:B$13,B$2:B$13)=MAX(COUNTIF($B$2:$B$13,$B$2:$B$13)))*(MATCH($B$2:$B$13,$B$2:$B$13,0)=ROW($2:$13)-1),ROW($2:$13),1048576),ROW(A1)))&&&}
按投诉次数升序排列客服姓名:{=INDEX(B:B,MOD(SMALL(IF(MATCH(B$2:B$12,B$2:B$12,)=ROW($2:$12)-1,COUNTIF(B$2:B$12,B$2:B$12)*10^5+IF(MATCH(B$2:B$12,B$2:B$12,)=ROW($2:$12)-1,ROW($2:$12),99999),ROW(A1)),10^5))&&&}
计算60分到95分之间的人员个数:=INDEX(FREQUENCY(B2:B11,{60,95}),2)
罗列导致产品不良的主因:{=IFERROR(T(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(A1)))),&&)}
按身高对学生排列座次表:{=INDEX($A:$A,MOD(SMALL($C$2:$C$49*1000+ROW($2:$49),(ROW(A1)-1)*6+MOD(COLUMN(A1)-1,6)+1),1000))}
重组教师授课表:{=INDEX(班级!$A:$A,SMALL(IF(班级!$B$2:$D$11=$A3,ROW($2:$11),1048576),COLUMN(C$1)))&&&}
提取三个不规则区域的交集:{=INDEX($B:$B,SMALL(IF(COUNTIF(C组!$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)))&&&}
不区分大小写查找单价:=VLOOKUP(B2,单价表!A$2:C$11,3,0)*C2
乱序资料表中查找多个项目:=VLOOKUP($B2,单价表!$A$2:$E$11,MATCH(C$1,单价表!$A$1:$E$1,0),0)
将得分转换成等级:=VLOOKUP(B2,{0,&D&;60,&C&;80,&B&;90,&A&},2)
查找美元与人民币报价:=VLOOKUP(B2,INDIRECT(E2&&报价!A2:B9&),2,0)
多条件查找:{=VLOOKUP(A2&B2&C2,IF({1,0},资料表!A2:A11&资料表!B2:B11&资料表!D2:D11,资料表!C2:C11),2,0)}
查找最后更新单价:{=VLOOKUP(10^16,--LEFT(VLOOKUP(B2,单价表!A:Z,COUNTA(INDIRECT(&单价表!A&&MATCH(B2,单价表!A:A,0)&&:Z&&MATCH(B2,单价表!A:A,0))),0),ROW($1:$16)),1)}
查找双列信息:{=VLOOKUP(A9,CHOOSE({3,2,1},A1:A6&B1:B6,C1:C6&D1:D6,E1:E6&F1:F6),{2,3},)}
提取姓名拼音的首字母:=VLOOKUP(LEFT(A2),拼音,2)&VLOOKUP(MID(A2,2,1),拼音,2)&VLOOKUP(MID(A2,3,1),拼音,2)
用不确定条件查找:{=VLOOKUP(A2&&&,IF({1,0},IF(COUNTIF(资料表!A2:A10,A2)=0,资料表!B2:B10,资料表!A2:A10),资料表!E2:E10),2,0)}
按学历对姓名排序:{=VLOOKUP(MOD(SMALL(MATCH(B$2:B$10,{&大学&;&高中&;&初中&;&小学&},0)*1000+ROW($2:$10),ROW(A1)),1000),IF({1,0},ROW($2:$10),A$2:A$10),2,0)}
使用通配符进行查找:{=VLOOKUP(&*&&A2&&*&,IF({1,0},资料表!B$2:B$9,资料表!A$2:A$9),2,0)}
多工作表查找最大值:{=TEXT(VLOOKUP(MAX(SUBTOTAL(9,INDIRECT(TEXT(ROW(1:6),&[DBNum1]&)&&年级!B&&MATCH(D2,A:A,0)))),IF({1,0},SUBTOTAL(9,INDIRECT(TEXT(ROW(1:6),&[DBNum1]&)&&年级!B&&MATCH(D2,A:A,0))),ROW(1:6)),2,0),&[DBNum1]&)}
对带有合并单元格的区域查找年假天数:=VLOOKUP(F2,OFFSET(B2,MATCH(E2,A2:A13,0)-1,,4,2),2)
查找某业务员在某季度的销量:=HLOOKUP(G2,A1:E9,MATCH(H2,A:A,0),0)
在同一行查找数据:{=HLOOKUP(MAX(A2:H2),IF({1;0},B2:H2,A2:G2),2,FALSE)}
计算两个产品不同时期的单价:=HLOOKUP(MONTH(A2),IF(B2=&塑胶机&,{0,3,8;25,19,18},{0,5,10;12.5,10,11}),2)
多条件计算加班费:=TEXT(HOUR(B2)+HLOOKUP(MINUTE(B2),{0,20.1;0,0.5,1},2),&[&2]6;5&)*HOUR(B2)+HLOOKUP(MINUTE(B2),{0,20.1;0,0.5,1},2)
根据进厂日期计算有薪假天数:=HLOOKUP(DATEDIF(B2,TODAY(),&y&),{0,1,3,5,7,10;0,2,3,5,7,10},2)
制作准考证:=HLOOKUP(B2,学生档案库!$1:$11,ROUNDUP(COLUMN()/5,0)+1+INT(ROW()/7)*2,FALSE)
不区分大小写判断两列相同数据个数:{=COUNT(MATCH(A2:A11,B2:B11,0))}
按汉字评语进行排序:{=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})}
提取A列最后一个数据:{=INDIRECT(&A&&(MATCH(1,0/(A:A&&&&))))}
提取字符串中的汉字:{=MID(A2,MATCH(1,1/(MID(A2,ROW($1:$99),1)&=&啊&),),SUM(MATCH({1,2},1/(MID(A2,ROW($1:$99),1)&=&啊&),{0,1})*{-1,1})+1)}
将文件号中的中文大写转小写:{=&第&&TEXT(SUM((MATCH(MID(A2,{2,3,4},1),TEXT(ROW($1:$10)-1,&[DBNum2]&),0)-1)*{100,10,1}),&000&)&&号文件&}
计算补课科目总数:{=COUNT(0/(MATCH(B2:B8,B2:B8,0)=ROW(2:8)-1))}
产生混合编号:=TEXT(COUNTIF(C$1:C1,&*&),&[DBNum2]&)&TEXT(ROW()-MATCH(&々&,C$1:C1),&(000);;&)
提取迟到次数最多者姓名:=INDEX(B2:B11,MODE(MATCH(B$2:B$11,B$2:B$11,0)))
罗列多次迟到者姓名:{=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)))),&&)}
区分、不区分大小写统计字符个数:{=COUNT(0/(MATCH(MID(A2,ROW($1:$100),1),MID(A2,ROW($1:$100),1),0)=ROW($1:$100)))-1}
按金、银、铜牌排名次:{=MATCH(B2:B11+C2:C11%+D2:D11%%,LARGE(B2:B11+C2:C11%+D2:D11%%,ROW(2:11)-1),0)}
按班级插入分隔行:{=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))*48576)),ROW(1:100)),1000),{1,2})&&&}
统计一、二班举重参赛人员数:{=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))),),)&&&}
利用公式对入库表进行数据分析:{=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)))&&&}
罗列每个地区的获奖人员姓名:{=IFERROR(INDEX($A:$A,MATCH(1,(COUNTIF(E$1:E1,$A$2:$A$10)=0)*($B$2:$B$10=E$1),)+1),&&)}
对合并区域进行数据查询:=OFFSET(B1,MATCH(G2,A2:A13,0)-1+MATCH(H2,{&冰箱&,&空调&,&洗衣机&},0),MATCH(I2,C1:E1,0))
将一维人事资料表转二维:{=REPLACE(IFERROR(OFFSET($A$1,MATCH(C$1:F$1&&：*&,IF(COUNTIF(OFFSET(A$1,,,ROW($1:$42)),&&)=ROW()-2,A$1:A$42),0)-1,),&&),1,LEN(C$1:F$1)+1,&&)}
区分大小写查找单价:{=INDEX(B:B,MATCH(0,0/EXACT(E1,A1:A8),0))}
标签(Tag):
------分隔线----------------------------
------分隔线----------------------------excel中，已知A2=SUMPRODUCT(B1:G1,B2:G2)，A3=SUMPRODUCT(B1:G1,B3:G3)，用简便方法得出A4、A5.....A10_百度知道
excel中，已知A2=SUMPRODUCT(B1:G1,B2:G2)，A3=SUMPRODUCT(B1:G1,B3:G3)，用简便方法得出A4、A5.....A10
:G4)这种方法:G1..com/zhidao/wh%3D450%2C600/sign=1fb80e0c18/54fbb2fbeaf://b:G1，是更为简便的<img class="ikqb_img" src="/zhidao/pic/item/54fbb2fbeaf.:G2),B3，A3=SUMPRODUCT(B1已知A2=SUMPRODUCT(B1
提问者采纳
=SUMPRODUCT(B$1在A2输入:G$1:G2)公式输入确定后再选中A2,B2，光标变为十字形时双击即可，将光标移到A2单元格右下角小黑点处（填充柄）
提问者评价
按照你说的，真的成功了，好开心，谢谢你！
其他类似问题
为您推荐：
其他2条回答
回车 公式编辑栏输入要复制的公式后. 或,名称框输入需要填充的范围 (比如 A1,B2,光标成黑十字时拖住下拉:G2)下拉复制公式 即点住已输入公式的A2单元格,点住A2单元格外框的右下角那个小黑点A2单元格输入公式=SUMPRODUCT(B$1:G$1
A2单元格输入公式=sumroduct(b$1:g$1*b2:g2)然后一下拉填充
来自：求助得到的回答
等待您来回答
下载知道APP
随时随地咨询
出门在外也不愁&& 文章内容
2011年第11期技术讲座：SUMPRODUCT函数的应用
[日期：] & 来源：IT部落窝& 作者：IT部落窝 & 阅读：10949次[字体：
内容提要：本文详解介绍sumproduct函数的用法，以及在计数和求和方面的经典套用格式应用。
　　分享题目：SUMPRODUCT函数的应用　　主讲老师：卢子老师
　　第一部分：SUMPRODUCT函数用法介绍
　　SUMPRODUCT是什么?其实结合英语就能很好的理解SUMPRODUCT函数，sum是和，product是积，结合起来就是乘积之和。
　　Excel中SUMPRODUCT函数是一个数组类型的函数。很多时候可以用SUMPRODUCT函数取代SUM函数的数组公式，就不需要按三键结束。
　　SUMPRODUCT函数能够计算多个区域的数值相乘后之和。SUMPRODUCT函数的用法就是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。
　　SUMPRODUCT函数的语法：SUMPRODUCT(array1,array2,array3, ...)
　　其中Array1, array2, array3, ... 为 2 到 30 个数组，其相应元素需要进行相乘并求和。
　　SUMPRODUCT函数使用需要注意三点：　　第一，数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。　　第二，函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。　　第三，如果是一个数组，那么就是对这个数组的求和。
　　我们先通过一个简单的工作表数据来认识SUMPRODUCT函数。
　　第一，输入公式：=SUMPRODUCT(A2:B4*C2:D4)，就是将A2:B4和C2:D4两个区域的所有元素对应相乘，然后把乘积相加，即3*2+4*7+8*6+6*7+1*5+9*3，得到结果为156。
　　第二，输入公式：=SUMPRODUCT(A2:B4)，得到结果为31。根据上面的要点介绍，如果是一个数组，那么就是对这个数组的求和，因此就是对{3,4;8,6;1,9}这个区域求和。
　　提示：两个数组相乘是同一行的对应两个数相乘。数组数据用大括号{}括起来，行数据之间用分号";"分隔，如果是同一行的数据，用逗号","分隔。
　　第二部分：SUMPRODUCT函数应用案例介绍
　　下图所示的是咱们
赞助商链接
赞助商链接Excel2007函数公式实例集大全(2)
统计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,2*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=&G&,1000,1),(D2:D10=&G&)*2))}
将金额保留&角&位，忽略&分&位:{=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,{&平均成绩&,&科目数量&,&最高成绩&,&最低成绩&,&成绩合计&;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:$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(B2&90,C2&&&汉族&)
所有裁判都给&通过&就进入决赛:{=AND(B2:E2=&通过&)}
判断身份证长度是否正确:=OR(LEN(B2)={15,18})
判断歌手是否被淘汰:{=OR(B2:E2=&不通过&)}
根据年龄判断职工是否退休:=OR(AND(B2=&男&,C2&60),AND(B2=&女&,C2&55))
根据年龄与职务判断职工是否退休:=OR(AND(B2=&男&,D2&60+(C2=&干部&)*3),AND(B2=&女&,D2&55+(C2=&干部&)*3))
没有任何裁判给&不通过&就进行决赛:{=NOT(OR(B2:E2=&不通过&))}
计彝成绩区域数字个数:{=SUM(NOT(ISERROR(NOT(B2:B11)))*1)}
评定学生成绩是否及格:=IF(AVERAGE(B2:D2)&=60,&及格&,&不及格&)
根据学生成绩自动产生评语:=IF(AVERAGE(B2:D2)&60,&不及格&,IF(AVERAGE(B2:D2)&90,&良好&,IF(AVERAGE(B2:D2)&100,&优秀&,&满分&)))
根据业绩计算需要发放多少奖金:{=SUM(IF(B2:B11&,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&&&&,PRODUCT(C2:D2),SUM(OFFSET(E2,-3,,3)))
分别统计收入和支出:收入{=SUM(IF(B2:B13&0,B2:B13))};支出{=SUM(IF(SUBSTITUTE(IF(B2:B13&&&&,B2:B13,0),&负&,&-&)*1&0,SUBSTITUTE(B2:B13,&负&,&-&)*1))}
将成绩从大到小排列:{=IF(ROW(A1)&COUNT(B$2:B$11),&&,LARGE(B$2:B$11,ROW(A1)))}
排除空值:{=INDEX($A:$B,SMALL(IF($B$1:$B$11&&&&,ROW($1:$11),ROWS($1:$11)+1),ROW()),COLUMN(B2))&&&}
有选择地汇总数据:{=SUM(IF(A2:A11={&A组&,&C组&},C2:C11))}
混合单价求金额合计:{=SUM(ROUND(B2:B10*C2:C10*IF(D2:D10=&K&,))}
计算异常停机时间:{=SUM(SUBSTITUTE(SUBSTITUTE(IF(C2:C11&&&&,C2:C11,0),&修机&,&&),&换原料&,&&)*1)}
计算最大数字行与文本行:{=MAX(IF(B:B&&&&,ROW(A:A)))}
找出谁夺冠次数最多:{=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(&1:&&LEN(A2))),1))&47)*(CODE(MID(A2,ROW(INDIRECT(&1:&&LEN(A2))),1))&58))}
计算单元格中大写加小写字母个数:{=SUM((CODE(UPPER(MID(A2,ROW(INDIRECT(&1:&&LEN(A2))),1)))&64)*(CODE(UPPER(MID(A2,ROW(INDIRECT(&1:&&LEN(A2))),1)))&91))}
产生大、小写字母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)),&年&,MID(B2,11-2*(LEN(B2)=15),2),&月&,MID(B2,13-2*(LEN(B2)=15),2),&日 &)
计算平均成绩及评判是否及格:=CONCATENATE(INT(AVERAGE(B2:D2)),&: &,IF(AVERAGE(B2:D2)&=60,&&,&不&),&及格&)
提取前三名人员姓名:=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)))
将单词转换成首字母大写:=PROPER(A2)
将所有单词转换成小写形式:=LOWER(A2)
将所有句子转换成首字母大写其余小写:=CONCATENATE(PROPER(LEFT(A2)),LOWER(RIGHT(A2,LEN(A2)-1)))
将所有字母转换成大写形式:=UPPER(A2)
计算字符串中英文字母个数:{=SUM(N(NOT(EXACT(UPPER(MID(A2,ROW(INDIRECT(&1:&&LEN(A2))),1)),LOWER(MID(A2,ROW(INDIRECT(&1:&&LEN(A2))),1))))))}
计算字符串中单词个数:{=SUM(N(EXACT(TRIM(MID(UPPER(A2),ROW(INDIRECT(&1:&&LEN(A2))),1)),MID(PROPER(A2),ROW(INDIRECT(&1:&&LEN(A2))),1))))}
将文本型数字转换成数值:{=SUM(VALUE(B2:B10))}
计算字符串中的数字个数:=SUMPRODUCT(N(ISNUMBER(VALUE(MID(A2,ROW($1:$100),1)*1))))
提取混合字符串中的数字:{=MAX(IFERROR(VALUE(MID(A2,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A2&)),ROW(INDIRECT(&1:&&LEN(A2))))),0))}
标签(Tag):
------分隔线----------------------------
------分隔线----------------------------}