標籤：

財務人員必懂得幾個Excel函數（五）

06-23
有關SUMIF()函數和SUBTOTAL()函數的應用。一、SUMIF()的Excel幫助解釋如下：

SUMIF(range, criteria, [sum_range])

SUMIF 函數語法具有以下：

  • range必需。用於條件計算的單元格區域。每個區域中的單元格都必須是數字或名稱、數組或包含數字的引用。空值和文本值將被忽略。
  • criteria必需。用於確定對哪些單元格求和的條件，其形式可以為數字、表達式、單元格引用、文本或函數。
  • 要點任何文本條件或任何含有邏輯或數學符號的條件都必須使用雙引號 (") 括起來。如果條件為數字，則無需使用雙引號。
  • sum_range可選。要求和的實際單元格（如果要對未在 range 參數中指定的單元格求和）。如果省略 sum_range 參數，Excel 會對在範圍參數中指定的單元格（即應用條件的單元格）求和。

    • 注釋

  • 可以在 criteria 參數中使用通配符（包括問號 (?) 和星號 (*)）。問號匹配任意單個字元；星號匹配任意一串字元。如果要查找實際的問號或星號，請在該字元前鍵入波形符 (~)。

    • 這個函數解釋的很清楚，要注意的是第三參數可以簡寫，只要寫出第一個單元格地址Excel就會智能收索擴展區域，但這樣做在數據量大的情況下要付出速度為代價，建議看情況使用。

    這個函數在會計匯總憑證時經常使用，既簡單又方便，這裡我做個視頻大家看看：C5=IF(D5>0,"借",IF(D5<0,"貸","平"))向下拉，G5=IF(H5>0,"借",IF(H5<0,"貸","平"))向下拉，E5=SUMIF(輸入數據!$B$2:$B$1000,匯總表!$B5,輸入數據!C$2:C$1000)向右拉入貸方再向下拉。期初餘額和期末餘額需要自定義：

    實際自定義並不能改變正負數，只是將負數的符號隱藏起來了，大家可以依據視屏做個練習。 二、SUBTOTAL()函數的Excel幫助解釋如下：

    SUBTOTAL(function_num,ref1,[ref2],...])

    SUBTOTAL 函數語法具有以下：

  • Function_num 必需。1 到 11（包含隱藏值）或 101 到 111（忽略隱藏值）之間的數字，用於指定使用何種函數在列表中進行分類匯總計算。
    • Function_num（包含隱藏值） Function_num（忽略隱藏值） 函數
    1 101 AVERAGE
    2 102 COUNT
    3 103 COUNTA
    4 104 MAX
    5 105 MIN
    6 106 PRODUCT
    7 107 STDEV
    8 108 STDEVP
    9 109 SUM
    10 110 VAR
    11 111 VARP
  • Ref1 必需。要對其進行分類匯總計算的第一個命名區域或引用。
  • Ref2,... 可選。要對其進行分類匯總計算的第 2 個至第 254 個命名區域或引用。

    • 說明

  • 如果在 ref1、ref2…中有其他的分類匯總（嵌套分類匯總），將忽略這些嵌套分類匯總，以避免重複計算。
  • 當 function_num 為從 1 到 11 的常數時，SUBTOTAL 函數將包括通過「隱藏行」命令所隱藏的行中的值，該命令位於 Excel 桌面應用程序中「開始」選項卡上「單元格」組中「格式」命令的「隱藏和取消隱藏」子菜單下面。當您要對列表中的隱藏和非隱藏數字進行分類匯總時，請使用這些常數。當 function_num 為從 101 到 111 的常數時，SUBTOTAL 函數將忽略通過「隱藏行」命令所隱藏的行中的值。當您只想對列表中的非隱藏數字進行分類匯總時，請使用這些常數。
  • SUBTOTAL 函數忽略任何不包括在篩選結果中的行，不論使用什麼 function_num 值。
  • SUBTOTAL 函數適用於數據列或垂直區域。不適用於數據行或水平區域。例如，當 function_num 大於或等於 101 時需要分類匯總某個水平區域時，例如 SUBTOTAL(109,B2:G2)，則隱藏某一列不影響分類匯總。但是隱藏分類匯總的垂直區域中的某一行就會對其產生影響。
  • 如果所指定的某一引用為三維引用，函數 SUBTOTAL 將返回錯誤值 #VALUE!。

    • 說明返回列表或資料庫中的分類匯總。通常，使用 Excel 桌面應用程序中「數據」選項卡上「大綱」組中的「分類匯總」命令更便於創建帶有分類匯總的列表。一旦創建了分類匯總列表，就可以通過編輯 SUBTOTAL 函數對該列表進行修改。這個函數用處在會計工作中很大，使用也很方便，先舉例說明：

    序號里我們輸入這個公式：B5=SUBTOTAL(3,$C$5:C5)*1向下拉，這樣我們就可以得到序列。規格數量單價金額前面都講過，您可以自己做。在G5=F5+G5+H5<>0,在2007版本安裝後沒有【開發工具】功能這一項，需要按下圖操作，看圖來勾選【開放工具】：

    你看視頻：輸入後我們使用高級篩選錄製宏做個篩選按鈕和撤銷按鈕，大家看看【序號】有什麼變化：

    看完視屏你看看這個函數的使用多麼神奇和不可思議，它會隨著篩選空值隱藏後序號還是由小到大排序。另外：在計算合計和累計是也很方便，看圖你不妨試試：

    這個函數有時容易被大家忽視，我經常用它來對賬也很方便，這裡就不再講了，希望你多使用就會有體會這個函數的妙用。
    推薦閱讀：

    ※Excel函數中的No.1，從入門到精通！
    ※財務人員必懂得幾個Excel函數（一）
    ※Excel函數學習2：INDEX函數
    ※Excel函數之——一個用於日期計算的隱藏函數，不會就太可惜了
    ※Excel函數的參數

    TAG:財務 | 函數 | Excel | Excel函數 |