Excel设计报销单,通过新建注释实现不打印标题只打印填写内容和金额

原创 kaixinit  2020-03-14 09:54  阅读 280 次

表格填写效果

表格打印预览效果

主要是通过设置新建注释实现以上效果,因为Excel 打印效果一般不会显示注释内容的,具体设置如下:

【新建注释】【显示注释】【选中注释表框右键】【设置批注格式】【设置字体】【设置颜色与线条】【设置大小】来去实现

接下来是年月日的公式设置,来去获取当天填写单据日期

年:=YEAR(TODAY())

月:=MONTH(TODAY())

日:=DAY(TODAY())

收款账户自动填写设置:(输入户名,卡号、开户行自动生成,如没有银行账户则空白)

新建一个工作表,记录账户信息,如户名、账号、开户行等

通过index match函数获取,设置如下:

户名:录入

账号:=IFERROR(INDEX(账户!B2:B50,MATCH(E5,账户!A2:A50,0)),"")

开户行:=IFERROR(INDEX(账户!C2:C50,MATCH(E5,账户!A2:A50,0)),"")

大写金额获取设置:

首先填写小写金额比如:12345.66元

百万位:=(TEXT(MID(SUBSTITUTE(TEXT($U8,"0000000.00"),".",""),COLUMN(A1),1),"[dbnum2]"))

十万位:=(TEXT(MID(SUBSTITUTE(TEXT($U8,"0000000.00"),".",""),COLUMN(B1),1),"[dbnum2]"))

万位:=(TEXT(MID(SUBSTITUTE(TEXT($U8,"0000000.00"),".",""),COLUMN(C1),1),"[dbnum2]"))

千位:=(TEXT(MID(SUBSTITUTE(TEXT($U8,"0000000.00"),".",""),COLUMN(D1),1),"[dbnum2]"))

百位:=(TEXT(MID(SUBSTITUTE(TEXT($U8,"0000000.00"),".",""),COLUMN(E1),1),"[dbnum2]"))

十位:=(TEXT(MID(SUBSTITUTE(TEXT($U8,"0000000.00"),".",""),COLUMN(F1),1),"[dbnum2]"))

个位:=(TEXT(MID(SUBSTITUTE(TEXT($U8,"0000000.00"),".",""),COLUMN(G1),1),"[dbnum2]"))

角位:=(TEXT(MID(SUBSTITUTE(TEXT($U8,"0000000.00"),".",""),COLUMN(H1),1),"[dbnum2]"))

分位:=(TEXT(MID(SUBSTITUTE(TEXT($U8,"0000000.00"),".",""),COLUMN(I1),1),"[dbnum2]"))

本文地址:http://www.kaixinit.com/info/office/4390.html
版权声明:本文为原创文章,版权归 kaixinit 所有,欢迎分享本文,转载请保留出处!
NEXT:已经是最新一篇了

发表评论


表情