Excel中如何在一单元格随意输入一组小写金额,在另一个单元格会自动显示其大写金额 在word或excel里输入小写金额，在另一格子里自动生成大...
Excel\u4e2d\u5982\u4f55\u5728\u4e00\u5355\u5143\u683c\u968f\u610f\u8f93\u5165\u4e00\u7ec4\u5c0f\u5199\u8d1f\u6570\u91d1\u989d,\u5728\u53e6\u4e00\u4e2a\u5355\u5143\u683c\u4f1a\u81ea\u52a8\u663e\u793a\u5176\u5927\u5199\u91d1\u989d\u5047\u8bbe\u6570\u636e\u5728A1
\u5728B1\u8f93\u5165\u6216\u590d\u5236\u7c98\u8d34\u6b64\u516c\u5f0f
=SUBSTITUTE(TEXT(A1*100,"[DBNUM2]0\u51430\u89d20\u5206"),"-","\u8d1f")
\u4e0a\u9762\u516c\u5f0f\u53ea\u9002\u5408\u4e0d\u592a\u4e25\u683c\u7684\u8d22\u52a1\u8981\u6c42\uff0c\u4e25\u683c\u7684\u8981\u6c42\u6240\u7528\u7684\u516c\u5f0f\u8981\u590d\u6742\u5f97\u591a\u3002
\u8bbeB11\u4e3a\u5c0f\u5199\u91d1\u989d.
\u5728\u5927\u5199\u91d1\u989d\u90a3\u5355\u5143\u683c\u91cc\u9762\u586b\u5165\u516c\u5f0f
=SUBSTITUTE(IF(B11,IF(B119][dbnum2]0\u89d20\u5206;[>][dbnum2]0\u5206;\u6574"),"\u96f6\u5143"),"\u96f6\u5206","\u6574")
假如输入的小写金额在A1单元格，
公式：
=IF(A1=0,"",IF(INT(A1),TEXT(TRUNC(A1),"[dbnum2]")&"元","")&IF(MOD(A1,1)=0,"整",IF(TRUNC(A1,1),IF(A1=TRUNC(A1,1),TEXT(LEFT(RIGHT(A1*100,2)),"[dbnum2]0角整"),TEXT(RIGHT(A1*100,2),"[dbnum2]0"&IF(LEFT(RIGHT(A1*100,2))="0","","角")&"0分")),TEXT(A1*100,"[dbnum2]0分"))))
示例图片：
公式解释：
IF函数是用来逻辑判断，INT函数是取整，TEXT函数是将小写数字转换成中文大写格式，MOD函数是取余数（小数部分）。关于该系列函数的详细帮助及使用说明可以参阅Excel的帮助文件（打开Excel按下F1搜索相关函数即可）。
假设数字金额在A1，公式为
=TEXT(A1*100,"[dbnum2]0佰0拾0元角0分")
就是其中的0佰0拾什么的要自己调整的。
我这个简短些，角位是可以不写整的
=IF(OR(A1="",A1=0),"",TEXT(INT(A1),"[dbnum2]")&IF(INT(A1)=A1,"元整","元")&IF(INT(A1)<>A1,TEXT(--RIGHT(INT(A1*10)),"[=0]零;[>0][dbnum2]#角"),"")&TEXT(--RIGHT(INT(A1*100)),"[dbnum2]#分;;"))
=IF(ROUND(F33,2)<0,"无效数值",IF(ROUND(F33,2)=0,"零",IF(ROUND(F33,2)<1,"",TEXT(INT(ROUND(F33,2)),"[dbnum2]")&"元")&IF(INT(ROUND(F33,2)*10)-INT(ROUND(F33,2))*10=0,IF(INT(ROUND(F33,2))*(INT(ROUND(F33,2)*100)-INT(ROUND(F33,2)*10)*10)=0,"","零"),TEXT(INT(ROUND(F33,2)*10)-INT(ROUND(F33,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(F33,2)*100)-INT(ROUND(F33,2)*10)*10)=0,"整",TEXT((INT(ROUND(F33,2)*100)-INT(ROUND(F33,2)*10)*10),"[dbnum2]")&"分")))
给两个公式你选择：
=IF(ISERROR(FIND(".",A1)),NUMBERSTRING(INT(A1),2)&"元整",IF(ISERROR(NUMBERSTRING(MID(A1,FIND(".",A1)+2,1),2)),NUMBERSTRING(INT(A1),2)&"元"&NUMBERSTRING(MID(A1,FIND(".",A1)+1,1),2)&"角整",NUMBERSTRING(INT(A1),2)&"元"&NUMBERSTRING(MID(A1,FIND(".",A1)+1,1),2)&"角"&NUMBERSTRING(MID(A1,FIND(".",A1)+2,1),2)&"分"))
------------------------------------------------------
=IF((A1-INT(A1))=0,TEXT(A1,"[DBNUM2]")&"元整",IF(INT(A1*10)-A1*10=0,TEXT(INT(A1),"[DBNUM2]")&"元"&TEXT((INT(A1*10)-INT(A1)*10),"[DBNUM2]")&"角整",TEXT(INT(A1),"[DBNUM2]")&"元"&IF(INT(A1*10)-INT(A1)*10=0,"零",TEXT(INT(A1*10)-INT(A1)*10,"[DBNUM2]")&"角")&TEXT(RIGHT(A1,1),"[DBNUM2]")&"分"))
绛旓細寰堝鏄,鍦˙1閲岄潰杈撳叆=A1+5,杩欐牱,浣燗1=100鏃,B1灏变細鑷姩鍙樻垚105,涓2000鏃,B1灏变細鍙樻垚2005
绛旓細鍙互鐨勩傝鐪嬶紱灏卞儚杩欐牱锛屽悎骞鍗曞厓鏍鐨勬椂鍊欙紝涓瀹氳鎻愬墠璁剧疆濂斤紝涓昏灏辨槸鑷姩鎹㈣銆傝繖鏍凤紝鍙互闅忔剰杈撳叆锛屼笉璁哄灏戣銆
绛旓細=VLOOKUP(A2,I5:L8,2)杩欎釜鍑芥暟涓殑绗竴涓弬鏁癆2琛ㄧず鍦ㄤ綘寰楀搴旀竻鍗旾5:L8涓煡鎵惧肩瓑浜嶢2鍗曞厓鏍鍊肩殑璁板綍锛岀浜屼釜鍙傛暟灏辨槸浣犲緱瀵瑰簲娓呭崟锛岀涓変釜鍙傛暟2琛ㄧず鏄剧ず鎵鎵惧埌璁板綍鐨勭浜屽垪鐨勬暟鎹備笉鏄庣櫧鐨勮瘽锛屼綘鍙互鍒扮綉涓婄湅鐪嬭繖涓嚱鏁扮殑涓浜涜祫鏂欍傛垜鍒氭墠鍋氫簡涓涓Excel鏂囦欢璇曚簡涓涓嬶紝杩欎釜鍑芥暟寰堝ソ浣裤備綘闇瑕佺殑...
绛旓細excel鍦鍚屼竴涓鍗曞厓鏍涓皟鏁村瓧闂磋窛鍜岃璺,鍙互閫氳繃璁剧疆鍗曞厓鏍兼牸寮忔枃鏈殑鈥滃榻愭柟寮忊濓紝鐒跺悗鏀瑰彉鍗曞厓鏍肩殑琛岄珮鍜屽垪瀹芥潵杈惧埌鎵闇鐨勬枃鏈棿璺濄傛柟娉曟楠ゅ涓嬶細1銆佹墦寮闇瑕EXCEL琛ㄦ牸锛岄変腑闇瑕佽皟鏁存枃鏈棿璺濈殑鍗曞厓鏍硷紝鍦ㄩ変腑鐨勫崟鍏冩牸鍖哄煙鍐呯偣鍑婚紶鏍囧彸閿紝閫夋嫨鈥滆缃崟鍏冩牸鏍煎紡鈥濓紝杩涘叆鍗曞厓鏍兼牸寮忚缃晫闈2銆佸湪鈥...
绛旓細鏉愭枡/宸ュ叿锛Excel2010 1銆侀紶鏍囧乏閿弻鍑昏绠楁満妗岄潰Excel2016绋嬪簭鍥炬爣锛屽皢鍏舵墦寮杩愯銆傚湪鎵撳紑鐨凟xcel2016绋嬪簭绐楀彛锛岀偣鍑烩滅┖鐧藉伐浣滆杽鈥濋夐」锛屾柊寤轰竴涓┖鐧紼xcel宸ヤ綔钖勩2銆鍦ㄥ崟鍏冩牸涓緭鍏ユ枃瀛楀唴瀹癸紝鏂囧瓧鍐呭瓒呭嚭鍗曞厓鏍兼锛屾枃瀛楀唴瀹瑰湪鍗曞厓鏍间腑杩樻槸涓琛屾樉绀虹殑锛屼笉浼氳嚜鑷姩杩涜鎹㈣銆3銆侀変腑鍗曞厓鏍硷紝鐐瑰嚮榧犳爣鍙抽敭锛...
绛旓細浠PS 2019鐗堟湰涓轰緥锛氬叧浜鍦‥XCEL涓浣瀵瑰伐浣滆〃杩涜淇濇姢,浣垮緱涓閮ㄥ垎鍖哄煙鍐呭鍙互琚慨鏀,鍏跺畠鍗曞厓鏍涓嶈兘琚慨鏀癸紙涓嶈缃瘑鐮侊級锛屽缓璁偍鍙娇鐢╓PS2019鏉ュ畬鎴愭搷浣滐紝鍏蜂綋姝ラ濡備笅锛1銆佸叏閫夛紙CTRL+A锛夎〃鏍硷紝鐐瑰嚮榧犳爣鍙抽敭銆岃缃崟鍏冩牸鏍煎紡銆嶏紝杩涘叆淇濇姢锛屽彇娑堛岄攣瀹氥嶏紱2銆侀変腑闇瑕佷繚鎶ょ殑鍖哄煙锛屽啀娆￠紶鏍囧彸閿繘鍏ャ...
绛旓細excel涓锛屼竴涓鍗曞厓鏍杈撳叆鍐呭鍚庡彟涓涓悓鏃惰嚜鍔ㄦ樉绀哄叿浣撴搷浣滄楠ゅ涓嬶細涓銆佺涓姝ワ紝鍏堟墦寮Excel锛屾帴涓嬫潵閫夋嫨涓涓崟鍏冩牸渚嬪锛欳4锛岀偣鍑绘暟鎹斺旀暟鎹湁鏁堟э紝濡備笅鍥炬墍绀恒備簩銆佺浜屾锛屾帴涓嬫潵寮瑰嚭妗嗘锛岀劧鍚庡湪鍏佽涓嬮潰閫夋嫨搴忓垪锛屾帴涓嬫潵鍦ㄦ潵婧愪笂闈㈠～鍐欐墍闇鐨勯夋嫨鍐呭锛岀敤灏忓啓鐨勯楀彿锛,锛夐殧寮锛屼緥濡傞粍鑹,绾㈣壊,...
绛旓細鍒欏湪B2杈撳叆鈥='C:\Users\Administrator\Desktop\[1.xlsx]Sheet1'!$A$10鈥濄傛敞鎰忓崟寮曞彿锛屽叾涓瑿:\Users\Administrator\Desktop\涓哄伐浣滆〃1.xlsx鎵鍦ㄨ矾寰勩傚苟闇瑕鍦‥XCEL涓鍚敤寮曠敤鍔熻兘銆傛敞鎰忎互涓婂紩鍙峰唴涓哄疄闄呰緭鍏鍗曞厓鏍鍐呭锛屾墍鏈夌鍙峰潎搴斿湪鑻辨枃鐘舵佷笅杈撳叆銆
绛旓細鍙互璁惧畾銆備緥濡傦細A1涓虹壒瀹鍗曞厓鏍锛孊1涓鸿緭鍏ュ崟鍏冩牸锛屽綋A1涓洪潪绌烘椂锛孊1鍙緭鍏ユ暟鎹紝鍒欏B1璁剧疆鈥滄暟鎹/鏈夋晥鎬р濓紝鏂规硶锛1 閫変腑B1鍗曞厓鏍 2 鏁版嵁鏈夋晥鎬т腑 鏈夋晥鎬ф潯浠堕夋嫨 鈥滆嚜瀹氫箟鈥 锛屾妸鈥滃拷鐣ョ┖鍊尖濆墠闈㈢殑鍕惧幓鎺夈傚湪鈥滃叕寮忊濅腑杈撳叆 =A1<>""OK锛
绛旓細1銆侀鍏堝氨鏄數鑴戜笂瀹夎Excel杞欢锛岀劧鍚庢墦寮Excel杞欢锛屽鍥炬墍绀猴紝2銆佺劧鍚鍦ㄥ崟鍏冩牸涓殢鎰杈撳叆鐐规枃瀛楋紝3銆佸綋鐒舵枃瀛楄緭鍏ョ殑瓒婂锛屾晥鏋滀細瓒婂ソ锛屽洜涓烘枃瀛楀皯鐨勮瘽锛屽ぇ浣撹鐪嬪氨娓呮櫚锛岃繖鏃跺欙紝鎵惧埌鈥滆鍥锯濓紝鐒跺悗鐐硅鍥俱4銆佽繖鏃跺欎粠宸﹀線鍙虫壘鍒扮鍥涗釜灞炴э紝鈥滈槄璇绘ā寮忊濆鍥炬墍绀猴紝浼氬彂鐜颁笉绠″崟鍑诲摢涓涓崟鍏冩牸锛屾暣琛...
