查看: 2118|回复: 0

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

[复制链接]
发表于 2016-9-3 07:12:02 | 显示全部楼层 |阅读模式
人民币(大写)excel数字小写改大写后再自动加"圆整"或"角整"
: F; O% w7 p1 T4 r) t& r  b7 M9 N$ q2 b0 f3 x# L& [; _
  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,,"零")),"零分","整")
复制代码

1 |) x& S# t3 U, Y' R7 c+ a2 s/ J  X8 O& W
  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]")&"分")))
复制代码

# v& Z  O" F6 \& U" H* w
8 A" Z7 b$ i; S3 d% P
  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]")&"分")))
复制代码

& R7 T. w* T, K/ Q$ S' ]0 m6 T# [2 \+ l1 Y# X. Q7 N; t' m) s
  z& L% E' Z4 e$ K0 U# |
; E& k4 [4 |) F
EXCEL中的数字金额转换成中文金额大写
, ?/ m. \5 v+ N( s0 Y& [/ G8 d0 W! Z9 ~8 H4 A3 v
如果单元格H14是小写数字,你想在其它单元格中显H14格的大写中文数字,就把下面的公式复制到你的目标单元格中,当然如果你的小写数字不是在H14格,只要把下面公式中的H14全换成你小写数字的单元格就可以了,# }2 s, u! E- U* G* u% g0 O) O

& t* ?2 j. P3 \7 x; M
  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/通用格式分"))))
复制代码

; k- E4 |, M# R/ x. K
4 c" ?5 F, I6 ?; {8 T# |) c$ V
EXCEL中数字转换中文大写公式5 y/ v; h1 j# L8 S, z$ U3 K
  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分")
复制代码

6 f* G& I5 b4 C( S; F
0 F# Z1 o$ e' M; z+ x- J/ Q% pEXCEL中数字小写换大写方法一:
6 F' A  }7 M" B$ S( q9 b; O在单元格A2中输入小写数字123.12
# ]. {. s' D  }: O& SB2处输入以下公式 . t3 F& C+ U2 W  l! y
  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,"","整")),"零元零",""),"零元","")
复制代码

& ?# S. h  b. s% _  T4 R5 r3 `# i# Y. ^! l2 x& ~
EXCEL中数字小写换大写方法二:( u- \7 d9 h  S& n/ M6 t

9 |, e; p; ]7 S+ j+ o在单元格A2中输入小写数字123.12 ! [! ?4 o/ B! t) l
B2处输入以下公式 & D- n# l; }' j

& D, F3 g  E/ R. `. _) G/ w2 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/通用格式")&"分"))
复制代码
6 i, c, x  |/ v$ m: G# F

6 N4 y' U3 @9 w2 o# Q: R& LEXCEL中数字小写换大写方法三:
, F( r% y8 x! F" ?
) z/ [- c7 p6 _( F8 U
  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/通用格式")&"分")
复制代码

, p( q9 N, N( Y5 m+ f/ A( U0 b* S8 t  Y, m% `! e& Z3 D
EXCEL中数字小写换大写方法四:8 I! Z8 k4 `8 V8 ^  p; s
" ]6 T+ w+ K/ C- k) 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]")&"分"))
复制代码
, e4 T% Z1 t7 [) U0 z* ~' G

; W, W+ B8 q2 I( }- _- y/ {
( C) D/ Q4 Y/ }  F4 Y2 ~( f9 W
) p8 `& l4 i8 F8 m' ~8 C; {
欢迎访问范氏宗亲网!请牢记我们的主网址:【www.fanwuzi.com】,备用网址:【www.fanjiaren.org】。
您需要登录后才可以回帖 登录 | 停止注册

本版积分规则

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

GMT+8, 2020-2-21 17:54 , Processed in 0.081943 second(s), 21 queries , Gzip On. Powered by Discuz! X3.4 Licensed

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