返回目录:excel表格制作
求出成绩最高的姓名
如果某科只有一个最高分,那就好办,现在涉及到相同成绩,要列所有的姓名
单求英语
解
题公式
在空单元格E1输入下列公式
=INDEX(A:A,SMALL(IF(B:B=MAX(B:B),ROW(B:B),COUNTA(B:B)+1),ROW(A1)))&""
按CTRL+SHIFT+ENTER数据运算
向下拖动填充,便可以得到英语成绩较高的学生
(主要思路是:
1、找到B列最大的数如92
2、让B列的数据跟最大的数比较,如果等于,返回他的行号,如果不等于,返回一个比较大的值,这里选用了比B列非空数据的值大1),(1,7,3,7,7)
3、让SMALL函数选出数组中第1,第2小的值,返回的就是上述的行号和那个比较大的值(1,3,7,7,7)
4、用INDEX引用A列对应行号的值,如果是最大值就引用出来了,如果不是最大的值,就会引用到B列数据下方空白的地方)(吕布、韩信)
现在这个不是问题,如果使用数据有效性创建了一级下拉菜单
如何G列输入公式,使得当F列更改科目时,G列得到结果?
效果如下:
解
题思路
如果选中的英语,则上述公式中为B:B
如果选中的是数学,则上述公司中的B:B改成C:C即可
如果选中的语文,则B:B改成D:D即可
那么我们可以定义一个名称区域来替换B;B即可
新建一个名称,命名为数据源,使用OFFSET函数定义数据区域
从A1出发,向下偏移0,向右偏移match匹配到的列数减1,选取所有行,选取1列,即得到区域范围
=OFFSET(Sheet1!$A$1,0,MATCH(Sheet1!$F$1,Sheet1!$A$1:$D$1,0)-1,COUNTBLANK(Sheet1!$O:$O),1)
或者向B1出发,MATCH函数后面不用减1
将上述公式的B:B,改成数据源,最后在G列输入:
=INDEX(A:A,SMALL(IF(数据源=MAX(数据源),ROW(数据源),COUNTA(数据源)+1),ROW(A1)))&""
用数组公式,向下填充,即可得到想要的结果。