Hướng dẫn find and replace multiple values in google sheets - tìm và thay thế nhiều giá trị trong google trang tính

=SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[B3,"Bobby","Bob"],"Samantha","Sam"],"Robert","Rob"]

=SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[B3,INDEX[$E$3:$E$5,1],INDEX[$F$3:$F$5,1]],
INDEX[$E$3:$E$5,2],INDEX[$F$3:$F$5,2]],INDEX[$E$3:$E$5,3],INDEX[$F$3:$F$5,3]]

Sao chép tệp mẫu với lời giải thích

Vấn đề

Thách thức là: Tìm và thay thế nhiều giá trị trong đầu vào của nhiều ô.

ArrayFormula's

Các giải pháp mà tôi tài khoản là giải pháp mảng phải là:

1. Dựa trên phạm vi mở
2. Không cần phải kéo công thức xuống
3. Không cần sửa đổi công thức khi các mục mới trong danh sách xuất hiện

Những bài kiểm tra này phải được thông qua:

• Là ArrayFormulaArrayFormula
• Người dùng có thể đặt độ nhạy của trường hợpCase Sensitivity
• Thay thế biểu tượng cảm xúcEmojis
• Thay thế các ký tự đặc biệt $\[]., v.v.Special Chars $\[]. etc.
• Sự cố. Hoạt động cho các hàng dữ liệu 10k. Works for 10K rows of data
• Sự cố. Hoạt động cho 2K thay thế. Works for 2K replacements

Script

Tôi khuyên bạn nên sử dụng tập lệnh không dựa trên Regex trong trường hợp này. Thuật toán này tìm thấy và thay thế văn bản bằng chars:

Cách sử dụng

Sử dụng như một công thức thông thường từ tờ:

=substitutes[A12:A;List!A1:B]

Mã số

Lưu mã này để sử dụng công thức ở trên:

/**
 * Substitutes in every entry in array
 * Text from prefilled array
 *
 * @param {array} input The array of strings.
 * @param {array} subTable The array of string pairs: search texts / replace texts.
 * @param {boolean} caseSensitive [optional=false] 
 * TRUE to match Apple and apple as different words
 * @return The input with all replacement made
 * @customfunction
 */
function substitutes[input, subTable,caseSensitive] {
  //  default behavior it is not case sensitive
  caseSensitive = caseSensitive || false;
  // if the input is not a list, become a list */
  if[ typeof input != "object" ] {
    input = [ input ];
  }
  var res = [], text;
  for [var i = 0; i < input.length; i++] {
    // force each array element in the input be a string
    text = input[i].toString[];
    for [var ii = 0; ii < subTable.length; ii++] {
      text = replaceAll_[
        text, 
        subTable[ii][0], 
        subTable[ii][1], 
        caseSensitive];
    }
    res.push[text];
  }
  return res;
}


/***
 * JavaScript Non-regex Replace
 * 
 * Original code sourse:
 * //stackoverflow.com/a/56989647/5372400
 */
function replaceAll_[str, find, newToken, caseSensitive] {
    var i = -1;
    // sanity check & defaults
    if [!str] {
        // Instead of throwing, act as 
        // COALESCE if find == null/empty and str == null
        if [[str == null] && [find == null]]
            return newToken;
        return str;
    }
    if [!find || find === '']{ return str; }
    if [find === newToken] { return str; }
    caseSensitive = caseSensitive || false;
    find = !caseSensitive ? find.toLowerCase[] : find;
    // search process, search by char
    while [[
        i = [!caseSensitive ? str.toLowerCase[] : str].indexOf[
            find, i >= 0 ? i + newToken.length : 0
        ]] !== -1
    ] {
        str = str.substring[0, i] +
            newToken +
            str.substring[i + find.length];
    } 
    return str;
}

Công thức quái vật

Tôi đã sử dụng thuật toán Regex để giải quyết nó với các hàm gốc. Phương pháp này không được khuyến nghị vì nó làm chậm bảng tính của bạn.

Công thức là:

=INDEX[SUBSTITUTE[REGEXREPLACE[TRANSPOSE[QUERY[TRANSPOSE[IFERROR[SPLIT[SUBSTITUTE[TRANSPOSE[QUERY[TRANSPOSE[IFERROR[VLOOKUP[SPLIT[REGEXREPLACE[A12:A;SUBSTITUTE[REGEXREPLACE[REGEXREPLACE[A12:A;"[?i]"&SUBSTITUTE[SUBSTITUTE[QUERY[FILTER[REGEXREPLACE[List!A1:A;"[\\|\+|\*|\?|\[|\^|\]|\$|\[|\]|\{|\}|\=|\!|\|\||\:|\-]";"\\$1"]&"𑇦";List!A1:A""];;2^99];"𑇦 ";"|"];"𑇦";""];"𑇡"];"[\\|\+|\*|\?|\[|\^|\]|\$|\[|\]|\{|\}|\=|\!|\|\||\:|\-]";"\\$1"];"𑇡";"[.*]"];INDEX[REGEXREPLACE[TRIM[TRANSPOSE[QUERY[TRANSPOSE[IF[SEQUENCE[COUNTA[INDEX[LEN[REGEXREPLACE[REGEXREPLACE[A12:A;"[?i]"&SUBSTITUTE[SUBSTITUTE[QUERY[FILTER[REGEXREPLACE[List!A1:A;"[\\|\+|\*|\?|\[|\^|\]|\$|\[|\]|\{|\}|\=|\!|\|\||\:|\-]";"\\$1"]&"𑇦";List!A1:A""];;2^99];"𑇦 ";"|"];"𑇦";""];"𑇡"];"[^𑇡]";""]]/2]];MAX[INDEX[LEN[REGEXREPLACE[REGEXREPLACE[A12:A;"[?i]"&SUBSTITUTE[SUBSTITUTE[QUERY[FILTER[REGEXREPLACE[List!A1:A;"[\\|\+|\*|\?|\[|\^|\]|\$|\[|\]|\{|\}|\=|\!|\|\||\:|\-]";"\\$1"]&"𑇦";List!A1:A""];;2^99];"𑇦 ";"|"];"𑇦";""];"𑇡"];"[^𑇡]";""]]/2]]]-[SEQUENCE[COUNTA[INDEX[LEN[REGEXREPLACE[REGEXREPLACE[A12:A;"[?i]"&SUBSTITUTE[SUBSTITUTE[QUERY[FILTER[REGEXREPLACE[List!A1:A;"[\\|\+|\*|\?|\[|\^|\]|\$|\[|\]|\{|\}|\=|\!|\|\||\:|\-]";"\\$1"]&"𑇦";List!A1:A""];;2^99];"𑇦 ";"|"];"𑇦";""];"𑇡"];"[^𑇡]";""]]/2]]]-1]*MAX[INDEX[LEN[REGEXREPLACE[REGEXREPLACE[A12:A;"[?i]"&SUBSTITUTE[SUBSTITUTE[QUERY[FILTER[REGEXREPLACE[List!A1:A;"[\\|\+|\*|\?|\[|\^|\]|\$|\[|\]|\{|\}|\=|\!|\|\||\:|\-]";"\\$1"]&"𑇦";List!A1:A""];;2^99];"𑇦 ";"|"];"𑇦";""];"𑇡"];"[^𑇡]";""]]/2]]