数据验证不通过时在字符串末尾追加文本的脚本实现方案问询

这个需求完全可以实现，根据你使用的是Google Sheets还是微软Excel，可分别通过Apps Script、VBA脚本完成对应的逻辑，以下是具体实现方案：

• 第一步：读取目标单元格区域配置的数据验证规则，提取允许的合法取值集合
• 第二步：遍历区域内所有单元格，判断当前单元格内容是否属于合法取值集合
• 第三步：对不符合规则的内容，自动在末尾追加你指定的文本，符合规则的内容不做改动

操作步骤：

1. 打开目标表格，点击顶部菜单栏「扩展程序」>「Apps Script」进入脚本编辑器
2. 清空默认生成的代码，粘贴以下代码：

function appendSuffixToInvalidValue() {
  // 以下参数可根据你的实际需求修改
  const TARGET_RANGE = "A2:A100"; // 你设置了数据验证的单元格区域
  const SUFFIX = " (*)"; // 需要追加的指定文本
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getRange(TARGET_RANGE);
  // 获取数据验证规则的允许值列表
  const dataValidation = range.getDataValidations();
  const values = range.getValues();
  
  for (let i = 0; i < values.length; i++) {
    const rule = dataValidation[i][0];
    if (!rule) continue; // 没有设置数据验证的单元格跳过
    const allowedValues = rule.getCriteriaValues()[0];
    const cellValue = values[i][0].toString();
    // 判断当前值是否不在允许列表内
    if (!allowedValues.includes(cellValue)) {
      // 已经追加过后缀的不需要重复追加
      if (!cellValue.endsWith(SUFFIX)) {
        range.getCell(i+1, 1).setValue(cellValue + SUFFIX);
      }
    }
  }
}

3. 保存项目后点击运行即可生效，你也可以在Apps Script的「触发器」菜单中设置编辑单元格后自动触发脚本运行。

操作步骤：

1. 打开目标Excel文件，按下Alt+F11调出VBA编辑器
2. 右键左侧窗口你的工作簿名称，选择「插入」>「模块」，粘贴以下代码：

Sub AppendSuffixToInvalidValue()
  ' 以下参数可根据你的实际需求修改
  Const TARGET_RANGE As String = "A2:A100" ' 你设置了数据验证的单元格区域
  Const SUFFIX As String = " (*)" ' 需要追加的指定文本
  
  Dim rng As Range, cell As Range
  Dim allowedValues As Variant
  Dim isValid As Boolean, i As Long
  
  On Error Resume Next
  Set rng = ActiveSheet.Range(TARGET_RANGE)
  On Error GoTo 0
  If rng Is Nothing Then Exit Sub
  
  For Each cell In rng
    If cell.Validation.Type <> xlValidateList Then GoTo NextCell ' 没有设置列表类数据验证跳过
    allowedValues = Split(cell.Validation.Formula1, ",")
    isValid = False
    For i = LBound(allowedValues) To UBound(allowedValues)
      If Trim(allowedValues(i)) = cell.Value Then
        isValid = True
        Exit For
      End If
    Next i
    ' 不符合规则且未追加过后缀的，添加后缀
    If Not isValid And Right(cell.Value, Len(SUFFIX)) <> SUFFIX Then
      cell.Value = cell.Value & SUFFIX
    End If
NextCell:
  Next cell
End Sub

3. 按下F5即可运行脚本，你也可以在表格中插入按钮绑定该宏，实现一键运行。

• 操作前请先备份原始表格数据，避免误操作导致内容丢失
• 上述代码默认适配的是列表类型的数据验证规则，如果你的数据验证是用公式定义的，需要对应修改代码中判断合规性的逻辑
• 代码中已经加了重复追加判断，不会对已经添加过后缀的内容重复添加

内容的提问来源于stack exchange，提问作者Kevin P.