楼主
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),{"低","中","高"})
|