Excel公式编写求助：按指定数值分配人员至对应位置

需求说明 #

当指定数值为9时：

• 位置1：Betty
• 位置2-8：按顺序填充除Albert、Betty外的其余人员
• 位置9：Albert

当指定数值为6时：

• 位置6：Albert
• 位置7：Betty
• 位置1-5、8-9：按顺序填充除Albert、Betty外的其余人员

假设数据结构 #

• 指定数值存储在单元格 $A$1
• 完整人员列表（包含Albert、Betty及其他7人）存储在区域 $C$2:$C$10
• 公式用于填充位置1-9对应的人员，对应单元格为B2（位置1）到B10（位置9）

公式实现 #

方案1：适用于Excel 365/2021及以上（支持FILTER函数） #

在B2单元格输入以下公式，然后下拉填充到B10：

=IF($A$1=9,
    IF(ROW()-1=1,"Betty",IF(ROW()-1=9,"Albert",INDEX(FILTER($C$2:$C$10,($C$2:$C$10<>"Albert")*($C$2:$C$10<>"Betty")),ROW()-2))),
    IF($A$1=6,
        IF(ROW()-1=6,"Albert",IF(ROW()-1=7,"Betty",
            IF(ROW()-1<6,INDEX(FILTER($C$2:$C$10,($C$2:$C$10<>"Albert")*($C$2:$C$10<>"Betty")),ROW()-1),
            INDEX(FILTER($C$2:$C$10,($C$2:$C$10<>"Albert")*($C$2:$C$10<>"Betty")),ROW()-3)
        ))),
    ""
)

方案2：适用于旧版Excel（无FILTER函数，需数组公式） #

在B2单元格输入以下公式，按Ctrl+Shift+Enter确认数组公式后，下拉填充到B10：

=IF($A$1=9,
    IF(ROW()-1=1,"Betty",IF(ROW()-1=9,"Albert",INDEX($C$2:$C$10,SMALL(IF(($C$2:$C$10<>"Albert")*($C$2:$C$10<>"Betty"),ROW($C$2:$C$10)-ROW($C$2)+1),ROW()-2)))),
    IF($A$1=6,
        IF(ROW()-1=6,"Albert",IF(ROW()-1=7,"Betty",
            IF(ROW()-1<6,INDEX($C$2:$C$10,SMALL(IF(($C$2:$C$10<>"Albert")*($C$2:$C$10<>"Betty"),ROW($C$2:$C$10)-ROW($C$2)+1),ROW()-1),
            INDEX($C$2:$C$10,SMALL(IF(($C$2:$C$10<>"Albert")*($C$2:$C$10<>"Betty"),ROW($C$2:$C$10)-ROW($C$2)+1),ROW()-3)
        ))),
    ""
)

公式逻辑说明 #

1. 先判断指定数值是9还是6，执行对应分支逻辑
2. 指定数值=9时：
   • 位置1直接返回Betty，位置9直接返回Albert
   • 位置2-8通过INDEX提取过滤后的其余人员列表的对应项（位置2对应列表第1项，以此类推）
3. 指定数值=6时：
   • 位置6返回Albert，位置7返回Betty
   • 位置1-5直接提取过滤后的人员列表的前5项
   • 位置8-9提取过滤后的人员列表的第6-7项（减去已占用的2个位置偏移量）

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