如何在Excel 365中创建无需VBA的联系人搜索功能

前置配置 #

• 将原始联系人数据转换为Excel超级表（选中数据区域按Ctrl+T，勾选「表包含标题」），将表命名为ContactTable，确认字段包含CustomerName、ID、City、Country、EmailAddress、Account Manager等。
• 单独新建名为「联系人查询」的工作表，作为唯一面向访问用户开放的工作表，后续你计划的原始数据防护操作（文字设为白色、锁定单元格、隐藏多余行列、密码保护隐藏原始数据表）和后续功能完全兼容，可正常配置。

刚性需求落地步骤 #

1. 解决多人访问搜索结果互相覆盖问题 #

无需VBA，通过SharePoint嵌入配置即可实现用户操作完全隔离：

• 将制作好的文件上传到对应SharePoint文档库，选中文件后选择「嵌入」，在嵌入配置面板中关闭「允许用户编辑源文件」，开启「启用个人交互」，将生成的嵌入代码发布到内部访问页面。
• 该模式下每个用户的搜索输入、筛选操作仅保存在自己的浏览器会话中，不会写入源文件，也不会同步给其他访问用户，从根本上避免操作互相覆盖的问题。
• 工作表内仅将关键词输入单元格设置为可编辑，其余单元格按后续权限配置锁定即可。

2. 实现按CustomerName字母升序的模糊搜索 #

放弃之前使用的SEARCH+RANK+ROW+VLOOKUP旧方案，改用Excel 365原生动态数组函数，排序逻辑固定无混乱，5万行数据计算延迟低于1秒：

• 在「联系人查询」工作表B2单元格输入提示文字「客户名称关键词」，C2单元格作为关键词输入框。
• 在结果区起始单元格C4输入以下公式，输入完成后按回车即可，不需要下拉填充：

=SORT(
 FILTER(
  ContactTable,
  ISNUMBER(SEARCH("*"&C2&"*",ContactTable[CustomerName])),
  "未查询到匹配联系人信息"
 ),
 MATCH("CustomerName",ContactTable[#Headers],0),
 1
)

• 公式逻辑说明：
  • SEARCH函数实现客户名称片段模糊匹配，不区分大小写
  • FILTER函数提取所有符合匹配条件的完整行数据，无匹配时返回预设提示
  • SORT函数固定按CustomerName字段字母升序排列结果，不存在旧方案的排名混乱问题
  • 公式为动态数组公式，会根据匹配结果数量自动溢出显示所有内容

可选优化功能实现 #

1. 支持其他字段独立搜索 #

如果需要增加City、Account Manager等字段的独立搜索（无需多条件叠加），可通过以下配置实现：

• 在B3单元格设置数据验证下拉列表，选项为客户名称,城市,客户经理，作为搜索字段选择器，C3为对应关键词输入框。
• 将结果区公式替换为以下内容即可：

=LET(
 selected_field, B3,
 search_keyword, C3,
 match_column, SWITCH(selected_field,"客户名称",ContactTable[CustomerName],"城市",ContactTable[City],"客户经理",ContactTable[Account Manager]),
 filter_result, FILTER(ContactTable,ISNUMBER(SEARCH("*"&search_keyword&"*",match_column)),"未查询到匹配结果"),
 SORT(filter_result,MATCH("CustomerName",ContactTable[#Headers],0),1)
)

• 该配置下用户选择对应搜索字段、输入关键词即可查询，所有返回结果依然默认按客户名称字母升序排列。

2. 单元格权限配置 #

不需要完全锁定所有单元格，可实现「允许复制内容、禁止修改公式/数据」的权限：

• 选中所有结果显示区域、关键词输入单元格，右键打开「设置单元格格式」-「保护」选项卡，取消勾选「锁定」，勾选「锁定文本内容」。
• 其余所有单元格（包括结果区存放公式的起始单元格、原始数据表所有单元格）保持「锁定」勾选状态。
• 开启工作表保护，设置保护密码，权限项仅勾选「选定未锁定单元格」「排序」「筛选」，取消其余所有权限。该配置下用户可以正常输入关键词、选中复制查询到的邮箱等内容，但无法修改单元格内的公式和原始数据。

内容的提问来源于stack exchange，提问作者Santa's Little Blep