查看: 1872|回复: 0

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

[复制链接]
发表于 2016-9-3 07:12:02 | 显示全部楼层 |阅读模式
人民币(大写)excel数字小写改大写后再自动加"圆整"或"角整"
6 a0 R4 ]% l6 e4 v& w' R$ [. Q& x* v" Z
  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,,"零")),"零分","整")
复制代码

& a. P" g( Z% s: J0 u; u5 z. r: |
( `3 Z3 a2 n1 s! [# u
  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]")&"分")))
复制代码

5 t8 ~6 b! u( N9 `0 c. _/ F, r5 r7 a0 h$ \- S2 h7 T0 E9 y+ @
  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]")&"分")))
复制代码

2 t* ]5 G' x. @) Y  P0 _) y
& B3 M. D/ {; R# w: V- k* X; [# R/ i6 c( D, T' h" P
) ]  l6 b$ I) E2 t, I6 u" Y' J9 M' i
EXCEL中的数字金额转换成中文金额大写
* i- ^! K5 x6 @% s
/ `# a3 o6 J6 x2 b) F/ v如果单元格H14是小写数字,你想在其它单元格中显H14格的大写中文数字,就把下面的公式复制到你的目标单元格中,当然如果你的小写数字不是在H14格,只要把下面公式中的H14全换成你小写数字的单元格就可以了,! J' t: n* O5 G# C$ }

$ n# ~5 m* ]8 }1 K. q9 c7 \$ T
  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/通用格式分"))))
复制代码

6 r$ ]0 s! ]% f2 g+ J" g4 J0 z" c" [: C0 \7 [0 c% I$ k
! n& y& v# A( C# I
EXCEL中数字转换中文大写公式% c5 V3 X4 ]2 R) W0 J# C
  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分")
复制代码
  q/ m3 }6 Q1 ?* }/ u

) H: f! o1 s3 X0 h1 }  N+ GEXCEL中数字小写换大写方法一:
* T% X, {" v: Q- E( S在单元格A2中输入小写数字123.12
9 d) }) O9 w) y% ZB2处输入以下公式
: D! x, ^; t& ^* J$ J
  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,"","整")),"零元零",""),"零元","")
复制代码

. k  T$ j4 Z) d0 T, G) N$ u8 C- i  _) ~
EXCEL中数字小写换大写方法二:3 e. Q1 D3 t% ~; B5 w! \
" x2 X) C9 T2 ?$ f; a( A8 c0 N
在单元格A2中输入小写数字123.12 5 W( k7 x6 R& b
B2处输入以下公式 . S8 @1 |. b6 n6 A. j: r

, I  y, q2 |( l. ]  I& j
  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/通用格式")&"分"))
复制代码
* m) ~- g+ f: V9 {# B
2 j" ^3 g( ~2 d  }8 V
EXCEL中数字小写换大写方法三:- I- n7 V9 {! D  e; Q% [
% W5 A3 O8 M" v) S5 U4 p: h
  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/通用格式")&"分")
复制代码
4 x1 I6 ]: O  U8 x( y
3 f) Q( r/ }9 }% A5 Y5 \) F4 Z3 c
EXCEL中数字小写换大写方法四:4 Y1 Z8 j- b9 [. \# M

6 ~( z+ r. }- P- K
  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]")&"分"))
复制代码

: V- R7 [* n& |, T% E
4 G0 _2 W- B" u0 t& ^. \$ N) V! l+ S1 d
5 _+ R/ K# a3 [" i
欢迎访问范氏宗亲网!请牢记我们的主网址:【www.fanwuzi.com】,备用网址:【www.fanjiaren.org】。
您需要登录后才可以回帖 登录 | 停止注册

本版积分规则

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

GMT+8, 2019-12-10 09:07 , Processed in 0.101962 second(s), 21 queries , Gzip On. Powered by Discuz! X3.4 Licensed

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