更新Excel数据验证列表并批量替换十万行旧数据需求

一、更新数据验证下拉列表 #

• 打开新参数表（ID对应部门的工作表），选中所有ID的有效数据区域，按Ctrl+C复制
• 切换到主表，按住Ctrl选中所有需要更新数据验证的列（如users、department列）
• 点击菜单栏「数据」→「数据验证」，在弹出窗口的「来源」框中，删除原有引用，粘贴新参数表的ID区域引用（或直接用鼠标选中新参数表的ID区域），点击「确定」完成批量设置

二、批量替换主表旧数据（两种可选方案） #

方案1：Power Query高效处理（推荐10万+行数据） #

• 选中主表的全部数据区域（包含表头），点击「数据」→「从表格/区域」，勾选「我的表格有标题」后进入Power Query编辑器
• 加载名称-ID映射表：切换到原参数表（名称对应ID的工作表），同样选中数据区域（含表头），点击「数据」→「从表格/区域」加载到Power Query
• 回到主表的查询界面，点击「添加列」→「合并查询」：
  • 合并窗口中，主表选择需要替换的列（如users），映射表选择对应的名称列，合并类型选「仅使用匹配的行」，点击「确定」
  • 点击合并列右侧的展开按钮，只勾选ID列，点击确定
  • 删除原users列，将新生成的ID列重命名为users
• 对department等其他需要替换的列重复上述合并、展开、重命名操作
• 完成后点击「主页」→「关闭并上载」，选择覆盖原主表的数据区域，即可完成批量替换

方案2：查找替换批量导入映射（适合参数条目较少的场景） #

• 在原参数表中整理出「名称-ID」两列数据（名称列在前，ID列在后），选中这两列按Ctrl+C复制
• 切换到主表，选中需要替换的列（如users），点击「开始」→「查找和选择」→「替换」
• 点击替换窗口的「选项」，勾选「单元格匹配」，再点击「全部替换」旁的下拉箭头，选择「从剪贴板导入」
• 系统会自动导入所有名称-ID的替换规则，点击「全部替换」即可完成该列替换；其他列重复此操作

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