iT邦幫忙

第 12 屆 iT 邦幫忙鐵人賽

DAY 4
1
自我挑戰組

白話文Excel及不好說系列 第 4

OK集#4白話文Excel-從黃金理財報表開始-取得數值最小值及sum的複合運用(這次玩真的)

今天~~
我一定要認真的把右半邊(下圖綠框處)的公式教完(握拳)

在這一集裡,你將學到:

  • 最小值如何取得
  • sum的複合運算方法
  • 下圖為今日教學之範圍
    https://ithelp.ithome.com.tw/upload/images/20200902/20130000xqbX4xzIia.png

在前幾次的課程裡,我們已經有請大家先建立好黃金理財的表格,並填入相關購買、售出的相關資訊及如何用驗證清單的方式快速的輸入資料。

而這一集呢!就要把表格右半邊未完成的部份完成起來,完成後,你們就可以開始用這份資料表,展開你們的黃金投資之路啦!

【取得歷次購買記錄中的黃金購買最低價(G2格)】
1.請先將儲存格選取(focus)到G2的儲存格。
https://ithelp.ithome.com.tw/upload/images/20200902/20130000yIsKvUmPSn.png
2.接著輸入「MIN(G3:G5)」就能取得G3到G5之間的最小值,同理可證,如果你有新的記錄產生時,你就要將G5改成G6(本範例是先設到G16),此時你一定在想,這樣好麻煩喔!那沒關係,你只要把G5設成一個比較大的數值,例如:G100,這樣你在輸入到第G100列之前,你都不用改公式,公式結果就會自動產生。

3.聰明如你,一定在想說那我可以用G:G(這是指要處理的範圍為G欄整欄)嗎?答案是:不行,原因是我公式也是下在G欄裡,是G欄的一份子,對Excel來說公式的世界怎麼可以包含答案自己呢?所以如果你的公式放的位置是在其它欄位,不影響整體公式運算的話,就可以那樣子下,沒問題。

4.前面說了這麼多,那我來稍微介紹一下Min這個公式吧!下面二個圖是Excel顯示給你看的提示文字(看得懂的請舉手)
(1)下圖是Min這個函數在Excel世界的說明,我換用白話文來說一下,他的意思是「在你選取的資料範圍裡,把最小值的資料吐出來給你,然後內容如果是文字或是有邏輯相關的判斷或公式會跳過不去計算」
https://ithelp.ithome.com.tw/upload/images/20200902/20130000KhcTbdMios.png

(2)好!那我們接著來看看下圖Excel對公式的提示,嘿!是不是看不懂什麼叫number1,number2...我再換成白話文說明一下,在Excel公式的世界裡,如果是不連續的資料會用「,」區隔,就像是min(g3:g5),我也可以下成min(g3,g4,g5),但如果有幾百個儲存格要算,我這樣一路的逗(鬥?噎?)下去,會很想死吧!所以才會有當儲存格是在連續的狀況下,可以用「:」省略中間的逗逗逗變成min(g3:g5)。
https://ithelp.ithome.com.tw/upload/images/20200902/201300000iFWpMG2us.png

5.另外,不知道有沒有眼尖的孩子發現,在我的成品範例裡,最小值的顯示是「最低:1454」(如果有發現,請自己為自己拍拍手),現在就來教教這要如何達成

這個其實很簡單,你就是先把公式下好,確定數值計算是正確沒有問題,那就直接在公式前面加上「="最低:"&公式」就好

其中的「""」是用來跟Excel說在雙引號的中間是要顯示的文字喔!,並且一定要記得在文字和公式之間加上一個「&」連接符號,這樣Excel才會知道說「喔!我要先顯示文字最低:以及公式,這樣才能得到期望中所想要的結果。

【計算本次購買合計(H,I欄)】
1.合計值的計算非常的簡單,就是有一欄專門計算回售的合計,另一欄專門計算買入的合計(本範例即指H及I欄),這樣當你不論是支出(賣出)或存入,一定輸入完基本資訊,他們就會自動計算結果了
https://ithelp.ithome.com.tw/upload/images/20200902/20130000QkCGuv7nmo.png
2.公式的下法就是:
(1)當次支出X當次購買金額(應該是交易金額)=回售合計
(2)當次存入X當次購買金額(應該是交易金額)=買入合計

