Trong bài viết này, chúng ta sẽ tìm hiểu xem chúng ta có thể sử dụng hàm SUMIF trên nhiều Sheet hoặc, nếu không, giải pháp thay thế là gì trong Google Sheets.
Hàm SUMIF hoạt động tốt với một phạm vi duy nhất trên một Sheet.
Nếu bạn muốn sử dụng nó với nhiều phạm vi, trong một Sheet hoặc từ nhiều Sheet khác nhau trong workbook, bạn nên trước tiên ghép các phạm vi đó theo chiều dọc trong một Sheet khác.
Bạn có thể sử dụng hàm VSTACK để làm điều đó. Đây là cú pháp của nó.
Cú pháp: VSTACK(range1, [range2, …])
Vì vậy, sẽ có một phạm vi vật lý mới để áp dụng tổng có điều kiện SUMIF.
Nếu thiếu một phạm vi vật lý, hàm sẽ trả về lỗi “Argument must be a range”. Dưới đây là một ví dụ về trạng thái lỗi này.
= sumif(vstack(‘April 23′!B2:B10,’May 23’!B2:B11), “Stationery”, vstack(‘April 23′!C2:C10,’May 23’!C2:C11))
Ví dụ trên là một cố gắng để sử dụng SUMIF trên hai Sheets nhưng trả về #N/A vì thiếu một phạm vi vật lý.
Vì vậy, chúng ta được hai lựa chọn khi muốn sử dụng SUMIF trên nhiều Sheets trong Google Sheets.
- Ghép các phạm vi trong một Sheet mới trong cùng một workbook và sử dụng SUMIF trong phạm vi vật lý MỚI đó.
- Sử dụng công thức để ghép các phạm vi trực tiếp trong một TRUY VẤN.
Dưới đây là các ví dụ cho cả hai lựa chọn này.
Dữ Liệu Mẫu: Bảng Chi Tiêu Hàng Ngày
Hãy giả sử dữ liệu chi tiêu hàng ngày của tôi được phân tán trên nhiều Sheet trong một workbook và “Internet” là một trong các danh mục (mục đầu mối) chi tiêu.
Dữ liệu hàng ngày nằm trên ba Sheet: “April 23”, “May 23” và “Jun 23”.
Chúng ta sẽ tìm hiểu cách sử dụng SUMIF trên các tab Sheet này để tổng chi tiêu cho mục “Internet”.
Ghép Các Phạm Vi Trên Các Sheet Theo Chiều Dọc để Sử Dụng SUMIF
Chúng ta muốn áp dụng SUMIF trên các phạm vi A1:C trong các Sheets “April 23”, “May 23” và “Jun 23” trong một workbook trong Google Sheets.
Các nhãn trường là Ngày, Mô tả và Số tiền trong A1:C1 (vui lòng xem ảnh chụp màn hình ở trên).
Với sự giúp đỡ của VSTACK, chúng ta có thể ghép các phạm vi A2:C từ các Sheets này như sau trong một Sheet thứ tư có tên “Dashboard”.
Chúng ta đã sử dụng A2:C thay vì A1:C để tránh tiêu đề trong phạm vi kết hợp.
Công thức (Dashboard!B1):
=LET( appended, VSTACK('April 23'!A2:C,'May 23'!A2:C,'Jun 23'!A2:C), FILTER( appended, TRIM(TRANSPOSE(QUERY(TRANSPOSE(appended),,9^9)))<>" ) )Lưu ý: Phần đậm trong công thức trên đã đủ. Nhưng có thể có hàng trống giữa mỗi phạm vi đã được ghép. Phần khác của công thức loại bỏ những hàng trống đó. Bạn có thể tìm hiểu thêm về nó tại đây: Filter Out If the Entire Row Is Blank in Google Sheets.
Chúng tôi đã sẵn sàng sử dụng SUMIF trên nhiều Sheets trong Google Sheets. Chúng ta sẽ tới phần đó dưới tiêu đề tiếp theo.
Trước đó, một việc nữa. Một số bạn có thể có câu hỏi như sau. Hãy để tôi làm rõ điều đó trước tiên.
Tôi muốn chỉ định tên Sheet trong ô A1:A trên Sheet “Dashboard” và tham chiếu đến nó thay vì mã cứng chúng trong công thức VSTACK như trên. Làm thế nào chúng ta làm điều đó?
Không có vấn đề. Sử dụng mã sau trong Dashboard!B1 thay vì công thức VSTACK trên.
=LET(combined,LET(data,"A2:C",tabs,"A1:A",REDUCE(HSTACK(IFERROR(INDEX(SEQUENCE(1,3)/0))),FILTER(tabs,tabs<>""),LAMBDA(accu,val,VSTACK(accu,INDIRECT(val&"!"&data))))),FILTER(combined,TRIM(TRANSPOSE(QUERY(TRANSPOSE(combined),,9^9)))<>""))Trong đó, “A2:C” là phạm vi cần được ghép và A1:A là phạm vi chứa tên Sheet.
Quan trọng:
Khi bạn nhập các tên Sheet trên vào ô A1:A, ứng dụng sẽ đọc chúng như là ngày thay vì văn bản vì chúng chứa tháng và năm.
Vui lòng chọn phạm vi A1:A trước và áp dụng lệnh trình đơn Format > Number > Plain text. Sau đó, bắt đầu nhập các tên Sheet.
Nếu công thức trên khiến bạn hoang mang, hãy nhập và sử dụng chức năng đã đặt tên COPY_TO_MASTER_SHEET của tôi.
SUMIF Trên Nhiều Sheets Trong Google Sheets
Cú pháp: SUMIF(range, criterion, [sum_range])
Bây giờ chúng ta sẽ xem cách sử dụng SUMIF trên các tab Sheet này để tổng chi tiêu cho mục “Internet”. Điều này đơn giản như sau.
=sumif(Dashboard!C1:C,"Internet",Dashboard!D1:D)Nếu bạn muốn nhập tiêu chí Internet vào ô E1 và tham chiếu đến đó trong SUMIF, sử dụng công thức sau.
=sumif(Dashboard!C1:C,Dashboard!E1,Dashboard!D1:D)Làm thế nào để sử dụng nhiều điều kiện, ví dụ: “Cable” và “Internet”, khi SUMIF trên nhiều Sheets trong Google Sheets?
Công thức SUMIF trên nhiều Sheets khi các tiêu chí Internet trong E1 và Cable trong E2:
=ArrayFormula(sumif(Dashboard!C1:C,Dashboard!E1:E2,Dashboard!D1:D))
Dưới đây là cách cứng.
=ArrayFormula(sumif(Dashboard!C1:C,vstack("Internet","Cable"),Dashboard!D1:D))Bỏ Qua Sheet Trợ Giúp “Dashboard”
Không phải ai cũng thích có thêm một Sheet cho SUMIF trên nhiều Sheets. Ví dụ, Sheet “Dashboard” trong công thức trên.
Nếu bạn là một người dùng như vậy, hãy thử sử dụng hàm QUERY làm thay thế cho SUMIF trên nhiều Sheets.
Trong đó, thay thế append_formula bằng công thức Dashboard!B1.
=QUERY(append_formula,"Select Col2,sum(Col3) where lower(Col2)='internet' group by Col2 label sum(Col3)''",0)Trong đó, tiêu chí là “Internet”. Bạn phải chỉ định nó bằng chữ thường vì công thức phân biệt chữ hoa chữ thường.
Chú ý: Thay thế lower(Col2)=’internet’ bằng lower(Col2)='”&lower(Dashboard!E1)&”‘ để chỉ định một ô, ở đây là ô E1, chứa tiêu chí.
Với “Cable” và “Internet” là các tiêu chí như công thức SUMIF trên nhiều Sheets?
=QUERY(append_formula,"Select Col2,sum(Col3) where lower(Col2) matches 'cable|internet' group by Col2 label sum(Col3)''",0)Phân tách mỗi tiêu chí bằng dấu “|”.
Chú ý: Thay thế ‘cable|internet’ bằng ‘”&textjoin(“|”,true,index(lower(Dashboard!E1:E2)))&”‘ để chỉ định một phạm vi ô, ở đây là E1:E2, chứa các tiêu chí.
SUMIFS Trên Nhiều Sheets Trong Google Sheets
Cú pháp: SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])
Tương tự như SUMIF, SUMIFS cũng không dành cho việc sử dụng trên nhiều Sheets.
Chúng ta phải ghép các phạm vi trên mỗi Sheet như trước. Vì vậy chúng ta có “Dashboard” Sheet với dữ liệu đã được ghép.
Làm thế nào để viết SUMIFS để trả về tổng số chi tiêu cho “Cable” trong tháng 4 và tháng 5?
SUMIFS sau sẽ trả về tổng số chi tiêu trong tháng 4 và tháng 5.
=ArrayFormula(SUMIFS(Dashboard!D1:D,REGEXMATCH(MONTH(Dashboard!B1:B)&"","^4$|^5$"),TRUE))Với điều này, chúng ta có thể thêm danh mục “Cable” như sau.
=ArrayFormula(SUMIFS(Dashboard!D1:D,REGEXMATCH(MONTH(Dashboard!B1:B)&"","^4$|^5$"),TRUE,Dashboard!C1:C,"Cable"))Làm thế nào để thêm các danh mục “Cable” và “Internet”?
=ArrayFormula(SUMIFS(Dashboard!D1:D,REGEXMATCH(MONTH(Dashboard!B1:B)&"","^4$|^5$"),TRUE,REGEXMATCH(Dashboard!C1:C,"(?i)^Cable$|^Internet$"),TRUE))Như bạn có thể thấy, hàm REGEXMATCH đóng vai trò quan trọng khi có nhiều tiêu chí trong cùng một cột.
Liên quan: REGEXMATCH trong SUMIFS và Các Cột Tiêu Chí Nhiều trong Google Sheets.
