为您找到与excel2007函数与公式应用大全相关的共200个结果:
Excel2007函数公式怎么用?读文网小编为大家介绍Excel2007函数公式实例大全,接下来大家跟着读文网小编一起来了解下吧。
Excel2007函数公式收集了688个实例,涉及到137个函数、7个行业、41类用途,为大家提供一个参考,拓展思路的机会。公式由{}包括的为数组公式,在复制粘贴到单元后先去掉{}然后按住Shift键+Ctrl键再按Enter键,自动生成数组公式。
对三组生产数据求和:=SUM(B2:B7,D2:D7,F2:F7)
对生产表中大于100的产量进行求和:{=SUM((B2:B11>100)*B2:B11)}
对生产表大于110或者小于100的数据求和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)}
对一车间男性职工的工资求和:{=SUM((B2:B10="一车间")*(C2:C10="男")*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(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)))}
计算仓库进库数量之和:=SUMIF(B2:B10,"=进库",C2:C10)
计算仓库大额进库数量之和:=SUMIF(B2:B8,">1000")
对1400到1600之间的工资求和:{=SUM(SUMIF(B2:B10,"<="&{1400,1600})*{-1,1})}
求前三名和后三名的数据之和:=SUMIF(B2:B10,">"&LARGE(B2:B10,4))+SUMIF(B2:B10,"<"&SMALL(B2:B10,4))
对所有车间人员的工资求和:=SUMIF(A2:A10,"?车间",C2)
对多个车间人员的工资求和:=SUMIF(A2:A10,"??车间*",C2)
汇总姓赵、刘、李的业务员提成金额:=SUM(SUMIF(A2:A10,{"赵","刘","李"}&"*",C2:C10))
汇总鼠标所在列中大于600的数据:=SUMIF(INDIRECT("R2C"&CELL("col")&":R8C"&CELL("col"),FALSE),">600")
只汇总60~80分的成绩:=SUMIFS(B2:B10,B2:B10,">=60",B2:B10,"<=80")
汇总三年级二班人员迟到次数:=SUMIFS(D2:D10,B2:B10,"三年级",C2:C10,"二班")
汇总车间女性人数:=SUMIFS(C2:C11,A2:A11,"*车间",B2:B11,"女")
计算车间男性与女性人员的差:=SUM(SUMIFS(C2:C11,B2:B11,{"女","男"},A2:A11,"*车间")*{-1,1})
计算参保人数:=SUMPRODUCT((C2:C11="是")*1)
求25岁以上男性人数:=SUMPRODUCT((B2:B10="男")*1,(C2:C10>25)*1)
汇总一班人员获奖次数:=SUMPRODUCT((B2:B11="一班")*C2:C11)
汇总一车间男性参保人数:=SUMPRODUCT((A2:A10&B2:B10&C2:C10="一车间男是")*1)
汇总所有车间人员工资:=SUMPRODUCT(--NOT(ISERROR(FIND("车间",A2:A10))),C2:C10)
汇总业务员业绩:=SUMPRODUCT((B2:B11={"江西","广东"})*(C2:C11="男")*D2:D11)
根据直角三角形之勾、股求其弦长:=POWER(SUMSQ(B1,B2),1/2)
计算A1:A10区域正数的平方和:{=SUMSQ(IF(A1:A10>0,A1:A10))}
根据二边长判断三角形是否为直角三角形:=CHOOSE((SUMSQ(MAX(B1:B3))=SUMSQ(LARGE(B1:B3,{2,3})))+1,"非直角","直角")
计算1到10的自然数的积:=FACT(10)
计算50到60之间的整数相乘的结果:=FACT(60)/FACT(49)
计算1到15之间奇数相乘的结果:=FACTDOUBLE(15)
计算每小时生产产值:=PRODUCT(C2:E2)
根据三边求普通三角形面积:=(PRODUCT(SUM(B1:B3)/2,SUM(B1:B3)/2-LARGE(B1:B3,{1,2,3})))^0.5
根据直角三角形三边求三角形面积:=PRODUCT(LARGE(B1:B3,{2,3}))/2
跨表求积:=PRODUCT(产量表:单价表!B2)
求不同单价下的利润:{=MMULT(B2:B10,G2:H2)*25%}
制作中文九九乘法表:=COLUMN()&"*"&ROW()&"="&MMULT(ROW(),COLUMN())
计算车间盈亏:=SUM(MMULT((B3:E5>0)*B3:E5,{1;1;1;1}),MMULT((B3:E5<0)*B3:E5,{1;1;1;1}))
计算各组别第三名产量是多少:{=MAX(MMULT(COLUMN(A:E)^0,B2:G6))}
计算C产品最大入库量:{=MAX(MMULT(N(A2:A11="C"),TRANSPOSE((B2:B11)*(A2:A11="C"))))}
求入库最多的产品数量:{=MAX(MMULT(TRANSPOSE((B2:B11)*(A2:A11={"A","B","C","D"})),(A2:A11={"A","B","C","D"})*1))}
计算累计入库数:{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11)}
计算每日库存数:{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11-C2:C11)}
计算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/MMULT(N(A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17)),ROW(2:17)^0))}
计算前进中学参赛人数:{=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))}
串联单元格中的数字:{=MMULT(10^(COLUMNS(B:K)-COLUMN(C:L)),TRANSPOSE(B2:K2))}或=SUMPRODUCT(B2:K2,10^(COLUMNS(B:K)-COLUMN(B:K)-1))
计算达标率:{=MMULT(TRANSPOSE(N(A2:A11<=(B2:B11))),ROW(2:11)^0)/ROWS(2:11)}
计算成绩在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)}
汇总A组男职工的工资:{=MMULT(TRANSPOSE(N(B2:B11&C2:C11="男A组")*D2:D11),ROW(2:11)^0)}
计算象棋比赛对局次数l:=COMBIN(B1,B2)
计算五项比赛对局总次数:{=SUM(COMBIN(B2:B5,2))}
预计所有赛事完成的时间:=COMBIN(B1,B2)*B3/B4/60
计算英文字母区分大小写做密码的组数:=PERMUT(B1*2,B2)
计算中奖率:=TEXT(1/PERMUT(B1,B2),"0.00%")
计算最大公约数:=GCD(B1:B5)
计算最小公倍数:=LCM(B1:B5)
计算余数:=MOD(A2,B2)
汇总奇数行数据:=SUMPRODUCT(MOD(ROW(2:13),2)*C2:C13)
根据单价数量汇总金额:=SUMPRODUCT(MOD(COLUMN(A:I),2)*A2:I2,(MOD(COLUMN(B:J),2)=0)*B2:J2)
设计工资条:=IF(MOD(ROW(),3)=1,单行表头工资明细!A$1,IF(MOD(ROW(),3)=2,OFFSET(单行表头工资明细!A$1,ROW()/3+1,0),""))
根据身份证号计算性别:=IF(MOD(MID(B2,15,3),2),"男","女")
每隔4行合计产值:=IF(MOD(ROW(),5)=1,SUM(OFFSET(F2,-4,,4,)),D2*E2)
工资截尾取整:=B2+MOD(一月!B2,10)-MOD(B2+MOD(一月!B2,10),10)
汇总3的倍数列的数据:{=SUM(IF(MOD(COLUMN(A:I),3)=0,A2:I10))}
将数值逐位相加成一位数:=IF(A2=0,0,MOD(A2-1,9)+1)
计算零钞: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
秒与小时、分钟的换算:=QUOTIENT(MOD($A2,IF(COLUMN()=2,A2+1,60^(3-COLUMN(A:A)+1))),60^(3-COLUMN(A:A)))
生成隔行累加的序列:=QUOTIENT(ROW()+1,2)
根据业绩计算业务员奖金:=CHOOSE(MIN(QUOTIENT(B2,10000)+1,6),0,3%,5%,7%,9%,11%)*B2
计算预报温度与实际温度的最大误差值:{=MAX(ABS(C2:C8-B2:B8))}
计算个人所得税:=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)
产生100到200之间带小数的随机数:=RAND()*(200-100)+100
产生ll到20之间的不重复随机整数:{=RANK(A2:A11,A2:A11)+10}
将20个学生的考位随机排列:{=INDEX(A$2:A$11,RANK(H2:H11,H2:H11))}
将三个学校植树人员随机分组:=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),)
产生-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="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>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<>"",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",1000,1),2))}
计算异常停机时间:{=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&1234567890)),ROW(INDI
串联区域中的文本:=CONCATENATE(T(A2),T(B2),T(C2))
给公式添加运算说明:=CONCATENATE("你好",B2,"2008")&T(N("公式含义:连接“你好”和单元格B2、“2008”"))
根据身份证号码判断性别:=TEXT(MOD(MID(B2,15,3),2),"[=1]男;[=0]女")
将所有数据转换成保留两位小数再求和:{=SUM(--TEXT(B2:B11*C2:C11,"0.00"))}
将货款显示为“万元”为单位:=TEXT(B2,"¥#"&""""&"."&""""&"#,万元")
根据身份证号码计算出生日期:=IF(LEN(B2)=15,19,"")&TEXT(MID(B2,7,8-(LEN(B2)=15)*2),"#年00月00日")
显示今天的英文日期及星期:="资料日期:"&TEXT(TODAY(),"dddd, mmmm dd, yyyy")
显示今天每项工程的预计完成时间:=TEXT(SUM("08:00",B$2:B2),"h:mm:ss 上午/下午")
统计A列有多少个星期日:{=SUM(N(TEXT(A1:A11,"aaa")="日"))}
将数据显示为小数点对齐:=TEXT(B2,"#.0????")
计算A列的日期有几个属于第二季度:{=SUM((--(TEXT(A1:A11,"m"))>{3,6})*{1,-1})}
在A列产生1到12月的英文月份名:=TEXT((ROW())&"-1","mmmm")
将日期显示为中文大写:=TEXT("2008-8-10","[DBNum2]yyyy年m月d日")
将数字金额显示为人民币大写:=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,,"零")),"零分",""))
判断单元格的数据类型:=TEXT(A2,"大于○;小于○;○;文本")
计算达成率,以不同格式显示:=TEXT(B2/800,"[>=1]0.0倍;[>0]0.00%;")
计算字母“A”的首次出现位置,忽略大小写:=TEXT(SEARCH("a",A2&"a"),"[>"&LEN(A2)&"]没找到;第"&SEARCH("a",A2&"a")&"个")
从身份证号码中提取表示性别的数字:=MID(B2,TEXT(LEN(B2),"[=15]15;17"),1)
将三列数据交换位置:{=TEXT({1,-1,0},C1:C5&";"&"!"&B1:B5&";"&A1:A5)}
计算年终奖:=TEXT(B2,"[>3]15!0!0;[>1]1!0!0!0;5!0!0;")
计算星期日完工的工程个数:{=COUNT((TEXT(B2:B10+C2:C10-1,"AAA")="日")^0)}
计算本月星期日的个数:{=SUM(N(TEXT(TODAY()-TEXT(TODAY(),"d")+ROW(INDIRECT("1:"&DAY(DATE(,TEXT(TODAY(),"m")+1,)))),"AAA")="日"))}
检验日期是否升序排列:=TEXT(N(A3>=A2),";;日期有误;")
判断单元格中首字符的类型:=TEXT(IF(AND(CODE(UPPER(A3))>64,CODE(UPPER(A3))<91),CODE(A3),A3),"[="&CODE(A3)&"]字母;;数字;汉字")
计算每个季度的天数:{=SUM(--TEXT(DATE(2008,3*ROW(A1)-ROW($1:$3)+2,),"d"))}
将数据重复显示5次:=SUBSTITUTE(TEXT(A2&"?","@@@@@"),"?","")
将表示起止时间的数字格式化为时间格式:=TEXT(B2,"#!:00-00!:00")
根据起止时间计算经过时间:=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分钟")
将数字转化成电话格式:=TEXT(A2,"(0000)0000-0000")
在A1:A7区域产生星期一到星期日的英文全称:{=TEXT(ROW(1:7)+1,"DDDD")}
将汇总金额保留一位小数并显示千分位分隔符:{=FIXED(SUM(--FIXED(B2:B11*C2:C11,1)),1,FALSE)}
计算订单金额并以“百万”为单位显示:=FIXED(SUMPRODUCT(B2:B10,C2:C10),-6)/1000000
将数据对齐显示,将空白以“.”占位:=WIDECHAR(REPT(".",10-LEN(B2))&B2)
利用公式制作简易图表:=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))
利用公式制作带轴的图表且标示升降:{=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))))))}
计算单元格中数字个数:=LEN(A2)*2-LENB(A2)
将数字倒序排列:{=TEXT(SUM(MID(A2,ROW(INDIRECT("1:"&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(A2)*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)))),"")
将单位为“双”与“片”混合的数量汇总:{=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),"")
分别提取长、宽、高:=MID($B2,FIND("@",SUBSTITUTE($B2,"(","@",COLUMN(A1)))+1,FIND("@",SUBSTITUTE($B2,")","@",COLUMN(A1)))-FIND("@",SUBSTITUTE($B2,"(","@",COLUMN(A1)))-1)
提取学校与医院地址:{=IF(OR(IFERROR(FIND({"学校","医院"},A2),FALSE)),A2,"")}
计算密码字符串中字符个数:{=COUNT(FIND(CHAR(ROW(65:90)),A2),FIND(CHAR(ROW(97:122)),A2),FIND(ROW(1:10)-1,A2))}
通讯录单列转三列:{=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)}
将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))}
将产品型号规范化:=IF(MID(A2,5,2)="00",A2,REPLACE(A2,5,,"00"))
求最大时间:{=TEXT(MAX(--TEXT(REPLACE(LEFT(A2:A7,7),5,1,RIGHT(A2:A7,2)),"00!:00 00-00")),"hmm/dd/mm")}
分别提取小时、分钟、秒:=REPLACE(REPLACE($A$1&$A2,FIND(B$1,$A$1&$A2),100,),1,FIND(A$1,$A$1&$A2)+1,)
将年级或者专业与班级名称分开:{=REPLACE(A2,MAX(IFERROR(SEARCH(CHAR(ROW($65:$90)),A
提取各软件的版本号:=REPLACE(REPLACE(A2,1,SEARCH("(",A2),),LEN(REPLACE(A2,1,SEARCH("(",A2),)),1,)
店名分类:=IF(COUNT(SEARCH({"小吃","酒吧","茶","咖啡","电影","休闲","网吧"},A2))=1,"餐饮娱乐",IF(COUNT(SEARCH({"干洗","医院","药","茶","蛋糕","面包","物流","驾校","开锁","家政","装饰","搬家","维修","中介","卫生","旅馆"},A2))=1,"便民服务",IF(COUNT(SEARCH({"游乐场","旅行社","旅游"},A2))=1,"旅游")))
查找编号中重复出现的数字:重复数字个数{=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,)
统计名为“刘星”者人数:{=COUNT(SEARCH("?刘星",A2:A9))}
剔除多余的省名:=SUBSTITUTE(A2,IF(ISERROR(SEARCH("重庆市",A2)),"","四川省"),"")
将日期规范化再求差:=SUBSTITUTE(C2,".","-")-SUBSTITUTE(B2,".","-")
提取两个符号之间的字符串:=TRIM(MID(SUBSTITUTE(B2,"*",REPT(" ",50)),FIND("*",B2),100))
产品规格格式转换:=SUBSTITUTE(SUBSTITUTE(A2,":","("),"*",")*")&")"
判断调色配方中是否包含色粉“B”:=LEN(SUBSTITUTE(B2,"B",""))<>LEN(B2)
提取姓名与省份:=TRIM(MID(A2,1,FIND("|",A2)-1)&MID(SUBSTITUTE(A2,"|",REPT(" ",100)),500,100))
将IP地址规范化:=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("."&A2,".0","."),".0","."),".","",1)
提取最后一次短跑成绩:=REPLACE(A2,1,FIND("々",SUBSTITUTE(A2,"|","々",LEN(A2)-LEN(SUBSTITUTE(A2,"|",)))),)
从地址中提取省名:=LEFT(A2,FIND("省",A2))
计算小学参赛者人数:{=COUNT(0/(LEFT(B2:B11)="小"))}
计算四川方向飞机票总价:=SUMPRODUCT(N(LEFT(A2:A11,2)="四川"),N(B2:B11="飞机"),C2:C11)
通过身份证号码计算年龄:=TEXT(TODAY(),"YYYY")-(IF(LEN(B2)=18,"",19)&LEFT(REPLACE(B2,1,6,""),2+(LEN(B2)=18)*2))
从混合字符串中取重量:=LOOKUP(9E+307,--LEFT(B2,ROW($1:$10)))*C2
将金额分散填充:=LEFT(RIGHT(" ¥"&$A2*100,13-COLUMN()))
提取成绩并计算平均:{=AVERAGE(MID(A2:A7,4,LEN(A2:A7)-3)*1)}
提取参赛选手姓名:=MID(A2,FIND(":",A2)+1,LEN(A2))
从混合字符串中提取金额:=LOOKUP(307,--MID(B2,MIN(FIND({1;2;3;4;5;6;7;8;9},B2&123456789)),ROW($1:$99)))
从卡机数据提取打卡时间:=730>--MID(A2,14,4)
根据卡机数据判断员工部门:=CHOOSE(MATCH(--RIGHT(A2,3),{1,38,14,11,8,21,43,9,28},0),"生产部","业务部","总务部","人事部","食堂","保卫部","采购部","送货部","财务部")
根据身份证号码统计男性人数:{=SUM(MOD(LEFT(RIGHT(B2:B11,1+(LEN(B2:B11)=18))),2))}
从汉字与数字混合字串中提取温度数据:{=MAX(IFERROR(--RIGHT(LEFT(B2,LEN(B2)-1),ROW($1:$10)),0))}
将字符串位数统一:{=TEXT(RIGHT(A2,LEN(A2)-1),"!"&LEFT(A2)&REPT(0,MAX(LEN(A$2:A$10))-1))}
对所有人员按平均分排序:{=INDEX(A:A,RIGHT(LARGE(B$2:B$11*1000+ROW($2:$11),ROW()-1),3))}
取金额的角位与分位叫:=--RIGHT(ROUND(A2*100,),2)
从格式不规范的日期中取出日:=TRIM(RIGHT(SUBSTITUTE(A2,"."," ",2),3))
计算平均成绩(忽略缺考人员):=ROUND(AVERAGE(B2:B10),2)
计算90分以上的平均成绩:{=ROUND(AVERAGE(IF(ISNUMBER(B2:B10)*(B2:B10>90),B2:B10)),2)}
计算当前表以外的所有工作表平均值2:=AVERAGE(一班:五班!B:B)
计算二车间女职工的平均工资:{=AVERAGE(IF((B2:B10="二车间")*(C2:C10="女"),D2:D10))}
计算一车间和三车间女职工的平均工资:{=AVERAGE(IF((B2:B10="一车间")+(B2:B10="三车间")*(C2:C10="女"),D2:D10))}
计算各业务员的平均奖金:{=AVERAGE(1500+300*(INT((C2:C11-80000)/10000)))}
计算平均工资(不忽略无薪人员):=ROUND(AVERAGEA(B2:B10),2)
计算每人平均出口量:{=AVERAGEA((C2:C11="A")*D2:D11)}
计算平均成绩,成绩空白也计算:{=AVERAGEA(B2:B11*1)}
计算二年级所有人员的平均获奖率:{=TEXT(AVERAGEA(IF(LEFT(A2:A10,3)="二年级",B2:B10/C2:C10)),"0.00%")}
统计前三名人员的平均成绩:=AVERAGEA(LARGE(B2:B11,{1,2,3}))
求每季度平均支出金额:=AVERAGEIF(B2:B9,"支出",C2)
计算每个车间大于250的平均产量:=AVERAGEIF(B2:C11,">250")
去掉首尾求平均:=AVERAGEIFS(B2:B11,B2:B11,">"&MIN(B2:B11),B2:B11,"<"&MAX(B2:B11))
生产A产品且无异常的机台平均产量:=AVERAGEIFS(C2:C11,B2:B11,"A",D2:D11,"")
计算生产车间异常机台个数:=COUNT(C2:C11)
计算及格率:{=TEXT(COUNT(0/(B2:B11>=60))/COUNT(B2:B11),"0.00%")}
统计属于餐饮娱乐业的店名个数:{=COUNT(SEARCH({"小吃","酒吧","茶","咖啡","电影","休闲","网吧"},A2:A11))}
统计各分数段人数:{=COUNT(0/((B$2:B$11>ROW(A6)*10)*(B$2:B$11<=ROW(A7)*10)))}
统计有多少个选手:{=COUNT(0/(MATCH(B2:B11,B2:B11,)=(ROW(2:11)-1)))}
统计出勤异常人数:=COUNTA(B2:B11)
判断是否有人缺考:=IF(COUNTA(B2:E10)=ROWS(B2:E10)*COLUMNS(B2:E10),"没有","有")
统计未检验完成的产品数:=COUNTBLANK(B2:B11)
统计产量达标率:=TEXT(COUNTIF(B2:B11,">=800")/COUNT(B2:B11),"0.00")
根据毕业学校统计中学学历人数:=COUNTIF(B2:B11,"*中学")
计算两列数据相同个数:{=SUM(COUNTIF(A2:A11,B2:B11))}
统计连续三次进入前十名的人数:{=SUM(COUNTIF(C2:C11,IF(COUNTIF(A2:A11,B2:B11),B2:B11)))}
统计淘汰者人数:{=SUM(N(COUNTIF(A2:C11,A2:C11)=1))}
统计区域中不重复数据个数:{=SUM(1/COUNTIF(B2:B8,B2:B8))}
统计诺基亚、摩托罗拉和联想已隹出手机个数:=SUM(COUNTIF(B2:B11,"*"&{"诺基亚","摩托罗拉","联想"}&"*"))
统计联想比摩托罗拉手机的销量高多少:{=SUM(COUNTIF(B2:B11,{"诺基亚*","*联想*"})*{1,-1})}
统计冠军榜前三名:{=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)))}
统计真空、假空单元格个数:=COUNTIF(成绩!C2:C11,"=")
对名册表进行混合编号:=IF(RIGHT(B1)<>"班",ROW()-COUNTIF($B$1:B1,"??班"),TEXT(COUNTIF($B$1:B1,"??班"),"[DBNum2]0"))
提取不重复数据5:{=INDEX(B:B,MATCH(0,COUNTIF($D$1:D1,B$2:B$11),0)+1)}
中国式排名:{=SUM(IF(B$2:B$11>B2,1/COUNTIF(B$2:B$11,B$2:B$11)))+1}
统计大于80分的三好学生个数:{=COUNTIFS(B2:B11,"三好学生",C2:C11,">80")}
统计业绩在6万到8万之间的女业务员个数:=COUNTIFS(B2:B11,"女",C2:C11,">60000",C2:C11,"<=800000")
统计二班和三班数学竞赛获奖人数:=SUM(COUNTIFS(B2:B11,{"二班","三班"},C2:C11,"数学*"))
根据身高计算各班淘汰人数:=SUM(COUNTIFS(B$2:B$11,E1,C$2:C$11,{"<160",">180"}))
计算A列最后一个非空单元格行号:{=MAX((A:A<>"")*ROW(A:A))}
计算女职工的最大年龄:{=MAX((B2:B11="女")*C2:C11)}
消除单位提取数据:{=MAX(IFERROR(ABS(LEFT(A2,ROW($1:$100))),))*IF(LEFT(A2)="-",-1,1)}
计算单日最高销售金额:{=MAX(SUMIF(A2:A11,A2:A11,C2:C11))}
查找第一名学生姓名:=INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10,))
统计季度最高产值合计:{=MAX(SUBTOTAL(9,OFFSET(B2,,COLUMN(B:E)-2,ROWS(2:10),1)))}
根据达标率计算员工奖金:=MAX((B2>{0,0.8,0.9,1,1.05})*{200,250,300,450,550})
提取产品最后报价和最高报价:{=INDEX(C:C,MAX((A2:A11="B")*ROW(2:11)))}
计算卫冕失败最多的次数:{=MAX(FREQUENCY(ROW(2:11),((B2:B10="第一名")<>(B3:B11="第一名"))*ROW(2:10)))}
低于平均成绩中的最优成绩:{=MAX(IF(B2:B11<AVERAGE(B2:B11),B2:B11,))}
计算语文成绩大于90分者的最高总成绩:=DMAX(A1:E11,5,G1:G2)
计算数学成绩等于100分的男生最高总成绩:=DMAX(A1:E11,"总分",B1:B2)
根据下拉列表计算不同项目的最大值:=DMAX(A1:E11,G4,G1:G2)
计算中间成绩:=MEDIAN(B2:B11)
显示动态日期,但不能超过9月30日:=MIN("2008-9-30",TODAY())
根据工作时间计算可休假天数:=MIN(SUM((B2={"A","B","C"})*{5,4,3})+(C2-1),10)
确定最佳成绩:=MATCH(MIN(B2:B11),B2:B11,)
计算文具类产品和家具类产品最小利率:{=TEXT(MIN(IF(ISNUMBER(SEARCH("(?具类",A2:A11)),B2:B11)),"0.00%")}
计算得票最少者有几票:{=MIN(COUNTIF(B2:C11,B2:C11))}
根据工程的难度系数计算奖金:=MIN(A2,1+(A2>1.3)*0.3)*500
将科目与成绩分开:{=MID(A2,MIN(IF(ISNUMBER(FIND(ROW($1:$9),A2)),FIND(ROW($1:$9),A2))),100)}
计算五个班的第一名人员的最低成绩:=MIN(SUBTOTAL(4,INDIRECT({"一","二","三","四","五"}&"班!B2:b11")))
根据员工生产产品的废品率记分:=MAX(MIN(6-(B2*100-5),10),0)
统计售价850元以上的产品最低利率是多少:=DMIN(A1:D11,F4,F1:F2)
统计文具类和厨具类产品的最低单价:=DMIN(A1:B11,2,D1:D2)
第三个最小的成绩:=SMALL(B2:B11,3)
计算最后三名成绩的平均值:=AVERAGE(SMALL(B2:B11,{1,2,3}))
将成绩按升序排列:{=SMALL(B$2:B$11,ROW(A1))}
罗列三个班第一名成绩:{=SMALL(IF(C$2:C$11="第一名",D$2:D$11),ROW(A1))}
将英文月份名称升序排列:{=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))}
查看产品曾经销售的所有价位:{=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)))}
罗列三个工作表B列最后三名成绩:=SMALL(一班:三班!B:B,ROW(A1))
第3个最小成绩到第6个最小成绩之间的人数:{=SUM((((SMALL(B2:D11,ROW(INDIRECT("1:"&COUNT(B2:D11))))>SMALL(B2:D11,{3,6}))*{1,-1})))}
计算与第3个最大值并列的个数:{=SUM(--(B2:B11=LARGE(B2:B11,3)))}
计算大于等于前10个最大产量之和:=SUMPRODUCT((B2:C11>LARGE(B2:C11,11))*B2:C11)
按成绩列出学生排行榜:{=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))}
最后一次获得第一名是第几届:{=INDEX(A:A,LARGE((B2:B11="第一名")*ROW(2:11),1))}
提取销量的前三名的外销产品名称:{=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)}
哪种产品生产次数最多:{=TEXT(MODE(B2:B9*1),"00")}
罗列出被投诉多次的工作人员编号:{=IFERROR(TEXT(MODE(IF(COUNTIF($D$1:D1,$B$2:$B$11)=0,$B$2:$B$11*1)),"00"),"")}
对学生成绩排名:=RANK(B2,B$2:B$11,0)
计算两列数值相同个数:=COUNT(RANK(B2:B11,C2:C11))
查询某人成绩在三个班中的排名:成绩{=LOOKUP(0,0/(E2:E11=H2),F2:F11)};名次=RANK(I2,(B2:B11,D2:D11,F2:F11),0)
分别统计每个分数段的人员个数:{=FREQUENCY(B2:B11,D2:D5)}
蝉联冠军最多的次数:{=MAX(FREQUENCY(ROW(B$2:B$11),(B$2:B$10<>B$3:B$11)*ROW(B$2:B$10)))}
计算最多经过几次测试才成功:{=MAX(FREQUENCY(ROW(2:11),(B2:B11="成功")*ROW(2:11)))}
计算三个不连续区间的频率分布:{=SUM(LOOKUP({1,3,5},ROW(1:5),FREQUENCY(B2:B11,{500,550,600,650})))}
计算因密码错误被锁定几次:{=COUNT(0/((FREQUENCY(ROW(2:12),(B2:B12<>"错误")*ROW(B2:B12))-1)>=3))}
计算小学加初中人数及中专加大学人数:{=FREQUENCY((B2:B11<>"小学")*(B2:B11<>"初中"),0)}
计算文本的频率分布:{=FREQUENCY(CODE(B2:B11),CODE(D2:D5))}
夺冠排行榜:{=IF(ROW(A1)>SUM(1/COUNTIF($B$2:$B$11,$B$2:$B$11)),"",INDEX($B$2:$B$11,MATCH(LARGE(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)%,)))}
谁蝉联冠军次数最多:=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)),))
中国式排名:{=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)))&""}
记录当前日期与时间:=TEXT(NOW(),"m月d日 h:m:s")
确定是否已到加油时间:=TEXT(NOW()-B2,"H:m")>"5:30"
国庆倒计时:=TEXT("10-1"-TEXT(NOW(),"mm-dd"),"00")
统计发货到收款天数:=ROUNDUP(IF(B2<>"",B2-A2,NOW()-A2),0)
统计已到达收款时间的货品数量:=COUNTIF(B2:B10,"<"&(TODAY()-30))
本月需要完成几批货物生产:{=SUM(N(B2:B11=TEXT(TODAY(),"MMMM")))}
计算本季度收款的合计:{=SUM(IF(ROUNDUP(B2:B11/3,0)=ROUNDUP(TEXT(TODAY(),"M")/3,0),C2:C11))}
判断今年是否闰年:=OR((MOD(TEXT(TODAY(),"yyyy"),4)=0)*(MOD(TEXT(TODAY(),"yyyy"),100)<>0),AND(MOD(TEXT(TODAY(),"yyyy"),{100,400})=0))
计算2008年有多少个星期日:{=SUM(N(TEXT(DATE(2008,1,ROW(INDIRECT("1:"&("2008-12-31"-"2008-1-1")))),"AAA")="日"))}
计算本月有多少天:=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0),"D")
确定今年母亲节的日期:=DATE(YEAR(TODAY()),5,14-WEEKDAY(DATE(YEAR(TODAY()),4,30),2))
今年包含多少个星期:{=SUM(N(WEEKDAY(DATE(YEAR(TODAY()),1,ROW(INDIRECT("1:"&(365+(DAY(DATE(YEAR(TODAY()),2,29))=29))))),2)=7))+(WEEKDAY(DATE(YEAR(TODAY()),1,365+(DAY(DATE(YEAR(TODAY()),2,29))=29)))<7)}
将身份证号码转换成出生日期序列:=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))
计算建国多少周年:=YEAR(TODAY())-1949
计算2000年前电脑培训平均收费:{=AVERAGE(IF(YEAR(A2:A11)<2000,B2:B11))}
计算今天离本年度最后一天的天数:=(YEAR(TODAY())&"-12-31")-TODAY()
计算本月需要交货的数量:{=SUM((MONTH(B2:B11)=MONTH(TODAY()))*C2:C11)}
计算8月份笔筒和毛笔的进货数量:{=SUM(IF(MONTH(A2:A11)=8,IF((B1:H1="笔筒")+(B1:H1="毛笔"),B2:H11)))}
计算交货起止月:{=MIN(MONTH(B2:B11))&"月-"&MAX(MONTH(B2:B11))&"月"}
有几个月要交货:{=COUNT(0/FREQUENCY(MONTH(B2:B11),MONTH(B2:B11)))}
哪几个月要交货:{=IFERROR(SMALL(IF(FREQUENCY(MONTH(B$2:B$11),MONTH(B$2:B$11)),MONTH(B$2:B$11)),ROW(A1))&"月","")}
统计家具类和文具类产品在1月份的出库次数:{=SUM((B2:B11={"文具类","家具类"})*(IF(C2:C11>0,MONTH(C2:C11)=1)))}
计算今年平均每月天数:{=AVERAGE(DAY(DATE(YEAR(TODAY()),ROW(2:13),0)))}
计算员工转正时间:=DATE(YEAR(B2),MONTH(B2)+3+(DAY(B2)>15),16)
统计本月下旬出库数量:{=SUM(C2:C11*(DAY(B2:B11)>20))}
计算生产速度是否达标:=YEARFRAC(C2,D2)<=(E2/B2)
计算截至今天的利息:=B2*D2*YEARFRAC(C2,NOW())
计算还款日期:=TEXT(EDATE(B2,C2),"yy-mm-dd")
计算2008年到2010年共有多少天:{=SUM(DAY(EDATE("2008-1-31",ROW(1:36)-1)))}
提示合同续约:=TEXT(EDATE(B2,C2*12)-TODAY(),"[<0]合同过期;[<=10]即将到期;;")
计算借款日期到本月底的天数:=EOMONTH(B2,MONTH(TODAY())-MONTH(B2))-B2
计算本季度天数:=SUM(DAY(EOMONTH(NOW(),{0,1,2}-MOD(MONTH(NOW())-1,3))))
生成工资结算日期:=TEXT(EOMONTH(B2,0)+1,"e年M月D日")
统计两倍工资的加班小时数:=SUMPRODUCT(--(TEXT(ROW(INDIRECT(B2&":"&EOMONTH(B2,0))),"AAA")="六"))*8
计算员工工作天数和月数:=DATEDIF(B2,C2,"M")
根据进厂日期计算员工可假休天数:=MIN(IF(DATEDIF(B2,TODAY(),"M")<6,0,IF(DATEDIF(B2,TODA
根据身份证号码计算年龄(包括年月天):=CONCATENATE(DATEDIF(TEXT(MID(B2,7,LEN(B2)/2-1),"#-00-00"),TODAY(),"Y"),"年",DATEDIF(TEXT(MID(B2,7,LEN(B2)/2-1),"#-00-00"),TODAY(),"YM"),"月",DATEDIF(TEXT(MID(B2,7,LEN(B2)/2-1),"#-00-00"),TODAY(),"MD"),"天")
计算年资:=10*MIN(DATEDIF(B2,TODAY(),"y"),15)+MAX(DATEDIF(B2,TODAY(),"y")-15,0)*5
计算临时工的工资:=ROUND(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(B2,"分",""),"小时",":"))/(8/24)*50,)
计算本日工时工资:=(HOUR(C2-TIMEVALUE("8:00"))-1-ROUNDUP(B2-TIMEVALUE("8:00"),0))*6
计算8:00一16:00的平均电压:{=AVERAGE(IF((DAY(A2:A11)=8)*(HOUR(A2:A11)>=8)*(HOUR(A2:A11)>=16),B2:B11))}
计算工作时间,精确到分钟:=HOUR(C2)+MINUTE(C2)/60-HOUR(B2)-MINUTE(B2)/60-D2+24*(C2<B2)
根据完工时间计算奖金:=IF(HOUR(B2)>=18,-(ROUNDUP((HOUR(B2-"18:00")*60+MINUTE(B2))/30,0))*3,(ROUNDDOWN((HOUR("18:00"-B2)*60+60-MINUTE(B2))/30,0))*3)
计算工程时间:=SUMPRODUCT(MINUTE(B2:B11)+(SECOND(B2:B11)>0))
计算今天是星期几:=WEEKDAY(NOW(),2)
汇总星期日的支出金额:{=SUM((WEEKDAY(A2:A11,2)=7)*(B2:B11="支出")*C2:C11)}
汇总第一个星期的出库数量:{=SUM(OFFSET(A2,,MIN(IF(WEEKDAY(B1:P1,2)=1,COLUMN(B:P))),,7))}
计算每日工时工资:=8*5*IF(WEEKDAY(A2,2)<6,1,1.5)+(B2-8)*5*1.5
计算指定日期所在月份有几个星期日:{=SUM(N(WEEKDAY(DATE(YEAR(A2),MONTH(A2),ROW(INDIRECT("1:"&DAY(EOMONTH(A2,0))))))=1))}
按周汇总产量:{=SUM(((WEEKDAY($B1,2)-WEEKDAY($B1:$AF1,2))+(COLUMN($B1:$AF1)-1)=(1+(COLUMN(A1)-1)*7))*$B2:$AF2)}
按周汇总进仓与出仓数量:{=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))}
罗列本月休息日:{=IFERROR(SMALL(IF(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),ROW(INDIRECT("1:"&DAY(EOMONTH(NOW(),0))))),2)=2,DATE(YEAR(NOW()),MONTH(NOW()),ROW(INDIRECT("1:"&DAY(EOMONTH(NOW(),0)))))),ROW()),"")}
计算周末奖金补贴:=SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(B2&":"&C2))-1,2)>5))*10
罗列值班日期:{=MIN(IF(WEEKDAY(DATE(2008,ROW(),ROW($1:$31)),2)=7,DATE(2008,ROW(),ROW($1:$31))))}
计算本月加班时间:{=SUM((MOD(MOD(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),ROW(INDIRECT("1:"&DAY(EOMONTH(NOW(),0))))),2),7),2)={1,0})*{3,2})}
今天是本年度第几周:=WEEKNUM(TODAY())
本月包括多少周:=WEEKNUM(EOMONTH(NOW(),0),2)-WEEKNUM((EOMONTH(NOW(),-1)+1),2)+1
罗列第30周日期:{=TEXT(SMALL(IF(WEEKNUM(DATE(YEAR(NOW()),1,ROW($1:$366)),2)=30,DATE(YEAR(NOW()),1,ROW($1:$366))),ROW(A1)),"YYYY-MM-DD")}
统计某月第四周的支出金额:{=SUM((WEEKNUM(A2:A11*1,1)-WEEKNUM(YEAR(A2:A11)&"-"&MONTH(A2:A11)&"-1")+1=4)*B2:B11)}
判断本月休息日:{=(SUM(N(WEEKNUM(ROW((INDIRECT((EOMONTH(NOW(),-1)+1)&":"&EOMONTH(NOW(),0)))),2)-WEEKNUM(EOMONTH(NOW(),-1)+1,2)+1=5))>3)+4}
计算离职日期:=WORKDAY(A2,5,{"2008-10-1","2008-10-2","2008-1-3"})
计算工程完工日期:{=WORKDAY(A2,B2,EOMONTH(A2,ROW(INDIRECT("1:"&INT(B2/30*2)))))}
计算2008年第一季度有多少个工作日:=NETWORKDAYS(EOMONTH(NOW(),-MONTH(NOW()))+1,EOMONTH(NOW(),3-MONTH(NOW())),{"2008-2-7","2008-2-8","2008-2-9"})
计算2008年第一季度有多少个非工作日:=EOMONTH(NOW(),3-MONTH(NOW()))-EOMONTH(NOW(),-MONTH(NOW()))-NETWORKDAYS(EOMONTH(NOW(),-MONTH(NOW()))+1,EOMONTH(NOW(),3-MONTH(NOW())),{"2008-2-7","2008-2-8","2008-2-9"})
计算今天离国庆节还有多少个工作日:=NETWORKDAYS(TODAY(),DATE(YEAR(TODAY())+(TODAY()>DATE(YEAR(TODAY()),10,1)),10,1))
填充12个月的月份名:=CONCATENATE("第",TEXT(ROW(A1),"[DBNum1]"),"月")
产生“坐标”:=CHAR(64+COLUMN(A1))
检查日仓库报表日期是否正确:{=IF(SUM(N((11-RANK(A2:A11,A2:A11))=(ROW(2:11)-1)=FALSE)),"非递增","递增")}
检查字符串中哪一个字符出现次数最多:{=CHAR(MODE(IFERROR(CODE(MID(A2,ROW(1:16),1)),"")))}
产生每两行累加1的编号:=IF(ROW()=1,1,IF(MOD(ROW(),3),COUNT(OFFSET(A$1,,,ROW()-1))+1,""))
最后一次不及格是哪次测试:{=INDEX(A:A,MAX((B2:B11<60)*ROW(2:11)))}
计算第11名到第30名学员的平均成绩:{=AVERAGE(IF(RANK(B2:B101,B2:B101)=TRANSPOSE(ROW(11:30)),B2:B101))}
计算成绩排名,不能产生并列名次:=SUMPRODUCT(--((A$2:A$15=A2)*(($C$2:$C$15)+1/ROW($C$2:$C$15))>C2+1/ROW(2:2)))+1
计算第一次收入金额大于30元时的金额是多少:=INDEX(B:B,MIN(IF((A2:A11=A2)*(B2:B11>30),ROW(2:11))))
计算扣除所有扣款后的最高薪资:{=MAX(B2:B10-MMULT(C2:G10*1,ROW(1:5)^0))}
对班级和成绩升序排列:{=1*MID(SMALL(1*($A2:$A12&TEXT($B2:$B12,"000")),ROW($A$2:$A$12)-1),{1,2},{1,3})}
罗列今日销售的诺基亚手机型号:{=T(INDEX(B:B,SMALL(IF(ISERROR(FIND("诺基亚",B$2:B$11)),10^6,ROW($2:$11)),ROW(1:1))))}
统计图书数量:{=IF(B2="","",MIN(IF(B3:B$13<>"",ROW(3:$13),13))-ROW())}
罗列第一名学生姓名:{=T(INDEX(A:A,SMALL(IF($B$2:$B$11=MAX(B$2:B$11),ROW($2:$11),12),ROW(A1))))}
罗列1到1000之间的质数:{=INDEX(A:A,SMALL(IF(A$2:A$1000<>"",ROW($2:$1000),1001),ROW(A1)))&""}
判断某数是否为质数:{=IF(A2<2,"非质非合",IF(SMALL(IF(MOD(A2,ROW(INDIRECT("1:"&A2)))=0,ROW(INDIRECT("1:"&A2))),2)=A2,"质数","合数"))}
计算某个数的约数个数及罗列所有约数:约数个数{=COUNT(0/(MOD(A2,ROW(INDIRECT("1:"&A2)))=0))};罗列约数{=IFERROR(SMALL(IF(MOD(A$2,ROW(INDIRECT("1:"&A$2)))=0,ROW(INDIRECT("1:"&A$2))),ROW(A1)),"")}
将六个号码组合成一个:{=SUM(B1:B6*10^(2*(ROWS(B1:B6)-ROW(1:6))))}
将每个人的贷款重新分组:{=INDEX($C:$C,SMALL(IF($A$2:$A$11=$E2,ROW($2:$11),ROWS($1:$12)),COLUMN(A1)))}
检测每个志愿是否与之前的重复:=MATCH(B2,$B$2:$B$10,)<>ROWS($2:2)
将列标转换成数字:=COLUMN(INDIRECT(A2&1))
重组人事资料表:=REPLACE(INDIRECT("B"&1+(ROW(A1)-1)*4+COLUMN(A:A)),1,LEN(D$1)+1,"")
班级成绩查询:{=INDEX($B:$E,SMALL(IF($A$2:$A$12=$H$2,ROW($2:$12),ROWS($1:$12)+1),ROW(A1)),COLUMN(A1))&""}
罗列每日缺席名单:{=INDEX(全体成员!$1:$1,SMALL(IF(COUNTIF($B2:$K2,全体成员!$A$1:$M$1)=0,COLUMN($A:$M),16384),COLUMN(A1)))&""}
计算所有人的一周产量并排名:{=INDEX(1:1,RIGHT(LARGE(SUBTOTAL(9,OFFSET($A2:$A8,,COLUMN($B:$J)-1,,))*10+COLUMN($B:$J)-1,COLUMN(A1)))+1)}
将金额分散填充,空位以“-”占位:=MID(TEXT(INT($A2*100),REPT("-",9-LEN(INT($A2)))&REPT(0,LEN(INT($A2))+1)),COLUMNS($A:A),1)
提取引用区域右下角的数据:=INDIRECT(ADDRESS(ROW(B3:D7)+ROWS(B3:D7)-1,COLUMN(B3:D7)+COLUMNS(B3:D7)-1))
整理成绩单:=INDIRECT(CHAR(ROWS($1:22)*3)&COLUMN())
合并三个工作表的数据:=INDIRECT(CHOOSE(MOD(ROW(A2)-1,3)+1,"一年级!A"&INT((ROW(A3))/3)+1,"二年级!A"&INT((ROW(A3))/3)+1,"三年级!A"&INT((ROW(A3))/3)+1))
多区域计数:=SUM(COUNTIF(INDIRECT({"C2:C11","F2:F11","I2:I11"}),"<60"))
求积、求和两相宜:=SUM(IF(C2="",INDIRECT("E"&LOOKUP(1,0/ISERROR((0/$C$1:C1="")),ROW($C$2:C2))&":E"&(ROW()-1)),C2*D2))
计算五个工作表最大平均值:{=MAX(SUBTOTAL(1,INDIRECT({"一","二","三","四","五"}&"班!B2:b11")))}
按卡号中的英文及数值排序:{=INDIRECT("A"&MOD(SMALL(CODE(B$2:B$11)*10000+MID(B$2:B$11,2,9)*100+ROW($2:$11),ROW(B1)),100))}
多行多列取唯一值:{=IF(OR((B$2:D$5<>"")*(COUNTIF(F$1:F1,B$2:D$5)=0)),INDIRECT(TEXT(MIN(IF((B$2:D$5<>"")*(COUNTIF(F$1:F1,B$2:D$5)=0),ROW(B$2:D$5)*1000+COLUMN(B:D))),"r0c???"),),"")}
罗列三个表中的最大值:{=SUBTOTAL(4,INDIRECT({"A组";"B组";"C组"}&"!B2:B11"))}
将三列课程转换成单列且忽略空值:{=INDIRECT(TEXT(SMALL(IF($B$2:$D$7<>"",ROW($2:$7)*1000+1,1048576001),ROW(A1)),"r#c000"),)&""}
罗列两个正整数的所有公约数:{=IFERROR(SMALL(IF((MOD(A$2,ROW(INDIRECT("1:"&GCD(A$2:B$2))))=0)*(MOD(B$2,ROW(INDIRECT("1:"&GCD(A$2:B$2))))=0),ROW(INDIRECT("1:"&GCD(A$2:B$2)))),ROW()-1),"")}
B列最大值的地址:{=ADDRESS(MAX(IF(B2:B11=MAX(B2:B11),ROW(2:11))),2)}
记录最后一次销量大于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))}
根据下拉列表引用不同工作表的产量:=INDIRECT(ADDRESS(11,2,1,1,D1))
根据下拉列表罗列班级成绩第一名姓名:{=IFERROR(INDIRECT(ADDRESS(LARGE(((INDIRECT(D$1&"!B2:B10")=MAX(INDIRECT(D$1&"!B2:B10")))*ROW($2:$10)),ROW(A1)),1,1,1,D$1)),"")}
查询成绩:=OFFSET(A1,MATCH(F1,A2:A11,0),MATCH(G1,B1:D1,0))
在具有合并单元格的A列产生自然数编号:=1+COUNT(OFFSET($A$2,,,ROW()-2,))
引用合并区域时防止产生0值:=IF(A1<>"",A1,OFFSET(B1,-1,))
计算10届运动会中有几次破纪录:=SUMPRODUCT(N(SUBTOTAL(5,OFFSET(B2,,,ROW(2:10)))<SUBTOTAL(5,OFFSET($B$2,,,ROW(1:9)))))
计第奎续三天之总产量大于等于25万元的次数:=SUMPRODUCT(N(SUBTOTAL(9,OFFSET($B$1,ROW(1:10)-1,,3))>=25))
进、出库合计查询:=SUM(OFFSET(A1,E2,MATCH(G2&"总计",B1:C1,0),F2-E2+1))
根据人数自动调整表格大小:{=IFERROR(OFFSET($E$1,SMALL(IF(F$2:F$5>=TRANSPOSE(ROW(INDIRECT("1:"&MAX(F$2:F$5)))),ROW($2:$5)-1),ROW(1:1)),),"")}
累计数据:{=SUM(OFFSET(B$2,,,ROW()-1))}
计算至少两科不及格的学生人数:{=SUM(--(COUNTIF(OFFSET($B$1,ROW(2:11)-1,,,4),"<60")>=2))}
列出成绩最好的科目:{=OFFSET(A2,,SUM((MAX(SUBTOTAL(9,OFFSET(A2,1,ROW(1:4),4)))=SUBTOTAL(9,OFFSET(A2,1,COLUMN(A:D),4)))*COLUMN(B:E))-1)}
计算及格率不超过50%的科目数:{=SUM(N(COUNTIF(OFFSET(A1,1,COLUMN(A:D),10,1),"<60")>=ROWS(2:11)/2))}
罗列两次未打卡人员:{=IFERROR(OFFSET(A$1,LARGE((COUNTIF(OFFSET(A$1,ROW($2:$11)-1,1,,4),"×")>=2)*ROW($2:$11),ROW(A1))-1,),"")}
计算语文、英语、化学、政治哪科总分最高:=CHOOSE(MATCH(MAX(SUBTOTAL(9,OFFSET(A1,1,MATCH({"语文","英语","化学","政治"},$B$1:$G$1,0),10,))),SUBTOTAL(9,OFFSET(A1,1,MATCH({"语文","英语","化学","政治"},$B$1:$G$1,0),10,)),0),"语文","英语","化学","政治")
连续三届达到100的次数:=SUMPRODUCT(N(COUNTIF(OFFSET(B1,ROW(2:9)-1,,3,1),">=100")=3))
罗列及格率最高的学生姓名:{=INDEX(A:A,SMALL(IF(MAX(COUNTIF(OFFSET(A$1,ROW($2:$11)-1,1,1,COLUMNS(B:G)),">=60"))=COUNTIF(OFFSET(A1,ROW($2:$11)-1,1,1,COLUMNS(B:G)),">=60"),ROW($2:$11),12),ROW(A1)))&""}
计算Excel类图书最多进货量及书名:{=MAX(SUMIF(OFFSET(B1,ROW(2:11)-1,1,1,6),">=100")*(B2:B11="excel"))}
计算Excel类图书进货最多的是哪一个月:{=INDEX(C1:H1,MATCH(MAX(SUMIF(B2:B11,"excel",OFFSET(C2,,COLUMN(C:H)-3,ROWS(2:11),1))),SUMIF(B2:B11,"excel",OFFSET(C2,,COLUMN(C:H)-3,ROWS(2:11),1)),0))}
根据下拉列表中的时间和产品名计算销量冠军:{=INDEX(A2:A11,MATCH(MAX(OFFSET(C2,,MATCH(J2,C1:H1,0)-1,ROWS(2:11),)*(B2:B11=K2)),OFFSET(C2,,MATCH(J2,C1:H1,0)-1,ROWS(2:11),)*(B2:B11=K2),0))}
根据下拉列表中的产品提取姓名与销量:{=IFERROR(1/MOD(SMALL(IF(B2:B11=K1,1/SUBTOTAL(9,OFFSET(C2,ROW(2:11)-2,0,1,COLUMNS(C:H)))+ROW(2:11)),ROW(1:10)),1),"")}
计算产量最高的季度:=TEXT(MATCH(MAX(SUBTOTAL(9,OFFSET(A1,{0,3,6,9},1,3))),SUBTOTAL(9,OFFSET(A1,{0,3,6,9},1,3)),0),"[DBNum1]0季度")
分栏打印:=IF(ROW()=1,CHOOSE(MOD(COLUMN()-1,3)+1,资料!$A$1,资料!$B$1,""),IF(MOD(COLUMN(),3)=0,"",OFFSET(资料!$A$1,INT(COLUMN()/3)*9+ROW()-1,MOD(COLUMN(),3)-1,)))
分类汇总:=IF(SUMIF(B$2:B$11,E2,C$2:C$11)=0,"",SUMIF(B$2:B$11,E2,C$2:C$11))
分类汇总并排序:{=OFFSET(B$1,RIGHT(LARGE(IF(MATCH(B$2:B$11,B$2:B$11,)=ROW($2:$11)-1,SUMIF(B$2:B$11,B$2:B$11,C$2:C$11)*1000+ROW($2:$11),ROWS($1:$11)+1),ROW(1:1)),3)-1,)&""}
工资查询:{=IFERROR(OFFSET(D1,MATCH(F2&G2&H2,A2:A11&B2:B11&C2:C11,0),),G2&"无此人")}
多表成绩查询:{=SUBTOTAL(9,OFFSET(INDIRECT(ADDRESS(1,MATCH(H1,1:1,0),1,1,{"一班";"二班";"三班"})),1,,ROWS(2:11),))}
计算每个学生总分是否高于本班平均成绩:{=SUM(C2:E2)>AVERAGE(IF((A2=A$2:A$11),SUBTOTAL(9,OFFSET(B$1,ROW($2:$11)-1,1,,COLUMNS(C:E)))))}
计算每个学生进入前三名的科目总数:{=SUM(N((RANK(N(OFFSET($B$2,ROW()-2,COLUMN(B:F)-2,1,1)),OFFSET($B$2,0,COLUMN(B:F)-2,ROWS($2:$11),1)))<=3))}
计算高于单科平均值的科目总数:{=SUM(N(N(OFFSET($B$2,ROW()-2,COLUMN(B:F)-2,1,1))>SUBTOTAL(1,OFFSET($B$2,0,COLUMN(B:F)-2,ROWS($2:$11),1))))}
罗列平均成绩倒数三名的班级:{=OFFSET(A1,MATCH(SMALL(SUBTOTAL(1,OFFSET(A1,ROW($2:$9)-1,1,1,COLUMNS(B:F)))*1000+ROW(2:9),ROW(1:3)),SUBTOTAL(1,OFFSET(A1,ROW($2:$9)-1,1,1,COLUMNS(B:F)))*1000+ROW(2:9),),)}
将姓名重复三次:{=T(OFFSET(A$1,ROUNDUP(ROW(INDIRECT("1:"&ROWS(A$2:A$5)*3))/3,0),))}
多表汇总金额:{=SUM(SUBTOTAL(6,OFFSET(INDIRECT({"华南区","华东区","华北区"}&"!B1:C1"),ROW(2:10)-1,)))}
从单价表引用单价并汇总金额:{=SUM((N(OFFSET(G1,MATCH(A2:A7,F2:F13,),)))*B2:B7)}
从单价表引用最新单价并汇总金额:{=SUM((N(OFFSET(F1,MATCH(A2:A7,D2:D13,)+(COUNTIF(D2:D13,A2:A7)-1),)))*B2:B7)}
根据完工状况汇总工程款:{=SUM(SUBTOTAL(9,OFFSET(C1,ROW(2:11)-1,,1,2))*(E2:E11=G2))}
统计最后三天的平均销量:{=SUBTOTAL(1,OFFSET(INDIRECT("B"&MAX((A:A<>"")*ROW(1:1048576))),,,-3,1))}
重组培训科目表:姓名=LOOKUP(ROW()-1,COUNTIF(OFFSET(B$1:G$1,,,ROW($1:$7)),"<>"),A$2:A$8)&"";科目=IFERROR(OFFSET(B$2,MATCH(H2,$A$2:$A$7,)-1,COUNTIF($H$2:H2,H2)-1),"")
从多个产品相同单价的单价表中引用单价:=SUMPRODUCT(COUNTIF(OFFSET(A$2,ROW($2:$4)-2,0,1,4),G2)*E$2:E$4)*H2
统计所有业务员销售利润并罗列排列榜:{=OFFSET(A1,MOD(LARGE(INT(SUBTOTAL(6,OFFSET(C2,ROW(C2:C11)-2,,,3)))*1000+ROW(2:11),ROW(2:11)-1),1000)-1,)}
按季度引用不同价格并统计金额与累计:{=IF(A2<>"累计",LOOKUP(COUNTIF(OFFSET(A$1,1,0,ROWS($2:2),),"合计")+1,ROW($2:$5)-1,F$2:F$5)*B2,SUM(C1:C$2*(A1:A$2<>"累计")))}
计算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(MMULT({1,1,1,1,1,1,1,1,1,1},SUBTOTAL(6,OFFSET(INDIRECT({"华东区","华南区","华北区","华中区","西南区"}&"!A1"),ROW(西南区!$2:$11)-1,1,1,3)))*1000+ROW(2:11))}
计算第几轮销量最高以及售货员姓名:{=OFFSET(A1,RIGHT(MAX(SUBTOTAL(9,OFFSET(D1,5*(ROW(INDIRECT("1:"&CEILING(COUNTA(C:C)/5,1)))-1),,5))*10+ROW(INDIRECT("1:"&CEILING(COUNTA(C:C)/5,1)))))*5-1,)}
提取组名及计算每组平均达标率:{=TEXT(SUBTOTAL(1,OFFSET(B1,((ROW(1:4))*2-1),,,8)),"0.00%")}
判断是否超过一半人达标率在90%以上:{=COUNTIF(OFFSET(B1,((ROW(1:4))*2-1),,,8),">=0.9")>COLUMNS(B:I)/2}
分别计算每个班第一名的成绩和姓名:名次{=MAX(SUBTOTAL(9,OFFSET(B$1,ROW($2:$31)-1,1,,COLUMNS(C:I)))*(B$2:B$31=K2))};名{=OFFSET(A$1,MOD(MAX((SUBTOTAL(9,OFFSET(B$1,ROW($2:$31)-1,1,,COLUMNS(C:I)))*1000+ROW($2:$31))*(B$2:B$31=K2)),1000)-1,)}
计算哪一个月完成目标:=OFFSET(A1,LOOKUP(,1*(SUBTOTAL(9,OFFSET(B1,1,0,ROW(2:12)-1))>=200),ROW(2:12)),)
有几次连续三个月的平均值低于整体平均值:{=SUM(N((SUBTOTAL(9,OFFSET(B4,ROW(2:11)-2,,3,2
计算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),9999999),9999999),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.0001,50.0001;0,0.5,1},2),"[>2]6;5")*HOUR(B2)+HLOOKUP(MINUTE(B2),{0,20.0001,50.0001;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))*1000+100),1048576)),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))}
根据姓名查找左边的身份证号:=LOOKUP(E2,B2:B9,A2:A9)
将中文大写编号转换成阿位伯数字小写:=TEXT(LOOKUP(1,0/(B2=TEXT(ROW($1:$1000),"[DBNum2]000")),ROW($1:$1000)),"000")
将姓名按拼音升序排列:{=LOOKUP(0,0/(ROW(A1)=MMULT(N($A$2:$A$11>=TRANSPOSE($A$2:$A$11)),ROW($2:$11)^0)),A$2:A$11)}
将酒店按星级降序排列:{=LOOKUP(ROUND(1/MOD(LARGE(LEN(B$2:B$10)+1/ROW($2:$10),ROW(A1)),1),0),ROW($2:$10),A$2:A$10)}
计算某班六年中谁获第一名次数最多:{=MAX(COUNTIF(B2:B7,B2:B7))}
罗列每个名次的所有姓名:{=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)))),"")}
提取新书的印刷批次:=LOOKUP(9E+307,--RIGHT(LEFT(A2,FIND("[",A2)-1),ROW($1:$99)))
罗列2008年每月第一个及最后一个星期日:{=MIN(IF(WEEKDAY(DATE(2008,ROW(A1),ROW(INDIRECT("1:"&DAY(DATE(2008,ROW(A1)+1,0))))),2)=7,DATE(2008,ROW(A1),ROW(INDIRECT("1:"&DAY(DATE(2008,ROW(A1)+1,0)))))))}
填补空白区:=LOOKUP(1,0/($A$2:A2<>""),A$2:A2)
将字母转换成评分:{=AVERAGE(LOOKUP(B2:I2,{"A","B","C","D","E"},{10,9.5,8,7,5}))}
将字母转换成评分并对选手排名:{=LOOKUP(MOD(LARGE(MMULT(LOOKUP($B$2:$I$7,{"A","B","C","D","E"},{10,9.5,8,7,5}),ROW($1:$8)^0)*10000+ROW($2:$7),ROW(A1)),10),ROW($2:$7),A$2:A$7)}
标识各选手应得的奖牌:{=LOOKUP(SUM(N(IF(FREQUENCY(B$2:B$11,B$2:B$11),B$2:B$11,0)>B2))+1,ROW($1:$4),{"冠军","亚军","季军",""})}
计算各厂商参赛人数:{=IFERROR(LOOKUP(SMALL(IF(A$2:A$21<>"",ROW($2:$21)),ROW(A1)),ROW($2:$21),A$2:A$21)&": "&MMULT(SMALL(IF(A$2:A$21<>"",ROW($1:$20),21),ROW(A1)+{0,1}),{-1;1}),"")}
从品名信息中分别提取多段数值:=IFERROR(-LOOKUP(0,-MID($A2,FIND(B$1,$A2)+LEN(B$1),ROW($1:$100))),"--")
反向查找数据:=LEN(A2)-LOOKUP(100,SEARCH(B2,A2,ROW($1:$99)))-LEN(B2)+2
一级、二级分组编号:=TEXT(COUNTIF(B$1:B2,"第*"),"00")&TEXT(ROW()-LOOKUP(1,0/(LEFT(B$1:B2)="第"),ROW($1:2)),"[=0] ;000")
计算购货金额:{=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("*",A2),""),ROW($1:$1000))))}
谁是百米冠军:=LOOKUP(0,0/(B2:B11=MIN(B2:B11)),A2:A11)
从销售记录中提取销量与单价并计算金额:{=LOOKUP(10^16,--RIGHT(REPLACE(A2,FIND("公斤",A2),100,""),ROW($1:$100)))*LOOKUP(10^16,--RIGHT(REPLACE(A2,FIND("元",A2),100,""),ROW($1:$100)))}
根据比赛结果降序排列选手且标识名次:{=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),{"冠军","亚军","季军",""})}
计算每个职工的得分:=LOOKUP(,-FIND(B2,{"A**","A*","A","B**","B*","B","C**","C*","C","D"}),11-ROW($1:$10))
查询业务员的负责地区:{=T(INDEX(B:B,SMALL(IF(LOOKUP(ROW(A$2:A$11),IF(A$2:A$11<>"",ROW(A$2:A$11)),A$2:A$11)=$D$2,ROW(A$2:A$11),1048576),ROW(1:1))))}
根据产量计算员工产量得分:{=LOOKUP(B2,{3,0.5}*(ROW($1:$11)-1))}
根据员工得分转换为相应的等级:=LOOKUP(B2,--REPLACE(等级与分值!B$2:B$6,FIND("-",等级与分值!B$2:B$6),10,""),等级与分值!A$2:A$6)
提取产量冠军的组别:=IF(COUNTA(B2:E2),LOOKUP(1,0/ISTEXT(B2:E2),B$1:E$1),"")
区分工种和达标率计算奖金:=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))
使用通配符查找所有符合条件的数据:{=IFERROR(LOOKUP(1,0/SEARCH("*医院*",IF(COUNTIF($C$1:C3,A$2:A$12)=0,A$2:A$12,)),A$2:A$12),"")}
分别提取身份证号码中的年月日:=TEXT(TEXT(MID($A2,7,8),"0000-00-00"),"[DBNum1]"&CHOOSE(MATCH(B$1,{"年","月","日"},0),"YYYY年","M月","D日"))
根据不良率判断送货品处理办法:=CHOOSE((SUM(N(C2/B2>={0,0.005,0.01}))),"合格","允收","退货")
让VLOOKUP函数在多区域查找:=VLOOKUP(A11,CHOOSE(MATCH(B11,{"一年级","二年级","三年级"},0),A1:B9,D1:E9,G2:H9),2,0)
将区域互换位置:=VLOOKUP(E2&"",CHOOSE({2,1},A2:A9,C2:C9),2,0)
跨表统计最大值:{=CHOOSE(MOD(MAX(SUBTOTAL(9,INDIRECT({"A组";"B组";"C组"}&"!B2:B10"))*100+{1;2;3}),100),"A组","B组","C组")}
罗列所有参加田径的人员:{=IFERROR(VLOOKUP(1,CHOOSE({1,2},--(COUNTIF(OFFSET(C$2,,,ROW($2:$11)-1),"田径")=ROW(1:1)),A$2:A$11),2,),"")}
计算今天是本月的上旬、中旬还是下旬:=CHOOSE(MIN(CEILING(DAY(TODAY())/10,1),3), "上旬","中旬","下旬")
建立文件目录:=HYPERLINK("[E:产量表"&TEXT(ROW(1:1),"[DBNum1]")&"月产量表.xlsx]sheet1!A1",TEXT(ROW(1:1),"[DBNum1]")&"月产量表")
链接“总表”中B列最大值单元格:{=HYPERLINK("#总表!B"&MAX((MAX(总表!B:B)=总表!B:B)*ROW(B:B)),"至总表B列最大值")}
链接至B列最末的非空单元格:{=HYPERLINK("#B"&MAX(((B:B)<>"")*ROW(B:B)),"B列最后非空值")}
选择冠军姓名:{=HYPERLINK("#"&TEXT(SUM(SMALL(IF(B2:B13=MAX(B2:B13),ROW(2:13)),ROW(INDIRECT("1:"&COUNTIF(B2:B13,MAX(B2:B13)))))*10^((ROW(INDIRECT("1:"&COUNTIF(B2:B13,MAX(B2:B13))))-1)*2)),REPT("A00!,",COUNTIF(B2:B13,MAX(B2:B13))-1)&"A00"),"得票冠军")}
选择二年级旷课人员名单:{=HYPERLINK("#A"&MIN(IF(A2:A12="二年级",ROW(2:12)))&":B"&MAX(IF(A2:A12="二年级",ROW(2:12))),"二年级名单")}
选择产量最高工作表:{=HYPERLINK("#"&CHAR(64+MOD(MAX(SUBTOTAL(9,INDIRECT(CHAR(64+ROW(1:8))&"组!B2:B11"))*100+ROW(1:8)),100))&"组!A1","跳至最大产量组")}
选择打印区域:=HYPERLINK("#Print_Area",IF(ISERR(INDEX(Print_Area,1,1)),"未设置打印区","跳至打印区域"))
计算期末平均成绩:{=AVERAGE(IF(ISEVEN(COLUMN(B:I)-1),B3:I3))}
提取期末成绩明细:{=INDEX(成绩表!1:1,SMALL(IF(ISEVEN(COLUMN($B:$I)-(ROW()<>1)),COLUMN($B:$I)),COLUMN(A1)))}
提取每日累计出库数和每日库存数:日期=INDEX(A:A,ROW(A1)*2);累计出库数{=SUM(ISODD(ROW(INDIRECT("2:"&(ROW(A1)*2)+1)))*OFFSET(C$1,1,,ROWS($1:1)*2))};每日库存数{=SUM(SUMIF(OFFSET(B$1,1,,ROW(A1)*2),{"进库","出库"},C$2)*{1,-1})}
根据身份证号码汇总男、女职工总数:男{=SUM(--ISODD(MID(B2:B10,15,3)))};女{=SUM(--ISEVEN(MID(B2:B10,15,3)))}
提取当前表打印区域地址:=IF(ISNA(VLOOKUP("苹果",A2:B5,2,0)),10,LOOKUP(10^16,--LEFT(VLOOKUP("苹果",A2:B5,2,0),ROW(1:100))))
计算生产部人数和非生产部人数:生产部人数{=SUM((NOT(ISERR(FIND("车间",A2:A11)))*B2:C11))};非生产部人数{=SUM((ISERR(FIND("车间",A2:A11)))*B2:C11)}
提取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))))}
计算产品体积:=IF(ISERROR(FIND("/",B2)),B2^3,PRODUCT(1*TRIM(MID(SUBSTITUTE(B2,"/",REPT(" ",100)),{1,100,200},100))))
引用单价并去除干扰符:=IF(ISNA(MATCH(B2,单价表!B$1:E$1,0)),"请更新单价",C2*LOOKUP(10^16,--LEFT(HLOOKUP(B2,单价表!B$1:E$2,2,0),ROW($1:$100))))
查询书籍在七年中的最高单价:{=IF(ISNA(MATCH(A10,A2:A8,0)),"书名错误",MAX(VLOOKUP(A10,A1:H8,COLUMN(B:H),0)))}
根据计价单位查询单价:=IF(ISNA(MATCH(B2,F$1:H$1,0)),"未设定汇率",C2*HLOOKUP(B2,F$1:H$2,2,0))
数字、字母与汉字个数计算:数字个数{=SUM(--(ERROR.TYPE(INDIRECT("XFD"&MID(A2,ROW(INDI
判断错误类型:=LOOKUP(ERROR.TYPE(A2),ROW(1:7),{"空值错误";"被零除错误";"值错误";"无效的单元格引用";"无效的名称";"数字错误";"值不可用"})
罗列某运动员九次参赛成绩:{=INDEX($1:$1,MAX(ISTEXT(B2:E2)*COLUMN(B:E)))}
提取每年级第一名名单:=LOOKUP(1,0/ISTEXT(B2:E2),B$1:E$1)&":"&LOOKUP(1,0/ISTEXT(B2:E2),B2:E2)
将按日期排列的销售表转换成按品名排列:{=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),"")}
按月份统计每个产品的机器返修数量:=SUMPRODUCT(ISNUMBER(FIND(F$2,$A$2:$A$11))*(TEXT($B$2:$B$11,"YM")=TEXT($E3,"YM"))*$C$2:$C$11)
按文字描述求和:{=SUM(ISNUMBER(FIND(A$2:A$8,D2))*B$2:B$8)}
按编码计算库存总数:{=SUM(ISNUMBER(FIND("/"&A$2:A$11*1&"/","/"&D2&"/"))*B$2:B$11)}
从产品规格中提取直径、长、宽:长(直径)=LOOKUP(9.9E+307,--RIGHT(IF(ISNUMBER(FIND("×",A2)),REPLACE(A2,FIND("×",A2),100,""),A2),ROW($1:$100)));宽=IF(ISNUMBER(FIND("×",A2)),--RIGHT(A2,LEN(A2)-FIND("×",A2)),0)
累计每日得分:=(N(C1)=0)*5+N(C1)+IF(B2>0,-B2,0.1)
统计各班所有科目成绩大于60分者人数:{=MMULT(N(TRANSPOSE(A2:A21)=H3:H6),N(COUNTIF(OFFSET(C2:F2,ROW(2:21)-2,),">=60")=4))}
区分大小写统计不重复值个数:{=SUM(N(MMULT(N(EXACT(A2:A11,TRANSPOSE(A2:A11))),ROW(2:11)^0)=TRANSPOSE(ROW(2:11)-1))/TRANSPOSE(ROW(2:11)-1))}
累计每日库存数:=N(G1)+SUM(OFFSET(C$1,ROW(A1)*2-1,,2))-SUM(OFFSET(D$1,ROW(A1)*2-1,,2))
提取当前工作表名、工作簿名及存放目录:工作表=REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"");工作簿=SUBSTITUTE(REPLACE(CELL("filename"),1,FIND("[",CELL("filename")),""),REPLACE(CELL("filename"),1,FIND("]",CELL("filename"))-1,""),"");存放目录=REPLACE(CELL("filename"),FIND("[",CELL("filename")),100,"")
提取第一次参赛取得最佳成绩者姓名与成绩:参赛者{=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)}
计算哪一个项目得票最多:{=INDEX({"A","B","C"},RIGHT(MAX(MMULT(TRANSPOSE(ROW(2:11)^0),N(IF(ISBLANK(B2:B11),"A",B2:B11)={"A","B","C"}))*10+{1,2,3})))}
根据利率、存款与时间计算存款加利息数:=FV(B2,D2,-C2,0)
计算七个投资项目相同收益条件下谁投资更少:{=MAX(PV(B2:B8,C2:C8,0,100000))}
根据利息和存款数计算存款达到1万元需要几个月:=NPER(A2,0,-B2,C2)*12
根据投资金额、时间和目标收益计算增长率:=RATE(B2,0,-A2,C2)
根据贷款、利率和时间计算某段时间的利息:=CUMIPMT(B2/12,C2*12,A2,1,24,0)
根据贷款、利率和时间计算需偿还的本金:=CUMPRINC(B2/12,C2*12,A2,1,24,0)
以固定余额递减法计算资产折旧值:=DB(A$2,B$2,C$2,ROW(A1),12)
以双倍余额递减法计算资产折旧值:=DDB(A$2,B$2,C$2,1,2)
以年限总和折旧法计算折旧值:=SYD(A$2,B$2,C$2,ROW(A1))
使用双倍余额递减法计算任何期间的资产折旧值:=VDB(A$2,B$2,C$2*12,7,12,2)
获取当前工作簿中工作表数量:=COLUMNS(sheets)&T(NOW())
建立工作表目录与超级链接:=IFERROR(HYPERLINK(INDEX(sheets,ROW(A1))&"!a1",REPLACE(INDEX(sheets,ROW(A1))&T(NOW()),1,FIND("]",INDEX(sheets,ROW(A1))),"")),"")
选择最后工作表的最后非空单元格:=HYPERLINK(INDEX(sheets,COLUMNS(sheets))&"!A"&LOOKUP(1,0/(INDIRECT(INDEX(sheets,COLUMNS(sheets))&"!A:A")<>""),ROW(1:1048576)))
引用单元格数据同时引用格式:=IF(TODAY()>A2,"",TEXT(A2,格式))
分别汇总当前表以外的所有工作表数据:AcSht=GET.CELL(62);sheets=GET.WORKBOOK(1);WorkBook=GET.CELL(66);{=IFERROR(REPLACE(INDEX(sheets,SMALL(IF(TRANSPOSE(sheets)<>AcSht,ROW(INDIRECT("1:"&COLUMNS(sheets)))),ROW(A2))),1,LEN(WorkBook)+2,""),"")}
提取单元格的公式:名称=GET.CELL(6,Sheet1!$B1)&T(NOW())
罗列工作簿中所有名称:{=IFERROR(INDEX(名称,SMALL(IF(名称<>"名称",TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(名称))))),ROW(A1))),"")}
在任意单元格显示当前页数及总页数:无拘无束的页眉="第"&IF(横向当前页=1,纵向当前页,横向当前页+纵向当前页)&"页/共"&总页&"页";纵向当前页=IFERROR(MATCH(ROW(),GET.DOCUMENT(64))+1,1)
提取单元格中的批注:批注=GET.OBJECT(12, "备注 1")
利用列表框筛选数据:筛选=IF(GET.OBJECT(78,"列表框 1"),GET.OBJECT(78,"列表框 1")*TRANSPOSE(ROW(sheet1!$A$2:$A$8)))
判断单元格是否被图形对象覆盖:=ADDRESS(ROW(INDIRECT(左上,0)),COLUMN(INDIRECT(左上,0)))&":"&ADDRESS(ROW(INDIRECT(右下,0)),COLUMN(INDIRECT(右下,0)))
将单元格的公式转换成数值:计算=EVALUATE(Sheet1!A3)
将IP地址补足三位:IP地址=TEXT(EVALUATE("{"&SUBSTITUTE(Sheet1!A4,".",",")&"}"),"000.");QQ=TEXT(EVALUATE("{"&SUBSTITUTE(Sheet1!A4,".",".,")&"}"),"000")
按分隔符取数并求平均:成绩=EVALUATE(SUBSTITUTE("{"&SUBSTITUTE(Sheet1!$B2,"https://","/FALSE/")&"}","/",";"))
根据产品规格计算体积:体积=EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!XFD4,"(L)","*"),"(W)","*"),"(H)",""))
计算减肥前后的三国差异:后=EVALUATE("{"&SUBSTITUTE(Sheet1!C3,":",",")&"}"); 前=EVALUATE("{"&SUBSTITUTE(Sheet1!B3,":",",")&"}")
计算各楼层空佘面积:面积=EVALUATE(SUBSTITUTE(SUBSTITUTE(Sheet1!XFD1,"[","*istext("""),"]",""")"))
将数据分列,提取省市县:分列=EVALUATE("{"""&SUBSTITUTE(SUBSTITUTE(Sheet1!$A5,"省","省"","""),"市","市"",""")&"""}")
按图书编号汇总价格:图书=EVALUATE("{"""&SUBSTITUTE(Sheet1!B2,"/",""",""")&"""}")
标识B列中的重复值:条件格式:=COUNTIF($B:$B,B1)>1
将数据间隔着色:条件格式:=MOD(SUM(N($B$2:$B2<>$B$1:$B1)),2)=0
隐藏错误值:单元格的公式:=VLOOKUP(A2,单价表!$A$2:$B$10,2,0),条件格式:=ISERROR(B1)
突显前三个最大值:条件格式:=B2>LARGE($B$2:$F$10,4)
将成绩高于平均值的姓名标示“优等”:条件格式:=(B2>AVERAGE($B$2:$F$10))*MOD(COLUMN(),2)
突显奇数行:条件格式:=ISODD(ROW())
突显非数值:条件格式:=NOT(ISNUMBER(A2))*ISEVEN(COLUMN())
B列中禁止输入重复数据:数据有效性设置-自定义:=COUNTIF(B:B,B8)=1
仅允许录入英文姓名:数据有效性设置-自定义:=SUM(--(ERROR.TYPE(INDIRECT(MID(SUBSTITUTE(A2," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A2," ","")))),1)&1))=3))=LEN(SUBSTITUTE(A2," ",""))
强制录入规范化的日期:数据有效性设置-自定义:=(LEN(A2)=8)*TEXT(A2,"#-00-00")
建立动态下拉选单:定义名称:水果=OFFSET(单价表!$A$1,,,COUNTA(单价表!$A:$A))
建立二级下拉选单:定义名称:省=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)))
建立不重复的下拉选单:{=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))
让A列只能输入质数:数据有效性设置-自定义:=OR(A2=2,A2=3,PRODUCT(MOD(A2,ROW(INDIRECT("2:"& INT(A2^0.5))))))
设置D列只能录入男职工的姓名:数据有效性设置-自定义:=VLOOKUP(D2,A:B,2,0)="男"
禁止录入不完整的产品规格:数据有效性设置-自定义:=ISNUMBER(SEARCH("长?*宽?*高?*",B2))
自动记录进库时间:=IF(ISBLANK(B2),"",IF(C2="",NOW(),C2))
记录历史最高值:=MAX(B:B,D2)
解一元二次方程:X+100=X^2+10
解二元一次方程:X=10X=(100-5Y)/25,Y=Y/5=(200 +4X)/4
浏览量:2
下载量:0
时间:
读文网小编为大家分享了Excel2010函数公式大全,下面大家就跟着小编一起去了解下吧。
一、函数应用基础
1.函数和公式
(1)什么是函数
Excel函数即是预先定义,执行计算、分析等处理数据任务的特殊公式。以常用的求和函数SUM为例,它的语法是“SUM(number1,number2,......)”。其中“SUM”称为函数名称,一个函数只有唯一的一个名称,它决定了函数的功能和用途。函数名称后紧跟左括号,接着是用逗号分隔的称为参数的内容,最后用一个右括号表示函数结束。
参数是函数中最复杂的组成部分,它规定了函数的运算对象、顺序或结构等。使得用户可以对某个单元格或区域进行处理,如分析存款利息、确定成绩名次、计算三角函数值等。
按照函数的来源,Excel函数可以分为内置函数和扩展函数两大类。前者只要启动了Excel,用户就可以使用它们;而后者必须通过单击“工具→加载宏”菜单命令加载,然后才能像内置函数那样使用。
(2)什么是公式
函数与公式既有区别又互相联系。如果说前者是Excel预先定义好的特殊公式,后者就是由用户自行设计对工作表进行计算和处理的公式。以公式“=SUM(E1:H1)*A1+26”为例,它要以等号“=”开始,其内部可以包括函数、引用、运算符和常量。上式中的“SUM(E1:H1)”是函数,“A1”则是对单元格A1的引用(使用其中存储的数据),“26”则是常量,“*”和“+”则是算术运算符(另外还有比较运算符、文本运算符和引用运算符)。
如果函数要以公式的形式出现,它必须有两个组成部分,一个是函数名称前面的等号,另一个则是函数本身。
2.函数的参数
函数右边括号中的部分称为参数,假如一个函数可以使用多个参数,那么参数与参数之间使用半角逗号进行分隔。参数可以是常量(数字和文本)、逻辑值(例如TRUE或FALSE)、数组、错误值(例如#N/A)或单元格引用(例如E1:H1),甚至可以是另一个或几个函数等。参数的类型和位置必须满足函数语法的要求,否则将返回错误信息。
(1)常量
常量是直接输入到单元格或公式中的数字或文本,或由名称所代表的数字或文本值,例如数字“2890.56”、日期“2003-8-19”和文本“黎明”都是常量。但是公式或由公式计算出的结果都不是常量,因为只要公式的参数发生了变化,它自身或计算出来的结果就会发生变化。
(2)逻辑值
逻辑值是比较特殊的一类参数,它只有TRUE(真)或FALSE(假)两种类型。例如在公式“=IF(A3=0,"",A2/A3)”中,“A3=0”就是一个可以返回TRUE(真)或FALSE(假)两种结果的参数。当“A3=0”为TRUE(真)时在公式所在单元格中填入“0”,否则在单元格中填入“A2/A3”的计算结果。
(3)数组
数组用于可产生多个结果,或可以对存放在行和列中的一组参数进行计算的公式。Excel中有常量和区域两类数组。前者放在“”(按下Ctrl+Shift+Enter组合键自动生成)内部,而且内部各列的数值要用逗号“,”隔开,各行的数值要用分号“;”隔开。假如你要表示第1行中的56、78、89和第2行中的90、76、80,就应该建立一个2行3列的常量数组“{56,78,89;90,76,80}。
区域数组是一个矩形的单元格区域,该区域中的单元格共用一个公式。例如公式“=TREND(B1:B3,A1:A3)”作为数组公式使用时,它所引用的矩形单元格区域“B1:B3,A1:A3”就是一个区域数组。
(4)错误值
使用错误值作为参数的主要是信息函数,例如“ERROR.TYPE”函数就是以错误值作为参数。它的语法为“ERROR.TYPE(error_val)”,如果其中的参数是#NUM!,则返回数值“6”。
(5)单元格引用
单元格引用是函数中最常见的参数,引用的目的在于标识工作表单元格或单元格区域,并指明公式或函数所使用的数据的位置,便于它们使用工作表各处的数据,或者在多个函数中使用同一个单元格的数据。还可以引用同一工作簿不同工作表的单元格,甚至引用其他工作簿中的数据。
根据公式所在单元格的位置发生变化时,单元格引用的变化情况,我们可以引用分为相对引用、绝对引用和混合引用三种类型。以存放在F2单元格中的公式“=SUM(A2:E2)”为例,当公式由F2单元格复制到F3单元格以后,公式中的引用也会变化为“=SUM(A3:E3)”。若公式自F列向下继续复制,“行标”每增加1行,公式中的行标也自动加1。
如果上述公式改为“=SUM($A $3:$E $3)”,则无论公式复制到何处,其引用的位置始终是“A3:E3”区域。
混合引用有“绝对列和相对行”,或是“绝对行和相对列”两种形式。前者如“=SUM($A3:$E3)”,后者如“=SUM(A$3:E$3)”。
上面的几个实例引用的都是同一工作表中的数据,如果要分析同一工作簿中多张工作表上的数据,就要使用三维引用。假如公式放在工作表Sheet1的C6单元格,要引用工作表Sheet2的“A1:A6”和Sheet3的“B2:B9”区域进行求和运算,则公式中的引用形式为“=SUM(Sheet2!A1:A6,Sheet3!B2:B9)”。也就是说三维引用中不仅包含单元格或区域引用,还要在前面加上带“!”的工作表名称。
假如你要引用的数据来自另一个工作簿,如工作簿Book1中的SUM函数要绝对引用工作簿Book2中的数据,其公式为“=SUM([Book2]Sheet1! SA S1: SA S8,[Book2]Sheet2! SB S1: SB S9)”,也就是在原来单元格引用的前面加上“[Book2]Sheet1!”。放在中括号里面的是工作簿名称,带“!”的则是其中的工作表名称。即是跨工作簿引用单元格或区域时,引用对象的前面必须用“!”作为工作表分隔符,再用中括号作为工作簿分隔符。不过三维引用的要受到较多的限制,例如不能使用数组公式等。
提示:上面介绍的是Excel默认的引用方式,称为“A1引用样式”。如果你要计算处在“宏”内的行和列,必须使用“R1C1引用样式”。在这种引用样式中,Excel使用“R”加“行标”和“C”加“列标”的方法指示单元格位置。启用或关闭R1C1引用样式必须单击“工具→选项”菜单命令,打开对话框的“常规”选项卡,选中或清除“设置”下的“R1C1引用样式”选项。由于这种引用样式很少使用,限于篇幅本文不做进一步介绍。
(6)嵌套函数
除了上面介绍的情况外,函数也可以是嵌套的,即一个函数是另一个函数的参数,例如“=IF(OR(RIGHTB(E2,1)="1",RIGHTB(E2,1)="3",RIGHTB(E2,1)="5",RIGHTB(E2,1)="7",RIGHTB(E2,1)="9"),"男","女")”。其中公式中的IF函数使用了嵌套的RIGHTB函数,并将后者返回的结果作为IF的逻辑判断依据。
(7)名称和标志
为了更加直观地标识单元格或单元格区域,我们可以给它们赋予一个名称,从而在公式或函数中直接引用。例如“B2:B46”区域存放着学生的物理成绩,求解平均分的公式一般是“=AVERAGE(B2:B46)”。在给B2:B46区域命名为“物理分数”以后,该公式就可以变为“=AVERAGE(物理分数)”,从而使公式变得更加直观。
给一个单元格或区域命名的方法是:选中要命名的单元格或单元格区域,鼠标单击编辑栏顶端的“名称框”,在其中输入名称后回车。也可以选中要命名的单元格或单元格区域,单击“插入→名称→定义”菜单命令,在打开的“定义名称”对话框中输入名称后确定即可。如果你要删除已经命名的区域,可以按相同方法打开“定义名称”对话框,选中你要删除的名称删除即可。
由于Excel工作表多数带有“列标志”。例如一张成绩统计表的首行通常带有“序号”、“姓名”、“数学”、“物理”等“列标志”(也可以称为字段),如果单击“工具→选项”菜单命令,在打开的对话框中单击“重新计算”选项卡,选中“工作簿选项”选项组中的“接受公式标志”选项,公式就可以直接引用“列标志”了。例如“B2:B46”区域存放着学生的物理成绩,而B1单元格已经输入了“物理”字样,则求物理平均分的公式可以写成“=AVERAGE(物理)”。
需要特别说明的是,创建好的名称可以被所有工作表引用,而且引用时不需要在名称前面添加工作表名(这就是使用名称的主要优点),因此名称引用实际上是一种绝对引用。但是公式引用“列标志”时的限制较多,它只能在当前数据列的下方引用,不能跨越工作表引用,但是引用“列标志”的公式在一定条件下可以复制。从本质上讲,名称和标志都是单元格引用的一种方式。因为它们不是文本,使用时名称和标志都不能添加引号。
3.函数输入方法
对Excel公式而言,函数是其中的主要组成部分,因此公式输入可以归结为函数输入的问题。
(1)“插入函数”对话框
“插入函数”对话框是Excel输入公式的重要工具,以公式“=SUM(Sheet2!A1:A6,Sheet3!B2:B9)”为例,Excel输入该公式的具体过程是:
首先选中存放计算结果(即需要应用公式)的单元格,单击编辑栏(或工具栏)中的“fx”按钮,则表示公式开始的“=”出现在单元格和编辑栏,然后在打开的“插入函数”对话框中的“选择函数”列表找到“SUM”函数。如果你需要的函数不在里面,可以打开“或选择类别”下拉列表进行选择。最后单击“确定”按钮,打开“函数参数”对话框。
对SUM函数而言,它可以使用从number1开始直到number30共30个参数。对上面的公式来说,首先应当把光标放在对话框的“number1”框中,单击工作簿中的“Sheet2!”工作表标签,“Sheet2!”即可自动进入其中,接着鼠标拖动选中你要引用的区域即可。接着用鼠标单击对话框的“number2”框,单击工作簿中的“Sheet3!”工作表标签,其名称“Sheet3!”即可自动进入其中,再按相同方法选择要引用的单元格区域即可。 上述方法的最大优点就是引用的区域很准确,特别是三维引用时不容易发生工作表或工作簿名称输入错误的问题。 (2)编辑栏输入 如果你要套用某个现成公式,或者输入一些嵌套关系复杂的公式,利用编辑栏输入更加快捷。 首先选中存放计算结果的单元格;鼠标单击Excel编辑栏,按照公式的组成顺序依次输入各个部分,公式输入完毕后,单击编辑栏中的“输入”(即“√”)按钮(或回车)即可。 手工输入时同样可以采取上面介绍的方法引用区域,以公式“=SUM(Sheet2!A1:A6,Sheet3!B2:B9)”为例,你可以先在编辑栏中输入“=SUM()”,然后将光标插入括号中间,再按上面介绍的方法操作就可以引用输入公式了。但是分隔引用之间的逗号必须用手工输入,而不能像“插入函数”对话框那样自动添加。 二、函数速查一览 (一)数据库函数 1.DAVERAGE 用途:返回数据库或数据清单中满足指定条件的列中数值的平均值。 语法:DAVERAGE(database,field,criteria) 参数:Database构成列表或数据库的单元格区域。Field指定函数所使用的数据列。Criteria为一组包含给定条件的单元格区域。 2.DCOUNT 用途:返回数据库或数据清单的指定字段中,满足给定条件并且包含数字的单元格数目。 语法:DCOUNT(database,field,criteria) 参数:Database构成列表或数据库的单元格区域。Field指定函数所使用的数据列。Criteria为一组包含给定条件的单元格区域。 3.DCOUNTA 用途:返回数据库或数据清单指定字段中满足给定条件的非空单元格数目。 语法:DCOUNTA(database,field,criteria) 参数:Database构成列表或数据库的单元格区域。Field指定函数所使用的数据列。Criteria为一组包含给定条件的单元格区域。 4.DGET 用途:从数据清单或数据库中提取符合指定条件的单个值。 语法:DGET(database,field,criteria) 参数:Database构成列表或数据库的单元格区域。Field指定函数所使用的数据列。Criteria为一组包含给定条件的单元格区域。 5.DMAX 用途:返回数据清单或数据库的指定列中,满足给定条件单元格中的最大数值。 语法:DMAX(database,field,criteria) 参数:Database构成列表或数据库的单元格区域。Field指定函数所使用的数据列。Criteria为一组包含给定条件的单元格区域。 6.DMIN 用途:返回数据清单或数据库的指定列中满足给定条件的单元格中的最小数字。 语法:DMIN(database,field,criteria) 参数:Database构成列表或数据库的单元格区域。Field指定函数所使用的数据列。Criteria为一组包含给定条件的单元格区域。 7.DPRODUCT 用途:返回数据清单或数据库的指定列中,满足给定条件单元格中数值乘积。 语法:DPRODUCT(database,field,criteria) 参数:同上 8.DSTDEV 用途:将列表或数据库的列中满足指定条件的数字作为一个样本,估算样本总体的标准偏差。 语法:DSTDEV(database,field,criteria) 参数:同上 9.DSTDEVP 参数:将数据清单或数据库的指定列中,满足给定条件单元格中的数字作为样本总体,计算总体的标准偏差。 语法:DSTDEVP(database,field,criteria) |
大家在日常工作中,充分挖掘Excel的潜力,同样可以让自己的工作水平更上一层楼。
浏览量:2
下载量:0
时间:
Excel的一大功能之一就是函数与图表的功能,函数是负责帮助用户进行数据的计算,图表则是帮助用户方便查看数据,下面是读文网小编推荐的一本关于excel 2007公式函数与图表应用的书籍,内容非常适合刚开始学习Excel的新人朋友,欢迎阅读!
excel2007公式函数与图表应用
《Excel2007公式·函数与图表应用从入门到精通》:1手册+1CD多媒体视频教学光盘,从精彩范例轻松掌握Excel 2007!
由浅入深地传授Excel软件的各种常用操作方法、技巧和实用模型,成倍提高工作效率;讲解如何高效输入、编辑、核对和管理数据,如何使用公式和函数;讲解创建各种精美实用图表的基本方法和技巧,使您的分析报告更有说服力;详解如何利用数据透视表工具编制各种统计汇总报表,进行数据分析,游刃有余地做一份专业的报告。总结和分析Excel电子表格制作必知必会、使用频率最高的经典技法,荟萃205个超级实例,举一反三。分享教程中200多个教学案例的电子档文件资料(模板、表格和样例等),帮助读者快速上手制定实战项目。演示时间长达300分钟的视频教程,全程讲解操作过程,可以反复观摩学习。超值加赠畅销书《新手学Excel2007》的100个精华技法视频教程及全部实例文件,真正实现花一本书的价钱学习两本书的内容!制作数据报表、进行产品分析,展示业绩成效是企业白领几乎每天都在干的工作。通过分析数据和演示图表。董事会从中了解公司的运营情况,领导从中了解职员的成果和能力,客户从中了解产品的优势和特点,同事从中了解目前的进展和下一步的工作……这些无不激励你立志成为Excel高手,轻松驾御软件,成为办公职场达人。Excel提供了数百个函数供用户使用,如此多的功能让人难以适从。笔者根据多年从事Excel软件应用技能培训的丰富经验,将不同种类功能的实践应用加以归纳总结,以案例演练的方式,使读者能迅速将函数功能对应于本职工作中,掌握灵活运用的方法。如何才能从埋头苦干但是效率不高,转化为略使巧劲就能成效明显?要掌握哪些Excel软件技能、要怎样锻炼自己的动手创新能力……这些都是本教程讨论的重点。
浏览量:3
下载量:0
时间:
Excel的函数在商务办公的时候,使用频率非常高,它能为用户省去了计算的时间,大大提高工作效率,需要学习商务函数应用的现在请阅读学习来小编整理的一些excel 2007公式与函数商务应用的内容。
Chapter01公式与函数的重要概念
1-1公式与函数的基本概念2
1-1-1公式与函数的输入工作2
1-1-2运算符的使用4
1-1-3加上验证的输入方法8
1-1-4自定义验证公式9
1-2引用地址的重要概念11
1-2-1绝对引用与相对引用11
1-2-2使用外部引用的公式12
1-3单元格标签和名称的应用17
1-3-1单元格名称的定义和应用17
1-3-2在公式中使用名称22
Chapter02公式与函数的错误更正
Chapter03文本函数
Chapter04日期和时间函数
Chapter05矩阵公式
Chapter06统计和求和函数
Chapter07财务函数
Chapter08查找和引用函数
Chapter09善用Excel分析工具
Chapter10图表函数的应用
Chapter11表格(数据库)的应用
Chapter12数据透视表和透视图
Chapter13会计处理程序(一)分录、过账与试算
Chapter14会计处理程序(二)调整、结账与报表
附录A什么是VBA
A-1什么是VBA278
A-1-1VBA概述278
A-1-2何谓宏278
A-1-3创建简单的VBA宏278
A-2VBA程序结构287
A-2-1程序的调用287
A-2-2变量和常数288
A-2-3程序代码和语句290
附录B函数索引
文本函数296
统计函数296
数学和三角函数299
查找和引用函数300
逻辑函数301
信息函数301
财务函数302
工程函数304
加载宏和自动化函数305
日期和时间函数305
数据库函数306
看了excel 2007公式与函数商务应用还看了:
浏览量:2
下载量:0
时间:
在商务办公中,人们都为了提高工作效率而用到Excel里的公式与函数进行数据的快速计算,公式与函数学习起来也很容易,下面是由读文网小编介绍的一本excel2007公式与函数实例详解书,相信阅读后你的工作效率会变得更高。
第1章公式与函数的应用
1.1公式应用基础知识2
实例001查看工作表中的所有公式2
实例002公式记忆式键入4
实例003审核公式6
实例004使用名称进行计算9
1.2函数应用基础知识13
实例005在公式中输入函数13
实例006利用函数帮助功能学习不会使用的函数18
1.3单元格引用20
实例007相对引用20
实例008绝对引用21
实例009通过混合引用创建乘法表23
实例010引用多张工作表中特定数据源进行计算25
实例011引用多个工作簿数据源进行计算27
实例012在不同工作表中引用区域名称进行计算30
实例013在不同工作簿中引用区域名称进行计算34
1.4数字计算36
实例014快速进行加法计算36
实例015在单元格中进行减法计算38
实例016在单元格中进行乘法计算39
实例017在单元格中进行除法计算40
实例018计算员工的工资41
1.5公式中的错误值43
实例019错误值为“#####”的处理方法43
实例020错误值为“#DIV/0!”的处理方法44
实例021错误值为“#N/A”的处理方法46
实例022错误值为“#NAME?”的处理方法49
实例023错误值为“#VALUE!”的处理方法50
实例024错误值为“#NULL”的处理方法52
第2章逻辑函数的实例应用
2.1逻辑运算函数56
实例025使用AND函数判断员工的销售额是否达标56
实例026使用OR函数判断员工的技能考核是否达标58
实例027使用NOT函数筛选掉性别为“男”的人员60
2.2逻辑判断函数62
实例028使用IF函数判断员工是否完成任务62
实例029使用IF函数将工作表中的“0”替换成“-”64
第3章数学与三角函数的实例应用
3.1数学函数68
实例030使用SUM函数求和68
实例031在不连续的单元格中求和69
实例032使用AVERAGE函数求平均值71
实例033使用SUMIF函数按条件对数字进行求和72
实例034使用PRODUCT函数计算多个数字的乘积74
实例035计算电话的通话费用75
实例036使用FLOOR函数制定产品价格79
实例037使用ROUND函数按位数进行四舍五入81
实例038随机创建3D彩票号码82
实例039通过Excel检查孩子的数学作业84
实例040计算员工工资所需各种面值的人民币张数87
实例041计算员工销售数量的误差值92
3.2三角函数93
实例042计算圆的面积94
实例043用Excel计算特殊角度的正弦、正切、余弦值96
第4章查找与引用函数的实例应用
4.1普通查询102
实例044使用LOOKUP函数查找员工信息102
实例045使用HLOOKUP函数实现水平查找106
实例046使用VLOOKUP函数实现竖直查找109
4.2引用查询112
实例047使用INDEX函数得到指定的内容112
实例048根据位置查询信息115
实例049使用TRANSPOSE函数改变行与列的位置120
实例050使用OFFSET函数建立动态图表数据源124
第5章统计函数的实例应用
5.1一般统计函数130
实例051使用AVERAGEA函数求不连续的平均值130
实例052使用COUNT函数统计每天加班人数132
实例053使用COUNTIF函数按性别统计员工人数135
实例054使用COUNTA函数自动添加员工编号137
实例055统计未完成工作量的总人数140
实例056使用RANK函数对学生成绩排名141
实例057获取计划开始日期和结束日期143
5.2数理统计函数146
实例058使用FREQUENCY函数调查产品的满意度146
实例059使用MODE函数统计哪种商品最受欢迎148
实例060使用CRITBINOM函数检测产品的合格数151
实例061使用GROWTH函数预测全年的销售金额152
实例062分析成本利润154
实例063产品的检验方法160
第6章日期与时间函数的实例应用
6.1日期函数166
实例064使用DATEVALUE函数计算员工请假天数166
实例065显示距2008年北京奥运会开幕所剩的天数167
实例066判断产品是否在保修期限内170
实例067使用NETWORKDAYS函数计算实际工作日173
实例068自动计算员工工龄及休假天数175
实例069在报表中自动填写当前月份179
实例070使用DATEDIF函数计算员工年龄181
实例071计算2000年至2008年哪年是闰年183
实例072计算出差日期对应的星期数186
实例073计算指定日期为一年中的第几周189
6.2时间函数192
实例074计算员工外出使用的时间192
实例075计算员工演讲所用的时间195
第7章文本函数的实例应用
7.1获取特定文本信息202
实例076如何从一个列中分离出分隔号前面的信息202
实例077判断调查表中人员的性别205
实例078使用CONCATENATE函数自动提取序号208
实例079使用REPLACE函数为电话号码进行升级210
实例080使用MID函数自动提取身份证号码中的出生日期211
实例081自动提取身份证号码中的性别214
实例082从一段信息中分离三段信息217
实例083使用SUBSTITUTE函数缩写公司的名称219
7.2获取其他文本信息223
实例084使用LEN函数检查身份证位数是否正确223
实例085用“-”表示员工销售业绩224
实例086使用EXACT函数比较两个字符串是否相同227
第8章财务函数的实例应用
8.1有关本金和利息计算的函数230
实例087使用PMT函数来分析贷款的每期偿还金额230
实例088使用IPMT函数计算贷款中每期还款利息额233
实例089使用PPMT函数计算贷款每期还款本金额237
实例090计算贷款期间所累积的本金额240
实例091计算贷款期间所累积的利息额242
8.2有关投资计算的函数244
实例092使用PV函数计算某项投资的现值244
实例093使用NPV函数计算某项投资的净现值247
实例094使用FV函数计算住房公积金的未来值249
实例095计算某项目的贷款期限251
8.3有关报酬率计算的函数254
实例096计算投资的内部收益率和修正内部收益率254
8.4有关折旧值计算的函数258
实例097使用SLN函数计算固定资产的折旧值258
实例098使用DB函数计算每年的折旧值260
8.5有关证券计算的函数263
实例099使用YIELD函数计算有价证券的收益率264
实例100计算证券成交日和到期日之间的付息次数266
第9章信息函数的实例应用
9.1IS类信息函数270
实例101判断查询的信息是否在单元格内270
实例102判断库存信息是否缺货272
实例103判断员工在考核中是否有缺考274
9.2其他信息函数275
实例104使用CELL函数获得单元格的相关设置信息276
实例105获得当前操作系统的名称和版本号279
第10章数据库函数的实例应用
10.1使用数据库函数统计日常工作282
实例106统计进入录取分数线的人数282
实例107统计基本工资小于800元的员工人数284
实例108统计某个职业的平均工资287
10.2使用数据库函数统计销售业绩289
实例109统计指定销售日期和产品名称对应的销量289
实例110统计销售员一个月对应的销售数量292
实例111统计指定产品的最高利润和最低利润294
实例112统计指定产品的偏差298
实例113统计指定产品的方差302
第11章综合函数实例应用
11.1人事管理308
实例114综合运用函数进行人事管理308
11.2财务管理319
实例115综合运用函数进行财务管理319
11.3销售管理335
实例116综合运用函数进行销售管理335
浏览量:2
下载量:0
时间:
在运用Excel中,不多不少会需要用到函数与公式进行对数据的计算并统计,现在请阅读学习来小编整理的一些excel2007函数与公式使用教程的内容。
excel2007函数与公式使用教程1:AVERAGE 求平均值函数 计算一组数据的平均值数据
选择一个单元,用来显示平均值数据
在fx公式输入栏里边输入:=AVERAGE(D2:D4)
其中D2 D4 为要计算平均的数据范围。如下图所示,回车显示平均值数据
excel2007函数与公式使用教程2:MAX 求最大值函数 计算一组数据的最大值
选择一个单元,用来显示最大值数据
在fx公式输入栏里边输入:=MAX(D2:D4)
其中D2 D4 为要计算平均的数据范围。如下图所示,回车显示最大值数据
excel2007函数与公式使用教程3:MIN 求最小值 计算一组数据的最小值
选择一个单元,用来计算最小值
在fx公式输入栏里边输入:=MIN(D2:D4)
其中D2 D4 为要计算平均的数据范围。如下图所示,回车显示平均值数据
excel2007函数与公式使用教程4:IF IF比较函数 对数据进行比较分组
下面我们来根据每小时的pm25数据,计算pm25等级,就要用到IF函数
在K列中选中K2单元格 点击公式栏目中的Fx 字样,打开公式选择器
选中列表中的IF函数,点击确定
excel2007函数与公式使用教程5:IF IF比较函数
在logical_test 中输入比较条件 F2>200
value_if_ture 输入当f2>200时候显示的结果
value_if_false 输入单f2<=200 时候显示结果
选中K2单元格,使其变成粗黑色边框,
对中右下方的黑点,单击下拉,把公式应用到所有的行中
浏览量:2
下载量:0
时间:
在用excel办公中,用到函数与公式的几率可谓是非常的大,下面是由读文网小编给大家推荐的一本有关excel2007函数与公式实战技巧精粹的书籍,希望对你有用。
附录AExcel常用快捷键
附录BExcel常用函数
附录CExcel的各种规范与限制
附录DExcel简、繁、英文词汇对照表
附录EExcel相关的网站推荐
附录F光盘内容介绍
浏览量:2
下载量:0
时间:
在商务办公中,需要用到的函数实在是太多了,一般人很少能全部掌握,因此需要一本有关Excel函数与公式的查询手册,今天读文网小编给大家推荐的关于excel 2007函数与公式速查手册的一本书,欢迎阅读!
本书包含Excel 2007中所有的函数,为了使初学者快速学会Excel 2007最新版本的重要功能,本书采用新颖、务实的内容和形式指导读者,本书对Excel 2007中每个函数的操作使用都进行了详细地说明,即使是一个不懂Excel的用户也可以实现“零时间”掌握Excel公式和函数使用技巧,书中提供有353个函数讲解,有266个实用案例,有48个技巧,供广大读者借鉴、使用。
本书的知识导航图如下表所示。
表 本书知识导航表
章 名 案例数 技巧数
第1章必学Excel知识 — 1
第2章 日期和时间函数 20 15
第3章逻辑函数 7 4
第4章信息函数 13 4
第5章数学与三角函数 48 —
第6章查找和引用函数 19 13
第7章文本函数 21 8
第8章数据库函数 12 —
第9章多维数据集函数 — —
第10章 外部函数 1 —
第11章统计函数 47 2
第12章财务函数 53 1
第13章 工程函数 25 —
本书适合谁读
本书对Excel 2007中12类函数进行了详细的介绍,进行函数的介绍时,无论是否有Excel基础的用户都可以从本书中找到想要的信息。
这是一本讲解Excel 2007公式与函数的宝典,凡是学习、使用Excel解决实际问题的用户都是本书的读者,读者不但可以查到函数的使用方法,还可以查到与实际问题相关的案例、技巧。
本书独到之处
人性化讲解
内容全面——本书包含Excel 2007中所有的函数,特别对Excel 2007新增的7个多维数据集函数进行了详细的讲解,全书介绍函数的过程中,针对每一个函数都介绍了函数的格式、参数、作用、应用举例、注意点说明、相关函数等6个基本方面进行全面具体的说明,如下图所示。在应用举例部分做到了竭尽全面,包含有关光盘文件的保存位置,列举计算中的正确结果,以及使用函数过程中易出现的错误。
结构清晰——本书函数讲解部分采用统一的函数格式、参数、作用、应用举例、注意点说明、相关函数等6个方面,使全书整体结构更加的清晰。讲解内容也更加清晰,读者查阅函数信息一目了然。
讲解细致——本书全方位地介绍了每一个函数,从函数基本格式出发,包括函数的参数进行了详细的说明,同时将函数与实际例子相结合,甚至将函数在实际使用中会出现的错误提示信息也进行了详细说明,使读者在使用过程中,能快速解决自己在使用函数中出现的错误。
本书在读者阅读体验方面尽量采用人性化讲解方式。结构清楚、步骤清晰,“标注”图中重要步骤,使读者一目了然。同时对所讲内容的扩展和提醒加有“标注”和“提示”。
本书提供的目录索引:
总目录:便于查询任意知识点。
函数索引目录:便于查询各类函数的功能。
案例目录:便于查询函数案例、综合案例。
适用面广
本书不仅适用于Excel 2007,而且对Excel的早期版本都适用,如Excel 2003、Excel 2002、Excel 2000等。
本书的售后服务
专用Excel博客:我们特意开通了专用Excel博客,广大读者可以互相交流、讨论Excel问题。博客版主也会适时对读者的问题进行解答。
注意事项
读者使用本书需要注意以下几点:
1.本书的编写以Microsoft Excel 2007为基础,详细介绍函数的基础知识和应用。
2.初学者使用本书,请认真阅读第1章的内容。
3.本书采用了多种索引的方式,方便读者更快地找到需要查找的函数。
4.Microsoft Excel 2007中新增加了7个多维数据集函数,用户必须在电脑上安装SQL SERVER 2005才可以实现该函数的功能。
浏览量:3
下载量:0
时间:
在Excel中运用到函数与公式的机会是非常多的,如果能熟练掌握函数与公式,那将会大大提高工作效率。下面是读文网小编带来的关于excel 2007函数与公式完美自学百分百的内容,欢迎阅读!
本手册采用“基本操作入门+典型实例进阶+自己动手提高”的全新体系结构,从零开始,通过丰富的实例和翔实的图文讲解,系统并全面地介绍了Excel 2007函数与公式在各方面应用的相关知识,目的是让读者先入门,再提高,然后达到精通应用的水平。
手册配套光盘采用多媒体教学方式和按照一步一步讲解操作步骤来制作实例。全手册共15章,第1章和第2章主要介绍Excel软件应用的一些基本知识,包括安装、数据录入等,以及一些函数公式的基本应用方法;第3章到第14章,详细介绍了函数与公式的应用方法,并附上了大量而又全面的应用实例;第15章是对前面知识的一个总结,以综合实例的方式,对Excel中的函数与公式在实际日常生活和工作方面的应用,作了一个比较详细的介绍。
本手册从Excel的基本应用知识到函数与公式在日常生活中的各种应用都进行详细的介绍,内容丰富,知识点层次分明,结构清晰明了,适合于使用Excel办公的初、中级读者阅读,同时也可作为相关院校及培训机构的教材使用,是学习与掌握Excel函数与公式知识的理想用书。
浏览量:2
下载量:0
时间:
在运用到Excel中,公式与函数是用得最多的,而且公式与函数实在太多,无法全部掌握,因此读文网小编给大家推荐一本关于excel 2010公式与函数辞典606秘技的一本书,欢迎阅读!
对于Excel软件,很多人都使用过它,它是渗透到我们日常工作和生活中的一个具有代表性的软件。但对于Excel软件中的函数内容,大部分读者却知之甚少,或只是一知半解,更不会正确运用函数来处理实际问题。其实函数与公式是Excel中一项非常重要的功能,本书将呈现给读者完整的函数知识,并利用函数实现Excel强大的数据处理和分析功能。
本书版式新颖,以"实例"的形式阐释函数在实际问题中的应用,以"要点"的形式指出各函数使用中的注意事项及使用技巧,并以"小知识"的形式穿插讲解了一些Excel基础知识,同时以"相关函数"的形式列出功能相近的一些函数以进行对比学习,最重要的是以"组合技巧"的形式介绍了许多函数的嵌套使用技巧,将函数本身的功能进行了扩充,实现了单纯一个函数无法完成的功能,使得各函数相得益彰,达到了"1+1>2"的效果。
本书的另一个显著特点是小开本大容量,沿用日版书紧凑的版式,容纳的信息量大,知识点全,可谓是32开的版本格式,16开的信息含量。独特的双色印刷,更是将不同的重点内容用不同颜色加以区分,可以提高读者的阅读兴趣。同时由于本书是辞书类工具书,所以作成便于携带和查阅的小开本,并以附录的形式将各函数按字母顺序进行索引,这些都方便了读者随时随地进行学习。而且附赠一塑封套,可以延长书的使用寿命。
本书共分为12部分,按功能对344个函数进行了彻底解说,包括数学与三角函数、统计函数、日期与时间函数、逻辑函数、查找与引用函数、文本函数、财务函数、信息函数、数据库函数、工程函数和外部函数等11大类函数,但并不是单纯地讲解知识点,而是将函数知识与行政、工程、财务、统计等各个领域中的经典实例结合,使读者不仅能学习到函数的操作方法,而且更能利用函数提高数据处理、分析和管理的能力。同时也包含500多个函数逆向查找技巧。
浏览量:3
下载量:0
时间:
如果需要把Excel2007掌握得更熟练的朋友,只知道一些常用的知识是不够的,现在读文网小编带给大家推荐一本excel 2007应用大全的书籍,希望对你掌握Excel2007有大的帮助。
内容简介
MicrosoftOffice2007是MicrosoftOffice历史上的一次重大转变,所有程序都经过了重新改
Excel2007应用大全写,全新的界面和新的特性集让你感到吃惊。本书正是这样一本带你逐步上手并精通Excel2007的使用手册,涵盖了Excel2007的所有新特性。全书共分为6个部分:第一部分让读者全面领略Excel2007的全新功能区用户界面;第二部分介绍了Excel2007的新特性,如大网格、神奇的表智能、数据透视表、增强的数据筛选和排序、增强的图表处理功能;第三部分介绍了Excel2007与早期版本的衔接问题;第四部分详细讨论Excel2007核心功能——数据计算;第五部分介绍工作表的格式化以及数据共享问题;第六部分介绍其他一些强大的功能,如VBA宏、加载项等。本书在讲解Excel2007各种特性的同时,还全面回顾了Excel2003,有助于使用Excel2003的读者顺利过渡到Excel2007。本书内容全面,讲解透彻,融入了作者实际工作的心得。本书适合各个级别的Excel读者阅读,尤其是从Excel早期版本过渡到Excel2007的读者。对于从事管理工作或者财务工作的读者,也能从作者的实践经验中获益匪浅。
第一篇 Excel基本功能
主要介绍Excel的发展历史、技术背景以及大多数基本功能的使用方法,本篇并非只为初学者准备,中高级用户也能从中找到许多有参考价值的技术细节。
第二篇 使用公式和函数
主要介绍如何创建简单和复杂的公式,如何使用名称以及如何在公式中运用各种函数。本篇不但介绍了常用函数的多个经典用法,还对其他图书少有涉及的数组公式和多维引用计算进行了全面的讲解。
第三篇 图表与图形
主要介绍如何利用图表来表达数字所不能直接传递的信息,以及如何利用图形来增强工作表的效果。
第四篇 使用Excel进行数据分析
主要介绍Excel提供的各项数据分析工具的使用,除了常用的排序、筛选、外部数据查询以外,浓墨重彩地介绍了数据透视表的使用技巧。另外,对于模拟运算表、单变量求解、规划求解以及分析工具库等专业分析工具的使用也进行了大量详细的介绍。
第五篇 使用Excel的高级功能
主要介绍了数据处理高级功能的使用,包括条件格式、数据有效性、分级显示与合并计算等。另外,还特别介绍了语音引擎的使用技巧。
第六篇 使用Excel进行协同
主要介绍Excel在开展协同办公中的各项应用方法,包括充分利用Internet与Intranet进行协同应用、Excel与其他应用程序之间的协同等。
第七篇 Excel自动化
主要介绍利用宏与VBA来进行Excel自动化方面的内容。
附录
主要包括Excel的规范与限制,Excel的快捷键以及Excel术语简繁英对照表。
浏览量:2
下载量:0
时间:
Excel使我们常用的办公软件,但是要熟悉使用这个软件还要记函数公式?Excel中的年龄信息具体该如何进行计算呢?接下来是读文网小编为大家带来的excel中年龄的计算方法_excel年龄计算公式,供大家参考。
一、条件判断:IF函数。
目的:判断成绩所属的等次。
方法:
1、选定目标单元格。
2、在目标单元格中输入公式:=IF(C3>=90,"优秀",IF(C3>=80,"良好",IF(C3>=60,"及格","不及格")))。
3、Ctrl+Enter填充。
解读:
IF函数是条件判断函数,根据判断结果返回对应的值,如果判断条件为TRUE,则返回第一个参数,如果为FALSE,则返回第二个参数。
Excel函数公式:工作中最常用的12个函数公式
二、条件求和:SUMIF、SUMIFS函数。
目的:求男生的总成绩和男生中分数大于等于80分的总成绩。
方法:
1、在对应的目标单元格中输入公式:=SUMIF(D3:D9,"男",C3:C9)或=SUMIFS(C3:C9,C3:C9,">=80",D3:D9,"男")。
解读:
1、SUMIF函数用于单条件求和。暨求和条件只能有一个。易解语法结构为:SUMIF(条件范围,条件,求和范围)。
2、SUMIFS函数用于多条件求和。暨求和条件可以有多个。易解语法结构:SUMIFS(求和范围,条件1范围,条件1,条件2范围,条件2,……条件N范围,条件N)。
Excel函数公式:工作中最常用的12个函数公式
三、条件计数:COUNTIF、COUNTIFS函数。
目的:计算男生的人数或男生中成绩>=80分的人数。
方法:
1、在对应的目标单元格中输入公式:=COUNTIF(D3:D9,"男")或=COUNTIFS(D3:D9,"男",C3:C9,">=80")。
解读:
1、COUNTIF函数用于单条件计数,暨计数条件只能有一个。易解语法结构为:COUNTIF(条件范围,条件).
2、COUNTIFS函数用于多条件计数,暨计数条件可以有多个。易解语法结构为:COUNTIFS(条件范围1,条件1,条件范围2,条件2……条件范围N,条件N)。
Excel函数公式:工作中最常用的12个函数公式
四、数据查询:VLOOKUP函数。
目的:查询相关人员对应的成绩。
方法:
在目标单元格中输入公式:=VLOOKUP(H3,B3:C9,2,0)。
解读:
函数VLOOKUP的基本功能就是数据查询。易解语法结构为:VLOOKUP(查找的值,查找范围,找查找范围中的第几列,精准匹配还是模糊匹配)。
Excel函数公式:工作中最常用的12个函数公式
五、逆向查询:LOOKUP函数。
目的:根据学生姓名查询对应的学号。
方法:
在目标单元格中输入公式:=LOOKUP(1,0/(B3:B9=H3),A3:A9)。
解读:
公式LOOKUP函数的语法结构为:LOOKUP(查找的值,查找的条件,返回值的范围)。本示例中使用的位变异用法。查找的值为1,条件为0。根据LOOKUP函数的特点,如果 LOOKUP 函数找不到 lookup_value,则该函数会与 lookup_vector 中小于或等于 lookup_value 的最大值进行匹配。
Excel函数公式:工作中最常用的12个函数公式
六、查询好搭档:INDEX+MATCH 函数
目的:根据姓名查询对应的等次。
方法:
在目标单元格中输入公式:=INDEX(E3:E9,MATCH(H3,B3:B9,0))。
解读:
1、INDEX函数:返回给定范围内行列交叉处的值。
2、MATCH函数:给出指定值在指定范围内的所在位置。
3、公式:=INDEX(E3:E9,MATCH(H3,B3:B9,0)),查询E3:E9中第MATCH(H3,B3:B9,0)行的值,并返回。
Excel函数公式:工作中最常用的12个函数公式
七、提取出生年月:TEXT+MID函数。
目的:从指定的身份证号码中提取出去年月。
方法:
1、选定目标单元格。
2、输入公式:=TEXT(MID(C3,7,8),"00-00-00")。
3、Ctrl+Enter填充。
解读:
1、利用MID函数从C3单元格中提取从第7个开始,长度为8的字符串。
2、利用TEXT函数将字符的格式转换为“00-00-00”的格式,暨1965-08-21。
Excel函数公式:工作中最常用的12个函数公式
八、计算年龄:DATEDIF函数。
目的:根据给出的身份证号计算出对应的年龄。
方法:
1、选定目标单元格。
2、输入公式:=DATEDIF(TEXT(MID(C3,7,8),"00-00-00"),TODAY(),"y")&"周岁"。
3、Ctrl+Enter填充。
解读:
1、利用MID获取C3单元格中从第7个开始,长度为8的字符串。
2、用Text函数将字符串转换为:00-00-00的格式。暨1965-08-21。
3、利用DATEDIF函数计算出和当前日期(TODAY())的相差年份(y)。
Excel函数公式:工作中最常用的12个函数公式
九、中国式排名:SUMPRODUCT+COUNTIF函数。
目的:对成绩进行排名。
方法:
1、选定目标单元格。
2、在目标单元格中输入公式:=SUMPRODUCT((C$3:C$9>C3)/COUNTIF(C$3:C$9,C$3:C$9))+1。
3、Ctrl+Enter填充。
解读:公式的前半部分(C$3:C$9>C3)返回的是一个数组,区域C$3:C$9中大于C3的单元格个数。后半部分COUNTIF(C$3:C$9,C$3:C$9)可以理解为:X1/COUNTIF(C$3:C$9,C$3:C$9),公式COUNTIF(C$3:C$9,C$3:C$9)返回的值为1,只是用于辅助计算。所以上述公式也可以简化为:=SUMPRODUCT((C$3:C$9>C3)X1)+1。
excel中年龄的计算方法相关
浏览量:2
下载量:0
时间:
Excel中经常需要使用到函数公式计算数据,函数公式具体该如何使用呢?接下来是读文网小编为大家带来的excel函数公式大全使用教程,供大家参考。
公式一使用步骤:AVERAGE 求平均值函数 计算一组数据的平均值数据
选择一个单元,用来显示平均值数据
在fx公式输入栏里边输入:=AVERAGE(D2:D4)
其中D2 D4 为要计算平均的数据范围。如下图所示,回车显示平均值数据
公式二使用步骤:MAX 求最大值函数 计算一组数据的最大值
选择一个单元,用来显示最大值数据
在fx公式输入栏里边输入:=MAX(D2:D4)
其中D2 D4 为要计算平均的数据范围。如下图所示,回车显示最大值数据
公式三使用步骤:MIN 求最小值 计算一组数据的最小值
选择一个单元,用来计算最小值
在fx公式输入栏里边输入:=MIN(D2:D4)
其中D2 D4 为要计算平均的数据范围。如下图所示,回车显示平均值数据
公式四使用步骤:IF IF比较函数 对数据进行比较分组
下面我们来根据每小时的pm25数据,计算pm25等级,就要用到IF函数
在K列中选中K2单元格 点击公式栏目中的Fx 字样,打开公式选择器
选中列表中的IF函数,点击确定
公式五使用步骤:IF IF比较函数
在logical_test 中输入比较条件 F2>200
value_if_ture 输入当f2>200时候显示的结果
value_if_false 输入单f2<=200 时候显示结果
选中K2单元格,使其变成粗黑色边框,
对中右下方的黑点,单击下拉,把公式应用到所有的行中
浏览量:4
下载量:0
时间:
Excel中经常需要使用到函数进行计算数据,函数公式具体该有多少呢?又起着什么作用呢?下面是由读文网小编分享的excel函数公式大全介绍,以供大家阅读和学习。
函数公式介绍1:AVERAGE 求平均值函数 计算一组数据的平均值数据
选择一个单元,用来显示平均值数据
在fx公式输入栏里边输入:=AVERAGE(D2:D4)
其中D2 D4 为要计算平均的数据范围。如下图所示,回车显示平均值数据
函数公式介绍2:MAX 求最大值函数 计算一组数据的最大值
选择一个单元,用来显示最大值数据
在fx公式输入栏里边输入:=MAX(D2:D4)
其中D2 D4 为要计算平均的数据范围。如下图所示,回车显示最大值数据
函数公式介绍3:MIN 求最小值 计算一组数据的最小值
选择一个单元,用来计算最小值
在fx公式输入栏里边输入:=MIN(D2:D4)
其中D2 D4 为要计算平均的数据范围。如下图所示,回车显示平均值数据
函数公式介绍4:IF IF比较函数 对数据进行比较分组
下面我们来根据每小时的pm25数据,计算pm25等级,就要用到IF函数
在K列中选中K2单元格 点击公式栏目中的Fx 字样,打开公式选择器
选中列表中的IF函数,点击确定
函数公式介绍5:IF IF比较函数
在logical_test 中输入比较条件 F2>200
value_if_ture 输入当f2>200时候显示的结果
value_if_false 输入单f2<=200 时候显示结果
选中K2单元格,使其变成粗黑色边框,
对中右下方的黑点,单击下拉,把公式应用到所有的行中
浏览量:4
下载量:0
时间:
Excel中经常需要使用到乘法公式进行计算求积,乘法公式具体该如何进行使用呢?下面是读文网小编带来的关于excel常用函数乘法公式的使用方法,希望阅读过后对你有所启发!
乘法公式使用步骤1:利用乘号“*”来实现两个数值的相乘运算。例如:在单元格A1中输入“30”,B1中输入“10”,在C1单位格中输入公式“=A1*B1”,回车,得到结果“300”。
乘法公式使用步骤2:利用公式“PRODUCT()"来实现若干个数值的乘积运行,该公式的具体格式为"=PRODUCT(X1,X2,X3,......,Xn)",其中n<=30.
PRODUCT函数说明:
将所有以参数形式给出的数字相乘,并返回乘积值。
语法:PRODUCT(number1,number2,...)
Number1, number2, ... 为 1 到 30 个需要相乘的数字参数。
乘法公式使用步骤3:具体方法是:在单位格中输入"=PRODUCT(30,10)"回车即得结果“300”。也可以通过对单元格的引用来实现相乘运算,例如:在单元格A1中输入“30”,B1中输入“10”,在C1单位格中输入公式“=PRODUCT(A1,B1)”,回车,得到结果“300”。
浏览量:5
下载量:0
时间:
Excel中输入好函数公式后经常出现value值的问题,输入好公式出现value值具体该如何解决呢?接下来是读文网小编为大家带来的excel函数公式出现value的解决方法,供大家参考。
出现value解决步骤1:打开几个Excel表格,如图所示输入数据,这是一个建筑面积统计的简单表格。
出现value解决步骤2:接下来我们在“合计”单元格右边单元格输入:
=SUM(B2+B3+B4+B5+B6+B7+B8)
出现value解决步骤3:输入完成后按回车键确定输入,发现该单元格显示:“ #VALUE! ”。
这是Excel中的错误提示。
出现value解决步骤4:是不是我们求和公式用错了呐?接下来换一种求和公式输入方法。
出现value解决步骤5:将刚刚公式中的"B2+B3+B4+B5+B6+B7+B8"替换成单元格区域表示:B2:B8
出现value解决步骤6:也可以将公式中的"B2+B3+B4+B5+B6+B7+B8"删除,然后用鼠标将参与计算的区域拖动选择,这时公式自动识别单元格区域,变成B2:B8。
出现value解决步骤7:回车后,单元格变为0,结果也不对。
浏览量:67
下载量:0
时间: