vba 如何计算 Excel 中每一行的五分位数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28287587/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you calculate the Quintile for every row in Excel?
提问by ChrisG
I'm trying to calculate the quintile for every row of a column in Excel.
我正在尝试计算 Excel 中每一列的五分之一。
The resulting value for each row in Excel should be 1, 2, 3, 4, or 5.
Excel 中每一行的结果值应为 1、2、3、4 或 5。
A value of one will be the top 20%, a value of 5 is the bottom 20%.
值 1 将是前 20%，值 5 是后 20%。
This is my current formula which SEEMS to work, but I'm curious to see if anyone has a better way, a UDF, or sees an error in my formula...
这是我目前似乎可以工作的公式，但我很想知道是否有人有更好的方法，UDF，或者在我的公式中看到错误...
=ROUNDDOWN(RANK.AVG(A1,$A:$A1,0)/((COUNT(A:A1)+1)/5),0)+1
A1 through A131 has the values I'm placing in quintiles.
A1 到 A131 具有我在五分位数中放置的值。
Thanks
谢谢
采纳答案by barry houdini
Your suggested formula works for me......but I think there are several ways you could do this, e.g. use PERCENTILEfunction to get the breakpoints and then match against those with MATCH, i.e.
你建议的公式对我有用......但我认为有几种方法可以做到这一点，例如使用PERCENTILE函数来获取断点，然后与那些匹配MATCH，即
=MATCH(A1,PERCENTILE(A$1:A$131,{5,4,3,2,1}/5),-1)
=MATCH(A1,PERCENTILE(A$1:A$131,{5,4,3,2,1}/5),-1)
In most cases that gives the same results as your formula but there might be some discrepancies around the boundaries
在大多数情况下，结果与您的公式相同，但边界周围可能存在一些差异
回答by Mark
I liked the simplicity of =MATCH(A1,PERCENTILE(A$1:A$131,{5,4,3,2,1}/5),-1). I compared the results with a two step manual process that included: 1) calculate 4 percentiles for .2, .4. .6 .8 and 2) a combination of nested IF statements and ended up with the same result as =Match recommended formula in a same of 250 values.
我喜欢=MATCH(A1,PERCENTILE(A$1:A$131,{5,4,3,2,1}/5),-1). 我将结果与两步手动过程进行了比较，其中包括：1) 计算 0.2、0.4 的 4 个百分位数。.6 .8 和 2) 嵌套 IF 语句的组合，结果与 =Match 推荐的公式相同，有 250 个值。
I.E. STEP 1:
第 1 步：
=PERCENTILE.INC($J:$J7,0.2) -> Adjust 0.2 to 0.4; 0.6; 0.8 accordingly
STEP 2:
第2步：
=IF(J3<=$AB,5,
IF(J3<=$AB,4,
IF(J3<=$AB,3,
IF(J3<=$AB,2,
1))))
Compare each value to the calculated =PERCENTILE.INC in STEP 1
将每个值与第 1 步中计算的 =PERCENTILE.INC 进行比较
