http://www.pptjcw.com

wps云文档使用教程:excel表格怎么指定成绩剔除?

    书接上回,开启进阶!

    还是用昨天同一组示例数据,稍微变一下表现形式:

    wps云文档使用教程:excel表格怎么指定成绩剔除?

    只不过,今天不用辅助列了,直接用一个公式,剔除指定姓名,写出保留姓名与成绩。

    公式实现

    在F2单元格输入公式:

    =IFERROR(INDEX(A$2:A$10,SMALL(IF(COUNTIF($D$2:$D$6,$A$2:$A$10),””,ROW($1:$9)),ROW(A1))),””),Ctrl+Shift+Enter结束公式输入。

    公式向下、向右填充,会得到剔除指定姓名以后,保留的姓名及对应成绩。

    如图:

    wps云文档使用教程:excel表格怎么指定成绩剔除?

    公式解析

    COUNTIF($D$2:$D$6,$A$2:$A$10)

    从D2:D5指定的要剔除的姓名区域,依次查找A1:A9所有姓名,查得到的返回1,查不到的返回0。所以此部分公式返回由1与0组成的数组:

    {0;1;1;0;1;1;0;1;0 }

    ROW($1:$9)

    返回由1—9组成的数组:

    {1;2;3;4;5;6;7;8;9}

    IF(COUNTIF($D$2:$D$6,$A$2:$A$10),””,ROW($1:$9))

    此部分公式原理用下图解释:

    wps云文档使用教程:excel表格怎么指定成绩剔除?

    所以,本部分IF函数的返回值是数组:

    {1;””;””;4;””;””;7;””;9}

    SMALL(IF(COUNTIF($D$2:$D$6,$A$2:$A$10),””,ROW($1:$9)),ROW(A1))

    ROW(A1)的返回值是1,所以,此部分是返回数组{1;””;””;4;””;””;7;””;9}的最小值,即1;公式每往下填充一行,ROW(A1)变为ROW(A2),ROW(A3),ROW(A4)……,此部分SMALL公式就会返回数组{1;””;””;4;””;””;7;””;9}的第2、3、4……小的数值,即4,7,9……;

    INDEX(A$2:A$10,SMALL(IF(COUNTIF($D$2:$D$6,$A$2:$A$10),””,ROW($1:$9)),ROW(A1)))

    公式在F2中,即是返回A2:A10区域中第1位的数据A1;公式往下填充,即返回A2:A10区域中第4、7、9位的数据A4、A7、A9,即得到了剔除指定姓名以后保留的姓名;

    IFERROR(INDEX(A$2:A$10,SMALL(IF(COUNTIF($D$2:$D$6,$A$2:$A$10),””,ROW($1:$9)),ROW(A1))),””)

    用IFERROR,如果查找到结果就返回值,如果查不到,出现错误,则返回空值。

    保留成绩的计算:

    因为公式中的A$2:A$10,使用的混合引用,A列没有锁定,所以,向右填充,会自从变为B$2:B$10,也就是保留的B列的成绩。

    提示:如果您觉得本文不错,请点击分享给您的好友!谢谢

    上一篇:wps云文档使用教程:Excel怎么提取销售量最大的月份 下一篇:wps演示背景图片怎么设置:excel表格怎么计算到期日的?

    郑重声明:本文版权归原作者所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们修改或删除,多谢。