如何用Excel公式查找指定文本单元格及生成分组用户列表？
2026-7-9
如何用Excel公式实现分组与用户列表的匹配
1. 获取包含特定文本的单元格列表
结合FILTER、TEXTJOIN和ISNUMBER(SEARCH())函数即可实现，核心是先筛选匹配项，再合并成字符串列表。
假设要在A列提取包含"目标文本"的单元格，公式如下：
=TEXTJOIN(", ", TRUE, FILTER(A:A, ISNUMBER(SEARCH("目标文本", A:A)), ""))
SEARCH("目标文本", A:A)：检测A列单元格是否包含目标文本，返回匹配位置或错误值ISNUMBER(...)：将匹配结果转为布尔值（匹配为TRUE，不匹配为FALSE），作为筛选条件FILTER(A:A, ...)：筛选出符合条件的单元格TEXTJOIN(", ", TRUE, ...)：用逗号加空格合并筛选结果，TRUE表示忽略空值
2. 根据用户-分组关联表生成分组对应的用户列表
原始数据（工作表名：用户表）
| 用户名 | 用户分组 |
|---|---|
| Michael Cardiff | VCO_PROD_ES_OCHSNERLSN, VCO_PROD_CLINICAL, VCO_PROD_EDM_ADMINISTRATOR_LAWSON, VCO_PROD_HR_ADMINS_LAWSON, VCO_STAGING_ES_OCHSNERLSN, VCO_STAGING_CLINICAL, VCO_STAGING_EDM_ADMINISTRATOR_LAWSON, VCO_STAGING_HR_ADMINS_LAWSON |
| Brian Berger | VCO_PROD_ES_OCHSNERLSN, VCO_PROD_CLINICAL, VCO_PROD_EDM_ADMINISTRATOR_LAWSON, VCO_PROD_HR_ADMINS_LAWSON, VCO_STAGING_ES_OCHSNERLSN, VCO_STAGING_CLINICAL, VCO_STAGING_EDM_ADMINISTRATOR_LAWSON, VCO_STAGING_HR_ADMINS_LAWSON |
| Adam Phipps | VCO_PROD_ES_OCHSNERLSN, VCO_PROD_CLINICAL, VCO_PROD_EDM_ADMINISTRATOR_LAWSON, VCO_PROD_HR_ADMINS_LAWSON, VCO_STAGING_ES_OCHSNERLSN, VCO_STAGING_CLINICAL, VCO_STAGING_EDM_ADMINISTRATOR_LAWSON, VCO_STAGING_HR_ADMINS_LAWSON |
| Stuart Thomas | VCO_PROD_ES_OCHSNERLSN, VCO_PROD_CLINICAL, VCO_PROD_EDM_ADMINISTRATOR_LAWSON, VCO_PROD_HR_ADMINS_LAWSON, VCO_STAGING_ES_OCHSNERLSN, VCO_STAGING_CLINICAL, VCO_STAGING_EDM_ADMINISTRATOR_LAWSON, VCO_STAGING_HR_ADMINS_LAWSON |
| Kerman Lafleur | VCO_PROD_ES_OCHSNERLSN, VCO_PROD_CLINICAL, VCO_PROD_EDM, VCO_PROD_SC_LAWSON, VCO_STAGING_ES_OCHSNERLSN, VCO_STAGING_CLINICAL, VCO_STAGING_EDM, VCO_STAGING_SC_LAWSON |
| Ira Perry | VCO_PROD_ES_OCHSNERLSN, VCO_PROD_CLINICAL, VCO_PROD_EDM, VCO_PROD_SC_LAWSON, VCO_STAGING_ES_OCHSNERLSN, VCO_STAGING_CLINICAL, VCO_STAGING_EDM, VCO_STAGING_SC_LAWSON |
| Pamela Trahan | VCO_PROD_ES_OCHSNERLSN, VCO_PROD_CLINICAL, VCO_PROD_EDM, VCO_PROD_SC_LAWSON, VCO_STAGING_ES_OCHSNERLSN, VCO_STAGING_CLINICAL, VCO_STAGING_EDM, VCO_STAGING_SC_LAWSON |
| Carol Northam | VCO_PROD_ES_OCHSNERLSN, VCO_PROD_CLINICAL, VCO_PROD_EDM, VCO_PROD_SC_LAWSON, VCO_STAGING_ES_OCHSNERLSN, VCO_STAGING_CLINICAL, VCO_STAGING_EDM, VCO_STAGING_SC_LAWSON |
| Sheena Ronsonet | VCO_PROD_ES_OCHSNERLSN, VCO_PROD_CLINICAL, VCO_PROD_EDM, VCO_PROD_HR_ADMINS_LAWSON, VCO_STAGING_ES_OCHSNERLSN, VCO_STAGING_CLINICAL, VCO_STAGING_EDM, VCO_STAGING_HR_ADMINS_LAWSON |
公式实现（工作表名：分组表）
假设分组列表在分组表的A列，在B2单元格输入以下公式后下拉填充：
=TEXTJOIN(", ", TRUE, FILTER(用户表!$A$2:$A$10, ISNUMBER(SEARCH(分组表!A2, 用户表!$B$2:$B$10)), ""))
SEARCH(分组表!A2, 用户表!$B$2:$B$10)：检查每个用户的分组列是否包含当前行的分组名称ISNUMBER(...)：将匹配结果转为布尔值，用于筛选符合条件的用户FILTER(用户表!$A$2:$A$10, ...)：提取属于该分组的所有用户名TEXTJOIN(", ", TRUE, ...)：将用户名合并为逗号分隔的字符串
预期结果
| 分组名称 | 用户列表 |
|---|---|
| VCO_PROD_ES_OCHSNERLSN | Michael Cardiff, Brian Berger, Adam Phipps, Stuart Thomas, Kerman Lafleur, Ira Perry, Pamela Trahan, Carol Northam, Sheena Ronsonet |
| VCO_PROD_CLINICAL | Michael Cardiff, Brian Berger, Adam Phipps, Stuart Thomas, Kerman Lafleur, Ira Perry, Pamela Trahan, Carol Northam, Sheena Ronsonet |
| VCO_PROD_EDM_ADMINISTRATOR_LAWSON | Michael Cardiff, Brian Berger, Adam Phipps, Stuart Thomas |
| VCO_PROD_HR_ADMINS_LAWSON | Michael Cardiff, Brian Berger, Adam Phipps, Stuart Thomas, Sheena Ronsonet |
| VCO_PROD_EDM | Kerman Lafleur, Ira Perry, Pamela Trahan, Carol Northam, Sheena Ronsonet |
| VCO_PROD_SC_LAWSON | Kerman Lafleur, Ira Perry, Pamela Trahan, Carol Northam |
| VCO_STAGING_ES_OCHSNERLSN | Michael Cardiff, Brian Berger, Adam Phipps, Stuart Thomas, Kerman Lafleur, Ira Perry, Pamela Trahan, Carol Northam, Sheena Ronsonet |
| VCO_STAGING_CLINICAL | Michael Cardiff, Brian Berger, Adam Phipps, Stuart Thomas, Kerman Lafleur, Ira Perry, Pamela Trahan, Carol Northam, Sheena Ronsonet |
| VCO_STAGING_EDM_ADMINISTRATOR_LAWSON | Michael Cardiff, Brian Berger, Adam Phipps, Stuart Thomas |
| VCO_STAGING_HR_ADMINS_LAWSON | Michael Cardiff, Brian Berger, Adam Phipps, Stuart Thomas, Sheena Ronsonet |
| VCO_STAGING_EDM | Kerman Lafleur, Ira Perry, Pamela Trahan, Carol Northam, Sheena Ronsonet |
| VCO_STAGING_SC_LAWSON | Kerman Lafleur, Ira Perry, Pamela Trahan, Carol Northam |
内容的提问来源于stack exchange，提问作者Mike
相关产品推荐
相关产品推荐
