Hôm nay Mr. Accouting xin tiếp tục hướng dẫn tạo bảng cân đối phát sinh cho doanh nghiệp vừa và nhỏ bằng Excel.
Tham khảo hình ảnh minh họa bảng cân đối phát sinh :
Bạn tự tạo bảng theo mẫu trên, sau đây là phần code tạo công thức cho bảng tính Excel :
Các cột nhập liệu :
Cột C nhập danh sách tài khoản cần xử lý, tạo dữ liệu đầu kỳ, cuối kỳ, phát sinh nợ có để tạo bảng cân đối phát sinh từ các sheet excel trên bảng tính.
Cột D nhập tên tài khoản.
Sheet("CDPS").Select
Tạo công thức :
Cột F : "Dư nợ đầu kỳ" :
Range("F5").Formula = "=IF(OR($N$1=1,$N$1=""N"",$N$1=""Q1""),$ACK5,IF($N$1=2,MAX(ACK5+ACM5-ACL5-ACN5,0),IF($N$1=3,MAX(ACK5+ACM5+ACO5-ACL5-ACN5-ACP5,0),IF($N$1=4,MAX(ACK5+ACM5+ACO5+ACQ5-ACL5-ACN5-ACP5-ACR5,0),IF($N$1=5,MAX(ACK5+ACM5+ACO5+ACQ5+ACS5-ACL5-ACN5-ACP5-ACR5-ACT5,0),IF($N$1=6,MAX(ACK5+ACM5+ACO5+ACQ5+ACS5+ACU5-ACL5-ACU5-ACN5-ACP5-ACR5-ACT5-ACV5,0),IF($N$1=7,MAX(ACK5+ACM5+ACO5+ACQ5+ACS5+ACU5+ACW5-ACL5-ACU5-ACN5-ACP5-ACR5-ACT5-ACV5-ACX5,0),IF($N$1=8,MAX(ACK5+ACM5+ACO5+ACQ5+ACS5+ACU5+ACW5+ACY5-ACL5-ACU5-ACN5-ACP5-ACR5-ACT5-ACV5-ACX5-ACZ5,0),IF($N$1=9,MAX(ACK5+ACM5+ACO5+ACQ5+ACS5+ACU5+ACW5+ACY5+ADA5-ACL5-ACU5-ACN5-ACP5-ACR5-ACT5-ACV5-ACX5-ACZ5-ADB5,0),IF($N$1=10,MAX(ACK5+ACM5+ACO5+ACQ5+ACS5+ACU5+ACW5+ACY5+ADA5+ADC5-ACL5-ACU5-ACN5-ACP5-ACR5-ACT5-ACV5-ACX5-ACZ5-ADB5-ADD5,0),IF($N$1=11,MAX(ACK5+ACM5+ACO5+ACQ5+ACS5+ACU5+ACW5+ACY5+ADA5+ADC5+ADE5-ACL5-ACU5-ACN5-ACP5-ACR5-ACT5-ACV5-ACX5-ACZ5-ADB5-ADD5-ADF5,0),IF($N$1=12,MAX(ACK5+ACM5+ACO5+ACQ5+ACS5+ACU5+ACW5+ACY5+ADA5+ADC5+ADE5+ADG5-ACL5-ACU5-ACN5-ACP5-ACR5-ACT5-ACV5-ACX5-ACZ5-ADB5-ADD5-ADF5-ADH5,0),IF($N$1=""Q2"",MAX(ACK5+ADK5-ACL5-ADL5,0),IF($N$1=""Q3"",MAX(ACK5+ADK5+ADM5-ACL5-ADL5-ADN5,0),IF($N$1=""Q4"",MAX(ACK5+ADK5+ADM5+ADO5-ACL5-ADL5-ADN5-ADP5,0),"""")))))))))))))))"
Cột G : "Dư có đầu kỳ" :
Range("G5").Formula = "=IF(OR($N$1=1,$N$1=""N"",$N$1=""Q1""),$ACL5,IF($N$1=2,MAX(ACL5+ACN5-ACK5-ACM5,0),IF($N$1=3,MAX(ACL5+ACN5+ACP5-ACK5-ACM5-ACO5,0),IF($N$1=4,MAX(ACL5+ACN5+ACP5+ACR5-ACK5-ACM5-ACO5-ACQ5,0),IF($N$1=5,MAX(ACL5+ACN5+ACP5+ACR5+ACT5-ACK5-ACM5-ACO5-ACQ5-ACS5,0),IF($N$1=6,MAX(ACL5+ACN5+ACP5+ACR5+ACT5+ACV5-ACK5-ACM5-ACO5-ACQ5-ACS5-ACU5,0),IF($N$1=7,MAX(ACL5+ACN5+ACP5+ACR5+ACT5+ACV5+ACX5-ACK5-ACM5-ACO5-ACQ5-ACS5-ACU5-ACW5,0),IF($N$1=8,MAX(ACL5+ACN5+ACP5+ACR5+ACT5+ACV5+ACX5+ACZ5-ACK5-ACM5-ACO5-ACQ5-ACS5-ACU5-ACW5-ACY5,0),IF($N$1=9,MAX(ACL5+ACN5+ACP5+ACR5+ACT5+ACV5+ACX5+ACZ5+ADB5-ACK5-ACM5-ACO5-ACQ5-ACS5-ACU5-ACW5-ACY5-ADA5,0),IF($N$1=10,MAX(ACL5+ACN5+ACP5+ACR5+ACT5+ACV5+ACX5+ACZ5+ADB5+ADD5-ACK5-ACM5-ACO5-ACQ5-ACS5-ACU5-ACW5-ACY5-ADA5-ADC5,0),IF($N$1=11,MAX(ACL5+ACN5+ACP5+ACR5+ACT5+ACV5+ACX5+ACZ5+ADB5+ADD5+ADF5-ACK5-ACM5-ACO5-ACQ5-ACS5-ACU5-ACW5-ACY5-ADA5-ADC5-ADE5,0),IF($N$1=12,MAX(ACL5+ACN5+ACP5+ACR5+ACT5+ACV5+ACX5+ACZ5+ADB5+ADD5+ADF5+ADH5-ACK5-ACM5-ACO5-ACQ5-ACS5-ACU5-ACW5-ACY5-ADA5-ADC5-ADE5-ADG5,0),IF($N$1=""Q2"",MAX(ACL5+ADL5-ACK5-ADK5,0),IF($N$1=""Q3"",MAX(ACL5+ADL5+ADN5-ACK5-ADK5-ADM5,0),IF($N$1=""Q4"",MAX(ACL5+ADL5+ADN5+ADP5-ACK5-ADK5-ADM5-ADO5,0),"""")))))))))))))))"
Cột H : "Phát sinh nợ" :
Range("H5").Formula = "=IF(AND($N$1>=1,$N$1<=12),SUMIF(mcdpsn,CĐPS!$N$1&""-""&CĐPS!$B5,stps),IF($N$1=""Q1"",$ADK5,IF($N$1=""Q2"",$ADM5,IF($N$1=""Q3"",$ADO5,IF($N$1="Q4",$ADQ5,IF($N$1="N",$ADS5,""))))))"
Cột I : "Phát sinh có" :
Range("I5").Formula = "=IF(AND($N$1>=1,$N$1<=12),SUMIF(mcdpsc,CĐPS!$N$1&"-"&CĐPS!$B5,stps),IF($N$1="Q1",$ADL5,IF($N$1="Q2",$ADN5,IF($N$1="Q3",$ADP5,IF($N$1="Q4",$ADR5,IF($N$1="N",$ADT5,""))))))"
Cột J : "Phát sinh Lũy kế nợ" :
Range("J5").Formula = "=IF($N$1=1,ACM5,IF($N$1=2,ACM5+ACO5,IF($N$1=3,ACM5+ACO5+ACQ5,IF($N$1=4,ACM5+ACO5+ACQ5+ACS5,IF($N$1=5,ACM5+ACO5+ACQ5+ACS5+ACU5,IF($N$1=6,ACM5+ACO5+ACQ5+ACS5+ACU5+ACW5,IF($N$1=7,ACM5+ACO5+ACQ5+ACS5+ACU5+ACW5+ACY5,IF($N$1=8,ACM5+ACO5+ACQ5+ACS5+ACU5+ACW5+ACY5+ADA5,IF($N$1=9,ACM5+ACO5+ACQ5+ACS5+ACU5+ACW5+ACY5+ADA5+ADC5,IF($N$1=10,ACM5+ACO5+ACQ5+ACS5+ACU5+ACW5+ACY5+ADA5+ADC5+ADE5,IF($N$1=11,ACM5+ACO5+ACQ5+ACS5+ACU5+ACW5+ACY5+ADA5+ADC5+ADE5+ADG5,IF($N$1=12,ACM5+ACO5+ACQ5+ACS5+ACU5+ACW5+ACY5+ADA5+ADC5+ADE5+ADG5+ADI5,IF($N$1="Q1",ADK5,IF($N$1="Q2",ADK5+ADM5,IF($N$1="Q3",ADK5+ADM5+ADO5,IF($N$1="Q4",ADK5+ADM5+ADO5+ADQ5,IF($N$1=""N"",ADS5,"""")))))))))))))))))"
Cột K : "Phát sinh Lũy kế có" :
Range("K5").Formula = "=IF($N$1=1,ACN5,IF($N$1=2,ACN5+ACP5,IF($N$1=3,ACN5+ACP5+ACR5,IF($N$1=4,ACN5+ACP5+ACR5+ACT5,IF($N$1=5,ACN5+ACP5+ACR5+ACT5+ACV5,IF($N$1=6,ACN5+ACP5+ACR5+ACT5+ACV5+ACX5,IF($N$1=7,ACN5+ACP5+ACR5+ACT5+ACV5+ACX5+ACZ5,IF($N$1=8,ACN5+ACP5+ACR5+ACT5+ACV5+ACX5+ACZ5+ADB5,IF($N$1=9,ACN5+ACP5+ACR5+ACT5+ACV5+ACX5+ACZ5+ADB5+ADD5,IF($N$1=10,ACN5+ACP5+ACR5+ACT5+ACV5+ACX5+ACZ5+ADB5+ADD5+ADF5,IF($N$1=11,ACN5+ACP5+ACR5+ACT5+ACV5+ACX5+ACZ5+ADB5+ADD5+ADF5+ADH5,IF($N$1=12,ACN5+ACP5+ACR5+ACT5+ACV5+ACX5+ACZ5+ADB5+ADD5+ADF5+ADH5+ADJ5,IF($N$1=""Q1"",ADL5,IF($N$1=""Q2"",ADL5+ADN5,IF($N$1=""Q3"",ADL5+ADN5+ADP5,IF($N$1=""Q4"",ADL5+ADN5+ADP5+ADR5,IF($N$1=""N"",ADT5,"""")))))))))))))))))"
Cột K : "Số dư nợ cuối kỳ" :
Range("K5").Formula = "=IF(ACK5-ACL5+J5-K5>0,ACK5-ACL5+J5-K5,0)"
Cột M : "Số dư nợ cuối kỳ" :
Range("M5").Formula = "=IF(ACL5-ACK5+K5-J5>0,ACL5-ACK5+K5-J5,0)"
0 nhận xét: