Thứ Năm, 24 tháng 12, 2020

Bảng cân đối Phát sinh - sổ sách kế toán cho doanh nghiệp vừa và nhỏ, TT133 bằng Excel

 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)"

Previous Post
Next Post

0 nhận xét: