查看: 1045|回复: 0

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

[复制链接]
发表于 2016-9-3 07:12:02 | 显示全部楼层 |阅读模式
人民币(大写)excel数字小写改大写后再自动加"圆整"或"角整"+ Z0 j1 h$ @; }: I

1 x3 B1 L" k* q6 W0 n
  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,,"零")),"零分","整")
复制代码
+ B& g% @/ k- f# r6 J! t

( ~4 Y9 }9 ]4 n. Q6 a# }! [" ^4 t
  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]")&"分")))
复制代码

- j/ U$ O% `4 B& N& b- J. j/ _! C& S2 j: r: ^
  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]")&"分")))
复制代码

9 _. A+ x8 c% e6 {0 x$ n. `% r& P, H8 b2 n

+ q# g8 F( M/ n( ?, x+ J  d0 v  ]! b( K% _! N1 [
EXCEL中的数字金额转换成中文金额大写
/ ]* j' j$ h7 t0 S( M- A, O) ^% B: t
如果单元格H14是小写数字,你想在其它单元格中显H14格的大写中文数字,就把下面的公式复制到你的目标单元格中,当然如果你的小写数字不是在H14格,只要把下面公式中的H14全换成你小写数字的单元格就可以了,5 ~$ Y2 P7 I9 G% O2 |9 ~1 L

4 |) A7 P9 ]# S  F- F+ c+ q4 [
  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/通用格式分"))))
复制代码
( m* `( A, \, c+ F
5 t2 x( j+ d% e6 M, ]

  _/ \) X" X& q% e6 wEXCEL中数字转换中文大写公式* F; L! {0 A3 \& ]6 K1 Y  t. ^
  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分")
复制代码
; r! S4 B2 I$ A% N, J8 R0 d

( v: J: _) o; M( QEXCEL中数字小写换大写方法一:
& Y  u; w( a5 `6 j" X在单元格A2中输入小写数字123.12
. e! n& t) ~3 x* ]# H3 XB2处输入以下公式
7 S5 y% R- q) N
  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,"","整")),"零元零",""),"零元","")
复制代码
, I4 U' T$ U2 ~' a0 z

! \0 E" F! c! f0 L" [EXCEL中数字小写换大写方法二:
4 p5 X/ I6 ^2 w0 n3 V3 z/ w1 q! j$ _& {5 i% `6 F- k0 W/ B
在单元格A2中输入小写数字123.12
, C0 M6 c& V# y& B$ GB2处输入以下公式   ]- t; K6 I+ p- t' Y
; a5 u; @  _2 j/ {: o9 v' ?4 E# f
  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/通用格式")&"分"))
复制代码
* R! ?8 A/ k# x$ s

8 n4 l6 \- i& \1 Q# A* ~5 wEXCEL中数字小写换大写方法三:" L# r3 n6 z; f3 Z3 D7 a1 m7 P

, s. t$ V8 g- }: D/ y4 G1 R
  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/通用格式")&"分")
复制代码
  K0 g+ E1 Z: s" ?% P2 l) S) U
  W1 k6 V1 K# H  ]. n0 G% w
EXCEL中数字小写换大写方法四:( t& V9 g) T  T. ^& ]

  S: F" T* M( Q- }2 h- n
  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]")&"分"))
复制代码

$ n4 T; P' w1 ?' V, ~/ @
1 E- Z3 v, a$ v5 P# I% a" t8 C6 g2 `# G. A( W( v) b

) m; k8 N+ e- n  _0 ~
欢迎访问范氏宗亲网!请牢记我们的主网址:【www.fanwuzi.com】,备用网址:【www.fanjiaren.org】。
您需要登录后才可以回帖 登录 | 停止注册

本版积分规则

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

GMT+8, 2018-6-18 06:21 , Processed in 0.107168 second(s), 26 queries , Gzip On. Powered by Discuz! X3.2 Licensed

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