| 近來轉(zhuǎn)了轉(zhuǎn)幾個EXCEL相關(guān)論壇，發(fā)現(xiàn)有部分網(wǎng)友在尋求數(shù)字金額轉(zhuǎn)換為人民幣大寫的方法。出于好奇，我在網(wǎng)上搜索了一番，什么VBA、加載宏，公式函數(shù)各式方法爭相亮相，應(yīng)有盡有。但我的習(xí)慣是，只要能用公式解決的問題，堅決不使用VBA。所以我就特別地關(guān)注使用公式來生成的方法，結(jié)果是用公式的方法可謂是更加地燦爛繽紛，使人眼花繚亂。 通過分析我收集到的二十幾個公式，發(fā)現(xiàn)比較牛，適合我口味的公式有三個，在此我將其列出。 公式一： SUBSTITUTE(SUBSTITUTE(IF(A1<0,"負(fù)","")&TEXT(TRUNC(ABS(ROUND(A1,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(A1,2))),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(A1,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A1,2),3))=".",TEXT(RIGHT(ROUND(A1,2)),"[DBNum2]")&"分",IF(ROUND(A1,2)=0,"","整")),"零元零",""),"零元","") 公式二： CONCATENATE(IF(A1<0,"負(fù)",""),TEXT(IF(TRUNC(A1)=0,"",TRUNC(ABS(A1))),"[DBNum2]"),IF(INT(TRUNC(A1))=0,"","元"),TEXT(IF(OR(ABS(A1)<0.1,TRUNC(A1)=A1),"",RIGHT(TRUNC(A1*10),1)),"[DBNum2]"),IF(RIGHT(TRUNC(A1*10),1)="0","","角"),TEXT(IF(RIGHT(TRUNC(A1*100),1)="0","",RIGHT(TRUNC(A1*100),1)),"[DBNum2]"),IF(RIGHT(TRUNC(A1*100),1)="0","","分")) 公式三： IF(ROUND(A1,2)=0,"",IF(ROUND(ABS(A1),2)>=1,TEXT(INT(ROUND(ABS(A1),2)),"[DBNum2]")&"元","")&IF(RIGHT(TEXT(A1,".00"),2)*1=0,"整",IF(RIGHT(TEXT(A1,".00"),4)*1>=1,IF(RIGHT(TEXT(A1,".00"),2)*1>9,"","零"),IF(ROUND(ABS(A1),2)>=1,"零",""))&IF(RIGHT(TEXT(A1,".00"),2)*1>9,TEXT(LEFT(RIGHT(TEXT(A1,".00"),2)),"[DBNum2]")&"角","")&IF(RIGHT(TEXT(A1,".00"))*1>0,TEXT(RIGHT(TEXT(A1,".00")),"[DBNum2]")&"分","整"))) 我用不同的數(shù)值對這三個公式進(jìn)行了一番測試，都達(dá)到了我的要求，至少到目前為止還沒有發(fā)現(xiàn)上述公式存在著什么錯誤。上述公式對負(fù)數(shù)的處理采取了不同的方法。公式一和公式二對負(fù)數(shù)是在其前部加上一個“負(fù)”字，而公式三則是按正數(shù)進(jìn)行處理。我比較推崇公式三的方式，不是還有條件格式可以設(shè)置嗎？若是負(fù)數(shù)就用條件格式來變?yōu)榧t色。 我不準(zhǔn)備在此對上述公式進(jìn)行分析解說。撰寫本文的目的是我也想自己來“組裝”一個所謂的通用公式。我只所以說是“組裝”而不是叫什么“開發(fā)”或者是“拼湊”，是因為EXCEL的函數(shù)現(xiàn)成地擺在了那里，使用時就是為了達(dá)到某種目的，按照其固有的規(guī)則，將其進(jìn)行有機(jī)的組合，可謂“組裝”。但是在這個過程中，也是要開動腦筋的，并非是隨隨便便“拼湊”就能成功的。本文的目的就是想將我在“組裝”公式時的思路展現(xiàn)出來。 若用程序設(shè)計語言（比如用C語言）來寫一個人民幣數(shù)字金額轉(zhuǎn)大寫金額會怎樣考慮呢？我想每一位數(shù)字都要考慮到。但在EXCEL中，單元格格式中有一個“特殊”格式，里面就有將小寫數(shù)字轉(zhuǎn)換為大寫數(shù)字的格式。但要注意的是，這里轉(zhuǎn)換的是數(shù)字而不是金額。所以轉(zhuǎn)換出來后并不存在“元”、“角”、“分”、“整”等在大寫金額中出現(xiàn)的字。但是，“元”只出現(xiàn)在整數(shù)部分的后面，而小數(shù)部分只有角分兩位。這樣只要利用EXCEL的“特殊”格式將數(shù)據(jù)分為整數(shù)部分、角位和分位三個部分來處理就行了。處理的順序是先整數(shù)，再角位、再分位。 由于要分三個部分分別處理，然后根據(jù)情況分別與“元”，“角”，“分”及“整”相連接。連接的方式可以使用CONCATENATE()函數(shù)，如公式二，也可以使用連接符＆。而用＆來連接，公式結(jié)構(gòu)似乎會相對簡單清晰一些，所以我選擇了用＆來連接。 雖然對問題進(jìn)行了分解，但需要考慮的細(xì)節(jié)相當(dāng)多。 若單元格為0或者空，輸出空，避免出現(xiàn)“零”、“零元”等字樣。對非數(shù)字字符不進(jìn)行處理，使用EXCEL自身的錯誤提示。 對負(fù)數(shù)按正數(shù)處理，再用條件格式將其轉(zhuǎn)換為紅色，不擬采用“負(fù)XXXXXX”的形式。 整數(shù)加“元”，若是純整數(shù)，就只處理整數(shù)且加“整”，不再處理小數(shù)部分。若是純小數(shù)，放在小數(shù)部分處理，不單獨列出處理。角為0且分不為0，應(yīng)輸出“零”，只有角的就加“整” 如果有尾數(shù)，即有3位以上小數(shù)（這種情況也許很少發(fā)生，但作為一個“通用”公式，卻不能不考慮這種情況）。則有兩種處理方式，一是截尾，另一則是四舍五入。由于兩種方式的排它性。決定做兩個公式。 如果數(shù)值小于分，截尾公式輸出空，而四舍五入公式則要考慮舍入問題。若有舍入輸出分，沒有則輸出空。 根據(jù)這個思路，我“組裝”了下面兩個公式。 公式四： IF(A1=0,"",IF(ABS(A1)<0.995,"",TEXT(INT(ROUND(ABS(A1),2)),"[DBNum2]")&"元")&IF(RIGHT(TEXT(A1,".00"),2)*1=0,IF(ABS(A1)<0.005,"","整"),TEXT(IF(ABS(A1)<0.095,"",LEFT(RIGHT(TEXT(A1,".00"),2))),"[dbnum2]")&IF(LEFT(RIGHT(TEXT(A1,".00"),2))*1=0,"","角")&IF(RIGHT(TEXT(A1,".00"))*1=0,"整",TEXT(RIGHT(TEXT(A1,".00")),"[dbnum2]")&"分"))) 公式五： IF(A1=0,"",IF(ABS(A1)<1,"",TEXT(TRUNC(ABS(A1)),"[DBNum2]")&"元")&IF(RIGHT(TRUNC(A1*100),2)*1=0,IF(ABS(A1)<0.01,"","整"),IF(ABS(A1)<0.1,"",TEXT(RIGHT(TRUNC(A1*10)),"[dbnum2]"))&IF(RIGHT(TRUNC(A1*10))*1=0,"","角")&IF(RIGHT(TRUNC(A1*100))*1=0,"整",TEXT(RIGHT(TRUNC(A1*100)),"[dbnum2]")&"分"))) 公式四是四舍五入公式，公式五則是截尾公式。兩個公式的結(jié)構(gòu)其實是一樣的，只是使用了不同的函數(shù)。下面用公式四來說明。 TEXT(INT(ROUND(ABS(A1),2)),"[DBNum2]")&"元"用來處理整數(shù)部分，考慮到純小數(shù)及舍入問題，增加一個判斷ABS(A1)<0.995，用0.995，即保證了正常的舍入，又避免了出現(xiàn)0.9945也進(jìn)行舍入的錯誤。 用RIGHTB(TEXT(A1,".00"),2)*1=0來判斷是不是純整數(shù)，是就輸出“整”，后面就不用處理了。因為TEXT函數(shù)輸出的是文本值，所以在這里有兩種處理方法，一是就是本式，二是RIGHTB(TEXT(A1,".00"),2)="00"。 LEFT(RIGHT(TEXT(A1,".00"),2))是取出角位數(shù)，增加一個判斷ABS(A1)<0.095是為了在只有分票的情況下角位避免出現(xiàn)“零”的字樣，0.095也是考慮了尾數(shù)的舍入問題。 IF(LEFT(RIGHT(TEXT(A1,".00"),2))*1=0,"","角")，角位是0輸出空，否則輸出“角”。同樣，這里的判斷表達(dá)式也可改為LEFT(RIGHT(TEXT(A1,".00"),2))="0 " 用RIGHT(TEXT(A1,".00"))取出分位數(shù)，分位為零輸出“整”，否則輸出分位數(shù)。 對于負(fù)數(shù)，如果一定要用“負(fù)XXXXXX”的形式，則直接在第二個判斷前加上“IF(A1<0,"負(fù)","")&”。 還有網(wǎng)友提出來說萬位為零時不輸出零不符合規(guī)范，為此我專門去查了一下相關(guān)規(guī)定。結(jié)果如下： 大寫金額中間有“0”時，大寫金額要寫“零”字；小寫金額中間連續(xù)有幾個“0”時，大寫金額中間可以只寫一個“零”字；小寫金額萬位或元位是“ 0”，或者數(shù)字中間連續(xù)有幾個“0”，萬位、元位也是“0”，但千位、角位不是“0”時，大寫金額中可以只寫一個“零”字，也可以不寫“零”字。 所以上述公式在當(dāng)萬位為零而千位不為零時，以不輸出零的方式處理，應(yīng)該是符合規(guī)范的。也許正是EXCEL采取這種樣式的原因。當(dāng)然，如果遵照習(xí)慣非要加上這個零字也不是不可能。只不過要增加一點公式復(fù)雜度罷了。 在一個數(shù)中，萬會出現(xiàn)幾次？不管是萬、十萬、百萬、千萬，這個萬就出現(xiàn)一次，如果是萬萬則是億，如果是萬億又是兆，萬兆呢？已經(jīng)超出EXCEL的處理能力了。所以答案是萬字在大寫金額中只可能出現(xiàn)一次，且只出現(xiàn)在整數(shù)部分。即然是這樣，用SUBSTITUTE()函數(shù)就能解決問題。即判斷當(dāng)萬位為零而千位不為零時，用“萬零”來替換“萬”，其它情況下不進(jìn)行替換。因為EXCEL只有在這種情況下不會輸出“零”。 在四舍五入公式中，因為還要考慮尾數(shù)舍入問題，判斷使用了四舍五入函數(shù)ROUND()，公式四的輸出整數(shù)的部分公式改為： IF(AND(RIGHT(INT(ROUND(ABS(A1),2)/10000))="0",RIGHT(INT(ROUND(ABS(A1),2)/1000))<>"0"),SUBSTITUTE(TEXT(INT(ROUND(ABS(A1),2)),"[DBNum2]"),"萬","萬零"),TEXT(INT(ROUND(ABS(A1),2)),"[DBNum2]")) 而截斷公式不用考慮尾數(shù)舍入問題，直接用TRUNC()取萬位和千位數(shù)來判斷。相應(yīng)地公式五的整數(shù)部分亦改為： IF(AND(RIGHT(TRUNC(ABS(A1)/10000))="0",RIGHT(TRUNC(ABS(A1)/1000))<>"0"),SUBSTITUTE(TEXT(TRUNC(ABS(A1)),"[DBNum2]"),"萬","萬零"),TEXT(TRUNC(ABS(A1)),"[DBNum2]")) 為了一個我認(rèn)為不是很必要的“零”字使公式臃腫了許多，而我喜歡簡單清晰的公式，所以不愿在正式公式中采用。 自認(rèn)為這兩個公式已經(jīng)考慮了許多的細(xì)節(jié)，但我也不敢保證沒有掛一漏萬。現(xiàn)在暫時告一段落，若以后發(fā)現(xiàn)問題再來修改了。 |
|站長郵箱|小黑屋|手機(jī)版|Office中國/Access中國
( 粵ICP備10043721號-1 )
GMT+8, 2025-7-13 08:03 , Processed in 0.068815 second(s), 16 queries .
Powered by Discuz! X3.3
© 2001-2017 Comsenz Inc.
