Công thức chung
=SUMPRODUCT((MONTH(dates)=month)*amounts)
Tóm lược
Để tổng hợp dữ liệu theo tháng, bỏ qua năm, bạn có thể sử dụng công thức dựa trên hàm SUMPRODUCT và MONTH. Trong ví dụ được hiển thị, công thức trong H6 là:
=SUMPRODUCT((MONTH(dates)=3)*amounts)
Kết quả là tổng tất cả doanh số bán hàng trong tháng Ba, bỏ qua năm.
Giải trình
Tập dữ liệu này chứa hơn 2900 bản ghi và công thức ở trên sử dụng hai phạm vi được đặt tên:
dates = B5:B2932 amounts = E5:E2932
Bên trong hàm SUMPRODUCT, hàm MONTH được sử dụng để trích xuất số tháng cho mỗi ngày trong tập dữ liệu và so sánh nó với số 3:
(MONTH(dates)=3)
Nếu chúng ta giả sử một tập dữ liệu nhỏ liệt kê 3 ngày mỗi tháng trong tháng Giêng, tháng Hai và tháng Ba (theo thứ tự đó), kết quả sẽ là một mảng chứa chín số như sau:
(1;1;1;2;2;2;3;3;3)
trong đó mỗi số là "số tháng" cho một ngày. Khi các giá trị được so sánh với 3, kết quả là một mảng như sau:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE)
Mảng này sau đó được nhân với các giá trị số tiền được liên kết với mỗi ngày tháng 3. Nếu chúng ta giả sử rằng tất cả chín số tiền đều bằng 100, thì phép toán sẽ giống như sau:
(0;0;0;0;0;0;1;1;1) * (100;100;100;100;100;100;100;100;100)
Lưu ý rằng phép toán thay đổi các giá trị TRUE FALSE thành các giá trị đơn vị và số không. Sau khi nhân, chúng ta có một mảng duy nhất trong SUMPRODUCT:
=SUMPRODUCT((0;0;0;0;0;0;100;100;100))
Lưu ý rằng số tiền duy nhất còn sót lại được liên kết với tháng Ba, số còn lại bằng không.
Cuối cùng, hàm SUMPRODUCT trả về tổng của tất cả các mục - 300 trong ví dụ viết tắt ở trên và 25,521 trong ảnh chụp màn hình với dữ liệu thực tế.
Đếm theo tháng bỏ qua năm
Để đếm theo tháng bỏ qua năm, bạn có thể sử dụng SUMPRODUCT như sau:
=SUMPRODUCT(--(MONTH(dates)=3))
Trung bình theo tháng bỏ qua năm
Để tính toán và trung bình theo tháng bỏ qua năm, bạn kết hợp hai công thức SUMPRODUCT ở trên như sau:
=SUMPRODUCT((MONTH(dates)=3)*amounts)/SUMPRODUCT(--(MONTH(dates)=3))
