excel 将不同单元格中的小写金额转换成大写金额
发布网友
共4个回答
热心网友
首先，我觉得你的思路要改一下，像你那样，即不方便输入，也不方便计算，修改为下图样式：
1、将M2：M3作为手工输入区域，在M4输入
=M2+M32、在B2输入
=LEFT(RIGHT(TEXT($M2/1%," ￥0; -￥0;"),11-COLUMN(A1)))右拉填充公式到K2再下拉填充到K4，自动完成数据分割并填列到相应栏。
3、B5输入
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(INT(M4),"[dbnum2]")&TEXT(RIGHT(M4/1%,2),"[dbnum2]元0角0分"),"零角","零"),"零分","整"),"零整","整")将M4合计转换为大写。
选中A1：K5，设置页面打印区域（这样，打印时，就不可能打印出录入区的了）
具体见附件。
如果非要在你现有格式下得出结果，直接在你B5输入：
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(B4&C4&D4&E4&F4&G4&H4&I4,"[dbnum2]")&TEXT(J4&K4,"[dbnum2]元0角0分"),"零角","零"),"零分","整"),"零整","整")
热心网友
如下D5中输入如下公式
=TEXT(TRUNC(VALUE(B4&C4&D4&E4&F4&G4&H4&I4&"."&J4&K4)),"[dbnum2]")&"元"&TEXT(MOD(VALUE(B4&C4&D4&E4&F4&G4&H4&I4&"."&J4&K4),1)*100,"00[dbnum2]")&"分"
追问“角”没有，最后还要有整字，多谢，就数你这个有用追答
如下D5中输入如下公式
=TEXT(VALUE(B4&C4&D4&E4&F4&G4&H4&I4),"[dbnum2]")&"元"&TEXT(J4,"[dbnum2]")&"角"&TEXT(K4,"[dbnum2]")&"分整"
热心网友
=IF(TRUNC(B3&C3&D3&E3&F3&G3&H3&K3*1,0)=B3&C3&D3&E3&F3&G3&H3&K3*1,TEXT(B3&C3&D3&E3&F3&G3&H3&K3*1,"[DBNum2]G/通用格式")&"元整",IF(TRUNC(B3&C3&D3&E3&F3&G3&H3&K3*1,1)=L4,TEXT(TRUNC(B3&C3&D3&E3&F3&G3&H3&K3*1,0),"[DBNum2]G/通用格式")&"元"&TEXT((B3&C3&D3&E3&F3&G3&H3&K3*1-TRUNC(B3&C3&D3&E3&F3&G3&H3&K3*1,0))*10,"[DBNum2]0")&"角整",IF(TRUNC(B3&C3&D3&E3&F3&G3&H3&K3*1,1)=TRUNC(B3&C3&D3&E3&F3&G3&H3&K3*1,0),TEXT(TRUNC(B3&C3&D3&E3&F3&G3&H3&K3*1,0),"[DBNum2]G/通用格式")&"元"&"零"&TEXT((B3&C3&D3&E3&F3&G3&H3&K3*1-TRUNC(B3&C3&D3&E3&F3&G3&H3&K3*1,1))*100,"[DBNum2]0")&"分",TEXT(TRUNC(B3&C3&D3&E3&F3&G3&H3&K3*1,0),"[DBNum2]G/通用格式")&"元"&TEXT((B3&C3&D3&E3&F3&G3&H3&K3*1-TRUNC(B3&C3&D3&E3&F3&G3&H3&K3*1,0))*10,"[DBNum2]0")&"角"&TEXT((B3&C3&D3&E3&F3&G3&H3&K3*1-TRUNC(B3&C3&D3&E3&F3&G3&H3&K3*1,1))*100,"[DBNum2]0")&"分")))&"整"
热心网友
求和再用text函数追问已经是合计数123456.78元了，只不过1、2、3、4、5、6、7、8分别在不同的单元格中，“人民币（大写）”处怎样显示合计的大写金额，如果我把最后的8改成9了，“人民币（大写）”处也马上变成大写的“玖分”
追答=TEXT(B4&C4&D4&E4&F4&G4&H4&I4,"[dbnum2]")&TEXT(J4*10+K4,"[dbnum2]元0角0分")
