诚心请教excel财务专家。
\u8bda\u5fc3\u8bf7\u6559excel\u8d22\u52a1\u4e13\u5bb6\u3002\u91d1\u989d\u5927\u5c0f\u5199http://www.kuaijiba.com/36.html
\u4e00\u3001\u901a\u8fc7EXCEL\u539f\u6709\u51fd\u6570\u548c\u81ea\u5b9a\u4e49\u683c\u5f0f\u8f6c\u5316
1\u3001\u8fd9\u4e2a\u770b\u8d77\u6765\u6bd4\u8f83\u70e6\u7410\uff0c\u4e0d\u8fc7\u7ec8\u7a76\u8fbe\u5230\u4e86\u6548\u679c\u3002
=TEXT(TRUNC(ROUND(\u5f85\u8f6c\u6362\u6570\u5b57\u6216\u5355\u5143\u683c,2),0),\u201d[DBNUM2]G/\u901a\u7528\u683c\u5f0f\u201d&\u201d\u5143\u201d)&(IF((ROUND(\u5f85\u8f6c\u6362\u6570\u5b57\u6216\u5355\u5143\u683c,2)-TRUNC(ROUND(\u5f85\u8f6c\u6362\u6570\u5b57\u6216\u5355\u5143\u683c,2),0))<0.01,\u201d\u201d,IF(MIDB(ROUND(\u5f85\u8f6c\u6362\u6570\u5b57\u6216\u5355\u5143\u683c,2),IF((ROUND(\u5f85\u8f6c\u6362\u6570\u5b57\u6216\u5355\u5143\u683c,2)-TRUNC(ROUND(\u5f85\u8f6c\u6362\u6570\u5b57\u6216\u5355\u5143\u683c,2),1))<0.01,(LEN(ROUND(\u5f85\u8f6c\u6362\u6570\u5b57\u6216\u5355\u5143\u683c,2))),(LEN(ROUND(\u5f85\u8f6c\u6362\u6570\u5b57\u6216\u5355\u5143\u683c,2))-1)),1)=\u201d0\u3003,\u201d\u96f6\u201d,TEXT(MIDB(ROUND(\u5f85\u8f6c\u6362\u6570\u5b57\u6216\u5355\u5143\u683c,2),IF((ROUND(\u5f85\u8f6c\u6362\u6570\u5b57\u6216\u5355\u5143\u683c,2)-TRUNC(ROUND(\u5f85\u8f6c\u6362\u6570\u5b57\u6216\u5355\u5143\u683c,2),1))<0.01,(LEN(ROUND(\u5f85\u8f6c\u6362\u6570\u5b57\u6216\u5355\u5143\u683c,2))),(LEN(ROUND(\u5f85\u8f6c\u6362\u6570\u5b57\u6216\u5355\u5143\u683c,2))-1)),1),\u201d[DBNUM2]G/\u901a\u7528\u683c\u5f0f\u201d)&\u201d\u89d2\u201d)))&(IF((ROUND(\u5f85\u8f6c\u6362\u6570\u5b57\u6216\u5355\u5143\u683c,2)-TRUNC(ROUND(\u5f85\u8f6c\u6362\u6570\u5b57\u6216\u5355\u5143\u683c,2),1))<0.01,\u201d\u6574\u201d,TEXT(RIGHT(ROUND(\u5f85\u8f6c\u6362\u6570\u5b57\u6216\u5355\u5143\u683c,2),1),\u201d[DBNUM2]G/\u901a\u7528\u683c\u5f0f\u201d)&\u201d\u5206\u201d))
2\u3001\u8fd9\u4e2a\u662f\u6700\u7b80\u6d01\u7684\u51fd\u6570\u5b9e\u73b0\u65b9\u5f0f
=IF(\u5f85\u8f6c\u6362\u6570\u5b57\u6216\u5355\u5143\u683c<0,\u201d\u8d1f\u201d,\u201d\u201d)&IF(TRUNC(ROUND(\u5f85\u8f6c\u6362\u6570\u5b57\u6216\u5355\u5143\u683c,2))=0,\u201d\u201d,TEXT(TRUNC(ABS(ROUND(\u5f85\u8f6c\u6362\u6570\u5b57\u6216\u5355\u5143\u683c,2))),\u201d[DBNum2]\u201d)&\u201d\u5143\u201d)&IF(ISERR(FIND(\u201d.\u201d,ROUND(\u5f85\u8f6c\u6362\u6570\u5b57\u6216\u5355\u5143\u683c,2))),\u201d\u201d,TEXT(RIGHT(TRUNC(ROUND(\u5f85\u8f6c\u6362\u6570\u5b57\u6216\u5355\u5143\u683c,2)*10)),\u201d[DBNum2]\u201d))&IF(ISERR(FIND(\u201d.0\u3003,TEXT(\u5f85\u8f6c\u6362\u6570\u5b57\u6216\u5355\u5143\u683c,\u201d0.00\u3003))),\u201d\u89d2\u201d,\u201d\u201d)&IF(LEFT(RIGHT(ROUND(\u5f85\u8f6c\u6362\u6570\u5b57\u6216\u5355\u5143\u683c,2),3))=\u201d.\u201d,TEXT(RIGHT(ROUND(\u5f85\u8f6c\u6362\u6570\u5b57\u6216\u5355\u5143\u683c,2)),\u201d[DBNum2]\u201d)&\u201d\u5206\u201d,\u201d\u6574\u201d)
\u4e8c\u3001\u901a\u8fc7\u81ea\u5b9a\u4e49\u51fd\u6570\u8f6c\u5316
\u901a\u8fc7\u81ea\u5b9a\u4e49\u51fd\u6570\uff0c\u4ee5\u540e\u5728EXCEL\u4e2d\u5c31\u53ef\u4ee5\u76f4\u63a5\u8c03\u7528\u51fd\u6570MoneyTrans()\u6765\u5b9e\u73b0\u4e86\u3002
\u5982\u679c\u719f\u6089VBA\u6216\u8005\u719f\u6089\u81ea\u5b9a\u4e49\u51fd\u6570\u7684\u670b\u53cb\u53ef\u4ee5\u5c1d\u8bd5\u4e00\u4e0b\uff0c\u5173\u4e8e\u81ea\u5b9a\u4e49\u51fd\u6570\u53ef\u53c2\u8003Excel\u81ea\u5b9a\u4e49\u51fd\u6570\u5b9e\u4f8b\u5256\u6790(\u8f6c).
\u4ee5\u4e0b\u662f\u6570\u5b57\u91d1\u989d\u8f6c\u6362\u6210\u4e2d\u6587\u5927\u5199\u91d1\u989d\u81ea\u5b9a\u4e49\u51fd\u6570,\u53ef\u4ee5\u5728VBA\u6a21\u5757\u4e2d\u6dfb\u52a0\u5c31\u53ef\u4ee5\u4e86\u3002
Public Function MoneyTrans(Money As Currency) As String
On Error GoTo Doerr
Dim CN(9) As String
Dim CU(15) As String
Dim Temp As String, strNum As String
Dim CM As String
Dim tFirst As String, tEnd As String
Dim i As Long, j As Long, k As Long
CN(0) = \u201c\u96f6\u201d
CN(1) = \u201c\u58f9\u201d
CN(2) = \u201c\u8d30\u201d
CN(3) = \u201c\u53c1\u201d
CN(4) = \u201c\u8086\u201d
CN(5) = \u201c\u4f0d\u201d
CN(6) = \u201c\u9646\u201d
CN(7) = \u201c\u67d2\u201d
CN(8) = \u201c\u634c\u201d
CN(9) = \u201c\u7396\u201d
\u2018 CU(0) = \u201c\u5206\u201d
\u2018 CU(1) = \u201c\u89d2\u201d
CU(0) = \u201c\u5143\u201d
CU(1) = \u201c\u62fe\u201d
CU(2) = \u201c\u4f70\u201d
CU(3) = \u201c\u4edf\u201d
CU(4) = \u201c\u4e07\u201d
CU(5) = \u201c\u62fe\u201d
CU(6) = \u201c\u4f70\u201d
CU(7) = \u201c\u4edf\u201d
CU(8) = \u201c\u4ebf\u201d
CU(9) = \u201c\u62fe\u201d
CU(10) = \u201c\u4f70\u201d
CU(11) = \u201c\u4edf\u201d
If Money = 0 Then
CM = \u201c\u96f6\u5143\u6574\u201d
GoTo Complete
End If
strNum = Trim(Str(FormatCurrency(Money, 2, vbTrue, vbFalse, vbFalse)))
If Left(strNum, 1) = \u201c-\u201d Then
tFirst = \u201c\u8d1f\u201d
strNum = Right(strNum, Len(strNum) - 1)
Else
tFirst = \u201c\u201d
End If
i = InStrRev(strNum, \u201c.\u201d)
If i 0 Then
Temp = Right(strNum, i)
If Len(strNum) - i = 1 Then Temp = Temp + \u201c0\u3003
CM = CN(CInt(Left(Right(Temp, 2), 1))) + \u201c\u89d2\u201d + CN(CInt(Right(Temp, 1))) + \u201c\u5206\u201d
tEnd = \u201c\u201d
strNum = Left(strNum, i - 1)
Else
tEnd = \u201c\u6574\u201d
End If
i = 0
For j = Len(strNum) To 1 Step -1
k = CInt(Right(Left(strNum, j), 1))
If k = 0 Then
If i 0 And i 4 And i 8 Then
CM = CN(k) + CM
Else
CM = CN(k) + CU(i) + CM
End If
Else
CM = CN(k) + CU(i) + CM
End If
\u2018 CM = CN(k) + CU(i) + CM
i = i + 1
Next j
CM = tFirst + CM + tEnd
CM = Replace(CM, \u201c\u96f6\u96f6\u201d, \u201c\u96f6\u201d)
CM = Replace(CM, \u201c\u96f6\u96f6\u201d, \u201c\u96f6\u201d)
CM = Replace(CM, \u201c\u4ebf\u96f6\u4e07\u96f6\u5143\u201d, \u201c\u4ebf\u5143\u201d)
CM = Replace(CM, \u201c\u4ebf\u96f6\u4e07\u201d, \u201c\u4ebf\u96f6\u201d)
CM = Replace(CM, \u201c\u4e07\u96f6\u5143\u201d, \u201c\u4e07\u5143\u201d)
CM = Replace(CM, \u201c\u96f6\u4ebf\u201d, \u201c\u4ebf\u201d)
CM = Replace(CM, \u201c\u96f6\u4e07\u201d, \u201c\u4e07\u201d)
CM = Replace(CM, \u201c\u96f6\u5143\u201d, \u201c\u5143\u201d)
CM = Replace(CM, \u201c\u96f6\u96f6\u201d, \u201c\u96f6\u201d)
CM = Replace(CM, \u201c\u96f6\u96f6\u201d, \u201c\u96f6\u201d) \u2018\u91cd\u590d\u66ff\u6362\u4e00\u6b21
Complete:
Gerr = 0 \u2018\u64cd\u4f5c\u6210\u529f,\u65e0\u9519\u8bef\u53d1\u751f
MoneyTrans = CM
Exit Function
Doerr:
Gerr = -1 \u2018\u672a\u77e5\u9519\u8bef
Errexit:
MoneyTrans = \u201c\u201d
End Function
如你可以在A1单元格输入数字，B1单元格复制下面的公式，即可显示
=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]")&"分")))
任何一格输入:
=IF(A1="","",TEXT(INT(A1),"[dbnum2]0仟0佰0拾0元"))&TEXT((A1-INT(A1))*100,"[dbnum2]0角0分")
如A1是 172.00
公式自动显示零仟壹佰柒拾贰元零角零分
如A1是 2001.45
公式自动显示贰仟零佰零拾壹元肆角伍分
如楼主测试后, 尚有不足, 请多举例子,
当然, 条件愈多, 公式愈长
绛旓細鏈鐑︽伡鐨勬槸鏁村ぉ璺熸灟鐕ョ殑鏁板瓧鎵撲氦鍒颁笖涓嶈兘椹檸鍑洪敊锛侊紒锛佷綘璇寸殑璐㈠姟杞欢寤鸿浣犺繕鏄埆涔颁簡锛屾瘡涓叕鍙哥敤鐨勮储鍔¤蒋浠朵笉澶竴鏍凤紝浣嗕笉鍙殢鎰忎娇鐢ㄥ叾浠栫殑杞欢鐨勶紝杩欐秹鍙婅储鍔″畨鍏ㄩ棶棰橈紝杩樻秹鍙婂吋瀹归棶棰樸傞佷笂浣犵殑鍏冲績鍋氬ソ銆傚啀鍔犱笂鐐瑰ス鍠滄鐨勭墿璐ㄩ渶姹傚氨濂斤紒
绛旓細涓夊ぇ鎶ヨ〃 鍙互鐩存帴鎵撳嵃 涔熷彲浠ョ洿鎺ヨ浆鎹㈡垚EXCEL琛 棣栧厛鎵惧埌涓夊ぇ鎶ヨ〃 鐩存帴鎵撳紑鎶ヨ〃 鐐规枃浠跺氨鍙互鎵撳嵃 瀵煎嚭鎿嶄綔浜
绛旓細鑰屼笖瀛﹂噾铻嶇殑璇濊揣甯侀摱琛屽锛屽浗闄呴噾铻嶅锛岃惀閿瀛︼紝璐㈠姟瀛﹁繖鍑犻棬鏄繀椤荤啛缁冪殑锛屼綘浠ュ悗鑰冮摱琛屼篃鏄繀椤昏鑰冪殑锛3锛屾帉鎻℃妧鑳斤細璁＄畻鏈烘妧鑳藉繀涓嶅彲灏戯紝OFFICE鎶鑳藉繀椤婚潪甯哥啛缁冿紝灏辨槸word,excel,ppt杩欎笁鏍枫傚鏋滃彲浠ヨ繕鍙互瀛︾偣ps浠ュ強瑙嗛澶勭悊Pre銆傚叾娆℃槸鑻辫鎶鑳斤紝鏈濂藉涓浜涘晢鍔¤嫳璇傜劧鍚庡啓锛屽啓浣滈潪甯搁潪甯搁噸瑕侊紝...
绛旓細浜嬪疄涓璐㈠姟閮ㄧ殑浼氳浠兘闈炲父鐨勭収椤炬垜,鏈熶腑鏈夋鏃堕棿鐢变簬鎴戣嚜宸辩殑浜涜鍘熷洜,瀵瑰疄涔犲伐浣滃け鍘讳簡淇″績鍜屾縺鎯,鐢氳嚦鎯冲埌浜嗛鍑,鏄储鍔￠儴鐨勪細璁＄粰浜嗘垜闀胯緢鑸殑寤鸿鍜屽姖瀵,璁╂垜鏈缁堥『鍒╁畬鎴愪簡瀹炰範銆傜敱姝,瀵逛簬宸ヤ綔杩囩▼涓拰鍚屼簨鍙婇瀵肩殑鐩稿,鎴戞湁浜嗗叏鏂扮殑璁よ瘑銆傝繖涓娆＄殑瀹炰範铏界劧鏃堕棿鐭殏,铏界劧鎺ヨЕ鍒扮殑宸ヤ綔寰堟祬,浣嗘槸渚濈劧璁╂垜瀛﹀埌...
绛旓細鏈夋椂鍊欎篃浼氶亣鍒颁竴浜涙妧鏈柟闈笉鎳傜殑闂,杩樻槸浼氳櫄蹇璇锋暀銆傚湪浠ュ悗鐨勫伐浣滀腑鎴戜細鏇村姞寮鸿嚜韬涔,鎻愰珮鑷韩...3銆佽壇濂界殑璁＄畻鏈烘搷浣滆兘鍔,鐔熺粌搴旂敤MSOutlook,Word,Excel,PowerPoint绛夊姙鍏蒋浠; 4銆佽兘澶熼傚簲鍚勭鍘嬪姏涓嬪伐浣...璐㈠姟閮ㄦ槸浼佷笟鐨勫叧閿儴闂,瀵瑰唴璐㈠姟绠＄悊姘村钩鐨勮姹傚簲涓嶆柇鎻愬崌,瀵瑰瑕佸簲瀵圭◣鍔°佸璁″強璐㈡斂绛夋満鍏崇殑鍚勯」妫鏌...
绛旓細鍐呭悜鐨勪汉鏅亶閮藉緢鏈夎愬績锛岃屼笖鎰挎剰鍊惧惉锛屽彲浠ュ緢濂界殑瑙ｅ喅瀛︾敓鐨勫悇绉嶉棶棰樸傛暀鑲茬被宸ヤ綔涓紝鑰佸笀鍜屽鐢熸槸涓绉嶄笂涓庝笅鐨勫叧绯伙紝鍦ㄤ笌瀛︾敓鐨勪氦娴佷腑锛屽唴鍚戠殑浜轰笉浼氫骇鐢熷帇鍔涳紝宸ヤ綔鏇村姞寮蹇冦2銆璐㈠姟绫诲伐浣 涓嶇鏄瘉鍒革紝杩樻槸閾惰锛屾垨鑰呴噾铻嶏紝鐢氳嚦鏄竴浜涘叕鍙搁噷鐨勮储浼氾紝閮芥槸闈炲父閫傚悎鍐呭悜鑰呯殑銆傚洜涓猴紝杩欎簺宸ヤ綔鏄笌鏁板瓧鎵...
绛旓細浣犱滑鏄粈涔堝叕鍙稿晩锛岃鎹㈡帀SAP鐢ㄦ氮娼紵鏄熀浜庝粈涔堟柟闈㈢殑鑰冭檻鎵嶆崲杞欢鐨勫晩锛熷叾瀹炰綘鐨勯棶棰樿窡SAP鏄病鏈変粈楹煎叧绯荤殑锛屼富瑕佺殑闂鏄浣曞鍏ュ埌娴疆杞欢涓傛氮娼SAP鎴戣兘鐭ラ亾锛屼絾鏄疭AP瀵兼氮娼垜灏变笉鐭ラ亾浜嗐
绛旓細PB9鑷甫ORACLE杩炵粨缁勪欢鍟,鐩存帴鍦ㄨ繛鎺ユ暟鎹簱閭ｉ噷璁剧疆濂藉氨琛屼簡鍟.鍏堝皢C:\ORACLE瀹夎鐩綍\...\TNSNAMES.ORA鏂囦欢(鐢ㄨ浜嬫湰鎵撳紑)閲岀殑杩炴帴鍙傛暟璁惧ソ.鍦≒B閲,DATABASE閲屾柊寤轰竴涓繛鎺,鍙互閫夋嫨ORACLE8/8I鎴朞RACLE9鏂板缓,杈撳叆PROFILE NAME\SERVER NAME\LOGIN ID\PASSWORD鍑犻」,灏卞彲浠ヨ繛鎺ヤ簡.鑷充簬璇翠綘EXLCE閲岀殑鏁...
绛旓細鍒嗛厤閫傚悎鐨勫伐浣滅粰浣犮傚笇鏈涘浣犳湁浜涘府鍔┿傝ˉ鍏呬竴涓嬶紝涓鑸瓥鍒掞紝浼氱敤鍒扮殑杞欢锛屽缓璁綘浜嗚В鎺屾彙锛寃ord涓嶇敤璇翠簡锛excel寰堥噸瑕侊紝鐗瑰埆鏄粬鐨勪竴浜涘畯鐨勭敤娉曪紝寰堝鍦版柟鐢ㄥ緱鍒般俻pt锛岀粡甯镐細鐢ㄥ埌銆俶indmanager锛屽緢鏈夌敤鐨勮蒋浠躲倂isio锛屾祦绋嬪浘缁樺埗杞欢锛屼竴鑸瓥鍒掍篃鐢ㄥ畠鏉ヨ鍒掔晫闈紝鍙互鍋氬埌鐪熷疄姣斾緥銆
绛旓細璇氬績鍚戠幆澧冭壓鏈璁″墠杈璇锋暀涓鐐瑰鍐呰璁＄殑瀛︿範鏂规硶鍜岀粡楠 鎴戞槸涓涓笓绉戦櫌鏍＄殑瀛︾敓,涓撲笟鏄鍐呰璁,浣嗘槸鐢变簬涓嶇敇蹇冨鍘,鎴戣繕鍙傚姞浜嗘湰绉戣嚜瀛﹁冭瘯,涓撲笟鏄幆澧冭壓鏈璁,鐜板湪澶т簩鍗冲皢缁撴潫浜,鑷冧篃鍙湁3绉戝氨鍙互閫氳繃浜,鎰熻鑷繁瀛﹀緱楂樹笉鎴愪綆... 鎴戞槸涓涓笓绉戦櫌鏍＄殑瀛︾敓,涓撲笟鏄鍐呰璁,浣嗘槸鐢变簬涓嶇敇蹇冨鍘,鎴戣繕鍙傚姞浜...
