Tóm lược
Để tính tổng theo tháng trong các cột, bạn có thể sử dụng hàm SUMIFS cùng với hàm EOMONTH. Trong ví dụ được hiển thị, công thức trong G5 là:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Công thức này sử dụng ba phạm vi được đặt tên: "số tiền" (D5: D15), "khách hàng" (B5: B15) và "ngày" (C5: C15).
Giải trình
Hàm SUMIFS được thiết kế để tính tổng các giá trị trong một phạm vi dựa trên một hoặc nhiều tiêu chí. Trong trường hợp này, chúng ta cần ba tiêu chí:
- Tên khách hàng = khách hàng trong cột F
- Ngày> = đầu tiên của tháng (từ ngày trong hàng 4)
- Ngày <= cuối tháng (từ ngày trong hàng 4)
Điểm mấu chốt: tên tháng trong G4: I4 thực sự là ngày hợp lệ, được định dạng với định dạng số tùy chỉnh "mmm". Điều này cho phép chúng tôi sử dụng trực tiếp các giá trị ngày trong G4: I4 cho tiêu chí số 2 và số 3 ở trên.
Quay lại SUMIFS, cặp phạm vi / tiêu chí đầu tiên được sử dụng để kiểm tra tên khách hàng:
client,$F5
Lưu ý F5 là một tham chiếu hỗn hợp, với cột bị khóa. Điều này cho phép hàng thay đổi khi công thức được sao chép qua bảng, nhưng tên khách hàng luôn xuất phát từ cột F.
Cặp phạm vi / tiêu chí thứ hai được sử dụng để kiểm tra ngày so với ngày đầu tiên của tháng:
date,">="&G$4
Như đã đề cập ở trên, các giá trị trong G4: I4 thực tế là các ngày: ngày 1 tháng 1 năm 2019, ngày 1 tháng 2 năm 2019 và ngày 1 tháng 3 năm 2019. Vì vậy, tiêu chí này chỉ kiểm tra các ngày lớn hơn ngày đầu tiên của tháng trong hàng 4. Lưu ý rằng đây là một tham chiếu hỗn hợp khác, lần này với hàng đã bị khóa. Điều này cho phép cột thay đổi khi công thức được sao chép trên bảng, nhưng giữ số hàng cố định. Việc nối với dấu và (&) là cần thiết khi xây dựng tiêu chí sử dụng toán tử logic và giá trị từ một ô khác.
Cặp phạm vi / tiêu chí thứ ba được sử dụng để kiểm tra ngày so với ngày cuối cùng của tháng:
date,"<="&EOMONTH(G$4,0)
Để lấy ngày cuối cùng của mỗi tháng, chúng tôi sử dụng hàm EOMONTH vào ngày từ tiêu đề cột ở hàng 4. Với số 0 được cung cấp cho đối số tháng, hàm EOMONTH trả về ngày cuối cùng của cùng tháng. Một lần nữa, tham chiếu đến G4 được trộn để giữ cho hàng không thay đổi.
Giải pháp Pivot Table
Bảng tổng hợp sẽ là một giải pháp tuyệt vời cho vấn đề này, bởi vì nó có thể tự động nhóm theo tháng mà không cần bất kỳ công thức nào. Để có so sánh song song giữa các công thức và bảng tổng hợp, hãy xem video này: Tại sao lại sử dụng bảng tổng hợp.
