Thay thế nhiều giá trị excel

Vì chúng tôi phải tìm năm ký tự nên chúng tôi sẽ cần năm hàm SUBSTITUTE lồng nhau. Vì vậy, hãy viết công thức dưới đây trong B3 và kéo xuống

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,INDEX(find,1),","),
INDEX(tìm,2),","),INDEX(tìm,3),","),INDEX(tìm,4),","),INDEX(tìm,5),",")

Tại đây Tìm =$E$3. $E$7

Thay thế nhiều giá trị excel

Bạn có thể thấy rằng dữ liệu đã được làm sạch. Chúng tôi đã tìm thấy thành công nhiều mục trong danh sách và thay thế chúng bằng dấu phẩy. Và chúng tôi đã làm sạch dữ liệu với mỗi số được phân tách bằng dấu phẩy

Làm thế nào nó hoạt động?

Chà, công thức có thể trông phức tạp nhưng kỹ thuật khá đơn giản. Đầu tiên, chúng tôi thay thế một văn bản này bằng một văn bản khác và lấy chuỗi đã thay thế. Bây giờ, chúng tôi lấy chuỗi văn bản được thay thế này làm chuỗi gốc và thực hiện thay thế tiếp theo bằng hàm SUBSTITUTE khác

Nếu chúng ta nói về các điều khoản của ví dụ trên, thì đây là cách nó được giải quyết

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,"@",","),INDEX(tìm,2),","),
INDEX(tìm,3),","),INDEX(tìm,4),","),INDEX(tìm,5),",")=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("1,2-3
INDEX(Tìm,5),",")=SUBSTITUTE(SUBSTITUTE("1,2,3,4,5,6#7;8","#",","),INDEX(Tìm,5),

Ở đây hàm INDEX đóng một vai trò lớn. Hàm INDEX lấy danh sách Tìm và trong lần lặp đầu tiên (INDEX(find,1)) trả về giá trị đầu tiên cần tìm. Trong lần lặp tiếp theo, nó trả về văn bản tiếp theo cần được thay thế bằng INDEX(find,2). Và như thế. Vì vậy, về cơ bản, hàm INDEX được sử dụng làm nguồn cấp dữ liệu cho hàm SUBSTITUTE ở đây cung cấp các giá trị cần tìm và thay thế

ví dụ 2. Tìm nhiều mục và thay thế bằng nhiều giá trị

Trong ví dụ trên, chúng tôi có nhiều giá trị để tìm và thay thế bằng một giá trị. Trong ví dụ này, chúng tôi có nhiều giá trị để tìm và thay thế chúng bằng nhiều giá trị

Thay thế nhiều giá trị excel

Trong ví dụ này, chúng tôi có một danh sách các mục để tìm kiếm và một danh sách các mục để thay thế các mục đó bằng. Bạn sẽ làm điều này như thế nào? . Vì chúng tôi đã sử dụng hàm INDEX để cung cấp hàm SUBSTITUTE với giá trị tìm thấy, nên chúng tôi cũng sẽ sử dụng hàm INDEX để cung cấp giá trị thay thế (thay vì giá trị được mã hóa cứng)

Vì vậy, sử dụng công thức chung thứ hai, chúng tôi viết công thức bên dưới vào ô C3 và kéo nó xuống

Thay thế nhiều giá trị excel

Vì vậy, làm thế nào để làm việc này?

Nó hoạt động giống như công thức đầu tiên. Vì chúng tôi cần tìm và thay thế bốn giá trị, nên chúng tôi có bốn hàm SUBSTITUTE. Để tìm các giá trị, chúng ta có hàm INDEX tương tự. Điều mới là một chức năng INDEX khác mà chúng tôi sử dụng để tìm nạp văn bản thay thế.  

Hãy xem công thức trong ô C4 được giải như thế nào

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4,"Chúng tôi","Bạn"),INDEX(tìm,2),
INDEX(replacer,2)),INDEX(find,3),INDEX(replacer,3)),INDEX(find,4),INDEX(replacer,4))=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("Exceltip. trong cổ phiếu tuyệt vời
bài viết. ""awesome","amazing"),INDEX(find,3),INDEX(replacer,3)),INDEX(find,4),(replacer,4))=SUBSTITUTE(SUBSTITUTE("Exceltip. trong chia sẻ bài viết tuyệt vời. ",". TRONG",". com"),
INDEX(tìm,4),INDEX(thay thế,4))=SUBSTITUTE("Mẹo Excel. com chia sẻ những bài viết hay. ","color", "color")=Exceltip. com chia sẻ những bài viết hay

Ở đây bạn có thể thấy từng bước thay thế được thực hiện như thế nào. Cặp INDEX đầu tiên cung cấp cặp tìm và thay thế đầu tiên (Chúng tôi và Bạn) và điều này xảy ra cho đến chức năng thay thế cuối cùng

Trong các ví dụ trước, chúng tôi chỉ sử dụng hàm INDEX để tìm nhiều văn bản nhưng trong ví dụ này, chúng tôi đã sử dụng hàm INDEX để tìm nạp văn bản thay thế

