我是 Excel 新手，所以可能已经有了答案，只是我不知道正确的术语。我试图转换一组数据，然后将其用于另一个公式，而不使用中间列。
现在，我有一张电子表格，其中 A 列有 10 项资产，B 列有一些分数。我想根据每项资产的分数，计算分配给每项资产的投资组合百分比，但设置上限，使得任何资产的占比都不能超过投资组合的 30%。
我无法弄清楚如何在不使用另一列作为分数素数的情况下实施此上限。
鉴于 B 列中的分数数据，我有 D 列“未调整的投资组合百分比”，其中每行都有公式=B3/SUM($B$2:$B$11)
然后我有 E 列，其中包含 B 列的值，但上限为 30% =MIN(D2, 0.3)。
最后，我有 F 列“调整后的投资组合百分比” =E2/SUM($E$2:$E$11)。
如果没有 E 列我该怎么做？
我的第一直觉是，在使用列之前，可能存在一些语法，如 lambda 样式。例如，在伪代码中，应该能够计算调整后的投资组合百分比列
let values = min(score / sum(scores), .3) / sum(adjusted) for score in scores
where adjusted = min(score / sum(scores), .3) for score in scores
我不知道如何用 excel 做到这一点，但如果我能用某种 lambda 语法编写一个公式，我会很兴奋。例如，如果 B 列中有分数，则第二行将是
=MIN(B2/SUM($B:$B), .3)/SUM(x -> MIN(x/SUM($B:$B), .3), $B:$B)。
将重复的逻辑分解成本地函数会很酷，但对于非编程语言来说，这可能要求太高了。对于非编程语言来说，这种数据转换可能要求太高了？
答案1
有一种方法可以做到。
如果说，1 或 2 个值设置为 30%，那么其他值必须缩放，以便它们加起来达到 70% 或 40% 等等。此公式执行以下操作：
=IF(C2>=0.3,0.3,(C2/(SUMIF(C$2:C$11,"<0.3")))*(1-0.3*(COUNTIF(C$2:C$11,">=.3"))))
约翰，Excel 语法可能比较难懂，所以让我来教你一下。IF() 公式根据 C 列中的 % 是否大于或等于 30% 来填充两个值之一。如果是，则公式返回 0.3。如果不是，则返回公式其余部分的计算结果。
第一部分 C2/SUMIF() 给出 C 列中的值占 < 30% 的值的百分比。这些百分比的总和为 100%，因此它们乘以第二部分 1-0.3*COUNTIF()。它从 1 开始，然后为每个 >= 30% 的值减去 .3。因此，第一部分中的值按 0.7 或 0.4 等倍数缩放。
如果有可能有超过 3 只股票 > 30%，那么可能需要进行一些错误检查。希望这能有所帮助，我很高兴看到您的 lambda 表达式。
答案2
您可能需要稍微重新考虑一下您的方法。考虑一下：
在哪一栏乙是原始分数，列C是列的规范化版本乙， 柱子德是柱的剪辑版本C和列埃是列的规范化版本德。
问题在于像奥菲莉亚这样的物品。它的标准化价值超过了 30%。当它被限制然后重新标准化时，它的价值仍然超过 30%。