楼主 wjc2090742 |
Q:如何在各个类别中按照数据大小的分段来定级?如下两图,需要在不同的类别编码内,根据数据的大小对其定级。
A:公式如下 1、使用max和min函数,算出各个区间,利用查找或比较,得到级别。
- =MID("高中低",SUM(N(H2<=(MAX(($F$2:$F$15=F2)*$H$2:$H$15)*ROW($1:$3)/3+(1-ROW($1:$3)/3)*MIN(IF($F$2:$F$15=F2,$H$2:$H$15))))),1)
荣版:
- =LOOKUP(H2,(MAX(IF($F$2:$F$15=F2,$H$2:$H$15))-MIN(IF($F$2:$F$15=F2,$H$2:$H$15)))/3*(ROW($1:$3)-1)+MIN(IF($F$2:$F$15=F2,$H$2:$H$15)),{"低";"中";"高"})
2、利用四分、百分点值统计函数直接返回最小值和最大值并计算区段。
- =MID("高中低",SUM(N(H2<=PERCENTILE(SUBTOTAL({4,5},OFFSET($H$1,MATCH(F2,$F$2:$F$15,),,SUM(N($F$2:$F$15=F2)))),ROW($1:$3)/3))),1)
liuguansky,加了round函数,在本例中更加合理:
- =LOOKUP(C2,MIN(IF(A$2:A$15=A2,C$2:C$15))+ROUND(SUM(PERCENTILE(IF(A$2:A$15=A2,C$2:C$15),{0,1})*{-1,1})/3,1)*{0,1,2},{"低","中","高"})
草版:
- =LOOKUP(H2,MIN(IF(F$2:F$15=F2,H$2:H$15))+{0,1,2}*SUM(QUARTILE(IF(F$2:F$15=F2,H$2:H$15),{0,4})*{-1,1})/3,{"低","中","高"})
3、结合了上面的2种思路,用数学上合并公因式的办法,得到了下面的公式。可能比较难理解,是从上面的公式演化来的。
- =MID("高中低",SUM(N(H2<=MMULT(QUARTILE(IF(F$2:F$15=F2,H$2:H$15),{0,4})*({-1,1}*ROW($1:$3)+{3,0})/3,{1;1}))),1)
在类别中按照分段定级.rar |
5楼 wjc2090742 |
楼上的太抬举我了,其实我画图是想说明临界点时,lookup和<=算法结果的差别。这层楼先探讨一下统计函数的公式吧。
- =MID("高中低",SUM(N(H2<=PERCENTILE(SUBTOTAL({4,5},OFFSET($H$1,MATCH(F2,$F$2:$F$15,),,SUM(N($F$2:$F$15=F2)))),ROW($1:$3)/3))),1)
上面的这个公式用了percentile,百分点值函数,公式写的比较绕。但是我的思路其实是很直接的数学算法,只有2个数值时,百分点值和百分比是一样的。从这个思路出发,可以写的更简单而且可能更明了一些。
- =MID("高中低",SUM(N(H2<=PERCENTILE(QUARTILE(IF(F$2:F$15=F2,H$2:H$15),{0,4}),{1,2,3}/3))),1)
或者
- =LOOKUP(H2,PERCENTILE(QUARTILE(IF(F$2:F$15=F2,H$2:H$15),{0,4}),{0,1,2}/3),{"低","中","高"})
|