EXCEL文本函數(shù)-數(shù)字小寫換大寫/英文大小寫互換

昵稱3977014 2010-10-16

展開全文

在大小寫中英文互換公式中我們先假設每個表都有表頭，且數(shù)字在第二行，在A2單元格內

注意：公式可以隨意拖動至任意指定單元格，公式內對象不變；可復制（待鼠標變成黑色十字再拖)；也可利用查找-替換-全部替換單元格如A2---全部換成F15

EXCEL中數(shù)字小寫換大寫方法一：
在單元格A2中輸入小寫數(shù)字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(FIND(".0",TEXT(A2,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A2,2),3))=".",TEXT(RIGHT(ROUND(A2,2)),"[DBNum2]")&"分",IF(ROUND(A2,2)=0,"","整")),"零元零",""),"零元","")

EXCEL中數(shù)字小寫換大寫方法二：

在單元格A2中輸入小寫數(shù)字123.12
B2處輸入以下公式

=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/通用格式")&"分"))

EXCEL中數(shù)字小寫換大寫方法三：

=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/通用格式")&"分")

EXCEL中數(shù)字小寫換大寫方法四：

=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]")&"分"))

看看結果是不是出現(xiàn)了你想要的?應該有你需要的大寫金額了

公式的由來:

仟分位公式如下:
=TEXT(IF(LEN(INT($A1))>=4,MID($A1,LEN(INT($A1))-3,1),""),"[DBNum2]")
佰分位公式如下:
=TEXT(IF(LEN(INT($A1))>=3,MID($A1,LEN(INT($A1))-2,1),""),"[DBNum2]")
第一位小數(shù)的公式:
=TEXT(MID($A1,FIND(".",$A1)+1,1),"[DBNum2]")
第二位小數(shù)的公式:
=TEXT(MID(TEXT($A1,"0.00"),FIND(".",$A1)+2,1),"[DBNum2]") 其他依此類推^^^^^^^^^^^^^^^^

用Excel函數(shù)將貨幣數(shù)值由阿拉伯數(shù)字自動生成為中文大寫格式

如圖是全部用Excel函數(shù)自編農業(yè)銀行轉帳支票套打工具，圖中內容是需要打印到轉帳支票上內容，其中A-D列為支票存根部分，我們利用Excel函數(shù)對要填寫大部分內容建立公式后，只要輸入支票存根部分的三項基本信息（A6-A8單元格）：收款單位、金額、用途，其他內容全部自動生成。

圖中輸入阿拉伯數(shù)字格式金額的是支票存根中A7單元格，要自動生成中文大寫金額的是H3單元格，我們按照支票中的金額中文大寫格式的特殊的要求，利用Excel函數(shù)對H3進行公式設置。

在H3單元格輸入：=IF((A7-INT(A7))=0,TEXT(A7,"[DBNUM2]")&"元整",IF(INT(A7*10)-A7*10=0,TEXT(INT(A7),"[DBNUM2]")&"元"&TEXT((INT(A7*10)-INT(A7)*10),"[DBNUM2]")&"角整",TEXT(INT(A7),"[DBNUM2]")&"元"&IF(INT(A7*10)-INT(A7)*10=0,"零",TEXT(INT(A7*10)-INT(A7)*10,"[DBNUM2]")&"角")&TEXT(RIGHT(A7,1),"[DBNUM2]")&"分"))。

公式中共運用4種函數(shù)，文本連接符號“&”及相應的漢字。

IF函數(shù)：是一個條件判斷函數(shù)，此函數(shù)的基本格式是：IF（a，b，c）其中“a”是一個條件，“b”和“c”是兩種判斷結果，可以表述為：如果條件a成立，那么結果是b，否則結果就是c。以上公式是有四層嵌套條件判斷函數(shù)，根據(jù)貨幣金額的不同情況生成不同格式的中文大寫。

