Excel中基于其他下拉列表创建筛选式下拉列表的需求

1. 拆分资产名称字段 #

先把资产名称里的各个属性提取到单独列，为后续筛选做准备：

• 假设资产名称在A列（从A2开始是数据）：
  • 提取建筑名称（B2单元格）：
    Excel 365/2021：=INDEX(TEXTSPLIT(A2, "-"),1)
    旧版Excel：=LEFT(A2, FIND("-",A2)-1)
    Google Sheets：=INDEX(SPLIT(A2, "-"),1)
  • 提取部门编号（C2单元格）：
    Excel 365：=INDEX(TEXTSPLIT(A2, "-"),2)
    旧版Excel：=MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1)
    Google Sheets：=INDEX(SPLIT(A2, "-"),2)
  • 提取主类型（D2单元格）：
    Excel 365：=INDEX(TEXTSPLIT(A2, "-"),3)
    旧版Excel：=MID(A2,FIND("-",A2,FIND("-",A2)+1)+1,FIND("-",A2,FIND("-",A2,FIND("-",A2)+1)+1)-FIND("-",A2,FIND("-",A2)+1)-1)
    Google Sheets：=INDEX(SPLIT(A2, "-"),3)
    把公式下拉到所有数据行，完成字段拆分。

2. 创建唯一值数据源 #

为每个下拉列表生成无重复的选项：

• 建筑名称唯一值：在空白列（如F列）F2输入=UNIQUE(B:B)，生成结果后手动添加一行「全部」
• 部门编号唯一值：G2输入=UNIQUE(C:C)，添加「全部」选项
• 主类型唯一值：H2输入=UNIQUE(D:D)，添加「全部」选项

3. 设置下拉筛选控件 #

假设把筛选控件放在工作表的B1（建筑）、C1（部门）、D1（主类型）：

• 选中B1，打开「数据验证」：
  • Excel：数据选项卡 → 数据验证 → 允许选「序列」，来源选择F列的唯一值区域（包含「全部」）
  • Google Sheets：数据 → 数据验证 → 条件选「列表从范围」，选择F列的唯一值区域
• 重复上述操作，设置C1（部门）和D1（主类型）的下拉列表

4. 用FILTER函数实现动态筛选 #

在空白区域（如A5）输入筛选公式，返回符合条件的资产记录：

Excel 365/2021 版本 #

=FILTER(A:D, 
  (UPPER(B:B)=IF(B1="全部",UPPER(B:B),UPPER(B1))) * 
  (C:C=IF(C1="全部",C:C,C1)) * 
  (UPPER(D:D)=IF(D1="全部",UPPER(D:D),UPPER(D1))), 
  "无匹配资产")

（注：UPPER用于忽略大小写匹配，不需要的话可以直接去掉，改成字段直接相等）

Google Sheets 版本 #

=FILTER(A:D, 
  (UPPER(B:B)=IF(B1="全部",UPPER(B:B),UPPER(B1))) * 
  (C:C=IF(C1="全部",C:C,C1)) * 
  (UPPER(D:D)=IF(D1="全部",UPPER(D:D),UPPER(D1))), 
  "无匹配资产")

旧版Excel（无FILTER函数） #

用高级筛选实现：

1. 把B1、C1、D1设为条件区域，列标题和拆分后的B、C、D列标题一致
2. 当选择「全部」时，对应条件单元格留空
3. 每次点击「数据」→「高级」，选择列表区域和条件区域，即可更新筛选结果

关键注意事项 #

• 先清理资产名称格式，确保所有记录都是「建筑-部门-主类型-子类型-序列号」的统一格式，避免拆分出错
• 可以把下拉列表的「全部」替换为空值，此时公式里的判断改成IF(B1="",B:B,B1)，操作更简洁
• 大数据量下，把公式里的整列引用（如B:B）改成实际数据范围（如B2:B10000），提升运算速度

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