Excel无脚本实现矩阵姓名双优先级提取且无空行需求

核心需求 #

• 单元格区域G3:O42每行包含1-9个姓名，空位填0，需从每行提取姓名到D3:D42
• 保持每行姓名从左到右的优先级，仅在必要时调整选择的姓名（避免后续行空值）
• 若总共有N个唯一姓名（最多9个），D3:D42的前N行必须填满所有唯一姓名，后续行无空值（可循环提取已选姓名）

解决方案公式 #

适用于Excel 365/2021（动态数组支持） #

在D3单元格输入以下公式，下拉填充至D42：

=IFERROR(
    INDEX(G3:O3,
        MATCH(
            MIN(
                IF(COUNTIFS(D$2:D2, G3:O3)=0,
                    SUMPRODUCT(--(G$3:O$42=G3:O3)*(ROW(G$3:O$42)>=ROW(G3))),
                    999
                )
            ),
            IF(COUNTIFS(D$2:D2, G3:O3)=0,
                SUMPRODUCT(--(G$3:O$42=G3:O3)*(ROW(G$3:O$42)>=ROW(G3))),
                999
            ),
            0
        )
    ),
    INDEX(D$2:D2, MATCH(TRUE, COUNTIF(D$2:D2, D$2:D2)=1, 0))
)

公式逻辑说明 #

1. 筛选未提取姓名：COUNTIFS(D$2:D2, G3:O3)=0 找出当前行中还未被提取过的姓名
2. 计算后续出现次数：SUMPRODUCT(--(G$3:O$42=G3:O3)*(ROW(G$3:O$42)>=ROW(G3))) 统计该姓名在当前行及以下所有行中的出现次数，次数越少说明后续可选机会越少
3. 优先选择稀缺姓名：通过MIN找到出现次数最少的姓名，用MATCH+INDEX提取该姓名（若次数相同，仍遵循行内从左到右的优先级）
4. 空值兜底：当所有姓名都已被提取时，从已提取的姓名中选择第一个仅出现一次的姓名循环填充，确保无空行

示例验证 #

针对你提到的场景：

• G8:O8为Matt、Jaime、Courtney、0...，G9:O9包含Matt
• 原公式会优先提取Matt导致D9无可选姓名为空
• 新公式会计算出Jaime在后续行的出现次数少于Matt，因此D8提取Jaime，D9可提取Matt，避免空行，同时填满前9个唯一姓名的位置

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