Bạn đang cố gắng tạo câu lệnh IF với văn bản ký tự đại diện, nhưng lần nào nó cũng không thành công? Vấn đề không nằm ở công thức của bạn mà nằm ở chính hàm – Excel IF không hỗ trợ các ký tự đại diện. Tuy nhiên, có một cách để làm cho nó hoạt động cho khớp một phần văn bản và hướng dẫn này sẽ hướng dẫn bạn cách thực hiện.
Bất cứ khi nào bạn muốn thực hiện đối sánh một phần hoặc đối sánh mờ trong Excel, giải pháp rõ ràng nhất là sử dụng các ký tự đại diện. Nhưng nếu một chức năng cụ thể mà bạn cần sử dụng không hỗ trợ các ký tự đại diện thì sao? Đáng buồn thay, Excel IF là một trong những hàm như vậy. Điều này đặc biệt đáng thất vọng khi xem xét rằng các hàm “có điều kiện” khác như COUNTIF, SUMIF và AVERAGEIFS hoạt động với các ký tự đại diện hoàn toàn tốt.
May mắn thay, nó không phải là trở ngại có thể ngăn cản một người dùng Excel sáng tạo 🙂 Bằng cách kết hợp IF với các hàm khác, bạn có thể buộc nó đánh giá kết quả khớp một phần và nhận được một thay thế tốt cho công thức ký tự đại diện Excel IF.
Tại sao hàm IF trong Excel với ký tự đại diện không hoạt động
Trong bảng mẫu bên dưới, giả sử bạn muốn kiểm tra xem các ID trong cột đầu tiên có chứa chữ cái “A” hay không. Nếu tìm thấy – hiển thị “Có” trong cột B, nếu không – hiển thị “Không”.
Có vẻ như bao gồm văn bản ký tự đại diện trong bài kiểm tra logic sẽ là một giải pháp dễ dàng:
=IF(A2="*a*","Yes", "No")
Nhưng đáng tiếc là nó không hoạt động. Công thức trả về “Không” cho tất cả các ô, ngay cả những ô có chứa “A”:
Tại sao câu lệnh IF ký tự đại diện không thành công? Từ tất cả các lần xuất hiện, Excel không nhận ra các ký tự đại diện được sử dụng với dấu bằng hoặc dấu hiệu khác toán tử logic. Nếu bạn xem kỹ danh sách các chức năng hỗ trợ ký tự đại diện, bạn sẽ nhận thấy rằng cú pháp của chúng giả định một văn bản ký tự đại diện xuất hiện trực tiếp trong một đối số như:
=COUNTIF(A2:A10, "*a*")
Câu lệnh IF trong Excel với văn bản ký tự đại diện
Bây giờ bạn đã biết lý do tại sao công thức IF ký tự đại diện không thành công, hãy thử tìm cách làm cho nó hoạt động. Đối với điều này, chúng tôi sẽ chỉ cần nhúng một hàm chấp nhận các ký tự đại diện trong kiểm tra logic của IF, cụ thể là Hàm COUNTIF:
IF (COUNTIF (ô, “*bản văn* “), value_if_true, value_if_false)
Với cách tiếp cận này, IF không gặp vấn đề gì với việc hiểu các ký tự đại diện và xác định hoàn hảo các ô chứa “A” hoặc “a” (vì COUNTIF không phân biệt chữ hoa chữ thường):
=IF(COUNTIF(A2, "*a*"),"Yes", "No")
Công thức này chuyển đến ô B2 hoặc bất kỳ ô nào khác trong hàng 2 và sau đó bạn có thể kéo công thức xuống bao nhiêu ô nếu cần:
Giải pháp này cũng có thể được sử dụng để xác định vị trí chuỗi của một mẫu cụ thể. Giả sử chỉ những ID bao gồm 2 nhóm 2 ký tự được phân tách bằng dấu gạch nối là hợp lệ, bạn có thể sử dụng dấu “?? – ??” chuỗi ký tự đại diện để xác định chúng:
=IF(COUNTIF(A2, "??-??"), "Valid", "")
Công thức này hoạt động như thế nào:
Đối với kiểm tra logic của IF, chúng tôi sử dụng hàm COUNTIF để đếm số ô phù hợp với chuỗi ký tự đại diện được chỉ định. Vì phạm vi tiêu chí là một ô duy nhất (A2), kết quả luôn là 1 (tìm thấy kết quả phù hợp) hoặc 0 (không tìm thấy kết quả phù hợp). Cho rằng 1 tương đương với TRUE và 0 là FALSE, công thức trả về “Hợp lệ” (value_if_true) khi số lượng là 1 và chuỗi trống (value_if_false) khi số lượng là 0.
IF ISNUMBER TÌM KIẾM công thức cho các kết quả phù hợp từng phần
Một cách khác để buộc Excel IF hoạt động đối với kết quả khớp từng phần văn bản là đưa hàm FIND hoặc SEARCH vào kiểm tra logic. Sự khác biệt là TÌM THẤY phân biệt chữ hoa chữ thường trong khi TÌM KIẾM không phải.
Vì vậy, tùy thuộc vào việc bạn muốn coi chữ thường và chữ hoa là các ký tự giống nhau hay khác nhau, một trong các công thức này sẽ hoạt động:
Trường hợp không nhạy cảm công thức đối sánh từng phần:
IF (ISNUMBER (SEARCH (“bản văn“, ô)), value_if_true, value_if_false)
Trường hợp nhạy cảm công thức đối sánh từng phần:
IF (ISNUMBER (FIND (“bản văn“, ô)), value_if_true, value_if_false)
Vì cả hai hàm đều được thiết kế để thực hiện kiểu so khớp “ô chứa”, nên ký tự đại diện không thực sự cần thiết trong trường hợp này.
Ví dụ: để phát hiện các ID có chứa “A” hoặc “a”, công thức là:
=IF(ISNUMBER(SEARCH("A", A2)), "Yes", "No")
Để chỉ tìm kiếm chữ hoa “A” và bỏ qua “a”, công thức là:
=IF(ISNUMBER(FIND("A", A2)), "Yes", "No")
Trong B6 trong ảnh chụp màn hình bên dưới, bạn có thể thấy sự khác biệt trong kết quả:
Công thức này hoạt động như thế nào:
Ở trung tâm của công thức, có sự kết hợp của ISNUMBER và SEARCH (hoặc FIND):
ISNUMBER(SEARCH("A", A2))
Hàm TÌM KIẾM tìm kiếm văn bản được chỉ định (“A” trong ví dụ này) và trả về vị trí của nó trong một chuỗi trong A2. Nếu không tìm thấy văn bản, lỗi #VALUE được trả về. Vì cả SEARCH và FIND đều được thiết kế để thực hiện kiểu so khớp “ô chứa”, nên các ký tự đại diện không thực sự cần thiết trong trường hợp này.
Các THÁNG NĂM hàm chuyển đổi một số thành TRUE và bất kỳ giá trị nào khác bao gồm cả lỗi thành FALSE. Giá trị lôgic đi trực tiếp vào kiểm tra lôgic của IF. Trong trường hợp của chúng tôi, A2 chứa “A”, vì vậy ISNUMBER trả về TRUE:
IF(TRUE, "Yes", "No")
Kết quả là IF trả về giá trị được đặt cho value_if_true đối số, là “Có”.
Câu lệnh IF OR trong Excel với các ký tự đại diện
Cần xác định các ô chứa một trong các chuỗi văn bản ký tự đại diện? Trong trường hợp này, bạn có thể kết hợp cổ điển Câu lệnh IF OR với công thức COUNTIF hoặc ISNUMBER SEARCH đã thảo luận ở trên.
Ví dụ: để tìm kiếm “aa” HOẶC “bb” trong A2 bỏ qua chữ hoa và trả về “Có” nếu được tìm thấy, hãy sử dụng một trong các công thức sau:
=IF(OR(ISNUMBER(SEARCH("aa", A2)), ISNUMBER(SEARCH("bb", A2))), "Yes", "")
hoặc là
=IF(OR(COUNTIF(A2, "*aa*"), COUNTIF(A2, "*bb*")), "Yes", "")
Thêm hai hàm COUNTIF cũng sẽ hoạt động. Trong trường hợp này, dấu cộng hoạt động giống như Toán tử HOẶC:
=IF(COUNTIF(A3, "*aa*") + COUNTIF(A3, "*bb*"), "Yes", "")
Thay vì mã hóa cứng các chuỗi ký tự đại diện trong công thức, bạn có thể nhập chúng vào các ô riêng biệt, chẳng hạn D2 và F2, như được hiển thị trong ảnh chụp màn hình bên dưới. Xin lưu ý rằng các tham chiếu ô này bị khóa với dấu $ để công thức sao chép chính xác vào các ô bên dưới:
=IF(OR(COUNTIF(A2, "*"&$D$2&"*"), COUNTIF(A2, "*"&$F$2&"*")), "Yes", "")
Các công thức trên hoạt động tốt cho 2 kết quả phù hợp từng phần, nhưng nếu bạn đang tìm kiếm 3 kết quả trở lên, chúng sẽ trở nên quá dài. Trong trường hợp này, có lý do để tiếp cận nhiệm vụ theo cách khác:
Cung cấp nhiều chuỗi con cho hàm SEARCH trong một mảng hằng số, đếm các số trả về và kiểm tra xem kết quả có lớn hơn không (có nghĩa là ít nhất một trong các chuỗi con nếu được tìm thấy):
=IF(COUNT(SEARCH({"aa","bb"}, A2))>0, "Yes", "")
Bằng cách này, bạn sẽ nhận được chính xác kết quả tương tự với một công thức nhỏ gọn hơn:
Công thức IF AND trong Excel với các ký tự đại diện
Khi bạn muốn kiểm tra xem một ô có chứa hai hoặc nhiều chuỗi con khác nhau hay không, cách dễ nhất là sử dụng COUNTIFS chức năng với các ký tự đại diện cho bài kiểm tra logic.
Giả sử bạn muốn định vị các ô trong cột A có chứa cả “b” VÀ “2”. Để hoàn thành, hãy sử dụng “* b *” và “* 2 *” cho tiêu chí của COUNTIFS và A2 cho phạm vi tiêu chí:
=IF(COUNTIFS(A2, "*b*", A2, "*2*"), "Yes", "")
Một cách khác là sử dụng IF AND công thức cùng với ISNUMBER SEARCH:
=IF(AND(ISNUMBER(SEARCH("b", A2)), ISNUMBER(SEARCH("2", A2))), "Yes", "")
Mặc dù chúng tôi không bao gồm bất kỳ ký tự đại diện nào trong công thức này, nhưng nó hoạt động giống như tìm kiếm hai chuỗi ký tự đại diện (“* b *” và “* 2 *”) trong cùng một ô.
Tất nhiên, không có gì ngăn cản bạn nhập các giá trị tìm kiếm vào các ô được xác định trước, D2 và F2 trong trường hợp của chúng tôi và cung cấp các tham chiếu ô cho công thức:
=IF(AND(ISNUMBER(SEARCH($D$2, A2)), ISNUMBER(SEARCH($F$2, A2))), "Yes", "")
Nếu bạn thích sử dụng các công thức nhỏ gọn hơn bất cứ khi nào có thể, thì bạn có thể thích cách tiếp cận hằng mảng hơn. Công thức IF COUNT SEARCH rất giống trong ví dụ trước, nhưng vì lần này cả hai chuỗi con phải xuất hiện trong A2, chúng tôi kiểm tra xem số đếm có bằng 2 không:
=IF(COUNT(SEARCH({"b","2"}, A2))=2, "Yes", "")
Đây là những phương pháp chính của việc sử dụng ký tự đại diện trong câu lệnh IF trong Excel. Nếu bạn biết bất kỳ giải pháp nào khác, những người dùng khác chắc chắn sẽ đánh giá cao nếu bạn chia sẻ trải nghiệm của mình trong nhận xét. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!
The post Câu lệnh ký tự đại diện Excel IF cho khớp một phần văn bản appeared first on Kế Toán MVB | Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng.
source https://ketoanmvb.com/cau-lenh-ky-tu-dai-dien-excel-if-cho-khop-mot-phan-van-ban.html
Không có nhận xét nào:
Đăng nhận xét