查看: 1857|回复: 0

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

[复制链接]
发表于 2016-9-3 07:12:02 | 显示全部楼层 |阅读模式
人民币(大写)excel数字小写改大写后再自动加"圆整"或"角整"1 Q& Q" k; T& i4 z" v
8 u9 |3 F  }0 V; `
  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,,"零")),"零分","整")
复制代码
$ p. h) K/ u+ P  R  `3 W% E

# d6 `/ ?+ J. R' ^$ {3 ]' S  v
  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]")&"分")))
复制代码
1 g, [4 P, y& N

3 c. u$ P5 M- |# R% D6 U; }7 h
  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]")&"分")))
复制代码

7 B- k5 p. o+ q, t# s; H) B+ t, r
/ \% E& E' z; \% U

  }' \5 v6 M! F# t2 {EXCEL中的数字金额转换成中文金额大写: @9 s" X9 p; H- x4 L, y
# f' l( A8 e& h3 r& q8 v
如果单元格H14是小写数字,你想在其它单元格中显H14格的大写中文数字,就把下面的公式复制到你的目标单元格中,当然如果你的小写数字不是在H14格,只要把下面公式中的H14全换成你小写数字的单元格就可以了,  d6 Z8 O9 v% {" n3 U

( t% p8 p/ N, e* z
  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/通用格式分"))))
复制代码
! R  f* D( ?2 L2 h3 c9 S/ A
' ~3 F. b. r0 t' U
6 ]- ]0 Z4 D) T8 v
EXCEL中数字转换中文大写公式7 c1 O9 _. H6 U) X6 r  ]! 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分")
复制代码
% ]( F3 p0 E) L& T
: C, H0 \+ p, ]3 G$ B; E2 w3 Y. p
EXCEL中数字小写换大写方法一:6 K  ]) P6 \/ s/ F% a( @% Z( }" a
在单元格A2中输入小写数字123.12 % q) d( |* }: _7 B  ~* }" ]
B2处输入以下公式
, X$ h! [) H2 P/ a0 c. y: x
  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,"","整")),"零元零",""),"零元","")
复制代码

; v" R5 E' d) h' i
  V3 ^% U2 ~6 z- e) h& e( GEXCEL中数字小写换大写方法二:( r& N5 f5 @# p6 a

/ u& h( Y  t  K: H$ O在单元格A2中输入小写数字123.12
. G$ W& [3 C- @8 Q( oB2处输入以下公式 & `7 ~, I8 q* u
% F' D' F5 h6 e# U1 y
  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/通用格式")&"分"))
复制代码
! }6 @( D( S) W0 Q, {

  f8 U( z* {. ?/ o* V/ n1 j/ tEXCEL中数字小写换大写方法三:
9 v5 ~3 D" L0 o, @4 \2 W! ^- c: l& o# F; g% Z
  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/通用格式")&"分")
复制代码

5 p- ]' z; M3 r6 }* u5 G
' X. R. s8 x0 I! Y5 @( U- CEXCEL中数字小写换大写方法四:- @/ \  N) i1 n
0 D/ I+ l8 Z4 `6 Y5 ^
  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]")&"分"))
复制代码

7 P9 X" _' X6 _/ t! K9 B 8 |0 o; s" r0 x  I1 R& H( D

& T# s; a+ Y) b: h+ d9 x- `* M
) V5 ^! l6 P1 G- u( M
欢迎访问范氏宗亲网!请牢记我们的主网址:【www.fanwuzi.com】,备用网址:【www.fanjiaren.org】。
您需要登录后才可以回帖 登录 | 停止注册

本版积分规则

小黑屋|手机版|Archiver|范氏宗亲网 ( 黑ICP备16002281号 )

GMT+8, 2019-12-6 11:42 , Processed in 0.105860 second(s), 21 queries , Gzip On. Powered by Discuz! X3.4 Licensed

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