vba 如何在excel中计算范围为12,000个单元格的十分位数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7612792/
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 can I calculate deciles with a range of 12,000 cells in excel?
提问by Andy
I have a column of 12,000+ numbers, both positive and negative, sorted from highest to lowest in an Excel spreadsheet.
我有一列 12,000 多个数字，包括正数和负数，在 Excel 电子表格中从高到低排序。
Is there an easy way to go about dividing this range into deciles?
有没有一种简单的方法可以将这个范围划分为十分位数？
回答by Brian Camire
This may not be the most efficient solution, but you might try the following:
这可能不是最有效的解决方案，但您可以尝试以下方法：
Assuming your numbers are in cells A1 through A12000, enter the following formula in cell B1
=PERCENTRANK($A$1:$A$12000,A1,1). This calculates the percent rank, with the set of values in cells $A$1:$A$12000, of the value in cell A1, rounded down to 1 decimal place (which is all you need to identify the decile).Copy the formula in cell B1 to cells B2 through B12000.
Use the values in column B to identify the decile for the corresponding value in column A. 0 identifies values greater than or equal to the 0th percentile and less than the 10th percentile, 0.1 identifies values greater than or equal to the 10th percentile and less than the 20th percentile, and so on. Depending on the size of your set and whether or not there are duplicates, there may or may not be a value that gets assigned a PERCENTRANK of exactly 1.
假设您的数字位于单元格 A1 到 A12000 中，请在单元格 B1 中输入以下公式
=PERCENTRANK($A$1:$A$12000,A1,1)。这将使用单元格 $A$1:$A$12000 中的一组值计算单元格 A1 中值的百分比排名，四舍五入到小数点后一位（这是识别十分位数所需的全部内容）。将单元格 B1 中的公式复制到单元格 B2 到 B12000。
使用 B 列中的值来标识 A 列中相应值的十分位数。0 标识大于或等于第 0 个百分位且小于第 10 个百分位的值，0.1 标识大于或等于第 10 个百分位且小于第 20 个百分位数，依此类推。根据您的集合的大小以及是否存在重复项，可能会或可能不会分配一个 PERCENTRANK 恰好为 1 的值。
If you are using Excel 2010, you might, depending on your needs, consider using the new functions PERCENTRANK.INC and PERCENTRANK.EXC that are supposed to supercede PERCENTRANK.
如果您使用的是 Excel 2010，则可以根据需要考虑使用新函数 PERCENTRANK.INC 和 PERCENTRANK.EXC，它们应该取代 PERCENTRANK。
Hope this helps.
希望这可以帮助。
回答by ak112358
Assuming your data is in column A, in a neighboring column in row 1 put this formula and then fill down:
假设您的数据在 A 列中，在第 1 行的相邻列中输入此公式，然后填写：
=IF(A1<PERCENTILE(A:A,0.1),1
,IF(A1<PERCENTILE(A:A,0.2),2
,IF(A1<PERCENTILE(A:A,0.3),3
,IF(A1<PERCENTILE(A:A,0.4),4
,IF(A1<PERCENTILE(A:A,0.5),5
,IF(A1<PERCENTILE(A:A,0.6),6
,IF(A1<PERCENTILE(A:A,0.7),7
,IF(A1<PERCENTILE(A:A,0.8),8
,IF(A1<PERCENTILE(A:A,0.9),9,10
)))))))))
This will display a 1 for the first decile, 2 for the second, 3 for the third, etc.
这将显示第一个十分位数为 1，第二个为 2，第三个为 3，依此类推。
回答by shraddha dalmia
I had same query, found answer on this forum: https://www.mrexcel.com/forum/excel-questions/581682-create-decile-segments.html
我有同样的疑问，在这个论坛上找到了答案：https: //www.mrexcel.com/forum/excel-questions/581682-create-decile-segments.html
Try:
尝试：
=INT((ROWS($A:A1) - 1) * 10 / ROWS($A:$A90))+1
