Cách tìm một từ trong một ô trong Excel

Một lúc trước, Vernon đã hỏi tôi làm thế nào anh ấy có thể tìm kiếm một ô và so sánh nó với một danh sách các từ. Nếu bất kỳ từ nào trong danh sách tồn tại thì trả về từ phù hợp

Ugh, tôi đã viết điều đó 3 lần và tôi không chắc nó rõ ràng hơn nữa...hãy xem một ví dụ

Tôi đã đánh dấu các từ phù hợp trong cột A màu đỏ

Những gì chúng tôi muốn Excel làm là kiểm tra chuỗi văn bản trong cột A để xem có bất kỳ từ nào trong danh sách của chúng tôi trong H1 không. H3 có mặt, nếu có thì trả về từ phù hợp. Ghi chú. Tôi đã cho các ô H1. H3 phạm vi được đặt tên là 'danh sách'

Có một số cách chúng tôi có thể giải quyết vấn đề này, vì vậy hãy xem xét các lựa chọn của chúng tôi

Cảnh báo. đây là một chủ đề khá nâng cao đòi hỏi một công thức mảng để giải quyết nó

Cập nhật. Việc sử dụng Power Query để tìm kiếm chuỗi văn bản dễ dàng và hiệu quả hơn, bao gồm cả tìm kiếm phân biệt chữ hoa chữ thường và chữ thường

So khớp không phân biệt chữ hoa chữ thường

Nếu bạn không lo lắng về các kết quả phân biệt chữ hoa chữ thường thì bạn có thể sử dụng chức năng TÌM KIẾM với INDEX, SUMPRODUCT và ISNUMBER như thế này

=INDEX(list,SUMPRODUCT(ISNUMBER(SEARCH(list,A2))*ROW($1:$3)))

Trong tiếng Anh công thức của chúng tôi đọc

TÌM KIẾM ô A2 để xem nó có chứa bất kỳ từ nào được liệt kê trong ô H1 không. H3 (tôi. e. phạm vi được đặt tên là 'danh sách') và trả về số của ký tự trong ô A2 nơi từ bắt đầu. Công thức của chúng tôi trở thành

