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

Question

Quay trở lại danh sách công thức Excel

Nội dung chính Show

Thay thế nhiều giá trị
Công thức hoạt động như thế nào?
Thay thế nhiều giá trị bằng cách sử dụng tham chiếu
Thay thế nhiều giá trị trong Google Sheets
Sao chép tệp mẫu với lời giải thích
Vấn đề
ArrayFormula's
Công thức quái vật
Các giải pháp khác
Công thức lồng nhau
Công thức bạn cần kéo xuống cho kết quả
Tôi có thể tìm và thay thế nhiều giá trị cùng một lúc không?
Làm cách nào để thay thế tất cả các giá trị trong một cột trong Google Sheets?
Tôi có thể tìm kiếm nhiều giá trị trong Google Sheets không?
Làm thế nào để bạn thay thế nhiều ô cùng một lúc?

Tải xuống ví dụ sổ làm việc

Tải xuống sổ làm việc ví dụ

Hướng dẫn này sẽ trình bày cách tìm và thay thế nhiều giá trị bằng các hàm thay thế lồng nhau.

Thay thế nhiều giá trị

Để tìm và thay thế nhiều giá trị trong Excel, hãy sử dụng hàm thay thế trong công thức lồng nhau:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,"Bobby","Bob"),"Samantha","Sam"),"Robert","Rob")

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

Công thức hoạt động như thế nào?

Công thức này làm tổ nhiều hàm thay thế với nhau để tìm và thay thế các giá trị mỗi lần. Kết quả từ sự thay thế đầu tiên được đưa vào chức năng thay thế tiếp theo, v.v. Trong ví dụ của chúng tôi, ba sự thay thế được thực hiện. Để thêm nhiều trạm biến áp, chỉ cần tổ thay thế tổ trong công thức.

Thay thế nhiều giá trị bằng cách sử dụng tham chiếu

Một cách tiếp cận khác là sử dụng phạm vi tham chiếu thay vì gõ các giá trị trong công thức.

=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))

Phương pháp này hoạt động giống như phương thức phổ biến ngoại trừ nó sử dụng hàm chỉ mục để tham chiếu phạm vi tìm và thay thế với các giá trị. Hai hàm chỉ số là cần thiết cho mỗi thay thế (một cho giá trị tìm và một cho giá trị thay thế).

Thay thế nhiều giá trị trong Google Sheets

Các công thức này hoạt động chính xác giống nhau trong Google Sheets như trong Excel.

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à:

Dựa trên phạm vi mở
Không cần phải kéo công thức xuống
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:
 * https://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))<=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));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))&"𑇤";));;2^99)));" ?𑇣";"$")));"𑇤");{List!A1:A\List!B1:B};2;)&"𑇩"));;2^99));"𑇩 ";"𑇩")&"𝅘";"𑇩")&SPLIT(REGEXREPLACE(A12:A;"(?i)"&SUBSTITUTE(SUBSTITUTE(QUERY(FILTER(REGEXREPLACE(List!A1:A;"(\\|\+|\*|\?|\[|\^|\]|\$|\(|\)|\{|\}|\=|\!|\<|\>|\||\:|\-)";"\\$1")&"𑇦";List!A1:A<>"");;2^99);"𑇦 ";"|");"𑇦";"");"𑇡")&"𝅘";"𑇡")))&"𝅗";;2^99));"𝅗 *";"");"𝅘";""))

Các giải pháp khác

Công thức lồng nhau

Nested SUBSTITUTE hoặc REGEXREPLACE Công thức như đã được ghi nhận trong các câu trả lời khác.

Công thức bạn cần kéo xuống cho kết quả

Đây là một công thức mẫu. Logic cơ bản - Chia văn bản thành các phần → sửa đổi các phần riêng lẻ → để tham gia kết quả mới.

Công thức này phải được sao chép:

=JOIN(" ";
ArrayFormula(
IFERROR(VLOOKUP(TRANSPOSE(SPLIT(A1;" "));List!A:B;2;0);TRANSPOSE(SPLIT(A1;" ")))))

Tôi có thể tìm và thay thế nhiều giá trị cùng một lúc không?

Cách dễ nhất để tìm và thay thế nhiều mục trong Excel là sử dụng hàm thay thế. Logic của công thức rất đơn giản: bạn viết một vài hàm riêng lẻ để thay thế một giá trị cũ bằng một hàm mới.using the SUBSTITUTE function. The formula's logic is very simple: you write a few individual functions to replace an old value with a new one.

Làm cách nào để thay thế tất cả các giá trị trong một cột trong Google Sheets?

Có ba cách để sử dụng tìm và thay thế trong Google Sheets: Điều hướng để chỉnh sửa-> Tìm và thay thế.Sử dụng phím tắt Ctrl+H (trên PC Windows) hoặc CMD+Shift+H (trên Mac) bằng cách sử dụng phím tắt CTRL+F (trên PC Windows) hoặc CMD+F (trên Mac)Navigating to Edit->Find and Replace. Using the keyboard shortcut CTRL+H (on a Windows PC) or CMD+SHIFT+H (on a Mac) Using the keyboard shortcut CTRL+F (on a Windows PC) or CMD+F (on a Mac)

Tôi có thể tìm kiếm nhiều giá trị trong Google Sheets không?

Chúng ta có thể sử dụng nhiều chuỗi tìm kiếm trong một chức năng/công thức tìm kiếm duy nhất trong Google Sheets.Không cần phải tổ chức năng tìm kiếm để thực hiện nhiều tìm kiếm.Tìm kiếm lồng nhau là về việc sử dụng chức năng tìm kiếm theo cách lồng nhau trong Google Sheets để kết hợp nhiều hơn một chuỗi tìm kiếm.. No need to nest the SEARCH function to do multiple searches. Nested search is about using the SEARCH function in a nested way in Google Sheets to incorporate more than one search string.

Làm thế nào để bạn thay thế nhiều ô cùng một lúc?

Để thực hiện việc này, nhấp vào menu "Chỉnh sửa", sau đó nhấp vào "Tìm và thay thế".Trong trường "Tìm những gì", nhập văn bản hoặc giá trị mà bạn muốn thay thế.Trong trường "Thay thế bằng", nhập văn bản hoặc giá trị mà bạn muốn sử dụng để thay thế.Sau đó, nhấp vào nút "Thay thế tất cả".

programming sheet SUBSTITUTE Google Sheets

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

Thay thế nhiều giá trị

Công thức hoạt động như thế nào?

Thay thế nhiều giá trị bằng cách sử dụng tham chiếu

Thay thế nhiều giá trị trong Google Sheets

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

Vấn đề

ArrayFormula's

Script

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

Các giải pháp khác

Công thức lồng nhau

Công thức bạn cần kéo xuống cho kết quả

Tôi có thể tìm và thay thế nhiều giá trị cùng một lúc không?

Làm cách nào để thay thế tất cả các giá trị trong một cột trong Google Sheets?

Tôi có thể tìm kiếm nhiều giá trị trong Google Sheets không?

Làm thế nào để bạn thay thế nhiều ô cùng một lúc?

Bài Viết Liên Quan

Quảng Cáo

Có thể bạn quan tâm

Toplist được quan tâm

Quảng cáo

Xem Nhiều

Quảng cáo

Chúng tôi

Điều khoản

Trợ giúp

Mạng xã hội