Tải xuống sổ làm việc mẫu
Hướng dẫn này sẽ trình bày cách sử dụng Hàm SUMIFS để tính tổng dữ liệu tương ứng với các tháng cụ thể trong Excel và Google Trang tính.
Tổng nếu theo tháng
Đầu tiên, chúng tôi sẽ trình bày cách tính tổng dữ liệu tương ứng với các ngày rơi vào một tháng và năm cụ thể.
Chúng ta có thể sử dụng Hàm SUMIFS, cùng với các Hàm DATE, YEAR, MONTH và EOMONTH để tính tổng Số lượng bán hàng trong từng Tháng.
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & NGÀY (NĂM (E3), THÁNG (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Để xây dựng công thức ở trên, chúng tôi bắt đầu bằng cách xác định phạm vi ngày cho mỗi tháng. Chúng tôi sử dụng Hàm DATE để xác định ngày đầu tiên của tháng (ví dụ: 5/1/2021). Chúng ta có thể làm điều này bằng cách "mã hóa cứng" ngày vào công thức:
| 1 | "> =" & DATE (2021,5,1) |
Hoặc, tốt hơn, chúng tôi có thể làm cho tiêu chí linh hoạt bằng cách tham chiếu đến một ngày trong ô E3, trích xuất năm và tháng của ngày (và đặt ngày bằng 1), như được hiển thị trong ví dụ trên:
| 1 | "> =" & NGÀY (NĂM (E3), THÁNG (E3), 1) |
Để xác định ngày cuối cùng của tháng, chúng ta có thể sử dụng Hàm EOMONTH:
| 1 | "<=" & EOMONTH (E3,0) |
Kết hợp tất cả các tiêu chí này lại với nhau, chúng ta có thể viết công thức SUMIFS sau:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & NGÀY (NĂM (E3), THÁNG (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Khóa tham chiếu ô
Để làm cho các công thức của chúng tôi dễ đọc hơn, chúng tôi đã hiển thị các công thức mà không có tham chiếu ô bị khóa:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & NGÀY (NĂM (E3), THÁNG (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Nhưng những công thức này sẽ không hoạt động bình thường khi sao chép và dán ở nơi khác trong tệp của bạn. Thay vào đó, bạn nên sử dụng các tham chiếu ô bị khóa như sau:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "> =" & NGÀY (NĂM (E3), THÁNG (E3), 1), $ B $ 3: $ B $ 9, "<=" & EOMONTH (E3,0)) |
Đọc bài viết của chúng tôi về Khóa tham chiếu ô để tìm hiểu thêm.
Định dạng giá trị tháng
Trong ví dụ này, chúng tôi đã liệt kê các tháng trong cột E. Các giá trị tháng này thực tế là ngày được định dạng để bỏ qua ngày, sử dụng định dạng số tùy chỉnh.
Định dạng dữ liệu tùy chỉnh là “mmm yyyy” để hiển thị vào tháng 5 năm 2022.
Tổng theo tháng trong nhiều năm
Ví dụ trên đã tổng hợp dữ liệu với các ngày rơi vào một tháng và năm cụ thể. Thay vào đó, bạn có thể tính tổng dữ liệu với các ngày rơi vào một tháng trong năm bất kỳ bằng cách sử dụng Hàm SUMPRODUCT.
| 1 | = SUMPRODUCT (C3: C8, - (MONTH (B3: B8) = MONTH (G3))) |
Trong ví dụ này, chúng tôi sử dụng Hàm SUMPRODUCT để thực hiện các phép tính phức tạp "tính tổng nếu". Hãy xem qua công thức.
Đây là công thức cuối cùng của chúng tôi:
| 1 | = SUMPRODUCT (C3: C8, - (MONTH (B3: B8) = MONTH (G3))) |
Đầu tiên, Hàm SUMPRODUCT liệt kê Số lượng bán hàng cho mỗi Ngày bán hàng và sau đó so sánh tháng của mỗi Ngày bán hàng chống lại những gì được chỉ định Tháng, trả về TRUE nếu các tháng khớp nhau hoặc FALSE nếu chúng không khớp:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34}, - ({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE})) |
Tiếp theo, dấu gạch ngang kép (-) chuyển đổi các giá trị TRUE và FALSE thành 1s và 0s:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34}, {1; 0; 1; 0; 1; 0}) |
Sau đó, Hàm SUMPRODUCT nhân từng cặp mục nhập trong mảng:
| 1 | = SUMPRODUCT ({30; 0; 51; 0; 17; 0}) |
Cuối cùng, các số trong mảng được tính tổng:
| 1 | =98 |
Bạn có thể tìm thêm thông tin chi tiết về cách sử dụng câu lệnh Boolean và lệnh “-” trong Hàm SUMPRODUCT tại đây
Tổng nếu theo tháng trong Google Trang tính
Các công thức này hoạt động giống hệt nhau trong Google Trang tính cũng như trong Excel.
