Chào các bạn! Hôm nay mình sẽ chia sẻ với các bạn cách so khớp nhiều giá trị trong một cột trong Google Sheets. Đây là một nhiệm vụ đơn giản, nhưng có một điểm quan trọng cần lưu ý, đó là phân biệt chữ hoa và chữ thường.
Thường thì chúng ta thực hiện ba loại so khớp: không phân biệt chữ hoa chữ thường, phân biệt chữ hoa chữ thường, và so khớp một phần. Loại thứ ba này còn có thể phân biệt chữ hoa chữ thường hoặc không phân biệt, tùy thuộc vào yêu cầu của bạn.
Hãy tưởng tượng rằng chúng ta có một danh sách các bộ phận phụ tùng ô tô. Phạm vi là A2:A trong một tệp Google Sheets của mình.
Mình có một yêu cầu về ba bộ phận phụ tùng là: Hộp pin, Tấm pin và Nắp pin.
Làm thế nào để tìm hiểu xem tất cả ba bộ phận có sẵn bằng cách tìm kiếm trong kho hàng với một công thức?
Chúng ta sẽ so khớp nhiều giá trị, tức là Hộp pin, Tấm pin và Nắp pin, trong cột A và lấy số lượng các giá trị như vậy.
Nếu số lượng đó bằng số lượng bộ phận phụ tùng yêu cầu, tức là tất cả các bộ phận đều có sẵn với chúng tôi. Hãy xem các ví dụ dưới đây.
Công thức không phân biệt chữ hoa chữ thường để So khớp nhiều giá trị trong một Cột
Chúng ta sẽ chỉ định các giá trị tìm kiếm, tức là Hộp pin, Tấm pin và Nắp pin, trong C2:C. Danh sách các bộ phận phụ tùng nằm trong A2:A.
Chúng ta sẽ sử dụng hàm LET để đặt tên C2:C là tiêu chí và A2:A là phạm vi. Điều này sẽ làm cho công thức dễ đọc hơn.
Công thức #1:
=ArrayFormula(let(range,A2:A,criteria,C2:C, if(count(match(criteria,range,0))=counta(criteria), "In Stock","No Stock") ))
Nếu bạn muốn chỉ định các tên bộ phận để tìm kiếm trong công thức, hãy thay C2:C bằng công thức VSTACK sau đây.
vstack("Hộp pin","Tấm pin","Nắp pin")Cấu trúc công thức #1
Hàm MATCH sẽ so khớp nhiều giá trị (tiêu chí) trong phạm vi và trả về vị trí tương đối cho các giá trị khớp và #N/A cho các giá trị không khớp.
Ví dụ, trong bài thử nghiệm dưới đây, công thức trả về #N/A cho tiêu chí “Start-Stop AGM battery” vì nó không có sẵn trong phạm vi.
Chúng ta đã sử dụng IF trong công thức gốc để kiểm tra kết quả khớp trong cú pháp sau đây:
if(count(match_result)=counta(criteria), "In Stock","No Stock")Liệu chúng ta có thể sử dụng hàm XMATCH thay cho hàm MATCH để khớp nhiều giá trị trong một cột trong Google Sheets không?
Có! Trong công thức gốc, hãy thay thế match(criteria,range,0) bằng xmatch(criteria,range).
Một điều nữa. Vai trò của ArrayFormula là hỗ trợ việc so khớp nhiều giá trị (tiêu chí). Nếu không có điều này hoặc một hàm mảng tương đương như INDEX, chúng ta chỉ có thể sử dụng C2 thay vì C2:C.
Công thức phân biệt chữ hoa chữ thường để So khớp nhiều giá trị trong một Cột
Để phân biệt giữa chữ thường và chữ hoa khi so khớp nhiều giá trị, chúng ta sử dụng công thức sau đây trong Google Sheets.
Công thức #2:
=ArrayFormula(let(range,unique(A2:A),criteria,unique(C2:C), if( sum(-regexmatch(range,"^"&textjoin("$|^",true,criteria)&"$"))=counta(criteria), "In Stock","No Stock") ))Ví dụ, bạn có hai mục khác nhau: AQ101A và AQ101a.
Công thức số 1 sẽ coi chúng là cùng một mục, nhưng công thức số 2 sẽ coi chúng là hai mục khác nhau.
Cấu trúc công thức số 2
Công thức này thay thế phần MATCH trong công thức số 1.
-regexmatch(range,"^"&textjoin("$|^",true,criteria)&"$")
Phần công thức REGEXMATCH trên trả về 1 cho các giá trị khớp và 0 cho các giá trị không khớp.
Chúng ta bọc phần công thức này bằng SUM thay vì COUNT vì chúng ta có một phạm vi kết quả là 1 và 0, không giống như số và #N/A trong MATCH, nơi COUNT không tính các giá trị lỗi. Hãy xem cột E trong các hình ảnh bên dưới và ở trên.
Ngoài ra, chúng ta đã áp dụng hàm UNIQUE cho phạm vi và tiêu chí vì công thức trên trả về kết quả khớp với phạm vi, không phải tiêu chí. Vì vậy, các bản sao có thể gây ra vấn đề (hãy xem hình ảnh bên dưới).
IF logic giữ nguyên trong công thức số 1 và công thức số 2.
Đây là cách phân biệt chữ hoa và chữ thường để so khớp nhiều giá trị trong một cột trong Google Sheets.
Lưu ý: Tìm kiếm trong REGEXMATCH sử dụng biểu thức chính quy RE2. Vì vậy, nếu chuỗi tìm kiếm của bạn chứa một ký tự đặc biệt như *+?()|,, bạn phải thoát chúng.
Ví dụ, Acid Lưu Huỳnh (H2SO4) sẽ tìm kiếm mục Acid Lưu Huỳnh (H2SO4).
Vậy là xong rồi. Cảm ơn các bạn đã đọc bài viết. Chúc các bạn thành công!
