如何计算Excel中的行组的五分之一？ [英] How do you calculate the Quintile for groups of rows in Excel?

问题描述

我在如何计算Excel中每一行的五分位数？我想为每行导出相同的五分位数据，但是我需要五分位数基于由另一列中的值确定的组。

I found a partial answer to this question under How do you calculate the Quintile for every row in Excel?. I would like to derive the same quintile data for each row but I need the quintiles to be based on groups that are determined by a value in another column.

推荐答案

使用以下公式：

=MAX(1,ROUNDUP(10*PERCENTRANK($C:$C,$C2,4),0))

将值10更改为您需要的任意数量的组 - 目前正在制作十进制组。将$ C：$ C更改为具有目标值范围的列，并将$ C2更改为具有目标值并自动填充的第一行中的单元格。

Change the value 10 to split into whatever number of groups you need - currently it's making decile groups. Change $C:$C to column with target range of values, and change $C2 to the cell in the first row with target value and autofill down.

您的输出将根据1的最小值和数值上的任何更高的值分组。

Your output will be a split of groups based on the value of 1 being lowest and anything greater higher value in terms of numerical value.

如果您的意思是只将行号作为排名标准，您可以在A中插入一个新列，并自动填写与您的行引用相对应的数字。

If you mean just row numbers as your ranking criteria, you could insert a new column in A and autofill down numbers that correspond to your row references.

这篇关于如何计算Excel中的行组的五分之一？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！