Trong Google Sheets, chúng ta có thể sử dụng công thức FILTER hoặc XLOOKUP để tự động điền ô với nhiều điều kiện trùng khớp.
Để công thức có thể tự động điền vào các hàng tiếp theo, chúng ta cũng có thể kết hợp với hàm lambda MAP.
Hàm XLOOKUP được thiết kế để tìm kiếm một khóa tìm kiếm trong một cột duy nhất. Tuy nhiên, chúng ta có thể tìm cách sử dụng nó trên nhiều cột.
Hãy để tôi giải thích ý nghĩa của “Tự động điền ô với nhiều điều kiện trùng khớp”.
Hãy xem dữ liệu được cung cấp (vui lòng tham khảo hình ảnh bên dưới) để hiểu mục tiêu.
Tôi muốn tự động điền vào cột “Giá” khi nhập giá trị trong các cột “Sản phẩm”, “Phân lớp” và “Phân nhóm con”.
Giá cả thay đổi cho mỗi sản phẩm và thay đổi khi “Phân lớp” và “Phân nhóm con” thay đổi.
Tôi có một “Bảng giá” trên một tab khác, như được minh họa dưới đây.
Mục tiêu là điền “Giá” từ dữ liệu của tab này vào tab “Báo cáo bán hàng”, cụ thể là trong cột “Giá”.
Để đạt được điều này, chúng ta cần so sánh dữ liệu trong cả hai tab bằng một công thức để thu được kết quả mong muốn.
Bạn có thể thực hiện công việc này bằng một công thức đơn giản trong Google Sheets. Làm theo các bước sau để tự động điền giá trị vào các ô dựa trên điều kiện trong Google Sheets.
Tự động điền ô với nhiều điều kiện trùng khớp bằng cách sử dụng công thức FILTER
Dưới đây là công thức để điền ô bằng cách trùng khớp nhiều điều kiện trong Google Sheets.
Công thức này nên được nhập vào ô D2 trên tab “Báo cáo bán hàng”, sau đó sao chép và dán vào ô phía dưới để tự động điền giá trị.
=FILTER( 'Bảng giá'!$D$2:$D, 'Bảng giá'!$A$2:$A=A2, 'Bảng giá'!$B$2:$B=B2, 'Bảng giá'!$C$2:$C=C2 )
Cho phép giải thích công thức này, sau đó chúng ta sẽ làm cho nó tự động đi xuống từ ô D2 mà không cần kéo nó xuống.
Công thức này tuân theo cú pháp của hàm FILTER:
FILTER(range, condition1, [condition2, ...])
Trong đó:
range: ‘Bảng giá’!$D$2:$Dcondition1: ‘Bảng giá’!$A$2:$A=A2condition2: ‘Bảng giá’!$B$2:$B=B2condition3: ‘Bảng giá’!$C$2:$C=C2
Công thức lọc cột giá trong tab ‘Bảng giá’, so khớp các cột sản phẩm, phân lớp và phân nhóm con trong tab đó với các giá trị tương ứng trong các ô A2, B2 và C2 trong tab ‘Báo cáo bán hàng’.
Khi kéo công thức xuống, A2, B2 và C2 trở thành A3, B3 và C3, và các giá trị tương ứng được trích xuất.
Làm thế nào chúng ta có thể cấu hình công thức này để tự động điền ô dựa trên nhiều điều kiện trùng khớp?
Chúng ta có thể làm thế nào để công thức này tự động đi xuống bắt đầu từ ô D2?
Công thức trên không tự động đi xuống mà yêu cầu kéo thủ công. Để cho phép mở rộng tự động xuống, hãy sử dụng hàm lambda MAP.
Trong MAP, chỉ định các phạm vi A2:A, B2:B và C2:C để lặp qua các giá trị trong các cột này. Do đó, không cần kéo công thức từ D2 xuống.
Công thức:
=IFERROR( MAP(A2:A, B2:B, C2:C, LAMBDA(x, y, z, FILTER( 'Bảng giá'!$D$2:$D, 'Bảng giá'!$A$2:$A=x, 'Bảng giá'!$B$2:$B=y, 'Bảng giá'!$C$2:$C=z ) )) )
Hãy phân tích công thức từng bước, bắt đầu từ cú pháp:
Syntax: MAP(array1, [array2, ...], LAMBDA([name, ...], formula_expression))
Ở đây, A2:A, B2:B và C2:C là array1, array2 và array3. Tên để xác định các giá trị trong các mảng này là x, y, z.
Công thức formula_expression là công thức FILTER trước đó, trong đó A2 được thay thế bằng x, B2 bằng y và C2 bằng z để lặp qua các giá trị trong các mảng. IFERROR được sử dụng để xử lý các lỗi có thể xảy ra và trả về kết quả trống.
Công thức mảng này tự động điền ô với nhiều điều kiện trùng khớp trong Google Sheets.
Tự động điền ô với nhiều điều kiện trùng khớp bằng cách sử dụng công thức XLOOKUP
Hàm XLOOKUP tìm kiếm một khóa tìm kiếm trong một cột (phạm vi tìm kiếm) và trả về một giá trị từ một cột khác (phạm vi kết quả). Tuy nhiên, thách thức ở đây là chúng ta muốn tìm kiếm ba khóa trong ba cột và trả về một giá trị từ cột giá.
Để giải quyết vấn đề này, chúng ta sẽ kết hợp ba cột thành một và ba khóa tìm kiếm thành một.
Bạn có thể nhập công thức này vào ô D2 và kéo xuống:
=ArrayFormula( XLOOKUP( A2&B2&C2, 'Bảng giá'!$A$2:$A&'Bảng giá'!$B$2:$B&'Bảng giá'!$C$2:$C, 'Bảng giá'!$D$2:$D, "" ) )
Cú pháp:
XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])
Trong đó:
search_key: A2&B2&C2lookup_range: ‘Bảng giá’!$A$2:$A&’Bảng giá’!$B$2:$B&’Bảng giá’!$C$2:$Cresult_range: ‘Bảng giá’!$D$2:$Dmissing_value: “”
Chúng tôi đã sử dụng ARRAYFORMULA vì chúng tôi kết hợp các cột và thao tác này yêu cầu hỗ trợ công thức mảng.
Làm thế nào chúng ta mở rộng công thức này xuống?
Không giống như FILTER, bạn không cần sử dụng MAP hoặc bất kỳ hàm lambda nào khác để mở rộng công thức này xuống. Đơn giản thay thế A2&B2&C2 bằng A2:A&B2:B&C2:C:
=ArrayFormula( XLOOKUP( A2:A&B2:B&C2:C, 'Bảng giá'!A2:A&'Bảng giá'!B2:B&'Bảng giá'!C2:C, 'Bảng giá'!D2:D, "" ) )
Đây là công thức XLOOKUP thay thế để tự động điền ô với nhiều giá trị trùng khớp.
Ở trên, tôi đã cung cấp hai phương pháp sử dụng công thức để tự động điền ô với nhiều giá trị trùng khớp trong Google Sheets. Chúng ta cũng có thể thay thế XLOOKUP bằng VLOOKUP và FILTER bằng QUERY.
Tuy nhiên, hai công thức được đề cập ở trên sẽ đủ để đáp ứng yêu cầu của bạn. Dưới đây là một số tài liệu tham khảo liên quan đến việc tự động điền ô.
- Cách tự động điền bảng chữ cái trong Google Sheets
- Tự động điền cột bên phải dựa trên giá trị bên trái trong Sheets
- Tự động điền ngày tuần tự khi có giá trị nhập vào cột tiếp theo trong Google Sheets
- Tự động điền mẫu Google Forms từ Google Sheets: Hướng dẫn từng bước
- Cách tự động điền các ngày trong tuần trong Google Sheets
