Excel

Ô chứa một trong nhiều thứ

Cell Contains One Many Things

Công thức Excel: Ô chứa một trong nhiều thứCông thức chung
= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,A1)))>0
Tóm lược

Để kiểm tra xem một ô có chứa một trong nhiều chuỗi hay không, bạn có thể sử dụng công thức dựa trên TÌM KIẾM , THÁNG NĂMGIỚI THIỆU chức năng. Công thức trong C5, được sao chép xuống, là:





 
= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,B5)))>0

ở đâu đồ đạcphạm vi được đặt tên E5: E7.

Giải trình

Mục tiêu của ví dụ này là kiểm tra từng ô trong B5: B14 để xem nó có chứa bất kỳ chuỗi nào trong phạm vi được đặt tên đồ đạc (E5: E7). Các chuỗi này có thể xuất hiện bất cứ nơi nào trong ô, vì vậy đây là vấn đề 'chứa' theo nghĩa đen. Công thức trong C5, được sao chép xuống, là:





 
= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,B5)))>0

Công thức này dựa trên một công thức khác kiểm tra một ô để tìm một chuỗi con. Nếu ô chứa chuỗi con, công thức trả về TRUE. Nếu không, công thức trả về FALSE:

 
 ISNUMBER ( SEARCH (substring,B5)) // test for substring

Khi mà Chức năng tìm kiếm tìm một chuỗi, nó trả về vị trí của chuỗi đó dưới dạng số. Nếu TÌM KIẾM không tìm một chuỗi, nó trả về lỗi #VALUE! lỗi. Điều này có nghĩa là ISNUMBER sẽ trả về TRUE nếu có khớp và FALSE nếu không.



Trong ví dụ này, mục tiêu là kiểm tra nhiều hơn một chuỗi, vì vậy chúng tôi cung cấp cho hàm TÌM KIẾM danh sách các chuỗi bên trong phạm vi được đặt tên đồ đạc . Vì có 3 chuỗi trong đồ đạc ('red', 'green' và 'blue'), TÌM KIẾM trả về 3 kết quả trong một mảng như thế này:

tìm độ lệch chuẩn trong excel
 
{#VALUE!#VALUE!23}

Vì không tìm thấy 'đỏ' và 'xanh lam', hàm TÌM KIẾM trả về giá trị lỗi #VALUE! lỗi. Tuy nhiên, vì 'màu xanh lá cây' xuất hiện gần cuối văn bản trong ô B5, TÌM KIẾM trả về 23 (tức là 'màu xanh lá cây' bắt đầu ở ký tự thứ 23).

Mảng này được trả về trực tiếp cho Hàm ISNUMBER , chuyển đổi các mục trong mảng thành TRUE hoặc FALSE:

 
 ISNUMBER ({#VALUE!#VALUE!23}) // returns {FALSEFALSETRUE}

Về mặt logic, nếu chúng ta có thậm chí một TRUE trong mảng, chúng ta biết một ô chứa ít nhất một trong các chuỗi mà chúng ta đang tìm kiếm. Cách dễ nhất để kiểm tra ĐÚNG là cộng tất cả các giá trị lại với nhau. Chúng tôi có thể làm điều đó với Hàm SUMPRODUCT , nhưng trước tiên chúng ta cần ép buộc các giá trị TRUE / FALSE thành 1s và 0s với Âm kép (--) như thế này:

 
--{FALSEFALSETRUE} // coerce to 1s and 0s

Điều này tạo ra một mảng mới chỉ chứa 1 và 0:

lịch thanh toán thế chấp máy tính khấu hao excel
 
{001}

được gửi trực tiếp đến SUMPRODUCT:

 
= SUMPRODUCT ({001}) // returns 1

Chỉ với một mảng để xử lý, SUMPRODUCT tính tổng các mục trong mảng và trả về một kết quả. Bất kỳ kết quả nào khác 0 có nghĩa là chúng tôi có một 'lần truy cập', vì vậy chúng tôi thêm> 0 để buộc kết quả cuối cùng là TRUE hoặc FALSE:

 
= SUMPRODUCT ({001})>0 // returns TRUE

Lưu ý rằng bất kỳ kết hợp phù hợp nào sẽ trả về một số lớn hơn 0 và khiến công thức trả về TRUE.

Với một danh sách được mã hóa cứng

Không cần thiết phải sử dụng một phạm vi cho danh sách các chuỗi cần tìm. Bạn cũng có thể sử dụng một mảng hằng số . Ví dụ: để kiểm tra 'red', 'blue' hoặc 'green', bạn có thể sử dụng công thức như sau:

 
= SUMPRODUCT (-- ISNUMBER ( SEARCH ({'red','blue','green'},B5)))>0

Hàm SUM

Trong lịch sử, GIỚI THIỆU thường xuất hiện trong công thức mảng , bởi vì nó có thể xử lý các mảng nguyên bản, không có điều khiển + shift + enter . Điều này làm cho công thức 'thân thiện hơn' với hầu hết người dùng. Trong Excel 365 , xử lý các mảng nguyên bản , NS Hàm SUM có thể được sử dụng thay vì SUMPRODUCT mà không cần điều khiển + shift + enter:

định dạng có điều kiện nếu ô chứa văn bản
 
= SUM (-- ISNUMBER ( SEARCH (things,A1)))>0

Ngăn chặn các kết quả phù hợp sai

Một vấn đề với cách tiếp cận này là bạn có thể nhận được kết quả phù hợp sai từ các chuỗi con xuất hiện bên trong các từ dài hơn. Ví dụ: nếu bạn cố gắng kết hợp 'dr', bạn cũng có thể tìm thấy 'Andrea', 'uống', 'khô', v.v. vì 'dr' xuất hiện bên trong những từ này. Điều này xảy ra bởi vì TÌM KIẾM tự động thực hiện kết hợp 'chứa'.

Để hack nhanh, bạn có thể thêm khoảng trắng xung quanh các từ tìm kiếm (tức là 'dr' hoặc 'dr') để tránh bắt gặp 'dr' trong một từ khác. Nhưng điều này sẽ không thành công nếu 'dr' xuất hiện đầu tiên hoặc cuối cùng trong một ô hoặc xuất hiện với dấu chấm câu.

Nếu bạn cần một giải pháp chính xác hơn, một lựa chọn là bình thường hóa văn bản đầu tiên trong một cột trợ giúp , hãy cẩn thận để thêm khoảng trắng ở đầu và cuối. Sau đó, bạn sử dụng công thức trên trang này trên văn bản kết quả.

Tệp đính kèm Tập tin ô chứa một trong nhiều thứ.xlsx Tác giả Dave Bruns


^