close

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鍵來完成公式輸入。

方法三:=AGGREGATE(9,6,C2:C10)

這是一個傳回清單或資料庫中的數值彙總,第一個參數9(SUM)跟第二個參數6(忽略錯誤值),想知道更多數值的代表意義在官方說明頁面可以查看,AGGREGATE函數其實是一個超強的複合式函數,只要變化各個參數中的數值就可以有不同的功能。

語法: 參照形式AGGREGATE(函數編號,選項,彙總範圍1,彙總範圍2,…,彙總範圍253)
陣列形式AGGREGATE(函數編號,選項,陣列,[順序值])

函數編號:數字119,指定要使用哪一個函數。

函數編號

函數

函數編號

函數

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)


arrow
arrow
    創作者介紹

    錦子老師 發表在 痞客邦 留言(0) 人氣()