Excel2007函数公式收集了688个实例

重组人事资料表:

=REPLACE(INDIRECT(\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(\($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\二年级!A\三年级!A\

多区域计数:

=SUM(COUNTIF(INDIRECT({\求积、求和两相宜:

=SUM(IF(C2=\OW($C$2:C2))&\

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

按卡号中的英文及数值排序:

{=INDIRECT(\2,9)*100+ROW($2:$11),ROW(B1)),100))}

多行多列取唯一值:

{=IF(OR((B$2:D$5<>\(MIN(IF((B$2:D$5<>\COLUMN(B:D))),\

罗列三个表中的最大值:{=SUBTOTAL(4,INDIRECT({\组\组\组\

将三列课程转换成单列且忽略空值:

{=INDIRECT(TEXT(SMALL(IF($B$2:$D$7<>\76001),ROW(A1)),\

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

{=IFERROR(SMALL(IF((MOD(A$2,ROW(INDIRECT(\))=0)*(MOD(B$2,ROW(INDIRECT(\

\

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&\MAX(INDIRECT(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<>\计算10届运动会中有几次破纪录:

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

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

=SUMPRODUCT(N(SUBTOTAL(9,OFFSET($B$1,ROW(1:10)-1,,3))>=25)) 进、出库合计查询:=SUM(OFFSET(A1,E2,MATCH(G2&\总计\

根据人数自动调整表格大小:

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

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

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

{=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),\11)/2))}

罗列两次未打卡人员:

{=IFERROR(OFFSET(A$1,LARGE((COUNTIF(OFFSET(A$1,ROW($2:$11)-1,1,,4),\\

计算语文、英语、化学、政治哪科总分最高:

=CHOOSE(MATCH(MAX(SUBTOTAL(9,OFFSET(A1,1,MATCH({\语文\英语\化学\政治\语文\英语\化学\政治\语文\英语\化学\政治\

连续三届达到100的次数:

=SUMPRODUCT(N(COUNTIF(OFFSET(B1,ROW(2:9)-1,,3,1),\

罗列及格率最高的学生姓名:

{=INDEX(A:A,SMALL(IF(MAX(COUNTIF(OFFSET(A$1,ROW($2:$11)-1,1,1,COLUMNS(B:G)),\MNS(B:G)),\

计算Excel类图书最多进货量及书名:

{=MAX(SUMIF(OFFSET(B1,ROW(2:11)-1,1,1,6),\)}

计算Excel类图书进货最多的是哪一个月:

{=INDEX(C1:H1,MATCH(MAX(SUMIF(B2:B11,\MN(C:H)-3,ROWS(2:11),1))),SUMIF(B2:B11,\-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),\季度\

分栏打印:=IF(ROW()=1,CHOOSE(MOD(COLUMN()-1,3)+1,资料!$A$1,资料!$B$1,\资料!$A$1,INT(COLUMN()/3)*9+ROW()-1,MOD(COLUMN(),3)-1,)))

分类汇总:

=IF(SUMIF(B$2:B$11,E2,C$2:C$11)=0,\分类汇总并排序:

{=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,{\一班\二班\三班\

计算每个学生总分是否高于本班平均成绩:

{=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(\3,0),))}

多表汇总金额:{=SUM(SUBTOTAL(6,OFFSET(INDIRECT({\华南区\华东区\华北区\

从单价表引用单价并汇总金额:

{=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(\6))),,,-3,1))}

重组培训科目表: 姓名

=LOOKUP(ROW()-1,COUNTIF(OFFSET(B$1:G$1,,,ROW($1:$7)),\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<>\累计\合计\累计\

计算10个月中的销售利润并排名:{=OFFSET(A1,MOD(LARGE(INT(MMULT(SUBTOTAL(6,OFFSET(INDIRECT({\华东区\华南区\华北区\华中区\西南区\-1,)}

计算五个地区销售利润:{=TRANSPOSE(MMULT({1,1,1,1,1,1,1,1,1,1},SUBTOTAL(6,OFFSET(INDIRECT({\华东区\华南区\华北区\华中区\西南区\西南