求助：如何更好地实现大小写金额的转换？

zxcvbnmch · 发表于 2006-1-13 20:01

第 13 楼能用

wxl9 · 发表于 2006-1-16 17:15

2.将A1中的123转换为大写的数字,公式如下:
将123转换为"一百二十三",公式为:=NUMBERSTRING(A1,1)
将123转换为"壹佰贰拾叁",公式为:=NUMBERSTRING(A1,2)
将123转换为"一二三",公式为:=NUMBERSTRING(A1,3)

yjjh · 发表于 2007-5-26 16:45

求助:如何把下面这个函数变成EXCEL中的自定义函数=IF(A9<0,"负","")&IF(TRUNC(ROUND(A9,2))=0,"",TEXT(TRUNC(ABS(ROUND(A9,2))),"[DBNum2]")&"元")&IF(ISERR(FIND(".",ROUND(A9,2))),"",IF(AND(LEFT(TRUNC(ROUND(A9,2)))="0",RIGHT(TRUNC(ROUND(A9,2)*10))="0"),,TEXT(RIGHT(TRUNC(ROUND(A9,2)*10)),"[DBNum2]")))&IF(ISERR(FIND(".0",TEXT(A9,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A9,2),3))=".",TEXT(RIGHT(ROUND(A9,2)),"[DBNum2]")&"分"

amwdhjy · 发表于 2007-8-12 23:25

考虑负数、零及四舍五入：=IF(LEFT(A1)="-",REPLACEB("-",1,1,"（负）"),"")&IF(ABS(ROUND(A1,2))<1,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(TRUNC(ROUND(A1,2)*10,0)),"[dbnum2]")&"角"&TEXT(RIGHT(TRUNC(ROUND(A1,2)*100,0)),"[dbnum2]")&"分","零角零分",""),"零角",""),"零分","整"),TEXT(INT(ROUND(A1,2)),"[dbnum2]")&"元"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(TRUNC(ROUND(A1,2)*10,0)),"[dbnum2]")&"角"&TEXT(RIGHT(TRUNC(ROUND(A1,2)*100,0)),"[dbnum2]")&"分","零角零分","整"),"零角","零"),"零分","整"))

瑾果 · 发表于 2004-3-22 15:56

<DIV class=quote>以下是引用nzlwc_fsb在2004-3-19 18:33:00的发言： </DIV> 
<TABLE style="WIDTH: 621pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=820 border=0 x:str>
<COLGROUP>
<COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3360" width=105>
<COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 3872" width=121>
<COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 2912" width=91>
<COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2048" span=2 width=64>
<COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2464" width=77>
<COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2048" width=64>
<COL style="WIDTH: 27pt; mso-width-source: userset; mso-width-alt: 1152" width=36>
<COL style="WIDTH: 14pt; mso-width-source: userset; mso-width-alt: 576" span=11 width=18>
<TBODY>
<TR style="HEIGHT: 14.25pt" height=19>
<TD class=xl29 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 467pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 28.5pt; BACKGROUND-COLOR: yellow" width=622 colSpan=8 height=38 rowSpan=2>贰角叁分</TD>
<TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #c0c0c0; BORDER-LEFT: windowtext; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=18>亿</TD>
<TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #c0c0c0; BORDER-LEFT: windowtext; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=18>千</TD>
<TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #c0c0c0; BORDER-LEFT: windowtext; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=18>百</TD>
<TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #c0c0c0; BORDER-LEFT: windowtext; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=18>十</TD>
<TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #c0c0c0; BORDER-LEFT: windowtext; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=18>万</TD>
<TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=18>千</TD>
<TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=18>百</TD>
<TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=18>十</TD>
<TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=18>元</TD>
<TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #c0c0c0; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=18>角</TD>
<TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=18>分</TD></TR>
<TR style="HEIGHT: 14.25pt" height=19>
<TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: yellow" height=19>　</TD>
<TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">　</TD>
<TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">　</TD>
<TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">　</TD>
<TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">　</TD>
<TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">　</TD>
<TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">　</TD>
<TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow" align=right x:num>2</TD>
<TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow" align=right x:num>3</TD>
<TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">.</TD>
<TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow" align=right x:num>3</TD></TR></TBODY></TABLE>

瑾果 · 发表于 2004-3-19 11:58

<TABLE style="WIDTH: 54pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=72 border=0 x:str>
<COLGROUP>
<COL style="WIDTH: 54pt" width=72>
<TBODY>
<TR style="HEIGHT: 14.25pt" height=19>
<TD style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; WIDTH: 54pt; BORDER-BOTTOM: #c0c0c0; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" width=72 height=19>‘=IF(ISNUMBER($N$49),IF($N$49<0,"无效数值",IF($N$49<0.005,"零",IF($N$49<0.995,"",TEXT(INT($N$49+0.005),"[dbnum2]")&"元")&IF(LEFT(RIGHT(FIXED($N$49,2),2),1)="0",IF(RIGHT(FIXED($N$49,2),1)="0","",IF($N$49>0.995,"零","")),TEXT(LEFT(RIGHT(FIXED($N$49,2),2),1),"[dbnum2]")&"角")&IF(RIGHT(FIXED($N$49,2),1)="0","整",TEXT(RIGHT(FIXED($N$49,2),1),"[dbnum2]")&"分"))),"非数值!")</TD></TR></TBODY></TABLE>停好用的

		自动登录	找回密码
密码			免费注册

[求助] 求助：如何更好地实现大小写金额的转换？

我输入0.233