如何在Excel可编辑工作表表格中实现外部数据源的Lookup查找？

以下方案均适配Excel 2016版本，无需全量加载千万级外部表到工作表，可实现单表内自动Lookup+自定义列可编辑的需求。 #

方案1：PowerQuery 单表整合（优先推荐） #

完全符合你偏好的拖拽式关联逻辑，无需VBA、无需额外写单元格公式，可解决你之前遇到的「刷新删除自定义列」问题：

前置配置 #

• 按照你已尝试的操作，先分别创建两个仅连接的PQ查询：
  • 本地Stores表的PQ连接：选中本地表>【数据】选项卡>【从表格】>【关闭并加载到】>勾选【仅创建连接】确认
  • 外部Managers表的PQ连接：新建查询对接外部数据源>仅保留City和Manager两列>【关闭并加载到】>勾选【仅创建连接】确认

合并查询 #

• 右键本地Stores表的PQ连接，选择【合并】
• 上下表分别选择City为匹配列，连接类型选「左外部（第一个表所有行，第二个表匹配行）」，点击确认
• 在PQ编辑器中展开新增的Managers表列，仅勾选Manager字段，取消「使用原始列名作为前缀」选项，确认
• 点击【关闭并加载到】，选择加载到现有工作表的目标位置，生成合并后的表格

关键设置（解决刷新丢失自定义列问题） #

• 右键生成的合并表格，选择【表格】>【外部数据属性】
• 勾选允许在刷新时保留列/排序/筛选/布局更改，确认保存

使用效果 #

• 你可以直接在该表格中新增任意自定义列录入数据，每次点击表格的【刷新】按钮，Manager列会自动从外部表拉取最新匹配值，所有手动录入的内容会完整保留不会被删除
• 由于提前做了列裁剪和左外连接过滤，每次刷新仅拉取和当前Stores表匹配的少量数据，哪怕外部表有千万条记录也不会占用过多内存

方案2：PowerPivot 数据模型关联查询（无额外辅助表） #

如果不需要通过PQ生成新表，可通过数据模型关联实现本地表直接查询外部数据：

• 把本地Stores表和裁剪后的外部Managers表都加载到PowerPivot数据模型，创建两个表City字段的一对多关联
• 在本地Stores表的Manager列输入公式：=CUBEVALUE("ThisWorkbookDataModel","[Managers].[Manager].["&[@City]&"]")
• 刷新数据模型即可自动更新Manager列的匹配值，本地表所有列都可自由编辑，外部表全程不需要加载到工作表

方案3：VBA UDF 单值查询（灵活度最高） #

如果以上方案不符合使用习惯，可通过简易自定义函数实现直接查询：

• 按Alt+F11打开VBA编辑器，插入模块，粘贴如下代码：

Function GetManager(CityName As String) As String
    Dim conn As Object, rs As Object
    Set conn = CreateObject("ADODB.Connection")
    ' 此处修改为你的外部数据源连接字符串，下方为SharePoint列表连接示例
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=你的SharePoint地址;LIST={列表GUID};"
    Set rs = conn.Execute("SELECT Manager FROM Managers WHERE City='" & Replace(CityName, "'", "''") & "'")
    If Not rs.EOF Then GetManager = rs.Fields(0).Value Else GetManager = "未匹配"
    rs.Close: conn.Close
    Set rs = Nothing: Set conn = Nothing
End Function

• 直接在Stores表的Manager列输入公式=GetManager([@City])即可自动返回匹配结果

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