Công thức Excel: Giá trị cố định mỗi N cột -

Công thức chung

=IF(MOD(COLUMN(A1)-offset,N)=0,amount,0)

Tóm lược

Để tạo giá trị cố định mỗi N cột (ví dụ: chi phí cố định 3 tháng một lần, khoản thanh toán cố định 6 tháng một lần, v.v.), bạn có thể sử dụng công thức dựa trên hàm MOD.

Trong ví dụ được hiển thị, hãy tạo giá trị 60 sau mỗi 3 tháng. Công thức trong B8 là:

=IF(MOD(COLUMN(B8)-1,3)=0,$B$5,0)

Trả về 60 mỗi tháng thứ 3 và 0 cho các tháng khác.

Giải trình

Cốt lõi của công thức này là hàm MOD. MOD nhận một số và số chia, và trả về phần còn lại sau khi chia, điều này làm cho nó hữu ích cho các công thức cần thực hiện điều gì đó mỗi lần thứ n.

Trong trường hợp này, số được tạo bằng hàm COLUMN, trả về số cột của ô B8, số 2, trừ đi 1, được cung cấp dưới dạng "phần bù". Chúng tôi sử dụng một phần bù, vì chúng tôi muốn đảm bảo rằng chúng tôi bắt đầu đếm ở 1, bất kể số cột thực tế là bao nhiêu.

Số chia được mã hóa cứng là 3, vì chúng ta muốn làm điều gì đó vào mỗi tháng thứ 3. Bằng cách kiểm tra phần dư bằng 0, biểu thức này sẽ trả về TRUE ở tháng thứ 3, 6, 9 và 12:

MOD(COLUMN(B8)-1,3)=0

Cuối cùng, IF chỉ đơn giản đánh giá biểu thức MOD và trả về giá trị trong B5 (được mã hóa dưới dạng tham chiếu tuyệt đối để ngăn các thay đổi khi công thức được sao chép) khi TRUE và bằng không khi FALSE.