首页| 新闻| 娱乐| 游戏| 科普| 文学| 编程| 系统| 数据库| 建站| 学院| 产品| 网管| 维修| 办公| 热点
图1 原始成绩表 原始的成绩表如图1所示。姓名位于C2:C92单元格，语文成绩位于D2:D92单元格区域。我们就以查找语文学科的前10名成绩及学生姓名为例。为方便比较结果，图1中我们已经将数据按语文成绩降序进行了排序，实际操作中是不需要事先排序的。 一、名次表的建立 前面我们说过，我们不太容易确定排在前10名的学生共有多少，所以，我们需要使用公式将它们找出来。当然，最好顺便将名次表填写出来。完成结果如图2所示。 图2 成绩排序 将鼠标定位于X3单元格，然后在编辑栏输入公式“=TEXT(SUMPRODUCT(($D$2:$D$92>=LARGE($D$2:$D$92,ROW(1:1)))/COUNTIF($D$2:$D$92,$D$2:$D$92)),"第G/通用格式名")”，回车后就可以得到“第1名”的结果。选定X3单元格，向下拖动其填充句柄至出现“第11名”为止。 这里用到了几个函数，感觉上比较复杂。其实思路是这样的：“ROW(1:1)”的结果是“1”，而“LARGE($D$2:$D$92,1)”的结果是在指定的单元格区域中最大的一个数；那么公式中“($D$2:$D$92>=LARGE($D$2:$D$92,ROW(1:1)))”可以理解为拿D2:D92单元格区域中的数据与该区域中最大值比较，大于或等于该值及小于该值的则会分别以“TRUE”、“FALSE”的结果保存在一个数组中。 公式中“COUNTIF($D$2:$D$92,$D$2:$D$92))”部分则会统计D2:D92单元格区域中每一个数值出现的次数，也分别保存到一个数组中。所以，我们所用公式中“SUMPRODUCT(($D$2:$D$92>=LARGE($D$2:$D$92,ROW(1:1)))/COUNTIF($D$2:$D$92,$D$2:$D$92))”在执行时会得到一个类似于“SUMPRODUCT({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;…}/{1;1;2;2;1;2;2;1;2;2;2;2;1;…})”的结果。两个数组中的对应的数据分别相除，再将所有的商相加，正是分数所对应的名次。这种方法即使名次是并列的，也不会影响显示效果。 至于最外层的TEXT函数，则是将得到的结果转换为按指定数字格式表示的文本。也就是本来内层公式运算的结果是数字“1”，现在我们将它显示为“第1名”。 二、分数的查找 将鼠标定位于Y3单元格，在编辑栏中输入如下公式“=INDEX($D$2:$D$92,MATCH(LARGE($D$2:$D$92 1/ROW($D$2:$D$92),ROW(1:1)),$D$2:$D$92 1/ROW($D$2:$D$92),0))”，然后按下“Ctrl Shift Enter”快捷键，完成数组公式的输入。这一步很关键的，否则不会出现正确的结果。 向下拖动Y3单元格的填充句柄向下至最后一个单元格完成公式的复制。 我们还是简单解释一下公式的思路。 由于D2:D92区域中有很多数据是重复的，这给我们造成了困难。所以，我们要想办法使每一数据都变成唯一。公式中“$D$2:$D$92 1/ROW($D$2:$D$92)”就是给D2:D92区域中每一个数据都加了该数据对应行数的倒数。由于每一数据对应的行数是不一样的，这样，就会使每一数据都变成了唯一的值，并保存到了一个数组中。 公式中的“LARGE($D$2:$D$92 1/ROW($D$2:$D$92),ROW(1:1))”还是返回了上面所得数组中的最大值。本例中的结果是“｛96.5｝”。 公式中“MATCH(LARGE($D$2:$D$92 1/ROW($D$2:$D$92),ROW(1:1)),$D$2:$D$92 1/ROW($D$2:$D$92),0)”返回的是刚刚得到的最大值在数组中的位置。本例中的结果是“｛1｝”。 这样，其实Excel最后执行的查询就是“INDEX($D$2:$D$92,1)”了，自然可以返回在$D$2:$D$92区域中的第一个值了。 三、姓名的查找 将鼠标定位于Z3单元格，在编辑栏中输入公式“=INDEX($C$2:$C$92,MATCH(LARGE($D$2:$D$92 1/ROW($D$2:$D$92),ROW(1:1)),$D$2:$D$92 1/ROW($D$2:$D$92),0))”，同样按下“Ctrl Shift Enter”快捷键完成数组公式的输入。 向下拖动Z3单元格的填充句柄向下至最后一个单元格完成公式的复制。最后的效果如图3所示。 图3 完成公式的复制 其实您肯定已经明白了，姓名的查找与前面分数的查找是一样的。公式本身也没有什么大的变化。所以，明白了前面的方法，要查找别的什么东西也就方便了。 其它的学科可以照此办理。只要注意变换一下公式中的单元格区域就可以了，我这里就不罗嗦了。
图2 成绩排序 将鼠标定位于X3单元格，然后在编辑栏输入公式“=TEXT(SUMPRODUCT(($D$2:$D$92>=LARGE($D$2:$D$92,ROW(1:1)))/COUNTIF($D$2:$D$92,$D$2:$D$92)),"第G/通用格式名")”，回车后就可以得到“第1名”的结果。选定X3单元格，向下拖动其填充句柄至出现“第11名”为止。 这里用到了几个函数，感觉上比较复杂。其实思路是这样的：“ROW(1:1)”的结果是“1”，而“LARGE($D$2:$D$92,1)”的结果是在指定的单元格区域中最大的一个数；那么公式中“($D$2:$D$92>=LARGE($D$2:$D$92,ROW(1:1)))”可以理解为拿D2:D92单元格区域中的数据与该区域中最大值比较，大于或等于该值及小于该值的则会分别以“TRUE”、“FALSE”的结果保存在一个数组中。 公式中“COUNTIF($D$2:$D$92,$D$2:$D$92))”部分则会统计D2:D92单元格区域中每一个数值出现的次数，也分别保存到一个数组中。所以，我们所用公式中“SUMPRODUCT(($D$2:$D$92>=LARGE($D$2:$D$92,ROW(1:1)))/COUNTIF($D$2:$D$92,$D$2:$D$92))”在执行时会得到一个类似于“SUMPRODUCT({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;…}/{1;1;2;2;1;2;2;1;2;2;2;2;1;…})”的结果。两个数组中的对应的数据分别相除，再将所有的商相加，正是分数所对应的名次。这种方法即使名次是并列的，也不会影响显示效果。 至于最外层的TEXT函数，则是将得到的结果转换为按指定数字格式表示的文本。也就是本来内层公式运算的结果是数字“1”，现在我们将它显示为“第1名”。 二、分数的查找 将鼠标定位于Y3单元格，在编辑栏中输入如下公式“=INDEX($D$2:$D$92,MATCH(LARGE($D$2:$D$92 1/ROW($D$2:$D$92),ROW(1:1)),$D$2:$D$92 1/ROW($D$2:$D$92),0))”，然后按下“Ctrl Shift Enter”快捷键，完成数组公式的输入。这一步很关键的，否则不会出现正确的结果。 向下拖动Y3单元格的填充句柄向下至最后一个单元格完成公式的复制。 我们还是简单解释一下公式的思路。 由于D2:D92区域中有很多数据是重复的，这给我们造成了困难。所以，我们要想办法使每一数据都变成唯一。公式中“$D$2:$D$92 1/ROW($D$2:$D$92)”就是给D2:D92区域中每一个数据都加了该数据对应行数的倒数。由于每一数据对应的行数是不一样的，这样，就会使每一数据都变成了唯一的值，并保存到了一个数组中。 公式中的“LARGE($D$2:$D$92 1/ROW($D$2:$D$92),ROW(1:1))”还是返回了上面所得数组中的最大值。本例中的结果是“｛96.5｝”。 公式中“MATCH(LARGE($D$2:$D$92 1/ROW($D$2:$D$92),ROW(1:1)),$D$2:$D$92 1/ROW($D$2:$D$92),0)”返回的是刚刚得到的最大值在数组中的位置。本例中的结果是“｛1｝”。 这样，其实Excel最后执行的查询就是“INDEX($D$2:$D$92,1)”了，自然可以返回在$D$2:$D$92区域中的第一个值了。 三、姓名的查找 将鼠标定位于Z3单元格，在编辑栏中输入公式“=INDEX($C$2:$C$92,MATCH(LARGE($D$2:$D$92 1/ROW($D$2:$D$92),ROW(1:1)),$D$2:$D$92 1/ROW($D$2:$D$92),0))”，同样按下“Ctrl Shift Enter”快捷键完成数组公式的输入。 向下拖动Z3单元格的填充句柄向下至最后一个单元格完成公式的复制。最后的效果如图3所示。 图3 完成公式的复制 其实您肯定已经明白了，姓名的查找与前面分数的查找是一样的。公式本身也没有什么大的变化。所以，明白了前面的方法，要查找别的什么东西也就方便了。 其它的学科可以照此办理。只要注意变换一下公式中的单元格区域就可以了，我这里就不罗嗦了。
图3 完成公式的复制 其实您肯定已经明白了，姓名的查找与前面分数的查找是一样的。公式本身也没有什么大的变化。所以，明白了前面的方法，要查找别的什么东西也就方便了。 其它的学科可以照此办理。只要注意变换一下公式中的单元格区域就可以了，我这里就不罗嗦了。
索泰发布一款GTX 1070 Mini迷
AMD新旗舰显卡轻松干翻NVIDIA
索泰发布一款GTX 1070 Mini迷你版本:小机
芭蕾舞蹈表演，真实美到极致
下午茶时间，悠然自得的休憩
充斥这繁华奢靡气息的城市迪拜风景图片
从山间到田野再到大海美丽的自然风景图片
肉食主义者的最爱美食烤肉图片
夏日甜心草莓美食图片
人逢知己千杯少，喝酒搞笑图集
搞笑试卷，学生恶搞答题
新闻热点
疑难解答
图片精选
Excel2013如何添加打钩方框 Excel2
Excel2013如何批量插入图片 Excel2
excel2013中如何插入控件图文教程
excel2013中如何制作组合图表 exce
网友关注
