如何将指定列（有空行，有公式）的数据按指定条件有序排列后并算出这些数据的均值

殷圣

排序的列中不能出现0,必须从第二行依次往下排序



出来砸到，求大神指点！万份感谢

E学子

1. =LET(x,SORT(FILTER(C2:C1000,(C2:C1000<>"")*(D2:D1000=""))),y,COUNT(x),AVERAGE(DROP(DROP(x,ROUND(0.2*y,0)),-ROUND(0.15*y,0))))

复制代码

示例中E列是否有误？缺少了一个210000，图中红色数值。
数据行数给到了1000行，如果不够用的话可自行修改。
得出的最终平均值未做小数位限定，需要的限定的话，根据实际需求（向上/向下舍入还是四舍五入），自己外套ROUND系列函数即可。

计划小文

=DROP(DROP(SORT(UNIQUE(IF(D2:D100="",C2:C100,""),FALSE,FALSE)),1+ROUND(COUNT(DROP(DROP(SORT(UNIQUE(IF(D2:D100="",C2:C100,""),FALSE,FALSE)),1),-1))*0.2,0)),-1-ROUND(COUNT(DROP(DROP(SORT(UNIQUE(IF(D2:D100="",C2:C100,""),FALSE,FALSE)),1),-1))*0.15,0))

inrever

1. =LET(a,IFERROR(UNIQUE(SORT(IF(D2:D19="",1,"")*C2:C19,,1)),""),LET(b,FILTER(a,a>INDEX(a,ROUND(COUNT(a)*20%,))),FILTER(b,b<=INDEX(a,COUNT(a)-ROUND(COUNT(a)*15%,)))))

复制代码

夜雨无声1949

像2楼的老师说的，你模拟的时候少了个21000

=LET(A,FILTER($C$2:$C$1000,($D$2:$D$1000="")*($C$2:$C$1000<>"")),B,COUNT(A),C,FILTER(A,(A>=INDEX(SORT(A,,-1),ROUND(B*0.8,)))*(A<=INDEX(A,ROUND(B*0.85,)))),ROUND(SUM(C)/COUNT(C),))

夜雨无声1949

如果你的数据超过1000个，你直接把里面的1000改大了就好了。

殷圣

夜雨无声1949 发表于 2024-6-9 00:12
像2楼的老师说的，你模拟的时候少了个21000

=LET(A,FILTER($C$2:$C$1000,($D$2:$D$1000="")*($C$2:$C$10 ...

对的 漏了个21000，在这个问题之前一个版本你给予的正确建议，谢谢。今天就模拟下

殷圣

E学子 发表于 2024-6-8 19:19
示例中E列是否有误？缺少了一个210000，图中红色数值。
数据行数给到了1000行，如果不够用的话可自行修 ...

刚才操作了一把 发现这个公式没问题，谢谢！我想把这个公式运用到腾讯文档的协同表格里，发现有几个函数腾讯文档不支持，如果你熟悉腾讯文档表格的话 能否帮忙优化下！再次感谢

E学子

殷圣 发表于 2024-6-9 09:54
刚才操作了一把 发现这个公式没问题，谢谢！我想把这个公式运用到腾讯文档的协同表格里，发现有几个函数 ...

抱歉~在线文档并不熟悉，据我所知，其支持的函数有限，我不知道有哪些函数它是不支持的，没办法帮忙调试了。
目前来看5楼老师的公式里需求版本比较低，LET函数可以去掉（但是会使这一套公式变的非常冗长），而且还是需要使用filter和sort这两个相对要求版本的函数，加上你要求的功能性，本身公式肯定要使用数组，不确保能完美移植到在线文档里。

夜雨无声1949

殷圣 发表于 2024-6-9 09:54
刚才操作了一把 发现这个公式没问题，谢谢！我想把这个公式运用到腾讯文档的协同表格里，发现有几个函数 ...

有个超长的公式，你试试。

1. =AVERAGE(IF((($D$2:$D$1000="")*($C$2:$C$1000<>"")*$C$2:$C$1000>=LARGE(IF(($D$2:$D$1000="")*($C$2:$C$1000<>""),$C$2:$C$1000,""),ROUND(COUNTIFS($D$2:$D$1000,"",$C$2:$C$1000,"<>")*0.8,)))*(($D$2:$D$1000="")*($C$2:$C$1000<>"")*$C$2:$C$1000<=SMALL(IF(($D$2:$D$1000="")*($C$2:$C$1000<>""),$C$2:$C$1000,""),ROUND(COUNTIFS($D$2:$D$1000,"",$C$2:$C$1000,"<>")*0.85,))),($D$2:$D$1000="")*($C$2:$C$1000<>"")*$C$2:$C$1000,""))

复制代码