查看: 4125|回复: 0

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

[复制链接]
发表于 2016-9-3 07:12:02 | 显示全部楼层 |阅读模式
人民币(大写)excel数字小写改大写后再自动加"圆整"或"角整"/ q2 Y0 b$ r2 W+ z( o; q
: q' f, U" C# o7 A
  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,,"零")),"零分","整")
复制代码

' b8 y, v4 t: B6 u5 W* M+ P5 j3 t+ \
  ?6 E' d- A; M1 a$ {
  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]")&"分")))
复制代码

: L0 M$ i7 Y9 G+ C  A' Q
+ m6 ?$ V" u0 O
  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]")&"分")))
复制代码
& p- ]% H% _) j3 q/ D
& ^$ y# ^' k. i( J$ c, u* p

  O+ {% `' l; `2 p: R* B) S0 d- u6 |1 o: f0 ~' ?# _- ?( x8 S; U
EXCEL中的数字金额转换成中文金额大写; I# @, J# @2 H  M4 m& i0 ^

- j7 y$ A  L8 ^$ M0 j; n如果单元格H14是小写数字,你想在其它单元格中显H14格的大写中文数字,就把下面的公式复制到你的目标单元格中,当然如果你的小写数字不是在H14格,只要把下面公式中的H14全换成你小写数字的单元格就可以了,
/ n: m8 Y) v/ d+ S. J$ h/ ^/ L0 J3 T# s7 Z5 v
  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/通用格式分"))))
复制代码
0 b; k- R4 K5 T4 c$ a5 k

; M5 a8 a) ?# g# y- T
* U9 y* t" ~( l* d$ c, {EXCEL中数字转换中文大写公式
, |% b# ?- p# X0 p3 v
  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分")
复制代码

8 R" z% ~$ E: f1 F% Z9 v' o; b
+ |* I/ t; P6 p/ R" R* @/ B$ @EXCEL中数字小写换大写方法一:! {# ?" a+ z& G$ w0 q- ^2 @: z
在单元格A2中输入小写数字123.12 2 u1 K8 X4 f9 m$ y1 x/ j+ m# z
B2处输入以下公式 8 B  ~" y7 y) {6 F% ~2 C3 Q; Q$ Y6 k
  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,"","整")),"零元零",""),"零元","")
复制代码

- ?) p( Z4 p2 i! K) F! p. [; p
7 y/ v+ K' s; U+ v+ l; S: kEXCEL中数字小写换大写方法二:$ g6 [7 o( b; F
/ g0 }# D" o/ D9 M0 O$ e8 ~, w, y
在单元格A2中输入小写数字123.12
$ l; \7 U2 y$ U3 E* Q/ B, r0 u* ZB2处输入以下公式 5 V9 y0 l+ X$ k2 a3 u1 J# L/ k
; n; H: S& G4 P: w8 W5 ?
  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/通用格式")&"分"))
复制代码

- }( W* ]& x3 F  h$ ^# X1 Z4 l# I' J- f6 Q2 D2 @/ A. z9 ~3 O
EXCEL中数字小写换大写方法三:, r( q- q5 m+ ~: x1 h3 y/ H; F
  ?. n3 l2 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/通用格式")&"分")
复制代码

/ O: D, K5 n0 q+ L: z
* N- x# f; q- F8 L( wEXCEL中数字小写换大写方法四:$ t. r' o' a/ `( I; q. P

5 D( w8 i5 H7 O
  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]")&"分"))
复制代码

+ w; b. I# ]" R" @" k# M
* y! ^$ r2 _: L0 z) k% a5 _+ d7 p* Y. W4 P) J
% i* Q3 C/ R3 [2 T
欢迎访问范氏宗亲网!请牢记我们的主网址:【www.fanwuzi.com】,备用网址:【www.fanjiaren.org】。
您需要登录后才可以回帖 登录 | 停止注册

本版积分规则

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

GMT+8, 2021-1-22 15:26 , Processed in 0.138925 second(s), 21 queries . Powered by Discuz! X3.4 Licensed

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