函数难搞的问题,用透视表轻松解决!

下图左表是数据源,记录了每人多次考试的成绩。要求是查找出至少有2次不及格的姓名,成绩小于80分为不及格。结果如右表所示,第1种方法用的是函数,第2种方法用的是透视表。
-01-

函数法

这个问题用函数来做,很复杂,数据量大了,也很卡。这里只列出公式,不作解释。在F3单元格输入下面的公式,按三键,下拉填充,得到符合条件的姓名。

=INDEX(A:A,SMALL(IF((MATCH(A$3:A$18,A$3:A$18,)=ROW($1:$16))*(COUNTIFS(A$3:A$18,A$3:A$18,C$3:C$18,"<80")>1),ROW($3:$18),4^8),ROW(A1)))&""

也可以在G列验证下不及格的次数,在G3单元格输入公式=COUNTIFS(A$3:A$18,F3,C$3:C$18,"<80"),向下填充。可以看到结果都是至少大于2次的。

-02-

透视表法

下面主要来说下透视表的做法,用透视表解决这个问题真的很简单。主要是快。

1)在D列加个辅助列,在D3单元格输入公式=N(C3<80),向下填充。结果如下图所示,可以看到小于80分的结果为1,否则为0。
2)选中数据源中任意一个单元格(如B9),单击【插入】-【数据透视表】,打开创建透视表的对话框,数据源区域选A2:D18,放置透视表的位置选【现有工作表】的F8单元格,点击【确定】。
3)在F8单元格创建了一个空白的透视表,将【姓名】拖到行标签,【不及格次数】拖到值标签中。右键单击【总计】,在弹出的菜单中,单击【删除总计】。

4)单击行标签的下拉按钮,在弹出的菜单中选【值筛选】-【大于】。在弹出的【值筛选(姓名)】中,【求和项:不及格次数】大于1,点击【确定】。这样就筛选出符合条件的姓名了。

5)最后把透视表美化下,把字段标题改下就完成了。如下图所示。用透视表做不到1分钟就搞定了。
(0)

相关推荐