2015年12月25日 星期五

COUNTIFS、SUMIFS及AVERAGEIFS 應用

       在 Google  試算表中 COUNTIFS、SUMIFS 及 AVERAGEIFS 這三個指令可以用多個條件限制所要範圍分別計算數量、總和及平均,其語法如下:
命令 語法
COUNTIFS COUNTIFS(加總範圍 1, 標準 1, [標準範圍 2, 標準 2, ...])
SUMIFS SUMIFS(加總範圍, 標準範圍 1, 標準 1, [標準範圍 2, 標準 2, ...])
AVERAGEIFS AVERAGEIFS(平均範圍, 標準範圍 1, 標準 1, [標準範圍 2, 表準 2, ...])
      為求容易了解,我們實際舉一個例子來說明,以下為範例表格,Data 為所要計算加總的資料範圍,Spec 1 至 3 為不同的條件範圍,分別為文字、TRUE/FALSE、數字。
image
     
       如果我們要計算 Data 資料範圍中符合 spec 1 中為 A 、spec 2 中為 TRUE 及 Spec 3 中大於30 的數值的總合可用下列語法:

=SUMIFS(B:B,C:C,"=A",D:D,FALSE,E:E,">30")

我們可看到其篩選標準條件的設定,除TRUE/FALSE邏輯值外,其標準條件是採用字串格式,例如針對篩選 spec 1 中符合 A 的值 ,我們可以輸入 “= A”,而針對 Spec 3 中大於 50 標準條件,可設為 “>30 ”,上例經輸入後計算結果為 164 。
      針對  AVERAGEIFS ,我們也可比照上例計算出相同條件下的平均值,輸入後計算結果為 54.66666667 。

 =AVERAGEIFS(B:B,C:C,"=A",D:D,FALSE,E:E,">30")

   但是 COUNTIFS 較前二個指令較大不同處,是其加總範圍 1,在本例也就是 DATA 資料範圍,也需要設一標準條件,如要求符合上面二例的數值總合了話,可以設定 Data 資料範圍內的數值皆大於 0,也就是一個空條件,例如下面設定。

=COUNTIFS(B:B,">0",C:C,"=A",D:D,FALSE,E:E,">30")

本例輸入後出來的結果為 3。

image

[Excel]如何創建自定義函數

在 Excel 內建函式中找不到自已想要的函式怎麼辦?事實上我們可以自已建一個,以下是一個簡單的Excel自定義函數示例,用於將民國年轉為西元: Function 民國轉西元(x As Double) As Double     民國轉西元 = x + 1911 End Func...