如图:Excel表数据如何用公式求出评定值?

发布网友发布时间：9小时前

共5个回答

热心网友时间：2024-10-21 06:26

相差10%是与最大值还是最小值相比？

D2=IF((MAX(A2:C2)-MIN(A2:C2))/MAX(A2:C2)>0.1,MEDIAN(A2:C2),IF((MAX(A2:C2)-MIN(A2:C2))/MAX(A2:C2)>0.15,"不合格",AVERAGE(A2:C2)))向下复制

这个公式是比最大值大10%。如果是比最小值大10%，用下面这个

D2=IF((MAX(A2:C2)-MIN(A2:C2))/MIN(A2:C2)>0.1,MEDIAN(A2:C2),IF((MAX(A2:C2)-MIN(A2:C2))/MIN(A2:C2)>0.15,"不合格",AVERAGE(A2:C2)))向下复制

追问最大值与中间值相差小于10%且最小值与中间值相差小于10%

追答

D2=IF(MAX((MAX(A2:C2)-MEDIAN(A2:C2))/MEDIAN(A2:C2),(MEDIAN(A2:C2)-MIN(A2:C2))/MIN(A2:C2))<0.1,AVERAGE(A2:C2),IF(MAX((MAX(A2:C2)-MEDIAN(A2:C2))/MEDIAN(A2:C2),(MEDIAN(A2:C2)-MIN(A2:C2))/MIN(A2:C2))<0.15,MEDIAN(A2:C2),"不合格"))

热心网友时间：2024-10-21 06:27

=if(max(A2:C2)/min(A2:C2)>1.15,"不合格",if(max(A2:C2)/min(A2:C2)<1.10,(A2:C2)/3),(A2:C3)-min(A2:C2)-max(A2:C2))
太长了，写的我头都晕
没去验证，你复制试吧
验证了下，上面有错，正确的为
=IF(MAX(A2:C2)/MIN(A2:C2)>1.15,"不合格",IF(MAX(A2:C2)/MIN(A2:C2)<1.1,((SUM(A2:C2)/3)),SUM(A2:C2)-MIN(A2:C2)-MAX(A2:C2)))

热心网友时间：2024-10-21 06:27

最大值与中间值相差小于10%
且还是或
最小值与中间值相差小于10%？追问最大值与中间值相差小于10%
且最小值与中间值相差小于10%

热心网友时间：2024-10-21 06:28

D2：
=IF(OR(MAX(A2:C2)/MEDIAN(A2:C2)>1.15,MIN(A2:C2)/MEDIAN(A2:C2)<0.85),"不合格",IF(AND(MAX(A2:C2)/MEDIAN(A2:C2)<1.1,MIN(A2:C2)/MEDIAN(A2:C2)>0.9),AVERAGEA(A2:C2),MEDIAN(A2:C2)))

热心网友时间：2024-10-21 06:28

公式很简单，但你表达的听起来有点懵，是谁和谁的差，和谁的百分比。