Làm việc trên một báo cáo, kế hoạch đầu tư hoặc bất kỳ tập dữ liệu nào khác có ngày tháng, bạn thường có thể cần tính tổng các con số trong một khoảng thời gian cụ thể. Hướng dẫn này sẽ dạy cho bạn một giải pháp nhanh chóng và dễ dàng – công thức SUMIFS với phạm vi ngày làm tiêu chí.
Trên blog của chúng tôi và các diễn đàn Excel khác, mọi người thường hỏi cách sử dụng SUMIF cho phạm vi ngày. Vấn đề là để tính tổng giữa hai ngày, bạn cần xác định cả hai ngày trong khi Excel SUMIF hàm chỉ cho phép một điều kiện. May mắn thay, chúng tôi cũng có hàm SUMIFS hỗ trợ nhiều tiêu chí.
Cách tính tổng nếu giữa hai ngày trong Excel
Để tính tổng các giá trị trong một phạm vi ngày nhất định, hãy sử dụng công thức SUMIFS với ngày bắt đầu và ngày kết thúc làm tiêu chí. Cú pháp của Hàm SUMIFS yêu cầu trước tiên bạn chỉ định các giá trị để cộng (sum_range), sau đó cung cấp các cặp phạm vi / tiêu chí. Trong trường hợp của chúng tôi, phạm vi (danh sách ngày) sẽ giống nhau cho cả hai tiêu chí.
Xét ở trên, các công thức chung để tính tổng các giá trị giữa hai ngày có dạng sau:
Bao gồm các ngày ràng buộc:
SUMIFS (sum_range, ngày, “> =” &start_date, ngày, “<=” &ngày cuối)
Không bao gồm các ngày bị ràng buộc:
SUMIFS (sum_range, ngày, “>” &start_date, ngày, “<” &ngày cuối)
Như bạn có thể thấy, sự khác biệt chỉ là ở các toán tử logic. Trong công thức đầu tiên, chúng tôi sử dụng lớn hơn hoặc là tương đương với (> =) và ít hơn hoặc bằng (<=) để bao gồm ngày giới hạn dưới và trên trong kết quả. Công thức thứ hai kiểm tra xem một ngày là lớn hơn (>) hoặc ít hơn (<), bỏ qua ngày bắt đầu và ngày kết thúc.
Trong bảng bên dưới, giả sử bạn muốn tổng hợp các dự án đến hạn trong một phạm vi ngày cụ thể. Để hoàn tất, chúng tôi nhập ngày bắt đầu trong F1, ngày kết thúc trong G1 và sử dụng công thức sau để cộng ngân sách trong B2: B10 giữa hai ngày này, bao gồm:
=SUMIFS(B2:B10, C2:C10, ">="&F1, C2:C10, "<="&G1)
Nếu bạn thích mã hóa cứng một phạm vi ngày trong công thức, hãy nhập ngày ngay sau toán tử logic và đặt toàn bộ tiêu chí trong dấu ngoặc kép như sau:
=SUMIFS(B2:B10, C2:C10, ">=9/10/2020", C2:C10, "<=9/20/2020")
Để tránh những sai lầm có thể xảy ra, bạn có thể cung cấp ngày tháng với sự trợ giúp của NGÀY chức năng:
=SUMIFS(B2:B10, C2:C10, ">="&DATE(2020,9,10), C2:C10, "<="&DATE(2020,9,20))
Tính tổng trong một phạm vi động dựa trên ngày hôm nay
Trong trường hợp bạn cần tổng hợp dữ liệu trong phạm vi ngày động (X ngày trở lại từ hôm nay hoặc Y ngày trở đi), hãy xây dựng tiêu chí bằng cách sử dụng HÔM NAY chức năng này sẽ lấy ngày hiện tại và cập nhật tự động.
Ví dụ: để tổng hợp các ngân sách đến hạn trong 7 ngày qua bao gồm cả ngày của ngày, công thức là:
=SUMIFS(B2:B10, C2:C10, "<="&TODAY(), C2:C10, ">"&TODAY()-7)
Nếu bạn không muốn đưa ngày hiện tại vào kết quả cuối cùng, hãy sử dụng ít hơn toán tử (<) cho tiêu chí đầu tiên để loại trừ ngày hôm nay và lớn hơn hoặc bằng (> =) cho tiêu chí thứ hai bao gồm ngày trước ngày hôm nay 7 ngày:
=SUMIFS(B2:B10, C2:C10, "<"&TODAY(), C2:C10, ">="&TODAY()-7)
Theo cách tương tự, bạn có thể tính tổng các giá trị nếu một ngày là một số ngày nhất định về sau.
Ví dụ: để nhận được tổng số ngân sách đến hạn trong 3 ngày tới, hãy sử dụng một trong các công thức sau:
Ngày hôm nay được bao gồm trong kết quả:
=SUMIFS(B2:B10, C2:C10, ">="&TODAY(), C2:C10, "<"&TODAY()+3)
Ngày hôm nay không được bao gồm trong kết quả:
=SUMIFS(B2:B10, C2:C10, ">"&TODAY(), C2:C10, "<="&TODAY()+3)
Tính tổng nếu giữa hai ngày và một tiêu chí khác
Để tính tổng các giá trị trong phạm vi ngày đáp ứng một số điều kiện khác trong một cột khác, chỉ cần thêm một cặp phạm vi / tiêu chí nữa vào công thức SUMIFS của bạn.
Ví dụ: để tổng hợp ngân sách trong một phạm vi ngày nhất định cho tất cả các dự án có chứa “tiền boa” trong tên của chúng, hãy mở rộng công thức với tiêu chí ký tự đại diện:
=SUMIFS(B2:B10, C2:C10, ">="&F1, C2:C10, "<="&G1, A2:A10, "tip*")
Trong đó A2: A10 là tên dự án, B2: B10 là số tổng, C2: C10 là ngày cần kiểm tra, F1 là ngày bắt đầu và G1 là ngày kết thúc.
Tất nhiên, không có gì ngăn cản bạn nhập tiêu chí thứ ba vào một ô riêng biệt và tham chiếu ô đó như được hiển thị trong ảnh chụp màn hình:
Cú pháp tiêu chí ngày SUMIFS
Khi nói đến việc sử dụng ngày tháng làm tiêu chí cho các hàm SUMIF và SUMIFS trong Excel, bạn sẽ không phải là người đầu tiên bối rối 🙂
Tuy nhiên, khi xem xét kỹ hơn, tất cả các trường hợp sử dụng đa dạng đều tóm gọn lại một vài quy tắc đơn giản:
Nếu bạn đặt ngày trực tiếp trong đối số tiêu chí, sau đó nhập một toán tử logic (>, <, =, <>) ngay trước ngày và đặt toàn bộ tiêu chí trong dấu ngoặc kép. Ví dụ:
=SUMIFS(B2:B10, C2:C10, ">=9/10/2020", C2:C10, "<=9/20/2020")
Khi một ngày được nhập vào một ngày được xác định trước ô, cung cấp tiêu chí dưới dạng một chuỗi văn bản: đặt một toán tử logic trong dấu ngoặc kép để bắt đầu một chuỗi và sử dụng dấu và (&) để nối và kết thúc chuỗi. Ví dụ:
=SUMIFS(B2:B10, C2:C10, ">="&F1, C2:C10, "<="&G1)
Khi một ngày được thúc đẩy bởi chức năng khác chẳng hạn như DATE hoặc TODAY (), nối một toán tử so sánh và một hàm. Ví dụ:
=SUMIFS(B2:B10, C2:C10, ">="&DATE(2020,9,10), C2:C10, "<="&TODAY())
Excel SUMIFS giữa các ngày không hoạt động
Trong trường hợp công thức của bạn không hoạt động hoặc tạo ra kết quả sai, các mẹo khắc phục sự cố sau đây có thể làm sáng tỏ lý do tại sao nó không thành công và giúp bạn khắc phục sự cố.
Kiểm tra định dạng ngày và số
Nếu một công thức SUMIFS có vẻ đúng không trả về giá trị nào ngoài số 0, điều đầu tiên cần kiểm tra là ngày tháng của bạn có thực sự là ngày tháng và không phải là chuỗi văn bản chỉ trông giống như ngày tháng. Tiếp theo, hãy chắc chắn rằng bạn đang tính tổng các số chứ không phải các số được lưu trữ dưới dạng văn bản. Các hướng dẫn sau đây sẽ giúp bạn phát hiện và khắc phục những vấn đề này.
Sử dụng cú pháp chính xác cho tiêu chí
Khi kiểm tra ngày bằng cách sử dụng SUMIFS, một ngày nên được đặt bên trong dấu ngoặc kép như “> = 9/10/2020”; tham chiếu ô và chức năng phải được đặt bên ngoài dấu ngoặc kép như “<=” & G1 hoặc “<=” & TODAY (). Để biết chi tiết đầy đủ, vui lòng xem cú pháp tiêu chí ngày tháng.
Xác minh logic của công thức
Một lỗi đánh máy nhỏ trong ngân sách có thể tiêu tốn hàng triệu USD. Một chút sai sót trong công thức có thể tốn hàng giờ gỡ lỗi. Vì vậy, khi tính tổng giữa 2 ngày, hãy kiểm tra xem ngày bắt đầu có trước lớn hơn (>) hoặc lớn hơn hoặc bằng (> =) toán tử và ngày kết thúc được bắt đầu bằng ít hơn (<) hoặc ít hơn hoặc bằng (<=).
Đảm bảo rằng tất cả các phạm vi đều có cùng kích thước
Để hàm SUMIFS hoạt động chính xác, phạm vi tổng và phạm vi tiêu chí phải có kích thước bằng nhau, nếu không thì lỗi #VALUE! lỗi xảy ra. Để khắc phục, hãy đảm bảo rằng tất cả tiêu_phí các đối số có cùng số hàng và số cột như sum_range.
Đó là cách sử dụng hàm SUMIFS trong Excel để tính tổng dữ liệu trong một phạm vi ngày. Nếu bạn có một số giải pháp thú vị khác, tôi sẽ thực sự biết ơn nếu bạn chia sẻ trong nhận xét. Cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!
The post Tính tổng các giá trị giữa 2 ngày trong Excel với Sumif appeared first on Kế Toán MVB | Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng.
source https://ketoanmvb.com/tinh-tong-cac-gia-tri-giua-2-ngay-trong-excel-voi-sumif.html
Không có nhận xét nào:
Đăng nhận xét