U8导excel会计科目表时常用excel公式

凭证号补0 添加4位长度的凭证号如： 0001

公式：=TEXT(A1,"0000")

科目编码长度不够 补位0

给5到6级科目编码补0

=LEFT(A1,12)&0&RIGHT(A1,1)

公式：长度为 11 和13的补位0
=IFS(LEN(A2)=13,LEFT(A2,12)&0&RIGHT(A2,1),LEN(A2)=11,LEFT(A2,10)&0&RIGHT(A2,1))

会计科目自动计算级次

编码长度为4位开始的 获取长度 len(A2)

例子：=IFS(D3=100,"满分",D3>=95,"优秀",D3>=85,"良好",D3>=70,"较好",D3>=60,"及格",D3<60,"不及格")

公式: =IFS(LEN(A2)=4,"1",LEN(A2)=6,"2",LEN(A2)=8,"3",LEN(A2)=10,"4",LEN(A2)=12,"5",LEN(A2)=14,"6",LEN(A2)=16,"7")

会计科目判断资产负债 权益 的借贷方向

获取第一个字符的值 =left(A2)

公式:
=IFS(LEFT(A2,1)="1","借方",LEFT(A2,1)="2","贷方",LEFT(A2,1)="3","贷方",LEFT(A2,1)="4","借方",LEFT(A2,1)="5","贷方")

会计科目判断资产 负债 权益

公式：
=IFS(LEFT(A2,1)="1","资产",LEFT(A2,1)="2","负债",LEFT(A2,1)="3","权益",LEFT(A2,1)="4","成本",LEFT(A2,1)="5","损益")

会计科目判断 资产负债权益的字母

公式:
=IFS(LEFT(A2,1)="1","ZC",LEFT(A2,1)="2","FZ",LEFT(A2,1)="3","QY",LEFT(A2,1)="4","CB",LEFT(A2,1)="5","SY")

会计科目期初判断借贷方

=IFS(E2=0,"贷",E2>0,"借")

临时替换日期格式

=IFS(C2=2007011,"2007-01-01",C2=2007021,"2007-02-01",C2=2007031,"2007-03-01",C2=2007041,"2007-04-01",C2=2007051,"2007-05-01",C2=2007061,"2007-06-01",C2=2007071,"2007-07-01",C2=2007081,"2007-08-01",C2=2007091,"2007-09-01",C2=2007101,"2007-10-01",C2=2007111,"2007-11-01",C2=2007121,"2007-12-01")

删除所有凭证

delete gl_accvouch

删除所有科目期初余额

GL_accass 辅助总账,GL_accsum 科目总账,GL_accvouch凭证及明细账,GL_AccMultiAss多辅助明细账汇总表.

DELETE GL_accsum
DELETE code