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

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

[复制链接]
跳转到指定楼层
1
发表于 2016-9-3 07:12:02 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
人民币(大写)excel数字小写改大写后再自动加"圆整"或"角整"4 c& W# r" [! U
+ ~' b( o+ y7 x; ]* P7 |% Q
  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,,"零")),"零分","整")
复制代码
, _" S$ K# p2 G" B3 c; d

) R8 T- O+ @' l: }) M
  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 F( {% \0 V6 U/ |* f. i5 K6 m

+ X??K9 O' b/ A??^; m9 l; @% Q: t
  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]")&"分")))
复制代码

* Q, Y$ b* r4 F) S* K% ]) G1 ]" l# N4 x/ y9 c
' E' D9 K2 Y' }$ n2 K: `% D- [
' v0 W1 G$ k, `4 P8 x6 d
EXCEL中的数字金额转换成中文金额大写
! S# f4 U; J/ I
$ X. q??{; w+ V' i, a* O如果单元格H14是小写数字,你想在其它单元格中显H14格的大写中文数字,就把下面的公式复制到你的目标单元格中,当然如果你的小写数字不是在H14格,只要把下面公式中的H14全换成你小写数字的单元格就可以了,! [& c; C7 n4 _' f7 P

6 j' M! ~6 y??Z* x( E, t4 i
  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/通用格式分"))))
复制代码
# S/ P4 B4 h: Q4 d9 l' p- W
+ m5 W1 C: k2 V' e. X$ V* T: b

! D??{( }9 P+ }' \# o/ h( y: t1 }EXCEL中数字转换中文大写公式' R??Z5 O+ I4 g! x5 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分")
复制代码
+ ^, Z; ?% s% s( P, W% }4 x" b5 J6 E
0 q??T, ?* Q0 ^: B! p+ H
EXCEL中数字小写换大写方法一:
1 r& h0 o& I, z; |2 G5 p" x在单元格A2中输入小写数字123.12 " R% u: z. Z9 I& _# R& B
B2处输入以下公式
# S2 A0 @, ]7 k+ n1 o0 D??|# F
  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,"","整")),"零元零",""),"零元","")
复制代码
3 Z# z1 o1 l8 v

* u! y4 E- r/ l! t3 @" j3 F& QEXCEL中数字小写换大写方法二:% K6 p) Q, O! D+ A

6 n, v. }- @+ ?& _2 Y8 J在单元格A2中输入小写数字123.12
% o$ N( q0 M, @+ b. |B2处输入以下公式
7 L6 E* g8 I1 ]/ J9 j" k7 V, z1 D' b$ o- |: D
  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/通用格式")&"分"))
复制代码

; v( v3 @) l3 d6 q
6 T8 ~$ r9 i) S- H1 K6 s9 ^EXCEL中数字小写换大写方法三:
; v??z! q4 z) B8 T( M3 D0 f??B/ f' 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/通用格式")&"分")
复制代码

( H/ F5 Y) k' p$ M6 S9 }+ e??A8 _" q! V9 ~/ n
EXCEL中数字小写换大写方法四:" A4 X/ {% K5 m6 V8 r- e
; C* c* ?8 z) A! P2 A& _' R' [
  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]")&"分"))
复制代码
4 k9 k# Q" ?7 _+ h7 L3 ~* Y& ]
; W, }! _/ r0 D1 J9 \" o

! K* s6 M??g4 U' i7 J' H+ C 7 |, z! c??Y+ T" b7 E& p$ f$ L- k& B
欢迎访问365体育投注app下载_体育彩票365能提现吗_365体育打不开了宗亲网!请牢记我们的主网址:【www.fanwuzi.com】,备用网址:【www.fanjiaren.org】。
您需要登录后才可以回帖 登录 | 停止注册

本版积分规则

小黑屋|手机版|Archiver|365体育投注app下载_体育彩票365能提现吗_365体育打不开了宗亲网(范家人) ( 黑ICP备16002281号 )
邮箱:service_fan#126.com QQ群:① 42116087 ② 2451985 ③ 8913601 | 始创于西元2008年12月8日

GMT+8, 2019-10-17 16:24 , Processed in 0.085318 second(s), 21 queries , Gzip On. Powered by Discuz! X3.4 Licensed

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