Một trong những vấn đề bạn có thể gặp phải khi sử dụng các hàm như DSUM, SUMIFS, SUMPRODUCT hoặc QUERY là việc sử dụng trường tiêu chuẩn hai lần. SUMIFS và DSUM là hai hàm tính tổng có nhiều tiêu chí. SUMIFS là một hàm logic, trong khi DSUM là một hàm cơ sở dữ liệu. Dù bạn sử dụng SUMIFS hay DSUM, bạn có thể đạt được cùng kết quả bằng cách sử dụng tiêu chuẩn một cách hợp lý. SUMPRODUCT cũng có thể thay thế hai hàm còn lại trong Google Sheets.
Trong DSUM, đơn giản để nhập tiêu chí dưới các tiêu đề trường. Tuy nhiên, việc khá phức tạp hơn khi bạn muốn bao gồm cột tiêu chí giống nhau (phạm vi tiêu chí) hai lần trong SUMIFS. Có ba phương pháp chính để sử dụng cùng trường tiêu chuẩn hai lần trong hàm SUMIFS trong Google Sheets:
Phương pháp 1: Kết hợp hai công thức SUMIFS
Phương pháp này liên quan đến việc thêm hai công thức SUMIFS với mỗi công thức sử dụng một tiêu chuẩn khác nhau trong cùng một phạm vi.
SUMIFS(D7:D14,C7:C14,">="&DATE(2017,7,1),C7:C14,"<="&DATE(2017,7,31),A7:A14,"Myron Ambriz",B7:B14,"North")
Công thức SUMIFS này sẽ tính tổng phạm vi D7:D14 nếu:
- C7:C14 lớn hơn hoặc bằng ngày 1 tháng 7 năm 2017.
- C7:C14 nhỏ hơn hoặc bằng ngày 31 tháng 7 năm 2017.
- Văn bản trong A7:A14 là “Myron Ambriz”.
- Văn bản trong B7:B14 là “North”.
Bạn có thể nhập các tiêu chuẩn này trong phạm vi ô C1:C4 và sử dụng công thức như sau:
=SUMIFS(D7:D14,C7:C14,">="&C1,C7:C14,"<="&C2,A7:A14,C3,B7:B14,C4)
Hãy xem một số ví dụ về điều này:
Cú pháp của hàm SUMIFS trong Google Sheets:
SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])
SUMIFS: Cùng Phạm Vi Tiêu Chuẩn So Sánh
Khi chúng ta sử dụng tiêu chuẩn ngày hoặc số với các toán tử so sánh trong hàm SUMIFS, chúng ta không gặp bất kỳ vấn đề nào khi sử dụng cùng phạm vi tiêu chuẩn hai lần.
Ví dụ, giả sử phạm vi ngày của bạn là C7:C14, bạn có thể bao gồm trường ngày này hai lần trong SUMIFS như là criteria_range1 và criteria_range2:
=SUMIFS(D7:D14,C7:C14,">="&DATE(2017,7,1),C7:C14,"<="&DATE(2017,7,31),A7:A14,"Myron Ambriz",B7:B14,"North")
Công thức SUMIFS này sẽ tính tổng phạm vi D7:D14 nếu:
- C7:C14 lớn hơn hoặc bằng ngày 1 tháng 7 năm 2017.
- C7:C14 nhỏ hơn hoặc bằng ngày 31 tháng 7 năm 2017.
- Văn bản trong A7:A14 là “Myron Ambriz”.
- Văn bản trong B7:B14 là “North”.
Bạn có thể nhập các tiêu chuẩn này trong phạm vi ô C1:C4 và sử dụng công thức như sau:
=SUMIFS(D7:D14,C7:C14,">="&C1,C7:C14,"<="&C2,A7:A14,C3,B7:B14,C4)
Hãy để tôi giải thích cách làm việc với cùng phạm vi tiêu chuẩn hai lần khi không có toán tử so sánh.
Các Phương Pháp Khác Nhau để Bao Gồm Cùng Trường Hai Lần trong Hàm SUMIFS
Đây là phần thú vị. Trong phần này, bạn có thể tìm hiểu các phương pháp khác nhau mà chúng tôi đã đề cập ở đầu để bao gồm cùng một trường (phạm vi tiêu chuẩn) hai lần trong SUMIFS trong Google Sheets.
Vấn đề: Làm thế nào để sử dụng SUMIFS để tổng số lượng bán hàng (C7:C14) của “Philip Nida” (A7:A14) trong “North” hoặc “South” (B7:B14)?
Phương Pháp 1: Kết hợp Hai Công Thức SUMIFS
Hãy bắt đầu với hình thức đơn giản nhất để sử dụng cùng một trường tiêu chuẩn hai lần, đó là kết hợp hai công thức SUMIFS.
Phương pháp này liên quan đến việc thêm hai công thức SUMIFS vào nhau:
=SUMIFS(C7:C,A7:A,"Philip Nida",B7:B,"North")+ SUMIFS(C7:C,A7:A,"Philip Nida",B7:B,"South")
Công thức này thay thế tiêu chuẩn cố định bằng các tham chiếu ô:
=SUMIFS(C7:C,A7:A,C1,B7:B,C2)+ SUMIFS(C7:C,A7:A,C1,B7:B,C3)
Phương Pháp 2: Sử dụng Mẹo thay thế
Phương pháp này liên quan đến việc sử dụng hàm SUBSTITUTE để thay thế một tiêu chuẩn bằng tiêu chuẩn khác.
Ví dụ, để tính tổng các giá trị trong C7:C14 nơi các giá trị trong A7:A14 là “Philip Nida” và B7:B14 là “North” hoặc “South”, bạn có thể sử dụng công thức sau:
=ArrayFormula(SUMIFS(C7:C14,A7:A14,"Philip Nida",SUBSTITUTE(B7:B14,"South","North"),"North"))
Chúng tôi đã sử dụng hàm SUBSTITUTE để thay thế “South” bằng “North” trong cột B. Vì vậy, thực chất, chúng ta không cần sử dụng cùng một trường tiêu chuẩn hai lần trong hàm SUMIFS.
Hàm SUBSTITUTE yêu cầu hỗ trợ ARRAYFORMULA.
Công thức SUMIFS giống nhau với các tiêu chuẩn cố định được thay thế bằng các tham chiếu ô:
=ArrayFormula(SUMIFS(C7:C,A7:A,C1,SUBSTITUTE(B7:B,C3,C2),C2))
Phương Pháp 3: Sử dụng REGEXMATCH
Phương pháp này liên quan đến việc sử dụng hàm REGEXMATCH để khớp cả hai tiêu chuẩn trong cùng một phạm vi. Điều này sẽ trả về TRUE cho các hàng khớp, sẽ được sử dụng làm tiêu chuẩn.
Ví dụ, để tính tổng các giá trị trong C nơi các giá trị trong cột A là “Philip Nida” và cột B là “North” hoặc “South”, bạn có thể sử dụng công thức sau:
=ArrayFormula(SUMIFS(C7:C14,A7:A14,"Philip Nida",REGEXMATCH(B7:B14, "North|South"),TRUE))
Công thức tương tự với tiêu chuẩn thay thế bằng tham chiếu ô:
=ArrayFormula(SUMIFS(C7:C14,A7:A14,C1,REGEXMATCH(B7:B14, C2&"|"&C3),TRUE))
Phương pháp nào hiệu quả nhất để sử dụng cùng trường hai lần trong hàm SUMIFS?
Trong ba phương pháp, tôi ưu tiên REGEXMATCH (phương pháp số 3). Khi bạn muốn sử dụng trường tiêu chuẩn nhiều hơn một lần, rất dễ dàng để bao gồm các tiêu chuẩn bằng cách đơn giản tách chúng với dấu đường ống.
Phương pháp SUBSTITUTE yêu cầu nhiều nhúng tùy thuộc vào số lượng tiêu chuẩn.
Phương pháp SUMIFS có thể yêu cầu thêm một số công thức SUMIFS, điều này có thể làm cho công thức trở nên rối rắm.
Kết luận, công thức SUMIFS không hỗ trợ việc sử dụng trường tiêu chuẩn hai lần mà không có toán tử so sánh.
Trong phương pháp SUBSTITUTE, chúng ta thay thế một tiêu chuẩn bằng tiêu chuẩn khác, vì vậy thực chất chỉ có một tiêu chuẩn. Khi sử dụng REGEXMATCH, chúng ta sử dụng tiêu chuẩn TRUE, cũng chỉ có một tiêu chuẩn.
Vì thế, tôi khuyến nghị sử dụng phương pháp REGEXMATCH để sử dụng cùng trường hai lần trong hàm SUMIFS.
