Trong Google Sheets, chúng ta có thể sử dụng hàm SUMIF để trả về một mảng hai chiều. Điều này rất hữu ích khi chúng ta cần tạo một báo cáo tổng hợp theo từng danh mục. Hãy cùng tìm hiểu cách hàm SUMIF trả về nhiều cột trong Google Sheets.
Trong bài viết này, để minh họa kết quả mảng hai chiều, tôi sẽ sử dụng hàm SUMIF để tạo một báo cáo tổng hợp theo danh mục và ngày/tháng/quý/năm.
SUMIF trả về kết quả nhiều cột hai chiều trong Google Sheets
Dưới đây là dữ liệu mẫu và kết quả tổng hợp theo danh mục và ngày/tháng/năm sử dụng hàm SUMIF (kết quả mảng SUMIF hai chiều).
Bạn có thể áp dụng cách tiếp cận này cho báo cáo tổng hợp theo tháng/quý/năm và danh mục. Dưới đây là công thức và cách viết công thức (giải thích công thức) chi tiết.
Chú ý:
Dữ liệu mẫu cho tất cả các ví dụ dưới đây đang ở trong tab ‘Sales’. Tiêu chí và công thức nằm trong tab ‘Summary’.
Cách sử dụng SUMIF để trả về kết quả nhiều cột trong Google Sheets
Hãy xem một ví dụ về kết quả hai chiều của hàm SUMIF trong báo cáo tổng hợp theo danh mục và ngày/tháng/năm. Trong đó, cột A và hàng 1 chứa các khóa tìm kiếm.
Cách bạn chèn các khóa tìm kiếm này vào SUMIF là câu trả lời cho việc SUMIF trả về nhiều cột trong Sheets.
Tôi sẽ giải thích việc sử dụng tiêu chí ngang và dọc của SUMIF dưới các tiêu đề khác nhau.
SUMIF để tạo báo cáo tổng hợp theo ngày và danh mục trong Sheets
Dữ liệu mẫu của tôi được lưu trong tab ‘Sales’ từ A1 đến C. Công thức nằm trong tab ‘Summary’ tại ô B2. Để trình bày công thức một cách tốt hơn, hãy xem cú pháp hàm SUMIF dưới đây. Điều này sẽ giúp tôi giải thích công thức một cách tốt hơn.
SUMIF(range, criterion, sum_range)
Công thức SUMIF trong ô B2 là một mảng kết quả hai chiều. Với SUMIF, chúng ta phải sử dụng hàm ArrayFormula. Điều này giúp SUMIF mở rộng.
Cú pháp SUMIF cho kết quả nhiều cột sẽ như sau:
ArrayFormula(SUMIF(range, criteria, sum_range))
Công thức số 1: Báo cáo tổng hợp theo ngày và danh mục
=ArrayFormula(if(A2:A<>””,sumif(Sales!$B$2:$B&Sales!$A$2:$A,$B$1:$G$1&$A$2:$A,Sales!$C$2:$C),))
Giải thích công thức:
Hãy bắt đầu giải thích công thức bằng một bức ảnh chụp màn hình.
range:
Kiểm tra dữ liệu mẫu của tôi. Trong đó, bạn có thể thấy cột đầu tiên chứa ngày và cột thứ hai chứa danh mục.
Tôi đã kết hợp hai cột này (range) trong công thức SUMIF thành category&date, tức là Sales!$B$2:$B&Sales!$A$2:$A.
criteria:
Do chúng ta đã kết hợp hai cột trong range, tiêu chí cũng phải được kết hợp như vậy. Ở đây, tôi đã sắp xếp tiêu chí cho SUMIF theo hàng (ngang) và cột (dọc).
Ở đây, tôi cũng kết hợp các tiêu chí (danh mục và ngày) theo cách sau đây.
$B$1:$G$1&$A$2:$A
Sẵn sàng để biết về các sự kết hợp này? Hãy nhập range và criteria SUMIF trên tab ‘Summary’ của bạn như hình dưới đây. Hãy chắc chắn bọc các công thức bằng hàm ArrayFormula và làm cho range hạn chế (ví dụ hạn chế: A2:A4 thay vì A2:A, hãy xem ảnh chụp màn hình). Nếu sử dụng range vô hạn, Sheet của bạn có thể trở nên không phản hồi.
SUMIF trả về nhiều cột vì chúng ta có tiêu chí trong một mảng hai chiều.
Điều này chỉ ra một điều quan trọng! Để có kết quả mảng 2D bằng cách sử dụng SUMIF, cần cung cấp cho nó một mảng 2D làm tiêu chí.
sum_rage:
Tất nhiên là cột C trong tab ‘Sales’.
Cách này giúp bạn sử dụng SUMIF để trả về một đầu ra hai chiều, nghĩa là một báo cáo tổng hợp theo ngày và danh mục trong Google Sheets.
SUMIF để tạo báo cáo tổng hợp theo tháng và danh mục trong Sheets
Sử dụng công thức nhiều cột SUMIF trên, bạn chỉ còn một bước nữa để tạo báo cáo tổng hợp theo tháng và danh mục.
Chỉ cần bọc range trong công thức nhiều cột SUMIF trên bằng chức năng MONTH.
Công thức số 2: Báo cáo tổng hợp theo tháng và danh mục
=ArrayFormula(if(A2:A<>””,sumif(Sales!$B$2:$B&month(Sales!$A$2:$A),$B$1:$G$1&$A$2:$A,Sales!$C$2:$C),))
Tôi có thể cần sửa đổi dữ liệu mẫu và tiêu chí để kiểm tra công thức mới. Đây là ví dụ về công thức mới.
Lần này, tôi đã sử dụng số tháng trong cột A của tab ‘Summary’. Đó là lý do tại sao tôi đã sử dụng hàm MONTH để chuyển đổi ngày thành tháng chỉ trong phần ‘range’ của công thức. Công thức trong ô B2 trả về một mảng.
Tôi đã chia sẻ hai ví dụ để giúp bạn hiểu rõ hơn về cách SUMIF trả về nhiều cột trong Google Sheets.
Tạo báo cáo tổng hợp theo năm và danh mục bằng SUMIF trong Sheets
Để hiểu phần này, hãy tìm hiểu về báo cáo tổng hợp theo tháng trước đó. Vì ở đây, tôi chỉ chia sẻ những thay đổi bạn cần thực hiện trong công thức trước đó.
Công thức số 3: Báo cáo tổng hợp theo năm và danh mục
=ArrayFormula(if(A2:A<>””,sumif(Sales!$B$2:$B&YEAR(Sales!$A$2:$A),$B$1:$G$1&$A$2:$A,Sales!$C$2:$C),))
Thay đổi trong công thức:
- Thay đổi hàm MONTH bằng hàm YEAR.
Thay đổi trong tiêu chí của tab ‘Summary’:
- Nhập các năm vào cột A2:A thay vì các số tháng.
Cách tạo báo cáo tổng hợp theo quý và danh mục bằng SUMIF trong Sheets
Báo cáo tổng hợp theo quý và danh mục cũng có thể được thực hiện bằng SUMIF. Tuy nhiên, bạn không thể sử dụng hàm QUARTER thay cho MONTH/YEAR vì nó chỉ hoạt động trong Query.
Vậy làm sao để cung cấp range SUMIF với các quý? Tôi có một công thức tùy chỉnh cho việc này – Extract Quarter from a Date in Google Sheets. Tôi sẽ sử dụng nó trong công thức kết quả hai chiều của SUMIF ở đây.
Công thức số 4: Báo cáo tổng hợp theo quý và danh mục
=ArrayFormula(if(A2:A<>””,sumif(Sales!$B$2:$B&ROUNDUP(month(Sales!C2:C)/3,0),$B$1:$G$1&$A$2:$A,Sales!$C$2:$C),))
Hãy thật chú ý đến ‘range’ của SUMIF trong công thức này. Thật không cần phải nói, hãy nhập 1, 2, 3 và 4 vào tab ‘Summary’ ô A2:A như tiêu chí.
Trừ khi công thức 2 và 4 (tháng và quý) không trả về kết quả chính xác nếu các ngày nằm trong hai năm khác nhau hoặc có thể nói là qua các năm.
Để tạo báo cáo tổng hợp theo tháng/quý và danh mục qua các năm, hãy sử dụng các công thức sau.
SUMIF trả về kết quả nhiều cột qua các năm trong Sheets
Đối với báo cáo tổng hợp theo tháng và danh mục qua các năm, hãy thay đổi công thức trong Công thức số 2 như sau.
Điều này giúp SUMIF trả về kết quả nhiều cột (kết quả hai chiều) trong một phạm vi ngày qua các năm.
SUMIF trả về kết quả tổng hợp theo tháng và danh mục qua các năm
Hãy xem Công thức số 2. Trong đó, thay thế phần month(Sales!$A$2:$A) bằng month(Sales!$A$2:$A)&”_”&YEAR(Sales!$A$2:$A). Vì vậy, công thức cuối cùng sẽ là:
Công thức số 5: Báo cáo tổng hợp theo tháng và danh mục qua các năm
=ArrayFormula(if(A2:A<>””,sumif(Sales!$B$2:$B&month(Sales!$A$2:$A)&”_”&YEAR(Sales!$A$2:$A),$B$1:$G$1&$A$2:$A,Sales!$C$2:$C),))
Trong tab ‘Summary’, nhập tiêu chí như hình dưới đây. Điều này giúp bạn sử dụng tiêu chí tháng và năm trong SUMIF trong Sheets.
SUMIF trả về kết quả tổng hợp theo quý và danh mục qua các năm
Xem Công thức số 4 ở trên. Thay đổi range SUMIF từ Sales!$B$2:$B&ROUNDUP(month(Sales!C2:C)/3,0) thành Sales!$B$2:$B&ROUNDUP(month(Sales!C2:C)/3,0)&”_”&YEAR(Sales!$A$2:$A).
Công thức số 6: Báo cáo tổng hợp theo quý và danh mục qua các năm
=ArrayFormula(if(A2:A<>””,sumif(Sales!$B$2:$B&ROUNDUP(month(Sales!C2:C)/3,0)&”_”&YEAR(Sales!$A$2:$A),$B$1:$G$1&$A$2:$A,Sales!$C$2:$C),))
Nhập tiêu chí trong tab ‘Summary’, cột A dưới dạng 1_2018 (quý_năm).
SUMIF này cũng sẽ trả về nhiều cột trong Google Sheets.
Đó là tất cả. Chúc bạn thành công!