=INDEX(list,SUMPRODUCT(ISNUMBER({20;#VALUE!;#VALUE!})*ROW($1:$3)))

i. e. trong ô A2, chữ 'F' trong từ 'Tài chính' bắt đầu ở vị trí 20

Bây giờ sử dụng kiểm tra ISNUMBER để xem liệu công thức TÌM KIẾM có trả về bất kỳ số nào không (nếu có nghĩa là có kết quả khớp). ISNUMBER sẽ trả về TRUE nếu có một số và FALSE nếu không (điều này cung cấp cho chúng tôi danh sách các giá trị Boolean TRUE hoặc FALSE). Công thức của chúng tôi trở thành

=INDEX(list,SUMPRODUCT({TRUE;FALSE;FALSE}*ROW($1:$3)))

Sử dụng hàm ROW để trả về một mảng số {1;2;3} (xem ghi chú bên dưới về lý do tôi sử dụng ROW trong công thức này). Công thức của chúng tôi trở thành

=INDEX(list,SUMPRODUCT({TRUE;FALSE;FALSE}*{1;2;3}))

Khi bạn nhân các giá trị Boolean TRUE/FALSE, chúng trở thành số tương đương i. e. TRUE = 1 và FALSE = 0. Vì vậy, công thức của chúng tôi đánh giá điều này ( {TRUE;FALSE;FALSE} * . ) like so: { 1 * 1 , . 0*2, 0*3} and our formula becomes:

=INDEX(list,SUMPRODUCT({1;0;0}))

TÓM TẮT chỉ tính tổng các giá trị {1+0+0} mang lại cho chúng tôi 1. Ghi chú. bằng cách sử dụng TÓM TẮT, chúng tôi sẽ tránh được nhu cầu về công thức mảng yêu cầu CTRL+SHIFT+ENTER. Công thức của chúng tôi trở thành

=INDEX(list,1)

Chỉ mục hiện có thể tiếp tục và trả về giá trị đầu tiên trong phạm vi ô H1. H3 là 'Tài chính'.

Ghi chú. công thức trên sẽ không hoạt động nếu không tìm thấy kết quả khớp. Nếu bạn muốn trả về lỗi nếu không tìm thấy kết quả phù hợp thì bạn có thể sử dụng biến thể này

=INDEX(list,IF(SUMPRODUCT(--ISNUMBER(SEARCH(list,A2)))<>0,SUMPRODUCT(ISNUMBER(SEARCH(list,A2))*ROW($1:$3)),NA()))

Không thanh lịch, phải không?

Lưu ý về chức năng ROW

Hàm ROW chỉ trả về số hàng của một tham chiếu. e. g. ROW(A2) sẽ trả về 2. Khi được sử dụng trong công thức mảng, nó sẽ trả về một mảng số. e. g. HÀNG(A2. A4) sẽ trả về {2;3;4}. Chúng ta cũng có thể chỉ cung cấp (các) tham chiếu hàng cho hàm ROW như vậy ROW(2. 4)

Trong công thức này, chúng tôi đã sử dụng ROW để trả về một mảng các giá trị {1;2;3} đại diện cho các mục trong 'danh sách' của chúng tôi. e. Tài chính là 1, Xây dựng là 2 và An toàn là 3. Ngoài ra, chúng tôi có thể đã nhập trực tiếp {1;2;3} vào công thức của mình hoặc thậm chí tham chiếu phạm vi được đặt tên như ROW(list) này

Vì vậy, bạn thấy rằng sử dụng hàm ROW chỉ là một cách nhanh chóng và thông minh để tạo ra một dãy số

Điều bạn phải ghi nhớ khi sử dụng hàm ROW cho mục đích này là chúng tôi cần một danh sách các số từ 1 đến 3 vì có 3 từ trong danh sách của chúng tôi và chúng tôi đang cố gắng tìm vị trí của từ phù hợp

Trong ví dụ này, công thức; . e. 'danh sách' thực sự sẽ tham chiếu các ô H2. H4

Vì vậy, đừng nhầm lẫn khi nghĩ rằng phần ROW của công thức chỉ đơn giản là tham chiếu đến danh sách hoặc phạm vi ô chứa danh sách, điểm quan trọng là công thức ROW trả về một dãy số và chúng tôi đang sử dụng các số đó để

Các chức năng được sử dụng

MỤC LỤC

TÌM hoặc TÌM

ISNUMBER

TÓM TẮT

ROW - Đã giải thích ở trên

Kết hợp phân biệt chữ hoa chữ thường

[cập nhật ngày 5 tháng 12 năm 2013]

Nếu tìm kiếm của bạn phân biệt chữ hoa chữ thường thì bạn không chỉ cần thay TÌM KIẾM bằng TÌM mà còn cần đưa ra công thức NẾU như vậy

=INDEX(list,IF(SUMPRODUCT(ISNUMBER(FIND(list,A2))*ROW($1:$3))<>0,SUMPRODUCT(ISNUMBER(FIND(list,A2))*ROW($1:$3)),NA()))

Thật không may, nó không đơn giản hay thanh lịch như tìm kiếm không phân biệt chữ hoa chữ thường đầu tiên. Thay vào đó, các công thức mảng bên dưới đẹp hơn

Tùy chọn công thức mảng

Dưới đây là một số tùy chọn công thức mảng để đạt được kết quả tương tự. Họ sử dụng LARGE thay vì TÓM TẮT, và kết quả là bạn cần nhập những thứ này bằng cách nhấn CTRL+SHIFT+ENTER

Phân biệt chữ hoa chữ thường

[cập nhật ngày 5 tháng 12 năm 2013]

Trường hợp nhạy cảm

[cập nhật ngày 5 tháng 12 năm 2013]

=INDEX(list,LARGE(IF(ISNUMBER(FIND(list,A2)),ROW($1:$3)),1)) Press CTRL+SHIFT+ENTER

Tải xuống

Nhập địa chỉ email của bạn dưới đây để tải xuống sổ làm việc mẫu

Nhận sổ làm việc

Bằng cách gửi địa chỉ email của bạn, bạn đồng ý rằng chúng tôi có thể gửi email cho bạn bản tin Excel của chúng tôi

Vui lòng nhập địa chỉ email hợp lệ

Tải xuống Sổ làm việc Excel. Ghi chú. Đây là một. xlsx, vui lòng đảm bảo trình duyệt của bạn không thay đổi phần mở rộng của tệp khi tải xuống