基于排名的门店货品均衡分配Excel技术方案问询

• 需按照无并列的排名，为4家门店分配货品，核心目标是尽可能满足更多门店的需求。
• 曾参考过一种Excel公式分配方案，但该方案存在“赢家通吃”的问题。
• 期望实现均衡化的资源分配逻辑，具体示例如下：
  • 场景1：有3件可用货品，分配给9家各需18件的门店，应给排名前三的门店各分配1件；
  • 场景2：9家门店各需2件（总需求18件），可用货品为17件时，前8家门店各分2件，最后1家分1件。

原方案的问题 #

原使用的Excel公式：

=IF(SUMPRODUCT(($B$2:$E$2<=B2)*$F$2:$I$2)<=$K$2,B2,MAX($K$2-SUMPRODUCT(($B$2:$E$2<=(B2-1))*$F$2:$I$2),0))

该公式的分配逻辑是“赢家通吃”：若有3件可用货品，会优先填满排名最高门店的全部需求，再将剩余货品分配给后续门店，直至货品耗尽，无法实现均衡分配的目标。

内容的提问来源于stack exchange，提问作者DMad0423