3.公式下好後,再複製到下面的儲存格裡就完成設定了

【計算當下剩餘成本(J欄總成本欄)】
1.當下成本的計算公式為:之前購買的黃金累積成本-回售+買入,這樣就能知道我這次在買賣之後,還剩下的錢有多少
https://ithelp.ithome.com.tw/upload/images/20200902/20130000rXn38iZkDR.png
2.請先在J3的儲存格輸入:I3-H3,因為這是第一次購買,所以並沒有之前的購買累積可以加

3.但在J4的儲存格時,他的公式就必須要輸入為:J3-I4+H4,因為已有先前的購買記錄了,所以J3的值也需要被計算到

4.接著,把J4的公式複製到其下的儲存格裡即可

【計算平均單價(K欄)】
1.此欄主要是為了計算「累計的成本 除以 累積的黃金克數」,其平均的價值,這樣才能知道在每一次追低或追高的狀況下,是賺還是賠以及賺賠的大小
https://ithelp.ithome.com.tw/upload/images/20200902/20130000fGYXkYGVwn.png
2.其公式為:當次總成本/當次結存

3.請在K3儲存格輸入:J3/F3,輸入完成後,再將公式複製到後面的欄位即可

4.以本次範例來說,我前面買了10克,總共花了台幣14,635元(平均單價為1464元),但在4/1時,我以1800元/g賣了5克,獲得9000元台幣,扣除之前的平均購買成本1464x5=7320,我等於賺了1680元,約賺23%,假若一次10克全部贖回將淨賺3365元。

補充說明:當你全部賣光時,公式會出現奇怪的狀況,例如出現了:###或#DIV/0!,不要怕,反正你都賣光了,下次再從頭開始就好(喂~~~)

【計算現值(L欄)】
1.此欄主要是在計算,假若現值(目前的牌價)為1800元/克,如果賣掉會賺多少錢(這個錢是總數喔!不是一克賺的錢喔!)
https://ithelp.ithome.com.tw/upload/images/20200902/20130000bc7GMPDkCi.png
2.公式:=($L$2*F3)-K3*F3,就等於(現值1800x目前結存的克數)-現在的平均單價x結存克數
※需要注意的是因為現值1800是固定欄位,一定要用「$」把欄和列都鎖定住,這樣公式才不會跑掉喔!
3.接著再將公式複製到下方儲存格即可

【計算利潤(M欄)】
1.利潤的計算,我這裡的公式是=($L$2*F3)/J3-1,也就是把現值x目前結存的克數/當下的總成本,因為其數值有賺就會超過100%,因此最後要再減1,就會是在目前金價現值的狀況下,所賺得的利潤數,但此公式,在賣出時數據的邏輯會有錯
https://ithelp.ithome.com.tw/upload/images/20200902/201300003ZerfS4wGL.png
2.我這裡還有另一種算法,但這算法的邏輯就會比較複雜一些,公式是=((K3+(L3/F3))/K3)-1,也就是當時的平均單價+(目前賺到的錢/當下的結存克數))相加完後再除當下的平均單價,最後再減1,即可求得利潤,而這段複雜的公式,其實就是把目前賺的錢除以當下的總克數,這樣可以求得平均的利潤值,簡單來說就是成本+利潤再除以成本的概念,這樣算出來的就是利潤

【計算總g數(I1格)】
1.我這裡公式的算法是,用總存入-總支出,公式即設為=SUM(E:E)-SUM(D:D),一般來說我們sum大多是單獨使用的,而這裡只是示範sum也是可以使用在一般的運算上
https://ithelp.ithome.com.tw/upload/images/20200902/20130000RxrvzM9sVv.png

呼~~~黃金理財報表的分享,分享完了~~~下一單元先讓我賣個關子,我們下集再見,See you~~


上一篇
OK集#3白話文Excel-從黃金理財報表開始(前情提要)黃金投資邏輯
下一篇
OK集#5白話文Excel-如何運用Excel快速查詢網頁內容
系列文
白話文Excel及不好說30

尚未有邦友留言

立即登入留言