本帖最后由 水之歌 于 2011-12-29 01:24 编辑
在"银行帐"工作表中:
在G7单元格,设置"数据有效性",操作步骤:点"数据"菜单-“数据有效性”-设置,在有效性条件:允许处,选“序列”,来源处
,输入“=摘要”-确定;然后格式下拉至G99;
在L7单元格直接输入数据;
在P7单元格输入=H7*L7
在B8单元格直接输入1;
在C8单元格设置“数据有效性”,操作步骤:点"数据"菜单-“数据有效性”-设置,在有效性条件:允许处,选“序列”,来源
处,输入“=日”-确定;然后格式下拉至C99;
在F8单元格设置“数据有效性”,操作步骤:点"数据"菜单-“数据有效性”-设置,在有效性条件:允许处,选“序列”,来源
处,输入“=科目”-确定;然后格式下拉至F99;
在L8单元格输入公式=IF(G8="",0,IF(OR(G8={"过 次 页","承 前 页","本月合计","本年累计"}),L7,L7+I8-J8)),然后下
拉公式至L99;
在M8单元格输入公式=I8*H$8,然后下拉公式至M99;
在N8单元格输入公式=J8*H$8,然后下拉公式至N99;
在P8单元格输入公式=IF(G8="",0,IF(OR(G8={"过 次 页","承 前 页","本月合计","本年累计"}),P7,P7+M8-N8))然后下拉
公式至P99;
在B9单元格输入公式=IF(C9="",0,IF(G8="本月合计",B7+1,IF(G8="本年累计",B6+1,IF(G8="承 前 页",B6,B8)))),然后下
拉公式至B99;
打印页面的银行存款日记帐区域:
W7=B7
X7=C7
Y7=D7
Z7=E7
AA7=F7
AB7=G7
AC7=H7
AD7=IF(LEN(H7*100)>=12,MIDB(H7*100,LEN(H7*100)-11,1),"")
AE7=IF(LEN(H7*100)>=11,MIDB(H7*100,LEN(H7*100)-10,1),"")
AF7=IF(LEN(H7*100)>=10,MIDB(H7*100,LEN(H7*100)-9,1),"")
AG7=IF(LEN(H7*100)>=9,MIDB(H7*100,LEN(H7*100)-8,1),"")
AH7=IF(LEN(H7*100)>=8,MIDB(H7*100,LEN(H7*100)-7,1),"")
AI7=IF(LEN(H7*100)>=7,MIDB(H7*100,LEN(H7*100)-6,1),"")
AJ7=IF(LEN(H7*100)>=6,MIDB(H7*100,LEN(H7*100)-5,1),"")
AK7=IF(LEN(H7*100)>=5,MIDB(H7*100,LEN(H7*100)-4,1),"")
AL7=IF(LEN(H7*100)>=4,MIDB(H7*100,LEN(H7*100)-3,1),"")
AM7=IF(LEN(H7*100)>=3,MIDB(H7*100,LEN(H7*100)-2,1),"")
AN7=IF(LEN(H7*100)>=2,MIDB(H7*100,LEN(H7*100)-1,1),"")
AO7=IF(H7=0,"",MIDB(H7*100,LEN(H7*100),1))
AP7=IF(LEN(J7*100)>=12,MIDB(J7*100,LEN(J7*100)-11,1),"")
AQ7=IF(LEN(H7*100)>=11,MIDB(H7*100,LEN(H7*100)-10,1),"")
AR7=IF(LEN(H7*100)>=10,MIDB(H7*100,LEN(H7*100)-9,1),"")
AS7=IF(LEN(H7*100)>=9,MIDB(H7*100,LEN(H7*100)-8,1),"")
AT7=IF(LEN(H7*100)>=8,MIDB(H7*100,LEN(H7*100)-7,1),"")
AU7=IF(LEN(H7*100)>=7,MIDB(H7*100,LEN(H7*100)-6,1),"")
AV7=IF(LEN(H7*100)>=6,MIDB(H7*100,LEN(H7*100)-5,1),"")
AW7=IF(LEN(H7*100)>=5,MIDB(H7*100,LEN(H7*100)-4,1),"")
AX7=IF(LEN(H7*100)>=4,MIDB(H7*100,LEN(H7*100)-3,1),"")
AY7=IF(LEN(H7*100)>=3,MIDB(H7*100,LEN(H7*100)-2,1),"")
AZ7=IF(LEN(H7*100)>=2,MIDB(H7*100,LEN(H7*100)-1,1),"")
BA7=IF(H7=0,"",MIDB(H7*100,LEN(H7*100),1))
BC7=IF(LEN(L7*100)>=12,MIDB(L7*100,LEN(L7*100)-11,1),"")
BD7=IF(LEN(H7*100)>=11,MIDB(H7*100,LEN(H7*100)-10,1),"")
BE7=IF(LEN(H7*100)>=10,MIDB(H7*100,LEN(H7*100)-9,1),"")
BF7=IF(LEN(H7*100)>=9,MIDB(H7*100,LEN(H7*100)-8,1),"")
BG7=IF(LEN(H7*100)>=8,MIDB(H7*100,LEN(H7*100)-7,1),"")
BH7=IF(LEN(H7*100)>=7,MIDB(H7*100,LEN(H7*100)-6,1),"")
BI7=IF(LEN(H7*100)>=6,MIDB(H7*100,LEN(H7*100)-5,1),"")
BJ7=IF(LEN(H7*100)>=5,MIDB(H7*100,LEN(H7*100)-4,1),"")
BK7=IF(LEN(H7*100)>=4,MIDB(H7*100,LEN(H7*100)-3,1),"")
BL7=IF(LEN(H7*100)>=3,MIDB(H7*100,LEN(H7*100)-2,1),"")
BM7=IF(LEN(H7*100)>=2,MIDB(H7*100,LEN(H7*100)-1,1),"")
BN7=IF(H7=0,"",MIDB(H7*100,LEN(H7*100),1))
BO7=IF(LEN(L7*100)>=12,MIDB(L7*100,LEN(L7*100)-11,1),"")
BP7=IF(LEN(H7*100)>=11,MIDB(H7*100,LEN(H7*100)-10,1),"")
BQ7=IF(LEN(H7*100)>=10,MIDB(H7*100,LEN(H7*100)-9,1),"")
BR7=IF(LEN(H7*100)>=9,MIDB(H7*100,LEN(H7*100)-8,1),"")
BS7=IF(LEN(H7*100)>=8,MIDB(H7*100,LEN(H7*100)-7,1),"")
BT7=IF(LEN(H7*100)>=7,MIDB(H7*100,LEN(H7*100)-6,1),"")
BU7=IF(LEN(H7*100)>=6,MIDB(H7*100,LEN(H7*100)-5,1),"")
BV7=IF(LEN(H7*100)>=5,MIDB(H7*100,LEN(H7*100)-4,1),"")
BW7=IF(LEN(H7*100)>=4,MIDB(H7*100,LEN(H7*100)-3,1),"")
BX7=IF(LEN(H7*100)>=3,MIDB(H7*100,LEN(H7*100)-2,1),"")
BY7=IF(LEN(H7*100)>=2,MIDB(H7*100,LEN(H7*100)-1,1),"")
BZ7=IF(H7=0,"",MIDB(H7*100,LEN(H7*100),1))
CA7=IF(LEN(L7*100)>=12,MIDB(L7*100,LEN(L7*100)-11,1),"")
CB7=IF(LEN(H7*100)>=11,MIDB(H7*100,LEN(H7*100)-10,1),"")
CC7=IF(LEN(H7*100)>=10,MIDB(H7*100,LEN(H7*100)-9,1),"")
CD7=IF(LEN(H7*100)>=9,MIDB(H7*100,LEN(H7*100)-8,1),"")
CE7=IF(LEN(H7*100)>=8,MIDB(H7*100,LEN(H7*100)-7,1),"")
CF7=IF(LEN(H7*100)>=7,MIDB(H7*100,LEN(H7*100)-6,1),"")
CG7=IF(LEN(H7*100)>=6,MIDB(H7*100,LEN(H7*100)-5,1),"")
CH7=IF(LEN(H7*100)>=5,MIDB(H7*100,LEN(H7*100)-4,1),"")
CI7=IF(LEN(H7*100)>=4,MIDB(H7*100,LEN(H7*100)-3,1),"")
CJ7=IF(LEN(H7*100)>=3,MIDB(H7*100,LEN(H7*100)-2,1),"")
CK7=IF(LEN(H7*100)>=2,MIDB(H7*100,LEN(H7*100)-1,1),"")
CL7=IF(H7=0,"",MIDB(H7*100,LEN(H7*100),1))
CN7=IF(LEN(L7*100)>=12,MIDB(L7*100,LEN(L7*100)-11,1),"")
CO7=IF(LEN(H7*100)>=11,MIDB(H7*100,LEN(H7*100)-10,1),"")
CP7=IF(LEN(H7*100)>=10,MIDB(H7*100,LEN(H7*100)-9,1),"")
CQ7=IF(LEN(H7*100)>=9,MIDB(H7*100,LEN(H7*100)-8,1),"")
CR7=IF(LEN(H7*100)>=8,MIDB(H7*100,LEN(H7*100)-7,1),"")
CS7=IF(LEN(H7*100)>=7,MIDB(H7*100,LEN(H7*100)-6,1),"")
CT7=IF(LEN(H7*100)>=6,MIDB(H7*100,LEN(H7*100)-5,1),"")
CU7=IF(LEN(H7*100)>=5,MIDB(H7*100,LEN(H7*100)-4,1),"")
CV7=IF(LEN(H7*100)>=4,MIDB(H7*100,LEN(H7*100)-3,1),"")
CW7=IF(LEN(H7*100)>=3,MIDB(H7*100,LEN(H7*100)-2,1),"")
CX7=IF(LEN(H7*100)>=2,MIDB(H7*100,LEN(H7*100)-1,1),"")
CY7=IF(H7=0,"",MIDB(H7*100,LEN(H7*100),1))
以上各公式都下拉
|