求Excel公式：自动生成指定唯一颜色数量的套装并计算组数

核心思路 #

每套选取当前库存最多的x种（示例为4种）唯一颜色，以这x种颜色中的最小库存数作为该套装的每份提取量，扣减对应库存后重复操作，直到剩余库存中符合条件的颜色不足x种为止。

数据结构假设 #

假设你的数据布局如下（可根据实际调整）：

• A列：颜色名称（如A2:A9）
• B列：初始库存数量（如B2:B9）
• C列：辅助列，记录实时剩余库存（初始值等于B列）
• D列及以后：套装记录区
  • D列：套装编号
  • E:H列：套装包含的4种颜色
  • I列：该套装的每份提取数量

高效公式实现（Excel 365/2021 推荐） #

利用动态数组函数替代LARGE，大幅提升大数据量下的性能：

1. 提取当前库存Top4的颜色 #

在E2单元格输入公式，下拉自动生成后续套装的颜色列表：

=IF(COUNTIF(C$2:C$9,">0")>=4,TAKE(SORTBY(A$2:A$9,C$2:C$9,-1),4),"凑不够4种颜色")

• SORTBY(A$2:A$9,C$2:C$9,-1)：按剩余库存降序排序颜色
• TAKE(...,4)：取排序后的前4种颜色
• COUNTIF(...)>=4：判断剩余可用颜色是否足够凑一套，不足则提示

2. 计算该套装的每份提取量 #

在I2单元格输入公式，下拉同步：

=IF(E2="凑不够4种颜色","",MIN(XLOOKUP(E2:H2,A$2:A$9,C$2:C$9)))

• XLOOKUP(E2:H2,A$2:A$9,C$2:C$9)：匹配4种颜色对应的剩余库存
• MIN(...)：取其中最小值作为套装每份的提取量（确保不超任何一种颜色的库存）

3. 实时更新剩余库存 #

在C2单元格输入公式，下拉到所有颜色行：

=B2-SUMPRODUCT(--(A2=E$2:H$100)*I$2:I$100)

• --(A2=E$2:H$100)：判断当前颜色是否出现在已生成的套装中
• SUMPRODUCT(...)：累计该颜色被提取的总数量
• 公式中E$2:H$100和I$2:I$100为套装记录的最大范围，可根据实际调整

旧版Excel（无动态数组）替代方案 #

若无法使用动态数组，可结合INDEX+MATCH实现，但性能略逊于动态数组版本：

提取Top4颜色（需按Ctrl+Shift+Enter执行数组公式） #

• 颜色1（E2）：

  =INDEX(A$2:A$9,MATCH(LARGE(C$2:C$9,1),C$2:C$9,0))
  

• 颜色2（F2）：

  =INDEX(A$2:A$9,MATCH(1,(C$2:C$9=LARGE(C$2:C$9,2))*(A$2:A$9<>E2),0))
  

• 颜色3（G2）、颜色4（H2）以此类推，每次排除已选颜色

计算每份提取量同动态数组版本的I2公式 #

示例验证（你的测试数据） #

初始库存：绿色800、黄/蓝/粉各360、白/黑各240、棕色120

1. 第一套装：E2:H2自动提取绿、黄、蓝、粉，I2计算得360，扣减后黄/蓝/粉库存清零，绿色剩余440
2. 第二套装：E3:H3自动提取绿、白、黑、棕，I3计算得120，扣减后棕色库存清零，绿色剩余320，白/黑各剩120
3. 第三套装：因剩余可用颜色仅3种（绿、白、黑），E4:H4显示凑不够4种颜色，停止生成

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