WORD调用EXCEL打印证书或支票,金额或日期自动变大写.doc

文档介绍：WORD调用EXCEL打印证书或支票,金额或日期自动变大写
 
我们经常会批量制作证书、支票或邮件,它们的共同特点是格式一样、内容有异,支票的金额或日期必须用大写,反复地书写或录入电脑都很繁琐。
其实用点小技巧,EXCEL可将金额或日期准确地变成大写,再用WORD的“邮件合并”功能调用EXCEL数据源,能自动批量印***、支票、邮件或请柬等。
方法如下:
一、用EXCEL准备数据源():
  (声明:表中的样板数据随意设定,若与某人或某单位同名,纯属巧合)
 
技巧:EXCEL表格中红字内容不是手工输入的,它们是用公式自动将其左边的日期或奖金自动转换成的大写。
“H2”的公式如下,其下方单元格自动填充即可。
=TEXT(LEFT(RIGHT(YEAR(F2),4)),"[DBNum1]")&TEXT(LEFT(RIGHT(YEAR(F2),3)),"[DBNum1]")&TEXT(LEFT(RIGHT(YEAR(F2),2)),"[DBNum1]")&TEXT(LEFT(RIGHT(YEAR(F2),1)),"[DBNum1]")&"年" & IF(MONTH(F2)<10, TEXT(RIGHT(MONTH(F2),2),"[DBNum1]"),IF(MONTH(F2)=10, "十","十" &TEXT(LEFT(RIGHT(MONTH(F2),1)),"[DBNum1]")))&"月"&
IF(DAY(F2)<10,TEXT(RIGHT(DAY(F2),2),"[DBNum1]"),IF(DAY(F2)=10,"十", IF(DAY(F2)<20,"十" &TEXT(LEFT(RIGHT(DAY(F2),1)),"[DBNum1]"), TEXT(RIGHT(DAY(F2),2),"[DBNum1]"))))&"日"
(注:“日期转大写公式”花了YH半天时间编写调试,诸位可复制使用,谢绝转载)
“I2”的公式如下,其下方单元格自动填充即可。
=IF(ROUND(G2,2)<0,"无效数值",IF(ROUND(G2,2)=0,"零",IF(ROUND(G2,2)<1,
"",TEXT(INT(ROUND(G2,2)),"[dbnum
2]")&"元")&IF(INT(ROUND(G2,2)*10)-INT(ROUND(G2,2))*10=0,IF(INT(ROUND(G2,2))*(INT(ROUND(G2,2)*100)-INT(ROUND(G2,2)*10)*10)=0,"","零"),TEXT(INT(ROUND(G2,2)*10)-INT(ROUND(G2,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(G2,2)*100)-INT(ROUND(G2,2)*10)*10)=0,"整",TEXT((INT(ROUND(G2,2)*100)-INT(ROUND(G2,2)*10)*10),"[dbnum2]")&"分")))
,按数位打印票据,“空位”自动填“零”。
=IF(ROUND(G2,2)<0,"无效数值",IF(ROUND(G2,2)=0,"零",IF(ROUND(G2,2)<1,
"",TEXT(