2153 |
三個忽略錯誤值或非數值進行加總的方法 |
Excel在台灣應用那麼多年,還是有一堆人對於基本函數應用不是非常熟悉,這是哪門子的古早問題我忘記了。有網友問到,當在欲計算數據中有錯誤值或是數值不存在(文字)時,要怎麼進行加總,記得以前在回答的時候,我一般都介紹使用SUMIF函數,將條件參數設為大於0就可以運算出來,由於錦子老師靜靜的思考一天,都怪我自己求好心切的,又想到其他二種方法可以做到(我想應該不只啦!),所以今天來和大家分享一下錦子老師的心血來潮解法,三種可以忽略錯誤值或是數值不存在(文字)的加總方法,給大家做為參考。
方法一:=SUMIF(C2:C10,">0")
這就是一開始提到的最簡單的方法,只要用SUMIF函數。
語法:SUMIF(欲計算範圍,條件)
D12儲存格公式:SUMIF(D2:D11,”>0”)
方法二:=SUM(IFERROR(C2:C10,0))
使用最常用的SUM函數來做,是為了讓大多數的使用者可以很快上手,但為了要忽略掉錯誤值或非數值(文字)的部份,所以利用IFERROR函數來判斷,只要是錯誤的話,就將其認定為0(第二個參數),所以遇上錯誤時會是以0來進行加總。
語法:IFERROR(欲判斷動作,產生錯誤的動作)
D12儲存格公式:=SUM(IFERROR(C2:C10,0)),由於是以陣列方式統計,故要按CTRL + SHIFT + ENTER鍵來完成公式輸入。
這是一個傳回清單或資料庫中的數值彙總,第一個參數9(SUM)跟第二個參數6(忽略錯誤值),想知道更多數值的代表意義在官方說明頁面可以查看,AGGREGATE函數其實是一個超強的複合式函數,只要變化各個參數中的數值就可以有不同的功能。
語法: 參照形式AGGREGATE(函數編號,選項,彙總範圍1,彙總範圍2,…,彙總範圍253)
陣列形式AGGREGATE(函數編號,選項,陣列,[順序值])
函數編號:數字1到19,指定要使用哪一個函數。
函數編號 |
函數 |
函數編號 |
函數 |
---|---|---|---|
1 |
AVERAGE |
11 |
VAR.P |
2 |
COUNT |
12 |
MEDIAN |
3 |
COUNTA |
13 |
MODE.SNGL |
4 |
MAX |
14 |
LARGE |
5 |
MIN |
15 |
SMALL |
6 |
PRODUCT |
16 |
PERCENTILE.INC |
7 |
STDEV.S |
17 |
QUARTILE.INC |
8 |
STDEV.P |
18 |
PERCENTILE.EXC |
9 |
SUM |
19 |
QUARTILE.EXC |
10 |
VAR.S |
|
|
選項:決定函數的評估範圍中要忽略哪些值。
選項 |
行為 |
---|---|
0 或省略 |
忽略巢狀 SUBTOTAL 及 AGGREGATE 函數 |
1 |
忽略隱藏列、巢狀 SUBTOTAL 及 AGGREGATE 函數 |
2 |
忽略錯誤值、巢狀 SUBTOTAL 及 AGGREGATE 函數 |
3 |
忽略隱藏列、錯誤值、巢狀 SUBTOTAL 及 AGGREGATE 函數 |
4 |
不忽略 |
5 |
忽略隱藏列 |
6 |
忽略錯誤值 |
7 |
忽略隱藏列及錯誤值 |
彙總範圍1為必要參數,彙總範圍2~253為選擇性參數可有可無。
陣列為計算彙總值的陣列、陣列公式或儲存格範圍的參照,由於某些函數必須要有順序值(第幾大、第幾小、….)
函數 |
---|
LARGE(array,k) |
SMALL(array,k) |
PERCENTILE.INC(array,k) |
QUARTILE.INC(array,quart) |
PERCENTILE.EXC(array,k) |
QUARTILE.EXC(array,quart) |
D12儲存格公式:=AGGREGATE(9,6,C2:C10)。
留言列表