多条件查找公式

ex1753

准提部林 发表于 2017-10-16 10:07
〔名次１〕这个公式好，不需要依赖〔序号〕E列，E列也不是重要参数，依赖越少，出错可能性越小  

改的 ...

老师讨扰了，麻烦在帮我写几个公式

1、Sheet1，把I列的名次在J列转换成分数，要求在绿色区域。哎，郁闷，好好的一个统计表，硬是给一个“重名”搞成一塌糊涂。

2、Sheet2，的【名次】列（E、K、Q……）写个条件格式，K2=E2则K2显示红色，才疏学浅，我写的条件碰到前后都是空格也会显示红色，怎么让空格不变色，目的：快速查找某一个项目有重名。

3、Sheet2，E列以后的公式用了几个IF，显示很臃肿，请帮我简化一下，

准提部林

ex1753 发表于 2017-10-16 18:23
老师讨扰了，麻烦在帮我写几个公式

1、Sheet1，把I列的名次在J列转换成分数，要求在绿色区域。哎，郁 ...

計分．Ｊ２．數組公式
=IF(H2="","",AVERAGE(SUMIF(M$2:M$9,TEXT(I2-1+ROW(INDIRECT("1:"&COUNTIF(OFFSET(I$1,MATCH(D2,D:D,)-1,,COUNTIF(D:D,D2)),I2))),"[>8]8"),N$2:N$9)))

我是2000版，這公式已超出圈數，不確定是否可用！
　
Sheet2，我的版本無法處理，可另尋熱心高手協助！
　
　

ex1753

准提部林 发表于 2017-10-16 21:19
計分．Ｊ２．數組公式
=IF(H2="","",AVERAGE(SUMIF(M$2:M$9,TEXT(I2-1+ROW(INDIRECT("1:"&COUNTIF(OFFSE ...

谢谢老师，这个公式对第8名重名，计算错误

还是感谢老师，遇到重名手动调整一下，一届运动会也没几个重名的

准提部林

ex1753 发表于 2017-10-16 21:48
谢谢老师，这个公式对第8名重名，计算错误

还是感谢老师，遇到重名手动调整一下，一届运动会也没几个 ...

=IF(H2="","",AVERAGE(SUMIF(M$2:M$9,I2-1+ROW(INDIRECT("1:"&COUNTIF(OFFSET(I$1,MATCH(D2,D:D,)-1,,COUNTIF(D:D,D2)),I2))),N$2:N$9)))

准提部林

不用參照表：數組公式
=IF(H2="","",AVERAGE(9-TEXT(I2-1+ROW(INDIRECT("1:"&COUNTIF(OFFSET(I$1,MATCH(D2,D:D,)-1,,COUNTIF(D:D,D2)),I2))),"[=1]!0;[>8]9")))
　
TEXT很慢～～
　
　

ex1753

准提部林 发表于 2017-10-16 23:30
不用參照表：數組公式
=IF(H2="","",AVERAGE(9-TEXT(I2-1+ROW(INDIRECT("1:"&COUNTIF(OFFSET(I$1,MATCH(D2 ...

哎，我自己也写了一个公式，也是电子表又卡起来了，郁闷

=SUM(OFFSET($N$1,MATCH(I2,$M$2:$M$9,),,COUNTIFS(D:D,D2,I:I,I2)))/COUNTIFS(D:D,D2,I:I,I2)