Excel非VBA方案：如何根据Cleaners工作表的房间分配在Colours工作表匹配显示对应清洁员名称

嗨，别担心不会VBA！我给你一个纯公式的解决方案，完全不用写代码就能轻松实现你的需求～

需求回顾 #

你需要把Cleaners工作表里每个清洁员（Cleaner1到Cleaner19，都是独立命名区域）分配的房间，反向对应到Colours工作表里：比如Cleaner1选了T1，Colours里T1旁边就自动显示Cleaner1。

具体操作步骤 #

假设你的Colours工作表A列是房间名称（比如A2是T1、A3是T2...），要在B列显示对应的清洁员，按以下步骤来：

方案1：适合Excel 365/2021（支持动态数组）

在Colours的B2单元格输入下面的公式，按回车后公式会自动适配所有行：

=FILTER(
    {"Cleaner 1","Cleaner 2","Cleaner 3","Cleaner 4","Cleaner 5","Cleaner 6","Cleaner 7","Cleaner 8","Cleaner 9","Cleaner 10","Cleaner 11","Cleaner 12","Cleaner 13","Cleaner 14","Cleaner 15","Cleaner 16","Cleaner 17","Cleaner 18","Cleaner 19"},
    INDIRECT("Cleaner "&ROW($1:$19))=A2,
    "未分配"
)

公式说明：

• 第一部分是所有清洁员的名称数组，你可以根据实际命名调整（比如如果命名是Cleaner1不带空格，就改成"Cleaner1"）
• INDIRECT("Cleaner "&ROW($1:$19))会自动调用Cleaner1到Cleaner19的命名区域，获取每个清洁员分配的房间值
• 最后一个参数"未分配"是没有找到对应清洁员时的提示，你也可以改成空字符串""

方案2：适合旧版Excel（2019及以前）

如果你的Excel不支持动态数组，就用这个数组公式，输入后按Ctrl+Shift+Enter确认，再下拉填充到所有行：

=IFERROR(
    INDEX(
        {"Cleaner 1","Cleaner 2","Cleaner 3","Cleaner 4","Cleaner 5","Cleaner 6","Cleaner 7","Cleaner 8","Cleaner 9","Cleaner 10","Cleaner 11","Cleaner 12","Cleaner 13","Cleaner 14","Cleaner 15","Cleaner 16","Cleaner 17","Cleaner 18","Cleaner 19"},
        MATCH(A2,INDIRECT("Cleaner "&ROW($1:$19)),0)
    ),
    "未分配"
)

关键注意事项 #

• 确保Cleaners里的命名区域名称和公式里的完全一致，比如命名是Cleaner1（无空格），公式里就要改成"Cleaner"&ROW($1:$19)，名称数组也要对应改成"Cleaner1"
• 如果一个房间被分配给多个清洁员，方案1的FILTER会显示所有匹配的名称；如果需要合并成一个单元格，可以把FILTER换成TEXTJOIN(", ",TRUE,...)

备注：内容来源于stack exchange，提问作者Dark_Entropy