基于另一列值修改单元格值并支持手动临时修改的Excel问询

核心思路 #

通过「辅助列存手动值+主列公式优先读取手动值」的逻辑，既实现U列导入数据时Q列自动更新，又允许临时手动修改且不破坏自动更新机制。

方案一：辅助列+公式（最通用，适用于Excel/Google Sheets） #

1. 新增辅助列：在表格中新增一列（比如列V），用来存放临时手动修改的状态值，之后可以隐藏该列。
2. 设置Q列公式
   • 单个单元格公式（以Q1为例）：

     =IF(V1<>"", V1, XLOOKUP(U1, {"REOK","INIC","PLAN","VERI","OPER","FISC","OTRA","TERC","SERV","PROG","FREN","INFO","INEX","IM01","IM02","IM03","IM04","IM05","CANC"}, {"CONCLUSIVE RESOLVED","IN PROCESS","IN PROCESS","IN PROCESS","IN PROCESS","IN PROCESS","IN PROCESS","IN PROCESS","IN PROCESS","IN PROCESS","CONCLUSIVE DENIED","CONCLUSIVE DENIED","CONCLUSIVE DENIED","CONCLUSIVE DENIED","CONCLUSIVE DENIED","CONCLUSIVE DENIED","CONCLUSIVE DENIED","CONCLUSIVE DENIED","CONCLUSIVE DENIED"}, ""))
     

   • 整列数组公式（一次性应用到所有行）：
     Google Sheets版本：

     =ARRAYFORMULA(IF(V1:V<>"", V1:V, XLOOKUP(U1:U, {"REOK","INIC","PLAN","VERI","OPER","FISC","OTRA","TERC","SERV","PROG","FREN","INFO","INEX","IM01","IM02","IM03","IM04","IM05","CANC"}, {"CONCLUSIVE RESOLVED","IN PROCESS","IN PROCESS","IN PROCESS","IN PROCESS","IN PROCESS","IN PROCESS","IN PROCESS","IN PROCESS","IN PROCESS","CONCLUSIVE DENIED","CONCLUSIVE DENIED","CONCLUSIVE DENIED","CONCLUSIVE DENIED","CONCLUSIVE DENIED","CONCLUSIVE DENIED","CONCLUSIVE DENIED","CONCLUSIVE DENIED","CONCLUSIVE DENIED"}, "")))
     

     Excel版本：

     =IF(V:V<>"", V:V, XLOOKUP(U:U, {"REOK","INIC","PLAN","VERI","OPER","FISC","OTRA","TERC","SERV","PROG","FREN","INFO","INEX","IM01","IM02","IM03","IM04","IM05","CANC"}, {"CONCLUSIVE RESOLVED","IN PROCESS","IN PROCESS","IN PROCESS","IN PROCESS","IN PROCESS","IN PROCESS","IN PROCESS","IN PROCESS","IN PROCESS","CONCLUSIVE DENIED","CONCLUSIVE DENIED","CONCLUSIVE DENIED","CONCLUSIVE DENIED","CONCLUSIVE DENIED","CONCLUSIVE DENIED","CONCLUSIVE DENIED","CONCLUSIVE DENIED","CONCLUSIVE DENIED"}, ""))
     

3. 操作逻辑：
   • 正常情况下，V列为空，Q列自动根据U列的代码映射对应状态；
   • 需要临时修改时，取消隐藏V列，在对应行输入状态值，Q列会立即显示手动值；
   • 后续U列导入新数据时，只要V列对应行无值，Q列会自动刷新为最新映射结果。

方案二：自定义映射表（方便后续维护映射关系） #

如果映射关系需要频繁修改，可以单独维护一个映射表，避免每次修改Q列公式：

1. 创建映射表：在表格的空白区域（比如新建Sheet2，A列存代码，B列存对应状态）：

   A	B
   REOK	CONCLUSIVE RESOLVED
   INIC	IN PROCESS
   PLAN	IN PROCESS
   ...	...

2. 定义名称：
   • Excel：选中映射表区域，点击「公式」→「定义名称」，命名为StatusMap；
   • Google Sheets：点击「数据」→「命名范围」，命名为StatusMap，引用映射表区域；
3. 修改Q列公式：
   单个单元格公式：

   =IF(V1<>"", V1, XLOOKUP(U1, StatusMap[代码], StatusMap[状态], ""))
   

   整列数组公式同理，后续修改映射关系直接更新Sheet2的表格即可，无需改动Q列公式。

方案三：脚本触发（适用于Google Sheets，无需辅助列） #

如果不想用辅助列，可以通过Google Apps Script实现「仅未手动修改的Q列随U列更新」：

1. 打开Google Sheets，点击「扩展程序」→「Apps脚本」；
2. 粘贴以下代码：

   function onEdit(e) {
     const range = e.range;
     const sheet = range.getSheet();
     // 仅处理U列（第21列）的编辑操作
     if (range.getColumn() !== 21) return;
     
     const row = range.getRow();
     const qCell = sheet.getRange(row, 17); // Q列是第17列
     // 仅当Q列单元格保留公式时（未手动修改），才自动更新
     if (!qCell.getFormula()) return;
     
     const uValue = range.getValue();
     // 定义映射关系
     const statusMap = {
       "REOK": "CONCLUSIVE RESOLVED",
       "INIC": "IN PROCESS",
       "PLAN": "IN PROCESS",
       "VERI": "IN PROCESS",
       "OPER": "IN PROCESS",
       "FISC": "IN PROCESS",
       "OTRA": "IN PROCESS",
       "TERC": "IN PROCESS",
       "SERV": "IN PROCESS",
       "PROG": "IN PROCESS",
       "FREN": "CONCLUSIVE DENIED",
       "INFO": "CONCLUSIVE DENIED",
       "INEX": "CONCLUSIVE DENIED",
       "IM01": "CONCLUSIVE DENIED",
       "IM02": "CONCLUSIVE DENIED",
       "IM03": "CONCLUSIVE DENIED",
       "IM04": "CONCLUSIVE DENIED",
       "IM05": "CONCLUSIVE DENIED",
       "CANC": "CONCLUSIVE DENIED"
     };
     // 更新Q列值
     qCell.setValue(statusMap[uValue] || "");
   }
   

3. 保存脚本，返回表格。后续U列更新时，未手动修改过的Q列会自动映射状态；手动修改过的Q列（公式已被删除）不会被覆盖。

内容的提问来源于stack exchange，提问作者Mateo Di napoli