查看: 1591|回复: 0
打印 上一主题 下一主题

[各类教程] excel中数字变中文的几种方法

[复制链接]
跳转到指定楼层
1
发表于 2016-9-3 07:12:02 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
人民币(大写)excel数字小写改大写后再自动加"圆整"或"角整"??T5 V$ t' O% T$ P( P. S8 g/ W

; T& Z/ o. N8 d$ t% C% D
  1. =SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(A1)),"[>0][dbnum2];[<0]负[dbnum2];;")&TEXT(RIGHT(FIXED(A1),2),"元[dbnum2]0角0分;;"&IF(ABS(A1)>1%,"元整",)),"零角",IF(ABS(A1)<1,,"零")),"零分","整")
复制代码

0 s5 e9 B5 |, \1 D
) |! N8 T+ m- s5 _! M9 g
  1. =IF(ROUND(A2,2)<0,"无效数值",IF(ROUND(A2,2)=0,"零",IF(ROUND(A2,2)<1,"",TEXT(INT(ROUND(A2,2)),"[dbnum2]")&"元")&IF(INT(ROUND(A2,2)*10)-INT(ROUND(A2,2))*10=0,IF(INT(ROUND(A2,2))*(INT(ROUND(A2,2)*100)-INT(ROUND(A2,2)*10)*10)=0,"","零"),TEXT(INT(ROUND(A2,2)*10)-INT(ROUND(A2,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(A2,2)*100)-INT(ROUND(A2,2)*10)*10)=0,"整",TEXT((INT(ROUND(A2,2)*100)-INT(ROUND(A2,2)*10)*10),"[dbnum2]")&"分")))
复制代码
0 g7 }, g8 P) x. L$ U
* P+ V8 O- t1 |6 i
  1. =IF(ROUND(A1,2)<0,"无效数值",IF(ROUND(A1,2)=0,"零",IF(ROUND(A1,2)<1,"",TEXT(INT(ROUND(A1,2)),"[dbnum2]")&"元")&IF(INT(ROUND(A1,2)*10)-INT(ROUND(A1,2))*10=0,IF(INT(ROUND(A1,2))*(INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10)=0,"","零"),TEXT(INT(ROUND(A1,2)*10)-INT(ROUND(A1,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10)=0,"整",TEXT((INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10),"[dbnum2]")&"分")))
复制代码
" P- g0 N- M. ]5 \" B& d9 M" G/ m
' T5 j+ h8 v6 H' M) u

& d??R4 W1 m# x; {5 z6 s8 R& `3 @9 b
EXCEL中的数字金额转换成中文金额大写, Y0 L4 v2 G( |( A
, F??d+ l8 G; o' i
如果单元格H14是小写数字,你想在其它单元格中显H14格的大写中文数字,就把下面的公式复制到你的目标单元格中,当然如果你的小写数字不是在H14格,只要把下面公式中的H14全换成你小写数字的单元格就可以了,
+ y% D8 v# d3 ~0 d6 ^
/ r??k3 O2 k% A8 A+ ^- B7 v1 q$ ~: z3 ]
  1. =IF(H14=0,"",CONCATENATE(IF(INT(ABS(H14))=0,"",TEXT(INT(ABS(H14)),"[DBNum2]G/通用格式元")),IF(INT(MID(RIGHT(FIXED(ABS(H14),2,1),2),1,1))=0,IF(INT(MID(RIGHT(FIXED(H14,2,1),1),1,1))=0,"",IF(INT(ABS(H14))=0,"","零")),TEXT(INT(MID(RIGHT(FIXED(H14,2,1),2),1,1)),"[DBNum2]G/通用格式角")),IF(INT(MID(RIGHT(FIXED(H14,2,1),1),1,1))=0,"整",TEXT(INT(MID(RIGHT(FIXED(H14,2,1),1),1,1)),"[DBNum2]G/通用格式分"))))
复制代码

) n- O+ |! K+ g??{, ^9 h??x& L9 e" M; P( j# [

1 r1 Y4 @, K) `7 t, \8 F; kEXCEL中数字转换中文大写公式
# H* y' K% z' x4 `7 i5 A: Z2 ^2 w
  1. =IF(G6<0,"负","")&TEXT(ROUNDDOWN(ABS(ROUND(G6,2)),0),"[dbnum2]g/通用格式元")&TEXT(RIGHT(ROUNDDOWN(ROUND(G6,2)*10,0),1),"[dbnum2]0角")&TEXT(RIGHT(ROUNDDOWN(ROUND(G6,2)*100,0),1),"[dbnum2]0分")
复制代码

1 X+ v0 e5 P! e& ]
8 f; P6 o" U+ h4 o! r+ w' XEXCEL中数字小写换大写方法一:
. O- Z* U& z??V& p! D. `* Y0 s在单元格A2中输入小写数字123.12
# \5 B' X7 N* Z7 n" o1 iB2处输入以下公式
3 l5 a9 U( B# c+ o' g
  1. =SUBSTITUTE(SUBSTITUTE(IF(A2<0,"负","")&TEXT(TRUNC(ABS(ROUND(A2,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(A2,2))),"",TEXT(RIGHT(TRUNC(ROUND(A2,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(A2,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A2,2),3))=".",TEXT(RIGHT(ROUND(A2,2)),"[DBNum2]")&"分",IF(ROUND(A2,2)=0,"","整")),"零元零",""),"零元","")
复制代码
??L( F$ }& {6 s* s& @
' R( \- e/ H??n) g0 k
EXCEL中数字小写换大写方法二:
) i- J: x, f( [% }3 C4 m7 S+ K
7 a5 S/ a$ A* v5 d9 g在单元格A2中输入小写数字123.12
??}& ~2 d2 ~7 ]6 a2 dB2处输入以下公式
9 y. O: {; I) R' ^7 I, q. r+ ~/ A; e) m7 B, h9 J* T4 K% E5 k8 @
  1. =IF((INT(A2*10)-INT(A2)*10)=0,TEXT(INT(A2),"[DBNum2]G/通用格式")&"元"&IF((INT(A2*100)-INT((A2)*10)*10)=0,"整","零"&TEXT(INT(A2*100)-INT(A2*10)*10,"[DBNum2]G/通用格式")&"分"),TEXT(INT(A2),"[DBNum2]G/通用格式")&"元"&IF((INT(A2*100)-INT((A2)*10)*10)=0,TEXT((INT(A2*10)-INT(A2)*10),"[DBNum2]G/通用格式")&"角整",TEXT((INT(A2*10)-INT(A2)*10),"[DBNum2]G/通用格式")&"角"&TEXT(INT(A2*100)-INT(A2*10)*10,"[DBNum2]G/通用格式")&"分"))
复制代码

8 h+ Y??~! w, L' @+ G: y
1 j1 f3 Z3 ]- j- k! WEXCEL中数字小写换大写方法三:
+ {6 L8 ?& q3 H7 ~
. I/ U) w/ v; v+ f6 c: l) P% M
  1. =IF(A2-INT(A2)=0,TEXT(INT(A2),"[DBNum2]G/通用格式")&"圆整",TEXT(INT(A2),"[DBNum2]G/通用格式")&"圆"&TEXT(INT((A2-INT(A2))*10),"[DBNum2]G/通用格式")&"角"&TEXT(INT((A2*10-INT(A2*10))*10),"[DBNum2]G/通用格式")&"分")
复制代码
6 B0 Z7 m" y* V2 q: {' i
3 T* a5 s+ J" K7 k( f* g; a
EXCEL中数字小写换大写方法四:
5 @5 l' a7 v; F. ~
. L2 _( C' L5 r5 E
  1. =IF((A2-INT(A2))=0,TEXT(A2,"[DBNUM2]")&"元整",IF(INT(A2*10)-A2*10=0,TEXT(INT(A2),"[DBNUM2]")&"元"&TEXT((INT(A2*10)-INT(A2)*10),"[DBNUM2]")&"角整",TEXT(INT(A2),"[DBNUM2]")&"元"&IF(INT(A2*10)-INT(A2)*10=0,"零",TEXT(INT(A2*10)-INT(A2)*10,"[DBNUM2]")&"角")&TEXT(RIGHT(A2,1),"[DBNUM2]")&"分"))
复制代码
% X# y4 ^( I" C+ y

. T8 \& [$ q* H& h, ~0 ?5 Y
0 L" U" t- V??j, t( ^ . S??f( C4 Z0 P' H& e1 G
欢迎访问亚博手机app宗亲网!请牢记我们的主网址:【www.fanwuzi.com】,备用网址:【www.fanjiaren.org】。
您需要登录后才可以回帖 登录 | 停止注册

本版积分规则

小黑屋|手机版|Archiver|亚博手机app宗亲网(范家人) ( 黑ICP备16002281号 )
邮箱:service_fan#126.com QQ群:① 42116087 ② 2451985 ③ 8913601 | 始创于西元2008年12月8日

GMT+8, 2019-9-22 09:04 , Processed in 0.083354 second(s), 20 queries , Gzip On. Powered by Discuz! X3.4 Licensed

快速回复 返回顶部 返回列表