Google Sheets có một cách độc đáo để áp dụng hàm SUMIFS với điều kiện HOẶC (logic). Phương pháp hằng số mảng mà bạn có thể quen thuộc trong Excel sẽ không hoạt động ở đây.
Ví dụ, trong Excel, bạn có thể sử dụng công thức sau để tổng các lượng hàng đã giao nếu ngày giao hàng là ngày 21 tháng 10 năm 2023 và trạng thái giao hàng là đã gửi hoặc đã giao:
=SUM(SUMIFS(C2:C14,A2:A14,DATE(2023,10,21),D2:D14,{"Sent","Delivered"}))
Cú pháp của hàm SUMIFS:
SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])
Trong công thức này:
- C2:C14 là phạm vi tổng.
- A2:A14 là phạm vi tiêu chí 1.
- D2:D14 là phạm vi tiêu chí 2.
- Các tiêu chí tương ứng là DATE(2023,10,21) và {“Sent”,”Delivered”}.
SUMIFS với logic HOẶC này sẽ không hoạt động trong Google Sheets.
Để thực hiện SUMIFS với điều kiện HOẶC, bạn phải sử dụng hàm REGEXMATCH hoặc một trong các hàm trợ giúp Lambda.
Lưu ý cho độc giả thường xuyên của tôi: Tôi đã đề cập đến điều này trong một trong các hướng dẫn trước đó của tôi, nhưng trong hướng dẫn đó, tôi tập trung vào hàm REGEXMATCH, chứ không phải hàm SUMIFS.
Dữ liệu Mẫu
Chúng tôi đã sử dụng dữ liệu mẫu sau trong ví dụ công thức Excel ở trên. Chúng tôi sẽ sử dụng cùng dữ liệu trong Google Sheets để bạn có thể thấy cách hàm SUMIFS với điều kiện logic HOẶC khác nhau trong Google Sheets và Excel.
SUMIFS với Điều kiện HOẶC trong Google Sheets: REGEXMATCH
Cách tiếp cận này có một số ưu điểm. Bạn không cần sử dụng ký tự đại diện cho các phần khớp riêng biệt, nếu có.
Ngoài ra, bạn có thể làm cho hàm SUMIFS với logic HOẶC sử dụng REGEXMATCH phân biệt hoa thường.
Hãy tôi giải thích logic đằng sau việc sử dụng REGEXMATCH trong SUMIFS, và sau đó chúng ta sẽ viết công thức.
Trong vấn đề trước đó, xác định tiêu chí trong cột ngày là dễ hiểu vì chúng ta không cần áp dụng kiểm tra logic HOẶC.
Thách thức phát sinh trong cột trạng thái. Ở đây, chúng ta cần kiểm tra hai điều kiện: “Sent” và “Delivered”.
Vui lòng tham khảo ảnh chụp màn hình dưới đây cho công thức mảng REGEXMATCH sau đây trong ô E2:
=ARRAYFORMULA(REGEXMATCH(D2:D14,"Sent|Delivered"))
Công thức này sẽ trả về một mảng các giá trị Boolean.
Cú pháp của công thức REGEXMATCH:
REGEXMATCH(text, regular_expression)
Nếu giá trị trong ô chứa “Sent” hoặc “Delivered”, phần tử tương ứng trong mảng sẽ là TRUE, chỉ ra sự phù hợp. Nếu ô không chứa một trong hai từ này, phần tử trong mảng sẽ là FALSE, chỉ ra không có sự phù hợp.
Trong trường hợp này, tiêu chí phân biệt chữ hoa và chữ thường, và đây là một phần khớp. Nếu bạn muốn một phù hợp chính xác, không phải phù hợp đại diện, thay thế biểu thức chính quy “Sent|Delivered” bằng “^Sent$|^Delivered$”.
Nếu bạn muốn làm cho nó không phân biệt chữ hoa chữ thường nhưng yêu cầu phù hợp chính xác, không phải phù hợp đại diện, hãy sử dụng “(?i)^Sent$|^Delivered$”.
Giờ đây, đây là công thức SUMIFS với nhiều tiêu chí và logic HOẶC trong Google Sheets:
=SUM(SUMIFS(C2:C14,A2:A14,DATE(2023,10,21),E2:E14,TRUE))
Chúng tôi đã thay thế cột trạng thái, ban đầu trong phạm vi tiêu chí D2:D14, bằng kết quả của công thức REGEXMATCH.
Lưu ý: Bạn không cần sử dụng cột trợ giúp E2:E14. Trong SUMIFS, thay thế phạm vi đó bằng chính công thức REGEXMATCH.
SUMIFS với Điều kiện HOẶC trong Google Sheets: Lambda
Sử dụng MAP hoặc BYROW lambda với SUMIFS là một cách khác để xử lý SUMIFS với điều kiện HOẶC trong Google Sheets.
Dưới đây là một ví dụ sử dụng MAP dựa trên dữ liệu mẫu ở trên:
=SUM(MAP({"Sent","Delivered"},LAMBDA(row, SUMIFS(C2:C14,A2:A14,DATE(2023,10,21),D2:D14,row))))
Công thức này hoạt động như sau:
Trong công thức này, chúng tôi sử dụng hàm MAP để lặp qua mảng {“Sent”,”Delivered”} và áp dụng hàm lambda cho mỗi phần tử trong mảng này.
Hàm lambda, được xác định là LAMBDA(row, SUMIFS(C2:C14, A2:A14, DATE(2023, 10, 21), D2:D14, row)), là một phần quan trọng của công thức.
Dòng được sử dụng như đối số truyền vào cho hàm lambda, đại diện cho “Sent” hoặc “Delivered”.
Hàm MAP về cơ bản thực thi hàm lambda hai lần, một lần với đối số dòng là “Sent” và một lần với đối số dòng là “Delivered”, và trả về hai giá trị.
Hàm SUM tổng hợp các giá trị đã trả về, mang lại tổng cộng cuối cùng cho kết quả.
Khớp đại diện
Để sử dụng các khớp phần trong SUMIFS với điều kiện HOẶC, sử dụng ký tự đại diện (*) đại diện.
Ví dụ, để khớp bất kỳ từ nào chứa “sent” hoặc “delivered”, hãy sử dụng khớp phần như sau:
{"*Sent*","*Delivered*"}
Ký tự đại diện (*) khớp với bất kỳ dãy ký tự nào, bao gồm cả không ký tự.
Trong trường hợp này, các khớp phần sẽ khớp với bất kỳ từ nào chứa chuỗi “sent” hoặc “delivered”, bất kể bất kỳ ký tự khác nào có thể xuất hiện trong từ.
Lưu ý: Công thức không phân biệt chữ hoa.
Các phương thức thay thế cho SUMIFS với Điều kiện HOẶC trong Google Sheets
Nếu bạn đang tìm kiếm một giải pháp hoàn hảo để thay thế SUMIFS với tiêu chí HOẶC, hãy sử dụng QUERY.
Hàm QUERY có thể được sử dụng để thực hiện nhiều nhiệm vụ, bao gồm SUMIFS với các tiêu chí HOẶC. Có một số cách để làm điều này, bằng cách sử dụng toán tử HOẶC logic hoặc các hàm so sánh chuỗi Matches hoặc Contains.
- Query với Toán tử HOẶC logic (Khớp chính xác và phân biệt hoa thường):
=QUERY(A2:D14,"select sum(C) where A=date '2023-10-21' and D='Sent' or D='Delivered' label sum(C)''")
- Query với Hàm so sánh chuỗi Matches (Khớp chính xác và phân biệt hoa thường):
=QUERY(A2:D14,"select sum(C) where A=date '2023-10-21' and D matches 'Sent|Delivered' label sum(C)''")
- Query với Hàm so sánh chuỗi Contains (Khớp chính xác và phần khớp):
=QUERY(A2:D14,"select sum(C) where A=date '2023-10-21' and D contains 'Sent' or D contains 'Delivered' label sum(C)''")
Ghi chú:
- Hành vi mặc định của các hàm so sánh chuỗi như Matches và Contains là phân biệt hoa thường. Để làm cho chúng không phân biệt hoa, hãy chuyển các chuỗi thành chữ thường bằng cách sử dụng hàm Lower trước khi so sánh.
- Hàm so sánh chuỗi Contains sẽ khớp với bất kỳ chuỗi nào chứa chuỗi con đã chỉ định, bất kể vị trí của nó trong chuỗi.
Kết luận
Bạn có thể đã nhận thấy rằng tôi đã cố định tiêu chí trong tất cả các công thức SUMIFS với các tiêu chí HOẶC ở trên. Bạn có thể sử dụng tham chiếu ô thay vì. Vui lòng tham khảo các hướng dẫn về các hàm liên quan trong hướng dẫn hàm của tôi để tìm hiểu thêm về cách sử dụng tiêu chí.
Hy vọng bạn đã thích hướng dẫn này. Cảm ơn bạn đã dành thời gian của mình!
