Công thức chung
=SUMIFS(values,date_range,">="&A1,date_range,"<="&EOMONTH(A1,0))
Tóm lược
Để tính tổng theo tháng, bạn có thể sử dụng công thức dựa trên hàm SUMIFS, với sự trợ giúp từ hàm EOMONTH. Trong ví dụ được hiển thị, công thức trong F4 là:
=SUMIFS(amount,date,">="&E4,date,"<="&EOMONTH(E4,0))
Công thức này sử dụng phạm vi được đặt tên là "số tiền" (C4: C9) và "ngày" (B4: B9).
Giải trình
Hàm SUMIFS có thể tính tổng các giá trị trong phạm vi dựa trên nhiều tiêu chí.
Trong trường hợp này, chúng tôi định cấu hình SUMIFS để tính tổng số tiền theo tháng bằng cách sử dụng hai tiêu chí: một để khớp với ngày lớn hơn hoặc bằng ngày đầu tiên của tháng, một để khớp với ngày nhỏ hơn hoặc bằng ngày cuối cùng của tháng. Nói cách khác, chúng ta cần một công thức như sau:
=SUMIFS(amount,date,">="&"1/1/2016",date,"<="&"1/31/2016")
Hoặc giải pháp thay thế chống đạn tốt hơn:
=SUMIFS(amount,date,">="&DATE(2016,1,1),date,"<="&DATE(2016,1,31))
Phiên bản này mạnh mẽ hơn vì ngày tháng được xây dựng với các giá trị năm, tháng và ngày riêng biệt và không được nhập dưới dạng văn bản cần được Excel diễn giải.
Thông thường, đây là một khó khăn, bởi vì nếu bạn thêm tên tháng dưới dạng văn bản (ví dụ: "Tháng Giêng", "Tháng Hai", "Tháng Ba", v.v.) trong cột E, bạn phải gặp thêm rắc rối để tạo ngày bạn có thể sử dụng cho tiêu chí . Tuy nhiên, trong trường hợp này, chúng tôi sử dụng một thủ thuật đơn giản để giúp mọi thứ dễ dàng: Trong cột E, thay vì nhập tên tháng, chúng tôi thêm ngày thực tế (1/1/2016, 2/1/2016, 3/1/2016), sau đó sử dụng định dạng ngày tùy chỉnh ("mmmm") để hiển thị tên tháng.
Điều này giúp bạn dễ dàng xây dựng các tiêu chí chúng ta cần cho SUMIFS. Để đối sánh các ngày lớn hơn hoặc bằng ngày đầu tiên của tháng, chúng tôi sử dụng:
">="&E4
Và để khớp các ngày nhỏ hơn hoặc bằng ngày cuối cùng của tháng, chúng tôi sử dụng:
"<="&EOMONTH(E4,0)
Với số 0 trong tháng, hàm EOMONTH tự động trả về ngày cuối cùng của tháng được cung cấp bởi ngày.
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ới giá trị số.
Giải pháp Pivot Table
Bảng tổng hợp là một giải pháp tuyệt vời khi bạn cần tóm tắt dữ liệu theo năm, tháng, quý, v.v., bởi vì chúng sẽ thực hiện loại nhóm này cho bạn 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.
