2014年1月7日 星期二

Excel-計算文字型式的數字(SUMPRODUCT,VALUE,陣列公式)

有朋友問到:如果在一個資料表中的數字是以文字型式或是全型文字來顯示,能夠執行加總的動作嗎?因為有些儲存格是這類的數字,在運算時帶來一些困擾。

例如:參考下圖,A欄:正常的數字,B欄:半型的文字,C欄為全型的文字,D欄和E欄為國字。

(1) 參考第12列,其公式為:=SUM(範圍),例如:=SUM(B2:B11)

如果使用 SUM 函數,則只有數字的欄位能正常計算,其餘文字皆會被視為「0」來運算。

(2) 參考第13列,其公式為:=SUMPRODUCT(範圍*1)

建議改用 SUMPRODUCT 函數來運算,例如:=SUMPRODUCT(B2:B11*1)

公式中的「*1」,乃用於將文字自動轉換為數字,如此便可以運算了。

(3) 參考第14列,其公式為:{=SUM(VALUE(範圍))},例如:{=SUM(VALUE(B2:B11))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

透過 VALUE 函數將文字轉換為數字,使用陣列公式可以將一個儲存格範圍內的文字全部轉換為數字。

(4) 因為D欄和E欄的文字無法轉換為數字,所以運算結果都為 #VALUE! 錯誤。

沒有留言:

張貼留言

檢視其他文章

好康東東