Smart自學網
  • Smart自學網

    出版品:叢書

    人人都能學會活用Excel做投資全圖解

    學會8大實用函數 複雜運算也可1秒解決
    出版品:叢書 > 精采試讀
    訂閱雜誌電子雜誌

    學會8大實用函數 複雜運算也可1秒解決

    撰文者:《Smart 智富》真‧投資研究室2018-03-28
    如果您喜歡這篇文章,請幫我們按個讚:

    3.SUBTOTAL函數:有條件計算
    在某些情況中,製表人會把暫時用不到的數據隱藏起來,但隱藏起來的數值不論用「SUM」或「AVERAGE」函數都還是會計算進去,該怎麼辦呢?很簡單,用「SUBTOTAL」函數就可以了。

    如果想要加總各儲存格並忽略隱藏項目,公式為「=SUBTOTAL(109,起始儲存格:結束儲存格)」;若想計算平均數,公式為「=SUBTOTAL(101,起始儲存格:結束儲存格)」。這裡的「109」、「101」是函數的引數,為固定數字。

    以前面平均數的範例為例,如果現在只想取2017年1月1日至1月9日的平均數,因此把1月10日的資料列隱藏起來,如果還是用「AVERAGE」函數,平均數仍會維持在「1,134」,但如果①儲存格C13改為「=SUBTOTAL(101,C3:C12)」,儲存格範圍一樣是C3~C12,但可以看到平均數已更新為1~9日平均「1,118」(詳見下圖)。

    索引函數
    1.IF函數:假設條件成立與否的回傳值
    「IF」函數能設定1個假設條件及2個回傳值:1.當假設條件成立的回傳值;2.當假設條件不成立的回傳值。意思就是說,如果是希望在某條件下才呈現某些數值,就可以用此函數解決,因此「IF」函數的用途也非常廣泛。「IF」函數的公式組成為「=IF(假設,假設成立回傳值,假設不成立回傳值)」。

    以下圖為例,假設管理預算的人,同時拿到會計部門給的支出明細和門市給的支出明細,想知道兩者數字是否有出入,只要多增加一欄「核對」項目,並於①儲存格E3中輸入「=IF(C3=D3,"","有誤")」,按下「確定」,並使用填滿控點的功能複製到最後一列,就知道哪一天的資料有問題囉!

    此外,「IF」函數還可以搭配「SUM」函數使用,變成「SUMIF」函數。意思就是,當條件成立時,才加總該項目。以下圖中為例,假設支出用途分為3大類,分別為客戶拜訪、產品說明會和廣宣列印,如果想在儲存格F欄統計一段時間以來3大類的支出金額,以「客戶拜訪」為例,就可以在①儲存格F3輸入「=SUMIF(B3:B11,E3,C3:C11)」,整個函數的意思就是:比對儲存格B3~B11中的所有內容,只要同為儲存格E3的「客戶拜訪」,就加總同列中C欄位的數值。

    2.MATCH函數:依據搜尋條件回傳位置
    「MATCH」函數可以幫助我們找到範圍中,符合搜尋條件的儲存格在範圍中的位置順序,公式為「=MATCH(搜尋條件,搜尋範圍,搜尋類型)」。而在搜尋類型有以下3類:

    第1類:尋找小於或等於搜尋條件的最大值→輸入「1」或省略。
    第2類:尋找完全等於搜尋條件的第1個值→輸入「0」。
    第3類:尋找大於或等於搜尋條件的最小值→輸入「-1」。

    舉例來說,下圖中如果我們想在①儲存格E3中呈現座號1號在表格中的第幾列,就可以輸入公式「=MATCH(1,C3:C8)」,此處的「1」代表我們要找的是1號、「C3:C8」是搜尋範圍,也就是我們想知道1號在C3~C8這個範圍中的第幾列,按下「Enter」鍵後,就可以看到回傳數值為「1」,代表1號在搜尋範圍的第1列。

    如果您想收到更多理財訊息,您可以按此: 訂閱電子報
    精選推薦
    課程好學 K線捕手36種制霸台股的K線攻略 集結投資人經常誤用的36種情況,優化技術分析實力!● 騙線、指標、缺口、均線、型態、時間週期逐一解析 ● 籌碼的一體兩面,其實誤
    課程好學 楊忠憲盤中實戰觀測台 想知道每年股市常勝軍的楊忠憲老師一天都在做些甚麼?盤中即時進出場點 偵測飆股基因 價量關係戰技 籌碼分析解讀10秒鐘盤中交易術
    出版品 上流哥:這年頭存錢比投資更重要 只要錢存得夠多報酬率低一點也能賺大錢臉書「上流哥投資理財粉絲團」版主上流哥還在職時只花2年時間就當上基金經理人,而他操盤的公募基
    出版品 躺著賺1年400萬的肥羊養股術 股市肥羊第2彈!躺著也能爽爽賺!在股市連賺20年的「股市肥羊」翁建原又來了,這次他將自創的肥羊派波浪理論再度精簡,讓投資人能夠更
    特別企劃
    達人帶路,保單不再霧煞煞! 達人帶路,保單不再霧煞煞!...
    不懼市場變動 資產穩健成長 不懼市場變動 資產穩健成長...