在 Excel 中有一个各班的成就表,有教员想要从成就总表摘要出各班总分最高分且数学年夜于或即是90分者,该若何处置?(本例假定总分无同分者)
【準备工作】
拔取贮存格A1:G25,按 Ctrl+Shift+F3 键,勾选「顶端列」,成立名称:班级、座号、姓名、国文、英文、数学、总分。
【公式设计与解析】
为了申明便利,出格成立了一个辅助栏位(M栏)。
贮存格M2:
{=SUMPRODUCT((班级=I2)*(总分=MAX(IF((班级=I2),总分)))*(数学>=90),
ROW(班级))}
这是阵列公式,输入完成要按 Ctrl+Shift+Enter 键,Excel 会主动加上{}。
MAX(IF((班级=I2),总分)):在阵列公式中找出班级名称和贮存格I2不异者的阵列,其总分最年夜值者。
在 SUMPRODUCT 函数中找出合适以下三个前提者的乘积和:
(1) 班级=I2:找出班级阵列中和贮存格I2不异者。北京拓展公司
(2) 总分=MAX(IF((班级=I2),总分)):找出总分阵列中和「合适班级阵列为贮存格I2者的最年夜值」不异者。
(3) 数学>=90:找出数学阵列中年夜于或即是90者。
贮存格J2:=IF($M2=0,"",OFFSET(INDIRECT(J$1),$M2-2,0,1,1))
INDIRECT(J$1):将贮存格J1的文字藉由 INDIRECT 函数转换为真实贮存格範围(已界说名称)。
再由 OFFSET 函数显示对应位址的贮存格内容。若是贮存格M2的内容为0,暗示没有合适的项目。
贮存格K2:=IF($M2=0,"",OFFSET(INDIRECT(K$1),$M2-2,0,1,1))
贮存格L2:=IF($M2=0,"",OFFSET(INDIRECT(L$1),$M2-2,0,1,1))
複製贮存格J2:M2,贴至贮存格J2:M7。
创作者先容 vincent- 全站分类:不设分类
- 小我分类:课本资料
- 此分类上一篇: Excel-藉由界说名称转换表格来摘要资料(阵列公式,OFFSET,INDIRECT)
- 此分类下一篇: Excel-按照年和月来主动发生该月的日期和礼拜几(DATE,TEXT,ROW)
- 上一篇: Excel-藉由界说名称转换表格来摘要资料(阵列公式,OFFSET,INDIRECT)
- 下一篇: Excel-按照年和月来主动发生该月的日期和礼拜几(DATE,TEXT,ROW)
汗青上的今天
- 2015: Excel-按照年和月来主动发生该月的日期和礼拜几(DATE,TEXT,ROW)
- 2014: Excel-将「分钟数」转换为暗示为「时:分」(INT,MOD,TEXT)
- 2014: Excel-限制贮存格输入的格局(资料验证)
- 2012: Excel-让个位数无前提捨去或进位
- 2010: Windows 7-为电脑发生健检陈述
- 2010: Windows 7-利用「履行」功能
- 2010: Windows 7-设定本身的关机按钮
- 2009: Google的新闻Fast Flip(快翻)功能