財(cái)務(wù)人員一定離不開(kāi)各種金額的輸入和轉(zhuǎn)換。有時(shí)一個(gè)數(shù)據(jù)在同一表格中會(huì)反復(fù)的調(diào)用，通過(guò)WPS函數(shù)可以使輸入更簡(jiǎn)化。

　　例如下列發(fā)票，假設(shè)不通過(guò)函數(shù)計(jì)算，需有輸入四次。不僅工作效率極差，出錯(cuò)的機(jī)率也會(huì)大大提高。

　　圖一面發(fā)票

　　解決思路：數(shù)量和單價(jià)是變量，需要手動(dòng)錄入，其它三處利用函數(shù)直計(jì)算總價(jià)，再將總價(jià)轉(zhuǎn)換成需要的格式。

　　步驟一：“金額”公式

　　1.選中單元格G4輸入公式，然后右拉至Q4; 選中G4：G4下拉填充至G9：Q9。

　　=LEFT(RIGHT(TEXT($E4*$F4/1%," ￥#;;"),COLUMNS(A1:$K1)))

　　2.公式解析：

　　首先將E4和F4單元格相乘。為了便于公式填充，將單元格地址設(shè)置為絕對(duì)列、相對(duì)行形式，使公式右拉下拉填充時(shí)可以適應(yīng)引用單元的變化;

　　然后將之除了1%，即擴(kuò)大100倍，作用是消除小數(shù)點(diǎn)。如“1.01/1%”即變?yōu)?01，方便的面的公式計(jì)算;

　　再用TEXT函數(shù)來(lái)剔除數(shù)量與單價(jià)為0之狀況。我們知道，單元格格式定義分為四段：[正數(shù)、負(fù)數(shù)、0、文本]，本例中TEXT的第二參數(shù)為" ￥#;;"，直接用公號(hào)分割開(kāi)留下空白即表示忽略負(fù)數(shù)和0。當(dāng)單價(jià)為0或者負(fù)數(shù)時(shí)公式結(jié)果返回空白;

　　再后用right函數(shù)提取字符。right是提取字符串右邊N個(gè)字符的函數(shù),用在此處因第二參數(shù)COLUMNS(A1:$K1)是動(dòng)態(tài)的，當(dāng)公式向右拉動(dòng)時(shí)COLUMNS(A1:$K1)的結(jié)果將從11遞減至1，那么提取的字符串也將進(jìn)行遞減;

　　最后用left函數(shù)提取最終結(jié)果。Left函數(shù)與right函數(shù)的作用相反--從字符串左邊開(kāi)始提取字符，其第二參數(shù)為可選。本例中為減短公式長(zhǎng)度，省略了第二參數(shù)，即表示提取長(zhǎng)度為默認(rèn)值1。在text函數(shù)的參數(shù)中第二參數(shù)為" ￥#;;"，重點(diǎn)在于前面的空格。此處用left函數(shù)提取字符時(shí)，若E4和F4乘積之字符長(zhǎng)度小于當(dāng)前單位的位數(shù)加1(指億萬(wàn)百等等的位數(shù))時(shí)，left只能提取到空格，若等于當(dāng)前單位的位數(shù)加1時(shí)則返回人民幣符號(hào)“￥”，然后才逐位返回乘積結(jié)果中的單字符，這正是需要結(jié)果。

　　3.本公式三個(gè)重點(diǎn)：A)TEXT函數(shù)的第二參數(shù)前面的空格的妙用;B)第二參數(shù)中兩個(gè)“;”的妙用;C)將right與left套用使公式逐位提取數(shù)字。

　　步驟二：“合計(jì)”公式

　　1.選中單元格M10輸入數(shù)組公式“=TEXT(SUM(E4:E9*F4:F9),"0.00")”，并同時(shí)按CTRL+SHIFT+ENTER三鍵結(jié)束，否則公式對(duì)果將出錯(cuò)。輸入完后若公式前后系統(tǒng)自動(dòng)加上花括號(hào)即表示輸入正錯(cuò)，見(jiàn)圖二所示。<

　　圖二 數(shù)組公式

　　2公式解析：將E4:E9區(qū)域的值分別乘以F4:F9的值，然后再相加即得到總計(jì)，公式會(huì)自動(dòng)忽略空白區(qū)。本例若不用數(shù)組公式則公式要長(zhǎng)很多，效率更差。如：=SUM(E4*F4+E5*F5+E6*F6+E7*F7+E8*F8+E9*F9)。數(shù)組運(yùn)算長(zhǎng)處是縮短公式，提高效率，可以將常規(guī)公式中的多步運(yùn)算合并得以簡(jiǎn)化。最后用text函數(shù)將數(shù)組結(jié)果格式化為精確到小數(shù)點(diǎn)兩位。

　　3.選擇單元格M10:Q10,單擊菜單上的“跨列合并”，使單元格的結(jié)果居于區(qū)域正中。

　　此處用“跨列居中”而不用“合并居中”是因數(shù)組公式不能存在于多單元格合并后的單元格中。故使用區(qū)域跨列合并，顯示效果和“合并及居中”一樣，但因未對(duì)單元格進(jìn)行合并，則與數(shù)組公式可以和平相處。

　　圖三 跨列合并單元格

　　步驟三：“大寫(xiě)”公式

　　1.選擇單元格C10,C10單元格可以先合并居中，然后輸入公式：

　　=TEXT(M10,"[DBNum2][$RMB]G/通用格式")

　　2.公式解析：本公式通過(guò)text函數(shù)改變引用源M10單元格數(shù)字的顯示方式，使之以人民幣大寫(xiě)方示。其中[DBNum2]修改為[DBNum1]以及“G/通用格式”修改為“0”有不同的效果，讀者可以自己試試。

　圖四 人民幣大寫(xiě)

　　總結(jié)：

　　1.Text函數(shù)是一個(gè)格式化函數(shù)，它可以改變引用源的顯示方式。第一參數(shù)是引用源，第二參數(shù)即顯示方式。這是一處運(yùn)用很廣的函數(shù)。本例中三個(gè)公式都已用到它。

　　2.工作表表要善用公式，簡(jiǎn)化數(shù)字的重復(fù)錄入，同時(shí)也提升準(zhǔn)確性。

最新評(píng)論