萬盛學電腦網

 萬盛學電腦網 >> Wps Office >> wps綜合教程 >> WPS表格函數在發票金額錄入中的妙用

WPS表格函數在發票金額錄入中的妙用

  財務人員一定離不開各種金額的輸入和轉換。有時一個數據在同一表格中會反復的調用,通過WPS函數可以使輸入更簡化。

  例如下列發票,假設不通過函數計算,需有輸入四次。不僅工作效率極差,出錯的機率也會大大提高。

WPS表格函數在發票金額錄入中的妙用 三聯

  圖一面發票

  解決思路:數量和單價是變量,需要手動錄入,其它三處利用函數直計算總價,再將總價轉換成需要的格式。

  步驟一:“金額”公式

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

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

  2.公式解析:

  首先將E4和F4單元格相乘。為了便於公式填充,將單元格地址設置為絕對列、相對行形式,使公式右拉下拉填充時可以適應引用單元的變化;

  然後將之除了1%,即擴大100倍,作用是消除小數點。如“1.01/1%”即變為101,方便的面的公式計算;

  再用TEXT函數來剔除數量與單價為0之狀況。我們知道,單元格格式定義分為四段:[正數、負數、0、文本],本例中TEXT的第二參數為" ¥#;;",直接用公號分割開留下空白即表示忽略負數和0。當單價為0或者負數時公式結果返回空白;

  再後用right函數提取字符。right是提取字符串右邊N個字符的函數,用在此處因第二參數COLUMNS(A1:$K1)是動態的,當公式向右拉動時COLUMNS(A1:$K1)的結果將從11遞減至1,那麼提取的字符串也將進行遞減;

  最後用left函數提取最終結果。Left函數與right函數的作用相反--從字符串左邊開始提取字符,其第二參數為可選。本例中為減短公式長度,省略了第二參數,即表示提取長度為默認值1。在text函數的參數中第二參數為" ¥#;;",重點在於前面的空格。此處用left函數提取字符時,若E4和F4乘積之字符長度小於當前單位的位數加1(指億萬百等等的位數)時,left只能提取到空格,若等於當前單位的位數加1時則返回人民幣符號“¥”,然後才逐位返回乘積結果中的單字符,這正是需要結果。

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

  步驟二:“合計”公式

  1.選中單元格M10輸入數組公式“=TEXT(SUM(E4:E9*F4:F9),"0.00")”,並同時按CTRL+SHIFT+ENTER三鍵結束,否則公式對果將出錯。輸入完後若公式前後系統自動加上花括號即表示輸入正錯,見圖二所示。<

  圖二 數組公式

  2公式解析:將E4:E9區域的值分別乘以F4:F9的值,然後再相加即得到總計,公式會自動忽略空白區。本例若不用數組公式則公式要長很多,效率更差。如:=SUM(E4*F4+E5*F5+E6*F6+E7*F7+E8*F8+E9*F9)。數組運算長處是縮短公式,提高效率,可以將常規公式中的多步運算合並得以簡化。最後用text函數將數組結果格式化為精確到小數點兩位。

  3.選擇單元格M10:Q10,單擊菜單上的“跨列合並”,使單元格的結果居於區域正中。

  此處用“跨列居中”而不用“合並居中”是因數組公式不能存在於多單元格合並後的單元格中。故使用區域跨列合並,顯示效果和“合並及居中”一樣,但因未對單元格進行合並,則與數組公式可以和平相處。

  圖三 跨列合並單元格

  步驟三:“大寫”公式

  1.選擇單元格C10,C10單元格可以先合並居中,然後輸入公式:

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

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

  電腦教程圖四 人民幣大寫

  總結:

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

  2.工作表表要善用公式,簡化數字的重復錄入,同時也提升准確性。

copyright © 萬盛學電腦網 all rights reserved