報銷單是工作中很常見的一種表單,為了我們工作時候更加方便,我們會在Excel中製作一個電子版的報銷單! 格式如下!的可以在公眾號後臺
看起來很方便,但是有一個問題,最下面的合計金額(標黃部分)如果能根據每一行的金額自動計算就完美了。
如何設置這裡的公式,就是今天要和各位分享的內容了。Excel中這3個神奇的公式,輕鬆構成自動計算結果的報銷單模板!
先看看完成後的效果吧:
隨著金額的錄入,下面的合計會自動更新。
要實現這個效果,需要解決三個問題:
1、對分列顯示的數據求和;
2、將求和後的數字分列顯示;
3、將分列顯示的數字轉為大寫金額。
下面分別來看如何實現。
對分列顯示的數據求和
要對分列顯示的數據求和,公式並不是唯一的,例子中用的公式是:
=SUM(IFERROR(F5:M14*10^{7,6,5,4,3,2,1,0},0))%
在這個公式中,用到了SUM函數(求和)和IFERROR函數(排除錯誤),還有一個常量數組
{7,6,5,4,3,2,1,0}。
公式的原理很簡單,因為表格中最多有8列數字,也就是數據區域F5:M14,每一列的數字乘以10的n次方,n隨列數的增加對應減少,因為左邊的是高位,右邊是低位。
10^{7,6,5,4,3,2,1,0}就是這個作用
遇到錯誤值直接變成0,這樣就會得到一組數字,後面來個%表示將數字縮小一百倍,因為角和分是小數部分。
最後用SUM函數求和就得到所需結果。
對於這部分如果不理解,有兩個辦法:直接套用公式,或者從基礎開始學一下公式。
將求和後的數字分列顯示
求和結果有了,接下來解決第二個問題,如何把求和後的數字分列顯示。
還是給出一個現成的公式吧:
=LEFT(RIGHT(" ¥"&$O$5*100,9-COLUMN(A1)))
這個公式同樣不是唯一的方法,需要重點說明的是,在人民幣符號前面是有個空格的,單就這個公式而言,涉及的函數都很基礎,LEFT從左向右提取,RIGHT從右向左提取,COLUMN得到列號。
一些很簡單的函數組合起來就能得到挺不錯的效果,關於這個公式的原理,解釋起來還比較費字,還是那句話,直接套用或者用F9自己去拆解,這是學習公式的一個方法。
數字轉為大寫金額
接下來是最後一步,得到大寫金額。
這個公式的套路就更多了,百度一下就有很多,本例使用的是程大管家提供的一個思路,比較簡潔明瞭:
="合計人民幣(大寫):"&TEXT(INT(O5),"[DBNum2]")&"元"&TEXT(MOD(O5,1)*100,"[DBNum2]0角0分;;整")
公式分為三部分:前面的文字算是固定開頭,第一個TEXT的作用是將整數部分變成大寫數字,第二個TEXT的作用是將小數部分轉為大寫數字並以角和分顯示。
具體含義的話需要對TEXT函數有比較全面的瞭解才行了
End.
牛賬網,為您提供會計實操乾貨與考證經驗分享、2000+們免費教學視頻及題庫等,讓您所學及所用。
免責聲明:本文內容僅供閱讀者參考,具體以相應法規及當地行政機關判定結果為準。引用或轉載,請註明以上信息。
閱讀更多 牛賬網 的文章