Một độc giả đã gửi cho tôi một bài toán công thức thú vị trong tuần này, vì vậy tôi nghĩ rằng tôi sẽ chia sẻ nó như một thử thách công thức. Vấn đề là đây:
Bạn có một khoản thanh toán hàng tháng cố định, ngày bắt đầu và một số tháng nhất định. Bạn có thể sử dụng công thức nào để tính tổng các khoản thanh toán theo năm, dựa trên bảng tính sau:
Nói cách khác, công thức nào hoạt động trong E5, được sao chép sang I5, để nhận tổng cho mỗi năm được hiển thị?
Tôi đã tự nghĩ ra một công thức, nhưng tôi cũng muốn xem ý tưởng của bạn. Nếu bạn quan tâm, hãy để lại bình luận với công thức bạn đề xuất.
Bạn có thể sử dụng các phạm vi được đặt tên sau trong công thức của mình nếu bạn thích: mos (C5), số tiền (C6), bắt đầu (C7), kết thúc (C8).
Bạn có thể tải xuống bảng tính bên dưới.
Trả lời (bấm để mở rộng)Rất nhiều công thức tuyệt vời! Cảm ơn tất cả mọi người đã dành thời gian để gửi câu trả lời. Dưới đây là những suy nghĩ lan man của tôi về vấn đề và một số giải pháp bên dưới.
Lưu ý: Tôi chưa bao giờ làm rõ cách xử lý ranh giới tháng. Tôi chỉ đang theo dõi một trang tính khác làm ví dụ. Thông tin chính của họ là 30 thanh toán, bắt đầu từ ngày 1 tháng 3: 10 thanh toán vào năm 2017, 12 thanh toán vào năm 2018 và 8 thanh toán (số dư) vào năm 2019.
Vì vậy, nếu bạn đang đấu tranh để hiểu cách bạn có thể cố gắng giải quyết một vấn đề như thế này, trước tiên hãy tập trung vào thanh toán. Khi bạn biết có bao nhiêu khoản thanh toán trong một năm, bạn chỉ cần nhân số đó với số tiền là xong.
Vì vậy, làm thế nào bạn có thể tìm thấy số lần thanh toán trong một năm nhất định? Trong phần bình luận bên dưới, bạn sẽ tìm thấy rất nhiều ý tưởng hay. Có một số mẫu tôi nhận thấy và tôi đã liệt kê một số mẫu bên dưới. Công việc này đang được tiến hành…
Mẫu thiết kế
IF + AND/OR + YEAR + MONTH
IF là một chế độ chờ đáng tin cậy trong rất nhiều công thức và nó được sử dụng trong nhiều công thức được đề xuất để tìm ra liệu năm quan tâm có nằm trong giới hạn của ngày bắt đầu và ngày kết thúc hay không. Trong nhiều trường hợp, IF được kết hợp với OR hoặc AND để giữ các công thức nhỏ gọn.
IFERROR + DATEDIF + MAX + MIN
DATEDIF có thể trả về sự khác biệt giữa hai ngày trong tháng, vì vậy ý tưởng ở đây là sử dụng MAX và MIN (ngắn gọn, thay vì IF) để tính toán ngày bắt đầu và ngày kết thúc cho mỗi năm và để DATEDIF lấy các tháng giữa. DATEDIF đưa ra lỗi #NUM khi ngày bắt đầu không nhỏ hơn ngày kết thúc, vì vậy IFERROR được sử dụng để bắt lỗi và trả về 0. Xem công thức của 闫 强, Arun và David bên dưới.
MAX + MIN + YEAR + MONTH
Các công thức của Robert và Peter hầu như thực hiện tất cả các công việc với MAX và MIN, không có IF trong tầm nhìn. Kinh ngạc. Nếu ý tưởng sử dụng MAX và MIN để thay thế IF là mới đối với bạn, thì bài viết này giải thích khái niệm này.
DAYS360
Hàm DAYS360 trong Excel trả về số ngày giữa hai ngày dựa trên một năm 360 ngày. Đó là một cách tính tháng dựa trên ý tưởng rằng mỗi tháng có 30 ngày.
SUM + DATE
Đây là cách tiếp cận không hiệu quả (nhưng thanh lịch!) Của tôi bằng cách sử dụng hàm DATE và một hằng số mảng với một số cho mỗi tháng. hàm DATE quay một ngày cho mỗi tháng trong năm bằng cách sử dụng một hằng số mảng và logic boolean được sử dụng để kiểm tra chồng chéo.
