发布时间 : 星期一 文章Excel2007函数公式收集了688个实例更新完毕开始阅读0944df5b650e52ea5518984c
重组人事资料表:
=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({\华东区\华南区\华北区\华中区\西南区\西南