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列。

    如果您想收到更多理財訊息,您可以按此:
    精選推薦
    課程好學 永泓技術分析進階專研班 朱家泓大弟子在 Smart 獨家開班囉!承襲朱式技術分析之精華,永泓要教你觀察K線多空出擊,用紀律戰法從股市淘金用專研走圖讓你實
    課程好學 ETF簡單投資法-用一個指標獲利12.78% 想市場投資卻不懂財務選股? 想用簡單的判斷方式進出市場?學股魚這招,免選股、免盯盤,小散戶一樣可以輕鬆獲利
    課程好學 富媽媽精明理財養成術 想存子女教育費?想給自己跟孩子基本保障? 想將日常省吃儉用的小錢,用最有效率的投資方式,積攢成大財富? 效率理財王要教你活用「存
    課程好學 XQ全球贏家進階班 ‧XQ軟體自設畫面、自訂工具列與報價欄位設定。 ‧技術分析指標套用、區間設定、畫面切割設定、以及主圖疊圖/副圖/畫線工具,興櫃K
    特別企劃
    達人帶路,保單不再霧煞煞! 達人帶路,保單不再霧煞煞!...
    不懼市場變動 資產穩健成長 不懼市場變動 資產穩健成長...