比如下面的表一中A列是学生名字,B列是他评优的次数。我现在不直接用高级筛选或自动筛选,而是要用公式实现,把评优的次数>0的“学生名字” 自动在另一个表二的A列中一一排列出来,“评优次数”自动在另一个表二的B列中按降序一一排列出来,(此案例中满足条件的就只有三人王小、刘四、李明,这三个人的名单要按评优次数降序自动生成到表二中)。请问我怎么实现?
表一:
A B
名字 评优次数
李明 1
王小 3
张三 0
刘四 2
陈大 0
表二:
名字 评优次数
王小 3
刘四 2
李明 1
注::我不希望产生不符合条件的空行,最后生成的表二,就只有符合条件的那几个人一一按降序排列下来,不会生成空行。。
如果一行行去用IF条件函数去筛选,那么,学生有多少名就会生成多少行。不满足条件的,就会是空行,还需要去删除空行,这样非常不整洁,占行数又多。不知道大家明白我的意思么??我只要满足条件的人就在表二中自动生成另一个表,并且按降序排列。不满足条件的,不会在表二中生成空行。
请各位高手,帮我直接写出公式,谢谢~~
1、打开所需表格,首先来操作一下一个条件的。
2、首先选中表格整个区域,在这里要注意的一个问题就是要选中以行为单位的整个表格,而不是单个选中供应商一列,因为要标记的是一整行。
3、接下来在样式选项卡下找到,条件格式选项,在条件格式下拉菜单中选择新建规则,并打开新建规则对话框。
4、在新建规则对话框中,规则类型选择最后一个“使用公式来确定要设置格式的单元格”。
5、选中使用公式规则类型以后,在编辑规则说明下面输入公式(=$C2="李丽")。在这里公式输入要注意两个问题:一,输入符号的时候要在英文状态下输入。二,公式中“$”绝对值符号只是C列是绝对的,但是行不是绝对的$C$2,要注意2前面是没有“$”符号的。
6、设置完成以后点击确定在表格中就可以实现。并且在表格中继续输入数据,如果输入的数据满足公式条件,表格也会自动标记。
需要多个函数来组合实现此功能,组合后的公式如下:
=IFERROR(INDEX($A$2:$A$17,LARGE(IF($A$2:$A$17>10,ROW($A$2:$A$17),""),ROW(2:2))),""),数组函数,三键结束。
公式详解:
我们首先用if函数来做条件判断,举例中源数据区域位于A2:A17,假定条件为">10"。
用IF来判断如果源数据满足条件则返回其行号,如不满足则返回空值。
通过第一步,我们可以得到一个由行号及“”(空值)组成的数组,然后运用LARGE函数将数组按照从大到小排序,及行号从大到小排列。
运用INDEX函数来根据第二步排列好的行号依次返回结果。
第二步得出的数组中的空值对应的INDEX结果是#NUM!, 需要用IFERROR函数来进行容错,即如果得到错误值则强制转化为空值。
注:此题要求从大到小比较特殊,所以用到了LARGE函数,通常情况下是按照原排列顺序返回值,这时候我们要用SMALL函数来代替LARGE函数,即IFERROR+INDEX+SMALL+IF组合。
不好意思,你的这种方法是对的,我按你的方法把三列的公式都弄成这样就弄好了,谢谢你!
追答如果搞定了还要麻烦采纳一下答案 ^_^
本回答被提问者采纳谢谢,你的公式完全可以。但是如果我增加学号一列,也要把学号一列在表二中显示出来,怎么办呢?我刚试了,如果只是这两列,你给出的公式完全可以了。但是我增加了学号一列,就不知道怎么弄了。学号那一列要写什么公式呢。
追答C2输入=VLOOKUP(B2,Sheet1!$B$2:$C$6,2,0)
追问55555555555,谢谢。如果把学号放在C列,用你给的公式是可以的。可是我要把A列是学号,B列是名字,C列是评优次数。怎么写公式呢。5555555不好意思,问了你这么多次。麻烦你了
追答A2公式=VLOOKUP(C2,IF({1,0},Sheet1!$C$2:$C$6,Sheet1!$A$2:$A$6),2,0)
B2公式=VLOOKUP(C2,IF({1,0},Sheet1!$C$2:$C$6,Sheet1!$B$2:$B$7),2,0)
C2公式=IF(ISERROR(LARGE(IF(Sheet1!$C$2:$C$60,Sheet1!$C$2:$C$6),ROW(B1))),"",LARGE(IF(Sheet1!$C$2:$C$60,Sheet1!$C$2:$C$6),ROW(B1)))按那三个键
啊,公式是可以,但是现在又出现一个问题,因为学生量大,行数多, 有一些学生评优次数是相同的,结果那些相同评优次数的学生,全都自动生成是同一个人,比如评优1次的学生有10个人,结果表二中就显示10行都是李明,10行显示同一个人,其他9个评优1次的人没有显示,要怎么样解决这个问题呢?
追答A2公式=INDEX(Sheet1!$A$2:$A$13,LARGE(IF(C2=Sheet1!$C$2:$C$13,ROW(Sheet1!$C$2:$C$13)),COUNTIF($C$2:C2,C2))-1)
B2公式=INDEX(Sheet1!$B$2:$B$13,LARGE(IF(C2=Sheet1!$C$2:$C$13,ROW(Sheet1!$C$2:$C$13)),COUNTIF($C$2:C2,C2))-1)
C2公式=IF(ISERROR(LARGE(IF(Sheet1!$C$2:$C$130,Sheet1!$C$2:$C$13),ROW(B1))),"",LARGE(IF(Sheet1!$C$2:$C$130,Sheet1!$C$2:$C$13),ROW(B1)))
全是按三个键结束,和之前的函数区域有改动,改成A2:C13区域了