使用Excel的Rank（）函数根据排名和约束计算分配 [英] Using the Excel's Rank() function to calculate allocations based on ranking and constraints

问题描述

我有下表设置

 限额分配收益率排名
 $ 600 [计算] 0.07％7 
 $ 600 0.09％6 
 $ 600 0.20％1 
 $ 400 0.20％1 
 $ 400 0.13％4 
 $ 200 0.19％3 
 $ 200 0.12％5 
  

此外，我有一个约束，我只能分配总共 $ 2000 这里的7行按照他们的收益排名（所以如果从$ 2000的总计有剩余的话，更高的收益将得到所有分配到限制列的所有内容）。

我想知道如何设置方程式，以便它可以自动执行分配。谢谢！

解决方案

我打算假设这个表从A1开始...

在E1中，将您必须分配的金额

在B2中（然后复制到B3 ... B8）使用以下公式（$，$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ B $ 2：$ B $ 8））

这将解释更高排名，并且休息一下，无论其限制是多少，还有什么剩余的。

这个方程式有一个错误，你需要弄清楚如何处理：
如果在分配结束时有相等的排名，那么两者都将得到最终的数额。 （例如，尝试这个$ 2,001，你会看到那2个排列为 1 的行将都会要求最终的美元）

I have the following table set up

Limit  Allocation  Yield    Ranking
$600    [to calc]   0.07%    7
$600                0.09%    6
$600                0.20%    1
$400                0.20%    1
$400                0.13%    4
$200                0.19%    3
$200                0.12%    5

Additionally, I have a constraint which I could only allocate a total of $2000 across the 7 rows here, by the rankings of their yield (so a higher yield would get everything allocated up to the limit column if there is any left overs from the $2000 total).

I was wondering how I could set up the equations so that it could perform the allocation automatically. Thanks!

解决方案

I'm going to assume this table starts in A1...

In E1, put the amount you have to allocate

In B2 (and then copied to B3...B8) use the following formula

=MIN(A2,$E$1-SUMIF($D$2:$D$8,">"&D2,$B$2:$B$8))

This will work out how much has been taken by higher ranked, and take the rest, upto whatever is the lesser amount of their limit, and what is left in the pot.
There is one fault with this equation that you will need to figure out how to handle: If there are equal ranks at the end of the distribution, then both will get the final amount. (e.g. try this with $2,001, and you will see that the 2 rows that have then rank 1 will both claim the final dollar)

这篇关于使用Excel的Rank（）函数根据排名和约束计算分配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！