Excel成本計算：篩選平均值及Subtotal條件計算平均數 - 贊贊小屋

Excel進階函數陣列公式 Excel成本計算：篩選平均值及Subtotal條件計算平均數 2015-09-12 Excel函數 1,007次瀏覽 目錄 一、產品成本明細二、資料排序與篩選三、篩選特定月份四、篩選金額不變五、SUBTOTAL函數六、SUBTOTAL功能七、選擇性平均值八、AVERAGEIF函數九、AVERAGE陣列Excel進階函數陣列公式 Excel成本分析可能希望依照選擇月份計算平均成本，但並沒有專門性指令可使用，本文介紹如何綜合應用篩選、SUM函數、SUBTOTAL函數、AVERAGEIF函數、AVERAGE陣列公式實現。

會計實務上，除了依照特定條件取合計數，在作長期趨勢分析、或者統計編製預算時，常常會用到一段期間的平均值作為參考，所以可能是依照特定條件取平均值，以下就平均成本作具體介紹： 一、產品成本明細 月份別銷貨成本表，三個月份兩種產品的料工費，左邊有一欄總成本：「=SUM(C2:E2)」，下面有一列類似的小計金額。

二、資料排序與篩選 像這樣標準的會計報表，第一個想到的是「篩選」：上方功能區移到「資料」標籤，「排序與篩選」區塊，然後就是「篩選」。

三、篩選特定月份 篩選一月份，拉下「月份」清單，保留勾選「01」和「合計」。

四、篩選金額不變 即使篩選了，合計金額並沒有變，仍然是把C2加到C7（「=SUM(C2:C7)」）。

五、SUBTOTAL函數 換個聰明一點的函數：「=SUBTOTAL(109,C2:C7)」，它有各種不同的加總方式，這裡用的是「109」，可以看到結果就是我們要的，只有小計被篩選出來的部份。

六、SUBTOTAL功能 按一下函數說明，瞭解到「SUBTOTAL」靈活度高，除了「109」，還可以實現很多不同函數的計算方式，並且能選擇「包括隱藏的值」和「忽略隱藏的值」。

經過實際測試，篩選一月份，「SUBTOTAL」參數用「109」和「9」，結果是一樣的，如果是用隱藏二三月份的方式，的確就會出現不同的結果，這是「SUBTOTAL」的特性，使用時必須注意。

七、選擇性平均值 將參數改成101：「=SUBTOTAL(101,C2:C7)」，搭配月份篩選，如此便實現了選擇性計算平均值。

八、AVERAGEIF函數 以篩選達到的選擇性計算平均值，從另一個角度而言，就是特定條件取平均值，可以設定公式：「=AVERAGEIF($A$2:$A$7,$A2,C2:C7)」。

「AVERAGE」是取平均值的Excel函數，「AVERAGEIF」作用類似於「SUMIF」，公式意思是在A2到A7之間，如果有等同於A2的單元儲存格，那麼取相對應的C2到C7儲存格的平均值，和上一節範例相同，這裡用了「$」固定住特定欄號和列號，以便直接拉公式填滿儲存格。

九、AVERAGE陣列 更高段是陣列函數：「{=AVERAGE(IF(($A$2:$A$7=$A$2),C2:C7))}」。

先輸入連同「=」在內的公式，滑鼠停留在資料編輯列，同時按住「Ctrl」和「Shift」，再按下「Enter」，兩邊會出現大括號，表示轉換成陣列函數。

Excel進階函數陣列公式 從上一節到這一節，都有介紹到套用陣列到函數裡的公式，應該能發現在必須條件化的情況下，陣列公式有一定類似的架構，如果能夠熟悉這個陣列架構，操作時比較直接並且很快，以後如有適當案例，再以獨立的章節多多介紹陣列的用法。

加入Line社群，口袋裡的Excel小教室！ 延伸閱讀： Excel取消合併儲存格，IF函數再快速鍵選擇性貼上 Excel資料篩選教學：應付帳款科餘明細表分析檢查 科目餘額表Excel會計專用檢查範例：Vlookup、Sum及Sumif Excel工作表清單：get.workbook巨集名稱與INDEX、FIND函數應用 Excel報表整理：IF、TRIM、MID函數刪除空白新增欄位 搜尋 最新文章 VBA取消刪除工作表的提醒，爬蟲取得網頁超連結 2022-04-07 VBA爬蟲取得網頁超連結，工作表重複自動偵錯 2022-03-27 APPInventor音樂播放器：響應式手機App操作介面設計 2022-03-20 WordPress文章編輯：利用自訂HTML區塊快速複製其他區塊 2022-03-18 營業稅線上申報教學：401申報書實務與PDF電子檔 2022-03-17 文章分類 Excel 程式 SEO 電影 人生 小說 職場 藝術 VBA Office 投資 音樂 分享 法律 美食 旅遊 所有文章分類 登入 記住我 忘記密碼？ 建立新帳號 成為講師 關於贊贊老師 與我聯絡 YouTube 部落格 所有課程 選單 成為講師 關於贊贊老師 與我聯絡 YouTube 部落格 所有課程 客服信箱：[email protected] 客服LineID：b88104069 關閉 插入/編輯連結 關閉 請輸入目標網址 網址 連結文字 在新分頁中開啟連結 或連結到現有的內容 搜尋 尚未指定搜尋詞彙。

以下顯示最近發佈的項目。

搜尋或使用向上/向下鍵以選取項目。

取消