EXCEL如何输入小写数字在另一个单元格中自动转换成人民币大写 在EXCEL表中输入小写金额后在另一行如何自动生成大写金额?
\u5728EXCEL\u4e2d\u600e\u6837\u4f7f\u8f93\u5165\u7684\u6570\u5b57\u751f\u6210\u4eba\u6c11\u5e01\u5927\u5199\uff1fExcel\u4e2d\u4f7f\u8f93\u5165\u7684\u6570\u5b57\u751f\u6210\u5927\u5199\uff0c\u53ef\u5728\u8bbe\u7f6e\u5355\u5143\u683c\u683c\u5f0f\u4e2d\u8bbe\u7f6e\u9009\u62e9\u201c\u4e2d\u6587\u5927\u5199\u6570\u5b57\u201d\u3002
\u5177\u4f53\u7684\u64cd\u4f5c\u6b65\u9aa4\u5982\u4e0b\uff1a
1\u3001\u5728\u7535\u8111\u4e2d\u6253\u5f00\u76f8\u5173\u7684EXCEL\u8868\u683c\uff0c\u4f7f\u7528\u9f20\u6807\u9009\u4e2d\u9700\u8981\u5c06\u6570\u5b57\u8f6c\u6362\u4e3a\u4eba\u6c11\u5e01\u5927\u5199\u7684\u5355\u5143\u683c\uff0c\u5e76\u5728\u9009\u4e2d\u533a\u57df\u5185\u70b9\u51fb\u9f20\u6807\u53f3\u952e\uff0c\u7136\u540e\u70b9\u51fb\u9009\u62e9\u201c\u8bbe\u7f6e\u5355\u5143\u683c\u683c\u5f0f\u201d\u3002
2\u3001\u5728\u6570\u5b57\u6807\u7b7e\u9875\u4e2d\u627e\u5230\u5e76\u70b9\u51fb\u201c\u7279\u6b8a\u201d\u9009\u9879\uff0c\u7136\u540e\u5728\u53f3\u4fa7\u9009\u62e9\u201c\u4e2d\u6587\u5927\u5199\u6570\u5b57\u201d\u5373\u53ef\uff0c\u70b9\u51fb\u53f3\u4e0b\u89d2\u7684\u786e\u5b9a\u6309\u94ae\u56de\u5230EXCEL\u8868\u683c\u3002
3\u3001\u56de\u5230EXCEL\u8868\u683c\u4e2d\uff0c\u53ef\u4ee5\u770b\u5230\u521a\u624d\u8bbe\u7f6e\u5355\u5143\u683c\u683c\u5f0f\u7684\u533a\u57df\u4e2d\uff0c\u6240\u6709\u7684\u6570\u5b57\u90fd\u5df2\u8f6c\u6362\u751f\u6210\u4eba\u6c11\u5e01\u5927\u5199\u7684\u6570\u5b57\uff0c\u5b8c\u6210\u8981\u6c42\u64cd\u4f5c\u3002
\u5bf9\u4e8e\u91d1\u989d2205576\u5143\uff0c\u6807\u51c6\u7684\u5927\u5199\u4e3a\u201c\u8d30\u4f70\u8d30\u62fe\u4e07\u4f0d\u4edf\u4f0d\u4f70\u67d2\u62fe\u9646\u5143\u6574\u201d\uff0c\u800c\u201c\u8d30\u4f70\u8d30\u62fe\u4e07\u96f6\u4f0d\u4edf\u4f0d\u4f70\u67d2\u62fe\u9646\u5143\u6574\u201d\u662f\u4e0d\u6b63\u786e\u7684\uff0c\u5982\u4f55\u5c06\u5c0f\u5199\u91d1\u989d\u8f6c\u6362\u6210\u6807\u51c6\u7684\u5927\u5199\u91d1\u989d\u53ef\u4ee5\u53c2\u8003\u4e0b\u65b9\u6b65\u9aa4\u3002
1\u3001\u6f14\u793a\u4f7f\u7528\u7684\u8f6f\u4ef6\u4e3aExcel\u7535\u5b50\u8868\u683c\uff0c\u4f7f\u7528\u7684\u7248\u672c\u4e3aoffice professional plus2013\u3002
2\u3001\u9996\u5148\u6253\u5f00\u7535\u5b50\u8868\u683c\u5e76\u5728\u8868\u683c\u4e2d\u8f93\u5165\u7528\u4e8e\u6f14\u793a\u5982\u4f55\u5728\u8f93\u5165\u5c0f\u5199\u91d1\u989d\u540e\uff0c\u81ea\u52a8\u751f\u6210\u5927\u5199\u91d1\u989d\u7684\u64cd\u4f5c\u6240\u9700\u8981\u7684\u6570\u636e\u3002
3\u3001\u9009\u4e2d\u60f3\u8981\u751f\u6210\u5927\u5199\u91d1\u989d\u7684\u5217\uff0c\u5e76\u5355\u51fb\u53f3\u952e\u9009\u62e9\u8bbe\u7f6e\u5355\u5143\u683c\u683c\u5f0f\u83dc\u5355\uff0c\u5728\u5f39\u51fa\u7684\u7a97\u53e3\u4e2d\u5c06\u683c\u5f0f\u8bbe\u7f6e\u4e3a\u7279\u6b8a\u4e2d\u6587\u5927\u5199\u6570\u5b57\u5e76\u70b9\u51fb\u786e\u5b9a\u3002
4\u3001\u7136\u540e\u5728b2\u5355\u5143\u683c\u4e2d\u8f93\u5165\u201c=A1\u201d\uff0c\u8be5\u516c\u53f8\u8868\u793a\u5c06B1\u5355\u5143\u683c\u4e2d\u7684\u503c\u8bbe\u7f6e\u4e3aA1\uff0c\u56e0\u4e3ab\u5217\u7684\u5355\u5143\u683c\u5df2\u7ecf\u88ab\u5168\u90e8\u8bbe\u7f6e\u4e3a\u5927\u5199\u91d1\u989d\u8868\u793a\uff0c\u6240\u4ee5\u5f53A\u5217\u7684\u6570\u5b57\u4ea7\u751f\u53d8\u52a8\u65f6\uff0cB\u5217\u5c06\u76f8\u5e94\u7684\u751f\u6210\u5927\u5199\u91d1\u989d\u3002
5\u3001\u8f93\u5165\u540e\u70b9\u51fb\u56de\u8f66\u786e\u5b9a\u53ef\u4ee5\u770b\u5230\u6b64\u65f6\u5df2\u7ecf\u5c06\u5c0f\u5199\u91d1\u989d\u81ea\u52a8\u5728\u53e6\u4e00\u884c\u4e2d\u751f\u6210\u4e86\u5927\u5199\u91d1\u989d\u8868\u793a\u3002
\u6b64\u65f6\u5c06b\u5217\u5355\u5143\u683c\u4f7f\u7528\u683c\u5f0f\u586b\u5145\u8fdb\u884c\u5411\u4e0b\u586b\u5145\uff0c\u540e\u7eeda\u5217\u5355\u5143\u683c\u6570\u503c\u53d8\u52a8\u65f6B\u5217\u5c06\u76f8\u5e94\u7684\u751f\u6210\u5927\u5199\u91d1\u989d\u8868\u793a\u3002
1、首先在电脑桌面上打开excel。
2、进入表格后,编辑表格。
3、在B2中输入=IF(A2=0,"",IF(A2<0,"负","")&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(INT(ABS(A2)),"[DBNum2]")&"元"&TEXT(RIGHT(TEXT(A2,".00"),2),"[DBNum2]0角0分"),"零角零分","整"),"零分","整"),"零角","零"),"零元零",""))
4、点击Enter键后发现B2是空白的,但是单击B2会发现,刚才所输入的在上边能显示。
5、然后在A2中输入各种数字,比如12345,输入后大写会自动在B2中生成。
如图:
A4公式:
=TEXT(SUBSTITUTE(A2&B2&C2&D2&E2&F2&G2,"¥",""),"[DBNum2][$-804]G/通用格式元")&TEXT(H2&I2,"[DBNum2][$-804]0角0分")
Q2公式
=NUMBERSTRING(I2*10000+J2*1000+K2*100+L2*10+M2,2) & NUMBERSTRING(N2,2) & "角" & NUMBERSTRING(O2,2) & "分"
NUMBERSTRING(a1,2)
EXCEL如何输入小写数字在另一个单元格中自动转换成人民币大写
如图,小写的数字不在同一个单元格中
绛旓細1銆侀鍏堝湪鐢佃剳妗岄潰涓婃墦寮excel銆2銆佽繘鍏ヨ〃鏍煎悗锛岀紪杈戣〃鏍笺3銆佸湪B2涓杈撳叆=IF(A2=0,"",IF(A2<0,"璐","")&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(INT(ABS(A2)),"[DBNum2]")&"鍏"&TEXT(RIGHT(TEXT(A2,".00"),2),"[DBNum2]0瑙0鍒"),"闆惰闆跺垎","鏁"),"闆跺垎","鏁")...
绛旓細杩欐槸涓涓敊璇殑鏂规锛屽洜涓洪噾棰濇暟鎹潵鑷绠楋紝灏忔牸鍐呯殑鏁版嵁鏉ヨ嚜璁$畻鐨勭粨鏋滐紝鎵浠ュ簲璇ョ敤璁$畻鐨勭粨鏋滅敓鎴愶紝涓枃澶у啓涓鏍枫傛墍浠ユ纭殑鏂规濡備笅锛
绛旓細1銆佹墦寮excel琛ㄦ牸鍦ˋ1鍗曞厓鏍煎唴杈撳叆闇瑕佽浆鎹㈡垚澶у啓閲戦鐨鏁板瓧鈥2205576鈥濄2銆佺劧鍚庡湪B1鍗曞厓鏍煎唴鐐瑰嚮鈥渇x鈥濇彃鍏ュ嚱鏁帮紝浣跨敤IF鍑芥暟鍜孲UBSTITUTE鍑芥暟鐨勭粍鍚堛3銆佸湪鍏朵腑杈撳叆鍑芥暟鍙傛暟锛屼笅鍥句负杞崲鐨勫叕寮忥細=IF(A1>=1,NUMBERSTRING(INT(A1),2)&"鍏",)&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(A1),2),"[dbnum2...
绛旓細绗竴姝ワ細杈撳叆鏁板瓧锛屽锛12345 绗簩姝ワ細鐐瑰嚮鏁板瓧缁勬梺杈圭殑鏂滅澶 绗笁姝ワ細鐐瑰嚮銆愮壒娈娿戞牸寮 绗洓姝ワ細鐐瑰嚮銆愪腑鏂囧皬鍐欍 鍚岀悊锛氱偣鍑汇愪腑鏂囧ぇ鍐欍 娉細鍦ㄦ櫤鑳借緭鍏ユ硶涓篃鍙互蹇熻緭鍏ヤ腑鏂囧ぇ鍐欐暟瀛楀拰涓枃灏忓啓鏁板瓧
绛旓細鍦˙1涓杈撳叆锛=LOOKUP(--MID(SUBSTITUTE($A$1,".",""),ROW(A1),1),{0,1,2,3,4,5,6,7,8,9},{"闆","澹","璐","鍙","鑲","浼","闄","鏌","鎹","鐜"})锛屽悜涓嬫嫋銆
绛旓細1銆佹湰娆℃搷浣滀娇鐢ㄧ殑Office鍔炲叕杞欢涓篍xcel 2013鐗堟湰銆2銆佹柊寤轰竴涓狤xcel鐢靛瓙琛ㄦ牸锛屽湪琛ㄦ牸涓杈撳叆婕旂ず鏁版嵁鐢ㄤ簬濡備綍鍦‥xcel琛ㄦ牸涓皢灏忓啓閲戦鍦ㄥ彟涓涓崟鍏冩牸鑷姩鐢熸垚澶у啓閲戦鐨勬搷浣溿3銆侀紶鏍囬変腑闇瑕佽浆鎹㈡垚澶у啓閲戦鐨勬暟鎹紝鍗曞嚮榧犳爣鍙抽敭锛屽湪宸ュ叿鏍忛夋嫨璁剧疆鍗曞厓鏍兼牸寮忓伐鍏枫4銆佸湪璁剧疆鍗曞厓鏍兼牸寮忓伐鍏锋爮锛屽崟鍑鏁板瓧鏍忥紝...
绛旓細EXCEL涓暟瀛灏忓啓鎹㈠ぇ鍐欐柟娉曚竴锛氬湪鍗曞厓鏍糀2涓杈撳叆灏忓啓鏁板瓧123.12 B2澶勮緭鍏ヤ互涓嬪叕寮 =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(...
绛旓細姣斿A鍒灏忓啓锛孊鍒楃敤鍏紡鍏堟槸澶у啓 B1杈撳叆 =TEXT(A1,"[dbnum1]")涓嬫媺
绛旓細鏃佽竟鍗曞厓鏍煎仛涓や釜鎿嶄綔锛氫竴璁剧疆鍏紡=A1锛屼簩璁剧疆鏍煎紡涓哄ぇ鍐欙紝濡備笅鍥撅細
绛旓細鍙互缁勫悎浣跨敤left鍜宺ight鍔熻兘锛屼緥濡123456.55锛宭eft(right(A1,6锛,1)锛宺ight锛圓1,6锛夎〃绀轰粠鍗曞厓鏍肩殑鍙宠竟寮濮嬪彇鏁帮紝鍙6浣嶏紝left(A1,6锛夎〃绀轰粠宸﹁竟寮濮嬪彇鏁帮紝鍙1浣嶃