TEXT函數(shù)：TEXT函數(shù)可以將阿拉伯格式數(shù)字轉換為中文大寫格式，如公式中“TEXT(A7,"[DBNUM2]”結果是將單元格A7的阿拉伯數(shù)字轉換為中文大寫。

INT函數(shù)：這是一個取整函數(shù)，如公式中“INT(A7)”就是取單元格A7數(shù)字整數(shù)部分。A7-INT(A7)表示A7為整數(shù)，INT(A7*10)-A7*10=0表示A7有一位小數(shù)。

RIGHT函數(shù)：用該函數(shù)可以提取字符串右邊的若干個字符，如公式中的“RIGHT(A7,1)”表示從A7單元格數(shù)字右起提取一個字符。

從整體來講，這是一個多層嵌套條件函數(shù)公式，共有4層嵌套來完成條件判斷。

可以將整個條件函數(shù)公式表述為：

如果單元格A7中數(shù)字為整數(shù)（假如為35566），那么人民幣大寫就是“叁萬伍仟伍佰陸拾陸元整”，否則：

如果單元格A7中數(shù)字為含有一位小數(shù)數(shù)字（假如位35566.2），那么人民幣大寫就是“叁萬伍仟伍佰陸拾陸元貳角整”，否則：

如果單元格A7中數(shù)字為兩位小數(shù)數(shù)字，且十分位為0（假如為35566.08），那么人民幣大寫就是“叁萬伍仟伍佰陸拾陸元零捌分”。否則：

如果單元格A7中數(shù)字為兩位數(shù)小數(shù)，且十分位不為0（假如為35566.28），那么人民幣大寫就是“叁萬伍仟伍佰陸拾陸元貳角捌分”。

Excel工作表中創(chuàng)建當前日期及中文大寫

Excel數(shù)據(jù)處理軟件以其強大的數(shù)據(jù)處理功能和操作的簡易性，成為辦公自動化的必備軟件，尤其在財務工作中更顯重要，成為會計人員在專門的電算化財務軟件之外會計數(shù)據(jù)處理重要輔助工具。在用Excel編制表格時，制表時間是Excel工作表中不可缺少的內容，對有些需經常打開使用的工作表，如月報表，至少每月打開一次，對其中報表時間每次都要由原來日期修改成當前日期，這樣不但很麻煩，而且容易因日期漏改而造成日期差錯，如果在工作表中利用Excel函數(shù)創(chuàng)建當前日期，并且任何時候打開這個工作表，永遠顯示當前日期，就會一勞永逸。下面分別介紹當前日期的大小寫兩種格式創(chuàng)建方法。

一、在工作表中創(chuàng)建阿拉伯數(shù)字當前日期

在一般情況下，在工作表中創(chuàng)建當前日期，用一個函數(shù)即可完成，在需要建立當前日期的單元格輸入“=TODAY()”（不含引號，下同），任何時候打開工作表，該單元格顯示的永遠是當前日期，如果當前日期是2003年3月18日，則工作表中顯示“2003年3月18日”。

在特殊情況下，要求某個單元格只顯示當前“年”、“月”或“日”，如圖是全部用Excel函數(shù)自編支票套打工具，支票中出票日期年、月、日的位置已經定好，要填寫的數(shù)字之間有一定的距離，要分開填寫，要利用Excel函數(shù)在工作表不同單元格分別創(chuàng)建公式（圖中A5-C5單元格），我們要實現(xiàn)支票套打，日期的一般格式顯然不行，需分別建立當前的年、月、日。在表示日期的A5-C5單元格創(chuàng)建如下公式：

在表示“年”A5單元格輸入：“=YEAR(TODAY())”，假如當前日期是2003年5月6日，則該單元格顯示“2003”。

在表示“月”B5單元格輸入：“=MONTH(TODAY())”，則該單元格顯示的是“5”。

在表示“日”C5單元格輸入：“=DAY(TODAY())”，則該單元格顯示的是“6”。

創(chuàng)建了以上公式，任何時間打開工作表，在這三個單元格分別顯示的永遠是當前的年、月、日。

二、將工作表中當前日期阿拉伯數(shù)字轉換為中文大寫

圖中I1-K1單元格分別表示的是日期的中文大寫，同樣根據(jù)支票規(guī)定的年、月、日位置填寫，因為在支票存根中已建立了出票日期信息，根據(jù)它來創(chuàng)建中文大寫日期。

根據(jù)我國《票據(jù)法》有關規(guī)定，票據(jù)的出票日期必須使用中文大寫。為防止變造票據(jù)的出票日期，在填寫月、日時，月為壹至壹拾的，日為壹至玖和壹拾、貳拾和叁拾的，應在其前加“零”。根據(jù)這些特殊日期填寫要求，在表中表示年月日的I1-K1單元格分別設置公式如下：

日期中“年”中文大寫的公式設置

在表示“年”的I1單元格輸入：=TEXT(MID(A5,1,1),"[DBNUM2]")&TEXT(MID(A5,2,1),"[DBNUM2]")&TEXT(MID(A5,3,1),"[DBNUM2]")&TEXT(MID(A5,4,1),"[DBNUM2]")。MID函數(shù)是提取字符函數(shù)，公式中“MID(A5,1,1)”，表示從A5單元格數(shù)字左第一個字符起提取一個字符，結果是“2”。TEXT函數(shù)將阿拉伯數(shù)字轉換為中文大寫，公式中“TEXT(MID(A5,1,1),"[DBNUM2]")”表示將“2”轉換為“貳”。整個公式由四個文本連接符號“&”連接而成，結果是“貳零零叁”。我們不能直接利用TEXT函數(shù)將“2003”轉換為中文大寫，因為“TEXT(2003,"[DBNUM2]")”的結果是“貳仟零叁”，所以需將“2003”逐個數(shù)字轉換為中文大寫。

日期中“月”中文大寫的公式設置

在表示“月”的J1單元格輸入：=IF(B5<11,"零"&TEXT(B5,"[DBNUM2]"),TEXT(B5,"[DBNUM2]"))。IF函數(shù)是一個條件判斷函數(shù)，整個公式可以表述為：如果B5單元格月份數(shù)為1-10之間數(shù)字，則將該數(shù)字轉換為中文大寫的同時，在前面加“零”，如當前是5月，則結果是“零伍”，否則直接將B5中月份數(shù)字轉換為中文大寫，如當前是11月，則結果是“壹拾壹”。

日期中“日”中文大寫公式設置

在表示“日”的K1單元格輸入：=IF(OR(C5<11,C5=20,C5=30),"零"&TEXT(C5,"[DBNUM2]"),TEXT(C5,"[DBNUM2]"))。公式中“OR(C5<11,C5=20,C5=30)”表示“C5或者小于11，或者等于20，或者等于30”。整個公式可以表述為：如果C5或者小于11，或者等于20，或者等于30，那么將C5中數(shù)字轉換為中文大寫的同時，在前面加“零”，否則直接將C5轉換為中文大寫數(shù)字。