查看: 873|回复: 0

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

[复制链接]
发表于 2016-9-3 07:12:02 | 显示全部楼层 |阅读模式
人民币(大写)excel数字小写改大写后再自动加"圆整"或"角整"' i% _6 |5 P+ _/ y( f) J: v

/ }' G: w( {& O- q4 c7 T2 I
  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,,"零")),"零分","整")
复制代码

# `. Y5 ^, z9 F& X% u; o
& @1 S" b$ m. \( I
  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]")&"分")))
复制代码

: R6 Q, C5 r6 \) w9 U/ Z+ P. g  X1 q; e  O% r5 U
  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]")&"分")))
复制代码

1 Y( P7 @$ q3 g/ ?# O6 k: H( m: C, g

: Y" _7 @2 \" k
; N* {- n* T9 y( R9 m' REXCEL中的数字金额转换成中文金额大写6 n) X" z- [$ e! J! r* V
  x* n5 t- @( [) H7 [
如果单元格H14是小写数字,你想在其它单元格中显H14格的大写中文数字,就把下面的公式复制到你的目标单元格中,当然如果你的小写数字不是在H14格,只要把下面公式中的H14全换成你小写数字的单元格就可以了,
9 A! a3 x( g; j( }; R3 d' S  l3 R3 s) Q
  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/通用格式分"))))
复制代码
$ O' h- \2 J: {# K2 P3 @

' \: X1 d, y7 c+ x# B) ^) o! }1 l: ?! h1 s- Q
EXCEL中数字转换中文大写公式9 r8 m1 Z: e2 M  S  j5 S
  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分")
复制代码

2 L1 D' v' i* o3 q
, ?: f. B1 f+ {0 L: v* `EXCEL中数字小写换大写方法一:6 z; @' v! ^; m7 T) z, T
在单元格A2中输入小写数字123.12 - K& \5 P% K& l5 `8 g6 `1 q
B2处输入以下公式
$ o% l# @/ `/ u6 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,"","整")),"零元零",""),"零元","")
复制代码

! y" l; K& O: t! z" c+ r- J9 Q9 v. U1 r! e/ z
EXCEL中数字小写换大写方法二:
9 U1 F1 Z8 m' k3 B6 ?$ x8 V# b. a& K4 {2 U4 w
在单元格A2中输入小写数字123.12
) {0 _0 W' b- R3 H8 M3 y' vB2处输入以下公式
% ?" j: y% L0 M. D# t% i
5 s/ G' K$ ?2 k( R, Q0 f) r" M
  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/通用格式")&"分"))
复制代码
' t: Z" D! C  N/ l: `- R3 S4 H; y& v

+ q) z7 a; ~3 b! x! }/ l$ @0 t2 ^EXCEL中数字小写换大写方法三:5 }5 s# R8 ^) I. T6 M0 p) C% u9 ^. _

3 A- E- ~. n$ Y/ `1 U- 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/通用格式")&"分")
复制代码
) |+ P5 y- A+ l( c8 r" Y. j
! g; x" O) {+ {3 s3 F# w
EXCEL中数字小写换大写方法四:+ A' q1 h- v1 [" Z* O: ^
$ f' f- Q9 m0 s4 x( r% u0 A
  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]")&"分"))
复制代码

& Q9 k. B" H6 u5 J& x * U( P! S! ^4 n; j. E# l
4 L7 z/ d" Y7 l) B  ^4 k4 }
8 a# c  V( @% L+ y  |
欢迎访问范氏宗亲网!请牢记我们的主网址:【www.fanwuzi.com】,备用网址:【www.fanjiaren.org】。
您需要登录后才可以回帖 登录 | 停止注册

本版积分规则

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

GMT+8, 2018-2-22 18:47 , Processed in 0.104150 second(s), 28 queries , Gzip On. Powered by Discuz! X3.2 Licensed

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