Hàm SUBSTITUTE phân biệt chữ hoa chữ thường. Nếu bạn muốn một công thức phân biệt chữ hoa chữ thường có thể tìm và thay thế nhiều giá trị, hãy ngắt dòng văn bản gốc trong hàm LOWER hoặc UPPER và ngắt dòng văn bản cũ trong cùng một hàm

Công thức tìm và thay thế nhiều mục không phân biệt chữ hoa chữ thường

=SUBSTITUTE(SUBSTITUTE(UPPER(B3),UPPER(INDEX(find,1)),","),UPPER(INDEX(find,2)),","))

Dưới đây là toàn bộ các ghi chú quan sát sử dụng công thức trong Excel
ghi chú

  1. Văn bản tìm và thay thế phải được sắp xếp trong một bảng. Khi chúng tôi sử dụng chỉ mục để tìm và thay thế, hãy đảm bảo rằng văn bản 'tìm' có cùng chỉ mục với văn bản 'thay thế'. Sẽ tốt hơn nếu có cả hai danh sách cạnh nhau như chúng ta có trong ví dụ thứ hai
  2. Công thức chung có hai hàm SUBSTITUTE. Bạn sẽ cần nhiều chức năng thay thế như nhiều chức năng thay thế mà bạn có để thực hiện
  3. ký tự đại diện giúp trích xuất giá trị từ các chuỗi con
  4. The COUNTIF function supports logical operators like <, >, <>, = but these are used using double quote sign ( " ) with arguments. But if you are using cell reference for the criteria quotes can be ignored.
  5. Đối số tiêu chí nên sử dụng dấu ngoặc kép (") cho văn bản (như "Gillen") hoặc số (như ">50" )
  6. Công thức có thể được sử dụng cho cả văn bản và số

Hy vọng bài viết này về Cách tìm và thay thế nhiều giá trị trong Excel sẽ giải thích được. Tìm thêm các bài viết về tính giá trị và công thức Excel liên quan tại đây. Nếu bạn thích blog của chúng tôi, hãy chia sẻ nó với bạn bè của bạn trên Facebook. Và bạn cũng có thể theo dõi chúng tôi trên Twitter và Facebook. Chúng tôi rất muốn nghe ý kiến ​​của bạn, hãy cho chúng tôi biết cách chúng tôi có thể cải thiện, bổ sung hoặc đổi mới công việc của mình và làm cho nó tốt hơn cho bạn. Viết thư cho chúng tôi tại info@exceltip. com

Những bài viết liên quan

Hàm THAY THẾ so với hàm SUBSTITUTE trong Excel. Các hàm REPLACE và SUBSTITUTE là các hàm bị hiểu lầm nhiều nhất. Để tìm và thay thế một văn bản đã cho, chúng tôi sử dụng chức năng SUBSTITUTE. Trong đó REPLACE được sử dụng để thay thế một số ký tự trong chuỗi…

Thay thế văn bản từ cuối chuỗi bắt đầu từ vị trí biến. Để thay thế văn bản từ cuối chuỗi, chúng ta sử dụng hàm REPLACE. Hàm REPLACE sử dụng vị trí của văn bản trong chuỗi để thay thế

Cách kiểm tra xem một chuỗi có chứa một trong nhiều văn bản trong Excel không. Để kiểm tra xem một chuỗi có chứa bất kỳ văn bản nào hay không, chúng tôi sử dụng công thức này. Chúng tôi sử dụng hàm SUM để tổng hợp tất cả các kết quả khớp và sau đó thực hiện logic để kiểm tra xem chuỗi có chứa bất kỳ chuỗi nào trong số nhiều chuỗi không

Đếm các ô có chứa văn bản cụ thể. Một hàm COUNTIF đơn giản sẽ làm nên điều kỳ diệu. Để đếm số lượng nhiều ô chứa một chuỗi đã cho, chúng tôi sử dụng toán tử ký tự đại diện với hàm COUNTIF

Các bài viết phổ biến

50 phím tắt Excel để tăng năng suất của bạn. Thực hiện nhanh hơn các nhiệm vụ của bạn trong Excel. Những phím tắt này sẽ giúp bạn tăng hiệu quả làm việc trong Excel

Cách sử dụng Hàm VLOOKUP trong Excel. Đây là một trong những hàm phổ biến và được sử dụng nhiều nhất trong excel được sử dụng để tra cứu giá trị từ các phạm vi và trang tính khác nhau.  

Cách sử dụng Hàm IF trong Excel. Câu lệnh IF trong Excel kiểm tra điều kiện và trả về một giá trị cụ thể nếu điều kiện là TRUE hoặc trả về một giá trị cụ thể khác nếu FALSE

Cách sử dụng Hàm SUMIF trong Excel. Đây là một chức năng thiết yếu khác của bảng điều khiển. Điều này giúp bạn tổng hợp các giá trị trên các điều kiện cụ thể

Cách sử dụng Hàm COUNTIF trong Excel. Đếm các giá trị có điều kiện bằng chức năng tuyệt vời này. Bạn không cần lọc dữ liệu của mình để đếm các giá trị cụ thể. Chức năng Countif là điều cần thiết để chuẩn bị bảng điều khiển của bạn