Excel nếu ô chứa một phần văn bản thì trả về giá trị
|
Trong ví dụ này, nếu ô A1 chứa chuỗi “abc” trong bất kỳ phần nào của ô thì nó sẽ trả về Có, nếu không nó sẽ trả về Không
Ví dụ sau đây cho thấy cách sử dụng công thức này trong thực tế Show Thí dụ. Kiểm tra xem ô có chứa một phần văn bản trong Excel khôngGiả sử chúng ta có tập dữ liệu sau trong Excel cho biết số điểm được ghi bởi các cầu thủ bóng rổ khác nhau  Chúng ta có thể sử dụng công thức sau để kiểm tra xem giá trị trong cột Đội có chứa một phần văn bản “guar” không =IF(COUNTIF(A2,"*guar*"),"Yes","No")Ta có thể gõ công thức này vào ô C2 rồi copy và dán xuống các ô còn lại trong cột C Bất kỳ hàng nào có phần văn bản “guar” trong cột Đội đều nhận được giá trị Bảo vệ trong cột mới trong khi tất cả các hàng khác nhận giá trị Không bảo vệ Hàm IF khi được sử dụng để so sánh các giá trị văn bản, sẽ kiểm tra sự khớp chính xác. Nhưng trong bài đăng trên blog này, chúng tôi muốn kiểm tra sự trùng khớp một phần. Chúng tôi quan tâm nếu ô chứa văn bản ở bất kỳ đâu trong đó Ví dụ của chúng tôi, chúng tôi có một danh sách các địa chỉ như hình dưới đây. Và chúng tôi muốn hiển thị từ 'local' nếu địa chỉ chứa văn bản 'CB2' Mục lục Ví dụ trường hợpĐược liệt kê trước trong ảnh ví dụ, chúng tôi có kho nước hoa của mình. Các loại nước hoa được sắp xếp theo thứ tự abc theo tên và vấn đề nhỏ ở đây là thương hiệu cũng được đề cập trong cùng một ô. Bây giờ nếu chúng ta tìm nước hoa theo nhãn hiệu, chúng ta không thể làm điều đó một cách dễ dàng Một sự khởi đầu có thể là tách các thương hiệu khỏi tên thành một cột riêng biệt nhưng ngay cả khi đó, chúng tôi vẫn gặp sự cố. Nếu chúng ta sắp xếp các tên thương hiệu theo thứ tự bảng chữ cái và tìm kiếm một loại nước hoa của Armani, thì việc tìm kiếm của chúng ta sẽ trở nên vô nghĩa vì loại nước hoa đó được liệt kê theo tên của Giorgio Armani và sẽ nằm trong dãy G trong khi chúng ta đang tìm kiếm các nhãn hiệu với Vì vậy, chúng tôi cần một cái gì đó dễ hiểu hơn một chút cho các tìm kiếm của mình và chúng tôi đã giải thích những ý tưởng này trước trong hướng dẫn này Hãy kiểm tra Sử dụng chức năng TÌM KIẾMCó thể kiểm tra một phần văn bản trong một ô bằng chức năng TÌM KIẾM. Hàm TÌM KIẾM tìm kiếm một ô cho một giá trị nhất định và trả về vị trí của ký tự mà tại đó giá trị được tìm thấy. Đó là cách chức năng TÌM KIẾM sẽ tự hoạt động nhưng có nhiều cách để sử dụng chức năng TÌM KIẾM để mở rộng khả năng sử dụng của nó Chúng ta có thể xem xét vấn đề này tốt hơn bằng một ví dụ, vì vậy hãy hợp nhất ví dụ trường hợp của chúng ta với hàm TÌM KIẾM và áp dụng công thức bên dưới
Đầu tiên chúng ta sẽ nói về chức năng TÌM KIẾM. Chúng tôi muốn tìm nước hoa của Chanel trong kho của chúng tôi và chúng tôi đã đưa tên thương hiệu này vào công thức. Điểm tìm kiếm là cột B do đó, ô đầu tiên cần tìm kiếm là B3. Về mặt kỹ thuật, điều này là đủ, phải không? Bây giờ nó hoạt động… một phần. Hàm TÌM KIẾM trả về vị trí của ký tự tìm thấy Chanel i. e. ký tự thứ 9. Vì TÌM KIẾM không thể trả về vị trí của ký tự trong phiên bản đầu tiên (vì B3 không chứa giá trị "Armani"), nên kết quả là #VALUE. lỗi. Mặc dù chức năng TÌM KIẾM tự nó làm điều gì đó để cung cấp cho chúng tôi ý tưởng rằng giá trị được tìm kiếm ở đó, chúng tôi cần một cái gì đó phù hợp hơn cho bảng tính của mình Mới đến. hàm ISNUMBER. ISNUMBER trả về TRUE khi giá trị được cung cấp là một số và FALSE khi không phải. Bằng cách này, hàm ISNUMBER sẽ cho kết quả là TRUE khi TÌM KIẾM cho kết quả là một giá trị số và FALSE nếu không Nhưng chúng tôi sẽ làm nổi bật hơn nữa trong kết quả và đó là lý do tại sao chúng tôi gói gọn mọi thứ trong hàm IF. Hàm IF sẽ trả về "Có" khi tìm thấy "Chanel" như một phần của giá trị ô. Nếu không, nó sẽ mang lại dấu gạch ngang "-" Nói rõ hơn, chúng ta có thể dễ dàng nhận thấy chúng tôi có 3 loại nước hoa Chanel trong kho ghi chú Hàm TÌM KIẾM không yêu cầu dấu hoa thị ký tự đại diện nhưng có thể sử dụng dấu chấm hỏi ký tự đại diện để thay thế một ký tự. Tìm hiểu ký tự đại diện là gì trong phần hàm COUNTIF Hàm TÌM KIẾM không phân biệt chữ hoa chữ thường. Tắt nó bằng chức năng TÌM cho tùy chọn phân biệt chữ hoa chữ thường Kiểm tra hai giá trịKiểm tra cổ phiếu của chúng tôi để tìm Chanel là một giá trị. Hãy cũng tìm kiếm nước hoa Gucci vì tại sao không? . Hàm OR trả về TRUE nếu bất kỳ điều kiện nào được chỉ định được đáp ứng và FALSE nếu không đáp ứng. Bây giờ hãy xem công thức với hàm TÌM KIẾM để kiểm tra xem một ô có chứa một trong hai giá trị hay không
Chúng tôi đang tìm kiếm hai giá trị để công thức có 2 công thức TÌM KIẾM; . Cả hai công thức TÌM KIẾM đều được kiểm soát bởi hàm ISNUMBER. Hàm OR được cung cấp với hai điều kiện; . Nếu tìm thấy một trong hai, hàm IF đảm bảo rằng "Có" được trả về. Khác, một dấu gạch ngang được trả lại Ghi chú. Giả sử thay vì tìm hai giá trị trong các ô riêng biệt, bạn muốn tìm hai giá trị trong cùng một ô. e. g. chúng tôi không thể nhớ tên đầy đủ của một loại nước hoa của Chanel nhưng hãy nhớ rằng tên bao gồm "de". Trong trường hợp đó, hàm OR sẽ được thay thế bằng hàm AND và các giá trị tìm kiếm sẽ là "Chanel" và "de"
Sử dụng hàm COUNTIFMột giải pháp thay thế cho hàm TÌM KIẾM để kiểm tra một phần văn bản trong một ô là hàm COUNTIF. Hàm COUNTIF dùng để đếm các ô thỏa mãn điều kiện cho trước. COUNTIF tạo một công thức nhẹ hơn để tìm văn bản khớp một phần do có thể bỏ hàm ISNUMBER Tuy nhiên, cảm ứng được thêm vào là phải sử dụng ký tự đại diện để khớp với 0 hoặc nhiều ký tự. Trong các phần tiếp theo, chúng tôi sẽ chỉ cho bạn cách sử dụng các ký tự đại diện (dấu hoa thị và dấu chấm hỏi) trong hàm COUNTIF để kiểm tra xem một ô có chứa văn bản nhất định hay không Ký tự đại diệncó dấu hoa thị Hàm COUNTIF cần được kích hoạt để tìm kiếm một phần văn bản trong ô có dấu hoa thị trong công thức. Dấu hoa thị đóng vai trò thay thế cho 0 hoặc nhiều ký tự và nếu không có dấu hoa thị, công thức sẽ tìm kiếm một ô chỉ chứa văn bản tìm kiếm thay vì tìm kiếm một ô bao gồm văn bản tìm kiếm. Dấu hoa thị sẽ được đặt tùy thuộc vào vị trí của văn bản bạn muốn tìm trong ô. Đây là một người trợ giúp nhỏ Đánh giá từ những điều trên, dấu hoa thị cũng có thể được đặt ở cuối Chanel nếu chúng tôi không chắc chắn về bất kỳ văn bản nào sau dấu hoa thị trong ô. Đặt cược an toàn hơn sẽ là đặt văn bản tìm kiếm trong dấu hoa thị vì sự hiện diện của dấu hoa thị luôn có thể chiếm 0 ký tự nhưng sự vắng mặt sẽ có nghĩa là không có văn bản Hãy chuyển sang công thức ngay bây giờ để bạn có thể thấy nó hoạt động như thế nào
Hàm COUNTIF dùng để trả về 1 cho ô chứa "*Chanel". Dấu hoa thị được đặt trước văn bản quan tâm vì chúng tôi biết rằng tên thương hiệu sẽ là phần cuối cùng của văn bản trong ô. Nếu ô không chứa Chanel ở cuối văn bản, COUNTIF sẽ trả về 0 Để tùy chỉnh 1 và 0, chúng tôi đã thêm hàm IF trong công thức để thay thế các số này bằng "Có" và "-" tương ứng. Đây là những kết quả Tìm kiếm một phạm vi Trong trường hợp trên, chúng tôi đang xem loại nước hoa nào của Chanel còn hàng. Nếu chúng tôi chỉ muốn kiểm tra xem kho hàng có chứa bất kỳ loại nước hoa Chanel nào hay không mà không có tính đặc hiệu, thì chúng tôi có thể áp dụng hàm COUNTIF cho toàn bộ phạm vi trong tập dữ liệu và nhận lại một kết quả ô duy nhất. Trong excel word, hàm COUNTIF hoạt động trên một phạm vi cũng như một ô. Sử dụng công thức này để kiểm tra phạm vi đối sánh một phần ________số 8_______ Điều duy nhất chúng tôi đã thay đổi trong công thức này là tham chiếu ô từ B3 sang phạm vi B3. B12. Công thức tìm kiếm phạm vi này cho các ô chứa Chanel làm văn bản kết thúc. Vì có 3 ô trong phạm vi này đáp ứng điều kiện này nên công thức cho chúng ta "Có" trong một ô duy nhất Với Dấu Hỏi Một ký tự đại diện khác có đặc điểm thú vị là dấu chấm hỏi. Mặc dù dấu hoa thị có thể biểu thị nhiều ký tự, nhưng một dấu hỏi duy nhất biểu thị một ký tự và có thể sử dụng nhiều ký tự Hãy rút ra từ ví dụ trường hợp của chúng tôi. Chúng tôi chắc chắn rằng có một loại nước hoa Lancôme trong kho của chúng tôi nhưng không thể nhớ chính xác mục nhập đã được viết bằng từ gì. Đó là Lancôme hay Lancôme? . Đó chỉ là chữ O gây nghi ngờ và chúng ta có thể thay thế nó bằng ký tự đại diện dấu chấm hỏi. Sử dụng công thức như thế này để kiểm tra khớp một phần bằng cách sử dụng dấu chấm hỏi ký tự đại diện
Chỉ thay đổi văn bản tìm kiếm của chúng tôi trong công thức này, phần còn lại giống như COUNTIF mà chúng tôi đã sử dụng trước đó. Bây giờ chúng tôi đang tìm kiếm một loại nước hoa Lancôme có dấu hoa thị ở đầu vì văn bản còn lại nằm trước tên thương hiệu trong ô. Chữ "o" đáng ngờ trong Lancôme đã được thay thế bằng dấu chấm hỏi Áp dụng công thức này cho tất cả các loại nước hoa trong bộ dữ liệu của chúng tôi, chúng tôi có một "Có" được trả về cho B11, giải quyết cách đánh vần bí ẩn của chúng tôi là Lancôme ghi chú Hàm COUNTIF cũng hoạt động cho các phạm vi nên nó cũng có thể được áp dụng dưới dạng công thức phạm vi, tùy thuộc vào yêu cầu của bạn là gì Sử dụng hàm MATCHTùy chọn cuối cùng để kiểm tra một ô có thể chứa một phần văn bản tìm kiếm của bạn là hàm MATCH. Hàm MATCH kiểm tra một mảng để tìm một giá trị và trả về vị trí của ô trong mảng đó. Chúng ta có thể làm việc với điều đó. Nếu vị trí của ô trong mảng là thứ chúng ta nhấn mạnh, thì chúng ta có thể thực hiện chỉ bằng cách sử dụng hàm MATCH. Nhưng chúng tôi đang tìm kiếm một văn bản cụ thể trong một phạm vi, vì vậy chúng tôi sẽ bổ sung nhiều cờ lê hơn vào công việc. Hãy đọc tiếp Ký tự đại diệncó dấu hoa thị Với hàm MATCH, chúng ta cũng cần sử dụng dấu sao làm ký tự đại diện. Chúng tôi yêu cầu các chức năng khác như trong công thức này phía trước
Chức năng MATCH đã được trang bị Chanel là tham số đầu tiên được đặt trong hai dấu hoa thị để thay thế văn bản có thể. Mảng tra cứu là B3. B12 và 0 đã được nhập để tìm kết quả khớp chính xác. Khi không tìm thấy văn bản tìm kiếm, MATCH sẽ dẫn đến lỗi #N/A và để tìm mọi kết quả phù hợp, 1 sẽ được trả về Để biến #N/A và 1 thành TRUE và FALSE, hàm ISNA đã được thêm vào công thức. hàm ISNA trả về TRUE khi một giá trị là #N/A. Bây giờ TRUE và FALSE được ngụy trang bằng "Không tìm thấy. " và "Tìm thấy" bằng hàm IF tương ứng Hàm MATCH chọn trên 3 ô có chứa văn bản Chanel. ISNA kiểm tra lỗi #N/A. Vì kết quả của MATCH là 3 nên không có lỗi #N/A – dẫn đến FALSE. Giá trị cho FALSE theo hàm IF là "Đã tìm thấy". Dấu hiệu cho thấy văn bản tìm kiếm Chanel đã được tìm thấy trong phạm vi B3. B12 Có thể sử dụng cùng một công thức với phạm vi bị loại trừ và bao gồm tham chiếu ô tương đối để tìm các ô chính xác chứa văn bản phù hợp
Với Dấu Hỏi Bây giờ hãy xem cách sử dụng dấu chấm hỏi làm ký tự đại diện với hàm MATCH để tìm một phần văn bản trong một phạm vi
Với cùng một dòng các hàm MATCH, ISNA và IF, chúng tôi đã nhập văn bản tìm kiếm bằng dấu hỏi ký tự đại diện để tìm Lanc?me trong B3. B12. Và tin tốt, nó đã được tìm thấy Mở kết quả bằng cách sử dụng tham chiếu ô thay vì phạm vi để tìm cụ thể ô chứa hương thơm Lanc?me
Một điều nữa mà hướng dẫn này chứa là phần cuối của hướng dẫn, vì vậy chúng ta sẽ bắt đầu tìm kiếm các kết quả khớp từng phần trong văn bản trong Excel. Tuy nhiên, nếu đây là điều mới mẻ đối với bạn, hãy khám phá một số thử nghiệm tìm kiếm văn bản và trước khi bạn sẵn sàng bắt đầu, chúng tôi sẽ quay lại với nhiều nội dung Excel hơn để khám phá. Bạn có thể tin tưởng vào chúng tôi cho điều đó |
